<?php
/**
 * Database Installation Script for Samanta CRM
 * Run this file once to set up the database tables
 */

require_once '../bootstrap/env.php';

// Database configuration
$host = 'localhost';
$dbname = 'samanta_crm';
$username = 'root';
$password = '';

try {
    // First, connect without specifying database to create it
    $pdo = new PDO("mysql:host=$host", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create database if it doesn't exist
    $pdo->exec("CREATE DATABASE IF NOT EXISTS `$dbname` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci");
    echo "✓ Database '$dbname' created successfully<br>";
    
    // Connect to the specific database
    $pdo = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    // Create user_types table first (before users table due to foreign key)
    $sql = "
    CREATE TABLE IF NOT EXISTS user_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) UNIQUE NOT NULL,
        description TEXT,
        permissions JSON,
        color VARCHAR(7) DEFAULT '#007bff',
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ User types table created successfully<br>";
    
    // Create languages table
    $sql = "
    CREATE TABLE IF NOT EXISTS languages (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(2) UNIQUE NOT NULL,
        name VARCHAR(100) NOT NULL,
        native_name VARCHAR(100),
        flag VARCHAR(10),
        is_active BOOLEAN DEFAULT TRUE,
        is_default BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ Languages table created successfully<br>";
    
    // Create translations table
    $sql = "
    CREATE TABLE IF NOT EXISTS translations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        language_id INT NOT NULL,
        translation_key VARCHAR(255) NOT NULL,
        translation_value TEXT NOT NULL,
        category VARCHAR(50) DEFAULT 'general',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_translation (language_id, translation_key),
        FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ Translations table created successfully<br>";

    // Create settings table
    $sql = "
    CREATE TABLE IF NOT EXISTS settings (
        `key` VARCHAR(100) PRIMARY KEY,
        `value` TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql);
    echo "✓ Settings table created successfully<br>";
    
    // Create users table
    $sql = "
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) UNIQUE NOT NULL,
        password VARCHAR(255) NOT NULL,
        role ENUM('user', 'admin') DEFAULT 'user',
        user_type_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_type_id) REFERENCES user_types(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ Users table created successfully<br>";
    
    // Create sessions table (for future use)
    $sql = "
    CREATE TABLE IF NOT EXISTS user_sessions (
        id VARCHAR(128) PRIMARY KEY,
        user_id INT NOT NULL,
        ip_address VARCHAR(45),
        user_agent TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ User sessions table created successfully<br>";
    
    // Create activity logs table (for future use)
    $sql = "
    CREATE TABLE IF NOT EXISTS activity_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        action VARCHAR(255) NOT NULL,
        description TEXT,
        ip_address VARCHAR(45),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    
    $pdo->exec($sql);
    echo "✓ Activity logs table created successfully<br>";

    // Create audit logs table (granular permission & entity change tracking)
    $sql = "
    CREATE TABLE IF NOT EXISTS audit_logs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        actor_id INT NULL,
        action VARCHAR(100) NOT NULL,
        entity_type VARCHAR(100) NOT NULL,
        entity_id INT NULL,
        details JSON NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE SET NULL,
        INDEX idx_entity (entity_type, entity_id),
        INDEX idx_action (action)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql);
    echo "✓ Audit logs table created successfully<br>";

    // Create categories table
    $sql = "CREATE TABLE IF NOT EXISTS categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT NULL,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_category_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Categories table created<br>";

    // Create products table
    $sql = "CREATE TABLE IF NOT EXISTS products (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(50) NOT NULL,
        name VARCHAR(255) NOT NULL,
        description TEXT NULL,
        category_id INT NULL,
        price DECIMAL(10,2) NULL,
        price_unit VARCHAR(20) NULL,
        notes TEXT NULL,
        production_site VARCHAR(100) NULL,
        factor DECIMAL(10,3) NULL,
        production_unit VARCHAR(20) NULL,
        report_flags JSON NULL,
        sort_order INT DEFAULT 0,
        status VARCHAR(30) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_product_code (code),
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Products table created<br>";

    // Create ingredients table (with supplier field)
    $sql = "CREATE TABLE IF NOT EXISTS ingredients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        unit VARCHAR(20) NULL,
        supplier VARCHAR(255) NULL,
        notes TEXT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY unique_ingredient_name (name)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Ingredients table created<br>";

    // Upgrade path: add supplier column if missing (older installations)
    try {
        $col = $pdo->query("SHOW COLUMNS FROM ingredients LIKE 'supplier'")->fetch();
        if (!$col) {
            $pdo->exec("ALTER TABLE ingredients ADD supplier VARCHAR(255) NULL AFTER unit");
            echo "✓ Added 'supplier' column to ingredients (upgrade)<br>";
        }
    } catch (Exception $e) { echo "⚠ Supplier column check failed: ".$e->getMessage()."<br>"; }

    // Create product_ingredients (pivot) table
    $sql = "CREATE TABLE IF NOT EXISTS product_ingredients (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT NOT NULL,
        ingredient_id INT NOT NULL,
        quantity DECIMAL(10,3) NULL,
        unit VARCHAR(20) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
        FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Product ingredients table created<br>";

    // Create units table (with scope: product, ingredient, both)
    $sql = "CREATE TABLE IF NOT EXISTS units (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(30) NOT NULL,
        name VARCHAR(100) NOT NULL,
        scope VARCHAR(20) DEFAULT 'product',
        is_active TINYINT(1) DEFAULT 1,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_unit_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Units table created<br>";

    // Upgrade path: add scope column if missing (previous installations before scope support)
    try {
        $col = $pdo->query("SHOW COLUMNS FROM units LIKE 'scope'")->fetch();
        if (!$col) {
            $pdo->exec("ALTER TABLE units ADD scope VARCHAR(20) DEFAULT 'product' AFTER name");
            $pdo->exec("UPDATE units SET scope='product' WHERE scope IS NULL OR scope='' ");
            echo "✓ Added missing 'scope' column to units (upgrade)<br>";
        }
    } catch (Exception $e) {
        echo "⚠ Scope column check failed: ".$e->getMessage()."<br>";
    }

    // Create report flags table
    $sql = "CREATE TABLE IF NOT EXISTS report_flags (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(100) NOT NULL,
        label VARCHAR(150) NOT NULL,
        description TEXT NULL,
        view_type VARCHAR(50) DEFAULT 'category_summary',
        show_additional TINYINT(1) DEFAULT 0,
        show_blocked TINYINT(1) DEFAULT 0,
        is_active TINYINT(1) DEFAULT 1,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_report_flag_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Report flags table created<br>";
    // Upgrade: add view_type if missing
    try { $col=$pdo->query("SHOW COLUMNS FROM report_flags LIKE 'view_type'")->fetch(); if(!$col){ $pdo->exec("ALTER TABLE report_flags ADD view_type VARCHAR(50) DEFAULT 'category_summary' AFTER description"); echo "✓ Added report_flags.view_type column (upgrade)<br>"; } } catch(Exception $e){ echo "⚠ view_type upgrade failed: ".$e->getMessage()."<br>"; }
    // Upgrade: add show_additional
    try { $col=$pdo->query("SHOW COLUMNS FROM report_flags LIKE 'show_additional'")->fetch(); if(!$col){ $pdo->exec("ALTER TABLE report_flags ADD show_additional TINYINT(1) DEFAULT 0 AFTER view_type"); echo "✓ Added report_flags.show_additional column (upgrade)<br>"; } } catch(Exception $e){ echo "⚠ show_additional upgrade failed: ".$e->getMessage()."<br>"; }
    // Upgrade: add show_blocked
    try { $col=$pdo->query("SHOW COLUMNS FROM report_flags LIKE 'show_blocked'")->fetch(); if(!$col){ $pdo->exec("ALTER TABLE report_flags ADD show_blocked TINYINT(1) DEFAULT 0 AFTER show_additional"); echo "✓ Added report_flags.show_blocked column (upgrade)<br>"; } } catch(Exception $e){ echo "⚠ show_blocked upgrade failed: ".$e->getMessage()."<br>"; }

    // Report flag override products table (include / exclude specific products for a flag)
    $sql = "CREATE TABLE IF NOT EXISTS report_flag_override_products (
        report_flag_id INT NOT NULL,
        product_id INT NOT NULL,
        mode ENUM('include','exclude') NOT NULL DEFAULT 'include',
        PRIMARY KEY(report_flag_id, product_id, mode),
        FOREIGN KEY (report_flag_id) REFERENCES report_flags(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Report flag override products table created<br>";

    // Create production sites table
    $sql = "CREATE TABLE IF NOT EXISTS production_sites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(50) NOT NULL,
        name VARCHAR(150) NOT NULL,
        description TEXT NULL,
        is_active TINYINT(1) DEFAULT 1,
        sort_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_prod_site_code (code)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Production sites table created<br>";

    // Create customers (receivers) table
    $sql = "CREATE TABLE IF NOT EXISTS customers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        email VARCHAR(255) NULL,
        phone VARCHAR(100) NULL,
        address TEXT NULL,
        notes TEXT NULL,
        status VARCHAR(30) DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY uniq_customer_email (email)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Customers table created<br>";

    // Customer access tables
    $sql = "CREATE TABLE IF NOT EXISTS customer_category_access (
        customer_id INT NOT NULL,
        category_id INT NOT NULL,
        PRIMARY KEY (customer_id, category_id),
        FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
        FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Customer category access table created<br>";

    $sql = "CREATE TABLE IF NOT EXISTS customer_blocked_products (
        customer_id INT NOT NULL,
        product_id INT NOT NULL,
        PRIMARY KEY (customer_id, product_id),
        FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Customer blocked products table created<br>";

    $sql = "CREATE TABLE IF NOT EXISTS customer_additional_products (
        customer_id INT NOT NULL,
        product_id INT NOT NULL,
        PRIMARY KEY (customer_id, product_id),
        FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Customer additional products table created<br>";

    // Customer assigned users pivot
    $sql = "CREATE TABLE IF NOT EXISTS customer_users (
        customer_id INT NOT NULL,
        user_id INT NOT NULL,
        PRIMARY KEY (customer_id,user_id),
        FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Customer users pivot table created<br>";

    // Orders table
    $sql = "CREATE TABLE IF NOT EXISTS orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        customer_id INT NOT NULL,
        order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        delivery_date DATE NULL,
        order_type VARCHAR(30) DEFAULT 'daily',
        status VARCHAR(30) DEFAULT 'pending',
        notes TEXT NULL,
        signature TEXT NULL,
        assigned_user_id INT NULL,
        created_by INT NULL,
        confirmed TINYINT(1) DEFAULT 0,
        confirm_user_id INT NULL,
        confirmed_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (assigned_user_id) REFERENCES users(id) ON DELETE SET NULL,
        FOREIGN KEY (confirm_user_id) REFERENCES users(id) ON DELETE SET NULL,
        INDEX idx_customer (customer_id),
        INDEX idx_status (status),
        INDEX idx_order_type (order_type)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Orders table created<br>";

    // Upgrade path for missing order columns
    try {
        $cols = $pdo->query("SHOW COLUMNS FROM orders")->fetchAll(PDO::FETCH_COLUMN);
        $need = [
            'order_type'=>"ALTER TABLE orders ADD order_type VARCHAR(30) DEFAULT 'daily' AFTER order_date",
            'delivery_date'=>"ALTER TABLE orders ADD delivery_date DATE NULL AFTER order_date",
            'signature'=>"ALTER TABLE orders ADD signature TEXT NULL AFTER notes",
            'assigned_user_id'=>"ALTER TABLE orders ADD assigned_user_id INT NULL AFTER signature",
            'confirmed'=>"ALTER TABLE orders ADD confirmed TINYINT(1) DEFAULT 0 AFTER created_by",
            'confirm_user_id'=>"ALTER TABLE orders ADD confirm_user_id INT NULL AFTER confirmed",
            'confirmed_at'=>"ALTER TABLE orders ADD confirmed_at DATETIME NULL AFTER confirm_user_id"
        ];
        foreach($need as $col=>$ddl){ if(!in_array($col,$cols,true)){ $pdo->exec($ddl); echo "✓ Added missing column orders.$col<br>"; } }
        // Upgrade order_date type to DATETIME if still DATE
        $colInfo = $pdo->query("SHOW COLUMNS FROM orders LIKE 'order_date'")->fetch(PDO::FETCH_ASSOC);
        if($colInfo && stripos($colInfo['Type'],'date')===0 && stripos($colInfo['Type'],'datetime')===false){
            try { $pdo->exec("ALTER TABLE orders MODIFY order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP"); echo "✓ Upgraded orders.order_date to DATETIME<br>"; } catch(Exception $e){ echo "⚠ Failed to upgrade order_date type: ".$e->getMessage()."<br>"; }
        }
    } catch(Exception $e){ echo "⚠ Order upgrade check failed: ".$e->getMessage()."<br>"; }

    // Order items table
    $sql = "CREATE TABLE IF NOT EXISTS order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_id INT NOT NULL,
        product_id INT NOT NULL,
        quantity DECIMAL(10,3) NOT NULL DEFAULT 1,
        unit_price DECIMAL(10,2) NULL,
        product_name_snapshot VARCHAR(255) NULL,
        product_code_snapshot VARCHAR(50) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
        INDEX idx_order (order_id)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
    $pdo->exec($sql); echo "✓ Order items table created<br>";

    // Seed units only if not already containing ingredient-specific units
    $existingScopeCheck = $pdo->query("SELECT COUNT(*) FROM units WHERE scope='ingredient' OR scope='both'")->fetchColumn();
    if ((int)$existingScopeCheck === 0) {
        $defaultUnitsProduct = [ ['szt','Sztuka','product',0], ['kg','Kilogram','both',1], ['blacha','Blacha','product',2] ];
        $stmt = $pdo->prepare("INSERT IGNORE INTO units (code,name,scope,sort_order) VALUES (?,?,?,?)");
        foreach ($defaultUnitsProduct as $u) { $stmt->execute($u); }
        $ingredientUnits = [ ['g','Gram','ingredient',0], ['mg','Milligram','ingredient',1], ['l','Litr','ingredient',2], ['ml','Mililitr','ingredient',3] ];
        foreach ($ingredientUnits as $u) { $stmt->execute($u); }
        echo "✓ Units (product & ingredient) inserted<br>";
    } else {
        echo "✓ Units already seeded (skipped)<br>";
    }

    // Seed default report flags (if empty)
    $reportFlags = [ ['ogolny','Ogólny',0,'category_summary'], ['kategoria','Kategoria',1,'category_summary'], ['piekarnia','Piekarnia',2,'category_with_customers'], ['cukiernia','Cukiernia',3,'category_with_customers'], ['lodziarnia','Lodziarnia',4,'category_summary'], ['magazyn','Magazyn',5,'category_summary'], ['wydawka1','Wydawka 1',6,'category_summary'], ['wydawka2','Wydawka 2',7,'category_summary'] ];
    $stmt = $pdo->prepare("INSERT IGNORE INTO report_flags (code,label,sort_order,view_type) VALUES (?,?,?,?)");
    foreach ($reportFlags as $rf) { $stmt->execute($rf); }
    echo "✓ Default report flags inserted<br>";

    // Seed production sites
    $prodSites = [ ['koscielisko','Kościelisko',0], ['kasprusie','Kasprusie',1], ['koscielisko_mag','Kościelisko - magazyn',2], ['kasprusie_mag','Kasprusie - magazyn',3] ];
    $stmt = $pdo->prepare("INSERT IGNORE INTO production_sites (code,name,sort_order) VALUES (?,?,?)");
    foreach ($prodSites as $ps) { $stmt->execute($ps); }
    echo "✓ Default production sites inserted<br>";
    
    // Insert default admin user
    $adminEmail = 'admin@example.com';
    $adminPassword = password_hash('password', PASSWORD_DEFAULT);
    
    $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
    $stmt->execute([$adminEmail]);
    
    if ($stmt->fetchColumn() == 0) {
        $stmt = $pdo->prepare("
            INSERT INTO users (name, email, password, role) 
            VALUES (?, ?, ?, ?)
        ");
        $stmt->execute(['Administrator', $adminEmail, $adminPassword, 'admin']);
        echo "✓ Default admin user created (admin@example.com / password)<br>";
    } else {
        echo "✓ Admin user already exists<br>";
    }
    
    // Insert default languages
    $languages = [
        ['en', 'English', 'English', '🇺🇸', true, true],
        ['es', 'Spanish', 'Español', '🇪🇸', true, false],
        ['fr', 'French', 'Français', '🇫🇷', true, false],
        ['de', 'German', 'Deutsch', '🇩🇪', true, false],
        ['it', 'Italian', 'Italiano', '🇮🇹', true, false],
        ['pt', 'Portuguese', 'Português', '🇵🇹', true, false],
        ['nl', 'Dutch', 'Nederlands', '🇳🇱', true, false],
        ['ru', 'Russian', 'Русский', '🇷🇺', true, false],
        ['zh', 'Chinese', '中文', '🇨🇳', true, false],
        ['ja', 'Japanese', '日本語', '🇯🇵', true, false]
    ];
    
    $sql = "INSERT IGNORE INTO languages (code, name, native_name, flag, is_active, is_default) VALUES (?, ?, ?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    foreach ($languages as $lang) {
        $stmt->execute($lang);
    }
    echo "✓ Default languages inserted successfully<br>";
    
    // Insert basic English translations
    $englishId = $pdo->query("SELECT id FROM languages WHERE code = 'en'")->fetchColumn();
    
    $translations = [
        // Navigation
        ['nav.dashboard', 'Dashboard', 'navigation'],
        ['nav.users', 'Users', 'navigation'],
        ['nav.user_types', 'User Types', 'navigation'],
        ['nav.languages', 'Languages', 'navigation'],
        ['nav.profile', 'Profile', 'navigation'],
        ['nav.logout', 'Logout', 'navigation'],
        
        // Common
        ['common.actions', 'Actions', 'common'],
        ['common.edit', 'Edit', 'common'],
        ['common.delete', 'Delete', 'common'],
        ['common.save', 'Save', 'common'],
        ['common.cancel', 'Cancel', 'common'],
        ['common.submit', 'Submit', 'common'],
        ['common.create', 'Create', 'common'],
        ['common.update', 'Update', 'common'],
        ['common.search', 'Search', 'common'],
        ['common.filter', 'Filter', 'common'],
        ['common.all', 'All', 'common'],
        ['common.active', 'Active', 'common'],
        ['common.inactive', 'Inactive', 'common'],
        ['common.yes', 'Yes', 'common'],
        ['common.no', 'No', 'common'],
        ['common.name', 'Name', 'common'],
        ['common.email', 'Email', 'common'],
        ['common.password', 'Password', 'common'],
        ['common.confirm_password', 'Confirm Password', 'common'],
        ['common.type', 'Type', 'common'],
        ['common.status', 'Status', 'common'],
        ['common.created_at', 'Created At', 'common'],
        ['common.updated_at', 'Updated At', 'common'],
        
        // Authentication
        ['auth.login', 'Login', 'auth'],
        ['auth.login_title', 'Login to Your Account', 'auth'],
        ['auth.remember_me', 'Remember Me', 'auth'],
        ['auth.forgot_password', 'Forgot Password?', 'auth'],
        ['auth.logout', 'Logout', 'auth'],
        ['auth.register', 'Register', 'auth'],
        ['auth.sign_up', 'Sign Up', 'auth'],
        
        // Dashboard
        ['dashboard.welcome', 'Welcome to Samanta CRM', 'dashboard'],
        ['dashboard.total_users', 'Total Users', 'dashboard'],
        ['dashboard.active_users', 'Active Users', 'dashboard'],
        ['dashboard.user_types', 'User Types', 'dashboard'],
        ['dashboard.recent_activity', 'Recent Activity', 'dashboard'],
        
        // Users
        ['users.title', 'Users Management', 'users'],
        ['users.create', 'Create User', 'users'],
        ['users.edit', 'Edit User', 'users'],
        ['users.list', 'Users List', 'users'],
        ['users.first_name', 'First Name', 'users'],
        ['users.last_name', 'Last Name', 'users'],
        ['users.user_type', 'User Type', 'users'],
        ['users.created_successfully', 'User created successfully', 'users'],
        ['users.updated_successfully', 'User updated successfully', 'users'],
        ['users.deleted_successfully', 'User deleted successfully', 'users'],
        
        // User Types
        ['user_types.title', 'User Types Management', 'user_types'],
        ['user_types.create', 'Create User Type', 'user_types'],
        ['user_types.edit', 'Edit User Type', 'user_types'],
        ['user_types.list', 'User Types List', 'user_types'],
        ['user_types.description', 'Description', 'user_types'],
        ['user_types.permissions', 'Permissions', 'user_types'],
        ['user_types.created_successfully', 'User type created successfully', 'user_types'],
        ['user_types.updated_successfully', 'User type updated successfully', 'user_types'],
        ['user_types.deleted_successfully', 'User type deleted successfully', 'user_types'],
        
        // Languages
        ['languages.title', 'Languages Management', 'languages'],
        ['languages.create', 'Add Language', 'languages'],
        ['languages.edit', 'Edit Language', 'languages'],
        ['languages.list', 'Languages List', 'languages'],
        ['languages.code', 'Code', 'languages'],
        ['languages.native_name', 'Native Name', 'languages'],
        ['languages.flag', 'Flag', 'languages'],
        ['languages.default', 'Default', 'languages'],
        ['languages.translations', 'Translations', 'languages'],
        ['languages.manage_translations', 'Manage Translations', 'languages'],
        ['languages.created_successfully', 'Language created successfully', 'languages'],
        ['languages.updated_successfully', 'Language updated successfully', 'languages'],
        ['languages.deleted_successfully', 'Language deleted successfully', 'languages'],
        
        // Translations
        ['translations.title', 'Translations Management', 'translations'],
        ['translations.key', 'Translation Key', 'translations'],
        ['translations.value', 'Translation Value', 'translations'],
        ['translations.category', 'Category', 'translations'],
        ['translations.created_successfully', 'Translation created successfully', 'translations'],
        ['translations.updated_successfully', 'Translation updated successfully', 'translations'],
        ['translations.deleted_successfully', 'Translation deleted successfully', 'translations'],
        
        // Messages
        ['messages.success', 'Operation completed successfully', 'messages'],
        ['messages.error', 'An error occurred', 'messages'],
        ['messages.confirm_delete', 'Are you sure you want to delete this item?', 'messages'],
        ['messages.no_data', 'No data available', 'messages'],
        ['messages.loading', 'Loading...', 'messages'],
        
        // Permissions
        ['permissions.users_view', 'View Users', 'permissions'],
        ['permissions.users_create', 'Create Users', 'permissions'],
        ['permissions.users_edit', 'Edit Users', 'permissions'],
        ['permissions.users_delete', 'Delete Users', 'permissions'],
        ['permissions.user_types_view', 'View User Types', 'permissions'],
        ['permissions.user_types_create', 'Create User Types', 'permissions'],
        ['permissions.user_types_edit', 'Edit User Types', 'permissions'],
        ['permissions.user_types_delete', 'Delete User Types', 'permissions'],
        ['permissions.languages_view', 'View Languages', 'permissions'],
        ['permissions.languages_create', 'Create Languages', 'permissions'],
        ['permissions.languages_edit', 'Edit Languages', 'permissions'],
        ['permissions.languages_delete', 'Delete Languages', 'permissions'],
        ['permissions.translations_view', 'View Translations', 'permissions'],
        ['permissions.translations_create', 'Create Translations', 'permissions'],
        ['permissions.translations_edit', 'Edit Translations', 'permissions'],
        ['permissions.translations_delete', 'Delete Translations', 'permissions']
    ];
    
    $sql = "INSERT IGNORE INTO translations (language_id, translation_key, translation_value, category) VALUES (?, ?, ?, ?)";
    $stmt = $pdo->prepare($sql);
    
    foreach ($translations as $translation) {
        $stmt->execute([$englishId, $translation[0], $translation[1], $translation[2]]);
    }
    echo "✓ Basic English translations inserted successfully<br>";

    // Insert default settings
    $defaultSettings = [
        'site_name' => 'Samanta CRM',
        'default_language' => 'en',
        'maintenance_mode' => '0',
        'maintenance_message' => 'The system is under maintenance. Please check back soon.'
    ];
    $stmt = $pdo->prepare("INSERT INTO settings (`key`,`value`) VALUES (?,?) ON DUPLICATE KEY UPDATE value = VALUES(value)");
    foreach ($defaultSettings as $k => $v) { $stmt->execute([$k, $v]); }
    echo "✓ Default settings inserted<br>";
    
    // Insert default user types
    $defaultUserTypes = [
        [
            'name' => 'Administrator',
            'description' => 'Full system access with all permissions',
            'permissions' => json_encode([
                'users' => ['view' => true, 'create' => true, 'edit' => true, 'delete' => true],
                'user_types' => ['view' => true, 'create' => true, 'edit' => true, 'delete' => true],
                'dashboard' => ['view' => true, 'admin' => true],
                'system' => ['settings' => true, 'logs' => true, 'backup' => true]
            ]),
            'color' => '#dc3545'
        ],
        [
            'name' => 'Manager',
            'description' => 'User management and dashboard access',
            'permissions' => json_encode([
                'users' => ['view' => true, 'create' => true, 'edit' => true],
                'dashboard' => ['view' => true]
            ]),
            'color' => '#fd7e14'
        ],
        [
            'name' => 'Employee',
            'description' => 'Basic dashboard access',
            'permissions' => json_encode([
                'dashboard' => ['view' => true]
            ]),
            'color' => '#198754'
        ],
        [
            'name' => 'Guest',
            'description' => 'Limited read-only access',
            'permissions' => json_encode([
                'dashboard' => ['view' => true]
            ]),
            'color' => '#6c757d'
        ]
    ];
    
    foreach ($defaultUserTypes as $userType) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM user_types WHERE name = ?");
        $stmt->execute([$userType['name']]);
        
        if ($stmt->fetchColumn() == 0) {
            $stmt = $pdo->prepare("
                INSERT INTO user_types (name, description, permissions, color) 
                VALUES (?, ?, ?, ?)
            ");
            $stmt->execute([
                $userType['name'],
                $userType['description'],
                $userType['permissions'],
                $userType['color']
            ]);
            echo "✓ Default user type created: {$userType['name']}<br>";
        }
    }
    
    // Insert sample users
    $sampleUsers = [
        ['John Doe', 'john@example.com', 'user'],
        ['Jane Smith', 'jane@example.com', 'user'],
        ['Bob Johnson', 'bob@example.com', 'user']
    ];
    
    foreach ($sampleUsers as $user) {
        $stmt = $pdo->prepare("SELECT COUNT(*) FROM users WHERE email = ?");
        $stmt->execute([$user[1]]);
        
        if ($stmt->fetchColumn() == 0) {
            $stmt = $pdo->prepare("
                INSERT INTO users (name, email, password, role) 
                VALUES (?, ?, ?, ?)
            ");
            $stmt->execute([$user[0], $user[1], password_hash('password', PASSWORD_DEFAULT), $user[2]]);
            echo "✓ Sample user created: {$user[1]}<br>";
        }
    }
            /* ------------------------------------------------------------
             * STOCK / STORAGE MODULE (ingredients usage & restocks)
             * ------------------------------------------------------------ */

            // Ingredient storage ledger (each row = stock movement)
            $sql = "CREATE TABLE IF NOT EXISTS ingredient_storage (
                id INT AUTO_INCREMENT PRIMARY KEY,
                ingredient_id INT NOT NULL,
                location VARCHAR(50) DEFAULT 'MAIN',
                change_qty DECIMAL(12,3) NOT NULL,
                reason VARCHAR(50) DEFAULT 'manual',
                note TEXT NULL,
                ref_date DATE NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
                INDEX idx_ing_loc (ingredient_id, location),
                INDEX idx_reason (reason),
                INDEX idx_ref_date (ref_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $pdo->exec($sql); echo "✓ Ingredient storage ledger table created<br>";

            // Restocks master (now with supplier field)
            $sql = "CREATE TABLE IF NOT EXISTS restocks (
                id INT AUTO_INCREMENT PRIMARY KEY,
                restock_date DATE NOT NULL,
                supplier VARCHAR(255) NULL,
                notes TEXT NULL,
                created_by INT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_restock_date (restock_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $pdo->exec($sql); echo "✓ Restocks table created<br>";
            // Upgrade: add supplier column if missing (older installs)
            try {
                $col = $pdo->query("SHOW COLUMNS FROM restocks LIKE 'supplier'")->fetch();
                if(!$col){
                    $pdo->exec("ALTER TABLE restocks ADD supplier VARCHAR(255) NULL AFTER restock_date");
                    echo "✓ Added restocks.supplier column (upgrade)<br>";
                }
            } catch(Exception $e){ echo "⚠ restocks supplier upgrade failed: ".$e->getMessage()."<br>"; }

            // Restock items
            $sql = "CREATE TABLE IF NOT EXISTS restock_items (
                id INT AUTO_INCREMENT PRIMARY KEY,
                restock_id INT NOT NULL,
                ingredient_id INT NOT NULL,
                quantity DECIMAL(12,3) NOT NULL,
                location VARCHAR(50) DEFAULT 'MAIN',
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (restock_id) REFERENCES restocks(id) ON DELETE CASCADE,
                FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
                INDEX idx_restock_ing (restock_id, ingredient_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $pdo->exec($sql); echo "✓ Restock items table created<br>";

            // Applied daily usage summary (prevent double apply)
            $sql = "CREATE TABLE IF NOT EXISTS ingredient_usage_applied (
                id INT AUTO_INCREMENT PRIMARY KEY,
                apply_date DATE NOT NULL,
                ingredient_id INT NOT NULL,
                applied_qty DECIMAL(12,3) NOT NULL DEFAULT 0,
                rolled_back TINYINT(1) DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY uniq_ing_date (apply_date, ingredient_id),
                FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
                INDEX idx_apply_date (apply_date)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $pdo->exec($sql); echo "✓ Ingredient usage applied table created<br>";

            // Applied usage log (audit per action & delta)
            $sql = "CREATE TABLE IF NOT EXISTS ingredient_usage_applied_log (
                id INT AUTO_INCREMENT PRIMARY KEY,
                apply_date DATE NOT NULL,
                ingredient_id INT NOT NULL,
                delta_qty DECIMAL(12,3) NOT NULL,
                action ENUM('apply','rollback') NOT NULL DEFAULT 'apply',
                user_id INT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (ingredient_id) REFERENCES ingredients(id) ON DELETE CASCADE,
                FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL,
                INDEX idx_apply_log_date (apply_date),
                INDEX idx_apply_log_action (action)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";
            $pdo->exec($sql); echo "✓ Ingredient usage applied log table created<br>";

            // Upgrade safety: add missing columns if older structure exists
            try {
                $cols = $pdo->query("SHOW COLUMNS FROM ingredient_storage")->fetchAll(PDO::FETCH_COLUMN);
                if($cols && !in_array('reason',$cols,true)){
                    $pdo->exec("ALTER TABLE ingredient_storage ADD reason VARCHAR(50) DEFAULT 'manual' AFTER change_qty");
                    echo "✓ Upgraded ingredient_storage: added reason column<br>";
                }
                if($cols && !in_array('ref_date',$cols,true)){
                    // place ref_date after note if note exists, else after reason / change_qty fallback
                    $after = in_array('note',$cols,true)?'note':(in_array('reason',$cols,true)?'reason':'change_qty');
                    try {
                        $pdo->exec("ALTER TABLE ingredient_storage ADD ref_date DATE NULL AFTER $after, ADD INDEX idx_ref_date (ref_date)");
                        echo "✓ Upgraded ingredient_storage: added ref_date column<br>";
                    } catch(Exception $e){ echo "⚠ ref_date add failed: ".$e->getMessage()."<br>"; }
                }
                // Upgrade legacy ingredient_usage_applied columns (usage_date->apply_date, quantity->applied_qty)
                $uCols=[]; try { $uCols = $pdo->query("SHOW COLUMNS FROM ingredient_usage_applied")->fetchAll(PDO::FETCH_COLUMN); } catch(Exception $e) { $uCols=[]; }
                if($uCols){
                    if(in_array('usage_date',$uCols,true) && !in_array('apply_date',$uCols,true)){
                        try { $pdo->exec("ALTER TABLE ingredient_usage_applied CHANGE usage_date apply_date DATE NOT NULL"); echo "✓ Renamed usage_date -> apply_date<br>"; } catch(Exception $e){ echo "⚠ Rename usage_date failed: ".$e->getMessage()."<br>"; }
                    }
                    if(in_array('quantity',$uCols,true) && !in_array('applied_qty',$uCols,true)){
                        try { $pdo->exec("ALTER TABLE ingredient_usage_applied CHANGE quantity applied_qty DECIMAL(12,3) NOT NULL DEFAULT 0"); echo "✓ Renamed quantity -> applied_qty<br>"; } catch(Exception $e){ echo "⚠ Rename quantity failed: ".$e->getMessage()."<br>"; }
                    }
                }
            } catch(Exception $e){ echo "⚠ ingredient_storage upgrade check failed: ".$e->getMessage()."<br>"; }

    
    echo "<br><div style='color: green; font-weight: bold;'>🎉 Installation completed successfully!</div>";
    echo "<br><strong>Login Details:</strong>";
    echo "<br>Email: admin@example.com";
    echo "<br>Password: password";
    echo "<br><br><a href='/samanta_crm/' style='background: #007bff; color: white; padding: 10px 20px; text-decoration: none; border-radius: 5px;'>Go to Login</a>";
    
} catch (PDOException $e) {
    echo "<div style='color: red;'>Error: " . $e->getMessage() . "</div>";
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Samanta CRM - Installation</title>
    <style>
        body {
            font-family: Arial, sans-serif;
            max-width: 600px;
            margin: 50px auto;
            padding: 20px;
            background: #f8f9fa;
        }
        .container {
            background: white;
            padding: 30px;
            border-radius: 10px;
            box-shadow: 0 2px 10px rgba(0,0,0,0.1);
        }
        h1 {
            color: #333;
            text-align: center;
            margin-bottom: 30px;
        }
    </style>
</head>
<body>
    <div class="container">
        <h1>🚀 Samanta CRM Installation</h1>
        <p>Setting up your database and initial configuration...</p>
        <hr>
    </div>
</body>
</html>
