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

function migrate_001_initial_schema() {
    $pdo = db();
    $sql = <<<SQL
CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `icon_url` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `brand` VARCHAR(255),
    `category_id` INT,
    `subcategory` VARCHAR(255),
    `image_url` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`) ON DELETE SET NULL,
    FULLTEXT KEY `ft_name_brand` (`name`, `brand`)
);

CREATE TABLE IF NOT EXISTS `platforms` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `slug` VARCHAR(255) NOT NULL UNIQUE,
    `logo_url` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS `product_listings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `platform_id` INT NOT NULL,
    `original_price` DECIMAL(10, 2),
    `discounted_price` DECIMAL(10, 2),
    `coupon_price` DECIMAL(10, 2),
    `effective_price` DECIMAL(10, 2),
    `rating` FLOAT,
    `final_score` FLOAT,
    `badge` ENUM('CHEAPEST', 'TOP RATED', 'BEST DEAL'),
    `product_url` TEXT,
    `last_synced_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`platform_id`) REFERENCES `platforms`(`id`) ON DELETE CASCADE,
    INDEX `idx_effective_price` (`effective_price`),
    INDEX `idx_rating` (`rating`)
);
SQL;

    try {
        $pdo->exec($sql);
        echo "Database schema created successfully." . PHP_EOL;
    } catch (PDOException $e) {
        die("Error creating schema: " . $e->getMessage());
    }
}

// If this script is run directly, execute the migration.
if (basename(__FILE__) == basename($_SERVER["SCRIPT_FILENAME"])) {
    migrate_001_initial_schema();
}