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

try {
    $pdo = db();
    $sql = "
    CREATE TABLE IF NOT EXISTS `users` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `email` VARCHAR(255) NOT NULL UNIQUE,
      `password` VARCHAR(255) NOT NULL,
      `planType` ENUM('basic', 'pro') DEFAULT 'basic',
      `creditsFastRemaining` INT DEFAULT 10,
      `creditsQualityRemaining` INT DEFAULT 5,
      `monthlyResetDate` DATE,
      `role` ENUM('user', 'admin') DEFAULT 'user',
      `createdAt` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    ";
    $pdo->exec($sql);

    $sql = "
    CREATE TABLE IF NOT EXISTS `projects` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `user_id` INT NOT NULL,
      `title` VARCHAR(255) NOT NULL,
      `story_text` TEXT,
      `style` VARCHAR(100),
      `target_duration` INT,
      `status` ENUM('draft', 'scripted', 'rendering', 'completed', 'failed') DEFAULT 'draft',
      `final_video_url` VARCHAR(255) NULL,
      `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
    );
    ";
    $pdo->exec($sql);

    $sql = "
    CREATE TABLE IF NOT EXISTS `scenes` (
      `id` INT AUTO_INCREMENT PRIMARY KEY,
      `project_id` INT NOT NULL,
      `user_id` INT NOT NULL,
      `scene_number` INT NULL,
      `description` TEXT,
      `duration` INT,
      `shot_type` VARCHAR(100),
      `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
      FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
      FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    );
    ";
    $pdo->exec($sql);

    echo "Database migration completed successfully." . PHP_EOL;
} catch (PDOException $e) {
    die("Database migration failed: " . $e->getMessage());
}