"""Shared, credential-free state-catalog transformation and publication."""
from __future__ import annotations

import argparse
import hashlib
import json
import os
import re
import sys
from collections import defaultdict
from datetime import date, datetime, timezone
from pathlib import Path
from urllib.error import HTTPError
from urllib.parse import quote, urlsplit
from urllib.request import Request, urlopen


# Operational state rules and public DOT metadata only. Never export Mongo's
# autofiller JavaScript, activation/admin fields, or any future unknown field.
DETAIL_FIELDS = (
    "csi_book_data", "escorts", "interstate_escort", "lane_escort_req",
    "legal_dimensions", "legal_limits", "max_weight_per_axle", "miscellaneous",
    "night_travel", "night_traver", "od_permit_price", "operating_time",
    "ow_permit_price", "permit_limits", "police_escort_req", "pricing",
    "provision", "provision_info", "restricted_travel", "route_survey",
    "service_fee", "signs_flags_lights", "speed_limit", "state_email",
    "state_phone", "state_website", "superload_trigger", "superloads",
    "time_zone", "total_permit", "provision_sync_status",
)
BLOCKED_KEY = re.compile(
    r"password|passcode|username|api[_-]?key|secret|token|credential|auth|cookie|session|(^|[_-])(pin|otp)($|[_-])",
    re.IGNORECASE,
)
CODE = re.compile(r"^[A-Z]{2}$")


def env_file(path: str | None) -> dict[str, str]:
    if not path:
        return {}
    values: dict[str, str] = {}
    for line in Path(path).read_text(encoding="utf-8").splitlines():
        if not line.strip() or line.lstrip().startswith("#") or "=" not in line:
            continue
        key, value = line.split("=", 1)
        value = value.strip()
        if len(value) >= 2 and value[0] == value[-1] and value[0] in "\"'":
            value = value[1:-1]
        values[key.strip()] = value
    return values


def setting(name: str, source: dict[str, str]) -> str:
    return source.get(name) or os.environ.get(name, "")


def json_safe(value):
    if isinstance(value, dict):
        return {str(key): json_safe(item) for key, item in value.items()
                if not BLOCKED_KEY.search(str(key))}
    if isinstance(value, (list, tuple)):
        return [json_safe(item) for item in value]
    if isinstance(value, (datetime, date)):
        return value.isoformat()
    if isinstance(value, str):
        # PostgreSQL text cannot contain NUL (U+0000). Synchron state metadata
        # is external input, so normalize only that invalid character before the
        # public catalog is serialized for Supabase.
        return value.replace("\x00", "")
    if isinstance(value, (int, float, bool)) or value is None:
        return value
    return str(value)


def canonical_ids(documents: list[dict]) -> dict[str, str]:
    by_code: dict[str, list[dict]] = defaultdict(list)
    for document in documents:
        code = str(document.get("state_short_name") or "").strip().upper()
        name = str(document.get("state_name") or "").strip()
        if not CODE.fullmatch(code) or not name or not document.get("_id"):
            raise ValueError("Mongo state is missing a valid code, name, or id")
        by_code[code].append(document)

    selected: dict[str, str] = {}
    for code, candidates in by_code.items():
        if len({str(row["state_name"]).strip().casefold() for row in candidates}) != 1:
            raise ValueError(f"Conflicting state names for {code}; manual review required")
        if len(candidates) == 1:
            chosen = candidates[0]
        else:
            # The existing GPT Logistics Bot queries lower-case state_name.
            active = [row for row in candidates
                      if str(row["state_name"]).strip() == str(row["state_name"]).strip().lower()]
            if len(active) != 1:
                raise ValueError(f"Ambiguous GPT-active state record for {code}")
            chosen = active[0]
        selected[code] = str(chosen["_id"])
    return selected


def prepare_rows(documents: list[dict]) -> tuple[list[dict], list[str]]:
    selected = canonical_ids(documents)
    rows = []
    for document in documents:
        code = str(document["state_short_name"]).strip().upper()
        country = {"1": "US", "2": "CA"}.get(str(document.get("country")))
        if not country:
            raise ValueError(f"Unknown country for state {code}")
        details = {key: json_safe(document[key]) for key in DETAIL_FIELDS if key in document}
        base = {
            "mongo_document_id": str(document["_id"]),
            "source_state_id": int(document["id"]) if document.get("id") is not None else None,
            "state_code": code,
            "state_name": str(document["state_name"]).strip(),
            "country_code": country,
            "is_canonical": str(document["_id"]) == selected[code],
            "source_present": True,
            "details": details,
            "source_updated_at": str(document["updated_at"]) if document.get("updated_at") else None,
        }
        digest = hashlib.sha256(json.dumps(base, sort_keys=True, separators=(",", ":"),
                                           ensure_ascii=False).encode("utf-8")).hexdigest()
        rows.append({**base, "source_sha256": digest})
    if len({row["mongo_document_id"] for row in rows}) != len(rows):
        raise ValueError("Duplicate Mongo document id")
    return rows, sorted(code for code in selected
                        if sum(row["state_code"] == code for row in rows) > 1)


class SupabaseReadWrite:
    def __init__(self, url: str, service_key: str):
        self.root = url.rstrip("/") + "/rest/v1"
        self.base = self.root + "/all_states"
        self.service_key = service_key

    def request(self, method: str, suffix: str = "", payload=None):
        headers = {
            "apikey": self.service_key,
            "Authorization": f"Bearer {self.service_key}",
            "Accept": "application/json",
        }
        body = None
        if payload is not None:
            body = json.dumps(payload, ensure_ascii=False, default=str).encode("utf-8")
            headers["Content-Type"] = "application/json"
        if method == "POST":
            headers["Prefer"] = "resolution=merge-duplicates,return=minimal"
        if method == "PATCH":
            headers["Prefer"] = "return=minimal"
        try:
            with urlopen(Request(self.base + suffix, data=body, headers=headers, method=method),
                         timeout=30) as response:
                content = response.read()
                return json.loads(content) if content else None
        except HTTPError as error:
            try:
                code = json.loads(error.read()).get("code", "unknown")
            except (ValueError, OSError):
                code = "unknown"
            # Never print a server error body; it may contain source data.
            raise RuntimeError(f"Supabase HTTP {error.code}, code {code}") from None

    def rpc(self, name, payload):
        # Each RPC uses its own client so the table-read URL is never mutated.
        endpoint = SupabaseReadWrite(self.root.removesuffix('/rest/v1'), self.service_key)
        endpoint.base = self.root + '/rpc/' + name
        return endpoint.request('POST', payload=payload)

    def existing(self) -> dict[str, dict]:
        fields = "mongo_document_id,source_sha256,state_code,is_canonical,source_present"
        rows = self.request("GET", f"?select={fields}&limit=1000") or []
        if len(rows) >= 1000:
            raise RuntimeError("State table has at least 1000 rows; review before syncing")
        return {row["mongo_document_id"]: row for row in rows}

    def patch_flags(self, mongo_id: str, **flags):
        self.request("PATCH", f"?mongo_document_id=eq.{quote(mongo_id, safe='')}", flags)

    def upsert(self, rows: list[dict]):
        if rows:
            self.request("POST", "?on_conflict=mongo_document_id", rows)



def compare_snapshot(rows, existing):
    if not rows:
        raise ValueError('Empty state source cannot replace the serving catalog')
    desired = {row['mongo_document_id']: row for row in rows}
    changed = [row for row in rows if row['mongo_document_id'] not in existing or
               existing[row['mongo_document_id']].get('source_sha256') != row['source_sha256'] or
               existing[row['mongo_document_id']].get('source_present') is not True or
               existing[row['mongo_document_id']].get('is_canonical') != row['is_canonical']]
    missing = [key for key, old in existing.items() if old.get('source_present') and key not in desired]
    active = sum(bool(old.get('source_present')) for old in existing.values())
    if active and len(missing) > active * 0.25:
        raise ValueError('Source removed more than 25 percent of active states; review required')
    return changed, missing


def claim_sync(client, holder):
    return client.rpc('claim_state_catalog_sync', {'p_holder': holder, 'p_ttl_seconds': 300}) is True


def publish_snapshot(client, rows, holder, resume_token=None):
    # SQL validates the complete snapshot and lease, publishes atomically, then saves the checkpoint.
    return client.rpc('publish_state_catalog_snapshot', {
        'p_holder': holder, 'p_rows': rows, 'p_resume_token': resume_token,
    })
