<?php
class MigrationService {
    public static function run(Database $db) {
        $driver = $db->getDriver();

        if ($driver === 'sqlite') {
            $db->query("CREATE TABLE IF NOT EXISTS home_users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(191) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                projects TEXT NULL,
                is_admin INTEGER NOT NULL DEFAULT 0,
                status VARCHAR(20) NOT NULL DEFAULT 'Active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");
            $db->execute();

            $db->query("CREATE TABLE IF NOT EXISTS home_invitations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                token VARCHAR(64) NOT NULL UNIQUE,
                recipient_email VARCHAR(191) NULL,
                assigned_projects TEXT NULL,
                max_uses INTEGER NOT NULL DEFAULT 1,
                uses_count INTEGER NOT NULL DEFAULT 0,
                expires_at DATETIME NULL,
                note VARCHAR(255) NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");
            $db->execute();

            $db->query("CREATE TABLE IF NOT EXISTS home_password_resets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                email VARCHAR(191) NOT NULL,
                token VARCHAR(64) NOT NULL UNIQUE,
                expires_at DATETIME NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            )");
            $db->execute();

            // SQLite column check for recipient_email
            $db->query("PRAGMA table_info(home_invitations)");
            $columns = $db->resultSet();
            $hasRecipientEmail = false;
            foreach ($columns as $col) {
                if (($col['name'] ?? '') === 'recipient_email') {
                    $hasRecipientEmail = true;
                    break;
                }
            }
            if (!$hasRecipientEmail) {
                $db->query("ALTER TABLE home_invitations ADD COLUMN recipient_email VARCHAR(191) NULL");
                $db->execute();
            }
        } else {
            $db->query("CREATE TABLE IF NOT EXISTS home_users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                email VARCHAR(191) NOT NULL UNIQUE,
                password VARCHAR(255) NOT NULL,
                projects TEXT NULL,
                is_admin TINYINT(1) NOT NULL DEFAULT 0,
                status VARCHAR(20) NOT NULL DEFAULT 'Active',
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            $db->execute();

            $db->query("CREATE TABLE IF NOT EXISTS home_invitations (
                id INT AUTO_INCREMENT PRIMARY KEY,
                token VARCHAR(64) NOT NULL UNIQUE,
                recipient_email VARCHAR(191) NULL,
                assigned_projects TEXT NULL,
                max_uses INT NOT NULL DEFAULT 1,
                uses_count INT NOT NULL DEFAULT 0,
                expires_at DATETIME NULL,
                note VARCHAR(255) NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            $db->execute();

            $db->query("CREATE TABLE IF NOT EXISTS home_password_resets (
                id INT AUTO_INCREMENT PRIMARY KEY,
                email VARCHAR(191) NOT NULL,
                token VARCHAR(64) NOT NULL UNIQUE,
                expires_at DATETIME NOT NULL,
                created_at DATETIME DEFAULT CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci");
            $db->execute();

            // MySQL column check for recipient_email
            $db->query("SHOW COLUMNS FROM home_invitations LIKE 'recipient_email'");
            $colExists = $db->single();
            if (!$colExists) {
                $db->query("ALTER TABLE home_invitations ADD COLUMN recipient_email VARCHAR(191) NULL AFTER token");
                $db->execute();
            }
        }

        // Ensure owner email addresses have admin privileges
        $db->query("UPDATE home_users SET is_admin = 1, status = 'Active' WHERE LOWER(email) IN ('philipp.urbschat@gmail.com', 'hi@philippurbschat.de')");
        $db->execute();
    }
}