#!/usr/bin/env python3
"""Lightweight tenant-overlay readiness checks for ci-test TiDB.

This verifies only the tenant artifacts required by the stable E2E API scenes.
It is intentionally narrower than the full tenant drift verify.
"""

from __future__ import annotations

import json
import os
import sys
import time
from dataclasses import dataclass
from typing import Callable, List, Optional, Sequence, Tuple

import pymysql


@dataclass
class CheckResult:
    name: str
    ok: bool
    detail: str
    elapsed_ms: int


class TenantBaselineVerifier:
    def __init__(self) -> None:
        self.conn = pymysql.connect(
            host=self._env("TIDB_HOST"),
            port=int(os.getenv("TIDB_PORT", "4000")),
            user=self._env("TIDB_USER"),
            password=self._env("TIDB_PASSWORD"),
            database=os.getenv("TIDB_DATABASE", "nexuscrm"),
            ssl={"ca": None},
            ssl_verify_cert=False,
            connect_timeout=10,
            read_timeout=10,
            write_timeout=10,
            cursorclass=pymysql.cursors.Cursor,
        )
        self.results: List[CheckResult] = []

    @staticmethod
    def _env(key: str) -> str:
        value = os.getenv(key)
        if not value:
            raise RuntimeError(f"missing required env: {key}")
        return value

    def close(self) -> None:
        self.conn.close()

    def query_one(self, sql: str, params: Optional[Sequence[object]] = None) -> Tuple[object, ...]:
        with self.conn.cursor() as cur:
            cur.execute(sql, params or ())
            row = cur.fetchone()
        if row is None:
            raise RuntimeError(f"query returned no rows: {sql}")
        return row

    def query_optional(self, sql: str, params: Optional[Sequence[object]] = None) -> Optional[Tuple[object, ...]]:
        with self.conn.cursor() as cur:
            cur.execute(sql, params or ())
            return cur.fetchone()

    def check(self, name: str, fn: Callable[[], str]) -> None:
        started = time.time()
        try:
            detail = fn()
            ok = True
        except Exception as exc:  # noqa: BLE001
            detail = str(exc)
            ok = False
        elapsed_ms = int((time.time() - started) * 1000)
        self.results.append(CheckResult(name=name, ok=ok, detail=detail, elapsed_ms=elapsed_ms))

    def run(self) -> int:
        self.check("lead_status_field_exists", self._check_lead_status_field)
        self.check("lead_status_working_tag", self._check_lead_status_working_tag)
        self.check("lead_status_flow_definition", self._check_lead_status_flow_definition)
        self.check("lead_status_flow_active_version", self._check_lead_status_flow_active_version)
        self.check("quote_status_options", self._check_quote_status_options)
        self.check("order_layout_exposure", self._check_order_layout_exposure)

        failed = [r for r in self.results if not r.ok]
        print(self._json_report())

        summary = os.getenv("GITHUB_STEP_SUMMARY")
        if summary:
            self._append_summary(summary)

        return 1 if failed else 0

    def _check_lead_status_field(self) -> str:
        row = self.query_one(
            """
            SELECT COUNT(*)
            FROM `_System_Field` f
            JOIN `_System_Object` o ON o.`Id` = f.`ObjectId`
            WHERE o.`ApiName` = 'Lead'
              AND f.`ApiName` = 'Status'
            """
        )
        count = int(row[0])
        if count == 0:
            raise RuntimeError("missing _System_Field row for Lead.Status")
        return f"count={count}"

    def _load_lead_status_options(self) -> List[dict]:
        row = self.query_one(
            """
            SELECT f.`Options`
            FROM `_System_Field` f
            JOIN `_System_Object` o ON o.`Id` = f.`ObjectId`
            WHERE o.`ApiName` = 'Lead'
              AND f.`ApiName` = 'Status'
            LIMIT 1
            """
        )
        raw = row[0]
        if raw in (None, ""):
            raise RuntimeError("Lead.Status options are empty")
        try:
            return json.loads(raw)
        except Exception as exc:  # noqa: BLE001
            raise RuntimeError(f"Lead.Status options are not valid JSON: {exc}") from exc

    def _check_lead_status_working_tag(self) -> str:
        options = self._load_lead_status_options()
        by_value = {str(opt.get("value")): opt for opt in options}
        if "Working" not in by_value:
            raise RuntimeError("Lead.Status is missing value=Working")
        tags = by_value["Working"].get("tags") or []
        if "working_or_later" not in tags:
            raise RuntimeError("Lead.Status.Working is missing tag working_or_later")
        return f"tags={tags}"

    def _check_lead_status_flow_definition(self) -> str:
        row = self.query_one(
            """
            SELECT `Id`, `ActiveVersionId`, COALESCE(`IsDeleted`, 0)
            FROM `FlowDefinition`
            WHERE `DeveloperName` = 'Lead_StatusChangeTimestamp'
            LIMIT 1
            """
        )
        definition_id, active_version_id, is_deleted = row
        if int(is_deleted) != 0:
            raise RuntimeError("Lead_StatusChangeTimestamp FlowDefinition is soft-deleted")
        if not active_version_id:
            raise RuntimeError("Lead_StatusChangeTimestamp FlowDefinition has empty ActiveVersionId")
        return f"id={definition_id} active_version={active_version_id}"

    def _check_lead_status_flow_active_version(self) -> str:
        row = self.query_one(
            """
            SELECT f.`Id`, f.`Status`, COALESCE(f.`IsDeleted`, 0), COALESCE(f.`Source`, '')
            FROM `FlowDefinition` fd
            JOIN `Flow` f ON f.`Id` = fd.`ActiveVersionId`
            WHERE fd.`DeveloperName` = 'Lead_StatusChangeTimestamp'
            LIMIT 1
            """
        )
        flow_id, status, is_deleted, source = row
        if int(is_deleted) != 0:
            raise RuntimeError("Lead_StatusChangeTimestamp active Flow version is soft-deleted")
        if str(status) != "Active":
            raise RuntimeError(f"Lead_StatusChangeTimestamp active Flow version status={status}")
        if str(source).strip().lower() == "standard":
            raise RuntimeError("Lead_StatusChangeTimestamp active Flow version is Source=standard and will be treated as protected")
        return f"id={flow_id} status={status} source={source}"

    def _load_field_options(self, object_api_name: str, field_api_name: str) -> List[dict]:
        row = self.query_one(
            """
            SELECT f.`Options`
            FROM `_System_Field` f
            JOIN `_System_Object` o ON o.`Id` = f.`ObjectId`
            WHERE o.`ApiName` = %s
              AND f.`ApiName` = %s
            LIMIT 1
            """,
            (object_api_name, field_api_name),
        )
        raw = row[0]
        if raw in (None, ""):
            raise RuntimeError(f"{object_api_name}.{field_api_name} options are empty")
        try:
            return json.loads(raw)
        except Exception as exc:  # noqa: BLE001
            raise RuntimeError(f"{object_api_name}.{field_api_name} options are not valid JSON: {exc}") from exc

    def _json_report(self) -> str:
        payload = {
            "ok": all(r.ok for r in self.results),
            "checks": [
                {
                    "name": r.name,
                    "ok": r.ok,
                    "detail": r.detail,
                    "elapsed_ms": r.elapsed_ms,
                }
                for r in self.results
            ],
        }
        return json.dumps(payload, ensure_ascii=False, indent=2)

    def _check_quote_status_options(self) -> str:
        options = self._load_field_options("Quote", "Status")
        by_value = {str(opt.get("value")): opt for opt in options}
        missing = [value for value in ("Draft", "Pending") if value not in by_value]
        if missing:
            raise RuntimeError(f"Quote.Status is missing expected values: {', '.join(missing)}")
        return "values=Draft,Pending"

    def _is_admin_owned_page_layout(self, is_customized: object, source: object) -> bool:
        source_text = str(source or "").strip().lower()
        return bool(is_customized) or source_text in {"admin_created", "cloned", "custom"}

    def _load_order_layout_config(self) -> Tuple[str, str, bool]:
        row = self.query_optional(
            """
            SELECT la.`LayoutId`, pl.`Config`, COALESCE(pl.`Source`, ''), COALESCE(pl.`IsCustomized`, 0)
            FROM `_System_LayoutAssignment` la
            JOIN `_System_PageLayout` pl
              ON pl.`Id` = la.`LayoutId`
             AND LOWER(pl.`ObjectApiName`) = LOWER(la.`ObjectApiName`)
             AND (pl.`IsDeleted` = 0 OR pl.`IsDeleted` IS NULL)
            WHERE LOWER(la.`ObjectApiName`) = 'order'
              AND la.`RoleId` IS NULL
              AND la.`ProfileId` IS NULL
              AND la.`RecordTypeId` IS NULL
            ORDER BY la.`CreatedDate` ASC
            LIMIT 1
            """
        )
        if row and row[0] and row[1]:
            return str(row[1]), f"wildcard layout {row[0]}", self._is_admin_owned_page_layout(row[3], row[2])

        row = self.query_optional(
            """
            SELECT `Config`, COALESCE(`Source`, ''), COALESCE(`IsCustomized`, 0)
            FROM `_System_PageLayout`
            WHERE `Id` = 'std_layout_Order'
              AND LOWER(`ObjectApiName`) = 'order'
              AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)
            LIMIT 1
            """
        )
        if row and row[0]:
            return str(row[0]), "std_layout_Order", self._is_admin_owned_page_layout(row[2], row[1])

        count_row = self.query_one(
            """
            SELECT COUNT(*)
            FROM `_System_PageLayout`
            WHERE LOWER(`ObjectApiName`) = 'order'
              AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)
            """
        )
        count = int(count_row[0])
        if count == 1:
            row = self.query_one(
                """
                SELECT `Id`, `Config`, COALESCE(`Source`, ''), COALESCE(`IsCustomized`, 0)
                FROM `_System_PageLayout`
                WHERE LOWER(`ObjectApiName`) = 'order'
                  AND (`IsDeleted` = 0 OR `IsDeleted` IS NULL)
                ORDER BY `Id` ASC
                LIMIT 1
                """
            )
            return str(row[1]), f"single live layout {row[0]}", self._is_admin_owned_page_layout(row[3], row[2])
        if count == 0:
            raise RuntimeError("tenant baseline has no live Order page layout row")
        raise RuntimeError("tenant baseline has multiple live Order page layout rows but no wildcard/std baseline target")

    def _check_order_layout_exposure(self) -> str:
        config_json, source, admin_owned = self._load_order_layout_config()
        if admin_owned:
            return f"{source} is admin-owned; tenant overlay preserve/skip rule remains in effect"
        try:
            layout = json.loads(config_json)
        except Exception as exc:  # noqa: BLE001
            raise RuntimeError(f"{source} config is not valid JSON: {exc}") from exc

        fields = set()
        for section in layout.get("sections") or []:
            for field in section.get("fields") or []:
                if field:
                    fields.add(str(field))

        required = {"Archived_Order_Form__c", "StandardUploadUrl__c", "Archived__c"}
        missing = sorted(required - fields)
        if missing:
            raise RuntimeError(f"{source} is missing tenant Order layout fields: {', '.join(missing)}")
        return f"{source} preserves Archived_Order_Form__c, StandardUploadUrl__c, Archived__c"

    def _append_summary(self, path: str) -> None:
        with open(path, "a", encoding="utf-8") as fp:
            fp.write("### CI-Test Tenant Baseline Verify\n\n")
            fp.write("| Check | Status | Detail | Time |\n")
            fp.write("|---|---|---|---|\n")
            for r in self.results:
                status = "PASS" if r.ok else "FAIL"
                detail = r.detail.replace("\n", " ")
                fp.write(f"| `{r.name}` | {status} | {detail} | {r.elapsed_ms}ms |\n")
            fp.write("\n")


def main() -> int:
    verifier: Optional[TenantBaselineVerifier] = None
    try:
        verifier = TenantBaselineVerifier()
        return verifier.run()
    except Exception as exc:  # noqa: BLE001
        print(json.dumps({"ok": False, "error": str(exc)}, ensure_ascii=False, indent=2))
        return 1
    finally:
        if verifier is not None:
            verifier.close()


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