"""
Push scraped products + prices to the Baskit Ops ingestion API.

Reads each retailer's existing SQLite `products` table (written by the unchanged
scrapers) and runs one ingestion cycle per retailer:

    open run -> products:batch -> prices:batch -> close run

Notes on the Ops contract:
- products:batch registers each retailer SKU with full scrape metadata (barcode,
  brand, image_url, category, ratings, etc.) plus required size.
- prices:batch records observations for matched SKUs; includes was_price and
  on_promotion when the scraper captured them.
- size is REQUIRED per product; rows with no derivable size are skipped locally.

  python push_to_ops.py                 # push all configured retailers
  python push_to_ops.py --retailers pnp,woolworths
  python push_to_ops.py --dry-run       # build payloads, print counts, no HTTP
  python push_to_ops.py --limit 20      # cap rows per retailer (smoke test)
"""
from __future__ import annotations

import argparse
import sqlite3
from pathlib import Path

import ops_config as cfg
import size_extract
from ingest_client import IngestClient, IngestError
import alerts


def _str(val) -> str | None:
    if val is None:
        return None
    s = str(val).strip()
    return s or None


def _float(val) -> float | None:
    if val is None or val == "":
        return None
    try:
        return float(val)
    except (TypeError, ValueError):
        return None


def _int(val) -> int | None:
    if val is None or val == "":
        return None
    try:
        return int(val)
    except (TypeError, ValueError):
        return None


def _promo(val) -> bool | None:
    if val is None or val == "":
        return None
    if isinstance(val, bool):
        return val
    if val in (1, "1", "true", "True", "yes"):
        return True
    if val in (0, "0", "false", "False", "no"):
        return False
    return None


def _scrape_fields(**kwargs) -> dict:
    """Drop empty optional scrape fields before POST."""
    out: dict = {}
    for key, val in kwargs.items():
        if val is None:
            continue
        if isinstance(val, str) and not val.strip():
            continue
        out[key] = val
    return out


def _product_base(sku: str, name: str, size: str, **scrape) -> dict:
    row = {"sku": sku, "name": name, "size": size}
    row.update(_scrape_fields(**scrape))
    return row


def _price_row(
    sku: str,
    price,
    in_stock: str,
    *,
    was_price=None,
    on_promotion=None,
    currency=None,
) -> dict | None:
    value = _float(price)
    if value is None or value <= 0:
        return None
    row: dict = {"sku": sku, "price": round(value, 2), "in_stock": in_stock}
    was = _float(was_price)
    if was is not None and was > 0:
        row["was_price"] = round(was, 2)
    promo = _promo(on_promotion)
    if promo is not None:
        row["on_promotion"] = promo
    cur = _str(currency)
    if cur:
        row["currency"] = cur.upper()[:8]
    return row


def _pnp_map(r: sqlite3.Row) -> tuple[dict | None, dict | None]:
    sku = (r["code"] or "").strip()
    name = (r["name"] or "").strip()
    if not sku or not name:
        return None, None
    size = size_extract.derive(name, r["unit_of_measure"], r["average_weight"], r["quantity_type"])
    if not size:
        return None, None
    product = _product_base(
        sku, name, size,
        barcode=_str(r["barcode"]),
        brand=_str(r["brand"]),
        manufacturer=_str(r["manufacturer"]),
        image_url=_str(r["primary_image_url"]),
        product_url=_str(r["url"]),
        category_raw=_str(r["category_path"]),
        unit_of_measure=_str(r["unit_of_measure"]),
        average_rating=_float(r["average_rating"]),
        number_of_reviews=_int(r["number_of_reviews"]),
        scraped_at=_str(r["scraped_at"]),
    )
    price = _price_row(
        sku, r["price_value"], _pnp_stock(r["stock_status"]),
        currency=_str(r["price_currency"]),
    )
    return product, price


def _checkers_map(r: sqlite3.Row) -> tuple[dict | None, dict | None]:
    sku = (r["id"] or "").strip()
    name = (r["name"] or "").strip()
    if not sku or not name:
        return None, None
    size = size_extract.derive(name, r["unit_of_measure"])
    if not size:
        return None, None
    product = _product_base(
        sku, name, size,
        barcode=_str(r["barcode"]),
        brand=_str(r["brand"]),
        image_url=_str(r["primary_image_url"]),
        product_url=_str(r["url"]),
        category_raw=_str(r["buckets"]) or _str(r["merchandise_cat"]),
        unit_of_measure=_str(r["unit_of_measure"]),
        scraped_at=_str(r["scraped_at"]),
    )
    price = _price_row(
        sku, r["price_value"], _checkers_stock(r["out_of_stock"], r["stock_on_hand"]),
        was_price=r["was_price"],
        on_promotion=r["on_promotion"],
        currency=_str(r["currency"]),
    )
    return product, price


def _woolworths_map(r: sqlite3.Row) -> tuple[dict | None, dict | None]:
    sku = (r["id"] or "").strip()
    name = (r["name"] or "").strip()
    if not sku or not name:
        return None, None
    size = size_extract.derive(name)
    if not size:
        return None, None
    dept = _str(r["department"])
    ptype = _str(r["product_type"])
    category = " / ".join(x for x in (dept, ptype) if x) or _str(r["buckets"])
    product = _product_base(
        sku, name, size,
        barcode=_str(r["barcode"]) or sku,
        brand=_str(r["brand"]),
        image_url=_str(r["primary_image_url"]),
        product_url=_str(r["url"]),
        category_raw=category,
        average_rating=_float(r["average_rating"]),
        number_of_reviews=_int(r["number_of_reviews"]),
        scraped_at=_str(r["scraped_at"]),
    )
    price = _price_row(
        sku, r["price_value"], "yes",
        was_price=r["was_price"],
        on_promotion=r["on_promotion"],
    )
    return product, price


RETAILER_QUERIES = {
    "pnp": (
        "SELECT code, name, brand, barcode, manufacturer, price_value, price_currency, "
        "unit_of_measure, quantity_type, average_weight, stock_status, primary_image_url, "
        "category_path, url, average_rating, number_of_reviews, scraped_at FROM products",
        _pnp_map,
    ),
    "checkers": (
        "SELECT id, barcode, name, brand, price_value, was_price, on_promotion, currency, "
        "unit_of_measure, out_of_stock, stock_on_hand, primary_image_url, buckets, "
        "merchandise_cat, url, scraped_at FROM products",
        _checkers_map,
    ),
    "woolworths": (
        "SELECT id, barcode, name, brand, price_value, was_price, on_promotion, "
        "department, product_type, primary_image_url, buckets, url, average_rating, "
        "number_of_reviews, scraped_at FROM products",
        _woolworths_map,
    ),
}


def _pnp_stock(status: str | None) -> str:
    s = (status or "").lower()
    if "out" in s:
        return "no"
    if "low" in s:
        return "low"
    return "yes"


def _checkers_stock(out_of_stock, stock_on_hand) -> str:
    if out_of_stock in (1, "1", True):
        return "no"
    if stock_on_hand is not None and int(stock_on_hand) <= 0:
        return "no"
    return "yes"


def build_payloads(retailer: str, limit: int = 0) -> tuple[list[dict], list[dict], int]:
    """Read a retailer SQLite DB into (products, prices, skipped_no_size)."""
    db_path: Path = cfg.RETAILER_DBS[retailer]
    if not db_path.exists():
        raise SystemExit(f"[{retailer}] SQLite DB not found at {db_path} — run the scraper first.")
    query, mapper = RETAILER_QUERIES[retailer]

    conn = sqlite3.connect(f"file:{db_path}?mode=ro", uri=True)
    conn.row_factory = sqlite3.Row
    products: list[dict] = []
    prices: list[dict] = []
    skipped_no_size = 0
    seen: set[str] = set()
    try:
        for r in conn.execute(query):
            product, price = mapper(r)
            sku = (product or price or {}).get("sku")
            if not sku or sku in seen:
                continue
            seen.add(sku)
            if product is None:
                skipped_no_size += 1
                continue
            products.append(product)
            if price is not None:
                prices.append(price)
            if limit and len(seen) >= limit:
                break
    finally:
        conn.close()
    return products, prices, skipped_no_size


def push_retailer(client: IngestClient, retailer: str, limit: int = 0,
                  dry_run: bool = False) -> None:
    retailer_id = cfg.RETAILER_IDS[retailer]
    products, prices, skipped = build_payloads(retailer, limit=limit)
    print(f"[{retailer}] retailer_id={retailer_id} products={len(products)} "
          f"prices={len(prices)} skipped_no_size={skipped}")
    if dry_run:
        if products:
            print(f"[{retailer}] sample product keys: {sorted(products[0].keys())}")
        print(f"[{retailer}] dry-run — nothing sent")
        return
    if not products and not prices:
        print(f"[{retailer}] nothing to send")
        return

    run_id = client.open_run(retailer_id)
    print(f"[{retailer}] opened run #{run_id}")
    push_msg = f"Pushing {len(products)} products / {len(prices)} prices"
    alerts.track_job(retailer, "push", run_id=run_id, message=push_msg)
    try:
        with alerts.heartbeat_keepalive(
            300.0, retailer=retailer, stage="push", message=push_msg, run_id=run_id,
        ):
            if products:
                n = client.post_in_chunks("products", run_id, retailer_id, products)
                print(f"[{retailer}] products sent: {n}")
            if prices:
                n = client.post_in_chunks("prices", run_id, retailer_id, prices)
                print(f"[{retailer}] prices sent: {n}")
            result = client.close_run(run_id)
    except IngestError as exc:
        alerts.report(str(exc), stage="push", retailer=retailer, run_id=run_id, exc=exc)
        try:
            client.close_run(run_id)
            print(f"[{retailer}] closed run #{run_id} after push failure (partial data)")
        except IngestError:
            pass
        raise
    finally:
        # run_pipeline_v2 also clear_job's; this keeps CLI push_to_ops heartbeats honest.
        alerts.clear_job(retailer)
    acc = result.get("accounting", {})
    print(f"[{retailer}] closed run #{run_id}: status={result.get('status')} "
          f"reconciles={result.get('reconciles')} accounting={acc}")


def main() -> None:
    p = argparse.ArgumentParser(description="Push scraped catalogue to Baskit Ops ingestion API")
    p.add_argument("--retailers", default="",
                   help="comma-separated subset (default: BASKIT_RETAILERS)")
    p.add_argument("--limit", type=int, default=0, help="cap rows per retailer (smoke test)")
    p.add_argument("--dry-run", action="store_true", help="build payloads only; no HTTP")
    args = p.parse_args()

    retailers = [r.strip() for r in (args.retailers or ",".join(cfg.RETAILERS)).split(",") if r.strip()]
    unknown = [r for r in retailers if r not in RETAILER_QUERIES]
    if unknown:
        raise SystemExit(f"Unknown retailer(s): {', '.join(unknown)} (supported: {', '.join(RETAILER_QUERIES)})")

    client = None if args.dry_run else IngestClient()
    for retailer in retailers:
        try:
            push_retailer(client, retailer, limit=args.limit, dry_run=args.dry_run)
        except IngestError:
            pass  # reported in push_retailer via run_pipeline_v2 or alerts in push_retailer


if __name__ == "__main__":
    main()