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

try {
    $pdo = db();

    $sql = "
    CREATE TABLE IF NOT EXISTS watchlist (
        id INT AUTO_INCREMENT PRIMARY KEY,
        symbol VARCHAR(20) NOT NULL UNIQUE,
        company_name VARCHAR(255),
        price DECIMAL(10, 2) DEFAULT 0.00,
        change_pct DECIMAL(5, 2) DEFAULT 0.00,
        added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );
    ";

    $pdo->exec($sql);

    // Let's add the initial mock data to the new table
    $stocks = [
        ['symbol' => 'TSLA', 'company_name' => 'Tesla, Inc.', 'price' => 177.48, 'change_pct' => -1.45],
        ['symbol' => 'AAPL', 'company_name' => 'Apple Inc.', 'price' => 170.03, 'change_pct' => 0.35],
        ['symbol' => 'GOOGL', 'company_name' => 'Alphabet Inc.', 'price' => 139.44, 'change_pct' => 1.25],
        ['symbol' => 'AMZN', 'company_name' => 'Amazon.com, Inc.', 'price' => 134.91, 'change_pct' => -0.21],
    ];

    $stmt = $pdo->prepare("INSERT IGNORE INTO watchlist (symbol, company_name, price, change_pct) VALUES (:symbol, :company_name, :price, :change_pct)");

    foreach ($stocks as $stock) {
        $stmt->execute($stock);
    }

    echo "Migration successful: 'watchlist' table created and seeded.\n";

} catch (PDOException $e) {
    die("Migration failed: " . $e->getMessage() . "\n");
}