<?php

require_once __DIR__ . '/../src/BaseMigration.php';

class CreateBaseTables extends \App\BaseMigration
{
    public function up(\PDO $pdo): void
    {
        // Создание таблицы owners (агенты)
        $ownersSql = "
            CREATE TABLE owners (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                contact VARCHAR(255),
                api_key VARCHAR(255) UNIQUE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                last_activity TIMESTAMP,
                status ENUM('active', 'suspended') DEFAULT 'active'
            )
        ";
        $pdo->exec($ownersSql);

        // Создание таблицы categories
        $categoriesSql = "
            CREATE TABLE categories (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) UNIQUE NOT NULL,
                description TEXT,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $pdo->exec($categoriesSql);

        // Создание таблицы tags
        $tagsSql = "
            CREATE TABLE tags (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) UNIQUE NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ";
        $pdo->exec($tagsSql);

        // Создание таблицы feeds
        $feedsSql = "
            CREATE TABLE feeds (
                id INT AUTO_INCREMENT PRIMARY KEY,
                url VARCHAR(255) NOT NULL,
                title VARCHAR(255),
                description TEXT,
                category_id INT,
                refresh_interval INT,
                owner_id INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
                FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
                FOREIGN KEY (owner_id) REFERENCES owners(id) ON DELETE CASCADE
            )
        ";
        $pdo->exec($feedsSql);

        // Создание таблицы feed_tags (many-to-many связь)
        $feedTagsSql = "
            CREATE TABLE feed_tags (
                feed_id INT NOT NULL,
                tag_id INT NOT NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                PRIMARY KEY (feed_id, tag_id),
                FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE,
                FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE
            )
        ";
        $pdo->exec($feedTagsSql);

        // Создание таблицы feed_stats (опционально)
        $feedStatsSql = "
            CREATE TABLE feed_stats (
                id INT AUTO_INCREMENT PRIMARY KEY,
                feed_id INT NOT NULL,
                access_count INT DEFAULT 0,
                last_access TIMESTAMP,
                FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE
            )
        ";
        $pdo->exec($feedStatsSql);

        // Создание индексов для производительности
        $pdo->exec("CREATE INDEX idx_feeds_status ON feeds(status)");
        $pdo->exec("CREATE INDEX idx_feeds_owner ON feeds(owner_id)");
        $pdo->exec("CREATE INDEX idx_feeds_category ON feeds(category_id)");
        $pdo->exec("CREATE INDEX idx_owners_api_key ON owners(api_key)");
    }

    public function down(\PDO $pdo): void
    {
        // Удаление таблиц в обратном порядке из-за внешних ключей
        $pdo->exec("DROP TABLE IF EXISTS feed_stats");
        $pdo->exec("DROP TABLE IF EXISTS feed_tags");
        $pdo->exec("DROP TABLE IF EXISTS feeds");
        $pdo->exec("DROP TABLE IF EXISTS tags");
        $pdo->exec("DROP TABLE IF EXISTS categories");
        $pdo->exec("DROP TABLE IF EXISTS owners");
    }
}