-- =============================================
-- Table: sys_demande_recurrente
-- Description: Gestion des demandes récurrentes (abonnements) SGE ENEDIS
-- Date de création: 2026-02-13
-- =============================================

-- Suppression de la table si elle existe déjà

-- Création de la table sys_demande_recurrente
CREATE TABLE sys_demande_recurrente (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Identifiant ENEDIS
    id_demande_enedis VARCHAR(100) DEFAULT NULL COMMENT 'Identifiant de la demande retourné par ENEDIS',
    id_abonnement_enedis VARCHAR(100) DEFAULT NULL COMMENT 'Identifiant abonnement retourné par ENEDIS (pour gestion cycle de vie)',
    
    -- Type de demande récurrente
    type_requete VARCHAR(50) NOT NULL COMMENT 'Type de requête: R67_RECURRENT, R63_RECURRENT, R64_RECURRENT',
    sens VARCHAR(20) NOT NULL COMMENT 'INJECTION ou SOUTIRAGE',
    
    -- Liste des PRM concernés
    prm_list TEXT NOT NULL COMMENT 'Liste des PRM (JSON array) concernés par la demande récurrente',
    nb_prm INT NOT NULL COMMENT 'Nombre de PRM dans la demande',
    
    -- Paramètres de récurrence
    frequence VARCHAR(20) NOT NULL COMMENT 'QUOTIDIEN, HEBDOMADAIRE, MENSUEL',
    date_debut DATE NOT NULL COMMENT 'Date de début de la récurrence',
    date_fin DATE DEFAULT NULL COMMENT 'Date de fin prévue (NULL = illimité)',
    
    -- Statut de l'abonnement
    statut VARCHAR(50) DEFAULT 'EN_ATTENTE' COMMENT 'EN_ATTENTE, ACTIF, SUSPENDU, ARRETE, ERREUR',
    
    -- Détails des publications
    derniere_publication_date DATETIME DEFAULT NULL COMMENT 'Date de la dernière publication reçue',
    nb_publications_recues INT DEFAULT 0 COMMENT 'Nombre total de publications reçues',
    prochaine_publication_attendue DATE DEFAULT NULL COMMENT 'Date théorique de la prochaine publication',
    
    -- Gestion du cycle de vie
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date création de la demande récurrente',
    date_activation DATETIME DEFAULT NULL COMMENT 'Date activation par ENEDIS',
    date_suspension DATETIME DEFAULT NULL COMMENT 'Date de suspension (si applicable)',
    date_arret DATETIME DEFAULT NULL COMMENT 'Date arrêt définitif',
    date_derniere_modif DATETIME DEFAULT NULL COMMENT 'Date dernière modification',
    
    -- Métadonnées
    created_by VARCHAR(100) DEFAULT NULL COMMENT 'Utilisateur créateur',
    commentaire TEXT DEFAULT NULL COMMENT 'Commentaire libre',
    erreur TEXT DEFAULT NULL COMMENT 'Message erreur si statut ERREUR',
    
    -- Paramètres techniques supplémentaires (JSON)
    parametres_json TEXT DEFAULT NULL COMMENT 'Paramètres additionnels en JSON',
    
    -- Index
    INDEX idx_id_demande_enedis (id_demande_enedis),
    INDEX idx_id_abonnement (id_abonnement_enedis),
    INDEX idx_type_requete (type_requete),
    INDEX idx_statut (statut),
    INDEX idx_date_creation (date_creation),
    INDEX idx_frequence (frequence),
    
    CONSTRAINT chk_type_requete_recurrent CHECK (type_requete IN ('R67_RECURRENT', 'R63_RECURRENT', 'R64_RECURRENT')),
    CONSTRAINT chk_sens_recurrent CHECK (sens IN ('INJECTION', 'SOUTIRAGE')),
    CONSTRAINT chk_statut_recurrent CHECK (statut IN ('EN_ATTENTE', 'ACTIF', 'SUSPENDU', 'ARRETE', 'ERREUR')),
    CONSTRAINT chk_frequence CHECK (frequence IN ('QUOTIDIEN', 'HEBDOMADAIRE', 'MENSUEL'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Gestion des demandes récurrentes SGE ENEDIS';

-- =============================================
-- Table: sys_publication_recurrente
-- Description: Historique des publications reçues pour les demandes récurrentes
-- =============================================


CREATE TABLE sys_publication_recurrente (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    -- Lien avec la demande récurrente
    id_demande_recurrente INT NOT NULL COMMENT 'FK vers sys_demande_recurrente',
    id_demande_enedis VARCHAR(100) DEFAULT NULL COMMENT 'ID demande ENEDIS pour traçabilité',
    
    -- Informations sur la publication
    date_publication DATETIME NOT NULL COMMENT 'Date de publication par ENEDIS',
    periode_debut DATE NOT NULL COMMENT 'Début de la période couverte par cette publication',
    periode_fin DATE NOT NULL COMMENT 'Fin de la période couverte',
    
    -- Statut du traitement
    statut VARCHAR(50) DEFAULT 'RECU' COMMENT 'RECU, EN_COURS, TRAITE, ERREUR',
    
    -- Stockage des données
    donnees_json LONGTEXT DEFAULT NULL COMMENT 'Données reçues en JSON',
    format_donnees VARCHAR(50) DEFAULT NULL COMMENT 'Format des données: JSON, XML, CSV',
    taille_donnees INT DEFAULT NULL COMMENT 'Taille en octets',
    
    -- Traitement
    date_reception DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'Date réception par notre système',
    date_traitement DATETIME DEFAULT NULL COMMENT 'Date de traitement des données',
    nb_lignes_traitees INT DEFAULT NULL COMMENT 'Nombre de lignes/mesures traitées',
    
    -- Erreurs éventuelles
    erreur TEXT DEFAULT NULL COMMENT 'Message erreur si problème',
    
    -- Métadonnées
    fichier_source VARCHAR(255) DEFAULT NULL COMMENT 'Nom du fichier source si applicable',
    checksum VARCHAR(64) DEFAULT NULL COMMENT 'Checksum pour vérification intégrité',
    
    -- Index
    INDEX idx_demande_recurrente (id_demande_recurrente),
    INDEX idx_date_publication (date_publication),
    INDEX idx_periode (periode_debut, periode_fin),
    INDEX idx_statut (statut),
    
    FOREIGN KEY (id_demande_recurrente) REFERENCES sys_demande_recurrente(id) ON DELETE CASCADE,
    
    CONSTRAINT chk_statut_publication CHECK (statut IN ('RECU', 'EN_COURS', 'TRAITE', 'ERREUR'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Historique publications récurrentes ENEDIS';

-- =============================================
-- Vues pratiques
-- =============================================

-- Vue des demandes récurrentes actives
CREATE OR REPLACE VIEW v_demandes_recurrentes_actives AS
SELECT 
    dr.id,
    dr.id_abonnement_enedis,
    dr.type_requete,
    dr.sens,
    dr.nb_prm,
    dr.frequence,
    dr.date_debut,
    dr.date_fin,
    dr.statut,
    dr.nb_publications_recues,
    dr.derniere_publication_date,
    dr.prochaine_publication_attendue,
    DATEDIFF(NOW(), dr.derniere_publication_date) as jours_depuis_derniere_pub,
    dr.date_creation
FROM sys_demande_recurrente dr
WHERE dr.statut IN ('ACTIF', 'EN_ATTENTE')
ORDER BY dr.date_creation DESC;

-- Vue statistiques par demande récurrente
CREATE OR REPLACE VIEW v_stats_demandes_recurrentes AS
SELECT 
    dr.id,
    dr.id_abonnement_enedis,
    dr.type_requete,
    dr.sens,
    dr.statut,
    dr.nb_prm,
    dr.nb_publications_recues,
    COUNT(pr.id) as nb_publications_stockees,
    MIN(pr.periode_debut) as premiere_periode,
    MAX(pr.periode_fin) as derniere_periode,
    SUM(CASE WHEN pr.statut = 'TRAITE' THEN 1 ELSE 0 END) as nb_traitees,
    SUM(CASE WHEN pr.statut = 'ERREUR' THEN 1 ELSE 0 END) as nb_erreurs,
    SUM(pr.nb_lignes_traitees) as total_lignes_traitees
FROM sys_demande_recurrente dr
LEFT JOIN sys_publication_recurrente pr ON pr.id_demande_recurrente = dr.id
GROUP BY dr.id, dr.id_abonnement_enedis, dr.type_requete, dr.sens, dr.statut, dr.nb_prm, dr.nb_publications_recues;

-- Vue publications en attente de traitement
CREATE OR REPLACE VIEW v_publications_en_attente AS
SELECT 
    pr.id,
    pr.id_demande_recurrente,
    dr.type_requete,
    dr.sens,
    pr.date_publication,
    pr.periode_debut,
    pr.periode_fin,
    pr.statut,
    pr.date_reception,
    TIMESTAMPDIFF(HOUR, pr.date_reception, NOW()) as heures_en_attente
FROM sys_publication_recurrente pr
INNER JOIN sys_demande_recurrente dr ON dr.id = pr.id_demande_recurrente
WHERE pr.statut IN ('RECU', 'EN_COURS')
ORDER BY pr.date_reception ASC;

-- Messages de confirmation
SELECT 'Table sys_demande_recurrente créée avec succès' as message;
SELECT 'Table sys_publication_recurrente créée avec succès' as message;
SELECT 'Vues créées avec succès' as message;
