-- ============================================================
-- Migration : 2026_04_18_donnees_test.sql
-- Projet    : Comptabilité AEC CONSEILS
-- Objet     : Jeu de données de test — 12 mois glissants
--             CA mensuel entre 8 000 € et 15 000 €
-- ⚠️  NE PAS exécuter en production
-- ============================================================

USE `aec_compta`;

INSERT INTO `aec_compta_bordereaux`
    (`compagnie_id`, `reference`, `date_bordereau`, `date_reception`,
     `periode_debut`, `periode_fin`, `montant_brut`, `montant_net`, `statut`)
VALUES
-- JANVIER 2025
(1,'GEN-2025-01-0847','2025-03-05','2025-03-07','2025-01-01','2025-01-31', 4850.00, 4850.00,'valide'),
(2,'SUR-25-JAN-112',  '2025-02-15','2025-02-16','2025-01-01','2025-01-31', 3020.00, 3020.00,'valide'),
(3,'APR-JAN25-04',    '2025-03-10','2025-03-12','2025-01-01','2025-01-31', 1640.00, 1640.00,'valide'),
(4,'GAN-2501-093',    '2025-04-02','2025-04-04','2025-01-01','2025-01-31',  980.00,  980.00,'valide'),
(5,'API-2501-018',    '2025-03-20','2025-03-21','2025-01-01','2025-01-31',  710.00,  710.00,'valide'),
-- FÉVRIER 2025
(1,'GEN-2025-02-0901','2025-04-05','2025-04-07','2025-02-01','2025-02-28', 4920.00, 4920.00,'valide'),
(2,'SUR-25-FEV-118',  '2025-03-15','2025-03-16','2025-02-01','2025-02-28', 2980.00, 2980.00,'valide'),
(3,'APR-FEV25-05',    '2025-04-11','2025-04-12','2025-02-01','2025-02-28', 1580.00, 1580.00,'valide'),
(4,'GAN-2502-101',    '2025-05-03','2025-05-05','2025-02-01','2025-02-28',  870.00,  870.00,'valide'),
(5,'API-2502-021',    '2025-04-18','2025-04-19','2025-02-01','2025-02-28',  650.00,  650.00,'valide'),
-- MARS 2025
(1,'GEN-2025-03-0962','2025-05-06','2025-05-08','2025-03-01','2025-03-31', 5840.00, 5840.00,'valide'),
(2,'SUR-25-MAR-127',  '2025-04-15','2025-04-16','2025-03-01','2025-03-31', 3450.00, 3450.00,'valide'),
(3,'APR-MAR25-06',    '2025-05-10','2025-05-12','2025-03-01','2025-03-31', 2100.00, 2100.00,'valide'),
(4,'GAN-2503-112',    '2025-06-04','2025-06-05','2025-03-01','2025-03-31', 1380.00, 1380.00,'valide'),
(5,'API-2503-028',    '2025-05-20','2025-05-21','2025-03-01','2025-03-31',  830.00,  830.00,'valide'),
-- AVRIL 2025
(1,'GEN-2025-04-1018','2025-06-05','2025-06-07','2025-04-01','2025-04-30', 4720.00, 4720.00,'valide'),
(2,'SUR-25-AVR-134',  '2025-05-15','2025-05-16','2025-04-01','2025-04-30', 2890.00, 2890.00,'valide'),
(3,'APR-AVR25-07',    '2025-06-12','2025-06-13','2025-04-01','2025-04-30', 1720.00, 1720.00,'valide'),
(4,'GAN-2504-118',    '2025-07-03','2025-07-04','2025-04-01','2025-04-30', 1050.00, 1050.00,'valide'),
(5,'API-2504-031',    '2025-06-19','2025-06-20','2025-04-01','2025-04-30',  620.00,  620.00,'valide'),
-- MAI 2025
(1,'GEN-2025-05-1071','2025-07-07','2025-07-08','2025-05-01','2025-05-31', 5100.00, 5100.00,'valide'),
(2,'SUR-25-MAI-140',  '2025-06-14','2025-06-15','2025-05-01','2025-05-31', 3150.00, 3150.00,'valide'),
(3,'APR-MAI25-09',    '2025-07-11','2025-07-11','2025-05-01','2025-05-31', 1890.00, 1890.00,'valide'),
(4,'GAN-2505-125',    '2025-08-05','2025-08-06','2025-05-01','2025-05-31', 1120.00, 1120.00,'valide'),
(5,'API-2505-035',    '2025-07-21','2025-07-21','2025-05-01','2025-05-31',  740.00,  740.00,'valide'),
-- JUIN 2025
(1,'GEN-2025-06-1124','2025-08-06','2025-08-07','2025-06-01','2025-06-30', 5280.00, 5280.00,'valide'),
(2,'SUR-25-JUN-148',  '2025-07-15','2025-07-15','2025-06-01','2025-06-30', 3300.00, 3300.00,'valide'),
(3,'APR-JUN25-10',    '2025-08-13','2025-08-13','2025-06-01','2025-06-30', 1950.00, 1950.00,'valide'),
(4,'GAN-2506-131',    '2025-09-03','2025-09-04','2025-06-01','2025-06-30', 1200.00, 1200.00,'valide'),
(5,'API-2506-038',    '2025-08-19','2025-08-19','2025-06-01','2025-06-30',  770.00,  770.00,'valide'),
-- JUILLET 2025 — creux
(1,'GEN-2025-07-1178','2025-09-05','2025-09-06','2025-07-01','2025-07-31', 3980.00, 3980.00,'valide'),
(2,'SUR-25-JUL-155',  '2025-08-14','2025-08-15','2025-07-01','2025-07-31', 2350.00, 2350.00,'valide'),
(3,'APR-JUL25-11',    '2025-09-10','2025-09-10','2025-07-01','2025-07-31', 1290.00, 1290.00,'valide'),
(4,'GAN-2507-137',    '2025-10-02','2025-10-03','2025-07-01','2025-07-31',  780.00,  780.00,'valide'),
(5,'API-2507-041',    '2025-09-17','2025-09-17','2025-07-01','2025-07-31',  500.00,  500.00,'valide'),
-- AOÛT 2025 — creux
(1,'GEN-2025-08-1221','2025-10-07','2025-10-08','2025-08-01','2025-08-31', 3820.00, 3820.00,'valide'),
(2,'SUR-25-AOU-161',  '2025-09-15','2025-09-15','2025-08-01','2025-08-31', 2200.00, 2200.00,'valide'),
(3,'APR-AOU25-12',    '2025-10-14','2025-10-14','2025-08-01','2025-08-31', 1180.00, 1180.00,'valide'),
(4,'GAN-2508-142',    '2025-11-05','2025-11-06','2025-08-01','2025-08-31',  710.00,  710.00,'valide'),
(5,'API-2508-044',    '2025-10-20','2025-10-20','2025-08-01','2025-08-31',  490.00,  490.00,'valide'),
-- SEPTEMBRE 2025 — reprise
(1,'GEN-2025-09-1289','2025-11-06','2025-11-07','2025-09-01','2025-09-30', 5620.00, 5620.00,'valide'),
(2,'SUR-25-SEP-170',  '2025-10-15','2025-10-15','2025-09-01','2025-09-30', 3480.00, 3480.00,'valide'),
(3,'APR-SEP25-14',    '2025-11-12','2025-11-12','2025-09-01','2025-09-30', 2050.00, 2050.00,'valide'),
(4,'GAN-2509-149',    '2025-12-04','2025-12-05','2025-09-01','2025-09-30', 1280.00, 1280.00,'valide'),
(5,'API-2509-048',    '2025-11-18','2025-11-18','2025-09-01','2025-09-30',  870.00,  870.00,'valide'),
-- OCTOBRE 2025
(1,'GEN-2025-10-1347','2025-12-05','2025-12-06','2025-10-01','2025-10-31', 6100.00, 6100.00,'valide'),
(2,'SUR-25-OCT-178',  '2025-11-14','2025-11-14','2025-10-01','2025-10-31', 3750.00, 3750.00,'valide'),
(3,'APR-OCT25-15',    '2025-12-11','2025-12-11','2025-10-01','2025-10-31', 2280.00, 2280.00,'valide'),
(4,'GAN-2510-156',    '2026-01-08','2026-01-09','2025-10-01','2025-10-31', 1420.00, 1420.00,'valide'),
(5,'API-2510-052',    '2025-12-17','2025-12-17','2025-10-01','2025-10-31',  950.00,  950.00,'valide'),
-- NOVEMBRE 2025
(1,'GEN-2025-11-1398','2026-01-07','2026-01-08','2025-11-01','2025-11-30', 5840.00, 5840.00,'valide'),
(2,'SUR-25-NOV-185',  '2025-12-15','2025-12-15','2025-11-01','2025-11-30', 3580.00, 3580.00,'valide'),
(3,'APR-NOV25-16',    '2026-01-14','2026-01-14','2025-11-01','2025-11-30', 2140.00, 2140.00,'valide'),
(4,'GAN-2511-161',    '2026-02-06','2026-02-07','2025-11-01','2025-11-30', 1310.00, 1310.00,'valide'),
(5,'API-2511-055',    '2026-01-19','2026-01-19','2025-11-01','2025-11-30',  830.00,  830.00,'valide'),
-- DÉCEMBRE 2025
(1,'GEN-2025-12-1452','2026-02-06','2026-02-07','2025-12-01','2025-12-31', 5420.00, 5420.00,'valide'),
(2,'SUR-25-DEC-191',  '2026-01-15','2026-01-15','2025-12-01','2025-12-31', 3290.00, 3290.00,'valide'),
(3,'APR-DEC25-17',    '2026-02-12','2026-02-12','2025-12-01','2025-12-31', 1980.00, 1980.00,'valide'),
(4,'GAN-2512-168',    '2026-03-07','2026-03-08','2025-12-01','2025-12-31', 1190.00, 1190.00,'valide'),
(5,'API-2512-058',    '2026-02-18','2026-02-18','2025-12-01','2025-12-31',  720.00,  720.00,'valide'),
-- JANVIER 2026
(1,'GEN-2026-01-0012','2026-03-05','2026-03-06','2026-01-01','2026-01-31', 5180.00, 5180.00,'valide'),
(2,'SUR-26-JAN-008',  '2026-02-14','2026-02-14','2026-01-01','2026-01-31', 3120.00, 3120.00,'valide'),
(3,'APR-JAN26-02',    '2026-03-11','2026-03-11','2026-01-01','2026-01-31', 1780.00, 1780.00,'valide'),
(4,'GAN-2601-007',    '2026-04-04','2026-04-05','2026-01-01','2026-01-31', 1080.00, 1080.00,'valide'),
(5,'API-2601-004',    '2026-03-19','2026-03-20','2026-01-01','2026-01-31',  740.00,  740.00,'valide'),
-- FÉVRIER 2026
(1,'GEN-2026-02-0051','2026-04-07','2026-04-08','2026-02-01','2026-02-28', 5340.00, 5340.00,'valide'),
(2,'SUR-26-FEV-015',  '2026-03-15','2026-03-15','2026-02-01','2026-02-28', 3260.00, 3260.00,'valide'),
(3,'APR-FEV26-03',    '2026-04-12','2026-04-13','2026-02-01','2026-02-28', 1850.00, 1850.00,'valide'),
(4,'GAN-2602-011',    '2026-05-05',NULL,         '2026-02-01','2026-02-28', 1140.00, NULL,   'recu'),
(5,'API-2602-007',    '2026-04-18','2026-04-18','2026-02-01','2026-02-28',  760.00,  760.00,'rapproche'),
-- MARS 2026 — partiellement reçus
(1,'GEN-2026-03-0089','2026-05-06',NULL,         '2026-03-01','2026-03-31', 5580.00, NULL,   'recu'),
(2,'SUR-26-MAR-022',  '2026-04-15',NULL,         '2026-03-01','2026-03-31', 3380.00, NULL,   'recu');

-- Encaissements 2026
INSERT INTO `aec_compta_encaissements`
    (`bordereau_id`, `date_encaissement`, `montant`, `mode`, `reference_bancaire`, `exercice_id`, `mois`)
VALUES
(61,'2026-03-08',5180.00,'virement','VIR-GEN-20260308',2,3),
(62,'2026-02-17',3120.00,'virement','VIR-SUR-20260217',2,2),
(63,'2026-03-14',1780.00,'virement','VIR-APR-20260314',2,3),
(65,'2026-03-22', 740.00,'virement','VIR-API-20260322',2,3),
(66,'2026-04-10',5340.00,'virement','VIR-GEN-20260410',2,4),
(67,'2026-03-18',3260.00,'virement','VIR-SUR-20260318',2,3),
(68,'2026-04-15',1850.00,'virement','VIR-APR-20260415',2,4);

-- Charges 2026
INSERT INTO `aec_compta_charges`
    (`exercice_id`, `categorie`, `libelle`, `montant`, `frequence`, `date_echeance`, `recurrente`)
VALUES
(2,'loyer',                      'Loyer bureau Villenave-d''Ornon',            180.00,'mensuelle',    NULL,        1),
(2,'assurance_rc',               'RC Pro courtier — Allianz',                 1240.00,'annuelle',    '2026-04-01',1),
(2,'orias',                      'Cotisation ORIAS 2026',                       65.00,'annuelle',    '2026-02-01',1),
(2,'cotisations',                'Cotisation ANACOFI-CIF 2026',                450.00,'annuelle',    '2026-01-15',1),
(2,'cotisations',                'Cotisation APAC courtiers 2026',             280.00,'annuelle',    '2026-03-01',1),
(2,'honoraires_expert_comptable','Honoraires Fiducial — forfait mensuel',      320.00,'mensuelle',    NULL,        1),
(2,'informatique',               'Abonnement CRM AEC CONSEILS',               480.00,'annuelle',    '2026-01-01',1),
(2,'informatique',               'Suite Microsoft 365 Business',              144.00,'annuelle',    '2026-06-01',1),
(2,'telephone',                  'Forfait téléphonique pro',                    38.00,'mensuelle',    NULL,        1),
(2,'marketing',                  'Site internet + SEO',                       150.00,'trimestrielle','2026-04-01',1),
(2,'formation',                  'Formation DDA 15h — e-learning 2026',       290.00,'annuelle',    '2026-09-01',1),
(2,'informatique',               'Remplacement imprimante bureau',            380.00,'ponctuelle',  '2026-02-15',0),
(2,'marketing',                  'Refonte plaquette commerciale',             650.00,'ponctuelle',  '2026-03-01',0);

-- Prévisions CA 2026 (historique 2025 +10%)
INSERT INTO `aec_compta_previsions_ca`
    (`exercice_id`, `mois`, `source`, `compagnie_id`, `montant_prevu`, `probabilite`, `notes`)
VALUES
(2, 1,'historique',NULL,11900.00,100,'Base janvier 2025 +10%'),
(2, 2,'historique',NULL,11380.00,100,'Base février 2025 +10%'),
(2, 3,'historique',NULL,14960.00,100,'Base mars 2025 +10%'),
(2, 4,'historique',NULL,11550.00, 90,'Estimation Q2'),
(2, 5,'historique',NULL,12100.00, 90,'Estimation Q2'),
(2, 6,'historique',NULL,12650.00, 85,'Estimation Q2'),
(2, 7,'historique',NULL, 9790.00, 80,'Creux estival — prudent'),
(2, 8,'historique',NULL, 9240.00, 80,'Creux estival — prudent'),
(2, 9,'historique',NULL,14630.00, 85,'Reprise rentrée'),
(2,10,'historique',NULL,15950.00, 80,'Objectif T4 ambitieux'),
(2,11,'historique',NULL,14520.00, 75,'Objectif T4'),
(2,12,'historique',NULL,13090.00, 75,'Fin d''année');
