"""Command line: python -m api.cli <command>

  init          create tables and reference data (idempotent)
  collect       run the collector (--all or --source KEY)
  export-ddl    write db/migrations/0001_init.sql for Supabase
  auto-publish  publish new candidates that pass the auto rule (needs AUTO_PUBLISH=1)
  rescore       re-judge stored candidates with the current relevance rules
  strip-text    remove raw_text from every row (used before committing the demo DB)
"""

from __future__ import annotations

import argparse
import json
import sys

from sqlalchemy import update
from sqlalchemy.schema import CreateIndex, CreateTable

from .app.config import ROOT, get_settings
from .app.db import get_engine, metadata, now, raw_reports
from .app.services import collect
from .app.services.bootstrap import bootstrap


def export_ddl() -> str:
    from sqlalchemy.dialects import postgresql
    out = ["-- Generated by `python -m api.cli export-ddl` from api/app/db.py. Do not edit by hand.",
           "-- Target: Supabase (PostgreSQL). Lists are JSON (see ADR 0001).", ""]
    for t in metadata.sorted_tables:
        out.append(str(CreateTable(t).compile(dialect=postgresql.dialect())).strip() + ";\n")
        for ix in t.indexes:
            out.append(str(CreateIndex(ix).compile(dialect=postgresql.dialect())).strip() + ";\n")
    out.append("-- Public access: the API uses a server-side connection. Keep RLS on and add no public policies.")
    out += [f"ALTER TABLE {t.name} ENABLE ROW LEVEL SECURITY;" for t in metadata.sorted_tables]
    return "\n".join(out) + "\n"


def main(argv=None) -> int:
    ap = argparse.ArgumentParser(prog="api.cli")
    sub = ap.add_subparsers(dest="cmd", required=True)
    sub.add_parser("init")
    p = sub.add_parser("collect")
    p.add_argument("--all", action="store_true")
    p.add_argument("--source")
    p.add_argument("--limit", type=int, default=60)
    p.add_argument("--since-days", type=int, default=None, help="walk older feed pages back this many days")
    p.add_argument("--trigger", default="schedule", choices=["schedule", "manual"])
    sub.add_parser("export-ddl")
    sub.add_parser("rescore")
    sub.add_parser("auto-publish")
    sub.add_parser("retention")
    sub.add_parser("strip-text")
    a = ap.parse_args(argv)

    if a.cmd == "export-ddl":
        path = ROOT / "db" / "migrations" / "0001_init.sql"
        path.parent.mkdir(parents=True, exist_ok=True)
        path.write_text(export_ddl(), encoding="utf-8", newline="\n")
        print(f"wrote {path}")
        return 0

    engine = get_engine()
    print(f"backend={get_settings().backend}")
    if a.cmd == "init":
        print(json.dumps(bootstrap(engine)))
    elif a.cmd == "collect":
        bootstrap(engine)
        if a.all:
            res = collect.run_all(engine, trigger=a.trigger, limit=a.limit, since_days=a.since_days)
        elif a.source:
            res = [collect.run_source(engine, a.source, trigger=a.trigger, batch_label="cli", limit=a.limit, since_days=a.since_days)]
        else:
            ap.error("collect needs --all or --source KEY")
        print(json.dumps(res, indent=2, default=str))
        return 1 if any(r.get("status") == "failed" for r in res) else 0
    elif a.cmd == "retention":
        from .app.services import retention
        print(json.dumps(retention.run(engine), default=str))
    elif a.cmd == "auto-publish":
        if not get_settings().auto_publish:
            print("Auto-publish is off. Set AUTO_PUBLISH=1 to turn it on.")
            return 2
        from .app.services import auto
        print(json.dumps(auto.run_pending(engine)))
    elif a.cmd == "rescore":
        print(json.dumps(collect.rescore(engine)))
    elif a.cmd == "strip-text":
        with engine.begin() as c:
            n = c.execute(update(raw_reports).where(raw_reports.c.raw_text.is_not(None))
                          .values(raw_text=None, raw_text_purged_at=now())).rowcount
        print(f"stripped raw_text from {n} rows")
    return 0


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