#!/usr/bin/env python3
"""
Poll Supabase scan_details every POLL_INTERVAL_SEC (default 30), claim one row,
run scan.py if needed, then generate_reports_from_update.py --send-email.

Requires: pip install supabase
Env: SUPABASE_URL, SUPABASE_SERVICE_ROLE_KEY, plus SMTP / SCAN_REPORT_* (see docs/SCAN_QUEUE_WORKER.md).
"""
from __future__ import annotations

import json
import os
import subprocess
import sys
import tempfile
import time
import traceback
from datetime import datetime, timezone
from pathlib import Path
from urllib.parse import urlparse

# Repo root = parent of scripts/
REPO_ROOT = Path(__file__).resolve().parent.parent
SCAN_PY = REPO_ROOT / "scan.py"
GEN_PY = REPO_ROOT / "generate_reports_from_update.py"
POLL_INTERVAL_SEC = int(os.environ.get("SCAN_QUEUE_POLL_SEC", "30"))

# Import ops alert helper from report generator (same SMTP env).
if str(REPO_ROOT) not in sys.path:
    sys.path.insert(0, str(REPO_ROOT))
from generate_reports_from_update import (  # noqa: E402
    emails_already_sent,
    notify_ops_error,
    report_path_for_db,
    resolve_report_out_dir,
    send_ops_run_summary,
)
from advance_progress import build_report_summary, load_advance_config  # noqa: E402
from load_env import load_env_files, log_smtp_config  # noqa: E402


def target_name_from_url(url: str) -> str | None:
    p = urlparse((url or "").strip())
    host = (p.hostname or p.netloc or "").strip()
    if not host:
        return None
    return host.replace("www.", "", 1).split(".")[0].replace("-", " ").title()


def get_supabase():
    from supabase import create_client

    url = (os.environ.get("SUPABASE_URL") or "").strip()
    key = (os.environ.get("SUPABASE_SERVICE_ROLE_KEY") or "").strip()
    if not url or not key:
        raise SystemExit(
            "Set SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY (service_role, not anon)."
        )
    return create_client(url, key)


def claim_row(client):
    # supabase-py requires an explicit params dict (use {} for zero-arg RPCs).
    r = client.rpc("claim_next_scan_detail", {}).execute()
    rows = r.data or []
    if not rows:
        return None
    return rows[0]


def update_row(client, row_id: str, payload: dict) -> None:
    body = {**payload, "updated_at": datetime.now(timezone.utc).isoformat()}
    try:
        client.table("scan_details").update(body).eq("id", row_id).execute()
    except Exception as e:
        err = str(e)
        if "report_path" in body and ("PGRST204" in err or "report_path" in err):
            body = {k: v for k, v in body.items() if k != "report_path"}
            print(
                "[warn] scan_details.report_path column missing — "
                "run supabase/migrations/20260525130000_scan_details_report_path.sql; "
                "saved row without report_path",
                flush=True,
            )
            client.table("scan_details").update(body).eq("id", row_id).execute()
            return
        raise


def run_scan(
    target_url: str,
    target_name: str | None,
    scan_detail_id: str | None = None,
    advance_config_path: str | None = None,
) -> dict:
    fd, out_path = tempfile.mkstemp(suffix=".json", prefix="scan_")
    os.close(fd)
    out_path = Path(out_path)
    try:
        cmd = [
            sys.executable,
            str(SCAN_PY),
            "--target",
            target_url,
            "--output",
            str(out_path),
            "--threads",
            os.environ.get("SCAN_QUEUE_THREADS", "4"),
        ]
        if target_name:
            cmd.extend(["--target-name", target_name])
        if scan_detail_id:
            cmd.extend(["--scan-id", scan_detail_id])
        if advance_config_path:
            cmd.extend(["--advance-config", advance_config_path])
        cmd.extend(["--timeout", os.environ.get("SCAN_TOOL_TIMEOUT", "1800")])
        timeout = int(os.environ.get("SCAN_QUEUE_SCAN_TIMEOUT", "3600"))
        p = subprocess.run(
            cmd,
            cwd=str(REPO_ROOT),
            capture_output=True,
            text=True,
            timeout=timeout,
            env=os.environ.copy(),
        )
        if p.returncode != 0:
            err = (p.stderr or p.stdout or "").strip()[:4000]
            raise RuntimeError(f"scan.py exit {p.returncode}: {err}")
        return json.loads(out_path.read_text(encoding="utf-8"))
    finally:
        try:
            out_path.unlink(missing_ok=True)
        except OSError:
            pass


def run_report_email(
    json_path: Path,
    requester_email: str,
    requester_name: str | None = None,
    scan_detail_id: str | None = None,
) -> str | None:
    load_env_files(REPO_ROOT / "generate_reports_from_update.py")
    env = {**os.environ, "SCAN_REPORT_EMAIL_TO": requester_email}
    if requester_name:
        env["SCAN_REPORT_REQUESTER_NAME"] = requester_name
    if scan_detail_id:
        env["SCAN_REPORT_SCAN_ID"] = scan_detail_id
    timeout = int(os.environ.get("SCAN_QUEUE_EMAIL_TIMEOUT", "600"))
    cmd = [
        sys.executable,
        str(GEN_PY),
        "--input",
        str(json_path),
        "--send-email",
    ]
    if scan_detail_id:
        cmd.extend(["--scan-id", scan_detail_id])
    p = subprocess.run(
        cmd,
        cwd=str(REPO_ROOT),
        env=env,
        capture_output=True,
        text=True,
        timeout=timeout,
    )
    if p.returncode != 0:
        err = (p.stderr or p.stdout or "").strip()[:4000]
        raise RuntimeError(f"generate_reports exit {p.returncode}: {err}")
    out = (p.stdout or "") + "\n" + (p.stderr or "")
    for line in out.splitlines():
        if line.startswith("report_path="):
            return line.split("=", 1)[1].strip()
    if scan_detail_id:
        return f"Reports/generated/{scan_detail_id}"
    return None


def process_one(client, row: dict) -> None:
    row_id = str(row["id"])
    target_url = (row.get("target_url") or "").strip()
    requester = (row.get("requester_email") or "").strip()
    requester_name = (row.get("requester_name") or "").strip() or None
    if not target_url or not requester:
        msg = "Missing target_url or requester_email"
        update_row(
            client,
            row_id,
            {"status": "failed", "error_message": msg},
        )
        notify_ops_error(
            "queue validation",
            msg,
            script="scripts/scan_queue_worker.py",
            scan_detail_id=row_id,
            target_url=target_url or None,
        )
        return

    raw = row.get("result")
    if isinstance(raw, str):
        try:
            raw = json.loads(raw)
        except json.JSONDecodeError:
            raw = None
    data: dict | None = raw if isinstance(raw, dict) else None
    scan_saved = data is not None
    reports_dir = REPO_ROOT / "Reports"
    out_dir = resolve_report_out_dir(reports_dir, scan_detail_id=row_id)

    try:
        if not data:
            advance_cfg = load_advance_config(client, row_id)
            adv_path: str | None = None
            if advance_cfg:
                fd_adv, adv_tmp = tempfile.mkstemp(suffix=".json", prefix="advance_")
                os.close(fd_adv)
                adv_path = adv_tmp
                Path(adv_path).write_text(json.dumps(advance_cfg), encoding="utf-8")
            try:
                data = run_scan(
                    target_url,
                    target_name_from_url(target_url),
                    scan_detail_id=row_id,
                    advance_config_path=adv_path,
                )
            finally:
                if adv_path:
                    try:
                        Path(adv_path).unlink(missing_ok=True)
                    except OSError:
                        pass
            summary = build_report_summary(data.get("findings") or [])
            update_row(
                client,
                row_id,
                {
                    "result": data,
                    "report_summary": summary,
                    "completed_at": datetime.now(timezone.utc).isoformat(),
                },
            )
            scan_saved = True

        report_path: str | None = None
        if emails_already_sent(out_dir):
            report_path = (
                report_path_for_db(reports_dir, out_dir)
                if (out_dir / "manifest.json").is_file()
                else f"Reports/generated/{row_id}"
            )
            print(f"[skip] row {row_id}: emails already sent; updating DB only", flush=True)
        else:
            fd, tmp_name = tempfile.mkstemp(suffix=".json", prefix="report_")
            os.close(fd)
            tmp_path = Path(tmp_name)
            try:
                tmp_path.write_text(json.dumps(data), encoding="utf-8")
                report_path = run_report_email(
                    tmp_path,
                    requester,
                    requester_name,
                    scan_detail_id=row_id,
                )
            finally:
                try:
                    tmp_path.unlink(missing_ok=True)
                except OSError:
                    pass

        update_row(
            client,
            row_id,
            {
                "status": "completed",
                "email_sent_at": datetime.now(timezone.utc).isoformat(),
                "error_message": None,
                "report_path": report_path,
            },
        )
        send_ops_run_summary(
            data,
            scan_detail_id=row_id,
            target_url=target_url,
            outcome=f"Completed — report emailed to {requester}",
            requester=requester,
            report_path=report_path,
            reports_dir=reports_dir,
        )
        print(
            f"[ok] row {row_id} completed and emailed {requester}"
            + (f" (reports: {report_path})" if report_path else ""),
            flush=True,
        )
    except Exception as e:
        msg = f"{e}\n{traceback.format_exc()}"[:8000]
        stage = "report email" if scan_saved else "scan"
        if scan_saved and emails_already_sent(out_dir):
            try:
                report_path = (
                    report_path_for_db(reports_dir, out_dir)
                    if (out_dir / "manifest.json").is_file()
                    else None
                )
                payload = {
                    "status": "completed",
                    "email_sent_at": datetime.now(timezone.utc).isoformat(),
                    "error_message": None,
                }
                if report_path:
                    payload["report_path"] = report_path
                update_row(client, row_id, payload)
                send_ops_run_summary(
                    data or {},
                    scan_detail_id=row_id,
                    target_url=target_url,
                    outcome=f"Completed — report emailed to {requester} (recovered after DB error)",
                    requester=requester,
                    report_path=report_path,
                    reports_dir=reports_dir,
                )
                print(f"[recover] row {row_id} completed without re-sending emails", flush=True)
                return
            except Exception as recover_err:
                print(
                    f"[recover] row {row_id} emails sent but DB update failed: {recover_err}",
                    flush=True,
                )
                return

        send_ops_run_summary(
            data or {},
            scan_detail_id=row_id,
            target_url=target_url,
            outcome=f"Failed during {stage}",
            requester=requester,
            extra_issues=[msg],
            reports_dir=reports_dir,
        )
        if scan_saved:
            update_row(
                client,
                row_id,
                {"status": "pending", "error_message": msg},
            )
            print(f"[retry] row {row_id} set pending for retry: {e}", flush=True)
        else:
            update_row(
                client,
                row_id,
                {"status": "failed", "error_message": msg},
            )
            print(f"[fail] row {row_id}: {e}", flush=True)


def main() -> None:
    load_env_files(REPO_ROOT / "generate_reports_from_update.py")
    log_smtp_config()
    if not SCAN_PY.is_file():
        raise SystemExit(f"Missing {SCAN_PY}")
    if not GEN_PY.is_file():
        raise SystemExit(f"Missing {GEN_PY}")

    client = get_supabase()
    print(
        f"scan_queue_worker: poll every {POLL_INTERVAL_SEC}s, repo={REPO_ROOT}",
        flush=True,
    )

    while True:
        try:
            row = claim_row(client)
            if not row:
                time.sleep(POLL_INTERVAL_SEC)
                continue
            print(f"[claim] id={row.get('id')} target={row.get('target_url')}", flush=True)
            process_one(client, row)
        except KeyboardInterrupt:
            print("Stopped.", flush=True)
            raise SystemExit(0) from None
        except Exception as e:
            err = f"{e}\n{traceback.format_exc()}"[:8000]
            print(f"[loop error] {e}", flush=True)
            notify_ops_error(
                "worker main loop",
                err,
                script="scripts/scan_queue_worker.py",
            )
        time.sleep(POLL_INTERVAL_SEC)


if __name__ == "__main__":
    main()