<?php
require_once __DIR__ . '/../config/database.php';

try {
    $database = new Database();
    $conn = $database->getConnection();
    
    echo "=== إنشاء جداول المخزون ===/n/n";
    
    // 1. إضافة أعمدة المخزون لجدول المنتجات
    echo "1. تحديث جدول المنتجات.../n";
    
    $columns_to_add = [
        "ALTER TABLE products ADD COLUMN IF NOT EXISTS stock_quantity INT DEFAULT 0",
        "ALTER TABLE products ADD COLUMN IF NOT EXISTS low_stock_alert INT DEFAULT 5",
        "ALTER TABLE products ADD COLUMN IF NOT EXISTS sku VARCHAR(100) UNIQUE",
        "ALTER TABLE products ADD COLUMN IF NOT EXISTS track_inventory BOOLEAN DEFAULT TRUE"
    ];
    
    foreach ($columns_to_add as $sql) {
        try {
            $conn->exec($sql);
        } catch (PDOException $e) {
            // العمود موجود بالفعل
        }
    }
    echo "   ✓ تم تحديث جدول المنتجات/n";
    
    // 2. جدول حركات المخزون
    echo "/n2. إنشاء جدول حركات المخزون.../n";
    $sql = "CREATE TABLE IF NOT EXISTS inventory_movements (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT NOT NULL,
        movement_type ENUM('in', 'out', 'adjustment', 'return') NOT NULL,
        quantity INT NOT NULL,
        previous_quantity INT NOT NULL,
        new_quantity INT NOT NULL,
        reference_type VARCHAR(50),
        reference_id INT,
        notes TEXT,
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_product_id (product_id),
        INDEX idx_movement_type (movement_type),
        INDEX idx_created_at (created_at),
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    $conn->exec($sql);
    echo "   ✓ تم إنشاء جدول inventory_movements/n";
    
    // 3. جدول تنبيهات المخزون
    echo "/n3. إنشاء جدول تنبيهات المخزون.../n";
    $sql = "CREATE TABLE IF NOT EXISTS inventory_alerts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        product_id INT NOT NULL,
        alert_type ENUM('low_stock', 'out_of_stock') NOT NULL,
        current_quantity INT NOT NULL,
        threshold_quantity INT NOT NULL,
        is_resolved BOOLEAN DEFAULT FALSE,
        resolved_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_product_id (product_id),
        INDEX idx_is_resolved (is_resolved),
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    $conn->exec($sql);
    echo "   ✓ تم إنشاء جدول inventory_alerts/n";
    
    // 4. جدول الموردين
    echo "/n4. إنشاء جدول الموردين.../n";
    $sql = "CREATE TABLE IF NOT EXISTS suppliers (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        contact_person VARCHAR(255),
        phone VARCHAR(20),
        email VARCHAR(255),
        address TEXT,
        notes TEXT,
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_name (name),
        INDEX idx_is_active (is_active)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    $conn->exec($sql);
    echo "   ✓ تم إنشاء جدول suppliers/n";
    
    // 5. جدول طلبات الشراء
    echo "/n5. إنشاء جدول طلبات الشراء.../n";
    $sql = "CREATE TABLE IF NOT EXISTS purchase_orders (
        id INT AUTO_INCREMENT PRIMARY KEY,
        order_number VARCHAR(50) UNIQUE NOT NULL,
        supplier_id INT,
        total_amount DECIMAL(10,2) NOT NULL,
        status ENUM('pending', 'approved', 'received', 'cancelled') DEFAULT 'pending',
        notes TEXT,
        created_by INT,
        received_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_order_number (order_number),
        INDEX idx_supplier_id (supplier_id),
        INDEX idx_status (status),
        FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    $conn->exec($sql);
    echo "   ✓ تم إنشاء جدول purchase_orders/n";
    
    // 6. جدول عناصر طلبات الشراء
    echo "/n6. إنشاء جدول عناصر طلبات الشراء.../n";
    $sql = "CREATE TABLE IF NOT EXISTS purchase_order_items (
        id INT AUTO_INCREMENT PRIMARY KEY,
        purchase_order_id INT NOT NULL,
        product_id INT NOT NULL,
        quantity INT NOT NULL,
        unit_price DECIMAL(10,2) NOT NULL,
        total_price DECIMAL(10,2) NOT NULL,
        received_quantity INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        INDEX idx_purchase_order_id (purchase_order_id),
        INDEX idx_product_id (product_id),
        FOREIGN KEY (purchase_order_id) REFERENCES purchase_orders(id) ON DELETE CASCADE,
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";
    $conn->exec($sql);
    echo "   ✓ تم إنشاء جدول purchase_order_items/n";
    
    echo "/n✅ تم إنشاء جميع جداول المخزون بنجاح!/n";
    echo "/n📊 الجداول المُنشأة:/n";
    echo "   - inventory_movements (حركات المخزون)/n";
    echo "   - inventory_alerts (تنبيهات المخزون)/n";
    echo "   - suppliers (الموردين)/n";
    echo "   - purchase_orders (طلبات الشراء)/n";
    echo "   - purchase_order_items (عناصر طلبات الشراء)/n";
    echo "   - تحديث جدول products (إضافة أعمدة المخزون)/n";
    
} catch (PDOException $e) {
    echo "❌ خطأ: " . $e->getMessage() . "/n";
}