#!/usr/bin/env python3
from __future__ import annotations

import argparse
import asyncio
import json
import os
import sys
from datetime import UTC, datetime
from pathlib import Path
from typing import Any

REPO_ROOT = Path(__file__).resolve().parents[1]
API_ROOT = REPO_ROOT / "apps" / "api"
sys.path.insert(0, str(API_ROOT))

import asyncpg  # noqa: E402

from app.services.phase3_kpi_export import (  # noqa: E402
    KPI_REPORT_PATH,
    PREPOST_CSV_PATH,
    ParticipantKeys,
    build_kpi_report,
    build_prepost_csv_rows,
    write_kpi_report,
    write_prepost_csv,
)


PREPOST_QUERY = """
WITH ranked AS (
  SELECT
    learner_id::text AS learner_id,
    pilot_id,
    measure_name,
    timepoint,
    raw_score,
    min_score,
    max_score,
    instrument_version,
    item_count,
    collected_at,
    updated_at,
    row_number() OVER (
      PARTITION BY learner_id, measure_name, timepoint
      ORDER BY updated_at DESC, collected_at DESC, instrument_version DESC
    ) AS rn
  FROM app.learner_prepost_measure
  WHERE pilot_id = $1
    AND ($2::timestamptz IS NULL OR collected_at >= $2::timestamptz)
    AND ($3::timestamptz IS NULL OR collected_at < $3::timestamptz)
)
SELECT
  learner_id,
  pilot_id,
  measure_name,
  timepoint,
  raw_score,
  min_score,
  max_score,
  instrument_version,
  item_count,
  collected_at,
  updated_at
FROM ranked
WHERE rn = 1
ORDER BY learner_id, measure_name, timepoint
"""


def parse_time(value: str | None) -> datetime | None:
    if not value:
        return None
    parsed = datetime.fromisoformat(value.replace("Z", "+00:00"))
    if parsed.tzinfo is None:
        parsed = parsed.replace(tzinfo=UTC)
    return parsed


def now_utc() -> str:
    return datetime.now(UTC).isoformat().replace("+00:00", "Z")


async def fetch_rows(args: argparse.Namespace) -> list[dict[str, Any]]:
    conn = await asyncpg.connect(args.database_url)
    try:
        records = await conn.fetch(
            PREPOST_QUERY,
            args.pilot_id,
            parse_time(args.started_at),
            parse_time(args.ended_at),
        )
    finally:
        await conn.close()
    return [dict(record) for record in records]


async def run(args: argparse.Namespace) -> int:
    rows = await fetch_rows(args)
    keys = ParticipantKeys()
    csv_rows = build_prepost_csv_rows(rows, participant_keys=keys)
    generated_at = args.generated_at or now_utc()
    source_window = None
    if args.started_at or args.ended_at:
        source_window = {
            "started_at": args.started_at or "",
            "ended_at": args.ended_at or "",
        }
    report = build_kpi_report(
        rows,
        pilot_id=args.pilot_id,
        generated_at=generated_at,
        source_window=source_window,
        review_operator=args.operator,
    )
    output_root = Path(args.output_root)
    prepost_path = output_root / PREPOST_CSV_PATH
    report_path = output_root / KPI_REPORT_PATH
    write_prepost_csv(csv_rows, prepost_path)
    write_kpi_report(report, report_path)
    print(
        json.dumps(
            {
                "prepost_csv": str(prepost_path),
                "kpi_report": str(report_path),
                "rows": len(csv_rows),
                "participants": len(keys),
                "pilot_id": args.pilot_id,
            },
            ensure_ascii=False,
        )
    )
    return 0


def build_parser() -> argparse.ArgumentParser:
    parser = argparse.ArgumentParser(
        description="Export Phase 3 pre/post KPI evidence CSV and kpi_report.json from app.learner_prepost_measure."
    )
    parser.add_argument("--database-url", default=os.environ.get("DATABASE_URL"), required=os.environ.get("DATABASE_URL") is None)
    parser.add_argument("--output-root", default=str(REPO_ROOT / "data" / "phase3-dry-run"))
    parser.add_argument("--pilot-id", default="phase3-pilot-draft")
    parser.add_argument("--started-at")
    parser.add_argument("--ended-at")
    parser.add_argument("--generated-at")
    parser.add_argument("--operator", default="")
    return parser


def main() -> int:
    parser = build_parser()
    args = parser.parse_args()
    return asyncio.run(run(args))


if __name__ == "__main__":
    raise SystemExit(main())