"""Add unique indexes to prevent duplicate customers at the database layer.

Run: python migrations/prevent_duplicate_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


def run():
    statements = [
        (
            "uq_customers_one_walk_in",
            """
            CREATE UNIQUE INDEX IF NOT EXISTS uq_customers_one_walk_in
            ON customers ((1))
            WHERE customer_type = 'walk_in'
            """,
        ),
        (
            "uq_customers_active_branch_name",
            """
            CREATE UNIQUE INDEX IF NOT EXISTS uq_customers_active_branch_name
            ON customers (branch_id, lower(btrim(name)))
            WHERE active = true
              AND customer_type IS DISTINCT FROM 'walk_in'
              AND branch_id IS NOT NULL
              AND btrim(name) <> ''
            """,
        ),
        (
            "uq_customers_active_branch_email",
            """
            CREATE UNIQUE INDEX IF NOT EXISTS uq_customers_active_branch_email
            ON customers (branch_id, lower(btrim(email)))
            WHERE active = true
              AND customer_type IS DISTINCT FROM 'walk_in'
              AND branch_id IS NOT NULL
              AND email IS NOT NULL
              AND btrim(email) <> ''
            """,
        ),
        (
            "uq_customers_active_branch_phone",
            """
            CREATE UNIQUE INDEX IF NOT EXISTS uq_customers_active_branch_phone
            ON customers (
                branch_id,
                regexp_replace(coalesce(phone, ''), '[^0-9]', '', 'g')
            )
            WHERE active = true
              AND customer_type IS DISTINCT FROM 'walk_in'
              AND branch_id IS NOT NULL
              AND phone IS NOT NULL
              AND regexp_replace(phone, '[^0-9]', '', 'g') <> ''
            """,
        ),
    ]

    with engine.begin() as conn:
        for label, ddl in statements:
            conn.execute(text(ddl))
            print(f"OK: {label}")

    print("Customer duplicate-prevention indexes ready.")


if __name__ == "__main__":
    run()
