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

try {
    $pdo = db();
    $pdo->exec("CREATE TABLE IF NOT EXISTS `users` (
        `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `email` VARCHAR(255) NOT NULL UNIQUE,
        `password` VARCHAR(255) NOT NULL,
        `google_id` VARCHAR(255) NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
    echo "Table 'users' created successfully." . PHP_EOL;

    $pdo->exec("CREATE TABLE IF NOT EXISTS `favorite_locations` (
        `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT(11) UNSIGNED NOT NULL,
        `city_name` VARCHAR(255) NOT NULL,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `user_city` (`user_id`, `city_name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
    echo "Table 'favorite_locations' created successfully." . PHP_EOL;

    $pdo->exec("CREATE TABLE IF NOT EXISTS `weather_subscriptions` (
        `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `user_id` INT(11) UNSIGNED NOT NULL,
        `location_id` INT(11) UNSIGNED NOT NULL,
        `delivery_time` TIME NOT NULL,
        `is_active` BOOLEAN NOT NULL DEFAULT TRUE,
        `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
        FOREIGN KEY (`location_id`) REFERENCES `favorite_locations`(`id`) ON DELETE CASCADE,
        UNIQUE KEY `user_location` (`user_id`, `location_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
    echo "Table 'weather_subscriptions' created successfully." . PHP_EOL;

    $pdo->exec("CREATE TABLE IF NOT EXISTS `weather_history` (
        `id` INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        `city_name` VARCHAR(255) NOT NULL,
        `temperature` FLOAT NOT NULL,
        `humidity` INT NOT NULL,
        `description` VARCHAR(255) NOT NULL,
        `icon` VARCHAR(50) NOT NULL,
        `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;");
    echo "Table 'weather_history' created successfully." . PHP_EOL;

} catch (PDOException $e) {
    die("DB ERROR: " . $e->getMessage());
}