"""
build_ui_config.py
==================
Creates and seeds the ui_features table in the MySQL database.

Each row controls whether a sidebar section is visible and its display order.
Edit these values directly in MySQL to reconfigure the sidebar without
touching frontend code.

Columns:
  feature_key   — unique identifier  (e.g. 'iris', 'poi.mairie')
  label         — human-readable name (informational, not consumed by frontend)
  section       — 'main' for top-level sidebar sections, 'poi' for POI sub-types
  enabled       — 1 = shown in sidebar, 0 = hidden
  display_order — ascending sort order within the section (lower = higher up)

Usage:
    python build_ui_config.py           # create table + insert missing rows
    python build_ui_config.py --reset   # drop and recreate (loses manual overrides)
"""

from __future__ import annotations

import argparse

from domain.core.mysql_db import get_connection, reset_table

_DDL = """
CREATE TABLE IF NOT EXISTS ui_features (
    feature_key   VARCHAR(100) PRIMARY KEY,
    label         TEXT         NOT NULL,
    section       VARCHAR(20)  NOT NULL DEFAULT 'main',
    enabled       INT          NOT NULL DEFAULT 1,
    display_order INT          NOT NULL DEFAULT 999
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
"""

_DEFAULTS: list[tuple] = [
    # (feature_key, label, section, enabled, display_order)
    # ── Main sidebar sections ──────────────────────────────────────────────
    ("iris",              "Quartiers IRIS",            "main", 1,  1),
    ("poi",               "Points d'intérêt",          "main", 1,  2),
    ("flood",             "Zones inondables",          "main", 1,  3),
    ("nature",            "Espaces naturels",          "main", 1,  4),
    ("radon",             "Radon",                     "main", 1,  5),
    ("rga",               "Argiles (RGA)",             "main", 1,  6),
    # ── POI sub-types ─────────────────────────────────────────────────────
    ("poi.mairie",            "Mairies",                      "poi", 1,  1),
    ("poi.gare",              "Gares SNCF",                   "poi", 1,  2),
    ("poi.metro",             "Métro",                        "poi", 1,  3),
    ("poi.tram",              "Tramway",                      "poi", 1,  4),
    ("poi.aeroport",          "Aéroports",                    "poi", 1,  5),
    ("poi.police",            "Commissariats / Gendarmeries", "poi", 1,  6),
    ("poi.supermarche",       "Supermarchés",                 "poi", 1,  7),
    ("poi.centre_commercial", "Centres commerciaux",          "poi", 1,  8),
    ("poi.maternelle",        "Maternelles",                  "poi", 1,  9),
    ("poi.ecole",             "Écoles",                       "poi", 1, 10),
    ("poi.universite",        "Universités / Grandes écoles", "poi", 1, 11),
    ("noise",             "Bruit (grands axes)",       "main", 1,  7),
    ("iso",               "Temps de trajet",           "main", 1,  8),
]


def build(reset: bool = False) -> None:
    conn = get_connection()
    try:
        if reset:
            with conn.cursor() as cur:
                cur.execute("DROP TABLE IF EXISTS ui_features")
            conn.commit()
            print("Table ui_features supprimée.")

        with conn.cursor() as cur:
            cur.execute(_DDL)
            cur.executemany(
                """REPLACE INTO ui_features
                   (feature_key, label, section, enabled, display_order)
                   VALUES (%s, %s, %s, %s, %s)""",
                _DEFAULTS,
            )
        conn.commit()

        with conn.cursor() as cur:
            cur.execute(
                "SELECT feature_key, section, enabled, display_order"
                " FROM ui_features ORDER BY section, display_order"
            )
            rows = cur.fetchall()
    finally:
        conn.close()

    print(f"\nui_features — {len(rows)} lignes :\n")
    print(f"  {'feature_key':<30} {'section':<6} {'enabled':<8} {'order'}")
    print("  " + "-" * 58)
    for row in rows:
        fk  = row["feature_key"]
        sec = row["section"]
        en  = row["enabled"]
        ord_ = row["display_order"]
        print(f"  {fk:<30} {sec:<6} {'oui' if en else 'non':<8} {ord_}")
    print()


if __name__ == "__main__":
    parser = argparse.ArgumentParser(description="Setup ui_features table in MySQL")
    parser.add_argument(
        "--reset", action="store_true",
        help="Drop and recreate the table (manual overrides will be lost)",
    )
    args = parser.parse_args()
    build(args.reset)
    print("Done.")