"""Backfill `erp_transactions.has_loyalty` by re-reading the rekapitulacija
files for the 'Loyalty kartica' column and updating each transaction.

We can't UPDATE individual rows efficiently without a stable join key —
the rekapitulacija has no unique id matching erp_transactions.id. The cleanest
backfill is to truncate-and-reload, but that's heavy. As a pragmatic
alternative we update via (document, product_id, transaction_date) match,
which is stable enough — duplicate matches within a doc just get the same
flag.

Run path: python db/backfill_has_loyalty.py
"""
import sys
sys.stdout.reconfigure(encoding="utf-8")
from pathlib import Path
import pandas as pd
import re
from backend.models.database import SessionLocal
from sqlalchemy import text

# Match canonical loader logic
LOYALTY_DIGITS = re.compile(r"^\d{6,}$")


def find_files() -> list[Path]:
    return sorted(Path("data").glob("*ekapitulac*.xlsx"))


def main():
    files = find_files()
    print(f"Files to scan: {len(files)}")
    for p in files:
        print(f"  - {p.name}")

    db = SessionLocal()
    try:
        # Build (document, sku, date) → has_loyalty map from xlsx
        loyalty_map: dict[tuple[str, str, str], bool] = {}
        total_xlsx_rows = 0
        for p in files:
            print(f"\n--- reading {p.name} ---")
            df = pd.read_excel(p, dtype={"Loyalty kartica": str})
            print(f"  rows: {len(df)}")
            total_xlsx_rows += len(df)
            # Normalise
            if "Dokument" not in df.columns or "Artikal" not in df.columns:
                print("  (missing Dokument/Artikal — skip)")
                continue
            if "Loyalty kartica" not in df.columns:
                print("  (no Loyalty kartica column — skip)")
                continue
            dates = pd.to_datetime(df["Datum"], errors="coerce")
            n_with_loyalty = 0
            for doc, sku, dt, lk in zip(
                df["Dokument"].fillna("").astype(str).str.strip(),
                df["Artikal"].fillna("").astype(str).str.strip(),
                dates,
                df["Loyalty kartica"].fillna("").astype(str).str.strip(),
            ):
                if not doc or not sku or pd.isna(dt):
                    continue
                has_l = bool(LOYALTY_DIGITS.match(lk))
                if has_l:
                    n_with_loyalty += 1
                key = (doc, sku, dt.date().isoformat())
                # If multiple rows share the same (doc, sku, date) we OR them
                loyalty_map[key] = loyalty_map.get(key, False) or has_l
            print(f"  rows flagged loyalty: {n_with_loyalty}")
        print(f"\nUnique (doc, sku, date) keys: {len(loyalty_map)}")
        n_loyalty_keys = sum(1 for v in loyalty_map.values() if v)
        print(f"Keys with loyalty=TRUE: {n_loyalty_keys}")

        if not loyalty_map:
            print("Nothing to backfill.")
            return

        # Stage in a temp table and UPDATE in one go — much faster than per-row.
        print("\nStaging temp table…")
        db.execute(text("DROP TABLE IF EXISTS _loyalty_stage"))
        db.execute(text("""
            CREATE TEMP TABLE _loyalty_stage (
                document VARCHAR,
                sku VARCHAR,
                transaction_date DATE,
                has_loyalty BOOLEAN
            )
        """))

        # Bulk insert via raw cursor
        raw_conn = db.connection().connection
        import io
        buf = io.StringIO()
        for (doc, sku, dt), has_l in loyalty_map.items():
            # Escape tabs and backslashes
            doc_e = doc.replace("\\", "\\\\").replace("\t", " ")
            sku_e = sku.replace("\\", "\\\\").replace("\t", " ")
            buf.write(f"{doc_e}\t{sku_e}\t{dt}\t{'t' if has_l else 'f'}\n")
        buf.seek(0)
        with raw_conn.cursor() as cur:
            cur.copy_expert(
                "COPY _loyalty_stage (document, sku, transaction_date, has_loyalty) FROM STDIN WITH (FORMAT text)",
                buf,
            )
        n_staged = db.execute(text("SELECT COUNT(*) FROM _loyalty_stage")).scalar()
        print(f"  staged {n_staged} rows")

        # UPDATE join
        print("\nApplying UPDATE…")
        result = db.execute(text("""
            UPDATE erp_transactions et
            SET has_loyalty = ls.has_loyalty
            FROM _loyalty_stage ls
            JOIN dim_products p ON p.sku = ls.sku
            WHERE et.product_id = p.id
              AND et.document = ls.document
              AND et.transaction_date = ls.transaction_date
        """))
        print(f"  rows updated: {result.rowcount}")
        db.commit()

        # Stats
        r = db.execute(text("""
            SELECT
                COUNT(*) AS n,
                SUM(CASE WHEN has_loyalty THEN 1 ELSE 0 END) AS n_loyalty
            FROM erp_transactions
        """)).mappings().first()
        print(f"\n=== After backfill ===")
        print(f"  total erp_transactions: {r['n']:,}")
        print(f"  with has_loyalty=TRUE:  {r['n_loyalty']:,} "
              f"({100*r['n_loyalty']/r['n']:.1f}%)")

    finally:
        db.close()


if __name__ == "__main__":
    main()