-- ============================================================================
-- MIGRATION: Budget réel eau + Fond travaux + Indemnités assurance
-- Date: 2025-07-29
-- Description:
--   1. Ajout champ factures.paye_par_fond_travaux pour marquer les factures
--      payées par le fond de travaux (génère écriture 7xxx → 105000)
--   2. Ajout champ bank.is_indemnite_assurance pour les virements reçus
--      correspondant à une indemnité d'assurance
--   3. Ajout champ bank.id_facture_indemnisee pour lier l'indemnité 
--      à la facture de charge correspondante
--   4. Ajout champ factures.exclure_budget_reel pour exclure certaines
--      factures du calcul (ex: 671 charges exceptionnelles)
-- ============================================================================

-- ────────────────────────────────────────────────────────────────────────────
-- 1. Table factures : ajout du flag "payé par fond travaux"
-- ────────────────────────────────────────────────────────────────────────────
ALTER TABLE `factures`
ADD COLUMN `paye_par_fond_travaux` TINYINT(1) NOT NULL DEFAULT 0 
    COMMENT 'Si 1, la facture est prise sur le fond travaux (génère écriture 7xxx → 105000)'
AFTER `compte_charge`;

ALTER TABLE `factures`
ADD COLUMN `id_ecriture_fond_travaux` BIGINT NULL DEFAULT NULL 
    COMMENT 'ID de l''écriture comptable générée pour le remboursement fond travaux'
AFTER `paye_par_fond_travaux`;

ALTER TABLE `factures`
ADD COLUMN `exclure_budget_reel` TINYINT(1) NOT NULL DEFAULT 0 
    COMMENT 'Si 1, cette facture est exclue du calcul du budget réel (ex: indemnités assurance)'
AFTER `id_ecriture_fond_travaux`;

-- ────────────────────────────────────────────────────────────────────────────
-- 2. Table bank : ajout des champs pour indemnités assurance
-- ────────────────────────────────────────────────────────────────────────────
ALTER TABLE `bank`
ADD COLUMN `is_indemnite_assurance` TINYINT(1) NOT NULL DEFAULT 0 
    COMMENT 'Si 1, ce virement entrant est une indemnité d''assurance'
AFTER `id_ecriture_comptable`;

ALTER TABLE `bank`
ADD COLUMN `id_facture_indemnisee` INT NULL DEFAULT NULL 
    COMMENT 'ID de la facture de charge liée à cette indemnité (pour neutraliser dans le budget)'
AFTER `is_indemnite_assurance`;

ALTER TABLE `bank`
ADD COLUMN `motif_indemnite` VARCHAR(255) NULL DEFAULT NULL 
    COMMENT 'Description du sinistre/motif de l''indemnité'
AFTER `id_facture_indemnisee`;

-- Index pour optimiser les jointures
ALTER TABLE `bank`
ADD INDEX `idx_facture_indemnisee` (`id_facture_indemnisee`);

-- Contrainte de clé étrangère (optionnel - décommenter si souhaité)
-- ALTER TABLE `bank`
-- ADD CONSTRAINT `fk_bank_facture_indemnite` 
-- FOREIGN KEY (`id_facture_indemnisee`) REFERENCES `factures`(`id`) 
-- ON DELETE SET NULL ON UPDATE CASCADE;

-- ────────────────────────────────────────────────────────────────────────────
-- 3. Vue pour le calcul du budget réel par type de charge
--    Basé sur les factures validées (y compris eau)
-- ────────────────────────────────────────────────────────────────────────────
DROP VIEW IF EXISTS `v_budget_reel_par_type`;

CREATE VIEW `v_budget_reel_par_type` AS
SELECT 
    f.id_copro,
    f.id_exercice,
    fo.type AS type_id,
    t.type AS type_nom,
    SUM(
        CASE 
            -- Exclure les factures payées par fond travaux du budget réel
            WHEN f.paye_par_fond_travaux = 1 THEN 0
            -- Exclure les factures liées à une indemnité assurance reçue
            WHEN f.exclure_budget_reel = 1 THEN 0
            -- Exclure le compte 671 (charges exceptionnelles fond travaux)
            WHEN f.compte_charge LIKE '671%' THEN 0
            ELSE f.montant_ttc 
        END
    ) AS total_reel
FROM factures f
JOIN fournisseurs fo ON fo.id = f.fournisseur_id
JOIN types t ON t.id = fo.type
WHERE f.statut = 'validated'
  AND fo.type != 8            -- Exclure travaux votés AG uniquement
GROUP BY f.id_copro, f.id_exercice, fo.type, t.type;

-- ────────────────────────────────────────────────────────────────────────────
-- 4. Table de configuration pour les comptes comptables
-- ────────────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `config_comptes_comptables` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `id_copro` INT NOT NULL,
    `code_usage` VARCHAR(50) NOT NULL COMMENT 'fond_travaux_reserve, produits_fond_travaux, indemnite_assurance...',
    `numero_compte` VARCHAR(20) NOT NULL,
    `libelle` VARCHAR(255) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uniq_copro_usage` (`id_copro`, `code_usage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Configuration des comptes comptables par copropriété';

-- Valeurs par défaut pour les copropriétés existantes
INSERT INTO `config_comptes_comptables` (`id_copro`, `code_usage`, `numero_compte`, `libelle`)
SELECT DISTINCT 
    id_copro, 
    'fond_travaux_reserve',
    '105000',
    'Réserve fond travaux'
FROM exercices
ON DUPLICATE KEY UPDATE libelle = libelle;

INSERT INTO `config_comptes_comptables` (`id_copro`, `code_usage`, `numero_compte`, `libelle`)
SELECT DISTINCT 
    id_copro, 
    'produits_fond_travaux',
    '702000',
    'Produits - Utilisation fond travaux'
FROM exercices
ON DUPLICATE KEY UPDATE libelle = libelle;

INSERT INTO `config_comptes_comptables` (`id_copro`, `code_usage`, `numero_compte`, `libelle`)
SELECT DISTINCT 
    id_copro, 
    'indemnite_assurance',
    '791000',
    'Transfert de charges - Indemnités assurance'
FROM exercices
ON DUPLICATE KEY UPDATE libelle = libelle;

-- ────────────────────────────────────────────────────────────────────────────
-- 6. Procédure pour générer l'écriture fond travaux
-- ────────────────────────────────────────────────────────────────────────────
DROP PROCEDURE IF EXISTS `sp_generer_ecriture_fond_travaux`;

DELIMITER //

CREATE PROCEDURE `sp_generer_ecriture_fond_travaux`(
    IN p_id_facture INT,
    IN p_id_user INT
)
BEGIN
    DECLARE v_id_copro INT;
    DECLARE v_id_exercice INT;
    DECLARE v_montant DECIMAL(10,2);
    DECLARE v_libelle VARCHAR(255);
    DECLARE v_compte_reserve VARCHAR(20);
    DECLARE v_compte_produits VARCHAR(20);
    DECLARE v_id_ecriture BIGINT;
    DECLARE v_numero_ecriture VARCHAR(50);
    DECLARE v_count INT;
    
    -- Récupérer les infos de la facture
    SELECT f.id_copro, f.id_exercice, f.montant_ttc, 
           CONCAT('Utilisation fond travaux - ', COALESCE(f.fournisseur_nom, 'Facture'), ' - ', f.numero_facture)
    INTO v_id_copro, v_id_exercice, v_montant, v_libelle
    FROM factures f
    WHERE f.id = p_id_facture;
    
    IF v_id_copro IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Facture non trouvée';
    END IF;
    
    -- Récupérer les comptes configurés
    SELECT numero_compte INTO v_compte_reserve
    FROM config_comptes_comptables
    WHERE id_copro = v_id_copro AND code_usage = 'fond_travaux_reserve';
    
    SELECT numero_compte INTO v_compte_produits
    FROM config_comptes_comptables
    WHERE id_copro = v_id_copro AND code_usage = 'produits_fond_travaux';
    
    -- Valeurs par défaut si non configuré
    SET v_compte_reserve = COALESCE(v_compte_reserve, '105000');
    SET v_compte_produits = COALESCE(v_compte_produits, '702000');
    
    -- Générer le numéro d'écriture
    SELECT COUNT(*) + 1 INTO v_count
    FROM ecritures_comptables
    WHERE id_copro = v_id_copro AND id_exercice = v_id_exercice;
    
    SET v_numero_ecriture = CONCAT('FT-', YEAR(CURDATE()), '-', LPAD(v_count, 4, '0'));
    
    -- Créer l'en-tête d'écriture
    INSERT INTO ecritures_comptables (
        numero_ecriture, id_copro, id_exercice, date_ecriture,
        libelle, type_ecriture, source_id, source_type,
        montant_total, valide, created_by
    ) VALUES (
        v_numero_ecriture, v_id_copro, v_id_exercice, CURDATE(),
        v_libelle, 'od', p_id_facture, 'facture_fond_travaux',
        v_montant, 1, p_id_user
    );
    
    SET v_id_ecriture = LAST_INSERT_ID();
    
    -- Ligne 1: Débit 105000 (diminution de la réserve)
    INSERT INTO lignes_ecritures (
        id_ecriture, numero_ligne, numero_compte, libelle_ligne,
        debit, credit
    ) VALUES (
        v_id_ecriture, 1, v_compte_reserve, v_libelle,
        v_montant, 0.00
    );
    
    -- Ligne 2: Crédit 702000 (produit - neutralisation de la charge)
    INSERT INTO lignes_ecritures (
        id_ecriture, numero_ligne, numero_compte, libelle_ligne,
        debit, credit
    ) VALUES (
        v_id_ecriture, 2, v_compte_produits, v_libelle,
        0.00, v_montant
    );
    
    -- Mettre à jour la facture avec l'ID de l'écriture
    UPDATE factures 
    SET id_ecriture_fond_travaux = v_id_ecriture
    WHERE id = p_id_facture;
    
    SELECT v_id_ecriture AS id_ecriture, v_numero_ecriture AS numero_ecriture;
END //

DELIMITER ;

-- ────────────────────────────────────────────────────────────────────────────
-- 7. Procédure pour générer l'écriture indemnité assurance
-- ────────────────────────────────────────────────────────────────────────────
DROP PROCEDURE IF EXISTS `sp_generer_ecriture_indemnite_assurance`;

DELIMITER //

CREATE PROCEDURE `sp_generer_ecriture_indemnite_assurance`(
    IN p_id_bank INT,
    IN p_id_facture_liee INT,
    IN p_id_user INT
)
BEGIN
    DECLARE v_id_copro INT;
    DECLARE v_id_exercice INT;
    DECLARE v_montant DECIMAL(10,2);
    DECLARE v_libelle VARCHAR(255);
    DECLARE v_compte_indemnite VARCHAR(20);
    DECLARE v_compte_charge VARCHAR(20);
    DECLARE v_id_ecriture BIGINT;
    DECLARE v_numero_ecriture VARCHAR(50);
    DECLARE v_count INT;
    
    -- Récupérer les infos de l'opération bancaire
    SELECT b.id_copro, b.exercice, b.credit, 
           CONCAT('Indemnité assurance - ', b.description)
    INTO v_id_copro, v_id_exercice, v_montant, v_libelle
    FROM bank b
    WHERE b.id = p_id_bank;
    
    IF v_id_copro IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Opération bancaire non trouvée';
    END IF;
    
    -- Récupérer le compte de charge de la facture liée (pour pouvoir le neutraliser)
    IF p_id_facture_liee IS NOT NULL THEN
        SELECT compte_charge INTO v_compte_charge
        FROM factures
        WHERE id = p_id_facture_liee;
    END IF;
    
    -- Récupérer le compte indemnité configuré
    SELECT numero_compte INTO v_compte_indemnite
    FROM config_comptes_comptables
    WHERE id_copro = v_id_copro AND code_usage = 'indemnite_assurance';
    
    SET v_compte_indemnite = COALESCE(v_compte_indemnite, '791000');
    
    -- Marquer l'opération bancaire comme indemnité
    UPDATE bank 
    SET is_indemnite_assurance = 1,
        id_facture_indemnisee = p_id_facture_liee
    WHERE id = p_id_bank;
    
    -- Si facture liée, la marquer comme exclue du budget réel
    IF p_id_facture_liee IS NOT NULL THEN
        UPDATE factures
        SET exclure_budget_reel = 1
        WHERE id = p_id_facture_liee;
    END IF;
    
    SELECT p_id_bank AS id_bank, 'Indemnité marquée' AS status;
END //

DELIMITER ;

-- ────────────────────────────────────────────────────────────────────────────
-- FIN MIGRATION
-- ────────────────────────────────────────────────────────────────────────────
