#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""SRM-RSK industrial database - local reference implementation.

- Normalized SQLite persistence (prototype of the Oracle schema).
- JSON API consumed by SRM_RSK_Dashboard.html.
- No third-party dependency: Python standard library only.
- Runs behind the Arena live preview on 0.0.0.0:$PORT.

Industrial codes are numbered per provincial / prefectural direction:
IN + CODE_DP + '-' + NNNN (example: INDPK-0001).
"""
from __future__ import annotations

import json
import os
import re
import sqlite3
import traceback
from datetime import date, datetime, timedelta
from http.server import BaseHTTPRequestHandler, ThreadingHTTPServer
from pathlib import Path
from urllib.parse import urlparse

HERE = Path(__file__).resolve().parent
ROOT = HERE.parent
DB_PATH = HERE / "srm_rsk.db"
SEED_PATH = HERE / "seed_data.json"
DASHBOARD = ROOT / "SRM_RSK_Dashboard.html"
PORT = int(os.environ.get("PORT", "8000"))

DP_CODES = {
    "Rabat": "DPR",
    "Salé": "DPS",
    "Skhirat-Témara": "DPST",
    "Kénitra": "DPK",
    "Khemisset": "DPKH",
    "Sidi Kacem": "DPSK",
    "Sidi Slimane": "DPSS",
}

STATIC = {
    "/": ("SRM_RSK_Dashboard.html", "text/html; charset=utf-8"),
    "/SRM_RSK_Dashboard.html": ("SRM_RSK_Dashboard.html", "text/html; charset=utf-8"),
    "/SRM_RSK_Architecture_APEX.md": ("SRM_RSK_Architecture_APEX.md", "text/markdown; charset=utf-8"),
    "/schema_oracle.sql": ("schema_oracle.sql", "text/plain; charset=utf-8"),
    "/README.md": ("README.md", "text/markdown; charset=utf-8"),
    "/livrables.html": ("livrables.html", "text/html; charset=utf-8"),
}

SCHEMA = r"""
PRAGMA foreign_keys = ON;

CREATE TABLE meta (
  id INTEGER PRIMARY KEY CHECK (id = 1),
  payload TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

CREATE TABLE direction (
  id TEXT PRIMARY KEY,
  code_dp TEXT NOT NULL UNIQUE,
  libelle TEXT NOT NULL,
  province TEXT NOT NULL,
  ordre INTEGER NOT NULL
);

CREATE TABLE parametre (
  code TEXT PRIMARY KEY,
  libelle TEXT NOT NULL,
  unite TEXT NOT NULL,
  seuil_min REAL,
  seuil_max REAL,
  famille TEXT NOT NULL,
  article TEXT NOT NULL,
  note TEXT,
  actif INTEGER NOT NULL DEFAULT 1 CHECK (actif IN (0,1)),
  derive INTEGER NOT NULL DEFAULT 0 CHECK (derive IN (0,1)),
  ordre INTEGER NOT NULL,
  source_ref TEXT,
  CHECK (seuil_min IS NOT NULL OR seuil_max IS NOT NULL),
  CHECK (seuil_min IS NULL OR seuil_max IS NULL OR seuil_min <= seuil_max)
);

CREATE TABLE zone_industrielle (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  libelle TEXT NOT NULL,
  province TEXT NOT NULL,
  lat REAL,
  lon REAL,
  CHECK (lat IS NULL OR lat BETWEEN -90 AND 90),
  CHECK (lon IS NULL OR lon BETWEEN -180 AND 180)
);

CREATE TABLE industriel (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL UNIQUE,
  ancien_code TEXT,
  direction_id TEXT REFERENCES direction(id),
  code_dp TEXT,
  ordre_dp INTEGER CHECK (ordre_dp IS NULL OR ordre_dp > 0),
  raison_sociale TEXT NOT NULL,
  secteur TEXT NOT NULL,
  zone_id TEXT REFERENCES zone_industrielle(id),
  province TEXT NOT NULL,
  statut TEXT NOT NULL CHECK (statut IN ('Actif','Arrêt','À vérifier','En implantation')),
  ice TEXT UNIQUE,
  rc TEXT,
  gps_lat REAL,
  gps_lon REAL,
  contact TEXT,
  telephone TEXT,
  email TEXT,
  effectif INTEGER CHECK (effectif IS NULL OR effectif >= 0),
  surface_m2 REAL CHECK (surface_m2 IS NULL OR surface_m2 >= 0),
  capacite TEXT,
  regime_travail TEXT,
  date_implantation TEXT,
  cree_le TEXT NOT NULL,
  CHECK (ice IS NULL OR (length(ice) = 15 AND ice NOT GLOB '*[^0-9]*')),
  CHECK (gps_lat IS NULL OR gps_lat BETWEEN -90 AND 90),
  CHECK (gps_lon IS NULL OR gps_lon BETWEEN -180 AND 180),
  UNIQUE (direction_id, ordre_dp)
);

CREATE TABLE prelevement (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  date_prelevement TEXT NOT NULL,
  point TEXT NOT NULL,
  laboratoire TEXT NOT NULL,
  type_prelevement TEXT NOT NULL,
  reference TEXT NOT NULL UNIQUE,
  commentaire TEXT,
  cree_le TEXT NOT NULL
);

CREATE TABLE resultat_analyse (
  id TEXT PRIMARY KEY,
  prelevement_id TEXT NOT NULL REFERENCES prelevement(id) ON DELETE CASCADE,
  code_parametre TEXT NOT NULL REFERENCES parametre(code),
  valeur REAL NOT NULL,
  saisi_par TEXT NOT NULL,
  date_saisie TEXT NOT NULL,
  UNIQUE (prelevement_id, code_parametre),
  CHECK (typeof(valeur) IN ('real','integer')),
  CHECK (valeur BETWEEN -50 AND 1000000000)
);

CREATE TABLE enquete (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  date_visite TEXT NOT NULL,
  type TEXT NOT NULL,
  agent TEXT NOT NULL,
  statut TEXT NOT NULL,
  observations TEXT
);

CREATE TABLE reclamation (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  type TEXT NOT NULL,
  date_ouverture TEXT NOT NULL,
  date_cloture TEXT,
  statut TEXT NOT NULL,
  objet TEXT NOT NULL,
  gravite TEXT NOT NULL,
  CHECK (date_cloture IS NULL OR date_cloture >= date_ouverture)
);

CREATE TABLE consommation (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  periode TEXT NOT NULL,
  volume_m3 REAL NOT NULL CHECK (volume_m3 >= 0),
  source TEXT NOT NULL,
  cree_le TEXT NOT NULL,
  UNIQUE (industriel_id, periode, source)
);

CREATE TABLE dossier (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  type TEXT NOT NULL,
  numero TEXT NOT NULL,
  date_debut TEXT NOT NULL,
  date_echeance TEXT,
  statut TEXT NOT NULL,
  autorite TEXT,
  objet TEXT,
  cree_le TEXT NOT NULL,
  UNIQUE (type, numero),
  CHECK (date_echeance IS NULL OR date_echeance >= date_debut)
);

CREATE TABLE document (
  id TEXT PRIMARY KEY,
  industriel_id TEXT NOT NULL REFERENCES industriel(id),
  contexte TEXT NOT NULL CHECK (contexte IN ('INDUSTRIEL','PRELEVEMENT','ENQUETE','RECLAMATION','DOSSIER')),
  source_id TEXT NOT NULL,
  type TEXT NOT NULL,
  nom TEXT NOT NULL,
  url TEXT,
  date TEXT NOT NULL
);

CREATE TABLE journal (
  id TEXT PRIMARY KEY,
  date_action TEXT NOT NULL,
  utilisateur TEXT NOT NULL,
  action TEXT NOT NULL,
  entite TEXT NOT NULL,
  detail TEXT NOT NULL
);

CREATE INDEX idx_prelevement_ind_date ON prelevement(industriel_id, date_prelevement DESC);
CREATE INDEX idx_resultat_prelevement ON resultat_analyse(prelevement_id);
CREATE INDEX idx_resultat_parametre ON resultat_analyse(code_parametre);
CREATE INDEX idx_industriel_province ON industriel(province, secteur, statut);
CREATE INDEX idx_industriel_direction ON industriel(direction_id, ordre_dp);
CREATE INDEX idx_enquete_ind_date ON enquete(industriel_id, date_visite DESC);
CREATE INDEX idx_reclamation_dates ON reclamation(date_ouverture, date_cloture);
CREATE INDEX idx_consommation_periode ON consommation(periode);
CREATE INDEX idx_dossier_statut ON dossier(type, statut, date_echeance);

CREATE TRIGGER trg_journal_no_update BEFORE UPDATE ON journal
BEGIN SELECT RAISE(ABORT, 'Le journal MAJ est append-only'); END;
CREATE TRIGGER trg_journal_no_delete BEFORE DELETE ON journal
BEGIN SELECT RAISE(ABORT, 'Le journal MAJ est append-only'); END;
"""

ENTITY = {
    "enquete": ("enquete", "E-", 4, ["id", "industriel_id", "date_visite", "type", "agent", "statut", "observations"]),
    "reclamation": ("reclamation", "RC-", 4, ["id", "industriel_id", "type", "date_ouverture", "date_cloture", "statut", "objet", "gravite"]),
    "consommation": ("consommation", "C-", 4, ["id", "industriel_id", "periode", "volume_m3", "source", "cree_le"]),
    "dossier": ("dossier", "D-", 4, ["id", "industriel_id", "type", "numero", "date_debut", "date_echeance", "statut", "autorite", "objet", "cree_le"]),
    "document": ("document", "DOC-", 4, ["id", "industriel_id", "contexte", "source_id", "type", "nom", "url", "date"]),
    "industriel": ("industriel", "INDP", 4, ["id", "code", "ancien_code", "direction_id", "code_dp", "ordre_dp", "raison_sociale", "secteur", "zone_id", "province", "statut", "ice", "rc", "gps_lat", "gps_lon", "contact", "telephone", "email", "effectif", "surface_m2", "capacite", "regime_travail", "date_implantation", "cree_le"]),
    "zone": ("zone_industrielle", "Z", 2, ["id", "code", "libelle", "province", "lat", "lon"]),
}


def connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    return conn


def now_iso() -> str:
    return datetime.now().isoformat(timespec="seconds")


def next_id(conn: sqlite3.Connection, table: str, prefix: str, width: int) -> str:
    rows = conn.execute(f"SELECT id FROM {table} WHERE id LIKE ?", (prefix + "%",)).fetchall()
    nums = []
    for row in rows:
        suffix = row[0][len(prefix):]
        if suffix.isdigit():
            nums.append(int(suffix))
    n = max(nums) + 1 if nums else 1
    return f"{prefix}{n:0{width}d}"


def next_industry_code(conn: sqlite3.Connection, code_dp: str):
    prefix = f"IN{code_dp}-"
    rows = conn.execute("SELECT code FROM industriel WHERE code LIKE ?", (prefix + "%",)).fetchall()
    nums = []
    for row in rows:
        m = re.match(r"^INDP[A-Z]*-(\d+)$", row[0])
        if m:
            nums.append(int(m.group(1)))
    n = max(nums) + 1 if nums else 1
    return f"{prefix}{n:04d}", n


def journal(conn, utilisateur: str, action: str, entite: str, detail: str) -> None:
    jid = next_id(conn, "journal", "J-", 6)
    conn.execute(
        "INSERT INTO journal(id,date_action,utilisateur,action,entite,detail) VALUES (?,?,?,?,?,?)",
        (jid, now_iso(), utilisateur or "Utilisateur APEX", action, entite, detail),
    )


def reset_demo(conn: sqlite3.Connection) -> None:
    seed = json.loads(SEED_PATH.read_text(encoding="utf-8"))
    conn.executescript("PRAGMA foreign_keys = OFF;")
    for table in ["resultat_analyse", "prelevement", "enquete", "reclamation", "consommation",
                  "dossier", "document", "industriel", "direction", "zone_industrielle",
                  "parametre", "journal", "meta"]:
        conn.execute(f"DROP TABLE IF EXISTS {table}")
    conn.executescript(SCHEMA)
    meta = dict(seed["meta"])
    conn.execute("INSERT INTO meta(id,payload,updated_at) VALUES (1,?,?)",
                 (json.dumps(meta, ensure_ascii=False), now_iso()))
    for d in seed["directions"]:
        conn.execute("INSERT INTO direction(id,code_dp,libelle,province,ordre) VALUES (?,?,?,?,?)",
                     (d["id"], d["code_dp"], d["libelle"], d["province"], d["ordre"]))
    for p in seed["parametres"]:
        conn.execute("""INSERT INTO parametre(code,libelle,unite,seuil_min,seuil_max,famille,article,note,actif,derive,ordre,source_ref)
                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
                     (p["code"], p["libelle"], p["unite"], p["min"], p["max"], p["famille"],
                      p["article"], p["note"], 1 if p["actif"] else 0, 1 if p["derived"] else 0,
                      p["ordre"], p["source"]))
    for z in seed["zones"]:
        conn.execute("INSERT INTO zone_industrielle(id,code,libelle,province,lat,lon) VALUES (?,?,?,?,?,?)",
                     (z["id"], z["code"], z["libelle"], z["province"], z["lat"], z["lon"]))
    for i in seed["industriels"]:
        conn.execute("""INSERT INTO industriel(id,code,ancien_code,direction_id,code_dp,ordre_dp,
                        raison_sociale,secteur,zone_id,province,statut,ice,rc,gps_lat,gps_lon,
                        contact,telephone,email,effectif,surface_m2,capacite,regime_travail,date_implantation,cree_le)
                        VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                     (i["id"], i["code"], i["ancien_code"], i["direction_id"], i["code_dp"], i["ordre_dp"],
                      i["raison_sociale"], i["secteur"], i["zone_id"], i["province"],
                      i["statut"], i["ice"], i["rc"], i["gps_lat"], i["gps_lon"], i["contact"],
                      i["telephone"], i["email"], i["effectif"], i["surface_m2"], i["capacite"],
                      i["regime_travail"], i["date_implantation"], i["cree_le"]))
    for p in seed["prelevements"]:
        conn.execute("""INSERT INTO prelevement(id,industriel_id,date_prelevement,point,laboratoire,type_prelevement,reference,commentaire,cree_le)
                        VALUES (?,?,?,?,?,?,?,?,?)""",
                     (p["id"], p["industriel_id"], p["date"], p["point"], p["laboratoire"], p["type"],
                      p["reference"], p["commentaire"], p["cree_le"]))
    for r in seed["resultats"]:
        conn.execute("""INSERT INTO resultat_analyse(id,prelevement_id,code_parametre,valeur,saisi_par,date_saisie)
                        VALUES (?,?,?,?,?,?)""",
                     (r["id"], r["prelevement_id"], r["code_parametre"], r["valeur"],
                      r["saisi_par"], r["date_saisie"]))
    for e in seed["enquetes"]:
        conn.execute("INSERT INTO enquete(id,industriel_id,date_visite,type,agent,statut,observations) VALUES (?,?,?,?,?,?,?)",
                     (e["id"], e["industriel_id"], e["date"], e["type"], e["agent"], e["statut"], e["observations"]))
    for r in seed["reclamations"]:
        conn.execute("""INSERT INTO reclamation(id,industriel_id,type,date_ouverture,date_cloture,statut,objet,gravite)
                        VALUES (?,?,?,?,?,?,?,?)""",
                     (r["id"], r["industriel_id"], r["type"], r["date_ouverture"], r["date_cloture"],
                      r["statut"], r["objet"], r["gravite"]))
    for c in seed["consommations"]:
        conn.execute("INSERT INTO consommation(id,industriel_id,periode,volume_m3,source,cree_le) VALUES (?,?,?,?,?,?)",
                     (c["id"], c["industriel_id"], c["periode"], c["volume_m3"], c["source"], c["cree_le"]))
    for d in seed["dossiers"]:
        conn.execute("""INSERT INTO dossier(id,industriel_id,type,numero,date_debut,date_echeance,statut,autorite,objet,cree_le)
                        VALUES (?,?,?,?,?,?,?,?,?,?)""",
                     (d["id"], d["industriel_id"], d["type"], d["numero"], d["date_debut"],
                      d["date_echeance"], d["statut"], d["autorite"], d["objet"], d["cree_le"]))
    for d in seed["documents"]:
        conn.execute("""INSERT INTO document(id,industriel_id,contexte,source_id,type,nom,url,date)
                        VALUES (?,?,?,?,?,?,?,?)""",
                     (d["id"], d["industriel_id"], d["contexte"], d["source_id"], d["type"],
                      d["nom"], d["url"], d["date"]))
    for j in seed["journal"]:
        conn.execute("INSERT INTO journal(id,date_action,utilisateur,action,entite,detail) VALUES (?,?,?,?,?,?)",
                     (j["id"], j["date"], j["utilisateur"], j["action"], j["entite"], j["detail"]))
    conn.commit()


def migrate_to_v2(conn: sqlite3.Connection) -> None:
    """Migrate an existing v1 database to the per-direction code format, preserving data."""
    seed = json.loads(SEED_PATH.read_text(encoding="utf-8"))
    tables = {r[0] for r in conn.execute("SELECT name FROM sqlite_master WHERE type='table'").fetchall()}
    if "direction" not in tables:
        conn.execute("""CREATE TABLE IF NOT EXISTS direction (
            id TEXT PRIMARY KEY, code_dp TEXT NOT NULL UNIQUE,
            libelle TEXT NOT NULL, province TEXT NOT NULL, ordre INTEGER NOT NULL)""")
        for d in seed["directions"]:
            conn.execute("INSERT OR REPLACE INTO direction(id,code_dp,libelle,province,ordre) VALUES (?,?,?,?,?)",
                         (d["id"], d["code_dp"], d["libelle"], d["province"], d["ordre"]))
    cols = {r[1] for r in conn.execute("PRAGMA table_info(industriel)").fetchall()}
    needed = {"ancien_code", "direction_id", "code_dp", "ordre_dp"}
    if not needed.issubset(cols):
        for col, decl in [("ancien_code", "TEXT"), ("direction_id", "TEXT REFERENCES direction(id)"),
                          ("code_dp", "TEXT"), ("ordre_dp", "INTEGER")]:
            if col not in cols:
                conn.execute(f"ALTER TABLE industriel ADD COLUMN {col} {decl}")
    conn.commit()
    # Detect legacy codes and build the old -> new mapping.
    rows = conn.execute("SELECT id, code, province, ancien_code FROM industriel ORDER BY id").fetchall()
    mapping = {}
    seq = {}
    for row in rows:
        old = row["id"]
        province = row["province"]
        code_dp = DP_CODES.get(province)
        if not code_dp:
            continue
        if re.match(r"^INDP[A-Z]*-\d{4,}$", old):
            new = old
            m = re.search(r"-(\d+)$", old)
            order = int(m.group(1)) if m else None
            ancien = row["ancien_code"] or None
        else:
            order = seq.get(code_dp, 0) + 1
            seq[code_dp] = order
            new = f"IN{code_dp}-{order:04d}"
            ancien = row["ancien_code"] or old
        mapping[old] = new
    if not mapping:
        conn.commit()
        return
    conn.execute("PRAGMA foreign_keys = OFF")
    try:
        with conn:
            for row in rows:
                old = row["id"]
                if old not in mapping:
                    continue
                new = mapping[old]
                province = row["province"]
                code_dp = DP_CODES.get(province)
                m = re.search(r"-(\d+)$", new)
                order = int(m.group(1)) if m else None
                ancien = row["ancien_code"] or (old if old != new else None)
                conn.execute("""UPDATE industriel SET id=?, code=?, ancien_code=?, direction_id=?, code_dp=?, ordre_dp=?
                                WHERE id=?""",
                             (new, new, ancien, code_dp, code_dp, order, old))
            for old, new in mapping.items():
                if old == new:
                    continue
                for table in ["prelevement", "enquete", "reclamation", "consommation", "dossier", "document"]:
                    conn.execute(f"UPDATE {table} SET industriel_id=? WHERE industriel_id=?", (new, old))
                conn.execute("UPDATE document SET source_id=? WHERE contexte='INDUSTRIEL' AND source_id=?", (new, old))
                conn.execute("UPDATE journal SET detail=REPLACE(detail, ?, ?) WHERE detail LIKE ?",
                             (old, new, f"%{old}%"))
            meta_row = conn.execute("SELECT payload FROM meta WHERE id=1").fetchone()
            if meta_row:
                meta = json.loads(meta_row[0])
                meta["version"] = 2
                meta["code_format"] = "IN + CODE_DP + '-' + NNNN"
                meta["demo_notice"] = seed["meta"]["demo_notice"]
                conn.execute("UPDATE meta SET payload=?, updated_at=? WHERE id=1",
                             (json.dumps(meta, ensure_ascii=False), now_iso()))
            journal(conn, "Administrateur", "MIGRATION_CODES", "INDUSTRIEL",
                    f"Migration vers la numérotation par direction provinciale : {len(mapping)} industriel(s) recodé(s), anciens codes conservés dans ANCIEN_CODE.")
    finally:
        conn.execute("PRAGMA foreign_keys = ON")
    conn.commit()


def ensure_db() -> None:
    fresh = not DB_PATH.exists()
    conn = connect()
    try:
        if fresh:
            reset_demo(conn)
        else:
            row = conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='meta'").fetchone()
            if row is None:
                reset_demo(conn)
            else:
                migrate_to_v2(conn)
    finally:
        conn.close()


def build_state(conn: sqlite3.Connection) -> dict:
    def rows(sql, mapper=None):
        out = []
        for row in conn.execute(sql).fetchall():
            item = dict(row)
            if mapper:
                item = mapper(item)
            out.append(item)
        return out

    meta = json.loads(conn.execute("SELECT payload FROM meta WHERE id=1").fetchone()[0])
    meta["mode"] = "SQLite normalisée — serveur local"
    meta["updated_at"] = conn.execute("SELECT updated_at FROM meta WHERE id=1").fetchone()[0]

    def p_map(x):
        return {"code": x["code"], "libelle": x["libelle"], "unite": x["unite"],
                "min": x["seuil_min"], "max": x["seuil_max"], "famille": x["famille"],
                "article": x["article"], "note": x["note"], "actif": bool(x["actif"]),
                "derived": bool(x["derive"]), "ordre": x["ordre"], "source": x["source_ref"]}

    def pre_map(x):
        return {"id": x["id"], "industriel_id": x["industriel_id"], "date": x["date_prelevement"],
                "point": x["point"], "laboratoire": x["laboratoire"], "type": x["type_prelevement"],
                "reference": x["reference"], "commentaire": x["commentaire"], "cree_le": x["cree_le"]}

    def res_map(x):
        return {"id": x["id"], "prelevement_id": x["prelevement_id"], "code_parametre": x["code_parametre"],
                "valeur": x["valeur"], "saisi_par": x["saisi_par"], "date_saisie": x["date_saisie"]}

    def enq_map(x):
        return {"id": x["id"], "industriel_id": x["industriel_id"], "date": x["date_visite"],
                "type": x["type"], "agent": x["agent"], "statut": x["statut"], "observations": x["observations"]}

    return {
        "meta": meta,
        "directions": rows("SELECT id, code_dp, libelle, province, ordre FROM direction ORDER BY ordre"),
        "parametres": rows("SELECT * FROM parametre ORDER BY ordre", p_map),
        "zones": rows("SELECT * FROM zone_industrielle ORDER BY id"),
        "industriels": rows("SELECT * FROM industriel ORDER BY direction_id, ordre_dp, id"),
        "prelevements": rows("SELECT * FROM prelevement ORDER BY date_prelevement DESC, id DESC", pre_map),
        "resultats": rows("SELECT * FROM resultat_analyse ORDER BY id", res_map),
        "enquetes": rows("SELECT * FROM enquete ORDER BY date_visite DESC", enq_map),
        "reclamations": rows("SELECT * FROM reclamation ORDER BY date_ouverture DESC"),
        "consommations": rows("SELECT * FROM consommation ORDER BY periode DESC, id"),
        "dossiers": rows("SELECT * FROM dossier ORDER BY date_debut DESC"),
        "documents": rows("SELECT * FROM document ORDER BY date DESC"),
        "journal": rows("SELECT id,date_action AS date,utilisateur,action,entite,detail FROM journal ORDER BY date_action DESC, id DESC"),
    }


def validate_value(param: sqlite3.Row, valeur) -> float:
    try:
        v = float(valeur)
    except (TypeError, ValueError):
        raise ValueError(f"Valeur non numérique pour {param['code']}")
    if v != v or v in (float("inf"), float("-inf")):
        raise ValueError(f"Valeur non finie pour {param['code']}")
    code = param["code"]
    if param["unite"] == "0/1":
        if v not in (0.0, 1.0):
            raise ValueError(f"{code} attend 0 ou 1")
    elif code == "TEMPERATURE":
        if not -10 <= v <= 100:
            raise ValueError("Température hors plage physique (-10 à 100 °C)")
    elif code.startswith("PH"):
        if not 0 <= v <= 14:
            raise ValueError("pH hors plage physique (0 à 14)")
    elif v < 0:
        raise ValueError(f"{code} ne peut pas être négatif")
    if v > 1_000_000_000:
        raise ValueError(f"Valeur invraisemblable pour {code}")
    return v


def add_sample(conn: sqlite3.Connection, payload: dict) -> str:
    ind_id = str(payload.get("industriel_id", "")).strip()
    d = str(payload.get("date", "")).strip()
    point = str(payload.get("point", "")).strip() or "Rejet principal"
    lab = str(payload.get("laboratoire", "")).strip() or "Laboratoire SRM-RSK"
    typ = str(payload.get("type", "")).strip() or "REJET_INDUSTRIEL"
    reference = str(payload.get("reference", "")).strip()
    commentaire = str(payload.get("commentaire", "")).strip()
    resultats = payload.get("resultats") or []
    utilisateur = str(payload.get("utilisateur", "")).strip() or "Laboratoire SRM-RSK"

    if not ind_id or conn.execute("SELECT 1 FROM industriel WHERE id=?", (ind_id,)).fetchone() is None:
        raise ValueError("Industriel inconnu")
    if not re.fullmatch(r"\d{4}-\d{2}-\d{2}", d):
        raise ValueError("Date invalide (AAAA-MM-JJ)")
    dd = date.fromisoformat(d)
    if dd > date.today() + timedelta(days=1):
        raise ValueError("Date de prélèvement dans le futur")
    if not resultats:
        raise ValueError("Saisissez au moins un résultat d'analyse")

    pid = next_id(conn, "prelevement", "P-", 5)
    if not reference:
        reference = f"ECH-{d.replace('-', '')}-{pid}"
    if conn.execute("SELECT 1 FROM prelevement WHERE reference=?", (reference,)).fetchone():
        raise ValueError("Référence d'échantillon déjà utilisée")
    if conn.execute("""SELECT 1 FROM prelevement WHERE industriel_id=? AND date_prelevement=? AND point=?
                       AND laboratoire=? AND type_prelevement=?""",
                    (ind_id, d, point, lab, typ)).fetchone():
        raise ValueError("Un prélèvement identique existe déjà pour cet industriel et cette date")

    seen, prepared = set(), []
    for item in resultats:
        code = str(item.get("code_parametre", "")).strip().upper()
        param = conn.execute("SELECT * FROM parametre WHERE code=?", (code,)).fetchone()
        if param is None:
            raise ValueError(f"Paramètre inconnu: {code}")
        if not param["actif"]:
            raise ValueError(f"Paramètre inactif ou dérogatoire: {code}")
        if param["derive"]:
            raise ValueError(f"{code} est calculé automatiquement et ne doit pas être saisi")
        if code in seen:
            raise ValueError(f"Paramètre en double dans le prélèvement: {code}")
        seen.add(code)
        prepared.append((code, validate_value(param, item.get("valeur"))))

    with conn:
        conn.execute("""INSERT INTO prelevement(id,industriel_id,date_prelevement,point,laboratoire,
                        type_prelevement,reference,commentaire,cree_le) VALUES (?,?,?,?,?,?,?,?,?)""",
                     (pid, ind_id, d, point, lab, typ, reference, commentaire, now_iso()))
        for code, valeur in prepared:
            rid = next_id(conn, "resultat_analyse", "R-", 6)
            conn.execute("""INSERT INTO resultat_analyse(id,prelevement_id,code_parametre,valeur,saisi_par,date_saisie)
                            VALUES (?,?,?,?,?,?)""", (rid, pid, code, valeur, utilisateur, now_iso()))
        journal(conn, utilisateur, "SAISIE_PRELEVEMENT", "PRELEVEMENT",
                f"{pid} / {ind_id} / {d}: {len(prepared)} résultat(s) brut(s). Conformité, alertes et KPI dérivés automatiquement.")
        conn.execute("UPDATE meta SET updated_at=? WHERE id=1", (now_iso(),))
    return pid


def add_record(conn: sqlite3.Connection, payload: dict) -> str:
    entity = payload.get("entity")
    data = dict(payload.get("data") or {})
    utilisateur = str(payload.get("utilisateur", "")).strip() or "Utilisateur APEX"
    if entity not in ENTITY:
        raise ValueError("Entité non autorisée")
    table, prefix, width, fields = ENTITY[entity]

    if entity == "industriel":
        province = str(data.get("province") or "").strip()
        code_dp = str(data.get("direction_id") or DP_CODES.get(province) or "").strip()
        if code_dp not in DP_CODES.values():
            raise ValueError("Direction provinciale / préfectorale inconnue")
        if conn.execute("SELECT 1 FROM direction WHERE id=?", (code_dp,)).fetchone() is None:
            raise ValueError("Direction provinciale absente du référentiel")
        if not data.get("raison_sociale") or not province or not data.get("secteur"):
            raise ValueError("Raison sociale, province et secteur sont obligatoires")
        eid, order = next_industry_code(conn, code_dp)
        data["id"] = eid
        data["code"] = eid
        data["ancien_code"] = None
        data["direction_id"] = code_dp
        data["code_dp"] = code_dp
        data["ordre_dp"] = order
        data.setdefault("statut", "Actif")
        data["cree_le"] = now_iso()
        if data.get("ice") and not re.fullmatch(r"\d{15}", str(data["ice"])):
            raise ValueError("ICE invalide: 15 chiffres attendus")
        if data.get("zone_id") and conn.execute("SELECT 1 FROM zone_industrielle WHERE id=?", (data["zone_id"],)).fetchone() is None:
            raise ValueError("Zone industrielle inconnue")
    else:
        eid = next_id(conn, table, prefix, width)
        data["id"] = eid
        if entity == "zone":
            if not data.get("code") or not data.get("libelle") or not data.get("province"):
                raise ValueError("Code, libellé et province sont obligatoires")
        else:
            if not data.get("industriel_id") or conn.execute("SELECT 1 FROM industriel WHERE id=?", (data["industriel_id"],)).fetchone() is None:
                raise ValueError("Industriel inconnu")
        if entity == "consommation":
            try:
                data["volume_m3"] = float(data.get("volume_m3"))
            except (TypeError, ValueError):
                raise ValueError("Volume invalide")
            if data["volume_m3"] < 0:
                raise ValueError("Le volume ne peut pas être négatif")
            data.setdefault("cree_le", now_iso())
        if entity == "reclamation" and data.get("date_cloture") and data.get("date_ouverture"):
            if str(data["date_cloture"]) < str(data["date_ouverture"]):
                raise ValueError("Date de clôture antérieure à l'ouverture")
        if entity == "dossier":
            if data.get("date_echeance") and str(data["date_echeance"]) < str(data["date_debut"]):
                raise ValueError("Échéance antérieure à la date de début")
            data.setdefault("cree_le", now_iso())
        if entity == "enquete" and not data.get("date_visite"):
            raise ValueError("Date de visite obligatoire")
        if entity == "document" and not data.get("date"):
            raise ValueError("Date du document obligatoire")

    cols = [k for k in fields if k in data]
    values = [data[k] for k in cols]
    with conn:
        conn.execute(f"INSERT INTO {table} ({','.join(cols)}) VALUES ({','.join('?' for _ in cols)})", values)
        detail = f"{eid} créé." if entity != "industriel" else f"{eid} créé ({code_dp}, ordre {order}). Code généré automatiquement par direction."
        journal(conn, utilisateur, f"AJOUT_{entity.upper()}", table.upper(), detail)
        conn.execute("UPDATE meta SET updated_at=? WHERE id=1", (now_iso(),))
    return eid


def delete_record(conn: sqlite3.Connection, payload: dict) -> None:
    entity = payload.get("entity")
    rid = str(payload.get("id", "")).strip()
    utilisateur = str(payload.get("utilisateur", "")).strip() or "Utilisateur APEX"
    allowed = {
        "prelevement": "prelevement", "resultat": "resultat_analyse", "enquete": "enquete",
        "reclamation": "reclamation", "consommation": "consommation",
        "dossier": "dossier", "document": "document",
    }
    if entity not in allowed or not rid:
        raise ValueError("Suppression non autorisée")
    table = allowed[entity]
    with conn:
        cur = conn.execute(f"DELETE FROM {table} WHERE id=?", (rid,))
        if cur.rowcount == 0:
            raise ValueError("Enregistrement introuvable")
        journal(conn, utilisateur, f"SUPPRESSION_{entity.upper()}", table.upper(),
                f"{rid} supprimé (action tracée).")
        conn.execute("UPDATE meta SET updated_at=? WHERE id=1", (now_iso(),))


class Handler(BaseHTTPRequestHandler):
    server_version = "SRM-RSK/1.1"

    def _send(self, code: int, body: bytes, content_type: str, extra=None):
        self.send_response(code)
        self.send_header("Content-Type", content_type)
        self.send_header("Content-Length", str(len(body)))
        self.send_header("Cache-Control", "no-store")
        self.send_header("Access-Control-Allow-Origin", "*")
        self.send_header("Access-Control-Allow-Headers", "Content-Type")
        self.send_header("Access-Control-Allow-Methods", "GET, POST, OPTIONS")
        for k, v in (extra or {}).items():
            self.send_header(k, v)
        self.end_headers()
        if self.command != "HEAD":
            self.wfile.write(body)

    def _json(self, code: int, obj: dict):
        self._send(code, json.dumps(obj, ensure_ascii=False).encode("utf-8"),
                   "application/json; charset=utf-8")

    def do_OPTIONS(self):
        self._json(200, {"ok": True})

    def do_HEAD(self):
        self.do_GET()

    def do_GET(self):
        path = urlparse(self.path).path
        if path == "/api/health":
            return self._json(200, {"ok": True, "service": "SRM-RSK dashboard API", "database": str(DB_PATH)})
        if path == "/api/state":
            conn = connect()
            try:
                return self._json(200, {"ok": True, "state": build_state(conn)})
            except Exception as e:
                traceback.print_exc()
                return self._json(500, {"ok": False, "error": str(e)})
            finally:
                conn.close()
        if path in STATIC:
            filename, ctype = STATIC[path]
            file_path = ROOT / filename
            if not file_path.exists():
                return self._json(404, {"ok": False, "error": f"Fichier absent: {filename}"})
            return self._send(200, file_path.read_bytes(), ctype)
        return self._json(404, {"ok": False, "error": "Ressource introuvable"})

    def do_POST(self):
        path = urlparse(self.path).path
        if path not in {"/api/sample", "/api/record", "/api/delete", "/api/reset"}:
            return self._json(404, {"ok": False, "error": "Endpoint inconnu"})
        try:
            length = int(self.headers.get("Content-Length", "0"))
            payload = json.loads(self.rfile.read(length).decode("utf-8")) if length else {}
            conn = connect()
            try:
                message = "Opération effectuée"
                if path == "/api/sample":
                    pid = add_sample(conn, payload)
                    message = f"Prélèvement {pid} enregistré. Conformité et KPI recalculés automatiquement."
                elif path == "/api/record":
                    eid = add_record(conn, payload)
                    message = f"Enregistrement {eid} ajouté."
                elif path == "/api/delete":
                    delete_record(conn, payload)
                    message = "Enregistrement supprimé et action tracée."
                elif path == "/api/reset":
                    reset_demo(conn)
                    message = "Jeu de démonstration réinitialisé (codes par direction provinciale)."
                state = build_state(conn)
                return self._json(200, {"ok": True, "message": message, "state": state})
            finally:
                conn.close()
        except Exception as e:
            traceback.print_exc()
            return self._json(400, {"ok": False, "error": str(e)})

    def log_message(self, fmt, *args):
        print(f"[{now_iso()}] {self.address_string()} - {fmt % args}", flush=True)


def main():
    ensure_db()
    server = ThreadingHTTPServer(("0.0.0.0", PORT), Handler)
    server.daemon_threads = True
    print(f"SRM-RSK dashboard ready: http://0.0.0.0:{PORT}/  (DB: {DB_PATH})", flush=True)
    try:
        server.serve_forever()
    except KeyboardInterrupt:
        pass
    finally:
        server.server_close()


if __name__ == "__main__":
    main()