"""Mantara v8 — cfg_* seed-insert renderer.

Generates `INSERT INTO cfg_X ...` blocks that match each cfg table's actual
column list, so changing the canonical shape doesn't break inserts.

Reads `_seed_values` from each cfg table (populated by normaliser, optionally
backfilled from CIR workflow states). If a cfg has no seed values, it's
skipped silently — the table still exists, just empty.
"""
from __future__ import annotations

import re


def _value_to_code(v: str) -> str:
    return re.sub(r"[^a-z0-9_]", "_", str(v).lower()).strip("_")


def _value_to_label(v: str) -> str:
    return str(v).replace("_", " ").title()


def _column_names_in_order(tbl: dict) -> list[str]:
    return [c.get("name") for c in (tbl.get("columns") or []) if c.get("name")]


def _column_constraints(tbl: dict) -> dict[str, str]:
    return {
        c["name"]: (c.get("constraints") or "")
        for c in (tbl.get("columns") or [])
        if c.get("name")
    }


def _has_default(constraints: str) -> bool:
    return bool(re.search(r"\bDEFAULT\b", constraints, re.IGNORECASE))


def _is_not_null(constraints: str) -> bool:
    return bool(re.search(r"\bNOT\s+NULL\b", constraints, re.IGNORECASE))


def _value_for_column(
    col_name: str,
    constraints: str,
    seed_value: str,
    row_idx: int,
    cfg_name: str,
) -> str:
    """Compute the SQL literal for a column in a seed row.

    Returns either a literal (e.g. 'pending', NULL, 1) or "" to mean "skip
    this column entirely so DEFAULT applies".
    """
    cl = col_name.lower()
    pk_col = f"{cfg_name}_id"

    if cl == pk_col:
        return str(row_idx)
    if cl == "code":
        return f"'{_value_to_code(seed_value)}'"
    if cl == "label":
        return f"'{_value_to_label(seed_value)}'"
    if cl == "description":
        return "NULL"
    if cl == "is_active":
        return "TRUE"
    if cl == "submenu_id":
        # Skip only when a DEFAULT or nullable allows the column to be omitted.
        # After normaliser repair, NOT NULL columns always carry a DEFAULT.
        if _has_default(constraints) or not _is_not_null(constraints):
            return ""
        # Fallthrough: NOT NULL without DEFAULT — treat as unknown column below.
    if cl == "display_order":
        return str(row_idx)
    if cl in ("created_at", "updated_at"):
        return ""
    # Unknown column — only emit a value if we MUST (NOT NULL without DEFAULT)
    if _is_not_null(constraints) and not _has_default(constraints):
        # Last-resort placeholder
        return f"'{_value_to_code(seed_value)}'"
    return ""


def render_seed_inserts(schema_json: dict) -> str:
    """Emit one INSERT per cfg_* table that has `_seed_values`.

    Column list is read from the table itself, so the INSERT always matches
    the table shape (no hard-coded column lists).
    """
    schema_name = schema_json.get("schema_name") or "schema"
    blocks: list[str] = []

    for menu in schema_json.get("menus", []):
        for sub in menu.get("submenus") or []:
            for tbl in sub.get("tables") or []:
                name = tbl.get("table_name", "")
                if not name.startswith("cfg_"):
                    continue
                values = tbl.get("_seed_values") or []
                if not values:
                    continue

                all_cols = _column_names_in_order(tbl)
                constraints = _column_constraints(tbl)
                # De-dup seed values by code
                seen_codes: set[str] = set()
                rows_data: list[tuple[int, str]] = []
                idx = 1
                for v in values:
                    code = _value_to_code(v) or f"value_{idx}"
                    if code in seen_codes:
                        continue
                    seen_codes.add(code)
                    rows_data.append((idx, v))
                    idx += 1
                if not rows_data:
                    continue

                # Pick which columns to actually emit (drop the ones that should
                # use DEFAULT or whose value would always be empty).
                emit_cols: list[str] = []
                col_emit_test_row = rows_data[0]
                for c in all_cols:
                    val = _value_for_column(
                        c, constraints.get(c, ""), col_emit_test_row[1],
                        col_emit_test_row[0], name,
                    )
                    if val != "":
                        emit_cols.append(c)

                # Build VALUES clause
                row_lines: list[str] = []
                for idx, v in rows_data:
                    parts = []
                    for c in emit_cols:
                        parts.append(_value_for_column(
                            c, constraints.get(c, ""), v, idx, name
                        ))
                    row_lines.append(f"    ({', '.join(parts)})")

                pk_col = f"{name}_id"
                col_list = ", ".join(emit_cols)
                blocks.append(
                    f"INSERT INTO {schema_name}.{name} "
                    f"({col_list}) VALUES\n"
                    + ",\n".join(row_lines)
                    + f"\nON CONFLICT ({pk_col}) DO NOTHING;"
                )

    if not blocks:
        return ""
    header = (
        "-- " + "=" * 76 + "\n"
        "-- CONFIGURATION LOOKUP SEED DATA\n"
        "-- " + "=" * 76
    )
    return header + "\n\n" + "\n\n".join(blocks) + "\n"


def render_indexes(schema_json: dict) -> str:
    """Emit the deterministic CREATE INDEX block.

    Reads from `schema_json['_indexes']` (populated by
    `mantara_v8.normaliser.normalise()`). Returns "" if no indexes were
    cached or if the list is empty.
    """
    stmts = schema_json.get("_indexes") or []
    if not stmts:
        return ""
    header = (
        "-- " + "=" * 76 + "\n"
        "-- INDEXES\n"
        "-- " + "=" * 76
    )
    return header + "\n\n" + "\n".join(stmts) + "\n"


__all__ = ["render_seed_inserts", "render_indexes"]