"""
scripts/db_smoke_test.py
========================
Quick DB sanity check. Run BEFORE starting the FastAPI server to confirm:
  • Postgres is reachable.
  • The participant table exists.
  • An access_key lookup returns the expected columns.

    cd people_hub_candidate
    python -m scripts.db_smoke_test                # just probe + describe
    python -m scripts.db_smoke_test DNPAW2         # also try a real lookup
"""
from __future__ import annotations

import sys
from pathlib import Path

# Allow running from project root: `python -m scripts.db_smoke_test`
_HERE = Path(__file__).resolve().parent.parent
if str(_HERE) not in sys.path:
    sys.path.insert(0, str(_HERE))

from config import settings                  # noqa: E402
from utils import database as db             # noqa: E402
from services import participant_repo        # noqa: E402
from utils.logger import get_logger          # noqa: E402

log = get_logger("db_smoke")


def main() -> int:
    log.info("DB target → host=%s port=%s db=%s user=%s",
             settings.DB_HOST, settings.DB_PORT, settings.DB_NAME, settings.DB_USER)

    try:
        db.init_pool()
    except Exception as exc:
        log.error("Pool init FAILED: %s", exc)
        return 1

    # 1. SELECT 1
    try:
        ping = db.fetch_one("SELECT 1 AS ping;")
        log.info("Probe SELECT 1 → %s", ping)
    except Exception as exc:
        log.error("SELECT 1 failed: %s", exc)
        return 2

    # 2. Describe the participant table
    try:
        cols = db.fetch_all(
            "SELECT column_name, data_type "
            "FROM information_schema.columns "
            "WHERE table_name = %s "
            "ORDER BY ordinal_position;",
            (settings.DB_TABLE_PARTICIPANT,),
        )
        if not cols:
            log.error("Table %r not found in current database!", settings.DB_TABLE_PARTICIPANT)
            return 3

        log.info("participant columns (%d):", len(cols))
        for c in cols:
            log.info("  - %-30s %s", c["column_name"], c["data_type"])
    except Exception as exc:
        log.error("Describe participant failed: %s", exc)
        return 4

    # 3. Optional: live access-key lookup
    if len(sys.argv) > 1:
        key = sys.argv[1]
        log.info("Trying access_key=%s", key)
        try:
            result = participant_repo.find_by_access_key(key)
            if result:
                log.info("Lookup OK → id=%s name=%s stage=%s",
                         result["id"], result["name"], result["current_stage"])
            else:
                log.warning("No participant row matched access_key=%s", key)
        except Exception as exc:
            log.error("Lookup raised: %s", exc)
            return 5

    db.close_pool()
    log.info("Smoke test passed ✓")
    return 0


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