-- ============================================================
-- Migration : 2026_04_18_creation_base_aec_compta.sql
-- Projet    : Comptabilité AEC CONSEILS
-- Base      : aec_compta (MySQL 8.0)
-- Auteur    : Guillaume — AEC CONSEILS (ORIAS 12069078)
-- Date      : 2026-04-18
-- ============================================================
-- Exécution : mysql -u root -p < migrations/2026_04_18_creation_base_aec_compta.sql
-- ============================================================

CREATE DATABASE IF NOT EXISTS `aec_compta`
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE `aec_compta`;

SET FOREIGN_KEY_CHECKS = 0;
SET sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

CREATE TABLE IF NOT EXISTS `aec_compta_exercices` (
    `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `annee`       YEAR         NOT NULL,
    `date_debut`  DATE         NOT NULL,
    `date_fin`    DATE         NOT NULL,
    `statut`      ENUM('ouvert','cloture') DEFAULT 'ouvert',
    `objectif_ca` DECIMAL(10,2) DEFAULT NULL COMMENT 'Objectif CA annuel saisi manuellement',
    `created_at`  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `uk_exercice_annee` (`annee`)
) ENGINE=InnoDB COMMENT='Exercices fiscaux annuels';

CREATE TABLE IF NOT EXISTS `aec_compta_compagnies` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`             VARCHAR(20)  NOT NULL UNIQUE COMMENT 'Code court : GEN, SUR, APR, GAN, API',
    `nom`              VARCHAR(100) NOT NULL,
    `type_commission`  ENUM('honoraires','commission','mixte') DEFAULT 'commission',
    `delai_versement`  TINYINT UNSIGNED DEFAULT 2 COMMENT 'Délai moyen versement en mois',
    `couleur_graphe`   VARCHAR(7)   DEFAULT '#6c757d' COMMENT 'Couleur hex pour Chart.js',
    `actif`            TINYINT(1)   DEFAULT 1,
    `created_at`       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB COMMENT='Compagnies partenaires courtage';

CREATE TABLE IF NOT EXISTS `aec_compta_types_produits` (
    `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code`      VARCHAR(30)  NOT NULL UNIQUE COMMENT 'Ex: PREV_TNS, AV, PER, IARD, AE',
    `libelle`   VARCHAR(100) NOT NULL,
    `categorie` ENUM('vie','iard','prevoyance','epargne','credit') NOT NULL,
    `actif`     TINYINT(1)   DEFAULT 1
) ENGINE=InnoDB COMMENT='Types de produits (gamme courtage)';

CREATE TABLE IF NOT EXISTS `aec_compta_bordereaux` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `compagnie_id`     INT UNSIGNED NOT NULL,
    `reference`        VARCHAR(100) DEFAULT NULL,
    `date_bordereau`   DATE         NOT NULL,
    `date_reception`   DATE         DEFAULT NULL,
    `periode_debut`    DATE         NOT NULL,
    `periode_fin`      DATE         NOT NULL,
    `montant_brut`     DECIMAL(10,2) NOT NULL,
    `montant_net`      DECIMAL(10,2) DEFAULT NULL,
    `statut`           ENUM('recu','rapproche','valide','litige') DEFAULT 'recu',
    `fichier_path`     VARCHAR(255) DEFAULT NULL COMMENT 'Chemin relatif vers /uploads/bordereaux/',
    `notes`            TEXT         DEFAULT NULL,
    `created_at`       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`compagnie_id`) REFERENCES `aec_compta_compagnies`(`id`) ON DELETE RESTRICT,
    INDEX `idx_bordereau_statut`    (`statut`),
    INDEX `idx_bordereau_periode`   (`periode_debut`, `periode_fin`),
    INDEX `idx_bordereau_compagnie` (`compagnie_id`)
) ENGINE=InnoDB COMMENT='Bordereaux de commissions reçus des compagnies';

CREATE TABLE IF NOT EXISTS `aec_compta_lignes_commissions` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bordereau_id`     INT UNSIGNED NOT NULL,
    `contrat_ref`      VARCHAR(100) DEFAULT NULL COMMENT 'Référence contrat — lien CRM',
    -- TODO-CRM: contrat_ref correspond à la référence contrat dans crm.aecconseils.fr
    `client_nom`       VARCHAR(150) DEFAULT NULL,
    `type_produit_id`  INT UNSIGNED DEFAULT NULL,
    `type_mouvement`   ENUM('affaire_nouvelle','renouvellement','regularisation','avoir') NOT NULL,
    `montant`          DECIMAL(10,2) NOT NULL,
    `taux_commission`  DECIMAL(5,2) DEFAULT NULL,
    `periode`          DATE         DEFAULT NULL,
    `rapproche`        TINYINT(1)   DEFAULT 0,
    FOREIGN KEY (`bordereau_id`)    REFERENCES `aec_compta_bordereaux`(`id`)     ON DELETE CASCADE,
    FOREIGN KEY (`type_produit_id`) REFERENCES `aec_compta_types_produits`(`id`) ON DELETE SET NULL,
    INDEX `idx_ligne_bordereau` (`bordereau_id`),
    INDEX `idx_ligne_contrat`   (`contrat_ref`),
    INDEX `idx_ligne_mouvement` (`type_mouvement`)
) ENGINE=InnoDB COMMENT='Lignes de détail des bordereaux de commissions';

CREATE TABLE IF NOT EXISTS `aec_compta_encaissements` (
    `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bordereau_id`        INT UNSIGNED DEFAULT NULL,
    `date_encaissement`   DATE         NOT NULL,
    `montant`             DECIMAL(10,2) NOT NULL,
    `mode`                ENUM('virement','cheque','prelevement') DEFAULT 'virement',
    `reference_bancaire`  VARCHAR(100) DEFAULT NULL,
    `exercice_id`         INT UNSIGNED NOT NULL,
    `mois`                TINYINT UNSIGNED NOT NULL COMMENT '1 à 12',
    `notes`               TEXT         DEFAULT NULL,
    `created_at`          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`bordereau_id`) REFERENCES `aec_compta_bordereaux`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`exercice_id`)  REFERENCES `aec_compta_exercices`(`id`)  ON DELETE RESTRICT,
    INDEX `idx_encaissement_date` (`date_encaissement`),
    INDEX `idx_encaissement_mois` (`exercice_id`, `mois`)
) ENGINE=InnoDB COMMENT='Encaissements réels (virements compagnies)';

CREATE TABLE IF NOT EXISTS `aec_compta_charges` (
    `id`               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `exercice_id`      INT UNSIGNED NOT NULL,
    `categorie`        ENUM('loyer','assurance_rc','orias','cotisations',
                           'honoraires_expert_comptable','informatique','marketing',
                           'formation','telephone','autre') NOT NULL,
    `libelle`          VARCHAR(200) NOT NULL,
    `montant`          DECIMAL(10,2) NOT NULL,
    `frequence`        ENUM('mensuelle','trimestrielle','annuelle','ponctuelle') NOT NULL,
    `date_echeance`    DATE         DEFAULT NULL,
    `mois_imputation`  TINYINT UNSIGNED DEFAULT NULL,
    `recurrente`       TINYINT(1)   DEFAULT 1,
    `tva_deductible`   DECIMAL(10,2) DEFAULT 0.00,
    `created_at`       TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`exercice_id`) REFERENCES `aec_compta_exercices`(`id`) ON DELETE RESTRICT,
    INDEX `idx_charge_categorie` (`categorie`),
    INDEX `idx_charge_exercice`  (`exercice_id`)
) ENGINE=InnoDB COMMENT='Charges fixes et variables du cabinet';

CREATE TABLE IF NOT EXISTS `aec_compta_previsions_ca` (
    `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `exercice_id`          INT UNSIGNED NOT NULL,
    `mois`                 TINYINT UNSIGNED NOT NULL COMMENT '1 à 12',
    `source`               ENUM('crm','manuelle','historique') DEFAULT 'manuelle',
    `compagnie_id`         INT UNSIGNED DEFAULT NULL,
    `type_produit_id`      INT UNSIGNED DEFAULT NULL,
    `montant_prevu`        DECIMAL(10,2) NOT NULL,
    `probabilite`          TINYINT UNSIGNED DEFAULT 100 COMMENT '% probabilité (issu pipe CRM)',
    `notes`                TEXT         DEFAULT NULL,
    `crm_opportunite_id`   INT UNSIGNED DEFAULT NULL COMMENT 'ID opportunité CRM — TODO-CRM',
    -- TODO-CRM: crm_opportunite_id est l'ID retourné par GET /api/opportunites
    `updated_at`           TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`exercice_id`)     REFERENCES `aec_compta_exercices`(`id`)      ON DELETE RESTRICT,
    FOREIGN KEY (`compagnie_id`)    REFERENCES `aec_compta_compagnies`(`id`)     ON DELETE SET NULL,
    FOREIGN KEY (`type_produit_id`) REFERENCES `aec_compta_types_produits`(`id`) ON DELETE SET NULL,
    INDEX `idx_prev_exercice_mois` (`exercice_id`, `mois`),
    INDEX `idx_prev_source`        (`source`)
) ENGINE=InnoDB COMMENT='Prévisions de CA mensuelles (manuel + CRM)';

CREATE TABLE IF NOT EXISTS `aec_compta_audit_log` (
    `id`                INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id`           INT UNSIGNED DEFAULT NULL,
    `action`            VARCHAR(50)  NOT NULL,
    `table_cible`       VARCHAR(50)  NOT NULL,
    `enregistrement_id` INT UNSIGNED DEFAULT NULL,
    `donnees_avant`     JSON DEFAULT NULL,
    `donnees_apres`     JSON DEFAULT NULL,
    `ip_address`        VARCHAR(45)  DEFAULT NULL,
    `created_at`        TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_audit_table` (`table_cible`),
    INDEX `idx_audit_date`  (`created_at`)
) ENGINE=InnoDB COMMENT='Journal d''audit des modifications';

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- DONNÉES DE RÉFÉRENCE
-- ============================================================

INSERT INTO `aec_compta_exercices` (`annee`, `date_debut`, `date_fin`, `statut`, `objectif_ca`) VALUES
(2025, '2025-01-01', '2025-12-31', 'cloture', 140000.00),
(2026, '2026-01-01', '2026-12-31', 'ouvert',  155000.00);

INSERT INTO `aec_compta_compagnies` (`code`, `nom`, `type_commission`, `delai_versement`, `couleur_graphe`) VALUES
('GEN', 'Generali',  'commission', 2, '#00814A'),
('SUR', 'Suravenir', 'commission', 1, '#1a3a5c'),
('APR', 'April',     'mixte',      2, '#FD7E14'),
('GAN', 'GAN',       'commission', 3, '#DC3545'),
('API', 'APICIL',    'commission', 2, '#6f42c1');

INSERT INTO `aec_compta_types_produits` (`code`, `libelle`, `categorie`) VALUES
('AV',        'Assurance-Vie',                   'vie'),
('PER',       'Plan d''Épargne Retraite',        'epargne'),
('PREV_TNS',  'Prévoyance TNS',                  'prevoyance'),
('PREV_SAL',  'Prévoyance Salarié',              'prevoyance'),
('IARD_PRO',  'IARD Professionnel',              'iard'),
('IARD_PART', 'IARD Particulier',                'iard'),
('AE',        'Assurance Emprunteur',            'credit'),
('SANTE',     'Mutuelle Santé TNS',              'prevoyance'),
('OB',        'Obligations / Produit structuré', 'epargne');
