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

try {
    echo "Starting Database Migration v3...\n";

    // 1. Create colors table
    $pdo->exec("CREATE TABLE IF NOT EXISTS colors (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL UNIQUE,
        hex VARCHAR(50) NOT NULL,
        border VARCHAR(50) DEFAULT '#FFFFFF'
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'colors' verified/created.\n";

    // 2. Create sizes table
    $pdo->exec("CREATE TABLE IF NOT EXISTS sizes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'sizes' verified/created.\n";

    // 3. Create product_colors table
    $pdo->exec("CREATE TABLE IF NOT EXISTS product_colors (
        product_id INT NOT NULL,
        color_id INT NOT NULL,
        PRIMARY KEY (product_id, color_id),
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
        FOREIGN KEY (color_id) REFERENCES colors(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'product_colors' verified/created.\n";

    // 4. Create product_sizes table
    $pdo->exec("CREATE TABLE IF NOT EXISTS product_sizes (
        product_id INT NOT NULL,
        size_id INT NOT NULL,
        PRIMARY KEY (product_id, size_id),
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
        FOREIGN KEY (size_id) REFERENCES sizes(id) ON DELETE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;");
    echo "Table 'product_sizes' verified/created.\n";

    // 5. Add color and size columns to order_items safely
    try {
        $pdo->exec("ALTER TABLE order_items ADD COLUMN color VARCHAR(255) DEFAULT NULL");
        echo "Added 'color' column to order_items.\n";
    } catch (PDOException $e) {
        // Safe to ignore if column exists
        echo "Column 'color' in order_items already exists or check failed: " . $e->getMessage() . "\n";
    }

    try {
        $pdo->exec("ALTER TABLE order_items ADD COLUMN size VARCHAR(50) DEFAULT NULL");
        echo "Added 'size' column to order_items.\n";
    } catch (PDOException $e) {
        // Safe to ignore if column exists
        echo "Column 'size' in order_items already exists or check failed: " . $e->getMessage() . "\n";
    }

    // 6. Insert default colors
    $default_colors = [
        ['Midnight Black', '#050505', '#2A2A2A'],
        ['Royal Gold', '#DCC7A3', '#FFFFFF'],
        ['Ivory White', '#F4F4F4', '#E0E0E0'],
        ['Espresso Brown', '#4E3629', '#6A4E3D']
    ];

    $stmt_color = $pdo->prepare("INSERT INTO colors (name, hex, border) VALUES (?, ?, ?) ON DUPLICATE KEY UPDATE hex=VALUES(hex), border=VALUES(border)");
    foreach ($default_colors as $c) {
        $stmt_color->execute($c);
    }
    echo "Default colors verified/populated.\n";

    // 7. Insert default sizes
    $default_sizes = ['XS', 'S', 'M', 'L', 'XL'];
    $stmt_size = $pdo->prepare("INSERT INTO sizes (name) VALUES (?) ON DUPLICATE KEY UPDATE name=name");
    foreach ($default_sizes as $s) {
        $stmt_size->execute([$s]);
    }
    echo "Default sizes verified/populated.\n";

    // 8. Assign defaults to existing products
    $products = $pdo->query("SELECT id FROM products")->fetchAll(PDO::FETCH_COLUMN);
    $colors = $pdo->query("SELECT id FROM colors")->fetchAll(PDO::FETCH_COLUMN);
    $sizes = $pdo->query("SELECT id FROM sizes")->fetchAll(PDO::FETCH_COLUMN);

    if (!empty($products)) {
        $pdo->beginTransaction();

        $stmt_prod_color = $pdo->prepare("INSERT IGNORE INTO product_colors (product_id, color_id) VALUES (?, ?)");
        $stmt_prod_size = $pdo->prepare("INSERT IGNORE INTO product_sizes (product_id, size_id) VALUES (?, ?)");

        foreach ($products as $pid) {
            foreach ($colors as $cid) {
                $stmt_prod_color->execute([$pid, $cid]);
            }
            foreach ($sizes as $sid) {
                $stmt_prod_size->execute([$pid, $sid]);
            }
        }

        $pdo->commit();
        echo "Successfully mapped all existing products to the default colors and sizes.\n";
    }

    echo "Migration completed successfully!\n";

} catch (Exception $e) {
    if ($pdo->inTransaction()) {
        $pdo->rollBack();
    }
    echo "Migration failed: " . $e->getMessage() . "\n";
}
?>
