--
-- Script de création des tables pour le système de gestion des demandes ENEDIS
-- Version 2.0 - Compatible avec l'interface PHP/Python
--

SET NAMES 'utf8mb4';
USE appros;

--
-- Table de référence des types d'actions disponibles
--
CREATE TABLE IF NOT EXISTS sys_demande_ref (
  type_action VARCHAR(50) NOT NULL PRIMARY KEY COMMENT 'Code du type d''action',
  libelle VARCHAR(255) NOT NULL COMMENT 'Libellé court de l''action',
  description TEXT DEFAULT NULL COMMENT 'Description détaillée de l''action',
  actif TINYINT(1) DEFAULT 1 COMMENT 'Action active (1) ou désactivée (0)'
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Table de référence des types d''actions disponibles';

--
-- Insertion des types d'actions
--
INSERT INTO sys_demande_ref (type_action, libelle, description, actif) VALUES
('CREER_DEMANDE_TECHNIQUE', 'Demande de données techniques (C68)', 'Crée une demande C68 pour obtenir les données techniques d''un PRM (puissance souscrite, calendrier, etc.)', 1),
('CREER_DEMANDE_HISTO_FACT', 'Demande d''historique de facturation (R67)', 'Crée une demande R67 pour obtenir les index de facturation d''un PRM sur une période', 1),
('CREER_DEMANDE_HISTO_FINE', 'Demande de courbes de charge (R63/R64)', 'Crée une demande R63 ou R64 pour obtenir les données de consommation détaillées d''un PRM', 1),
('CONSULTER_MESURES', 'Consulter les mesures disponibles', 'Récupère les mesures déjà collectées pour un PRM', 1),
('LISTER_PRM', 'Lister les PRM gérés', 'Retourne la liste des Points de Référence Mesure dans la base de données', 1),
('DETAILS_PRM', 'Détails d''un PRM', 'Retourne les informations complètes d''un PRM', 1),
('STATISTIQUES', 'Statistiques générales', 'Génère des statistiques sur les demandes et les PRM', 1),
('HEALTHCHECK', 'Vérification du système', 'Test de fonctionnement du service backend', 1),
('EXPORT_DONNEES', 'Export des données', 'Exporte les données collectées au format CSV ou JSON', 1)
ON DUPLICATE KEY UPDATE 
  libelle = VALUES(libelle),
  description = VALUES(description),
  actif = VALUES(actif);

--
-- Table principale de gestion des demandes
--
CREATE TABLE IF NOT EXISTS sys_demande_sge (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  type_action VARCHAR(50) NOT NULL COMMENT 'Type d''action à effectuer',
  params JSON DEFAULT NULL COMMENT 'Paramètres JSON de la demande (PRM, dates, type_requete, sens, etc.)',
  statut VARCHAR(20) DEFAULT 'EN_ATTENTE' COMMENT 'Statut: EN_ATTENTE, EN_COURS, TERMINE, ERREUR',
  resultat JSON DEFAULT NULL COMMENT 'Résultat du traitement ou détails de l''erreur',
  date_creation DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date de création de la demande',
  date_debut_traitement DATETIME DEFAULT NULL COMMENT 'Date de début du traitement',
  date_fin_traitement DATETIME DEFAULT NULL COMMENT 'Date de fin du traitement',
  utilisateur VARCHAR(100) DEFAULT 'SYSTEM' COMMENT 'Utilisateur ayant créé la demande',
  commentaire TEXT DEFAULT NULL COMMENT 'Commentaire ou note sur la demande',
  PRIMARY KEY (id),
  INDEX idx_statut (statut),
  INDEX idx_type_action (type_action),
  INDEX idx_date_creation (date_creation),
  INDEX idx_utilisateur (utilisateur),
  CONSTRAINT fk_sys_demande_type_action 
    FOREIGN KEY (type_action) 
    REFERENCES sys_demande_ref(type_action)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
)
ENGINE = INNODB,
CHARACTER SET utf8mb4,
COLLATE utf8mb4_general_ci,
COMMENT = 'Table de gestion des demandes vers les services ENEDIS';

--
-- Création d'une vue pour faciliter les requêtes
--
CREATE OR REPLACE VIEW v_demandes_actives AS
SELECT 
  d.id,
  d.type_action,
  r.libelle as type_action_libelle,
  r.description as type_action_description,
  d.params,
  d.statut,
  d.resultat,
  d.date_creation,
  d.date_debut_traitement,
  d.date_fin_traitement,
  TIMESTAMPDIFF(SECOND, d.date_debut_traitement, d.date_fin_traitement) as duree_secondes,
  d.utilisateur,
  d.commentaire
FROM sys_demande_sge d
LEFT JOIN sys_demande_ref r ON d.type_action = r.type_action
WHERE d.statut IN ('EN_ATTENTE', 'EN_COURS')
ORDER BY d.date_creation DESC;

--
-- Vue pour les statistiques
--
CREATE OR REPLACE VIEW v_statistiques_demandes AS
SELECT 
  DATE(date_creation) as date,
  type_action,
  statut,
  COUNT(*) as nombre,
  AVG(TIMESTAMPDIFF(SECOND, date_debut_traitement, date_fin_traitement)) as duree_moyenne_sec
FROM sys_demande_sge
WHERE date_creation >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY DATE(date_creation), type_action, statut;

--
-- Affichage du résultat
--
SELECT '✅ Tables créées avec succès!' as message;
SELECT COUNT(*) as nombre_types_action FROM sys_demande_ref;
SELECT COUNT(*) as nombre_demandes FROM sys_demande_sge;
