/**
 * Класс для управления миграциями базы данных
 * Файл: database_migrations.php
 */

require_once 'database_config.php';

class DatabaseMigrations {
    private $db;
    private $migration_table = 'migrations_history';

    public function __construct($test_mode = false) {
        $this->db = Database::getInstance($test_mode)->getConnection();
        $this->createMigrationTable();
    }

    /**
     * Создать таблицу для отслеживания миграций
     */
    private function createMigrationTable() {
        $sql = "CREATE TABLE IF NOT EXISTS {$this->migration_table} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            migration_name VARCHAR(255) NOT NULL,
            executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE KEY unique_migration (migration_name)
        ) ENGINE=InnoDB";

        $this->db->exec($sql);
    }

    /**
     * Выполнить все непримененные миграции
     */
    public function migrate() {
        $migrations = $this->getMigrationsList();
        $executed_migrations = $this->getExecutedMigrations();

        foreach ($migrations as $migration) {
            if (!in_array($migration, $executed_migrations)) {
                $this->executeMigration($migration);
            }
        }
    }

    /**
     * Откатить последнюю миграцию
     */
    public function rollback() {
        $last_migration = $this->getLastExecutedMigration();
        if ($last_migration) {
            $this->rollbackMigration($last_migration);
        } else {
            echo "Нет миграций для отката\n";
        }
    }

    /**
     * Получить список доступных миграций
     */
    private function getMigrationsList() {
        return [
            '2024_01_15_create_weather_stations_table',
            '2024_01_20_create_weather_data_table',
            '2024_01_25_create_users_table',
            '2024_02_01_add_weather_forecasts_table',
            '2024_02_10_add_weather_alerts_table',
        ];
    }

    /**
     * Получить выполненные миграции
     */
    private function getExecutedMigrations() {
        $stmt = $this->db->query("SELECT migration_name FROM {$this->migration_table} ORDER BY executed_at");
        return $stmt->fetchAll(PDO::FETCH_COLUMN);
    }

    /**
     * Получить последнюю выполненную миграцию
     */
    private function getLastExecutedMigration() {
        $stmt = $this->db->query("SELECT migration_name FROM {$this->migration_table} ORDER BY executed_at DESC LIMIT 1");
        return $stmt->fetchColumn();
    }

    /**
     * Выполнить конкретную миграцию
     */
    private function executeMigration($migration_name) {
        try {
            $this->db->beginTransaction();

            // Выполняем SQL для конкретной миграции
            $sql = $this->getMigrationSQL($migration_name);
            $this->db->exec($sql);

            // Записываем в историю миграций
            $stmt = $this->db->prepare("INSERT INTO {$this->migration_table} (migration_name) VALUES (?)");
            $stmt->execute([$migration_name]);

            $this->db->commit();
            echo "✅ Миграция {$migration_name} выполнена успешно\n";

        } catch (Exception $e) {
            $this->db->rollback();
            echo "❌ Ошибка выполнения миграции {$migration_name}: " . $e->getMessage() . "\n";
        }
    }

    /**
     * Откатить миграцию
     */
    private function rollbackMigration($migration_name) {
        try {
            $this->db->beginTransaction();

            // Выполняем SQL для отката миграции
            $sql = $this->getRollbackSQL($migration_name);
            if ($sql) {
                $this->db->exec($sql);
            }

            // Удаляем из истории миграций
            $stmt = $this->db->prepare("DELETE FROM {$this->migration_table} WHERE migration_name = ?");
            $stmt->execute([$migration_name]);

            $this->db->commit();
            echo "✅ Миграция {$migration_name} откачена успешно\n";

        } catch (Exception $e) {
            $this->db->rollback();
            echo "❌ Ошибка отката миграции {$migration_name}: " . $e->getMessage() . "\n";
        }
    }

    /**
     * Получить SQL для выполнения миграции
     */
    private function getMigrationSQL($migration_name) {
        $migrations_sql = [
            '2024_01_15_create_weather_stations_table' => "
                CREATE TABLE weather_stations (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    name VARCHAR(255) NOT NULL,
                    latitude DECIMAL(10, 8) NOT NULL,
                    longitude DECIMAL(11, 8) NOT NULL,
                    altitude INT DEFAULT 0,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB",

            '2024_01_20_create_weather_data_table' => "
                CREATE TABLE weather_data (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    station_id INT NOT NULL,
                    temperature DECIMAL(5, 2),
                    humidity INT,
                    pressure DECIMAL(7, 2),
                    wind_speed DECIMAL(5, 2),
                    wind_direction INT,
                    recorded_at TIMESTAMP NOT NULL,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (station_id) REFERENCES weather_stations(id) ON DELETE CASCADE,
                    INDEX idx_station_recorded (station_id, recorded_at)
                ) ENGINE=InnoDB",

            '2024_01_25_create_users_table' => "
                CREATE TABLE users (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    username VARCHAR(50) UNIQUE NOT NULL,
                    email VARCHAR(100) UNIQUE NOT NULL,
                    password_hash VARCHAR(255) NOT NULL,
                    role ENUM('admin', 'meteorologist', 'viewer') DEFAULT 'viewer',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                ) ENGINE=InnoDB",

            '2024_02_01_add_weather_forecasts_table' => "
                CREATE TABLE weather_forecasts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    station_id INT NOT NULL,
                    forecast_date DATE NOT NULL,
                    min_temperature DECIMAL(5, 2),
                    max_temperature DECIMAL(5, 2),
                    humidity INT,
                    precipitation_chance INT,
                    description TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (station_id) REFERENCES weather_stations(id) ON DELETE CASCADE,
                    UNIQUE KEY unique_forecast (station_id, forecast_date)
                ) ENGINE=InnoDB",

            '2024_02_10_add_weather_alerts_table' => "
                CREATE TABLE weather_alerts (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    station_id INT NOT NULL,
                    alert_type ENUM('warning', 'watch', 'advisory') NOT NULL,
                    severity ENUM('minor', 'moderate', 'severe', 'extreme') NOT NULL,
                    title VARCHAR(255) NOT NULL,
                    description TEXT NOT NULL,
                    start_time TIMESTAMP NOT NULL,
                    end_time TIMESTAMP,
                    is_active BOOLEAN DEFAULT TRUE,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (station_id) REFERENCES weather_stations(id) ON DELETE CASCADE
                ) ENGINE=InnoDB"
        ];

        return $migrations_sql[$migration_name] ?? '';
    }

    /**
     * Получить SQL для отката миграции
     */
    private function getRollbackSQL($migration_name) {
        $rollback_sql = [
            '2024_01_15_create_weather_stations_table' => "DROP TABLE IF EXISTS weather_stations",
            '2024_01_20_create_weather_data_table' => "DROP TABLE IF EXISTS weather_data",
            '2024_01_25_create_users_table' => "DROP TABLE IF EXISTS users",
            '2024_02_01_add_weather_forecasts_table' => "DROP TABLE IF EXISTS weather_forecasts",
            '2024_02_10_add_weather_alerts_table' => "DROP TABLE IF EXISTS weather_alerts",
        ];

        return $rollback_sql[$migration_name] ?? '';
    }

    /**
     * Показать статус миграций
     */
    public function status() {
        $all_migrations = $this->getMigrationsList();
        $executed_migrations = $this->getExecutedMigrations();

        echo "\n=== Статус миграций ===\n";
        foreach ($all_migrations as $migration) {
            $status = in_array($migration, $executed_migrations) ? '✅ Выполнена' : '❌ Не выполнена';
            echo "{$migration}: {$status}\n";
        }
        echo "\n";
    }
}

// Пример использования
if (basename(__FILE__) == basename($_SERVER['PHP_SELF'])) {
    $migrations = new DatabaseMigrations();

    if (isset($argv[1])) {
        switch ($argv[1]) {
            case 'migrate':
                $migrations->migrate();
                break;
            case 'rollback':
                $migrations->rollback();
                break;
            case 'status':
                $migrations->status();
                break;
            default:
                echo "Использование: php migrations.php [migrate|rollback|status]\n";
        }
    } else {
        echo "Использование: php migrations.php [migrate|rollback|status]\n";
    }
}