#!/usr/bin/env python3
"""Request, approve, activate, and query local-mysql using one admin API key.

Run: python3 scripts/query-local-mysql.py
The key is prompted without echo, or read from A10E_API_KEY (also accepts KEY).
Uses Python 3's standard library; no datasource credentials or template needed.
Default API: http://localhost:8080 (override with A10E).
Requests merchant's id, name, mcc, and category columns, expires after one hour,
and leaves access active for reuse.
Pass --revoke to tear down the request on exit, including after a failure.
"""

import argparse
import getpass
import json
import os
import sys
import time
import urllib.error
import urllib.parse
import urllib.request
from datetime import datetime, timedelta, timezone


class APIError(RuntimeError):
    def __init__(self, status, message):
        self.status = status
        super().__init__(message)


class API:
    def __init__(self, base, key):
        self.base = base.rstrip("/")
        self.key = key
        # Do not forward the API key to a redirect destination.
        class NoRedirect(urllib.request.HTTPRedirectHandler):
            def redirect_request(self, req, fp, code, msg, headers, newurl):
                return None
        self.opener = urllib.request.build_opener(NoRedirect())

    def call(self, method, path, body=None):
        headers = {"X-API-Key": self.key, "Accept": "application/json"}
        data = None
        if body is not None:
            data = json.dumps(body).encode()
            headers["Content-Type"] = "application/json"
        req = urllib.request.Request(self.base + path, data, headers, method=method)
        try:
            with self.opener.open(req, timeout=120) as response:
                return json.load(response)
        except urllib.error.HTTPError as exc:
            detail = exc.read().decode(errors="replace").replace(self.key, "[redacted]")
            hint = " Use an organization admin's API key." if exc.code == 403 else ""
            raise APIError(exc.code, f"{method} {path}: HTTP {exc.code}: {detail}{hint}") from None
        except urllib.error.URLError as exc:
            raise RuntimeError(f"{method} {path}: connection failed: {exc.reason}") from None

    def list_all(self, path, field):
        items, page = [], 1
        while True:
            separator = "&" if "?" in path else "?"
            data = self.call("GET", f"{path}{separator}limit=100&page={page}")["data"]
            batch = data[field]
            items.extend(batch)
            if not batch or len(items) >= data["total"]:
                return items
            page += 1


def run(api, args):
    # Check admin access before creating a request that this key cannot approve.
    api.call("GET", "/v1/admin/requests?limit=1")
    sources = api.list_all("/v1/datasources", "datasources")
    matches = [d for d in sources if d["name"] == "local-mysql" and d["type"] == "mysql"]
    if len(matches) != 1:
        raise RuntimeError("Expected one local-mysql MySQL datasource in the key's organization.")
    source = matches[0]
    entries = api.list_all("/v1/catalog?" + urllib.parse.urlencode({
        "datasource_id": source["id"], "target_name": "merchant",
    }), "entries")
    merchants = [e for e in entries if e["target_name"] == "merchant"]
    if len(merchants) > 1:
        raise RuntimeError("Multiple merchant catalog entries; cannot choose an unambiguous source.")
    table = merchants[0]["target_location"] if merchants else "merchant"
    # The data-pull contract requires explicit column identifiers, not SQL '*'.
    # These are the merchant columns defined in seeds/mysql/001_schema.sql.
    columns = ["id", "name", "mcc", "category"]
    print(f"Datasource: {source['name']} ({source['id']}); source table: {table}", flush=True)
    if not merchants:
        print("No merchant catalog entry; using the documented local MySQL sample table.", flush=True)
    expiry = (datetime.now(timezone.utc) + timedelta(hours=1)).isoformat().replace("+00:00", "Z")
    request = api.call("POST", "/v1/requests", {
        "title": "local-mysql merchant — API example",
        "description": "Read up to 20 merchant records from the configured local MySQL datasource.",
        "business_justification": "Validate the API request, approval, activation, and read-only query workflow.",
        "source": "manual", "type": "enclave", "valid_until": expiry,
        "scope": [{"datasource": "local-mysql", "datasets": [
            {"source": {"kind": "table", "name": table}, "columns": columns},
        ]}],
    })["data"]
    request_id = request["id"]
    path = "/v1/requests/" + urllib.parse.quote(request_id, safe="")
    print(f"Request: {request_id} (expires {expiry})", flush=True)
    print(f"Reuse: {api.base}{path}/query", flush=True)
    try:
        api.call("POST", f"/v1/admin/requests/{request_id}/approve", {
            "notes": "Approved to validate local-mysql API querying for one hour.",
        })
        print("Approved. Activating…", flush=True)
        api.call("POST", path + "/activate", {})
        deadline = time.monotonic() + args.wait_seconds
        while time.monotonic() < deadline:
            try:
                status = api.call("GET", path + "/status")
            except APIError as exc:
                if exc.status not in (409, 502, 503):
                    raise
                print(f"Readiness temporarily unavailable (HTTP {exc.status}); retrying…", flush=True)
                time.sleep(5)
                continue
            state = status.get("state")
            print(f"Data status: {state}", flush=True)
            if state == "failed":
                raise RuntimeError("Data loading failed: " + json.dumps(status))
            if state == "ready":
                break
            time.sleep(5)
        else:
            raise RuntimeError(f"Data did not become ready in {args.wait_seconds} seconds.")
        relations = [d["relation"] for d in status.get("datasets", []) if d.get("relation")]
        if len(relations) != 1:
            raise RuntimeError("Expected exactly one loaded relation: " + json.dumps(status))
        # Quote each namespace/table component using DuckDB identifier syntax.
        relation = ".".join('"' + part.replace('"', '""') + '"' for part in relations[0].split("."))
        sql = f"SELECT * FROM {relation} LIMIT 20"
        print(f"SQL: {sql}", flush=True)
        result = api.call("POST", path + "/query", {"sql": sql})
        if "error" in result:
            raise RuntimeError("Query failed: " + json.dumps(result))
        print(json.dumps(result, indent=2))
    finally:
        if args.revoke:
            try:
                api.call("POST", path + "/revoke", {"reason": "API example finished"})
                print(f"Revoked request {request_id}.", flush=True)
            except (RuntimeError, ValueError) as exc:
                print(f"Cleanup failed for {request_id}: {exc}", file=sys.stderr)
        else:
            print(f"Request retained until {expiry}. To end access early, POST {api.base}{path}/revoke.", flush=True)


def main():
    parser = argparse.ArgumentParser(description=__doc__, formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument("--revoke", action="store_true", help="revoke this request on exit")
    parser.add_argument("--wait-seconds", type=int, default=300, help="data readiness wait (default: 300)")
    args = parser.parse_args()
    if args.wait_seconds <= 0:
        parser.error("--wait-seconds must be positive")
    key = os.environ.get("A10E_API_KEY") or os.environ.get("KEY") or getpass.getpass("Organization admin API key: ")
    if not key.strip():
        parser.error("API key is required")
    base = os.environ.get("A10E", "http://localhost:8080")
    try:
        run(API(base, key.strip()), args)
    except (RuntimeError, ValueError, KeyError) as exc:
        print(f"Error: {str(exc).replace(key.strip(), '[redacted]')}", file=sys.stderr)
        return 1
    except KeyboardInterrupt:
        print("Interrupted.", file=sys.stderr)
        return 130
    return 0


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