-- HALCOMI IV Zone 2 : schéma MySQL 9.1 (InnoDB, utf8mb4). Une instruction par ";" en fin de ligne.
CREATE TABLE antennes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  nom VARCHAR(120) NOT NULL,
  type VARCHAR(20) NOT NULL DEFAULT 'EQUIPE',
  ordre SMALLINT NOT NULL DEFAULT 0,
  actif TINYINT NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE zones_intervention (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  antenne_id INT UNSIGNED NOT NULL,
  nom VARCHAR(120) NOT NULL,
  FOREIGN KEY (antenne_id) REFERENCES antennes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE points_controle (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom VARCHAR(120) NOT NULL,
  antenne_id INT UNSIGNED NOT NULL,
  actif TINYINT NOT NULL DEFAULT 1,
  FOREIGN KEY (antenne_id) REFERENCES antennes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE organes_responsables (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(30) NOT NULL UNIQUE,
  libelle VARCHAR(120) NOT NULL,
  supervise_halcomi TINYINT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE personnels (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nom_complet VARCHAR(150) NOT NULL,
  grade VARCHAR(60) NULL,
  fonction VARCHAR(60) NULL,
  antenne_id INT UNSIGNED NULL,
  role_equipe VARCHAR(20) NOT NULL DEFAULT 'AGENT',
  actif TINYINT NOT NULL DEFAULT 1,
  FOREIGN KEY (antenne_id) REFERENCES antennes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE utilisateurs (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NULL,
  login VARCHAR(60) NOT NULL UNIQUE,
  mot_de_passe_hash VARCHAR(255) NOT NULL,
  role VARCHAR(20) NOT NULL,
  doit_changer_mdp TINYINT NOT NULL DEFAULT 1,
  actif TINYINT NOT NULL DEFAULT 1,
  derniere_connexion DATETIME NULL,
  FOREIGN KEY (personnel_id) REFERENCES personnels(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE dossiers_importation (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  num_dau VARCHAR(100) NOT NULL UNIQUE,
  nb_conteneurs SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  statut VARCHAR(10) NOT NULL DEFAULT 'RETENU',
  depose_le DATETIME NULL,
  depose_par INT UNSIGNED NULL,
  restitue_le DATETIME NULL,
  restitue_par INT UNSIGNED NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE prises_en_charge (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(30) NOT NULL UNIQUE,
  client_uid VARCHAR(40) NULL UNIQUE,
  journee DATE NOT NULL,
  heure TIME NULL,
  antenne_pec_id INT UNSIGNED NOT NULL,
  point_controle_id INT UNSIGNED NULL,
  dossier_id INT UNSIGNED NULL,
  num_conteneur VARCHAR(30) NOT NULL,
  taille_pieds SMALLINT NULL,
  proprietaire VARCHAR(255) NOT NULL,
  designation TEXT NULL,
  lieu_destination VARCHAR(255) NULL,
  zone_id INT UNSIGNED NULL,
  tel_chauffeur VARCHAR(60) NULL,
  tel_proprietaire VARCHAR(60) NULL,
  responsable_origine VARCHAR(100) NULL,
  organe_responsable_id INT UNSIGNED NULL,
  hors_perimetre TINYINT NOT NULL DEFAULT 0,
  antenne_ecor_id INT UNSIGNED NULL,
  cotee_par INT UNSIGNED NULL,
  cotee_le DATETIME NULL,
  statut VARCHAR(30) NOT NULL DEFAULT 'PRISE_EN_CHARGE',
  agents_ecor_legacy VARCHAR(255) NULL,
  motif_annulation VARCHAR(255) NULL,
  saisi_par INT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (antenne_pec_id) REFERENCES antennes(id),
  FOREIGN KEY (point_controle_id) REFERENCES points_controle(id),
  FOREIGN KEY (dossier_id) REFERENCES dossiers_importation(id),
  FOREIGN KEY (zone_id) REFERENCES zones_intervention(id),
  FOREIGN KEY (organe_responsable_id) REFERENCES organes_responsables(id),
  FOREIGN KEY (antenne_ecor_id) REFERENCES antennes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_pec_statut ON prises_en_charge (statut);
CREATE INDEX idx_pec_ecor ON prises_en_charge (antenne_ecor_id, statut);
CREATE INDEX idx_pec_journee ON prises_en_charge (journee);
CREATE INDEX idx_pec_conteneur ON prises_en_charge (num_conteneur);
CREATE INDEX idx_pec_dossier ON prises_en_charge (dossier_id);

CREATE TABLE ecor_agents (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prise_en_charge_id INT UNSIGNED NOT NULL,
  personnel_id INT UNSIGNED NULL,
  nom_libre VARCHAR(150) NULL,
  organisme VARCHAR(40) NOT NULL DEFAULT 'HALCOMI',
  designe_par INT UNSIGNED NOT NULL,
  designe_le DATETIME NOT NULL,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id),
  FOREIGN KEY (personnel_id) REFERENCES personnels(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_ea_pec ON ecor_agents (prise_en_charge_id);

CREATE TABLE fiches_ecor (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  reference VARCHAR(30) NOT NULL UNIQUE,
  prise_en_charge_id INT UNSIGNED NOT NULL UNIQUE,
  date_operation DATE NULL,
  lieu_operation VARCHAR(255) NULL,
  operateur_nom VARCHAR(150) NULL,
  cni_numero VARCHAR(60) NULL,
  cni_delivree_le VARCHAR(30) NULL,
  cni_delivree_a VARCHAR(100) NULL,
  cni_prorogee_au VARCHAR(30) NULL,
  immatriculation VARCHAR(40) NULL,
  chassis VARCHAR(60) NULL,
  provenance VARCHAR(120) NULL,
  destination VARCHAR(120) NULL,
  depot VARCHAR(160) NULL,
  observations TEXT NULL,
  statut VARCHAR(12) NOT NULL DEFAULT 'BROUILLON',
  deposee_le DATETIME NULL,
  deposee_par INT UNSIGNED NULL,
  recue_le DATETIME NULL,
  recue_par INT UNSIGNED NULL,
  finalisee_le DATETIME NULL,
  finalisee_par INT UNSIGNED NULL,
  resultat VARCHAR(12) NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fiche_ecor_lignes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  fiche_id INT UNSIGNED NOT NULL,
  numero SMALLINT NOT NULL,
  designation VARCHAR(255) NOT NULL,
  etat VARCHAR(50) NULL,
  qte_declaree DECIMAL(12,2) NULL,
  qte_declaree_dossier DECIMAL(12,2) NULL,
  qte_reconnue DECIMAL(12,2) NOT NULL DEFAULT 0,
  ecart DECIMAL(12,2) NOT NULL DEFAULT 0,
  observation VARCHAR(255) NULL,
  CONSTRAINT chk_numero CHECK (numero BETWEEN 1 AND 30),
  FOREIGN KEY (fiche_id) REFERENCES fiches_ecor(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX uq_fiche_numero ON fiche_ecor_lignes (fiche_id, numero);

CREATE TABLE pv_constat (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  numero VARCHAR(30) NOT NULL UNIQUE,
  prise_en_charge_id INT UNSIGNED NOT NULL UNIQUE,
  fiche_id INT UNSIGNED NOT NULL,
  organe_responsable_id INT UNSIGNED NULL,
  redacteur_id INT UNSIGNED NOT NULL,
  date_pv DATE NOT NULL,
  expose_faits TEXT NULL,
  statut VARCHAR(10) NOT NULL DEFAULT 'BROUILLON',
  created_at DATETIME NOT NULL,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id),
  FOREIGN KEY (fiche_id) REFERENCES fiches_ecor(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE pv_constat_lignes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  pv_id INT UNSIGNED NOT NULL,
  numero SMALLINT NOT NULL,
  designation VARCHAR(255) NOT NULL,
  nature_ecart VARCHAR(40) NOT NULL,
  qte_dossier DECIMAL(12,2) NULL,
  qte_reconnue DECIMAL(12,2) NULL,
  ecart DECIMAL(12,2) NULL,
  position_tarifaire VARCHAR(40) NULL,
  origine VARCHAR(60) NULL,
  valeur_imposable DECIMAL(15,2) NULL,
  droits DECIMAL(15,2) NULL,
  droits_compromis DECIMAL(15,2) NULL,
  FOREIGN KEY (pv_id) REFERENCES pv_constat(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE convocations (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prise_en_charge_id INT UNSIGNED NOT NULL UNIQUE,
  convoque_le DATETIME NOT NULL,
  convoque_par INT UNSIGNED NOT NULL,
  venu_le DATETIME NULL,
  dossier_remis_le DATETIME NULL,
  remis_par INT UNSIGNED NULL,
  observation VARCHAR(255) NULL,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE cahiers_service (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  personnel_id INT UNSIGNED NOT NULL,
  antenne_id INT UNSIGNED NULL,
  grade VARCHAR(60) NOT NULL,
  semaine_du DATE NOT NULL,
  semaine_au DATE NOT NULL,
  numero_feuillet SMALLINT NOT NULL DEFAULT 1,
  statut VARCHAR(12) NOT NULL DEFAULT 'BROUILLON',
  saisi_par INT UNSIGNED NOT NULL,
  visa_antenne_par INT UNSIGNED NULL,
  visa_antenne_le DATETIME NULL,
  transmis_le DATETIME NULL,
  recu_par INT UNSIGNED NULL,
  recu_le DATETIME NULL,
  visa_operations_par INT UNSIGNED NULL,
  visa_operations_le DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY (personnel_id) REFERENCES personnels(id),
  FOREIGN KEY (antenne_id) REFERENCES antennes(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX uq_agent_semaine_feuillet ON cahiers_service (personnel_id, semaine_du, numero_feuillet);
CREATE INDEX idx_cs_antenne_semaine ON cahiers_service (antenne_id, semaine_du);

CREATE TABLE cahier_service_lignes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  cahier_id INT UNSIGNED NOT NULL,
  numero SMALLINT NOT NULL,
  jour DATE NOT NULL,
  heure_debut TIME NULL,
  heure_cessation TIME NULL,
  absence VARCHAR(10) NULL,
  absence_precision VARCHAR(100) NULL,
  nature_services TEXT NULL,
  resultats_suites TEXT NULL,
  visa_remarques TEXT NULL,
  prise_en_charge_id INT UNSIGNED NULL,
  CONSTRAINT chk_ligne_numero CHECK (numero BETWEEN 1 AND 14),
  FOREIGN KEY (cahier_id) REFERENCES cahiers_service(id) ON DELETE CASCADE,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE UNIQUE INDEX uq_cahier_numero ON cahier_service_lignes (cahier_id, numero);
CREATE INDEX idx_csl_jour ON cahier_service_lignes (jour);

CREATE TABLE pieces_jointes (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  prise_en_charge_id INT UNSIGNED NOT NULL,
  fiche_id INT UNSIGNED NULL,
  type VARCHAR(20) NOT NULL,
  fichier VARCHAR(120) NOT NULL,
  nom_origine VARCHAR(160) NULL,
  mime VARCHAR(60) NOT NULL,
  taille INT UNSIGNED NOT NULL,
  pris_par INT UNSIGNED NULL,
  pris_le DATETIME NOT NULL,
  FOREIGN KEY (prise_en_charge_id) REFERENCES prises_en_charge(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_pj_pec ON pieces_jointes (prise_en_charge_id);

CREATE TABLE historique_statuts (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  objet VARCHAR(20) NOT NULL,
  objet_id INT UNSIGNED NOT NULL,
  ancien_statut VARCHAR(30) NULL,
  nouveau_statut VARCHAR(30) NOT NULL,
  utilisateur_id INT UNSIGNED NULL,
  note VARCHAR(255) NULL,
  date_heure DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_hs_objet ON historique_statuts (objet, objet_id);

CREATE TABLE journal_audit (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  utilisateur_id INT UNSIGNED NULL,
  action VARCHAR(60) NOT NULL,
  objet VARCHAR(30) NULL,
  objet_id INT UNSIGNED NULL,
  detail TEXT NULL,
  ip VARCHAR(45) NULL,
  date_heure DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE INDEX idx_ja_date ON journal_audit (date_heure);

CREATE TABLE compteurs (
  type VARCHAR(10) NOT NULL,
  annee SMALLINT NOT NULL,
  valeur INT UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (type, annee)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;