<?php

declare(strict_types=1);

namespace PpPhp\Core\Deploy;

use PpPhp\Core\DB;

/**
 * Schema owner for the deployment subsystem.
 *
 * Four tables live in the core (platform) database:
 *   - deploy_connections   saved FTP/FTPS/SFTP server profiles (encrypted creds)
 *   - deploy_builds        a record of every build, with a path→hash manifest
 *   - deploy_uploads       one row per upload run (summary + status)
 *   - deploy_upload_files  per-file detail for each upload
 *
 * schemaSql() is registered by the installer for fresh installs; ensureSchema()
 * creates the tables lazily on existing installs (idempotent, IF NOT EXISTS).
 */
class DeploySchema
{
    private static bool $ready = false;

    /** Create the tables on demand (used by upgraded installs). */
    public static function ensureSchema(): void
    {
        if (self::$ready) {
            return;
        }
        DB::Init();
        DB::SetConnection();
        foreach (self::statements() as $sql) {
            DB::Exec($sql);
            if (DB::LastError() !== '') {
                throw new \RuntimeException('Could not prepare deployment tables: ' . DB::LastError());
            }
        }
        self::$ready = true;
    }

    /** Single string for the installer's schema inventory. */
    public static function schemaSql(): string
    {
        return implode(";\n\n", self::statements());
    }

    /** @return list<string> */
    public static function statements(): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS `deploy_connections` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `project_id` INT NOT NULL,
                `name` VARCHAR(150) NOT NULL,
                `protocol` ENUM('ftp','ftps','sftp') NOT NULL DEFAULT 'ftp',
                `host` VARCHAR(255) NOT NULL DEFAULT '',
                `port` INT NOT NULL DEFAULT 21,
                `username` VARCHAR(255) NOT NULL DEFAULT '',
                `password_enc` TEXT,
                `remote_path` VARCHAR(500) NOT NULL DEFAULT '',
                `site_url` VARCHAR(500) NOT NULL DEFAULT '',
                `passive` TINYINT(1) NOT NULL DEFAULT 1,
                `private_key_file` VARCHAR(500) NOT NULL DEFAULT '',
                `private_key_pass_enc` TEXT,
                `last_used_at` DATETIME DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX `idx_deploy_conn_project` (`project_id`),
                CONSTRAINT `fk_deploy_conn_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `deploy_db_connections` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `project_id` INT NOT NULL,
                `name` VARCHAR(150) NOT NULL,
                `type` ENUM('mysql','pgsql','sqlite','sqlsrv') NOT NULL DEFAULT 'mysql',
                `host` VARCHAR(255) NOT NULL DEFAULT '127.0.0.1',
                `port` VARCHAR(10) NOT NULL DEFAULT '3306',
                `dbname` VARCHAR(190) NOT NULL DEFAULT '',
                `username` VARCHAR(190) NOT NULL DEFAULT '',
                `password_enc` TEXT,
                `charset` VARCHAR(30) NOT NULL DEFAULT 'utf8mb4',
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX `idx_deploy_db_project` (`project_id`),
                CONSTRAINT `fk_deploy_db_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `deploy_builds` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `project_id` INT NOT NULL,
                `files` INT NOT NULL DEFAULT 0,
                `size_bytes` BIGINT NOT NULL DEFAULT 0,
                `duration` DECIMAL(8,2) NOT NULL DEFAULT 0,
                `output_dir` VARCHAR(500) NOT NULL DEFAULT '',
                `manifest` LONGTEXT,
                `created_by` INT DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_deploy_build_project` (`project_id`),
                INDEX `idx_deploy_build_created` (`created_at`),
                CONSTRAINT `fk_deploy_build_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `deploy_uploads` (
                `id` INT AUTO_INCREMENT PRIMARY KEY,
                `project_id` INT NOT NULL,
                `connection_id` INT DEFAULT NULL,
                `connection_name` VARCHAR(150) NOT NULL DEFAULT '',
                `protocol` VARCHAR(10) NOT NULL DEFAULT 'ftp',
                `host` VARCHAR(255) NOT NULL DEFAULT '',
                `remote_path` VARCHAR(500) NOT NULL DEFAULT '',
                `mode` VARCHAR(20) NOT NULL DEFAULT 'changed',
                `status` ENUM('success','partial','failed') NOT NULL DEFAULT 'success',
                `files_uploaded` INT NOT NULL DEFAULT 0,
                `files_failed` INT NOT NULL DEFAULT 0,
                `files_skipped` INT NOT NULL DEFAULT 0,
                `bytes` BIGINT NOT NULL DEFAULT 0,
                `duration` DECIMAL(8,2) NOT NULL DEFAULT 0,
                `db_connection` VARCHAR(150) NOT NULL DEFAULT '',
                `message` VARCHAR(500) NOT NULL DEFAULT '',
                `created_by` INT DEFAULT NULL,
                `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                INDEX `idx_deploy_upload_project` (`project_id`),
                INDEX `idx_deploy_upload_created` (`created_at`),
                CONSTRAINT `fk_deploy_upload_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE CASCADE,
                CONSTRAINT `fk_deploy_upload_conn` FOREIGN KEY (`connection_id`) REFERENCES `deploy_connections` (`id`) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",

            "CREATE TABLE IF NOT EXISTS `deploy_upload_files` (
                `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
                `upload_id` INT NOT NULL,
                `path` VARCHAR(1000) NOT NULL,
                `size` INT NOT NULL DEFAULT 0,
                `status` ENUM('uploaded','failed','skipped') NOT NULL DEFAULT 'uploaded',
                INDEX `idx_deploy_upfiles_upload` (`upload_id`),
                CONSTRAINT `fk_deploy_upfiles_upload` FOREIGN KEY (`upload_id`) REFERENCES `deploy_uploads` (`id`) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4",
        ];
    }
}
