"""Schema creation, column migrations and one-off data backfills."""
import sqlite3
from datetime import datetime

from nexora.database.connection import db
from nexora.utils.team import parse_team_members
from nexora.services.qualifiers import qualifier_members_for_registration
from nexora.services.ticket_codes import (
    make_ticket_serial,
    make_ticket_serial_number,
    make_ticket_token,
)


OLD_PARTICIPANT_SERIAL_MARKERS = ("-P", "-M")
OLD_QUALIFIER_SERIAL_MARKERS = ("-Q", "-M")


def _ticket_serial_exists(conn, serial, table="", row_id=None):
    has_registry = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'ticket_id_registry'"
    ).fetchone()
    if has_registry:
        registry = conn.execute(
            """
            SELECT 1 FROM ticket_id_registry
            WHERE ticket_id = ?
              AND (? = '' OR source_table != ? OR source_id != ?)
            """,
            (serial, table or "", table or "", row_id or 0),
        ).fetchone()
        if registry:
            return True
    checks = (
        ("registrations", "ticket_serial"),
        ("member_tickets", "ticket_serial"),
        ("qualifier_entry_tickets", "ticket_serial"),
    )
    for check_table, column in checks:
        row = conn.execute(
            f"""
            SELECT 1 FROM {check_table}
            WHERE {column} = ?
              AND (? = '' OR ? != ? OR id != ?)
            """,
            (serial, table or "", check_table, table or "", row_id or 0),
        ).fetchone()
        if row:
            return True
    return False


def make_unique_ticket_serial(conn, table="", row_id=None):
    serial = make_ticket_serial_number()
    while _ticket_serial_exists(conn, serial, table, row_id):
        serial = make_ticket_serial_number()
    return serial


def _looks_like_role_serial(serial):
    value = serial or ""
    return any(marker in value for marker in OLD_PARTICIPANT_SERIAL_MARKERS)


def ensure_ticket_tokens(conn=None):
    owns_conn = conn is None
    conn = conn or db()
    try:
        # Cheap guard so Passenger process boots do not re-scan the whole
        # table on every start when nothing is missing.
        if not conn.execute(
            "SELECT 1 FROM registrations WHERE ticket_token IS NULL OR ticket_token = '' LIMIT 1"
        ).fetchone():
            return
        rows = conn.execute(
            "SELECT id FROM registrations WHERE ticket_token IS NULL OR ticket_token = ''"
        ).fetchall()
        for row in rows:
            token = make_ticket_token()
            while conn.execute(
                "SELECT id FROM registrations WHERE ticket_token = ?", (token,)
            ).fetchone():
                token = make_ticket_token()
            conn.execute("UPDATE registrations SET ticket_token = ? WHERE id = ?", (token, row["id"]))
        if rows:
            conn.commit()
    finally:
        if owns_conn:
            conn.close()


def ensure_ticket_serials(conn=None):
    owns_conn = conn is None
    conn = conn or db()
    try:
        event_date = conn.execute("SELECT event_date FROM settings WHERE id = 1").fetchone()
        if not conn.execute(
            """SELECT 1 FROM registrations
               WHERE ticket_serial IS NULL OR ticket_serial = ''
                  OR ticket_serial LIKE 'NX-TICKET-%'
               LIMIT 1"""
        ).fetchone():
            return
        rows = conn.execute(
            """
            SELECT registrations.id, COALESCE(games.title, 'GAME') AS game_title
            FROM registrations LEFT JOIN games ON games.id = registrations.game_id
            WHERE registrations.ticket_serial IS NULL
               OR registrations.ticket_serial = ''
               OR registrations.ticket_serial LIKE 'NX-TICKET-%'
            """
        ).fetchall()
        for row in rows:
            conn.execute(
                "UPDATE registrations SET ticket_serial = ? WHERE id = ?",
                (make_ticket_serial(row["id"], row["game_title"], event_date["event_date"] if event_date else ""), row["id"]),
            )
        if rows:
            conn.commit()
    finally:
        if owns_conn:
            conn.close()


def ensure_indexes(conn):
    conn.executescript(
        """
        CREATE UNIQUE INDEX IF NOT EXISTS idx_registrations_ticket_token
        ON registrations(ticket_token);

        CREATE INDEX IF NOT EXISTS idx_registrations_payment_status
        ON registrations(payment_status);

        CREATE INDEX IF NOT EXISTS idx_registrations_game_id
        ON registrations(game_id);

        CREATE INDEX IF NOT EXISTS idx_registrations_created_at
        ON registrations(created_at);

        CREATE INDEX IF NOT EXISTS idx_registrations_entry_type
        ON registrations(entry_type);

        CREATE INDEX IF NOT EXISTS idx_registrations_entry_status
        ON registrations(entry_status);

        CREATE INDEX IF NOT EXISTS idx_registrations_payment_method
        ON registrations(payment_method);

        CREATE INDEX IF NOT EXISTS idx_games_public
        ON games(active, deleted_at, id);

        CREATE INDEX IF NOT EXISTS idx_media_kind_id
        ON media(kind, id);

        CREATE INDEX IF NOT EXISTS idx_leaders_active_order
        ON leaders(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_sponsors_active_order
        ON sponsors(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_prize_items_active_order
        ON prize_items(active, sort_order, id);

        CREATE INDEX IF NOT EXISTS idx_contacts_created_at
        ON contacts(created_at);

        CREATE INDEX IF NOT EXISTS idx_member_tickets_registration
        ON member_tickets(registration_id, member_number);

        CREATE INDEX IF NOT EXISTS idx_qualifier_entry_registration
        ON qualifier_entry_tickets(registration_id, qualifier_id, member_number);

        CREATE INDEX IF NOT EXISTS idx_ticket_audit_registration
        ON ticket_audit_log(registration_id, id);

        CREATE INDEX IF NOT EXISTS idx_email_logs_registration
        ON email_logs(registration_id, id);

        DROP INDEX IF EXISTS idx_registrations_ticket_serial;

        CREATE UNIQUE INDEX IF NOT EXISTS idx_email_logs_idempotency
        ON email_logs(idempotency_key)
        WHERE idempotency_key != '';

        CREATE INDEX IF NOT EXISTS idx_email_logs_type
        ON email_logs(email_type, registration_id);

        CREATE INDEX IF NOT EXISTS idx_email_outbox_status
        ON email_outbox(status, priority, process_after);

        CREATE INDEX IF NOT EXISTS idx_email_outbox_idempotency
        ON email_outbox(idempotency_key)
        WHERE idempotency_key != '';

        CREATE INDEX IF NOT EXISTS idx_email_outbox_registration
        ON email_outbox(registration_id);

        CREATE INDEX IF NOT EXISTS idx_email_outbox_bulk
        ON email_outbox(bulk_operation_id)
        WHERE bulk_operation_id > 0;

        CREATE INDEX IF NOT EXISTS idx_bulk_operations_status
        ON bulk_email_operations(status);

        CREATE INDEX IF NOT EXISTS idx_registrations_ticket_serial
        ON registrations(ticket_serial);

        """
    )
    # ── Registration uniqueness constraints ──
    # Team name / nickname must be unique per game (case-insensitive, blank-safe).
    # Drop old global index if it exists, then create per-game partial index.
    try:
        conn.execute("DROP INDEX IF EXISTS idx_reg_unique_team_name")
    except Exception:
        pass
    try:
        conn.execute(
            """CREATE UNIQUE INDEX IF NOT EXISTS idx_reg_unique_team_name
               ON registrations(LOWER(TRIM(team_name)), game_id)
               WHERE team_name IS NOT NULL AND TRIM(team_name) != ''"""
        )
    except Exception:
        pass

    try:
        # User requested to allow multiple registrations per CNIC/Email
        # Drop the unique index so new registrations won't conflict
        conn.execute("DROP INDEX IF EXISTS idx_reg_unique_email_cnic_game")
    except Exception:
        pass

    # Ensure member tickets index
    has_member_tickets = conn.execute(
        "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'member_tickets'"
    ).fetchone()
    if has_member_tickets:
        conn.executescript(
            """
            CREATE UNIQUE INDEX IF NOT EXISTS idx_member_tickets_serial
            ON member_tickets(ticket_serial);

            CREATE UNIQUE INDEX IF NOT EXISTS idx_member_tickets_token
            ON member_tickets(ticket_token);
            """
        )

    conn.executescript(
        """
        CREATE UNIQUE INDEX IF NOT EXISTS idx_qualifier_entry_ticket_serial
        ON qualifier_entry_tickets(ticket_serial);

        CREATE UNIQUE INDEX IF NOT EXISTS idx_qualifier_entry_ticket_token
        ON qualifier_entry_tickets(ticket_token);

        CREATE TABLE IF NOT EXISTS ticket_id_registry (
            ticket_id TEXT PRIMARY KEY,
            source_table TEXT NOT NULL,
            source_id INTEGER NOT NULL,
            created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
        );

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_registrations_insert
        AFTER INSERT ON registrations
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'registrations', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_registrations_update
        AFTER UPDATE OF ticket_serial ON registrations
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'registrations' AND source_id = NEW.id;
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'registrations', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_registrations_delete
        AFTER DELETE ON registrations
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'registrations' AND source_id = OLD.id;
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_member_insert
        AFTER INSERT ON member_tickets
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'member_tickets', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_member_update
        AFTER UPDATE OF ticket_serial ON member_tickets
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'member_tickets' AND source_id = NEW.id;
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'member_tickets', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_member_delete
        AFTER DELETE ON member_tickets
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'member_tickets' AND source_id = OLD.id;
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_qualifier_insert
        AFTER INSERT ON qualifier_entry_tickets
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'qualifier_entry_tickets', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_qualifier_update
        AFTER UPDATE OF ticket_serial ON qualifier_entry_tickets
        WHEN COALESCE(NEW.ticket_serial, '') != ''
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'qualifier_entry_tickets' AND source_id = NEW.id;
            INSERT INTO ticket_id_registry(ticket_id, source_table, source_id)
            VALUES (NEW.ticket_serial, 'qualifier_entry_tickets', NEW.id);
        END;

        CREATE TRIGGER IF NOT EXISTS trg_ticket_id_qualifier_delete
        AFTER DELETE ON qualifier_entry_tickets
        BEGIN
            DELETE FROM ticket_id_registry
            WHERE source_table = 'qualifier_entry_tickets' AND source_id = OLD.id;
        END;
        """
    )

    # Seed the ticket_id_registry ONCE. Re-scanning every table on every
    # Passenger boot held write locks and caused intermittent timeouts.
    if not conn.execute("SELECT 1 FROM ticket_id_registry LIMIT 1").fetchone():
        for table, column in (
            ("registrations", "ticket_serial"),
            ("member_tickets", "ticket_serial"),
            ("qualifier_entry_tickets", "ticket_serial"),
        ):
            try:
                conn.execute(
                    f"""INSERT OR IGNORE INTO ticket_id_registry (ticket_id, source_table, source_id)
                        SELECT {column}, '{table}', id FROM {table}
                        WHERE COALESCE({column}, '') != ''"""
                )
            except sqlite3.Error:
                pass


MEMBER_TICKET_EXTRA_COLUMNS = {
    "ticket_token": "",
    "member_cnic": "",
    "entry_status": "pending",
    "entry_marked_at": "",
    "entry_note": "",
    "checkin_gate": "",
    "checked_in_by": "",
    "rejection_reason": "",
    "rejected_by": "",
    "rejected_at": "",
    "override_by": "",
    "override_at": "",
    "override_reason": "",
}


def ensure_member_ticket_tokens(conn):
    """Give historical member tickets their own opaque QR token."""
    if not conn.execute(
        "SELECT 1 FROM member_tickets WHERE ticket_token IS NULL OR ticket_token = '' LIMIT 1"
    ).fetchone():
        return
    rows = conn.execute(
        "SELECT id FROM member_tickets WHERE ticket_token IS NULL OR ticket_token = ''"
    ).fetchall()
    for row in rows:
        token = make_ticket_token()
        while conn.execute("SELECT id FROM member_tickets WHERE ticket_token = ?", (token,)).fetchone():
            token = make_ticket_token()
        conn.execute("UPDATE member_tickets SET ticket_token = ? WHERE id = ?", (token, row["id"]))
    if rows:
        conn.commit()


def ensure_group_member_tickets(conn):
    """Create a unique participant ticket for solo/leader and every member."""
    # Skip fast when every registration already has its primary member ticket.
    if not conn.execute(
        """SELECT 1 FROM registrations r
           WHERE NOT EXISTS (
               SELECT 1 FROM member_tickets m
               WHERE m.registration_id = r.id AND m.member_number = 1
           )
           LIMIT 1"""
    ).fetchone():
        return
    rows = conn.execute(
        "SELECT id, full_name, email, cnic, leader_name, entry_type, members, ticket_serial FROM registrations"
    ).fetchall()
    for row in rows:
        ensure_member_tickets_for_registration(conn, row)
    conn.commit()


def ensure_member_tickets_for_registration(conn, row_or_id):
    """Create/update participant tickets for one registration only."""
    if isinstance(row_or_id, int):
        row = conn.execute(
            "SELECT id, full_name, email, cnic, leader_name, entry_type, members, ticket_serial FROM registrations WHERE id = ?",
            (row_or_id,),
        ).fetchone()
    else:
        row = row_or_id
    if not row:
        return

    existing = conn.execute(
        "SELECT id FROM member_tickets WHERE registration_id = ? AND member_number = 1",
        (row["id"],),
    ).fetchone()
    if not existing:
        conn.execute(
            """INSERT INTO member_tickets
               (registration_id, member_number, member_name, member_email, member_cnic, ticket_serial, ticket_token, created_at)
               VALUES (?, 1, ?, ?, ?, ?, ?, ?)""",
            (row["id"], row["leader_name"] or row["full_name"] or "Player", row["email"] or "",
             row["cnic"] or "", make_unique_ticket_serial(conn),
             make_ticket_token(), datetime.now().isoformat(timespec="seconds")),
        )
    else:
        ticket = conn.execute(
            "SELECT id, ticket_serial FROM member_tickets WHERE id = ?", (existing["id"],)
        ).fetchone()
        if ticket and _looks_like_role_serial(ticket["ticket_serial"]):
            conn.execute(
                "UPDATE member_tickets SET ticket_serial = ? WHERE id = ?",
                (make_unique_ticket_serial(conn, "member_tickets", ticket["id"]), ticket["id"]),
            )
    if (row["entry_type"] or "").lower() != "group":
        return
    for member_number, member in enumerate(parse_team_members(row["members"]), start=2):
        if not any(member.values()):
            continue
        existing = conn.execute(
            "SELECT id FROM member_tickets WHERE registration_id = ? AND member_number = ?",
            (row["id"], member_number),
        ).fetchone()
        if existing:
            ticket = conn.execute(
                "SELECT id, ticket_serial FROM member_tickets WHERE id = ?", (existing["id"],)
            ).fetchone()
            if ticket and _looks_like_role_serial(ticket["ticket_serial"]):
                conn.execute(
                    "UPDATE member_tickets SET ticket_serial = ? WHERE id = ?",
                    (make_unique_ticket_serial(conn, "member_tickets", ticket["id"]), ticket["id"]),
                )
            continue
        serial = make_unique_ticket_serial(conn)
        conn.execute(
            """INSERT INTO member_tickets
               (registration_id, member_number, member_name, member_email, member_cnic, ticket_serial, ticket_token, created_at)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
            (row["id"], member_number, member["name"], member["email"], member.get("cnic", ""), serial,
             make_ticket_token(), datetime.now().isoformat(timespec="seconds")),
        )


def ensure_qualifier_entry_tickets(conn):
    """Create one independent check-in entry per (seat x qualifier).

    Each row carries its own ticket_serial + ticket_token, so a check-in on
    Qualifier 1 never affects Qualifier 2/3/4 (independent per qualifier).
    """
    regs = conn.execute(
        "SELECT * FROM registrations WHERE COALESCE(selected_qualifiers, '') != ''"
    ).fetchall()
    for reg in regs:
        _create_entry_tickets_for_reg(conn, reg)
    ensure_qualifier_ticket_serials(conn)
    conn.commit()


def ensure_qualifier_entry_tickets_for_registration(conn, registration_id):
    """Create qualifier tickets for one registration only."""
    reg = conn.execute("SELECT * FROM registrations WHERE id = ?", (registration_id,)).fetchone()
    if reg:
        _create_entry_tickets_for_reg(conn, reg)


def ensure_qualifier_ticket_serials(conn):
    rows = conn.execute(
        "SELECT id, registration_id, qualifier_id, member_number, ticket_serial FROM qualifier_entry_tickets"
    ).fetchall()
    for row in rows:
        reg = conn.execute("SELECT * FROM registrations WHERE id = ?", (row["registration_id"],)).fetchone()
        qual = conn.execute("SELECT * FROM qualifiers WHERE id = ?", (row["qualifier_id"],)).fetchone()
        if not reg or not qual:
            continue
        if not _looks_like_role_serial(row["ticket_serial"]):
            continue
        expected = make_unique_ticket_serial(conn, "qualifier_entry_tickets", row["id"])
        conn.execute("UPDATE qualifier_entry_tickets SET ticket_serial = ? WHERE id = ?", (expected, row["id"]))


def _create_entry_tickets_for_reg(conn, reg):
    """Create qualifier entry tickets for a single registration row."""
    q_ids = [x.strip() for x in (reg["selected_qualifiers"] or "").split(",") if x.strip().isdigit()]
    if not q_ids:
        return
    q_rows = conn.execute(
        "SELECT id, qualifier_name, qualifier_number FROM qualifiers WHERE id IN ({})".format(
            ",".join("?" for _ in q_ids)
        ),
        [int(x) for x in q_ids],
    ).fetchall()
    quals = {q["id"]: q for q in q_rows}
    for seat in qualifier_members_for_registration(reg):
        for qid in q_ids:
            qid = int(qid)
            qual = quals.get(qid)
            if not qual:
                continue
            existing = conn.execute(
                "SELECT id FROM qualifier_entry_tickets WHERE registration_id = ? AND qualifier_id = ? AND member_number = ?",
                (reg["id"], qid, seat["member_number"]),
            ).fetchone()
            if existing:
                continue
            serial = make_unique_ticket_serial(conn)
            token = make_ticket_token()
            while conn.execute("SELECT id FROM qualifier_entry_tickets WHERE ticket_token = ?", (token,)).fetchone():
                token = make_ticket_token()
            try:
                conn.execute(
                    """INSERT INTO qualifier_entry_tickets
                       (registration_id, qualifier_id, member_number, player_name, player_email, player_phone,
                        role, ticket_serial, ticket_token, entry_status, created_at)
                       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 'pending', ?)""",
                    (reg["id"], qid, seat["member_number"], seat["name"], seat["email"], seat["phone"],
                     seat["role"], serial, token, datetime.now().isoformat(timespec="seconds")),
                )
            except sqlite3.IntegrityError:
                pass


# ────────────────────────────────────────────
# Schema + migrations
# ────────────────────────────────────────────
SETTINGS_EXTRA_COLUMNS = {
    "event_tagline": "Solo and team formats for serious players on one competitive stage.",
    "event_venue": "Main Sports Complex, Campus Ground",
    "event_city": "Karachi, Pakistan",
    "hero_heading": "Nexora Esports Championship",
    "about_intro": (
        "Nexora Esports Championship is a professionally managed gaming event for students, teams, "
        "and competitive players who want a fair stage to prove their skill."
    ),
    "about_body": (
        "Nexora was built to give campus gamers a serious, fair, and well-organized platform. Every "
        "registration is verified, every bracket is managed transparently, and every match follows clear "
        "tournament standards."
    ),
    "contact_email": "",
    "contact_phone": "+92 331 0314962",
    "contact_address": "Sports Office, Main Campus",
    "whatsapp_link": "https://wa.me/923310314962",
    "facebook_link": "",
    "instagram_link": "",
    "youtube_link": "",
    "discord_link": "",
    "payment_method_label": "G-Pay / JazzCash / Easypaisa",
    "payment_bank_name": "",
    "payment_account_title": "",
    "payment_iban": "",
    "payment_account_number": "",
    "payment_bank_branch": "",
    "payment_qr_image": "",
    "site_logo": "Nexora Logo.png",
    "home_background": "NexoraGame.png",
    "nav_bg_color": "#d4a017",
    "nav_text_color": "#111827",
    "nav_accent_color": "#e8384f",
    "group_ticket_delivery": "leader_only",
    "countdown_mode": "event",
    "prize_pool_title": "Victory Feels Bigger Here",
    "prize_pool_amount": "Prize Pool Announced Soon",
    "prize_pool_note": "Prize pool, winner perks, and event rewards are updated from the admin panel.",
    "prize_first_label": "Winner Trophy",
    "prize_first_note": "Champion-level recognition",
    "prize_second_label": "Official Certificates",
    "prize_second_note": "For finalists and top teams",
    "prize_third_label": "MVP Spotlight",
    "prize_third_note": "Featured moments and shoutouts",
    "prize_banner_image": "",
    "prize_banner_images": "",
    "smtp_host": "",
    "smtp_port": "465",
    "smtp_user": "",
    "smtp_password": "",
    "smtp_from_email": "",
    "smtp_admin_email": "",
    "admin_password_hash": "",
}


GAME_EXTRA_COLUMNS = {
    "reg_from": "",
    "reg_to": "",
    "discount_label": "",
    "individual_old_price": "",
    "group_old_price": "",
    "min_members": "1",
    "max_members": "1",
    "price_for_2": "",
    "price_for_3": "",
    "price_for_4": "",
    "old_price_2": "",
    "old_price_3": "",
    "old_price_4": "",
    "discount_from": "",
    "discount_to": "",
    "discount_pct": "",
    "deleted_at": "",
    # Qualifier configuration
    "qualifiers_enabled": "0",
    "qualifier_fee_type": "separate",
    "qualifier_package_size": "1",
    "qualifier_package_fee": "",
    "qualifier_additional_fee": "",
    # Custom label for the "Team Name / Nick Name" field on registration form
    "team_label": "",
    # Free entry: when enabled, no payment proof is required at registration
    "is_free": "0",
    # Custom label for registration field shown on PDF tickets and exports
    "registration_label": "",
    "venue_address": "",
    "event_end_time": "",
}


REGISTRATION_EXTRA_COLUMNS = {
    "institution_type": "University",
    "ticket_token": "",
    "ticket_serial": "",
    "verified_at": "",
    "entry_status": "pending",
    "entry_marked_at": "",
    "entry_note": "",
    "amount_paid": "",
    "total_payable_saved": "",
    "original_price_saved": "",
    "discounted_price_saved": "",
    "original_amount": "",
    "discount_type_at_registration": "",
    "discount_value_at_registration": "",
    "discount_amount": "",
    "final_payable_amount": "",
    "currency": "PKR",
    "transaction_reference": "",
    "ticket_issued_at": "",
    "checkin_gate": "",
    "checked_in_by": "",
    "rejection_reason": "",
    "rejected_by": "",
    "rejected_at": "",
    "override_by": "",
    "override_at": "",
    "override_reason": "",
    # Qualifier selection
    "selected_qualifiers": "",
    "qualifier_count": "0",
    "qualifier_fee": "",
    # Email tracking
    "last_email_status": "",
    "last_email_sent_at": "",
    "cancellation_reason": "",
}


QUALIFIER_EXTRA_COLUMNS = {
    "qualifier_time": "",
    "qualifier_end_time": "",
    "venue_address": "",
    "location": "TBD",
}


EMAIL_LOG_EXTRA_COLUMNS = {
    "email_type": "",
    "previous_status": "",
    "new_status": "",
    "pdf_attached": "0",
    "retry_count": "0",
    "idempotency_key": "",
}


def ensure_columns(conn, table, columns):
    existing = {row["name"] for row in conn.execute(f"PRAGMA table_info({table})")}
    for name, default in columns.items():
        if name not in existing:
            conn.execute(f"ALTER TABLE {table} ADD COLUMN {name} TEXT NOT NULL DEFAULT ''")
            conn.execute(f"UPDATE {table} SET {name} = ? WHERE {name} = ''", (default,))


def init_db():
    with db() as conn:
        conn.executescript(
            """
            CREATE TABLE IF NOT EXISTS settings (
                id INTEGER PRIMARY KEY CHECK (id = 1),
                event_title TEXT NOT NULL,
                event_date TEXT NOT NULL,
                early_deadline TEXT NOT NULL,
                early_discount TEXT NOT NULL,
                bank_account TEXT NOT NULL,
                payment_note TEXT NOT NULL,
                registration_open INTEGER NOT NULL DEFAULT 1
            );

            CREATE TABLE IF NOT EXISTS games (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                platform TEXT NOT NULL,
                individual_price TEXT NOT NULL,
                group_price TEXT NOT NULL,
                image TEXT,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL,
                registration_label TEXT,
                venue TEXT NOT NULL DEFAULT '',
                event_date TEXT NOT NULL DEFAULT '',
                event_time TEXT NOT NULL DEFAULT ''
            );

            CREATE TABLE IF NOT EXISTS media (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                kind TEXT NOT NULL,
                title TEXT NOT NULL,
                caption TEXT,
                image TEXT NOT NULL,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS registrations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                full_name TEXT NOT NULL,
                email TEXT NOT NULL,
                phone TEXT NOT NULL,
                cnic TEXT NOT NULL,
                university TEXT NOT NULL,
                roll_no TEXT,
                entry_type TEXT NOT NULL,
                game_id INTEGER NOT NULL,
                team_name TEXT,
                leader_name TEXT,
                members TEXT,
                payment_method TEXT NOT NULL,
                payment_status TEXT NOT NULL,
                proof_image TEXT,
                created_at TEXT NOT NULL,
                FOREIGN KEY (game_id) REFERENCES games(id)
            );

            CREATE TABLE IF NOT EXISTS contacts (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                email TEXT NOT NULL,
                phone TEXT NOT NULL,
                message TEXT NOT NULL,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS leaders (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                role TEXT NOT NULL,
                quote TEXT NOT NULL,
                image TEXT,
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS sponsors (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                tier TEXT NOT NULL DEFAULT 'Partner',
                commitment TEXT,
                website TEXT,
                logo TEXT,
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS prize_items (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                amount TEXT NOT NULL DEFAULT '',
                note TEXT NOT NULL DEFAULT '',
                image TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS member_tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                member_number INTEGER NOT NULL,
                member_name TEXT NOT NULL DEFAULT '',
                member_email TEXT NOT NULL DEFAULT '',
                member_cnic TEXT NOT NULL DEFAULT '',
                ticket_serial TEXT NOT NULL,
                created_at TEXT NOT NULL,
                UNIQUE(registration_id, member_number),
                UNIQUE(ticket_serial),
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS ticket_audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                ticket_serial TEXT NOT NULL,
                action TEXT NOT NULL,
                previous_status TEXT NOT NULL DEFAULT '',
                new_status TEXT NOT NULL DEFAULT '',
                reason TEXT NOT NULL DEFAULT '',
                gate_location TEXT NOT NULL DEFAULT '',
                staff_username TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS roles (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL UNIQUE,
                description TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS role_permissions (
                role_id INTEGER NOT NULL,
                permission_key TEXT NOT NULL,
                PRIMARY KEY (role_id, permission_key),
                FOREIGN KEY (role_id) REFERENCES roles(id)
            );

            CREATE TABLE IF NOT EXISTS admin_users (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                full_name TEXT NOT NULL DEFAULT '',
                role_id INTEGER,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL,
                FOREIGN KEY (role_id) REFERENCES roles(id)
            );

            CREATE TABLE IF NOT EXISTS role_audit_log (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                actor_username TEXT NOT NULL,
                action TEXT NOT NULL,
                target_username TEXT NOT NULL DEFAULT '',
                details TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS user_permissions (
                user_id INTEGER NOT NULL,
                permission_key TEXT NOT NULL,
                PRIMARY KEY (user_id, permission_key),
                FOREIGN KEY (user_id) REFERENCES admin_users(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS team_sections (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT NOT NULL,
                description TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS team_members (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                section_id INTEGER NOT NULL,
                name TEXT NOT NULL,
                image TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                created_at TEXT NOT NULL,
                FOREIGN KEY (section_id) REFERENCES team_sections(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS team_rows (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                section_id INTEGER NOT NULL,
                row_index INTEGER NOT NULL DEFAULT 0,
                direction TEXT NOT NULL DEFAULT 'ltr',
                speed INTEGER NOT NULL DEFAULT 30,
                enabled INTEGER NOT NULL DEFAULT 1,
                FOREIGN KEY (section_id) REFERENCES team_sections(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS qualifiers (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                game_id INTEGER NOT NULL,
                qualifier_name TEXT NOT NULL DEFAULT '',
                qualifier_number INTEGER NOT NULL DEFAULT 0,
                fee TEXT NOT NULL DEFAULT '',
                qualifier_date TEXT NOT NULL DEFAULT '',
                sort_order INTEGER NOT NULL DEFAULT 0,
                active INTEGER NOT NULL DEFAULT 1,
                location TEXT NOT NULL DEFAULT 'TBD',
                created_at TEXT NOT NULL,
                FOREIGN KEY (game_id) REFERENCES games(id) ON DELETE CASCADE
            );

            CREATE TABLE IF NOT EXISTS qualifier_entry_tickets (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                qualifier_id INTEGER NOT NULL,
                member_number INTEGER NOT NULL DEFAULT 1,
                player_name TEXT NOT NULL DEFAULT '',
                player_email TEXT NOT NULL DEFAULT '',
                player_phone TEXT NOT NULL DEFAULT '',
                role TEXT NOT NULL DEFAULT 'leader',
                ticket_serial TEXT NOT NULL,
                ticket_token TEXT NOT NULL DEFAULT '',
                entry_status TEXT NOT NULL DEFAULT 'pending',
                entry_marked_at TEXT NOT NULL DEFAULT '',
                entry_note TEXT NOT NULL DEFAULT '',
                checkin_gate TEXT NOT NULL DEFAULT '',
                checked_in_by TEXT NOT NULL DEFAULT '',
                rejection_reason TEXT NOT NULL DEFAULT '',
                rejected_by TEXT NOT NULL DEFAULT '',
                rejected_at TEXT NOT NULL DEFAULT '',
                override_by TEXT NOT NULL DEFAULT '',
                override_at TEXT NOT NULL DEFAULT '',
                override_reason TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                UNIQUE(registration_id, qualifier_id, member_number),
                UNIQUE(ticket_serial),
                UNIQUE(ticket_token),
                FOREIGN KEY (registration_id) REFERENCES registrations(id),
                FOREIGN KEY (qualifier_id) REFERENCES qualifiers(id)
            );

            CREATE TABLE IF NOT EXISTS email_logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                recipient_email TEXT NOT NULL DEFAULT '',
                subject TEXT NOT NULL DEFAULT '',
                venue TEXT NOT NULL DEFAULT '',
                qualifier_details TEXT NOT NULL DEFAULT '',
                event_date TEXT NOT NULL DEFAULT '',
                event_time TEXT NOT NULL DEFAULT '',
                description TEXT NOT NULL DEFAULT '',
                status TEXT NOT NULL DEFAULT 'sent',
                error_message TEXT NOT NULL DEFAULT '',
                sent_by TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL,
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS email_outbox (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                registration_id INTEGER NOT NULL,
                recipient_email TEXT NOT NULL DEFAULT '',
                recipient_name TEXT NOT NULL DEFAULT '',
                email_type TEXT NOT NULL DEFAULT '',
                old_status TEXT NOT NULL DEFAULT '',
                new_status TEXT NOT NULL DEFAULT '',
                reason TEXT NOT NULL DEFAULT '',
                priority INTEGER NOT NULL DEFAULT 5,
                idempotency_key TEXT NOT NULL DEFAULT '',
                status TEXT NOT NULL DEFAULT 'pending',
                attempt_count INTEGER NOT NULL DEFAULT 0,
                max_attempts INTEGER NOT NULL DEFAULT 5,
                last_error TEXT NOT NULL DEFAULT '',
                next_retry_at TEXT NOT NULL DEFAULT '',
                process_after TEXT NOT NULL DEFAULT '',
                locked_by TEXT NOT NULL DEFAULT '',
                locked_at TEXT NOT NULL DEFAULT '',
                completed_at TEXT NOT NULL DEFAULT '',
                bulk_operation_id INTEGER NOT NULL DEFAULT 0,
                created_at TEXT NOT NULL DEFAULT '',
                FOREIGN KEY (registration_id) REFERENCES registrations(id)
            );

            CREATE TABLE IF NOT EXISTS bulk_email_operations (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                operation_type TEXT NOT NULL DEFAULT 'bulk_status',
                status TEXT NOT NULL DEFAULT 'pending',
                total_jobs INTEGER NOT NULL DEFAULT 0,
                queued_count INTEGER NOT NULL DEFAULT 0,
                processing_count INTEGER NOT NULL DEFAULT 0,
                sent_count INTEGER NOT NULL DEFAULT 0,
                failed_count INTEGER NOT NULL DEFAULT 0,
                pending_count INTEGER NOT NULL DEFAULT 0,
                created_by TEXT NOT NULL DEFAULT '',
                created_at TEXT NOT NULL DEFAULT '',
                completed_at TEXT NOT NULL DEFAULT ''
            );
            """
        )

        ensure_columns(conn, "settings", SETTINGS_EXTRA_COLUMNS)
        ensure_columns(conn, "games", GAME_EXTRA_COLUMNS)
        ensure_columns(conn, "registrations", REGISTRATION_EXTRA_COLUMNS)
        ensure_columns(conn, "member_tickets", MEMBER_TICKET_EXTRA_COLUMNS)
        ensure_columns(conn, "qualifiers", QUALIFIER_EXTRA_COLUMNS)
        ensure_columns(conn, "email_logs", EMAIL_LOG_EXTRA_COLUMNS)
        ensure_ticket_tokens(conn)
        ensure_ticket_serials(conn)
        ensure_group_member_tickets(conn)
        ensure_member_ticket_tokens(conn)
        ensure_indexes(conn)

        exists = conn.execute("SELECT id FROM settings WHERE id = 1").fetchone()
        fresh_db = not exists
        if not exists:
            conn.execute(
                """
                INSERT INTO settings
                (id, event_title, event_date, early_deadline, early_discount, bank_account,
                 payment_note, registration_open)
                VALUES (1, ?, ?, ?, ?, ?, ?, 1)
                """,
                (
                    "Nexora Esports Championship",
                    "2026-10-15T18:00",
                    "2026-09-30T23:59",
                    "Early Bird: 20% discount before deadline",
                    "Bank / JazzCash / Easypaisa account: 03XX-XXXXXXX",
                    "Upload a payment proof screenshot. The admin will verify it and mark the status as Paid.",
                ),
            )
            for column, value in SETTINGS_EXTRA_COLUMNS.items():
                conn.execute(f"UPDATE settings SET {column} = ? WHERE id = 1", (value,))

        now = datetime.now().isoformat(timespec="seconds")

        if not conn.execute("SELECT id FROM roles WHERE name = 'Check-in Staff'").fetchone():
            cur = conn.execute("INSERT INTO roles (name, description, created_at) VALUES (?, ?, ?)", ("Check-in Staff", "Scans and processes event entry tickets.", now))
            conn.executemany("INSERT INTO role_permissions (role_id, permission_key) VALUES (?, ?)", [(cur.lastrowid, key) for key in ("view_dashboard", "view_registrations", "scan_qr_codes", "manually_verify_tickets", "view_scanned_record", "approve_entry", "reject_entry", "view_checkin_history")])
        checkin_role = conn.execute("SELECT id FROM roles WHERE name = 'Check-in Staff'").fetchone()
        if checkin_role:
            conn.execute("INSERT OR IGNORE INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (checkin_role["id"], "view_dashboard"))
            conn.execute("INSERT OR IGNORE INTO role_permissions (role_id, permission_key) VALUES (?, ?)", (checkin_role["id"], "view_scanned_record"))

        # Demo content is seeded ONLY once, when the database has never been
        # initialized (no settings row). Deleted games/leaders stay deleted.
        if fresh_db:
            if conn.execute("SELECT COUNT(*) AS c FROM games").fetchone()["c"] == 0:
                conn.executemany(
                    """
                    INSERT INTO games (title, platform, individual_price, group_price, image, active, created_at)
                    VALUES (?, ?, ?, ?, ?, 1, ?)
                    """,
                    [
                        ("EA FC / FIFA", "PS5", "Rs. 800", "Rs. 2,800", "", now),
                        ("PUBG Mobile", "Mobile", "Rs. 600", "Rs. 2,000", "", now),
                        ("Tekken 8", "PC / PS5", "Rs. 700", "Rs. 2,400", "", now),
                    ],
                )

            if conn.execute("SELECT COUNT(*) AS c FROM leaders").fetchone()["c"] == 0:
                conn.executemany(
                    """
                    INSERT INTO leaders (name, role, quote, image, sort_order, active, created_at)
                    VALUES (?, ?, ?, ?, ?, 1, ?)
                    """,
                    [
                        (
                            "Full Name Here",
                            "Sports CEO",
                            "Esports is more than a hobby. It is discipline, teamwork, and strategy on a serious stage.",
                            "",
                            1,
                            now,
                        ),
                        (
                            "Full Name Here",
                            "Sports Secretary",
                            "Every registration, match, and result should stay transparent. Fair play defines this championship.",
                            "",
                            2,
                            now,
                        ),
                        (
                            "Full Name Here",
                            "Sports Director",
                            "We want players who can think clearly under pressure. Every Nexora bracket tests that mindset.",
                            "",
                            3,
                            now,
                        ),
                    ],
                )

        conn.commit()