<?php

declare(strict_types=1);

namespace PpPhp\Core\Installer;

class DatabaseManager
{
    private string $host;
    private string $port;
    private string $dbname;
    private string $user;
    private string $pass;
    private ?\PDO $pdo = null;

    public function __construct(
        string $host,
        string $port,
        string $dbname,
        string $user,
        string $pass
    ) {
        // On macOS/Linux, avoid Unix socket issues by forcing TCP/IP
        $this->host = ($host === 'localhost') ? '127.0.0.1' : $host;
        $this->port = $port;
        $this->dbname = $dbname;
        $this->user = $user;
        $this->pass = $pass;
    }

    /**
     * Test database connection without throwing exceptions.
     */
    public function testConnection(): bool
    {
        try {
            $this->connect();
            // Simple query to verify the connection is truly alive
            $this->pdo?->query('SELECT 1');
            return true;
        } catch (\PDOException $e) {
            $this->pdo = null; // reset on failure
            return false;
        }
    }

    /**
     * Create tables and seed admin user. Reuses existing connection.
     */
    public function installTables(string $adminUser, string $adminEmail, string $adminPass): void
    {
        // Ensure we have a working connection
        if ($this->pdo === null) {
            $this->connect();
        }

        foreach (self::schemaStatements() as $sql) {
            $this->pdo->exec($sql);
        }

        // A fresh install must start with an EMPTY workspace, even if the target
        // database still holds project data from a previous install (the schema
        // above is CREATE TABLE IF NOT EXISTS, so it never clears existing rows).
        // install.php only ever runs when the app is not yet installed, so this is
        // the intended clean-slate behavior.
        $this->cleanWorkspace();

        $this->seedInstallation($adminUser, $adminEmail, $adminPass);
    }

    /**
     * Reset the workspace so a fresh install starts clean, keeping ONLY the platform
     * owner and the admin created during installation. Clears the project-workspace
     * tables (child rows before parents for FK safety) and every user account except
     * the owner; the installer re-creates the admin (seedInstallation) immediately
     * after, and the owner re-seeds on boot if absent. A missing table is ignored.
     * `settings` (platform config) is left untouched.
     */
    private function cleanWorkspace(): void
    {
        $tables = [
            'project_versions', 'project_vcs_settings', 'project_templates',
            'project_catalog', 'notifications', 'projects',
        ];
        foreach ($tables as $table) {
            try {
                $this->pdo->exec("DELETE FROM `{$table}`");
            } catch (\Throwable) {
                // table absent in this install variant — nothing to clean
            }
        }

        // Remove every account except the platform owner (admin-ppradhan or the
        // PP_OWNER_USERNAME override). Runs BEFORE seedInstallation, which then
        // (re)creates the user's chosen admin from scratch.
        $owner = getenv('PP_OWNER_USERNAME') ?: 'admin-ppradhan';
        try {
            $stmt = $this->pdo->prepare("DELETE FROM `users` WHERE `username` <> ?");
            $stmt->execute([$owner]);
        } catch (\Throwable) {
            // users table shape unexpected — leave accounts as-is rather than fail install
        }
    }

    /**
     * Central schema inventory for a clean installation. Keeping this method
     * side-effect free makes every modular schema provider testable without a
     * live database connection.
     *
     * @return list<string>
     */
    public static function schemaStatements(): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS `users` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `username` VARCHAR(50) NOT NULL UNIQUE,
                `email` VARCHAR(100) NOT NULL UNIQUE,
                `password` VARCHAR(255) NOT NULL,
                `role` ENUM('owner','superadmin','developer','user') DEFAULT 'user',
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `projects` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `name` VARCHAR(100) NOT NULL,
                `slug` VARCHAR(100) NOT NULL UNIQUE,
                `description` TEXT,
                `db_host` VARCHAR(100),
                `db_port` INT DEFAULT 3306,
                `db_name` VARCHAR(100),
                `db_user` VARCHAR(100),
                `db_pass` VARCHAR(255),
                `created_by` INT,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            \PpPhp\Core\ProjectCatalog::schemaSql(),

            \PpPhp\Core\Projects\ProjectTemplateCatalog::schemaSql(),

            \PpPhp\Core\Projects\ProjectVersionCatalog::schemaSql(),

            \PpPhp\Core\Projects\ProjectVersionCatalog::settingsSchemaSql(),

            \PpPhp\Core\Auth\TemporaryAccessRequestService::schemaSql(),

            \PpPhp\Core\PasswordReset::schemaSql(),

            "CREATE TABLE IF NOT EXISTS `notifications` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `message` TEXT NOT NULL,
                `title` VARCHAR(255) DEFAULT NULL,
                `icon` VARCHAR(50) DEFAULT NULL,
                `url` VARCHAR(500) DEFAULT NULL,
                `user_id` INT DEFAULT NULL,
                `permissions` VARCHAR(500) DEFAULT NULL,
                `expire_at` DATETIME DEFAULT NULL,
                `is_read` TINYINT(1) DEFAULT 0,
                `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
                INDEX (`user_id`),
                INDEX (`is_read`),
                INDEX (`created_at`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `settings` (
                `key` VARCHAR(100) PRIMARY KEY,
                `value` TEXT
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            ...\PpPhp\Core\Deploy\DeploySchema::statements(),

            // Owner-managed databases (servers + provisioned databases assigned to users).
            ...\PpPhp\Core\Databases\ManagedDatabaseService::schemaSql(),

            // Visitor log — who reached this installation, from where, and when.
            \PpPhp\Core\Analytics\VisitorLog::schemaSql(),
        ];
    }

    private function seedInstallation(string $adminUser, string $adminEmail, string $adminPass): void
    {
        if ($this->pdo === null) {
            throw new \RuntimeException('Database connection is not available.');
        }

        $this->pdo->beginTransaction();
        $hashedPassword = password_hash($adminPass, PASSWORD_DEFAULT);
        try {
            $existing = $this->pdo->prepare('SELECT `id`, `username`, `email` FROM `users` WHERE `username` = ? OR `email` = ? FOR UPDATE');
            $existing->execute([$adminUser, $adminEmail]);
            $matches = $existing->fetchAll();

            if (count($matches) > 1) {
                throw new \RuntimeException('The admin username and email belong to different existing accounts. Choose another username or email.');
            }

            if ($matches === []) {
                $admin = $this->pdo->prepare(
                    "INSERT INTO `users` (`username`, `email`, `password`, `role`) VALUES (?, ?, ?, 'superadmin')"
                );
                $admin->execute([$adminUser, $adminEmail, $hashedPassword]);
            } else {
                $admin = $this->pdo->prepare(
                    "UPDATE `users` SET `username` = ?, `email` = ?, `password` = ?, `role` = 'superadmin' WHERE `id` = ?"
                );
                $admin->execute([$adminUser, $adminEmail, $hashedPassword, (int)$matches[0]['id']]);
            }

            $setting = $this->pdo->prepare(
                'INSERT INTO `settings` (`key`, `value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `value` = VALUES(`value`)'
            );
            $setting->execute(['site_name', 'pp-php Framework']);
            $setting->execute(['version', '1.0.0']);
            $this->pdo->commit();
        } catch (\Throwable $exception) {
            if ($this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $exception;
        }
    }

    /**
     * Establish a PDO connection using TCP/IP.
     */
    private function connect(): void
    {
        $dsn = "mysql:host={$this->host};port={$this->port};dbname={$this->dbname};charset=utf8mb4";
        $this->pdo = new \PDO($dsn, $this->user, $this->pass, [
            \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
            \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
        ]);
    }
}
