"""Ingest the 3 prodaja files into erp_transactions.

Strategy: DELETE rows in [min(file_date), max(file_date)] window, then
re-insert via UploadService. Refresh materialized views afterwards.
"""
from __future__ import annotations

import sys
from pathlib import Path

sys.stdout.reconfigure(encoding="utf-8")

from sqlalchemy import text

from backend.models.database import SessionLocal
from backend.repositories.upload_repo import parse_excel_file
from backend.services.upload_service import UploadService


FILES = [
    "data/prodaja/prodajacro2mj.xlsx",
    "data/prodaja/prodajaslo2mj.xlsx",
    "data/prodaja/prodajaasutri2mjj.xlsx",
]


def main() -> int:
    db = SessionLocal()
    try:
        # --- Probe state before ---
        pg_min, pg_max, pg_n_before = db.execute(
            text("SELECT MIN(transaction_date), MAX(transaction_date), COUNT(*) FROM erp_transactions")
        ).first()
        print(f"Before: {pg_n_before:,} rows in erp_transactions, {pg_min} → {pg_max}")

        # --- Find the file date window ---
        file_bytes: list[tuple[str, bytes]] = []
        all_min, all_max = None, None
        total_parsed = 0
        for fp in FILES:
            p = Path(fp)
            if not p.exists():
                print(f"  MISSING: {fp}")
                continue
            data = p.read_bytes()
            file_bytes.append((p.name, data))
            parsed = parse_excel_file(data, filename=p.name)
            df = parsed["df"]
            total_parsed += len(df)
            dmin, dmax = df["date"].min(), df["date"].max()
            print(f"  {p.name:30s} parsed_rows={len(df):>7,}  {dmin.date()} → {dmax.date()}")
            if all_min is None or dmin < all_min: all_min = dmin
            if all_max is None or dmax > all_max: all_max = dmax

        if all_min is None:
            print("Nothing parsed.")
            return 1

        win_a, win_b = all_min.date(), all_max.date()
        print(f"\nFile window: {win_a} → {win_b}, parsed_total={total_parsed:,}")

        # --- Delete the overlap window ---
        deleted = db.execute(
            text("DELETE FROM erp_transactions WHERE transaction_date BETWEEN :a AND :b"),
            {"a": win_a, "b": win_b},
        ).rowcount
        db.commit()
        print(f"Deleted {deleted:,} rows in [{win_a} .. {win_b}]")

        # --- Drive UploadService with the same bytes ---
        result = UploadService(db).process_weekly_update(file_bytes)
        print("\nUploadService result:")
        for k, v in result.items():
            print(f"  {k}: {v}")

        # --- Probe state after ---
        pg_min2, pg_max2, pg_n_after = db.execute(
            text("SELECT MIN(transaction_date), MAX(transaction_date), COUNT(*) FROM erp_transactions")
        ).first()
        print(f"\nAfter:  {pg_n_after:,} rows in erp_transactions, {pg_min2} → {pg_max2}")
        print(f"Net:    {pg_n_after - pg_n_before:+,}")

        return 0 if not result.get("warnings") else 0
    finally:
        db.close()


if __name__ == "__main__":
    sys.exit(main())