<?php

declare(strict_types=1);

use Wibiesana\Padi\Core\DatabaseManager;

return new class
{
    public function up(): void
    {
        $db = DatabaseManager::connection();
        $driver = DatabaseManager::getDriver();

        if ($driver === 'sqlite') {
            $sql = "CREATE TABLE IF NOT EXISTS tags (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(100) NOT NULL UNIQUE,
                slug VARCHAR(100) NOT NULL UNIQUE,
                description TEXT,
                created_by INTEGER,
                updated_by INTEGER,
                created_at INTEGER DEFAULT (strftime('%s', 'now')),
                updated_at INTEGER DEFAULT (strftime('%s', 'now')),
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
                FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
            )";

            $db->exec($sql);
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_slug ON tags(slug)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_created_by ON tags(created_by)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_updated_by ON tags(updated_by)");
        } elseif ($driver === 'pgsql') {
            $sql = "CREATE TABLE IF NOT EXISTS tags (
                id SERIAL PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                slug VARCHAR(100) NOT NULL UNIQUE,
                description TEXT,
                created_by INTEGER,
                updated_by INTEGER,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
                FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
            )";

            $db->exec($sql);
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_slug ON tags(slug)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_created_by ON tags(created_by)");
            $db->exec("CREATE INDEX IF NOT EXISTS idx_tags_updated_by ON tags(updated_by)");

            $db->exec("
                DROP TRIGGER IF EXISTS update_tags_updated_at ON tags;
                CREATE TRIGGER update_tags_updated_at 
                    BEFORE UPDATE ON tags 
                    FOR EACH ROW 
                    EXECUTE FUNCTION update_updated_at_column();
            ");
        } else {
            // MySQL/MariaDB
            $sql = "CREATE TABLE IF NOT EXISTS tags (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL UNIQUE,
                slug VARCHAR(100) NOT NULL UNIQUE,
                description TEXT,
                created_by INT UNSIGNED NULL,
                updated_by INT UNSIGNED NULL,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_tags_slug (slug),
                INDEX idx_tags_created_by (created_by),
                INDEX idx_tags_updated_by (updated_by),
                FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
                FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci";

            $db->exec($sql);
        }

        echo "✓ Tags table created\n";
    }

    public function down(): void
    {
        $db = DatabaseManager::connection();
        $driver = DatabaseManager::getDriver();

        if ($driver === 'pgsql') {
            $db->exec("DROP TRIGGER IF EXISTS update_tags_updated_at ON tags");
        }

        $db->exec("DROP TABLE IF EXISTS tags");
        echo "✓ Tags table dropped\n";
    }
};
