from __future__ import annotations

import argparse
import os
import pathlib
import subprocess
import sys


ROOT = pathlib.Path(__file__).resolve().parent
SCHEMA_PATH = ROOT / "official_mail_schema.sql"
MIGRATION_SQL = """
SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'domains'
      AND COLUMN_NAME = 'connection_completed_email_sent_at'
  ),
  'SELECT 1',
  'ALTER TABLE domains ADD COLUMN connection_completed_email_sent_at DATETIME NULL AFTER verified_at'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'users'
      AND COLUMN_NAME = 'preferred_locale'
  ),
  'SELECT 1',
  'ALTER TABLE users ADD COLUMN preferred_locale VARCHAR(8) NULL AFTER mail_sort_order'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'mailbox_messages'
      AND COLUMN_NAME = 'message_id_header'
  ),
  'SELECT 1',
  'ALTER TABLE mailbox_messages ADD COLUMN message_id_header VARCHAR(1024) NULL AFTER body_text'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'mailbox_messages'
      AND COLUMN_NAME = 'raw_source'
  ),
  'SELECT 1',
  'ALTER TABLE mailbox_messages ADD COLUMN raw_source LONGTEXT NULL AFTER message_id_header'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'mailbox_messages'
      AND COLUMN_NAME = 'transport_status'
  ),
  'SELECT 1',
  "ALTER TABLE mailbox_messages ADD COLUMN transport_status ENUM('saved', 'queued', 'sent', 'failed') NOT NULL DEFAULT 'saved' AFTER raw_source"
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'mailbox_messages'
      AND COLUMN_NAME = 'transport_response'
  ),
  'SELECT 1',
  'ALTER TABLE mailbox_messages ADD COLUMN transport_response LONGTEXT NULL AFTER transport_status'
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @stmt = IF (
  EXISTS(
    SELECT 1
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'mailbox_delivery_logs'
  ),
  'SELECT 1',
  "CREATE TABLE mailbox_delivery_logs (id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, mailbox_message_id BIGINT UNSIGNED NOT NULL, mailbox_id BIGINT UNSIGNED NOT NULL, action ENUM('send', 'draft', 'receive') NOT NULL, status ENUM('saved', 'queued', 'sent', 'failed') NOT NULL DEFAULT 'saved', transport VARCHAR(64) NOT NULL DEFAULT 'app', source_address VARCHAR(320) NOT NULL, target_address TEXT NOT NULL, subject TEXT NOT NULL, message_id_header VARCHAR(1024) NULL, response_text LONGTEXT NULL, created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_mailbox_delivery_logs_message_id (mailbox_message_id), KEY idx_mailbox_delivery_logs_mailbox_id (mailbox_id), CONSTRAINT fk_mailbox_delivery_logs_message FOREIGN KEY (mailbox_message_id) REFERENCES mailbox_messages(id) ON DELETE CASCADE, CONSTRAINT fk_mailbox_delivery_logs_mailbox FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci"
);
PREPARE stmt FROM @stmt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

ALTER TABLE users
  MODIFY COLUMN email VARCHAR(320) NOT NULL,
  MODIFY COLUMN recovery_email VARCHAR(320) NULL;
ALTER TABLE signup_recovery_email_verifications
  MODIFY COLUMN email VARCHAR(320) NOT NULL;
ALTER TABLE password_reset_tokens
  MODIFY COLUMN recovery_email VARCHAR(320) NOT NULL;
ALTER TABLE domains
  MODIFY COLUMN domain VARCHAR(253) NOT NULL;
ALTER TABLE mailboxes
  MODIFY COLUMN email VARCHAR(320) NOT NULL;
ALTER TABLE managed_team_mailboxes
  MODIFY COLUMN email VARCHAR(320) NOT NULL;
ALTER TABLE mailbox_ai_assist_settings
  MODIFY COLUMN notification_email VARCHAR(320) NULL,
  MODIFY COLUMN assistant_mailbox_email VARCHAR(320) NOT NULL;
ALTER TABLE mailbox_messages
  MODIFY COLUMN subject TEXT NOT NULL,
  MODIFY COLUMN from_name TEXT NULL,
  MODIFY COLUMN from_address VARCHAR(320) NOT NULL,
  MODIFY COLUMN message_id_header VARCHAR(1024) NULL;
ALTER TABLE mailbox_ai_assist_threads
  MODIFY COLUMN notification_email VARCHAR(320) NOT NULL,
  MODIFY COLUMN original_sender_email VARCHAR(320) NOT NULL,
  MODIFY COLUMN original_sender_name TEXT NULL,
  MODIFY COLUMN original_subject TEXT NOT NULL,
  MODIFY COLUMN summary_message_id_header VARCHAR(1024) NULL;
ALTER TABLE mailbox_delivery_logs
  MODIFY COLUMN source_address VARCHAR(320) NOT NULL,
  MODIFY COLUMN target_address TEXT NOT NULL,
  MODIFY COLUMN subject TEXT NOT NULL,
  MODIFY COLUMN message_id_header VARCHAR(1024) NULL;
ALTER TABLE mailbox_ai_assist_replies
  MODIFY COLUMN remote_message_key VARCHAR(600) NOT NULL,
  MODIFY COLUMN message_id_header VARCHAR(1024) NULL;
ALTER TABLE marketing_blog_posts
  MODIFY COLUMN created_by_email VARCHAR(320) NULL;
"""


def get_value(cli_value: str | None, env_name: str, default: str | None = None) -> str:
    value = cli_value or os.getenv(env_name) or default
    if not value:
        raise SystemExit(f"{env_name} is required")
    return value


def run_mysql(command: list[str], sql: str | None = None) -> None:
    process = subprocess.run(
        command,
        input=sql,
        text=True,
        capture_output=True,
        check=False,
    )
    if process.returncode != 0:
        sys.stderr.write(process.stderr or process.stdout)
        raise SystemExit(process.returncode)
    if process.stdout.strip():
        print(process.stdout.strip())


def main() -> None:
    parser = argparse.ArgumentParser(description="Create official_mail database and schema.")
    parser.add_argument("--host")
    parser.add_argument("--port")
    parser.add_argument("--user")
    parser.add_argument("--password")
    parser.add_argument("--database", default="official_mail")
    parser.add_argument("--print-only", action="store_true")
    args = parser.parse_args()

    host = get_value(args.host, "DB_HOST")
    port = get_value(args.port, "DB_PORT", "3306")
    user = get_value(args.user, "DB_USER")
    password = get_value(args.password, "DB_PASSWORD")
    database = args.database
    schema_sql = SCHEMA_PATH.read_text(encoding="utf-8")

    create_db_sql = (
        f"CREATE DATABASE IF NOT EXISTS `{database}` "
        "CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    )

    if args.print_only:
        print(create_db_sql)
        print(schema_sql)
        print(MIGRATION_SQL)
        return

    base_command = [
        "mysql",
        f"--host={host}",
        f"--port={port}",
        f"--user={user}",
        f"--password={password}",
        "--default-character-set=utf8mb4",
    ]

    run_mysql(base_command + ["-e", create_db_sql])
    run_mysql(base_command + [database], schema_sql)
    run_mysql(base_command + [database], MIGRATION_SQL)
    print(f"{database} schema applied successfully.")


if __name__ == "__main__":
    main()