"""Déclencheurs d'immuabilité, droits du rôle applicatif, configuration initiale (rôles, 14 indicateurs).

Revision ID: 0002
Revises: 0001
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects.postgresql import JSONB

revision = "0002"
down_revision = "0001"
branch_labels = None
depends_on = None

PERMISSIONS = [
    ("bilan.read", "Consulter les bilans et le document final"),
    ("bilan.edit", "Saisir et modifier un brouillon"),
    ("bilan.validate", "Valider définitivement un bilan"),
    ("bilan.reopen", "Rouvrir un bilan validé"),
    ("bilan.archive", "Archiver un bilan"),
    ("import.manage", "Importer des documents et valider les imports"),
    ("export.create", "Générer des exports"),
    ("stats.read", "Consulter le tableau de bord"),
    ("history.read", "Consulter l'historique et les versions"),
    ("indicator.manage", "Gérer les indicateurs"),
    ("user.manage", "Gérer les comptes"),
    ("settings.manage", "Modifier les paramètres"),
    ("audit.read", "Consulter le journal d'audit"),
]
ADMIN_PERMS = [p for p, _ in PERMISSIONS]
READER_PERMS = ["bilan.read", "stats.read"]  # export.create : accordé compte par compte (can_export)

# Configuration initiale du cahier des charges : ordre et libellés exacts, acronymes non développés.
INDICATORS = [
    ("alcool_delictuelle", "Alcoolémie délictuelle", "taux", "",
     ["alcoolemie delictuelle", "alcool delictuel", "alcoolemie delit"]),
    ("alcool_contraventionnelle", "Alcoolémie contraventionnelle", None, "",
     ["alcoolemie contraventionnelle", "alcool contraventionnel", "alcoolemie contravention"]),
    ("stupefiants", "Conduite sous stupéfiants", None, "",
     ["conduite sous stupefiants", "conduite stupefiants", "cs stupefiants", "stupefiants conduite"]),
    ("vitesse_plus_50", "Vitesse (+ 50 km/h)", "vitesse", "km/h",
     ["vitesse + 50", "vitesse plus 50", "vitesse 50 et plus", "vitesse superieure a 50"]),
    ("vitesse_40_49", "Vitesse (40 à 49 km/h)", None, "",
     ["vitesse 40 a 49", "vitesse 40 49", "vitesse de 40 a 49"]),
    ("vitesse_39_moins", "Vitesse (inférieure ou égale à 39 km/h)", None, "",
     ["vitesse inferieure ou egale a 39", "vitesse 39", "vitesse moins de 40", "vitesse inferieure a 40"]),
    ("distracteurs", "Distracteurs", None, "", ["distracteur", "telephone"]),
    ("afd_stupefiants", "AFD Stupéfiants", None, "", ["afd stup", "afd stupefiant"]),
    ("afd_assurance", "AFD Assurance", None, "", ["afd assurances"]),
    ("afd_permis", "AFD Permis", None, "", ["afd permis de conduire"]),
    ("contrefacons", "Contrefaçons", None, "", ["contrefacon"]),
    ("esi", "ESI", None, "", []),
    ("autres_infractions", "Autres infractions", None, "", ["autres", "autre infraction", "divers"]),
    ("enquetes_judiciaires", "Enquêtes judiciaires", None, "", ["enquete judiciaire", "enquetes"]),
]

APPEND_ONLY_SQL = """
CREATE OR REPLACE FUNCTION edcf_append_only() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'La table % est en ajout seul (opération % refusée).', TG_TABLE_NAME, TG_OP
    USING ERRCODE = 'insufficient_privilege';
END $$;

CREATE OR REPLACE FUNCTION edcf_versions_guard() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  -- Seules les versions de démonstration peuvent être supprimées (commande demo-clear).
  IF TG_OP = 'DELETE' AND OLD.is_demo THEN
    RETURN OLD;
  END IF;
  RAISE EXCEPTION 'Les versions de bilan sont immuables (opération % refusée).', TG_OP
    USING ERRCODE = 'insufficient_privilege';
END $$;

CREATE TRIGGER audit_log_append_only BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION edcf_append_only();
CREATE TRIGGER bilan_versions_immutable BEFORE UPDATE OR DELETE ON bilan_versions
  FOR EACH ROW EXECUTE FUNCTION edcf_versions_guard();
"""

GRANTS_SQL = """
DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'edcf_app') THEN
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO edcf_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO edcf_app;
    -- Moindre privilège : journal et versions sans UPDATE ; journal sans DELETE.
    REVOKE UPDATE, DELETE, TRUNCATE ON audit_log FROM edcf_app;
    REVOKE UPDATE, TRUNCATE ON bilan_versions FROM edcf_app;
    REVOKE ALL ON alembic_version FROM edcf_app;
    GRANT SELECT ON alembic_version TO edcf_app;
    REVOKE INSERT, UPDATE, DELETE ON roles, permissions, role_permissions FROM edcf_app;
  END IF;
END $$;
"""


def upgrade() -> None:
    op.execute(APPEND_ONLY_SQL)

    roles = sa.table("roles", sa.column("code"), sa.column("label"))
    perms = sa.table("permissions", sa.column("code"), sa.column("label"))
    rp = sa.table("role_permissions", sa.column("role_code"), sa.column("permission_code"))
    op.bulk_insert(roles, [
        {"code": "admin", "label": "Administrateur / gestionnaire"},
        {"code": "reader", "label": "Lecture seule"},
    ])
    op.bulk_insert(perms, [{"code": c, "label": lbl} for c, lbl in PERMISSIONS])
    op.bulk_insert(rp, [{"role_code": "admin", "permission_code": p} for p in ADMIN_PERMS]
                   + [{"role_code": "reader", "permission_code": p} for p in READER_PERMS])

    cats = sa.table("indicator_categories", sa.column("id"), sa.column("label"), sa.column("position"))
    op.bulk_insert(cats, [{"id": 1, "label": "Infractions relevées", "position": 1}])
    op.execute("SELECT setval(pg_get_serial_sequence('indicator_categories','id'), 1)")

    ind = sa.table(
        "indicators", sa.column("code"), sa.column("label"), sa.column("position"), sa.column("is_active"),
        sa.column("category_id"), sa.column("detail_kind"), sa.column("detail_unit"),
        sa.column("include_in_total"), sa.column("aliases", JSONB),
    )
    op.bulk_insert(ind, [
        {"code": code, "label": label, "position": i + 1, "is_active": True, "category_id": 1,
         "detail_kind": kind, "detail_unit": unit, "include_in_total": True, "aliases": aliases}
        for i, (code, label, kind, unit, aliases) in enumerate(INDICATORS)
    ])
    op.execute(GRANTS_SQL)


def downgrade() -> None:
    op.execute("DROP TRIGGER IF EXISTS audit_log_append_only ON audit_log")
    op.execute("DROP TRIGGER IF EXISTS bilan_versions_immutable ON bilan_versions")
    op.execute("DROP FUNCTION IF EXISTS edcf_append_only()")
    op.execute("DROP FUNCTION IF EXISTS edcf_versions_guard()")
    op.execute("DELETE FROM indicators; DELETE FROM indicator_categories; DELETE FROM role_permissions;"
               " DELETE FROM permissions; DELETE FROM roles;")