"""Database initialization — schema creation and migration orchestration."""

import sqlite3

from app.db import get_db, write_lock
from app.migrations import (
    migrate_add_abuse_protection,
    migrate_add_agents,
    migrate_add_ai_builder,
    migrate_add_analytics,
    migrate_add_api_keys,
    migrate_add_branding,
    migrate_add_document_generation_status,
    migrate_add_document_templates,
    migrate_add_email_campaigns,
    migrate_add_email_phase3,
    migrate_add_email_phase4,
    migrate_add_email_tracking,
    migrate_add_email_verification,
    migrate_add_field_config,
    migrate_add_form_actions,
    migrate_add_form_analytics,
    migrate_add_form_sessions,
    migrate_add_form_templates,
    migrate_add_form_variants,
    migrate_add_form_versions,
    migrate_add_indexes,
    migrate_add_invite_expiration,
    migrate_add_rate_limit,
    migrate_add_submission_data,
    migrate_add_teams,
    migrate_add_usage,
    migrate_add_user_id_to_doc_templates,
    migrate_add_user_id_to_invoice_schedules,
    migrate_add_user_id_to_sites,
    migrate_add_user_referral_cols,
    migrate_add_webhook_destinations,
    migrate_add_webhook_retry_locking,
    migrate_normalize_field_configs,
)


def init_db():
    """Initialize database schema and run all migrations. Idempotent."""
    # Serialize schema changes across gunicorn workers + RQ worker
    with write_lock():
        conn = None
        try:
            conn = get_db()
            conn.executescript("""
                CREATE TABLE IF NOT EXISTS users (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email_hash TEXT UNIQUE NOT NULL,
                    email_encrypted TEXT,
                    password_hash TEXT NOT NULL,
                    name_encrypted TEXT,
                    display_name_encrypted TEXT,
                    tier TEXT DEFAULT 'free',
                    stripe_customer_id TEXT,
                    stripe_subscription_id TEXT,
                    email_verified INTEGER DEFAULT 0,
                    email_verification_token TEXT,
                    email_verification_sent_at TIMESTAMP,
                    pending_email TEXT,
                    password_reset_token TEXT,
                    password_reset_sent_at TIMESTAMP,
                    magic_login_token TEXT,
                    magic_login_sent_at TIMESTAMP,
                    magic_login_enabled INTEGER DEFAULT 1,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );

                CREATE TABLE IF NOT EXISTS sites (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    token TEXT UNIQUE NOT NULL,
                    name TEXT NOT NULL,
                    owner_email TEXT NOT NULL,
                    smtp_from TEXT,
                    user_id INTEGER,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    FOREIGN KEY (user_id) REFERENCES users(id)
                );

                CREATE TABLE IF NOT EXISTS submissions (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    site_id INTEGER NOT NULL REFERENCES sites(id),
                    customer_name TEXT,
                    customer_phone TEXT,
                    customer_email TEXT,
                    customer_equipment TEXT,
                    customer_message TEXT,
                    submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );

                CREATE INDEX IF NOT EXISTS idx_submissions_site_id ON submissions(site_id);
                CREATE INDEX IF NOT EXISTS idx_submissions_created ON submissions(submitted_at DESC);

                CREATE TABLE IF NOT EXISTS waitlist (
                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                    email TEXT UNIQUE NOT NULL,
                    source TEXT DEFAULT 'landing',
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                );

                CREATE INDEX IF NOT EXISTS idx_sites_user_id ON sites(user_id);
            """)
            conn.commit()
        finally:
            if conn:
                conn.close()

    # Run migrations (all idempotent — safe on both fresh and existing DBs)
    migrate_add_field_config()
    migrate_add_submission_data()
    migrate_add_user_id_to_sites()
    migrate_add_usage()
    migrate_add_abuse_protection()
    migrate_add_rate_limit()
    migrate_add_analytics()
    migrate_add_email_verification()
    migrate_add_indexes()
    migrate_add_api_keys()
    migrate_normalize_field_configs()
    migrate_add_form_templates()
    migrate_add_teams()
    migrate_add_form_analytics()
    migrate_add_webhook_destinations()
    migrate_add_ai_builder()
    migrate_add_form_versions()
    migrate_add_form_sessions()
    migrate_add_form_variants()
    migrate_add_branding()
    migrate_add_webhook_retry_locking()
    migrate_add_document_templates()
    migrate_add_invite_expiration()
    migrate_add_user_id_to_doc_templates()
    migrate_add_user_id_to_invoice_schedules()
    migrate_add_email_campaigns()
    migrate_add_email_tracking()
    migrate_add_email_phase3()
    migrate_add_email_phase4()
    migrate_add_form_actions()
    migrate_add_user_referral_cols()
    migrate_add_agents()
    migrate_add_document_generation_status()

    # Run last — encrypts existing plaintext PII data
    try:
        from app.migrations import migrate_encrypt_existing_data

        migrate_encrypt_existing_data()
    except (sqlite3.OperationalError, AttributeError):
        pass

    # Encrypt remaining plaintext PII (users, webhook destinations, invoice schedules, etc.)
    try:
        from app.migrations import migrate_encrypt_user_pii

        migrate_encrypt_user_pii()
    except (sqlite3.OperationalError, AttributeError):
        pass

    # Re-encrypt submissions from site-scoped keys to user-scoped keys
    try:
        from app.migrations import migrate_rescope_to_user_keys

        migrate_rescope_to_user_keys()
    except (sqlite3.OperationalError, AttributeError):
        pass

    # Rotate encryption keys if V2 is configured
    try:
        from app.migrations import migrate_rotate_encryption_keys

        migrate_rotate_encryption_keys()
    except (sqlite3.OperationalError, AttributeError):
        pass