-- =====================================================================
--  ALTALYS PROTECTION — Base de données d'exploitation
--  Cible : MySQL / MariaDB (o2switch, cPanel > phpMyAdmin)
--  Import : phpMyAdmin > sélectionner la base > onglet "Importer" > ce fichier
--  Encodage : utf8mb4 (accents, darija, emoji). Moteur : InnoDB (clés étrangères).
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- 1. UTILISATEURS (accès à la plateforme + rôles)
--    role : admin (direction) / exploitation / chef_poste / agent
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `utilisateurs` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `login`         VARCHAR(80)  NOT NULL,
  `mot_de_passe`  VARCHAR(255) NOT NULL,        -- hash (password_hash PHP), jamais en clair
  `role`          ENUM('admin','exploitation','chef_poste','agent') NOT NULL DEFAULT 'chef_poste',
  `agent_id`      BIGINT UNSIGNED NULL,         -- si l'utilisateur est un agent terrain
  `nom_affichage` VARCHAR(120) NULL,
  `actif`         TINYINT(1) NOT NULL DEFAULT 1,
  `derniere_cnx`  DATETIME NULL,
  `cree_le`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_login` (`login`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 2. AGENTS (effectif complet)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `agents` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `matricule`      VARCHAR(30)  NULL,
  `civilite`       ENUM('M','Mme') NULL,
  `nom`            VARCHAR(120) NOT NULL,
  `nom_usage`      VARCHAR(120) NULL,          -- nom d'époux / d'usage (ex. BELAID, TABOUT)
  `prenom`         VARCHAR(120) NOT NULL,
  `sexe`           ENUM('M','F') NULL,         -- mention obligatoire registre (D1221-23)
  `date_naissance` DATE NULL,
  `lieu_naissance` VARCHAR(160) NULL,
  `nationalite`    VARCHAR(80)  NULL,
  `adresse`        VARCHAR(200) NULL,
  `code_postal`    VARCHAR(12)  NULL,
  `ville`          VARCHAR(120) NULL,
  `telephone`      VARCHAR(30)  NULL,
  `email`          VARCHAR(160) NULL,
  `photo`          VARCHAR(255) NULL,           -- chemin fichier /uploads/agents/xxx.jpg
  `fonction`       ENUM('aps','ssiap1','ssiap2','ssiap3','cyno','ctrl','admin') NOT NULL DEFAULT 'aps',
  -- Volet RH / paie
  `type_contrat`   ENUM('CDI','CDD','interim','stage') NULL,
  `temps_travail`  ENUM('temps_plein','temps_partiel') NULL,
  `coefficient`    VARCHAR(20)  NULL,           -- ex. 140 (CCN IDCC 1351)
  `taux_horaire`   DECIMAL(8,4) NULL,           -- € brut / heure
  `salaire_base`   DECIMAL(10,2) NULL,          -- € brut mensuel
  `date_entree`    DATE NULL,
  `date_sortie`    DATE NULL,
  `num_secu`       VARCHAR(25)  NULL,
  `iban`           VARCHAR(40)  NULL,
  `statut`         ENUM('actif','inactif','sorti') NOT NULL DEFAULT 'actif',
  `notes`          TEXT NULL,
  `cree_le`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `maj_le`         DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_matricule` (`matricule`),
  KEY `idx_nom` (`nom`,`prenom`),
  KEY `idx_statut` (`statut`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 3. DOCUMENTS & QUALIFICATIONS d'un agent (avec échéances -> veille)
--    type : carte_pro, titre_sejour, cni, ssiap, sst, medical, cyno_hab,
--           contrat, dpae, rib, casier, autre
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `agent_documents` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agent_id`         BIGINT UNSIGNED NOT NULL,
  `type`             VARCHAR(30)  NOT NULL,
  `numero`           VARCHAR(120) NULL,          -- n° carte pro / AUT / réf.
  `date_delivrance`  DATE NULL,
  `date_expiration`  DATE NULL,
  `fichier`          VARCHAR(255) NULL,          -- chemin pièce scannée
  `remarque`         VARCHAR(255) NULL,
  `cree_le`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_agent` (`agent_id`),
  KEY `idx_exp` (`date_expiration`),
  CONSTRAINT `fk_doc_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 4. CONTRATS DE TRAVAIL
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `contrats` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agent_id`      BIGINT UNSIGNED NOT NULL,
  `type`          ENUM('CDI','CDD','interim','stage') NOT NULL DEFAULT 'CDI',
  `intitule_poste` VARCHAR(160) NULL,
  `date_debut`    DATE NULL,
  `date_fin`      DATE NULL,                    -- NULL si CDI
  `coefficient`   VARCHAR(20)  NULL,
  `taux_horaire`  DECIMAL(8,4) NULL,
  `heures_mois`   DECIMAL(7,2) NULL,
  `fichier`       VARCHAR(255) NULL,            -- PDF du contrat signé
  `statut`        ENUM('en_cours','termine','rompu','brouillon') NOT NULL DEFAULT 'en_cours',
  `cree_le`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_agent` (`agent_id`),
  CONSTRAINT `fk_contrat_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 5. AVENANTS (modification de contrat)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `avenants` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `contrat_id`    BIGINT UNSIGNED NOT NULL,
  `objet`         VARCHAR(200) NOT NULL,        -- ex. "Modification du taux horaire"
  `date_effet`    DATE NULL,
  `ancien_taux`   DECIMAL(8,4) NULL,
  `nouveau_taux`  DECIMAL(8,4) NULL,
  `ancien_coef`   VARCHAR(20) NULL,
  `nouveau_coef`  VARCHAR(20) NULL,
  `contenu`       MEDIUMTEXT NULL,              -- corps généré de l'avenant
  `fichier`       VARCHAR(255) NULL,            -- PDF généré
  `statut`        ENUM('brouillon','a_signer','signe') NOT NULL DEFAULT 'brouillon',
  `cree_le`       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_contrat` (`contrat_id`),
  CONSTRAINT `fk_avenant_contrat` FOREIGN KEY (`contrat_id`) REFERENCES `contrats`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 6. SITES / POSTES
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sites` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `nom`        VARCHAR(160) NOT NULL,
  `client`     VARCHAR(160) NULL,
  `adresse`    VARCHAR(200) NULL,
  `code_postal` VARCHAR(12) NULL,
  `ville`      VARCHAR(120) NULL,
  `latitude`   DECIMAL(10,7) NULL,
  `longitude`  DECIMAL(10,7) NULL,
  `couleur`    VARCHAR(9) NULL DEFAULT '#1B4250',
  `consignes`  TEXT NULL,
  `actif`      TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 7. POINTS DE CONTRÔLE (rondes) — NFC / QR / GPS
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `points_controle` (
  `id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id`   BIGINT UNSIGNED NOT NULL,
  `libelle`   VARCHAR(160) NOT NULL,            -- ex. "Entrée parking niveau -1"
  `methode`   ENUM('nfc','qr','gps') NOT NULL DEFAULT 'nfc',
  `tag_uid`   VARCHAR(64) NULL,                 -- identifiant unique du tag NFC
  `qr_code`   VARCHAR(64) NULL,                 -- valeur encodée dans le QR
  `latitude`  DECIMAL(10,7) NULL,
  `longitude` DECIMAL(10,7) NULL,
  `ordre`     INT NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_tag` (`tag_uid`),
  CONSTRAINT `fk_pc_site` FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 8. RONDES + pointages de ronde (traçabilité horodatée/géolocalisée)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `rondes` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id`     BIGINT UNSIGNED NOT NULL,
  `agent_id`    BIGINT UNSIGNED NULL,
  `debut`       DATETIME NULL,
  `fin`         DATETIME NULL,
  `statut`      ENUM('en_cours','terminee','incomplete') NOT NULL DEFAULT 'en_cours',
  PRIMARY KEY (`id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_agent` (`agent_id`),
  CONSTRAINT `fk_ronde_site` FOREIGN KEY (`site_id`) REFERENCES `sites`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ronde_pointages` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ronde_id`   BIGINT UNSIGNED NOT NULL,
  `point_id`   BIGINT UNSIGNED NULL,
  `horodatage` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `methode`    ENUM('nfc','qr','gps','manuel') NOT NULL DEFAULT 'nfc',
  `latitude`   DECIMAL(10,7) NULL,
  `longitude`  DECIMAL(10,7) NULL,
  `photo`      VARCHAR(255) NULL,
  `remarque`   VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_ronde` (`ronde_id`),
  CONSTRAINT `fk_rp_ronde` FOREIGN KEY (`ronde_id`) REFERENCES `rondes`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 9. PLANNING (vacations)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `vacations` (
  `id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agent_id`  BIGINT UNSIGNED NOT NULL,
  `site_id`   BIGINT UNSIGNED NOT NULL,
  `date`      DATE NOT NULL,
  `debut`     TIME NOT NULL,
  `fin`       TIME NOT NULL,
  `pause_min` INT NOT NULL DEFAULT 0,
  `statut`    ENUM('prevu','confirme','realise','absent') NOT NULL DEFAULT 'prevu',
  PRIMARY KEY (`id`),
  KEY `idx_agent_date` (`agent_id`,`date`),
  KEY `idx_site_date` (`site_id`,`date`),
  CONSTRAINT `fk_vac_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vac_site`  FOREIGN KEY (`site_id`)  REFERENCES `sites`(`id`)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 10. POINTAGE / PRISE DE SERVICE (géolocalisé)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `pointages` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agent_id`       BIGINT UNSIGNED NOT NULL,
  `site_id`        BIGINT UNSIGNED NULL,
  `prise_service`  DATETIME NULL,
  `fin_service`    DATETIME NULL,
  `lat_debut`      DECIMAL(10,7) NULL,
  `lng_debut`      DECIMAL(10,7) NULL,
  `lat_fin`        DECIMAL(10,7) NULL,
  `lng_fin`        DECIMAL(10,7) NULL,
  `methode`        ENUM('nfc','qr','gps','manuel') NOT NULL DEFAULT 'gps',
  PRIMARY KEY (`id`),
  KEY `idx_agent` (`agent_id`),
  CONSTRAINT `fk_pt_agent` FOREIGN KEY (`agent_id`) REFERENCES `agents`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 11. MAIN COURANTE ÉLECTRONIQUE
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `main_courante` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `site_id`       BIGINT UNSIGNED NULL,
  `agent_id`      BIGINT UNSIGNED NULL,
  `horodatage`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `type_evenement` VARCHAR(60) NOT NULL,        -- prise_service, ronde, incident, visiteur, livraison...
  `gravite`       ENUM('info','normal','important','critique') NOT NULL DEFAULT 'normal',
  `description`   TEXT NULL,
  `latitude`      DECIMAL(10,7) NULL,
  `longitude`     DECIMAL(10,7) NULL,
  `photo`         VARCHAR(255) NULL,
  PRIMARY KEY (`id`),
  KEY `idx_site` (`site_id`),
  KEY `idx_horo` (`horodatage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- 12. GED (documents libres, glisser-déposer) + 13. modèles + 14. paramètres
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `documents` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `agent_id`        BIGINT UNSIGNED NULL,
  `categorie`       VARCHAR(40) NULL,
  `nom_fichier`     VARCHAR(200) NOT NULL,
  `chemin`          VARCHAR(255) NOT NULL,
  `date_expiration` DATE NULL,
  `date_ajout`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_agent` (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `modeles_courriers` (
  `id`        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `categorie` VARCHAR(40) NULL,
  `titre`     VARCHAR(160) NOT NULL,
  `objet`     VARCHAR(200) NULL,
  `corps`     MEDIUMTEXT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `parametres` (
  `cle`    VARCHAR(60) NOT NULL,
  `valeur` TEXT NULL,
  PRIMARY KEY (`cle`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------
-- Données initiales (société). Le 1er compte administrateur est créé
-- séparément par api/setup.php (mot de passe défini par vous, jamais en clair ici).
-- ---------------------------------------------------------------------
INSERT INTO `parametres` (`cle`,`valeur`) VALUES
 ('societe_nom','ALTALYS PROTECTION'),
 ('societe_aut','AUT-091-2121-02-03-20210394936'),
 ('societe_siret','751 408 600 R.C.S. Évry'),
 ('societe_adresse','87 Route de Grigny, 91130 Ris-Orangis'),
 ('societe_ccn','IDCC 1351 — Prévention et Sécurité')
ON DUPLICATE KEY UPDATE `valeur`=VALUES(`valeur`);

-- Le compte administrateur est créé par le script d'installation PHP
-- (api/setup.php), qui génère un hash sécurisé via password_hash().
-- Ne PAS insérer de mot de passe en clair ici.

SET FOREIGN_KEY_CHECKS = 1;
-- =====================  FIN DU SCHÉMA  ================================