"""
Hourly Baskit pipeline orchestrator.

Runs retailer scrapers, rebuilds the unified catalogue, exports comparison CSVs,
and optionally loads results into MySQL.

  python run_pipeline.py run          # one full cycle
  python run_pipeline.py --schedule   # repeat every BASKIT_PIPELINE_INTERVAL_HOURS
"""
from __future__ import annotations

import argparse
import gzip
import logging
import shutil
import sqlite3
import subprocess
import sys
import time
import urllib.request
from datetime import datetime, timezone
from pathlib import Path

import schedule

import config

ROOT = config.ROOT
LOG = logging.getLogger("baskit.pipeline")

SCRAPER_MAP = {
    "pnp": ("pnp_scraper.py", config.PNP_DELAY),
    "checkers": ("checkers_scraper.py", config.CHECKERS_DELAY),
    "woolworths": ("woolworths_scraper.py", config.WOOLWORTHS_DELAY),
}

_running = False


def _ts() -> str:
    return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")


def _run(cmd: list[str], label: str) -> None:
    """Run a subprocess; raise on non-zero exit."""
    LOG.info("[%s] %s", label, " ".join(cmd))
    result = subprocess.run(cmd, cwd=ROOT, check=False)
    if result.returncode != 0:
        raise RuntimeError(f"{label} failed (exit {result.returncode})")


def _python() -> str:
    return sys.executable


def _download_pnp_db() -> bool:
    """Fetch pnp.db from BASKIT_PNP_DOWNLOAD_URL when the working copy is empty."""
    url = config.PNP_DOWNLOAD_URL.strip()
    if not url:
        return False
    config.PNP_DB.parent.mkdir(parents=True, exist_ok=True)
    target = config.PNP_DB
    tmp = target.with_suffix(target.suffix + ".download")
    LOG.info("[pnp] downloading seed from %s", url)
    urllib.request.urlretrieve(url, tmp)
    with tmp.open("rb") as fh:
        magic = fh.read(2)
    if url.rstrip("/").endswith(".gz") or magic == b"\x1f\x8b":
        with gzip.open(tmp, "rb") as src, target.open("wb") as dst:
            shutil.copyfileobj(src, dst)
        tmp.unlink(missing_ok=True)
    else:
        tmp.replace(target)
    conn = sqlite3.connect(f"file:{config.PNP_DB}?mode=ro", uri=True)
    n = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
    conn.close()
    LOG.info("[pnp] download complete — %s products", n)
    return n > 0


def _copy_pnp_seed(seed: Path) -> None:
    config.PNP_DB.parent.mkdir(parents=True, exist_ok=True)
    for side in (config.PNP_DB, config.PNP_DB.with_suffix(config.PNP_DB.suffix + "-shm"),
                 config.PNP_DB.with_suffix(config.PNP_DB.suffix + "-wal")):
        side.unlink(missing_ok=True)
    shutil.copy2(seed, config.PNP_DB)
    LOG.info("[pnp] seeded from %s", seed)


def _ensure_pnp_db() -> None:
    """Create pnp.db with schema when missing (before build or scrape)."""
    if config.PNP_FORCE_SEED and config.PNP_SEED_PATH:
        seed = Path(config.PNP_SEED_PATH)
        if seed.is_file():
            _copy_pnp_seed(seed)
            return
    if config.PNP_DB.exists():
        conn = sqlite3.connect(f"file:{config.PNP_DB}?mode=ro", uri=True)
        n = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
        conn.close()
        if n > 0:
            return
    elif config.PNP_SEED_PATH:
        seed = Path(config.PNP_SEED_PATH)
        if seed.is_file():
            _copy_pnp_seed(seed)
            return
    if _download_pnp_db():
        return
    if config.PNP_DB.exists():
        return
    from db_init import init_pnp_db
    init_pnp_db()


def _pnp_product_count() -> int:
    if not config.PNP_DB.exists():
        return 0
    conn = sqlite3.connect(f"file:{config.PNP_DB}?mode=ro", uri=True)
    n = conn.execute("SELECT COUNT(*) FROM products").fetchone()[0]
    conn.close()
    return int(n)


def _prepare_pnp_scrape() -> None:
    """Seed PnP from sitemaps when the database has no product queue yet."""
    from pnp_scraper import db_connect
    _ensure_pnp_db()
    conn = db_connect()
    seed_count = conn.execute("SELECT COUNT(*) FROM seeds").fetchone()[0]
    conn.close()
    if seed_count == 0:
        _run([_python(), "pnp_scraper.py", "fetch-sitemaps"], "scrape/pnp-sitemaps")


def scrape_retailer(retailer: str, refresh: bool) -> None:
    """Scrape a single retailer (checkers, woolworths, or pnp)."""
    if retailer not in SCRAPER_MAP:
        LOG.warning("unknown retailer %r — skipped", retailer)
        return
    script, delay = SCRAPER_MAP[retailer]
    if retailer == "pnp":
        _prepare_pnp_scrape()
    cmd = [_python(), script, "scrape", "--delay", str(delay)]
    use_refresh = refresh
    if retailer == "pnp":
        use_refresh = refresh and config.PNP_REFRESH
        if refresh and not config.PNP_REFRESH:
            LOG.info(
                "[pnp] skipping --refresh (%s products seeded; set BASKIT_PNP_REFRESH=true to re-fetch)",
                _pnp_product_count(),
            )
    if use_refresh:
        cmd.append("--refresh")
    if retailer == "checkers" and not config.HEADLESS:
        cmd.append("--headed")
    _run(cmd, f"scrape/{retailer}")


def scrape_retailers(refresh: bool) -> None:
    """Scrape each retailer configured in BASKIT_RETAILERS."""
    for retailer in config.RETAILERS:
        scrape_retailer(retailer, refresh)


def build_catalogue() -> None:
    """Rebuild baskit.db, MVP subset, and price-comparison CSV."""
    py = _python()
    _run([py, "baskit_match.py", "build"], "match/build")
    _run([py, "baskit_match.py", "mvp"], "match/mvp")
    _run(
        [py, "baskit_match.py", "compare",
         "--min-retailers", str(config.COMPARE_MIN_RETAILERS)],
        "match/compare",
    )


def load_mysql() -> None:
    """Push baskit.db into the configured MySQL database."""
    if not config.RUN_MYSQL_LOAD:
        LOG.info("[mysql] skipped (BASKIT_RUN_MYSQL_LOAD=false)")
        return
    if _pnp_product_count() == 0:
        LOG.warning("[mysql] skipped — pnp.db has 0 products (would wipe remote PnP data)")
        return
    _run([_python(), "load_mysql.py"], "mysql/load")


def run_pipeline(refresh: bool | None = None) -> None:
    """Execute one full pipeline cycle."""
    global _running
    if _running:
        LOG.warning("[%s] previous run still in progress — skipping", _ts())
        return
    _running = True
    refresh = config.REFRESH if refresh is None else refresh
    started = time.monotonic()
    LOG.info("[%s] pipeline start (retailers=%s refresh=%s)",
             _ts(), ",".join(config.RETAILERS), refresh)
    try:
        config.ensure_dirs()
        _ensure_pnp_db()
        scrape_retailers(refresh)
        build_catalogue()
        load_mysql()
        elapsed = time.monotonic() - started
        LOG.info("[%s] pipeline done in %.0fs", _ts(), elapsed)
    except Exception:
        LOG.exception("[%s] pipeline failed", _ts())
        raise
    finally:
        _running = False


def cmd_seed(args: argparse.Namespace) -> None:
    """Download/copy pnp.db, rebuild catalogue, sync MySQL — no scrape."""
    config.ensure_dirs()
    _ensure_pnp_db()
    build_catalogue()
    load_mysql()


def cmd_run(args: argparse.Namespace) -> None:
    run_pipeline(refresh=args.refresh if args.refresh else None)


def cmd_schedule(args: argparse.Namespace) -> None:
    interval = args.interval or config.PIPELINE_INTERVAL_HOURS
    refresh = args.refresh if args.refresh else config.REFRESH
    LOG.info("scheduling every %.2f hour(s); refresh=%s", interval, refresh)

    def _job() -> None:
        try:
            run_pipeline(refresh=refresh)
        except Exception:
            pass  # logged inside run_pipeline; keep scheduler alive

    schedule.every(interval).hours.do(_job)
    _job()  # run immediately on start
    while True:
        schedule.run_pending()
        time.sleep(30)


def main() -> None:
    logging.basicConfig(
        level=logging.INFO,
        format="%(asctime)s %(levelname)s %(message)s",
        datefmt="%Y-%m-%d %H:%M:%S",
    )
    p = argparse.ArgumentParser(description="Baskit hourly pipeline orchestrator")
    p.add_argument("--schedule", action="store_true",
                   help="run on an hourly loop (default interval from .env)")
    p.add_argument("--interval", type=float, default=0,
                   help="hours between runs (overrides BASKIT_PIPELINE_INTERVAL_HOURS)")
    p.add_argument("--refresh", action="store_true",
                   help="force --refresh on scrapers this cycle")
    sub = p.add_subparsers(dest="cmd")
    sub.add_parser("run", help="run one full pipeline cycle").set_defaults(func=cmd_run)
    sub.add_parser("seed", help="seed pnp.db and sync MySQL without scraping").set_defaults(func=cmd_seed)
    args = p.parse_args()

    if args.schedule:
        cmd_schedule(args)
    elif args.cmd == "run" or args.cmd is None:
        cmd_run(args)
    else:
        args.func(args)


if __name__ == "__main__":
    main()