-- =====================================================================
-- SRM-RSK | BASE DE DONNEES INDUSTRIELS
-- DDL Oracle 12c+ / 19c / 23ai - modele 3NF, conformite automatisee
-- Sources : presentation synthetique, canevas de collecte (11 onglets),
--           cahier des charges d'assainissement liquide (Art. 42 a 48).
-- =====================================================================
-- Principe : l'utilisateur saisit UNIQUEMENT les valeurs brutes d'analyse.
-- Le code industriel est genere par direction : IN + CODE_DP + '-' + NNNN.
-- Les statuts, alertes, total metaux lourds, KPI et cartes sont derives.
-- =====================================================================
SET DEFINE OFF;
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- ---------------------------------------------------------------------
-- 1. REFERENTIELS
-- ---------------------------------------------------------------------
CREATE TABLE ref_province (
  province_id      NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_province PRIMARY KEY,
  code_province    VARCHAR2(10 CHAR) NOT NULL CONSTRAINT uq_ref_province_code UNIQUE,
  libelle          VARCHAR2(100 CHAR) NOT NULL,
  region           VARCHAR2(100 CHAR) DEFAULT 'Rabat-Sale-Kenitra' NOT NULL,
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_province_actif CHECK (actif IN ('O','N')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE direction (
  code_dp           VARCHAR2(10 CHAR) CONSTRAINT pk_direction PRIMARY KEY,
  libelle           VARCHAR2(150 CHAR) NOT NULL,
  province_id       NUMBER(10) NOT NULL CONSTRAINT fk_direction_province REFERENCES ref_province(province_id),
  ordre_affichage   NUMBER(3) NOT NULL CONSTRAINT ck_direction_ordre CHECK (ordre_affichage > 0),
  dernier_ordre     NUMBER(6) DEFAULT 0 NOT NULL CONSTRAINT ck_direction_dernier CHECK (dernier_ordre >= 0),
  CONSTRAINT uq_direction_ordre UNIQUE (ordre_affichage),
  CONSTRAINT ck_direction_code CHECK (REGEXP_LIKE(code_dp, '^DP[A-Z]+$')),
  cree_par          VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le           TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par       VARCHAR2(64 CHAR),
  modifie_le        TIMESTAMP(6) WITH TIME ZONE,
  date_archivage    DATE
);

CREATE TABLE ref_secteur_activite (
  secteur_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_secteur PRIMARY KEY,
  code_secteur     VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_secteur_code UNIQUE,
  libelle          VARCHAR2(150 CHAR) NOT NULL,
  code_naf         VARCHAR2(20 CHAR),
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_secteur_actif CHECK (actif IN ('O','N')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE ref_liste (
  code_liste       VARCHAR2(40 CHAR) CONSTRAINT pk_ref_liste PRIMARY KEY,
  libelle          VARCHAR2(150 CHAR) NOT NULL,
  description      VARCHAR2(4000 CHAR),
  modifiable       CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_ref_liste_mod CHECK (modifiable IN ('O','N')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE ref_valeur_liste (
  code_liste       VARCHAR2(40 CHAR) NOT NULL,
  code_valeur      VARCHAR2(40 CHAR) NOT NULL,
  libelle          VARCHAR2(150 CHAR) NOT NULL,
  ordre_affichage  NUMBER(5) DEFAULT 0 NOT NULL CONSTRAINT ck_ref_valeur_ordre CHECK (ordre_affichage >= 0),
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_valeur_actif CHECK (actif IN ('O','N')),
  valeur_num       NUMBER(12,3),
  CONSTRAINT pk_ref_valeur_liste PRIMARY KEY (code_liste, code_valeur),
  CONSTRAINT fk_ref_valeur_liste FOREIGN KEY (code_liste) REFERENCES ref_liste(code_liste),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE ref_unite (
  unite_id         NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_unite PRIMARY KEY,
  code_unite       VARCHAR2(20 CHAR) NOT NULL CONSTRAINT uq_ref_unite_code UNIQUE,
  libelle          VARCHAR2(80 CHAR) NOT NULL,
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE zone_industrielle (
  zone_id          NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_zone_ind PRIMARY KEY,
  code_zone        VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_zone_ind_code UNIQUE,
  libelle          VARCHAR2(150 CHAR) NOT NULL,
  province_id      NUMBER(10) NOT NULL CONSTRAINT fk_zone_ind_province REFERENCES ref_province(province_id),
  latitude         NUMBER(9,6) CONSTRAINT ck_zone_ind_lat CHECK (latitude IS NULL OR latitude BETWEEN -90 AND 90),
  longitude        NUMBER(9,6) CONSTRAINT ck_zone_ind_lon CHECK (longitude IS NULL OR longitude BETWEEN -180 AND 180),
  superficie_ha    NUMBER(10,2) CONSTRAINT ck_zone_ind_sup CHECK (superficie_ha IS NULL OR superficie_ha >= 0),
  -- geometrie     SDO_GEOMETRY,  -- activer si Oracle Spatial / APEX Map Region
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

-- ---------------------------------------------------------------------
-- 2. IDENTIFICATION DES INDUSTRIELS
-- ---------------------------------------------------------------------
CREATE TABLE industriel (
  industriel_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_industriel PRIMARY KEY,
  code_industriel     VARCHAR2(20 CHAR) NOT NULL CONSTRAINT uq_industriel_code UNIQUE,
  ancien_code         VARCHAR2(20 CHAR) CONSTRAINT uq_industriel_ancien UNIQUE,
  direction_id        VARCHAR2(10 CHAR) NOT NULL CONSTRAINT fk_industriel_direction REFERENCES direction(code_dp),
  code_dp             VARCHAR2(10 CHAR) NOT NULL,
  ordre_dp            NUMBER(6) NOT NULL CONSTRAINT ck_industriel_ordre CHECK (ordre_dp > 0),
  raison_sociale      VARCHAR2(200 CHAR) NOT NULL,
  ice                 CHAR(15) CONSTRAINT uq_industriel_ice UNIQUE,
  rc                  VARCHAR2(30 CHAR),
  secteur_id          NUMBER(10) NOT NULL CONSTRAINT fk_industriel_secteur REFERENCES ref_secteur_activite(secteur_id),
  zone_id             NUMBER(10) CONSTRAINT fk_industriel_zone REFERENCES zone_industrielle(zone_id),
  province_id         NUMBER(10) NOT NULL CONSTRAINT fk_industriel_province REFERENCES ref_province(province_id),
  statut              VARCHAR2(30 CHAR) NOT NULL,
  gps_lat             NUMBER(9,6) CONSTRAINT ck_industriel_lat CHECK (gps_lat IS NULL OR gps_lat BETWEEN -90 AND 90),
  gps_lon             NUMBER(9,6) CONSTRAINT ck_industriel_lon CHECK (gps_lon IS NULL OR gps_lon BETWEEN -180 AND 180),
  effectif            NUMBER(8) CONSTRAINT ck_industriel_effectif CHECK (effectif IS NULL OR effectif >= 0),
  surface_m2          NUMBER(12,2) CONSTRAINT ck_industriel_surface CHECK (surface_m2 IS NULL OR surface_m2 >= 0),
  capacite_production VARCHAR2(200 CHAR),
  regime_travail      VARCHAR2(50 CHAR),
  date_implantation   DATE,
  CONSTRAINT ck_industriel_code CHECK (REGEXP_LIKE(code_industriel, '^INDP[A-Z]*-[0-9]{4,}$')),
  CONSTRAINT ck_industriel_code_genere CHECK (code_industriel = 'IN' || direction_id || '-' || TO_CHAR(ordre_dp, 'FM0000')),
  CONSTRAINT ck_industriel_code_dp CHECK (code_dp = direction_id),
  CONSTRAINT ck_industriel_ice CHECK (ice IS NULL OR REGEXP_LIKE(ice, '^[0-9]{15}$')),
  CONSTRAINT ck_industriel_statut CHECK (statut IN ('Actif','Arret','A verifier','En implantation')),
  CONSTRAINT uq_industriel_direction_ordre UNIQUE (direction_id, ordre_dp),
  cree_par            VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le             TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par         VARCHAR2(64 CHAR),
  modifie_le          TIMESTAMP(6) WITH TIME ZONE,
  date_archivage      DATE
);

CREATE TABLE contact_industriel (
  contact_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_contact_ind PRIMARY KEY,
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_contact_ind_industriel REFERENCES industriel(industriel_id) ON DELETE CASCADE,
  type_contact     VARCHAR2(30 CHAR) NOT NULL,
  nom              VARCHAR2(120 CHAR) NOT NULL,
  fonction         VARCHAR2(100 CHAR),
  telephone        VARCHAR2(30 CHAR),
  email            VARCHAR2(150 CHAR),
  principal        CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_contact_principal CHECK (principal IN ('O','N')),
  CONSTRAINT ck_contact_email CHECK (email IS NULL OR REGEXP_LIKE(email, '^[^@]+@[^@]+\.[^@]+$')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);
CREATE UNIQUE INDEX uq_contact_principal ON contact_industriel (industriel_id, CASE WHEN principal = 'O' THEN 1 END);

-- ---------------------------------------------------------------------
-- 3. ABONNEMENTS, BRANCHEMENTS, COMPTEURS (Art. 47)
-- ---------------------------------------------------------------------
CREATE TABLE ref_type_branchement (
  type_branchement_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_branchement PRIMARY KEY,
  code_type           VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_branchement UNIQUE,
  libelle             VARCHAR2(120 CHAR) NOT NULL,
  cree_par            VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le             TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par         VARCHAR2(64 CHAR),
  modifie_le          TIMESTAMP(6) WITH TIME ZONE,
  date_archivage      DATE
);

CREATE TABLE branchement (
  branchement_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_branchement PRIMARY KEY,
  industriel_id        NUMBER(10) NOT NULL CONSTRAINT fk_branchement_industriel REFERENCES industriel(industriel_id),
  type_branchement_id  NUMBER(10) NOT NULL CONSTRAINT fk_branchement_type REFERENCES ref_type_branchement(type_branchement_id),
  reference_branchement VARCHAR2(50 CHAR) NOT NULL CONSTRAINT uq_branchement_ref UNIQUE,
  date_mise_en_service DATE,
  etat                 VARCHAR2(30 CHAR) NOT NULL,
  regard_acces         VARCHAR2(100 CHAR),
  CONSTRAINT uq_branchement_unique UNIQUE (industriel_id, type_branchement_id, date_mise_en_service),
  cree_par             VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le              TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par          VARCHAR2(64 CHAR),
  modifie_le           TIMESTAMP(6) WITH TIME ZONE,
  date_archivage       DATE
);

CREATE TABLE abonnement (
  abonnement_id        NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_abonnement PRIMARY KEY,
  industriel_id        NUMBER(10) NOT NULL CONSTRAINT fk_abonnement_industriel REFERENCES industriel(industriel_id),
  numero_abonnement    VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_abonnement_num UNIQUE,
  type_abonnement      VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_abonnement_type CHECK (type_abonnement IN ('EAU','ASSAINISSEMENT','DEVERSEMENT')),
  date_debut           DATE NOT NULL,
  date_fin             DATE,
  convention_deversement VARCHAR2(80 CHAR),
  statut               VARCHAR2(30 CHAR) NOT NULL,
  CONSTRAINT ck_abonnement_dates CHECK (date_fin IS NULL OR date_fin >= date_debut),
  cree_par             VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le              TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par          VARCHAR2(64 CHAR),
  modifie_le           TIMESTAMP(6) WITH TIME ZONE,
  date_archivage       DATE
);

CREATE TABLE compteur (
  compteur_id      NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_compteur PRIMARY KEY,
  numero_compteur  VARCHAR2(50 CHAR) NOT NULL CONSTRAINT uq_compteur_num UNIQUE,
  abonnement_id    NUMBER(10) CONSTRAINT fk_compteur_abonnement REFERENCES abonnement(abonnement_id),
  branchement_id   NUMBER(10) CONSTRAINT fk_compteur_branchement REFERENCES branchement(branchement_id),
  type_compteur    VARCHAR2(30 CHAR) NOT NULL,
  unite            VARCHAR2(20 CHAR) DEFAULT 'm3' NOT NULL,
  date_pose        DATE,
  CONSTRAINT ck_compteur_rattachement CHECK (abonnement_id IS NOT NULL OR branchement_id IS NOT NULL),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE releve_compteur (
  releve_id        NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_releve_compteur PRIMARY KEY,
  compteur_id      NUMBER(10) NOT NULL CONSTRAINT fk_releve_compteur_compteur REFERENCES compteur(compteur_id),
  date_releve      DATE NOT NULL,
  index_releve     NUMBER(14,2) NOT NULL CONSTRAINT ck_releve_index CHECK (index_releve >= 0),
  volume_m3        NUMBER(14,2) NOT NULL CONSTRAINT ck_releve_volume CHECK (volume_m3 >= 0),
  source_releve    VARCHAR2(40 CHAR) NOT NULL,
  CONSTRAINT uq_releve_compteur UNIQUE (compteur_id, date_releve),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);
-- Controle d'index croissant : a implementer via COMPOUND TRIGGER ou API APEX
-- pour eviter ORA-04091 (table mutante) sur les imports multi-lignes.

-- ---------------------------------------------------------------------
-- 4. ENQUETES ET CONTROLES (Art. 48)
-- ---------------------------------------------------------------------
CREATE TABLE ref_type_visite (
  type_visite_id   NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_visite PRIMARY KEY,
  code_type        VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_visite UNIQUE,
  libelle          VARCHAR2(120 CHAR) NOT NULL,
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE app_role (
  role_id          NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_app_role PRIMARY KEY,
  code_role        VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_app_role UNIQUE,
  libelle          VARCHAR2(120 CHAR) NOT NULL,
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE app_utilisateur (
  utilisateur_id   NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_app_utilisateur PRIMARY KEY,
  username         VARCHAR2(100 CHAR) NOT NULL CONSTRAINT uq_app_utilisateur UNIQUE,
  nom_complet      VARCHAR2(150 CHAR) NOT NULL,
  email            VARCHAR2(150 CHAR),
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_app_user_actif CHECK (actif IN ('O','N')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE affectation_role (
  utilisateur_id   NUMBER(10) NOT NULL CONSTRAINT fk_affect_role_user REFERENCES app_utilisateur(utilisateur_id),
  role_id          NUMBER(10) NOT NULL CONSTRAINT fk_affect_role_role REFERENCES app_role(role_id),
  date_debut       DATE NOT NULL,
  date_fin         DATE,
  CONSTRAINT pk_affectation_role PRIMARY KEY (utilisateur_id, role_id),
  CONSTRAINT ck_affect_role_dates CHECK (date_fin IS NULL OR date_fin >= date_debut),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE enquete_controle (
  enquete_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_enquete PRIMARY KEY,
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_enquete_industriel REFERENCES industriel(industriel_id),
  type_visite_id   NUMBER(10) NOT NULL CONSTRAINT fk_enquete_type REFERENCES ref_type_visite(type_visite_id),
  date_visite      DATE NOT NULL,
  agent_id         NUMBER(10) CONSTRAINT fk_enquete_agent REFERENCES app_utilisateur(utilisateur_id),
  statut           VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_enquete_statut CHECK (statut IN ('PLANIFIEE','REALISEE','REPORTEE')),
  observations     CLOB,
  CONSTRAINT uq_enquete UNIQUE (industriel_id, date_visite, type_visite_id),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE constat_enquete (
  constat_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_constat_enquete PRIMARY KEY,
  enquete_id       NUMBER(10) NOT NULL CONSTRAINT fk_constat_enquete_enquete REFERENCES enquete_controle(enquete_id) ON DELETE CASCADE,
  libelle          VARCHAR2(200 CHAR) NOT NULL,
  gravite          VARCHAR2(30 CHAR) NOT NULL,
  action_requise   VARCHAR2(4000 CHAR),
  echeance         DATE,
  statut_action    VARCHAR2(30 CHAR) NOT NULL,
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

-- ---------------------------------------------------------------------
-- 5. PARAMETRES, SEUILS REGLEMENTAIRES ET DEROGATIONS
-- ---------------------------------------------------------------------
CREATE TABLE ref_parametre (
  parametre_id     NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_parametre PRIMARY KEY,
  code_parametre   VARCHAR2(40 CHAR) NOT NULL CONSTRAINT uq_ref_parametre_code UNIQUE,
  libelle          VARCHAR2(200 CHAR) NOT NULL,
  unite_id         NUMBER(10) NOT NULL CONSTRAINT fk_ref_param_unite REFERENCES ref_unite(unite_id),
  famille          VARCHAR2(60 CHAR) NOT NULL,
  type_valeur      VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_ref_param_type CHECK (type_valeur IN ('NUMERIQUE','BOOLEEN','TEXTE')),
  min_physique     NUMBER(12,4),
  max_physique     NUMBER(12,4),
  est_derive       CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_ref_param_derive CHECK (est_derive IN ('O','N')),
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_param_actif CHECK (actif IN ('O','N')),
  CONSTRAINT ck_ref_param_physique CHECK (min_physique IS NULL OR max_physique IS NULL OR min_physique <= max_physique),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE ref_seuil_reglementaire (
  seuil_id         NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_seuil PRIMARY KEY,
  parametre_id     NUMBER(10) NOT NULL CONSTRAINT fk_ref_seuil_param REFERENCES ref_parametre(parametre_id),
  seuil_min        NUMBER(14,4),
  seuil_max        NUMBER(14,4),
  date_effet       DATE NOT NULL,
  date_fin         DATE,
  portee           VARCHAR2(20 CHAR) DEFAULT 'TOUS' NOT NULL CONSTRAINT ck_ref_seuil_portee CHECK (portee IN ('TOUS','SECTEUR','INDUSTRIEL')),
  secteur_id       NUMBER(10) CONSTRAINT fk_ref_seuil_secteur REFERENCES ref_secteur_activite(secteur_id),
  industriel_id    NUMBER(10) CONSTRAINT fk_ref_seuil_industriel REFERENCES industriel(industriel_id),
  article          VARCHAR2(30 CHAR) NOT NULL,
  reference_texte  VARCHAR2(200 CHAR) NOT NULL,
  actif            CHAR(1) DEFAULT 'O' NOT NULL CONSTRAINT ck_ref_seuil_actif CHECK (actif IN ('O','N')),
  CONSTRAINT ck_ref_seuil_minmax CHECK (seuil_min IS NOT NULL OR seuil_max IS NOT NULL),
  CONSTRAINT ck_ref_seuil_ordre CHECK (seuil_min IS NULL OR seuil_max IS NULL OR seuil_min <= seuil_max),
  CONSTRAINT ck_ref_seuil_dates CHECK (date_fin IS NULL OR date_fin >= date_effet),
  CONSTRAINT ck_ref_seuil_portee2 CHECK (
    (portee = 'TOUS' AND secteur_id IS NULL AND industriel_id IS NULL) OR
    (portee = 'SECTEUR' AND secteur_id IS NOT NULL AND industriel_id IS NULL) OR
    (portee = 'INDUSTRIEL' AND industriel_id IS NOT NULL)),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);
CREATE UNIQUE INDEX uq_seuil_version ON ref_seuil_reglementaire
  (parametre_id, portee, date_effet, NVL(secteur_id,-1), NVL(industriel_id,-1));
-- Anti-chevauchement de periodes : a completer par COMPOUND TRIGGER ou
-- validation applicative APEX (REGEXP / date_effet / date_fin).

CREATE TABLE derogation_seuil (
  derogation_id    NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_derogation PRIMARY KEY,
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_derog_industriel REFERENCES industriel(industriel_id),
  parametre_id     NUMBER(10) NOT NULL CONSTRAINT fk_derog_param REFERENCES ref_parametre(parametre_id),
  seuil_min_derog  NUMBER(14,4),
  seuil_max_derog  NUMBER(14,4),
  date_debut       DATE NOT NULL,
  date_fin         DATE NOT NULL,
  motif            VARCHAR2(4000 CHAR) NOT NULL,
  approuve_par     VARCHAR2(64 CHAR) NOT NULL,
  statut           VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_derog_statut CHECK (statut IN ('PROPOSEE','VALIDEE','REJETEE','EXPIREE')),
  CONSTRAINT ck_derog_dates CHECK (date_fin >= date_debut),
  CONSTRAINT ck_derog_minmax CHECK (seuil_min_derog IS NULL OR seuil_max_derog IS NULL OR seuil_min_derog <= seuil_max_derog),
  -- Controle pH-chaux (5,5 a 9,5 maximum) implemente par validation APEX / trigger
  -- car une contrainte CHECK Oracle ne peut pas contenir de sous-requete.
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

-- ---------------------------------------------------------------------
-- 6. PRELEVEMENTS ET RESULTATS (SAISIE UNIQUE)
-- ---------------------------------------------------------------------
CREATE TABLE prelevement (
  prelevement_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_prelevement PRIMARY KEY,
  industriel_id        NUMBER(10) NOT NULL CONSTRAINT fk_prelev_industriel REFERENCES industriel(industriel_id),
  numero_echantillon   VARCHAR2(60 CHAR) NOT NULL CONSTRAINT uq_prelev_echantillon UNIQUE,
  date_prelevement     TIMESTAMP(6) NOT NULL,
  point_prelevement    VARCHAR2(120 CHAR) NOT NULL,
  type_prelevement     VARCHAR2(40 CHAR) NOT NULL CONSTRAINT ck_prelev_type CHECK (type_prelevement IN ('REJET_INDUSTRIEL','EAU_USEE_DOMESTIQUE','EAU_PLUVIALE','CONTROLE_INOPINE','AUTRE')),
  laboratoire          VARCHAR2(120 CHAR) NOT NULL,
  enquete_id           NUMBER(10) CONSTRAINT fk_prelev_enquete REFERENCES enquete_controle(enquete_id),
  temperature_ambiante NUMBER(6,2),
  commentaire          CLOB,
  -- Pas de date future : validation APEX / trigger (SYSTIMESTAMP n'est pas
  -- autorise dans une contrainte CHECK Oracle).
  cree_par             VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le              TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par          VARCHAR2(64 CHAR),
  modifie_le           TIMESTAMP(6) WITH TIME ZONE,
  date_archivage       DATE
);

CREATE TABLE resultat_analyse (
  resultat_id      NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_resultat_analyse PRIMARY KEY,
  prelevement_id   NUMBER(10) NOT NULL CONSTRAINT fk_resultat_prelevement REFERENCES prelevement(prelevement_id) ON DELETE CASCADE,
  parametre_id     NUMBER(10) NOT NULL CONSTRAINT fk_resultat_parametre REFERENCES ref_parametre(parametre_id),
  valeur_num       NUMBER(14,4),
  valeur_texte     VARCHAR2(200 CHAR),
  valeur_bool      CHAR(1) CONSTRAINT ck_resultat_bool CHECK (valeur_bool IS NULL OR valeur_bool IN ('0','1')),
  limite_detection NUMBER(14,4),
  methode_analyse  VARCHAR2(100 CHAR),
  saisi_par        VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  date_saisie      TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  CONSTRAINT uq_resultat_param UNIQUE (prelevement_id, parametre_id),
  CONSTRAINT ck_resultat_valeur CHECK (valeur_num IS NOT NULL OR valeur_texte IS NOT NULL OR valeur_bool IS NOT NULL),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

-- ---------------------------------------------------------------------
-- 7. CONFORMITE REGLEMENTAIRE, RECLAMATIONS, DOCUMENTS, ALERTES, AUDIT
-- ---------------------------------------------------------------------
CREATE TABLE ref_type_dossier (
  type_dossier_id  NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_dossier PRIMARY KEY,
  code_type        VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_dossier UNIQUE,
  libelle          VARCHAR2(120 CHAR) NOT NULL,
  renouvelable     CHAR(1) DEFAULT 'N' NOT NULL CONSTRAINT ck_type_dossier_renouv CHECK (renouvelable IN ('O','N')),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE dossier_reglementaire (
  dossier_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_dossier PRIMARY KEY,
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_dossier_industriel REFERENCES industriel(industriel_id),
  type_dossier_id  NUMBER(10) NOT NULL CONSTRAINT fk_dossier_type REFERENCES ref_type_dossier(type_dossier_id),
  numero_dossier   VARCHAR2(80 CHAR) NOT NULL,
  autorite         VARCHAR2(150 CHAR),
  date_debut       DATE NOT NULL,
  date_echeance    DATE,
  statut           VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_dossier_statut CHECK (statut IN ('ACTIVE','VALIDEE','CLOTUREE','EXPIREE')),
  objet            VARCHAR2(4000 CHAR),
  CONSTRAINT uq_dossier_num UNIQUE (type_dossier_id, numero_dossier),
  CONSTRAINT ck_dossier_dates CHECK (date_echeance IS NULL OR date_echeance >= date_debut),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE ref_type_reclamation (
  type_reclamation_id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_ref_type_reclam PRIMARY KEY,
  code_type          VARCHAR2(30 CHAR) NOT NULL CONSTRAINT uq_ref_type_reclam UNIQUE,
  libelle            VARCHAR2(120 CHAR) NOT NULL,
  cree_par           VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le            TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par        VARCHAR2(64 CHAR),
  modifie_le         TIMESTAMP(6) WITH TIME ZONE,
  date_archivage     DATE
);

CREATE TABLE reclamation_incident (
  reclamation_id       NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_reclamation PRIMARY KEY,
  industriel_id        NUMBER(10) CONSTRAINT fk_reclam_industriel REFERENCES industriel(industriel_id),
  type_reclamation_id  NUMBER(10) NOT NULL CONSTRAINT fk_reclam_type REFERENCES ref_type_reclamation(type_reclamation_id),
  date_ouverture       TIMESTAMP(6) NOT NULL,
  date_cloture         TIMESTAMP(6),
  statut               VARCHAR2(30 CHAR) NOT NULL,
  gravite              VARCHAR2(30 CHAR) NOT NULL,
  objet                VARCHAR2(4000 CHAR) NOT NULL,
  delai_traitement_j   NUMBER(8,2) GENERATED ALWAYS AS
    (CASE WHEN date_cloture IS NOT NULL THEN ROUND(CAST(date_cloture AS DATE) - CAST(date_ouverture AS DATE), 2) END) VIRTUAL,
  CONSTRAINT ck_reclam_dates CHECK (date_cloture IS NULL OR date_cloture >= date_ouverture),
  cree_par             VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le              TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par          VARCHAR2(64 CHAR),
  modifie_le           TIMESTAMP(6) WITH TIME ZONE,
  date_archivage       DATE
);

CREATE TABLE document (
  document_id      NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_document PRIMARY KEY,
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_document_industriel REFERENCES industriel(industriel_id),
  contexte         VARCHAR2(30 CHAR) NOT NULL CONSTRAINT ck_document_contexte CHECK (contexte IN ('INDUSTRIEL','PRELEVEMENT','ENQUETE','RECLAMATION','DOSSIER')),
  source_id        NUMBER(10) NOT NULL,
  type_document    VARCHAR2(60 CHAR) NOT NULL,
  nom_fichier      VARCHAR2(255 CHAR) NOT NULL,
  mime_type        VARCHAR2(100 CHAR),
  contenu_blob     BLOB,
  url_sharepoint   VARCHAR2(500 CHAR),
  date_document    DATE NOT NULL,
  CONSTRAINT ck_document_stockage CHECK (contenu_blob IS NOT NULL OR url_sharepoint IS NOT NULL),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE alerte_non_conformite (
  alerte_id        NUMBER(10) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_alerte PRIMARY KEY,
  resultat_id      NUMBER(10) CONSTRAINT fk_alerte_resultat REFERENCES resultat_analyse(resultat_id) ON DELETE SET NULL,
  prelevement_id   NUMBER(10) NOT NULL CONSTRAINT fk_alerte_prelevement REFERENCES prelevement(prelevement_id),
  industriel_id    NUMBER(10) NOT NULL CONSTRAINT fk_alerte_industriel REFERENCES industriel(industriel_id),
  parametre_id     NUMBER(10) NOT NULL CONSTRAINT fk_alerte_parametre REFERENCES ref_parametre(parametre_id),
  valeur_mesuree   NUMBER(14,4) NOT NULL,
  seuil_applique   NUMBER(14,4) NOT NULL,
  depassement_pct  NUMBER(10,2) DEFAULT 0 NOT NULL CONSTRAINT ck_alerte_pct CHECK (depassement_pct >= 0),
  date_alerte      TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  gravite          VARCHAR2(20 CHAR) NOT NULL CONSTRAINT ck_alerte_gravite CHECK (gravite IN ('MOYENNE','ELEVEE','CRITIQUE')),
  statut           VARCHAR2(20 CHAR) DEFAULT 'NOUVELLE' NOT NULL CONSTRAINT ck_alerte_statut CHECK (statut IN ('NOUVELLE','NOTIFIEE','TRAITEE','CLOTUREE','LEVEE')),
  notifie_le       TIMESTAMP(6) WITH TIME ZONE,
  CONSTRAINT uq_alerte_resultat UNIQUE (resultat_id),
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

CREATE TABLE journal_maj (
  journal_id       NUMBER(18) GENERATED BY DEFAULT AS IDENTITY CONSTRAINT pk_journal PRIMARY KEY,
  date_action      TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  utilisateur      VARCHAR2(64 CHAR) NOT NULL,
  action           VARCHAR2(40 CHAR) NOT NULL,
  entite           VARCHAR2(60 CHAR) NOT NULL,
  identifiant_ligne VARCHAR2(80 CHAR),
  detail           CLOB,
  cree_par         VARCHAR2(64 CHAR) DEFAULT USER NOT NULL,
  cree_le          TIMESTAMP(6) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
  modifie_par      VARCHAR2(64 CHAR),
  modifie_le       TIMESTAMP(6) WITH TIME ZONE,
  date_archivage   DATE
);

-- ---------------------------------------------------------------------
-- 8. INDEX METIER
-- ---------------------------------------------------------------------
CREATE INDEX idx_industriel_recherche ON industriel (province_id, secteur_id, statut, date_archivage);
CREATE INDEX idx_prelev_ind_date ON prelevement (industriel_id, date_prelevement DESC);
CREATE INDEX idx_resultat_prelev ON resultat_analyse (prelevement_id);
CREATE INDEX idx_resultat_param ON resultat_analyse (parametre_id);
CREATE INDEX idx_seuil_param_date ON ref_seuil_reglementaire (parametre_id, date_effet DESC, date_fin);
CREATE INDEX idx_enquete_ind_date ON enquete_controle (industriel_id, date_visite DESC);
CREATE INDEX idx_reclam_ouverture ON reclamation_incident (date_ouverture, date_cloture);
CREATE INDEX idx_releve_date ON releve_compteur (date_releve);
CREATE INDEX idx_dossier_statut ON dossier_reglementaire (type_dossier_id, statut, date_echeance);
CREATE INDEX idx_alerte_statut ON alerte_non_conformite (statut, date_alerte DESC);

-- ---------------------------------------------------------------------
-- 9. DONNEES DE REFERENCE
-- ---------------------------------------------------------------------
INSERT ALL
  INTO ref_province (province_id, code_province, libelle) VALUES (1,'RAB','Rabat')
  INTO ref_province (province_id, code_province, libelle) VALUES (2,'SAL','Sale')
  INTO ref_province (province_id, code_province, libelle) VALUES (3,'SKT','Skhirat-Temara')
  INTO ref_province (province_id, code_province, libelle) VALUES (4,'KEN','Kenitra')
  INTO ref_province (province_id, code_province, libelle) VALUES (5,'KHE','Khemisset')
  INTO ref_province (province_id, code_province, libelle) VALUES (6,'SIK','Sidi Kacem')
  INTO ref_province (province_id, code_province, libelle) VALUES (7,'SIS','Sidi Slimane')
SELECT * FROM DUAL;

INSERT ALL
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPR', 'Direction provinciale / préfectorale de Rabat', 1, 1, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPS', 'Direction provinciale / préfectorale de Salé', 2, 2, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPST', 'Direction provinciale de Skhirat-Témara', 3, 3, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPK', 'Direction provinciale de Kénitra', 4, 4, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPKH', 'Direction provinciale de Khemisset', 5, 5, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPSK', 'Direction provinciale de Sidi Kacem', 6, 6, 0)
  INTO direction (code_dp, libelle, province_id, ordre_affichage, dernier_ordre) VALUES ('DPSS', 'Direction provinciale de Sidi Slimane', 7, 7, 0)
SELECT * FROM DUAL;


INSERT ALL
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (1,'TEXTILE','Textile')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (2,'CHIMIE','Chimie')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (3,'AGRO','Agroalimentaire')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (4,'BOULANGERIE','Boulangerie')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (5,'TANNERIE','Tannerie')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (6,'PAPIER','Papier / Carton')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (7,'METALLURGIE','Metallurgie')
  INTO ref_secteur_activite (secteur_id, code_secteur, libelle) VALUES (8,'AUTRE','Autre')
SELECT * FROM DUAL;

INSERT ALL
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (1,'mg/L','Milligramme par litre')
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (2,'-','Sans unite / pH')
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (3,'C','Degre Celsius')
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (4,'uS/cm','Microsiemens par centimetre')
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (5,'0/1','Booleen 0/1')
  INTO ref_unite (unite_id, code_unite, libelle) VALUES (6,'m3','Metre cube')
SELECT * FROM DUAL;

INSERT ALL
  INTO ref_liste (code_liste, libelle, modifiable) VALUES ('STATUT_INDUSTRIEL','Statuts industriels','N')
  INTO ref_liste (code_liste, libelle, modifiable) VALUES ('GRAVITE','Gravites','N')
  INTO ref_liste (code_liste, libelle, modifiable) VALUES ('FREQUENCE_MAJ','Frequences de mise a jour','N')
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','ACTIF','Actif',1)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','ARRET','Arret',2)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('STATUT_INDUSTRIEL','A_VERIFIER','A verifier',3)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','FAIBLE','Faible',1)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','MOYENNE','Moyenne',2)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('GRAVITE','ELEVEE','Elevee',3)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','MENSUELLE','Mensuelle',1)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','TRIMESTRIELLE','Trimestrielle',2)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','ANNUELLE','Annuelle',3)
  INTO ref_valeur_liste (code_liste, code_valeur, libelle, ordre_affichage) VALUES ('FREQUENCE_MAJ','CONTINUE','Continue',4)
SELECT * FROM DUAL;

INSERT ALL
  INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (1,'EAU_USEE_DOMESTIQUE','Eaux usees domestiques')
  INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (2,'EAU_PLUVIALE','Eaux pluviales - reseau separatif')
  INTO ref_type_branchement (type_branchement_id, code_type, libelle) VALUES (3,'EAU_INDUSTRIELLE','Eaux usees industrielles')
  INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (1,'ENQUETE','Enquete terrain')
  INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (2,'CONTROLE','Controle inopine')
  INTO ref_type_visite (type_visite_id, code_type, libelle) VALUES (3,'SUIVI','Visite de suivi')
  INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (1,'MED','Mise en demeure','N')
  INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (2,'EIE','Etude d''impact environnemental','O')
  INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (3,'AUTORISATION','Autorisation / convention de deversement','O')
  INTO ref_type_dossier (type_dossier_id, code_type, libelle, renouvelable) VALUES (4,'CERTIFICATION','Certification','O')
  INTO ref_type_reclamation (type_reclamation_id, code_type, libelle) VALUES (1,'RECLAMATION','Reclamation')
  INTO ref_type_reclamation (type_reclamation_id, code_type, libelle) VALUES (2,'INCIDENT','Incident environnemental')
  INTO app_role (role_id, code_role, libelle) VALUES (1,'ADMIN','Administrateur')
  INTO app_role (role_id, code_role, libelle) VALUES (2,'LABO','Laboratoire SRM-RSK')
  INTO app_role (role_id, code_role, libelle) VALUES (3,'ENV','Cellule Environnement')
  INTO app_role (role_id, code_role, libelle) VALUES (4,'SIG','Service SIG')
  INTO app_role (role_id, code_role, libelle) VALUES (5,'CLIENTELE','Service Clientele')
  INTO app_role (role_id, code_role, libelle) VALUES (6,'ENQUETE','Equipe Enquetes')
  INTO app_role (role_id, code_role, libelle) VALUES (7,'REPORTING','Division reporting / Direction')
SELECT * FROM DUAL;

INSERT ALL
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (1, 'PH', 'pH', (SELECT unite_id FROM ref_unite WHERE code_unite = '-'), 'Admissibilité générale', 'NUMERIQUE', 0, 14, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (2, 'TEMPERATURE', 'Température', (SELECT unite_id FROM ref_unite WHERE code_unite = 'C'), 'Admissibilité générale', 'NUMERIQUE', -10, 100, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (3, 'MES', 'Matières en suspension (MES)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (4, 'DBO5', 'Demande biochimique en oxygène (DBO5)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (5, 'DCO', 'Demande chimique en oxygène (DCO)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (6, 'AZOTE_TOTAL', 'Azote total (exprimé en N)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (7, 'AMMONIUM', 'Azote ammoniacal (ion ammonium)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (8, 'CONDUCTIVITE', 'Conductivité', (SELECT unite_id FROM ref_unite WHERE code_unite = 'uS/cm'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (9, 'HUILES_GRAISES', 'Huiles et graisses', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Admissibilité générale', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (10, 'ECOTOX', 'Écotoxicité conforme AFNOR T90.301', (SELECT unite_id FROM ref_unite WHERE code_unite = '0/1'), 'Contrôle qualitatif', 'BOOLEEN', 0, 1, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (11, 'CYCLIQUES_HALOGENES', 'Composés cycliques hydroxylés / dérivés halogénés', (SELECT unite_id FROM ref_unite WHERE code_unite = '0/1'), 'Contrôle qualitatif', 'BOOLEEN', 0, 1, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (12, 'ATTEINTE_TRAITEMENT', 'Substances nuisant au traitement biologique / ouvrages', (SELECT unite_id FROM ref_unite WHERE code_unite = '0/1'), 'Contrôle qualitatif', 'BOOLEEN', 0, 1, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (13, 'DEVERSEMENT_INTERDIT', 'Corps ou matières interdits (Art. 45)', (SELECT unite_id FROM ref_unite WHERE code_unite = '0/1'), 'Contrôle qualitatif', 'BOOLEEN', 0, 1, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (14, 'PH_CHAUX', 'pH - dérogation neutralisation à la chaux', (SELECT unite_id FROM ref_unite WHERE code_unite = '-'), 'Dérogation', 'NUMERIQUE', 0, 14, 'N', 'N')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (15, 'FE', 'Fer (Fe)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (16, 'AL', 'Aluminium (Al)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (17, 'MGOH2', 'Magnésium hydroxyde (Mg(OH)2)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (18, 'CD', 'Cadmium (Cd)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (19, 'SO4', 'Sulfate ((SO4)2-)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (20, 'CR6', 'Chrome hexavalent (Cr6+)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (21, 'CU', 'Cuivre (Cu)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (22, 'CO', 'Cobalt (Co)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (23, 'ZN', 'Zinc (Zn)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (24, 'HG', 'Mercure (Hg)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (25, 'NI', 'Nickel (Ni)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (26, 'AG', 'Argent (Ag)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (27, 'PB', 'Plomb (Pb)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (28, 'CL2', 'Chlore libre (Cl2)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (29, 'AS', 'Arsenic (As)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (30, 'S2', 'Sulfures (S2-)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (31, 'CRO3', 'Chromates (CrO3)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (32, 'F', 'Fluorure (F-)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (33, 'CN', 'Cyanure (CN-)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (34, 'NO2', 'Nitrites (NO2)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (35, 'PHENOL', 'Phénol (C6H5OH)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 14, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (36, 'SN', 'Étain (Sn)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (37, 'P', 'Phosphore (P)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (38, 'MN', 'Manganèse (Mn)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Métaux', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (39, 'CL', 'Chlorures (Cl-)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Composés chimiques', 'NUMERIQUE', 0, 1000000000, 'N', 'O')
  INTO ref_parametre (parametre_id, code_parametre, libelle, unite_id, famille, type_valeur, min_physique, max_physique, est_derive, actif) VALUES (40, 'TOTAL_METAUX', 'Total métaux lourds (Cd, Cu, Zn, Cr, Ni, Fe)', (SELECT unite_id FROM ref_unite WHERE code_unite = 'mg/L'), 'Indicateur dérivé', 'NUMERIQUE', 0, 1000000000, 'O', 'O')
SELECT * FROM DUAL;

INSERT INTO ref_seuil_reglementaire
  (seuil_id, parametre_id, seuil_min, seuil_max, date_effet, date_fin, portee,
   secteur_id, industriel_id, article, reference_texte, actif, cree_par, cree_le)
SELECT 1, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'PH'), 5.5, 8.5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 2, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'TEMPERATURE'), NULL, 30, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 3, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'MES'), NULL, 500, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 4, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'DBO5'), NULL, 500, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 5, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'DCO'), NULL, 1000, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 6, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'AZOTE_TOTAL'), NULL, 150, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 7, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'AMMONIUM'), NULL, 200, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 8, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CONDUCTIVITE'), NULL, 2700, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 9, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'HUILES_GRAISES'), NULL, 150, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 10, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'ECOTOX'), 1, 1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 11, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CYCLIQUES_HALOGENES'), NULL, 0, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 12, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'ATTEINTE_TRAITEMENT'), NULL, 0, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 42', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 13, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'DEVERSEMENT_INTERDIT'), NULL, 0, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 45', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 14, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'FE'), NULL, 0.5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 15, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'AL'), NULL, 5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 16, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'MGOH2'), NULL, 100, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 17, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CD'), NULL, 0.5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 18, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'SO4'), NULL, 400, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 19, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CR6'), NULL, 0.1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 20, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CU'), NULL, 1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 21, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CO'), NULL, 2, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 22, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'ZN'), NULL, 5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 23, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'HG'), NULL, 0.05, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 24, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'NI'), NULL, 2, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 25, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'AG'), NULL, 0.1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 26, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'PB'), NULL, 0.1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 27, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CL2'), NULL, 3, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 28, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'AS'), NULL, 1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 29, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'S2'), NULL, 1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 30, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CRO3'), NULL, 2, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 31, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'F'), NULL, 10, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 32, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CN'), NULL, 0.5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 33, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'NO2'), NULL, 10, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 34, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'PHENOL'), NULL, 5, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 35, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'SN'), NULL, 0.1, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 36, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'P'), NULL, 20, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 37, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'MN'), NULL, 2, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 38, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'CL'), NULL, 1500, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL
UNION ALL
SELECT 39, (SELECT parametre_id FROM ref_parametre WHERE code_parametre = 'TOTAL_METAUX'), NULL, 15, DATE '2020-01-01', NULL, 'TOUS', NULL, NULL, 'Art. 44', 'Cahier des charges SRM-RSK', 'O', USER, SYSTIMESTAMP FROM DUAL;


-- ---------------------------------------------------------------------
-- 10. PACKAGE PL/SQL DE CONFORMITE
-- ---------------------------------------------------------------------
CREATE OR REPLACE PACKAGE srm_pkg_conformite AS
  FUNCTION get_seuil_min(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER;
  FUNCTION get_seuil_max(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER;
  FUNCTION statut_reglementaire(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC;
  FUNCTION niveau_vigilance(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC;
  FUNCTION formater_code_industriel(p_code_dp IN VARCHAR2, p_ordre IN NUMBER) RETURN VARCHAR2 DETERMINISTIC;
  PROCEDURE reserver_ordre_direction(p_code_dp IN VARCHAR2, p_ordre OUT NUMBER);
  PROCEDURE journaliser(p_action IN VARCHAR2, p_entite IN VARCHAR2, p_id IN VARCHAR2, p_detail IN VARCHAR2);
END srm_pkg_conformite;
/
CREATE OR REPLACE PACKAGE BODY srm_pkg_conformite AS
  FUNCTION get_seuil_min(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER IS
    v_val NUMBER;
  BEGIN
    BEGIN
      SELECT d.seuil_min_derog INTO v_val
      FROM derogation_seuil d
      WHERE d.parametre_id = p_parametre_id
        AND d.industriel_id = p_industriel_id
        AND d.statut = 'VALIDEE'
        AND TRUNC(p_date) BETWEEN d.date_debut AND d.date_fin
      ORDER BY d.date_debut DESC, d.derogation_id DESC
      FETCH FIRST 1 ROW ONLY;
      RETURN v_val;
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;
    SELECT s.seuil_min INTO v_val
    FROM (
      SELECT s.seuil_min
      FROM ref_seuil_reglementaire s
      WHERE s.parametre_id = p_parametre_id
        AND s.actif = 'O'
        AND TRUNC(p_date) >= s.date_effet
        AND (s.date_fin IS NULL OR TRUNC(p_date) <= s.date_fin)
        AND (s.portee = 'TOUS'
             OR (s.portee = 'SECTEUR' AND s.secteur_id = (SELECT i.secteur_id FROM industriel i WHERE i.industriel_id = p_industriel_id))
             OR (s.portee = 'INDUSTRIEL' AND s.industriel_id = p_industriel_id))
      ORDER BY CASE s.portee WHEN 'INDUSTRIEL' THEN 1 WHEN 'SECTEUR' THEN 2 ELSE 3 END,
               s.date_effet DESC, s.seuil_id DESC
    ) WHERE ROWNUM = 1;
    RETURN v_val;
  EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
  END get_seuil_min;

  FUNCTION get_seuil_max(p_parametre_id IN NUMBER, p_industriel_id IN NUMBER, p_date IN DATE) RETURN NUMBER IS
    v_val NUMBER;
  BEGIN
    BEGIN
      SELECT d.seuil_max_derog INTO v_val
      FROM derogation_seuil d
      WHERE d.parametre_id = p_parametre_id
        AND d.industriel_id = p_industriel_id
        AND d.statut = 'VALIDEE'
        AND TRUNC(p_date) BETWEEN d.date_debut AND d.date_fin
      ORDER BY d.date_debut DESC, d.derogation_id DESC
      FETCH FIRST 1 ROW ONLY;
      RETURN v_val;
    EXCEPTION WHEN NO_DATA_FOUND THEN NULL;
    END;
    SELECT s.seuil_max INTO v_val
    FROM (
      SELECT s.seuil_max
      FROM ref_seuil_reglementaire s
      WHERE s.parametre_id = p_parametre_id
        AND s.actif = 'O'
        AND TRUNC(p_date) >= s.date_effet
        AND (s.date_fin IS NULL OR TRUNC(p_date) <= s.date_fin)
        AND (s.portee = 'TOUS'
             OR (s.portee = 'SECTEUR' AND s.secteur_id = (SELECT i.secteur_id FROM industriel i WHERE i.industriel_id = p_industriel_id))
             OR (s.portee = 'INDUSTRIEL' AND s.industriel_id = p_industriel_id))
      ORDER BY CASE s.portee WHEN 'INDUSTRIEL' THEN 1 WHEN 'SECTEUR' THEN 2 ELSE 3 END,
               s.date_effet DESC, s.seuil_id DESC
    ) WHERE ROWNUM = 1;
    RETURN v_val;
  EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL;
  END get_seuil_max;

  FUNCTION statut_reglementaire(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
  BEGIN
    IF p_valeur IS NULL THEN RETURN 'NON_ANALYSE'; END IF;
    IF (p_min IS NOT NULL AND p_valeur < p_min) OR (p_max IS NOT NULL AND p_valeur > p_max)
      THEN RETURN 'NON_CONFORME'; END IF;
    RETURN 'CONFORME';
  END statut_reglementaire;

  FUNCTION niveau_vigilance(p_valeur IN NUMBER, p_min IN NUMBER, p_max IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
    v_span NUMBER;
  BEGIN
    IF p_valeur IS NULL THEN RETURN 'NORMAL'; END IF;
    IF (p_min IS NOT NULL AND p_valeur < p_min) OR (p_max IS NOT NULL AND p_valeur > p_max)
      THEN RETURN 'NON_CONFORME'; END IF;
    IF p_min IS NOT NULL AND p_max IS NOT NULL AND p_max > p_min THEN
      v_span := p_max - p_min;
      IF p_valeur < p_min + v_span * 0.10 OR p_valeur > p_max - v_span * 0.10
        THEN RETURN 'A_SURVEILLER'; END IF;
    ELSIF p_max IS NOT NULL AND p_max > 0 AND p_valeur >= p_max * 0.80 THEN
      RETURN 'A_SURVEILLER';
    END IF;
    RETURN 'NORMAL';
  END niveau_vigilance;

  FUNCTION formater_code_industriel(p_code_dp IN VARCHAR2, p_ordre IN NUMBER) RETURN VARCHAR2 DETERMINISTIC IS
  BEGIN
    RETURN 'IN' || p_code_dp || '-' || TO_CHAR(p_ordre, 'FM0000');
  END formater_code_industriel;

  PROCEDURE reserver_ordre_direction(p_code_dp IN VARCHAR2, p_ordre OUT NUMBER) IS
    v_ordre NUMBER;
  BEGIN
    SELECT dernier_ordre + 1 INTO v_ordre
      FROM direction WHERE code_dp = p_code_dp FOR UPDATE;
    UPDATE direction
       SET dernier_ordre = v_ordre, modifie_par = USER, modifie_le = SYSTIMESTAMP
     WHERE code_dp = p_code_dp;
    p_ordre := v_ordre;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20012, 'Direction provinciale / prefectorale inconnue : ' || p_code_dp);
  END reserver_ordre_direction;

  PROCEDURE journaliser(p_action IN VARCHAR2, p_entite IN VARCHAR2, p_id IN VARCHAR2, p_detail IN VARCHAR2) IS
  BEGIN
    INSERT INTO journal_maj (utilisateur, action, entite, identifiant_ligne, detail)
    VALUES (NVL(SYS_CONTEXT('APEX$SESSION','APP_USER'), SYS_CONTEXT('USERENV','SESSION_USER')),
            p_action, p_entite, p_id, p_detail);
  END journaliser;
END srm_pkg_conformite;
/

-- ---------------------------------------------------------------------
-- 11. VUES CALCULEES - AUCUNE CONFORMITE / KPI SAISIE MANUELLEMENT
-- ---------------------------------------------------------------------
CREATE OR REPLACE VIEW v_total_metaux AS
SELECT r.prelevement_id, p.industriel_id, p.date_prelevement, SUM(r.valeur_num) valeur_totale
FROM resultat_analyse r
JOIN prelevement p ON p.prelevement_id = r.prelevement_id
JOIN ref_parametre rp ON rp.parametre_id = r.parametre_id
WHERE rp.code_parametre IN ('CD','CU','ZN','CR6','NI','FE')
GROUP BY r.prelevement_id, p.industriel_id, p.date_prelevement;

CREATE OR REPLACE VIEW v_resultat_conformite AS
WITH base AS (
  SELECT r.resultat_id, r.prelevement_id, p.industriel_id, p.date_prelevement,
         r.parametre_id, rp.code_parametre, rp.libelle, r.valeur_num, ru.code_unite,
         srm_pkg_conformite.get_seuil_min(r.parametre_id, p.industriel_id, CAST(p.date_prelevement AS DATE)) seuil_min,
         srm_pkg_conformite.get_seuil_max(r.parametre_id, p.industriel_id, CAST(p.date_prelevement AS DATE)) seuil_max
  FROM resultat_analyse r
  JOIN prelevement p ON p.prelevement_id = r.prelevement_id
  JOIN ref_parametre rp ON rp.parametre_id = r.parametre_id
  JOIN ref_unite ru ON ru.unite_id = rp.unite_id
  WHERE r.date_archivage IS NULL AND p.date_archivage IS NULL
), calculee AS (
  SELECT b.*,
         srm_pkg_conformite.statut_reglementaire(b.valeur_num, b.seuil_min, b.seuil_max) statut_resultat,
         srm_pkg_conformite.niveau_vigilance(b.valeur_num, b.seuil_min, b.seuil_max) niveau_vigilance,
         CASE
           WHEN b.seuil_max IS NOT NULL AND b.seuil_max > 0 AND b.valeur_num > b.seuil_max
             THEN ROUND((b.valeur_num / b.seuil_max - 1) * 100, 2)
           WHEN b.seuil_min IS NOT NULL AND b.seuil_min > 0 AND b.valeur_num < b.seuil_min
             THEN ROUND((b.seuil_min - b.valeur_num) / b.seuil_min * 100, 2)
           ELSE 0 END depassement_pct,
         'N' est_derive
  FROM base b
), total AS (
  SELECT t.prelevement_id, t.industriel_id, t.date_prelevement,
         rp.parametre_id, rp.libelle, t.valeur_totale, ru.code_unite
  FROM v_total_metaux t
  JOIN ref_parametre rp ON rp.code_parametre = 'TOTAL_METAUX'
  JOIN ref_unite ru ON ru.unite_id = rp.unite_id
)
SELECT * FROM calculee
UNION ALL
SELECT NULL resultat_id, t.prelevement_id, t.industriel_id, t.date_prelevement,
       t.parametre_id, 'TOTAL_METAUX' code_parametre, t.libelle, t.valeur_totale valeur_num, t.code_unite,
       NULL seuil_min, 15 seuil_max,
       srm_pkg_conformite.statut_reglementaire(t.valeur_totale, NULL, 15) statut_resultat,
       srm_pkg_conformite.niveau_vigilance(t.valeur_totale, NULL, 15) niveau_vigilance,
       CASE WHEN t.valeur_totale > 15 THEN ROUND((t.valeur_totale / 15 - 1) * 100, 2) ELSE 0 END depassement_pct,
       'O' est_derive
FROM total t;

CREATE OR REPLACE VIEW v_prelevement_conformite AS
SELECT p.prelevement_id, p.industriel_id, p.numero_echantillon, p.date_prelevement,
       p.point_prelevement, p.laboratoire, p.type_prelevement,
       COUNT(v.prelevement_id) nb_resultats,
       SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) nb_depassements,
       SUM(CASE WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 1 ELSE 0 END) nb_vigilance,
       CASE WHEN SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) > 0 THEN 'NON_CONFORME'
            WHEN COUNT(v.prelevement_id) > 0 THEN 'CONFORME'
            ELSE 'NON_ANALYSE' END statut_prelevement,
       CASE WHEN SUM(CASE WHEN v.statut_resultat = 'NON_CONFORME' THEN 1 ELSE 0 END) > 0 THEN 'NON_CONFORME'
            WHEN SUM(CASE WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 1 ELSE 0 END) > 0 THEN 'A_SURVEILLER'
            ELSE 'NORMAL' END niveau_vigilance
FROM prelevement p
LEFT JOIN v_resultat_conformite v ON v.prelevement_id = p.prelevement_id
WHERE p.date_archivage IS NULL
GROUP BY p.prelevement_id, p.industriel_id, p.numero_echantillon, p.date_prelevement,
         p.point_prelevement, p.laboratoire, p.type_prelevement;

CREATE OR REPLACE VIEW v_dernier_statut_industriel AS
SELECT * FROM (
  SELECT i.industriel_id, i.code_industriel, i.ancien_code, i.direction_id, i.code_dp,
         i.ordre_dp, d.libelle direction_libelle, i.raison_sociale, i.province_id, i.secteur_id,
         i.zone_id, i.statut statut_exploitation, i.gps_lat, i.gps_lon,
         v.prelevement_id dernier_prelevement_id, v.date_prelevement date_dernier_controle,
         v.statut_prelevement, v.niveau_vigilance, v.nb_depassements,
         CASE WHEN v.statut_prelevement = 'NON_CONFORME' THEN 'NON_CONFORME'
              WHEN v.niveau_vigilance = 'A_SURVEILLER' THEN 'A_SURVEILLER'
              WHEN v.statut_prelevement = 'CONFORME' THEN 'CONFORME'
              ELSE 'NON_CONTROLE' END statut_carte,
         ROW_NUMBER() OVER (PARTITION BY i.industriel_id ORDER BY v.date_prelevement DESC NULLS LAST, v.prelevement_id DESC NULLS LAST) rn
  FROM industriel i
  LEFT JOIN direction d ON d.code_dp = i.direction_id
  LEFT JOIN v_prelevement_conformite v ON v.industriel_id = i.industriel_id
  WHERE i.date_archivage IS NULL
) WHERE rn = 1;

CREATE OR REPLACE VIEW v_kpi_general AS
SELECT
  (SELECT COUNT(*) FROM industriel WHERE date_archivage IS NULL) industriels_recenses,
  (SELECT COUNT(*) FROM v_prelevement_conformite
    WHERE date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY')
      AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12)) prelevements_annee,
  ROUND(100 * (SELECT COUNT(*) FROM v_prelevement_conformite
                WHERE statut_prelevement = 'CONFORME'
                  AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY')
                  AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12))
        / NULLIF((SELECT COUNT(*) FROM v_prelevement_conformite
                   WHERE statut_prelevement <> 'NON_ANALYSE'
                     AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY')
                     AND date_prelevement < ADD_MONTHS(TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY'),12)),0),1) taux_conformite,
  ROUND(100 * (SELECT COUNT(DISTINCT i.industriel_id) FROM industriel i
                JOIN enquete_controle e ON e.industriel_id = i.industriel_id
                WHERE i.statut = 'Actif' AND i.date_archivage IS NULL
                  AND e.date_visite >= TRUNC(SYSDATE,'YYYY'))
        / NULLIF((SELECT COUNT(*) FROM industriel WHERE statut = 'Actif' AND date_archivage IS NULL),0),1) couverture_enquetes,
  (SELECT COUNT(*) FROM dossier_reglementaire d
    JOIN ref_type_dossier t ON t.type_dossier_id = d.type_dossier_id
    WHERE t.code_type = 'MED' AND d.statut = 'ACTIVE'
      AND (d.date_echeance IS NULL OR d.date_echeance >= SYSDATE)) med_actives,
  (SELECT NVL(SUM(r.volume_m3),0) FROM releve_compteur r
    WHERE r.date_releve >= TRUNC(SYSDATE,'YYYY')) conso_annee_m3,
  ROUND((SELECT AVG(CAST(r.date_cloture AS DATE) - CAST(r.date_ouverture AS DATE))
         FROM reclamation_incident r
         WHERE r.date_cloture IS NOT NULL AND r.date_ouverture >= TRUNC(SYSDATE,'YYYY')),1) delai_moyen_reclamation_j,
  (SELECT COUNT(*) FROM v_resultat_conformite
    WHERE statut_resultat = 'NON_CONFORME'
      AND date_prelevement >= TRUNC(CAST(SYSTIMESTAMP AS DATE),'YYYY')) nb_depassements
FROM DUAL;

CREATE OR REPLACE VIEW v_kpi_mensuel AS
SELECT TRUNC(CAST(date_prelevement AS DATE),'MM') mois,
       COUNT(*) nb_prelevements,
       SUM(nb_resultats) nb_resultats,
       SUM(nb_depassements) nb_depassements,
       SUM(CASE WHEN statut_prelevement = 'CONFORME' THEN 1 ELSE 0 END) nb_conformes,
       ROUND(100 * SUM(CASE WHEN statut_prelevement = 'CONFORME' THEN 1 ELSE 0 END)
             / NULLIF(SUM(CASE WHEN statut_prelevement <> 'NON_ANALYSE' THEN 1 ELSE 0 END),0),1) taux_conformite
FROM v_prelevement_conformite
GROUP BY TRUNC(CAST(date_prelevement AS DATE),'MM');

CREATE MATERIALIZED VIEW mv_kpi_mensuel
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS SELECT * FROM v_kpi_mensuel;

CREATE OR REPLACE VIEW v_alertes_actives AS
SELECT a.alerte_id, a.date_alerte, i.code_industriel, i.raison_sociale,
       rp.code_parametre, a.valeur_mesuree, a.seuil_applique, a.depassement_pct,
       a.gravite, a.statut, rp.article
FROM alerte_non_conformite a
JOIN industriel i ON i.industriel_id = a.industriel_id
JOIN ref_parametre rp ON rp.parametre_id = a.parametre_id
WHERE a.statut NOT IN ('CLOTUREE','LEVEE') AND a.date_archivage IS NULL;

-- ---------------------------------------------------------------------
-- 12. TRIGGERS : ALERTES, AUDIT, APPEND-ONLY
-- ---------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_resultat_alerte
AFTER INSERT OR UPDATE OF valeur_num, valeur_bool ON resultat_analyse
FOR EACH ROW
DECLARE
  v_industriel_id  NUMBER;
  v_date           DATE;
  v_min            NUMBER;
  v_max            NUMBER;
  v_valeur         NUMBER;
  v_statut         VARCHAR2(20);
  v_seuil          NUMBER;
  v_pct            NUMBER;
  v_gravite        VARCHAR2(20);
BEGIN
  SELECT p.industriel_id, CAST(p.date_prelevement AS DATE)
    INTO v_industriel_id, v_date
    FROM prelevement p WHERE p.prelevement_id = :NEW.prelevement_id;

  v_valeur := COALESCE(:NEW.valeur_num, TO_NUMBER(:NEW.valeur_bool));
  -- Les controles booleens utilisent 0/1 ; l'absence de valeur numerique est ignoree ici.
  IF v_valeur IS NULL THEN RETURN; END IF;

  v_min := srm_pkg_conformite.get_seuil_min(:NEW.parametre_id, v_industriel_id, v_date);
  v_max := srm_pkg_conformite.get_seuil_max(:NEW.parametre_id, v_industriel_id, v_date);
  v_statut := srm_pkg_conformite.statut_reglementaire(v_valeur, v_min, v_max);

  IF v_statut = 'NON_CONFORME' THEN
    v_seuil := CASE WHEN v_max IS NOT NULL AND v_valeur > v_max THEN v_max ELSE v_min END;
    v_pct := CASE
      WHEN v_max IS NOT NULL AND v_max > 0 AND v_valeur > v_max THEN ROUND((v_valeur / v_max - 1) * 100, 2)
      WHEN v_min IS NOT NULL AND v_min > 0 AND v_valeur < v_min THEN ROUND((v_min - v_valeur) / v_min * 100, 2)
      ELSE 0 END;
    v_gravite := CASE WHEN v_pct >= 100 THEN 'CRITIQUE' WHEN v_pct >= 50 THEN 'ELEVEE' ELSE 'MOYENNE' END;

    MERGE INTO alerte_non_conformite a
    USING (SELECT :NEW.resultat_id resultat_id, :NEW.prelevement_id prelevement_id,
                  v_industriel_id industriel_id, :NEW.parametre_id parametre_id,
                  v_valeur valeur_mesuree, NVL(v_seuil,0) seuil_applique,
                  v_pct depassement_pct, v_gravite gravite FROM DUAL) s
    ON (a.resultat_id = s.resultat_id)
    WHEN MATCHED THEN UPDATE SET a.valeur_mesuree = s.valeur_mesuree,
                                a.seuil_applique = s.seuil_applique,
                                a.depassement_pct = s.depassement_pct,
                                a.gravite = s.gravite,
                                a.statut = 'NOUVELLE',
                                a.modifie_par = USER,
                                a.modifie_le = SYSTIMESTAMP
    WHEN NOT MATCHED THEN INSERT
      (resultat_id, prelevement_id, industriel_id, parametre_id, valeur_mesuree,
       seuil_applique, depassement_pct, gravite, statut)
      VALUES
      (s.resultat_id, s.prelevement_id, s.industriel_id, s.parametre_id, s.valeur_mesuree,
       s.seuil_applique, s.depassement_pct, s.gravite, 'NOUVELLE');
  ELSE
    UPDATE alerte_non_conformite
       SET statut = 'LEVEE', modifie_par = USER, modifie_le = SYSTIMESTAMP
     WHERE resultat_id = :NEW.resultat_id AND statut NOT IN ('CLOTUREE','LEVEE');
  END IF;

  UPDATE prelevement SET modifie_par = USER, modifie_le = SYSTIMESTAMP
   WHERE prelevement_id = :NEW.prelevement_id;
END;
/

CREATE OR REPLACE TRIGGER trg_journal_append_only
BEFORE UPDATE OR DELETE ON journal_maj
FOR EACH ROW
BEGIN
  RAISE_APPLICATION_ERROR(-20001, 'JOURNAL_MAJ est append-only : modification interdite.');
END;
/

CREATE OR REPLACE TRIGGER trg_industriel_code
BEFORE INSERT ON industriel
FOR EACH ROW
DECLARE
  v_ordre NUMBER;
BEGIN
  IF :NEW.direction_id IS NULL THEN
    BEGIN
      SELECT d.code_dp INTO :NEW.direction_id
        FROM direction d
       WHERE d.province_id = :NEW.province_id AND ROWNUM = 1;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      RAISE_APPLICATION_ERROR(-20011, 'Aucune direction provinciale / prefectorale pour cette province');
    END;
  END IF;
  srm_pkg_conformite.reserver_ordre_direction(:NEW.direction_id, v_ordre);
  :NEW.ordre_dp := v_ordre;
  :NEW.code_dp := :NEW.direction_id;
  :NEW.code_industriel := srm_pkg_conformite.formater_code_industriel(:NEW.direction_id, v_ordre);
END;
/

CREATE OR REPLACE TRIGGER trg_industriel_audit
AFTER INSERT OR UPDATE OR DELETE ON industriel
FOR EACH ROW
BEGIN
  IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','INDUSTRIEL',TO_CHAR(:NEW.industriel_id),'Fiche industriel creee : '||:NEW.code_industriel);
  ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','INDUSTRIEL',TO_CHAR(:NEW.industriel_id),'Fiche modifiee');
  ELSE srm_pkg_conformite.journaliser('DELETE','INDUSTRIEL',TO_CHAR(:OLD.industriel_id),'Fiche supprimee');
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_prelevement_audit
AFTER INSERT OR UPDATE OR DELETE ON prelevement
FOR EACH ROW
BEGIN
  IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','PRELEVEMENT',TO_CHAR(:NEW.prelevement_id),'Echantillon '||:NEW.numero_echantillon);
  ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','PRELEVEMENT',TO_CHAR(:NEW.prelevement_id),'Prelevement modifie');
  ELSE srm_pkg_conformite.journaliser('DELETE','PRELEVEMENT',TO_CHAR(:OLD.prelevement_id),'Prelevement supprime');
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_resultat_audit
AFTER INSERT OR UPDATE OR DELETE ON resultat_analyse
FOR EACH ROW
BEGIN
  IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','RESULTAT_ANALYSE',TO_CHAR(:NEW.resultat_id),'Resultat brut saisi');
  ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','RESULTAT_ANALYSE',TO_CHAR(:NEW.resultat_id),'Resultat corrige');
  ELSE srm_pkg_conformite.journaliser('DELETE','RESULTAT_ANALYSE',TO_CHAR(:OLD.resultat_id),'Resultat supprime');
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_seuil_audit
AFTER INSERT OR UPDATE OR DELETE ON ref_seuil_reglementaire
FOR EACH ROW
BEGIN
  IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:NEW.seuil_id),'Nouvelle version de seuil');
  ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:NEW.seuil_id),'Seuil modifie - verification de version obligatoire');
  ELSE srm_pkg_conformite.journaliser('DELETE','REF_SEUIL_REGLEMENTAIRE',TO_CHAR(:OLD.seuil_id),'Seuil supprime');
  END IF;
END;
/

CREATE OR REPLACE TRIGGER trg_derogation_audit
AFTER INSERT OR UPDATE OR DELETE ON derogation_seuil
FOR EACH ROW
BEGIN
  IF INSERTING THEN srm_pkg_conformite.journaliser('INSERT','DEROGATION_SEUIL',TO_CHAR(:NEW.derogation_id),'Derogation proposee');
  ELSIF UPDATING THEN srm_pkg_conformite.journaliser('UPDATE','DEROGATION_SEUIL',TO_CHAR(:NEW.derogation_id),'Statut derogation : '||:NEW.statut);
  ELSE srm_pkg_conformite.journaliser('DELETE','DEROGATION_SEUIL',TO_CHAR(:OLD.derogation_id),'Derogation supprimee');
  END IF;
END;
/

-- ---------------------------------------------------------------------
-- 13. AUTOMATION : rafraichissement quotidien de la MV KPI
-- ---------------------------------------------------------------------
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'SRM_REFRESH_MV_KPI',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN DBMS_MVIEW.REFRESH(''MV_KPI_MENSUEL'', ''C''); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=0',
    enabled         => TRUE,
    comments        => 'Rafraichissement quotidien des KPI mensuels SRM-RSK');
EXCEPTION
  WHEN OTHERS THEN
    IF SQLCODE != -27477 THEN RAISE; END IF; -- job deja existant
END;
/

COMMENT ON TABLE direction IS 'Directions provinciales / prefectorales et compteur de numerotation IN + CODE_DP + NNNN.';
COMMENT ON COLUMN industriel.code_industriel IS 'Genere par TRG_INDUSTRIEL_CODE : IN + CODE_DP + ordre. Ancien code conserve dans ANCIEN_CODE.';
COMMENT ON TABLE resultat_analyse IS 'Saisie unique des valeurs brutes. Aucun champ de conformite ou de KPI.';
COMMENT ON TABLE ref_seuil_reglementaire IS 'Valeurs limites versionnees du cahier des charges (Art. 42 et 44).';
COMMENT ON TABLE alerte_non_conformite IS 'Alertes generees par trigger, jamais saisies manuellement.';
COMMENT ON TABLE journal_maj IS 'Journal MAJ automatique, append-only (onglet 11 du canevas).';

PROMPT SRM-RSK schema created. Next: APEX application, authorization schemes and Automations email.