-- Table pour l'analyse prédictive des factures
CREATE TABLE IF NOT EXISTS `factures_predictions` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `id_copro` INT NOT NULL,
    `id_fournisseur` INT NOT NULL,
    `fournisseur_nom` VARCHAR(255) NOT NULL,
    
    -- Analyse historique
    `periodicite` ENUM('mensuel', 'bimensuel', 'trimestriel', 'semestriel', 'annuel', 'irregulier') DEFAULT 'irregulier',
    `nb_factures_analysees` INT DEFAULT 0,
    `montant_moyen` DECIMAL(10,2) DEFAULT 0,
    `montant_min` DECIMAL(10,2) DEFAULT 0,
    `montant_max` DECIMAL(10,2) DEFAULT 0,
    `ecart_type` DECIMAL(10,2) DEFAULT 0,
    `jour_moyen_facture` INT DEFAULT NULL COMMENT 'Jour du mois moyen (1-31)',
    
    -- Tendance
    `tendance` ENUM('stable', 'hausse', 'baisse') DEFAULT 'stable',
    `taux_variation_annuel` DECIMAL(5,2) DEFAULT 0 COMMENT 'Pourcentage de variation',
    
    -- Prédiction prochaine facture
    `date_derniere_facture` DATE DEFAULT NULL,
    `date_prevue_prochaine` DATE DEFAULT NULL,
    `montant_prevu_prochaine` DECIMAL(10,2) DEFAULT 0,
    `confiance_prediction` DECIMAL(3,2) DEFAULT 0 COMMENT 'Score 0-1',
    
    -- Alertes
    `alerte_active` BOOLEAN DEFAULT FALSE,
    `alerte_type` ENUM('retard', 'montant_anormal', 'nouvelle_facture') DEFAULT NULL,
    `alerte_message` TEXT DEFAULT NULL,
    
    -- Métadonnées
    `derniere_analyse` DATETIME DEFAULT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    UNIQUE KEY `unique_copro_fournisseur` (`id_copro`, `id_fournisseur`),
    INDEX `idx_copro_periodicite` (`id_copro`, `periodicite`),
    INDEX `idx_date_prevue` (`date_prevue_prochaine`),
    INDEX `idx_alertes` (`alerte_active`, `alerte_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
