CREATE TABLE candidates (id INT AUTO_INCREMENT PRIMARY KEY,full_name VARCHAR(180) NOT NULL,email VARCHAR(150) NOT NULL,phone VARCHAR(50) NULL,gender VARCHAR(20) NULL,province VARCHAR(100) NULL,commune VARCHAR(100) NULL,created_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE competitions (id INT AUTO_INCREMENT PRIMARY KEY,code VARCHAR(50) NOT NULL UNIQUE,title VARCHAR(255) NOT NULL,category VARCHAR(120) NULL,year INT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'draft',start_date DATE NULL,end_date DATE NULL,budget DECIMAL(18,2) NOT NULL DEFAULT 0,description TEXT NULL,created_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE applications (id INT AUTO_INCREMENT PRIMARY KEY,competition_id INT NOT NULL,candidate_id INT NOT NULL,reference VARCHAR(80) NOT NULL UNIQUE,project_title VARCHAR(255) NOT NULL,sector VARCHAR(120) NULL,summary TEXT NULL,requested_amount DECIMAL(18,2) NOT NULL DEFAULT 0,admin_status VARCHAR(30) NOT NULL DEFAULT 'soumis',final_status VARCHAR(30) NULL,validated_score DECIMAL(6,2) NULL,created_at DATETIME NULL,CONSTRAINT fk_app_comp FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE,CONSTRAINT fk_app_cand FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE application_files (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,original_name VARCHAR(255) NOT NULL,file_name VARCHAR(255) NOT NULL,mime_type VARCHAR(120) NULL,file_size INT NOT NULL DEFAULT 0,created_at DATETIME NULL,CONSTRAINT fk_file_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE evaluations (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,evaluator_name VARCHAR(150) NOT NULL,innovation DECIMAL(5,2) NOT NULL DEFAULT 0,feasibility DECIMAL(5,2) NOT NULL DEFAULT 0,financial DECIMAL(5,2) NOT NULL DEFAULT 0,impact_score DECIMAL(5,2) NOT NULL DEFAULT 0,team_score DECIMAL(5,2) NOT NULL DEFAULT 0,pitch_score DECIMAL(5,2) NOT NULL DEFAULT 0,total_score DECIMAL(6,2) NOT NULL DEFAULT 0,comment TEXT NULL,created_at DATETIME NULL,CONSTRAINT fk_eval_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE jury_decisions (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,decision VARCHAR(30) NOT NULL,ranking INT NULL,validated_score DECIMAL(6,2) NULL,pv_reference VARCHAR(80) NULL,notes TEXT NULL,created_at DATETIME NULL,CONSTRAINT fk_jury_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE results_publications (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,publication_date DATE NOT NULL,status_label VARCHAR(100) NOT NULL,public_note TEXT NULL,created_at DATETIME NULL,CONSTRAINT fk_res_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE contracts (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,contract_reference VARCHAR(80) NOT NULL,signed_date DATE NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',amount_approved DECIMAL(18,2) NOT NULL DEFAULT 0,created_at DATETIME NULL,CONSTRAINT fk_contract_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE disbursements (id INT AUTO_INCREMENT PRIMARY KEY,contract_id INT NOT NULL,tranche_label VARCHAR(80) NOT NULL,amount DECIMAL(18,2) NOT NULL DEFAULT 0,planned_date DATE NULL,paid_date DATE NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',created_at DATETIME NULL,CONSTRAINT fk_disb_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE followups (id INT AUTO_INCREMENT PRIMARY KEY,application_id INT NOT NULL,progress_percent INT NOT NULL DEFAULT 0,jobs_created INT NOT NULL DEFAULT 0,revenue_amount DECIMAL(18,2) NOT NULL DEFAULT 0,remarks TEXT NULL,created_at DATETIME NULL,CONSTRAINT fk_follow_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE notifications (id INT AUTO_INCREMENT PRIMARY KEY,recipient_email VARCHAR(150) NOT NULL,subject_line VARCHAR(255) NOT NULL,message_body TEXT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'logged',created_at DATETIME NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE audit_logs (id INT AUTO_INCREMENT PRIMARY KEY,user_id INT NULL,module_name VARCHAR(80) NOT NULL,action_name VARCHAR(80) NOT NULL,reference VARCHAR(120) NOT NULL,details TEXT NULL,ip_address VARCHAR(64) NULL,created_at DATETIME NULL,INDEX idx_audit_created_at (created_at)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO candidates (full_name,email,phone,gender,province,commune,created_at) VALUES
('Aline Nduwimana','candidat1@example.com','+25779100000','Femme','Bujumbura Mairie','Bujumbura',NOW()),
('Jean Bosco Niyonkuru','candidat2@example.com','+25779100001','Homme','Gitega','Gitega',NOW()),
('Grace Uwimana','candidat3@example.com','+25779100002','Femme','Ngozi','Ngozi',NOW()),
('Didier Hakizimana','candidat4@example.com','+25779100003','Homme','Muyinga','Muyinga',NOW()),
('Claudine Ndayishimiye','candidat5@example.com','+25779100004','Femme','Rumonge','Rumonge',NOW()),
('Patrick Ntahobari','candidat6@example.com','+25779100005','Homme','Kayanza','Kayanza',NOW()),
('Olive Niyonsaba','candidat7@example.com','+25779100006','Femme','Bururi','Bururi',NOW()),
('Fabrice Nsabimana','candidat8@example.com','+25779100007','Homme','Kirundo','Kirundo',NOW()),
('Anitha Bizimana','candidat9@example.com','+25779100008','Femme','Muramvya','Muramvya',NOW()),
('Eric Nkurunziza','candidat10@example.com','+25779100009','Homme','Makamba','Makamba',NOW()),
('Nadine Niragira','candidat11@example.com','+25779100010','Femme','Bujumbura Mairie','Bujumbura',NOW()),
('Yves Hakizayezu','candidat12@example.com','+25779100011','Homme','Gitega','Gitega',NOW()),
('Josiane Nduwimana','candidat13@example.com','+25779100012','Femme','Ngozi','Ngozi',NOW()),
('Pascal Niyonkuru','candidat14@example.com','+25779100013','Homme','Muyinga','Muyinga',NOW()),
('Diane Uwimana','candidat15@example.com','+25779100014','Femme','Rumonge','Rumonge',NOW()),
('Thierry Hakizimana','candidat16@example.com','+25779100015','Homme','Kayanza','Kayanza',NOW()),
('Liliane Ndayishimiye','candidat17@example.com','+25779100016','Femme','Bururi','Bururi',NOW()),
('Claude Ntahobari','candidat18@example.com','+25779100017','Homme','Kirundo','Kirundo',NOW()),
('Sandrine Niyonsaba','candidat19@example.com','+25779100018','Femme','Muramvya','Muramvya',NOW()),
('Emmanuel Nsabimana','candidat20@example.com','+25779100019','Homme','Makamba','Makamba',NOW()),
('Sonia Bizimana','candidat21@example.com','+25779100020','Femme','Bujumbura Mairie','Bujumbura',NOW()),
('Aristide Nkurunziza','candidat22@example.com','+25779100021','Homme','Gitega','Gitega',NOW());
INSERT INTO competitions (code,title,category,year,status,start_date,end_date,budget,description,created_at) VALUES ('PRETE-2026-A','Concours national des plans d''affaires innovants','Jeunes entrepreneurs',2026,'published','2026-03-01','2026-04-30',500000000,'Appel national pour projets innovants à fort impact.',NOW()),('PRETE-2026-F','Concours femmes entrepreneures PRETE','Femmes',2026,'published','2026-04-20','2026-05-30',350000000,'Compétition dédiée aux femmes entrepreneures.',NOW()),('PRETE-2026-G','Concours innovation verte PRETE','Innovation verte',2026,'published','2026-06-01','2026-07-15',275000000,'Compétition sur les projets à impact environnemental positif.',NOW());
INSERT INTO applications (competition_id,candidate_id,reference,project_title,sector,summary,requested_amount,admin_status,final_status,validated_score,created_at) VALUES
(1,1,'PRETE-2026-001','Agro-transformation intelligente','Agro-industrie','Projet démonstratif pour le concours PRETE NYUNGANIRA.',25000000,'eligible','winner',88,NOW()),
(1,2,'PRETE-2026-002','Plateforme logistique rurale','Numérique','Projet démonstratif pour le concours PRETE NYUNGANIRA.',26700000,'eligible','waiting_list',85,NOW()),
(1,3,'PRETE-2026-003','Élevage moderne et alimentation durable','Élevage','Projet démonstratif pour le concours PRETE NYUNGANIRA.',28400000,'evaluation',NULL,NULL,NOW()),
(1,4,'PRETE-2026-004','Atelier textile communautaire','Services','Projet démonstratif pour le concours PRETE NYUNGANIRA.',30100000,'eligible','winner',79,NOW()),
(1,5,'PRETE-2026-005','Mini-réseau solaire villageois','Énergie','Projet démonstratif pour le concours PRETE NYUNGANIRA.',31800000,'eligible',NULL,NULL,NOW()),
(1,6,'PRETE-2026-006','Production de semences améliorées','Agriculture','Projet démonstratif pour le concours PRETE NYUNGANIRA.',33500000,'eligible','waiting_list',88,NOW()),
(1,7,'PRETE-2026-007','Application de commercialisation agricole','Logistique','Projet démonstratif pour le concours PRETE NYUNGANIRA.',35200000,'soumis',NULL,NULL,NOW()),
(1,8,'PRETE-2026-008','Ferme piscicole intégrée','Pêche','Projet démonstratif pour le concours PRETE NYUNGANIRA.',36900000,'eligible','winner',82,NOW()),
(1,9,'PRETE-2026-009','Unité de transformation du maïs','Transformation','Projet démonstratif pour le concours PRETE NYUNGANIRA.',38600000,'evaluation',NULL,NULL,NOW()),
(1,10,'PRETE-2026-010','Eco-tourisme collinaire','Tourisme','Projet démonstratif pour le concours PRETE NYUNGANIRA.',40300000,'eligible','waiting_list',76,NOW()),
(1,11,'PRETE-2026-011','Recyclage plastique local','Agro-industrie','Projet démonstratif pour le concours PRETE NYUNGANIRA.',42000000,'eligible',NULL,NULL,NOW()),
(1,12,'PRETE-2026-012','Chaîne de froid laitière','Numérique','Projet démonstratif pour le concours PRETE NYUNGANIRA.',43700000,'evaluation',NULL,NULL,NOW()),
(2,13,'PRETE-2026-013','Solution fintech agricole','Élevage','Projet démonstratif pour le concours PRETE NYUNGANIRA.',45400000,'eligible','winner',82,NOW()),
(2,14,'PRETE-2026-014','Centre de services mécanisés','Services','Projet démonstratif pour le concours PRETE NYUNGANIRA.',47100000,'eligible',NULL,NULL,NOW()),
(2,15,'PRETE-2026-015','Unité de séchage fruits','Énergie','Projet démonstratif pour le concours PRETE NYUNGANIRA.',48800000,'evaluation',NULL,NULL,NOW()),
(2,16,'PRETE-2026-016','Briqueterie écologique','Agriculture','Projet démonstratif pour le concours PRETE NYUNGANIRA.',50500000,'eligible','waiting_list',88,NOW()),
(2,17,'PRETE-2026-017','Distribution d’intrants connectée','Logistique','Projet démonstratif pour le concours PRETE NYUNGANIRA.',52200000,'eligible',NULL,NULL,NOW()),
(2,18,'PRETE-2026-018','Culture sous serre','Pêche','Projet démonstratif pour le concours PRETE NYUNGANIRA.',53900000,'evaluation',NULL,NULL,NOW()),
(3,19,'PRETE-2026-019','Transformation du manioc','Transformation','Projet démonstratif pour le concours PRETE NYUNGANIRA.',55600000,'soumis',NULL,NULL,NOW()),
(3,20,'PRETE-2026-020','Transport frigorifique','Tourisme','Projet démonstratif pour le concours PRETE NYUNGANIRA.',57300000,'eligible',NULL,NULL,NOW()),
(3,21,'PRETE-2026-021','Miel premium du Burundi','Agro-industrie','Projet démonstratif pour le concours PRETE NYUNGANIRA.',59000000,'evaluation',NULL,NULL,NOW()),
(3,22,'PRETE-2026-022','Marketplace artisanale','Numérique','Projet démonstratif pour le concours PRETE NYUNGANIRA.',60700000,'soumis',NULL,NULL,NOW());
INSERT INTO evaluations (application_id,evaluator_name,innovation,feasibility,financial,impact_score,team_score,pitch_score,total_score,comment,created_at) VALUES
(1,'Évaluateur Technique',13,14,12,15,14,13,68,'Évaluation de démonstration pour statistiques.',NOW()),
(2,'Évaluateur Technique',14,15,13,16,15,14,71,'Évaluation de démonstration pour statistiques.',NOW()),
(3,'Évaluateur Technique',15,16,14,17,16,15,74,'Évaluation de démonstration pour statistiques.',NOW()),
(4,'Évaluateur Technique',16,17,15,18,17,16,77,'Évaluation de démonstration pour statistiques.',NOW()),
(5,'Évaluateur Technique',17,18,11,19,13,17,80,'Évaluation de démonstration pour statistiques.',NOW()),
(6,'Évaluateur Technique',18,13,12,14,14,12,83,'Évaluation de démonstration pour statistiques.',NOW()),
(7,'Évaluateur Technique',12,14,13,15,15,13,86,'Évaluation de démonstration pour statistiques.',NOW()),
(8,'Évaluateur Technique',13,15,14,16,16,14,65,'Évaluation de démonstration pour statistiques.',NOW()),
(9,'Évaluateur Technique',14,16,15,17,17,15,68,'Évaluation de démonstration pour statistiques.',NOW()),
(10,'Évaluateur Technique',15,17,11,18,13,16,71,'Évaluation de démonstration pour statistiques.',NOW()),
(11,'Évaluateur Technique',16,18,12,19,14,17,74,'Évaluation de démonstration pour statistiques.',NOW()),
(12,'Évaluateur Technique',17,13,13,14,15,12,77,'Évaluation de démonstration pour statistiques.',NOW()),
(13,'Évaluateur Technique',18,14,14,15,16,13,80,'Évaluation de démonstration pour statistiques.',NOW()),
(14,'Évaluateur Technique',12,15,15,16,17,14,83,'Évaluation de démonstration pour statistiques.',NOW()),
(15,'Évaluateur Technique',13,16,11,17,13,15,86,'Évaluation de démonstration pour statistiques.',NOW());
INSERT INTO jury_decisions (application_id,decision,ranking,validated_score,pv_reference,notes,created_at) VALUES
(1,'winner',1,87,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(2,'waiting_list',2,85,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(4,'winner',3,83,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(6,'waiting_list',4,81,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(8,'winner',5,79,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(10,'waiting_list',6,77,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(13,'winner',7,75,'PV-JURY-2026-001','Décision consolidée du jury.',NOW()),
(16,'waiting_list',8,73,'PV-JURY-2026-001','Décision consolidée du jury.',NOW());
INSERT INTO results_publications (application_id,publication_date,status_label,public_note,created_at) VALUES
(1,CURDATE(),'Lauréat','Publication officielle PRETE.',NOW()),
(2,CURDATE(),'Liste d'attente','Publication officielle PRETE.',NOW()),
(4,CURDATE(),'Lauréat','Publication officielle PRETE.',NOW()),
(6,CURDATE(),'Liste d'attente','Publication officielle PRETE.',NOW()),
(8,CURDATE(),'Lauréat','Publication officielle PRETE.',NOW()),
(10,CURDATE(),'Liste d'attente','Publication officielle PRETE.',NOW()),
(13,CURDATE(),'Lauréat','Publication officielle PRETE.',NOW()),
(16,CURDATE(),'Liste d'attente','Publication officielle PRETE.',NOW());
INSERT INTO contracts (application_id,contract_reference,signed_date,status,amount_approved,created_at) VALUES
(1,'CTR-PRETE-001','2026-05-11','signed',31100000,NOW()),
(4,'CTR-PRETE-004','2026-05-13','signed',34400000,NOW()),
(8,'CTR-PRETE-008','2026-05-15','signed',38800000,NOW()),
(13,'CTR-PRETE-013','2026-05-17','signed',44300000,NOW());
INSERT INTO disbursements (contract_id,tranche_label,amount,planned_date,paid_date,status,created_at) VALUES
(1,'Tranche 1',15550000.00,'2026-06-11','2026-06-11','paid',NOW()),
(1,'Tranche 2',15550000.00,'2026-09-11',NULL,'pending',NOW()),
(2,'Tranche 1',17200000.00,'2026-06-13','2026-06-13','paid',NOW()),
(2,'Tranche 2',17200000.00,'2026-09-13',NULL,'pending',NOW()),
(3,'Tranche 1',19400000.00,'2026-06-15','2026-06-15','paid',NOW()),
(3,'Tranche 2',19400000.00,'2026-09-15',NULL,'pending',NOW()),
(4,'Tranche 1',22150000.00,'2026-06-17','2026-06-17','paid',NOW()),
(4,'Tranche 2',22150000.00,'2026-09-17',NULL,'pending',NOW());
INSERT INTO followups (application_id,progress_percent,jobs_created,revenue_amount,remarks,created_at) VALUES
(1,45,3,6500000,'Suivi de démonstration du projet financé.',NOW()),
(4,55,5,9500000,'Suivi de démonstration du projet financé.',NOW()),
(8,65,7,12500000,'Suivi de démonstration du projet financé.',NOW()),
(13,75,9,15500000,'Suivi de démonstration du projet financé.',NOW());
INSERT INTO audit_logs (user_id,module_name,action_name,reference,details,ip_address,created_at) VALUES (1,'system','seed','INIT','Initialisation enrichie de la base de démonstration avec plus de vingt dossiers.','127.0.0.1',NOW());
INSERT INTO notifications (recipient_email,subject_line,message_body,status,created_at) VALUES
('admin@prete.bi','Alerte nouveaux comptes candidats','3 comptes candidats sont en attente d’approbation.','logged',NOW()),
('coordo@prete.bi','Synthèse résultats COPA','Les résultats de la première vague ont été consolidés.','logged',NOW()),
('finance@prete.bi','Décaissements à planifier','Deux conventions signées attendent la première tranche.','logged',NOW());
