<?php

declare(strict_types=1);

namespace FecaMailshots\Infrastructure;

use PDO;

final class MailshotSchemaInstaller
{
    private DatabaseRouter $router;

    public function __construct(DatabaseRouter $router)
    {
        $this->router = $router;
    }

    public function install(): void
    {
        $pdo = $this->router->mailshotsPdo();
        foreach ($this->createTableSql() as $sql) {
            $pdo->exec($sql);
        }

        $this->ensureMailshotsColumns($pdo);
        $this->ensureMailshotQueriesColumns($pdo);
    }

    /** @return list<string> */
    private function createTableSql(): array
    {
        return [
            "CREATE TABLE IF NOT EXISTS mailshots (
                id INT(11) NOT NULL AUTO_INCREMENT,
                Purpose VARCHAR(100) DEFAULT NULL,
                DataSource VARCHAR(100) NOT NULL,
                CC VARCHAR(100) DEFAULT NULL,
                BCC VARCHAR(100) DEFAULT NULL,
                Subject LONGTEXT NOT NULL,
                Message LONGTEXT NOT NULL,
                PDFAttachment LONGTEXT DEFAULT NULL,
                AttachmentNames LONGTEXT DEFAULT NULL,
                PDFFilenameDerivedFrom VARCHAR(255) DEFAULT NULL,
                ReplyTo VARCHAR(100) DEFAULT NULL,
                RecipientEmailField VARCHAR(255) DEFAULT NULL,
                PRIMARY KEY (id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
            "CREATE TABLE IF NOT EXISTS mailshot_queries (
                ID INT(11) NOT NULL AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                dsl_text LONGTEXT DEFAULT NULL,
                dsl_version VARCHAR(16) NOT NULL DEFAULT 'v1',
                `sql` LONGTEXT NOT NULL,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (ID),
                UNIQUE KEY mailshot_queries_name_uk (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
            "CREATE TABLE IF NOT EXISTS mailshot_attachments (
                id INT(11) NOT NULL AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                mime_type VARCHAR(255) NOT NULL,
                file_bytes LONGBLOB NOT NULL,
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY mailshot_attachments_name_uk (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
            "CREATE TABLE IF NOT EXISTS mailshot_pdf_assets (
                id INT(11) NOT NULL AUTO_INCREMENT,
                name VARCHAR(100) NOT NULL,
                file_name VARCHAR(255) NOT NULL,
                mime_type VARCHAR(100) NOT NULL,
                file_bytes LONGBLOB NOT NULL,
                width_mm DECIMAL(8,2) NOT NULL,
                height_mm DECIMAL(8,2) NOT NULL,
                justification ENUM('left','right','in-place') NOT NULL DEFAULT 'in-place',
                created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                UNIQUE KEY mailshot_pdf_assets_name_uk (name)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
            "CREATE TABLE IF NOT EXISTS mailshot_last_run (
                id INT(11) NOT NULL AUTO_INCREMENT,
                mailshot_id INT(11) NOT NULL,
                data_source VARCHAR(100) DEFAULT NULL,
                row_index INT(11) NOT NULL DEFAULT 0,
                recipient_key VARCHAR(255) DEFAULT NULL,
                recipient_key_field VARCHAR(64) DEFAULT NULL,
                recipient_email_last VARCHAR(255) DEFAULT NULL,
                status VARCHAR(32) NOT NULL,
                error_message LONGTEXT DEFAULT NULL,
                warning_message LONGTEXT DEFAULT NULL,
                attempt_count INT(11) NOT NULL DEFAULT 1,
                run_started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                last_attempt_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (id),
                KEY mailshot_last_run_mailshot_idx (mailshot_id),
                KEY mailshot_last_run_status_idx (status),
                KEY mailshot_last_run_recipient_idx (recipient_key)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
            "CREATE TABLE IF NOT EXISTS mailshot_credentials (
                wp_user_id BIGINT UNSIGNED NOT NULL,
                smtp_host VARCHAR(255) NOT NULL,
                smtp_port INT NOT NULL,
                smtp_user VARCHAR(255) NOT NULL,
                smtp_password_enc LONGTEXT NOT NULL,
                smtp_from_email VARCHAR(255) NOT NULL,
                smtp_from_name VARCHAR(255) DEFAULT NULL,
                smtp_require_tls TINYINT(1) NOT NULL DEFAULT 0,
                imap_host VARCHAR(255) DEFAULT NULL,
                imap_port INT DEFAULT 993,
                imap_user VARCHAR(255) DEFAULT NULL,
                imap_password_enc LONGTEXT DEFAULT NULL,
                imap_sent_folder VARCHAR(255) DEFAULT 'Sent',
                imap_mailbox_flags VARCHAR(64) DEFAULT '/imap/ssl',
                updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                PRIMARY KEY (wp_user_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci",
        ];
    }

    private function ensureMailshotsColumns(PDO $pdo): void
    {
        $this->ensureColumn($pdo, 'mailshots', 'AttachmentNames', 'ALTER TABLE mailshots ADD COLUMN AttachmentNames LONGTEXT DEFAULT NULL AFTER PDFAttachment');
        $this->ensureColumn($pdo, 'mailshots', 'PDFFilenameDerivedFrom', 'ALTER TABLE mailshots ADD COLUMN PDFFilenameDerivedFrom VARCHAR(255) DEFAULT NULL AFTER AttachmentNames');
        $this->ensureColumn($pdo, 'mailshots', 'ReplyTo', 'ALTER TABLE mailshots ADD COLUMN ReplyTo VARCHAR(100) DEFAULT NULL AFTER PDFFilenameDerivedFrom');
        $this->ensureColumn($pdo, 'mailshots', 'RecipientEmailField', 'ALTER TABLE mailshots ADD COLUMN RecipientEmailField VARCHAR(255) DEFAULT NULL AFTER ReplyTo');
    }

    private function ensureMailshotQueriesColumns(PDO $pdo): void
    {
        $this->ensureColumn($pdo, 'mailshot_queries', 'dsl_version', "ALTER TABLE mailshot_queries ADD COLUMN dsl_version VARCHAR(16) NOT NULL DEFAULT 'v1' AFTER dsl_text");
        $this->ensureColumn($pdo, 'mailshot_queries', 'updated_at', 'ALTER TABLE mailshot_queries ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER `sql`');
    }

    private function ensureColumn(PDO $pdo, string $table, string $column, string $alterSql): void
    {
        if ($this->columnExists($pdo, $table, $column)) {
            return;
        }
        $pdo->exec($alterSql);
    }

    private function columnExists(PDO $pdo, string $table, string $column): bool
    {
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM information_schema.columns WHERE table_schema = :schema AND table_name = :table AND column_name = :column');
        $stmt->execute([
            'schema' => $this->router->mailshotsDbName(),
            'table' => $table,
            'column' => $column,
        ]);
        return (int) $stmt->fetchColumn() > 0;
    }
}
