"""Merge duplicate Walk-in Customer rows into the global system customer.

The global walk-in is seeded at startup (id 00000000-0000-0000-0000-000000000001).
Legacy company setup incorrectly created a second walk-in per company.

Run: python migrations/dedupe_walkin_customers.py
"""
import sys
from pathlib import Path

ROOT = Path(__file__).resolve().parents[1]
sys.path.insert(0, str(ROOT))

from sqlalchemy import text
from app.core.database import engine
from app.constants.walkin_customer import WALKIN_CUSTOMER_ID

# (table_name, column_name) — nullable FKs updated to canonical walk-in id
CUSTOMER_FK_TABLES = [
    ("sales", "customer_id"),
    ("invoices", "customer_id"),
    ("payments", "customer_id"),
    ("quotations", "customer_id"),
    ("receipts", "customer_id"),
    ("credit_notes", "customer_id"),
    ("serial_numbers", "customer_id"),
    ("product_traceability_log", "customer_id"),
    ("billing_cycles", "customer_id"),
    ("job_cards", "customer_id"),
]


def table_exists(conn, table: str) -> bool:
    row = conn.execute(
        text(
            "SELECT 1 FROM information_schema.tables "
            "WHERE table_schema = 'public' AND table_name = :t"
        ),
        {"t": table},
    ).fetchone()
    return row is not None


def column_exists(conn, table: str, column: str) -> bool:
    row = conn.execute(
        text(
            "SELECT 1 FROM information_schema.columns "
            "WHERE table_schema = 'public' AND table_name = :t AND column_name = :c"
        ),
        {"t": table, "c": column},
    ).fetchone()
    return row is not None


def run():
    with engine.begin() as conn:
        if not table_exists(conn, "customers"):
            print("SKIP: customers table not found")
            return

        # Ensure canonical walk-in exists
        exists = conn.execute(
            text("SELECT 1 FROM customers WHERE id = :id"),
            {"id": WALKIN_CUSTOMER_ID},
        ).fetchone()
        if not exists:
            conn.execute(
                text(
                    """
                    INSERT INTO customers (
                        id, name, customer_type, payment_terms, credit_limit, active, notes,
                        created_at, updated_at
                    ) VALUES (
                        :id, 'Walk-in Customer', 'walk_in', 0, 0, true,
                        'System-generated Walk-in Customer.',
                        NOW(), NOW()
                    )
                    """
                ),
                {"id": WALKIN_CUSTOMER_ID},
            )
            print(f"Created canonical walk-in customer {WALKIN_CUSTOMER_ID}")

        dupes = conn.execute(
            text(
                """
                SELECT id, name, branch_id
                FROM customers
                WHERE id != :canonical
                  AND (
                    LOWER(TRIM(name)) IN ('walk-in customer', 'walkin customer', 'walk in customer')
                    OR customer_type = 'walk_in'
                  )
                """
            ),
            {"canonical": WALKIN_CUSTOMER_ID},
        ).fetchall()

        if not dupes:
            print("No duplicate walk-in customers found.")
            return

        print(f"Found {len(dupes)} duplicate walk-in customer(s)")
        for dup_id, dup_name, branch_id in dupes:
            print(f"  -> merging {dup_id} ({dup_name!r}, branch={branch_id})")

            for table, col in CUSTOMER_FK_TABLES:
                if not table_exists(conn, table) or not column_exists(conn, table, col):
                    continue
                result = conn.execute(
                    text(
                        f'UPDATE "{table}" SET "{col}" = :canonical WHERE "{col}" = :dup'
                    ),
                    {"canonical": WALKIN_CUSTOMER_ID, "dup": dup_id},
                )
                if result.rowcount:
                    print(f"     updated {result.rowcount} row(s) in {table}.{col}")

            conn.execute(
                text("DELETE FROM customers WHERE id = :dup"),
                {"dup": dup_id},
            )
            print(f"     removed duplicate customer row {dup_id}")

        # Normalize canonical record
        conn.execute(
            text(
                """
                UPDATE customers
                SET name = 'Walk-in Customer',
                    customer_type = 'walk_in',
                    active = true
                WHERE id = :id
                """
            ),
            {"id": WALKIN_CUSTOMER_ID},
        )

        print("Walk-in customer deduplication complete.")


if __name__ == "__main__":
    run()
