
CREATE TABLE competitions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category VARCHAR(150) NULL,
    year INT NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Brouillon',
    start_date DATE NULL,
    end_date DATE NULL,
    budget DECIMAL(18,2) DEFAULT 0,
    description TEXT NULL,
    target_group VARCHAR(150) NULL,
    max_winners INT DEFAULT 0,
    min_amount DECIMAL(18,2) DEFAULT 0,
    max_amount DECIMAL(18,2) DEFAULT 0,
    created_at DATETIME NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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,
    INDEX idx_candidates_email (email),
    INDEX idx_candidates_province (province)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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) DEFAULT 0,
    admin_status VARCHAR(50) NOT NULL DEFAULT 'soumis',
    final_status VARCHAR(50) NULL,
    admin_comment TEXT NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_app_comp FOREIGN KEY (competition_id) REFERENCES competitions(id) ON DELETE CASCADE,
    CONSTRAINT fk_app_candidate FOREIGN KEY (candidate_id) REFERENCES candidates(id) ON DELETE CASCADE,
    INDEX idx_app_competition (competition_id),
    INDEX idx_app_admin_status (admin_status),
    INDEX idx_app_final_status (final_status),
    INDEX idx_app_project_title (project_title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE application_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    doc_type VARCHAR(80) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    mime_type VARCHAR(150) NOT NULL,
    file_size INT NOT NULL DEFAULT 0,
    created_at DATETIME NULL,
    CONSTRAINT fk_doc_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE evaluations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    evaluator_name VARCHAR(150) NOT NULL,
    innovation DECIMAL(5,2) DEFAULT 0,
    feasibility DECIMAL(5,2) DEFAULT 0,
    financial DECIMAL(5,2) DEFAULT 0,
    impact_score DECIMAL(5,2) DEFAULT 0,
    team_score DECIMAL(5,2) DEFAULT 0,
    total_score DECIMAL(6,2) DEFAULT 0,
    comment TEXT NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_eval_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
    INDEX idx_eval_app (application_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE jury_deliberations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    jury_president VARCHAR(150) NULL,
    decision VARCHAR(50) NOT NULL,
    rank_position INT NULL,
    validated_score DECIMAL(6,2) DEFAULT 0,
    notes TEXT NULL,
    deliberation_date DATE NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_jury_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE official_results (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    published_status VARCHAR(50) NOT NULL,
    reference_pv VARCHAR(150) NULL,
    publication_note TEXT NULL,
    publication_date DATE NULL,
    published_by VARCHAR(150) NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_result_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE,
    INDEX idx_result_status (published_status),
    INDEX idx_result_date (publication_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE contracts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    contract_reference VARCHAR(150) NOT NULL,
    contract_status VARCHAR(50) NOT NULL DEFAULT 'en_preparation',
    signed_at DATE NULL,
    approved_amount DECIMAL(18,2) DEFAULT 0,
    end_date DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_contract_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE disbursements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contract_id INT NOT NULL,
    disbursement_label VARCHAR(150) NOT NULL,
    amount DECIMAL(18,2) DEFAULT 0,
    planned_date DATE NULL,
    paid_date DATE NULL,
    disbursement_status VARCHAR(50) NOT NULL DEFAULT 'prevu',
    comment TEXT NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_disb_contract FOREIGN KEY (contract_id) REFERENCES contracts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE monitoring_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    report_date DATE NULL,
    project_status VARCHAR(50) NOT NULL DEFAULT 'en_cours',
    jobs_created INT DEFAULT 0,
    turnover_amount DECIMAL(18,2) DEFAULT 0,
    execution_rate DECIMAL(5,2) DEFAULT 0,
    achievements TEXT NULL,
    challenges TEXT NULL,
    recommendations TEXT NULL,
    created_at DATETIME NULL,
    CONSTRAINT fk_monitor_app FOREIGN KEY (application_id) REFERENCES applications(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE email_notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    recipient_email VARCHAR(180) NOT NULL,
    subject_line VARCHAR(255) NOT NULL,
    message_body TEXT NULL,
    context_label VARCHAR(120) NULL,
    send_status VARCHAR(50) NOT NULL DEFAULT 'pending',
    error_message TEXT NULL,
    created_at DATETIME NULL,
    INDEX idx_email_status (send_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_name VARCHAR(150) NULL,
    user_role VARCHAR(50) NULL,
    action VARCHAR(150) NOT NULL,
    module VARCHAR(100) NOT NULL,
    reference_value VARCHAR(150) NULL,
    details TEXT NULL,
    ip_address VARCHAR(50) NULL,
    created_at DATETIME NULL,
    INDEX idx_audit_module (module),
    INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(120) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO competitions (title, category, year, status, start_date, end_date, budget, description, target_group, max_winners, min_amount, max_amount, created_at, updated_at) VALUES
('Concours national des plans d''affaires innovants', 'Jeunes entrepreneurs', 2026, 'Publié', '2026-03-01', '2026-04-15', 500000000, 'Appel national pour projets à fort potentiel.', 'Jeunes porteurs de projets', 25, 5000000, 50000000, NOW(), NOW()),
('Concours femmes entrepreneures PRETE', 'Femmes', 2026, 'Publié', '2026-04-20', '2026-05-30', 350000000, 'Appui aux projets portés par des femmes entrepreneures.', 'Femmes entrepreneures', 20, 5000000, 45000000, NOW(), NOW());

INSERT INTO candidates (full_name, email, phone, gender, province, commune, created_at) VALUES
('Aline Nduwimana', 'aline@example.com', '+25779000001', 'Femme', 'Bujumbura Mairie', 'Mukaza', NOW()),
('Jean Bosco Niyonkuru', 'jean@example.com', '+25779000002', 'Homme', 'Gitega', 'Gitega', NOW()),
('Grace Uwimana', 'grace@example.com', '+25779000003', 'Femme', 'Ngozi', 'Ngozi', NOW());

INSERT INTO applications (competition_id, candidate_id, reference, project_title, sector, summary, requested_amount, admin_status, final_status, admin_comment, created_at) VALUES
(1, 1, 'PRETE-2026-001', 'Agro-transformation intelligente', 'Agro-industrie', 'Projet de transformation locale de fruits avec chaîne de froid intelligente et distribution digitale.', 45000000, 'eligible', 'laureat', 'Dossier complet et conforme.', NOW()),
(1, 2, 'PRETE-2026-002', 'Plateforme logistique rurale', 'Numérique', 'Solution de mutualisation des transports pour producteurs ruraux.', 38000000, 'en_evaluation', 'liste_attente', 'Compléments financiers demandés.', NOW()),
(1, 3, 'PRETE-2026-003', 'Élevage moderne et alimentation durable', 'Élevage', 'Projet d''élevage productif avec valorisation des déchets organiques.', 52000000, 'soumis', NULL, NULL, NOW());

INSERT INTO application_documents (application_id, doc_type, original_name, stored_name, mime_type, file_size, created_at) VALUES
(1, 'projet_principal', 'business_plan_aline.pdf', 'sample_aline.pdf', 'application/pdf', 204800, NOW()),
(2, 'projet_principal', 'plateforme_logistique.docx', 'sample_jean.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document', 102400, NOW());

INSERT INTO evaluations (application_id, evaluator_name, innovation, feasibility, financial, impact_score, team_score, total_score, comment, created_at) VALUES
(1, 'Évaluateur Technique', 18, 17, 16, 19, 18, 88, 'Projet mature, solide et aligné avec les objectifs PRETE.', NOW()),
(1, 'Administrateur PRETE', 17, 18, 16, 18, 17, 86, 'Forte capacité d''exécution et bon ancrage marché.', NOW()),
(2, 'Évaluateur Technique', 16, 15, 14, 17, 16, 78, 'Projet prometteur nécessitant un cadrage financier plus précis.', NOW());

INSERT INTO jury_deliberations (application_id, jury_president, decision, rank_position, validated_score, notes, deliberation_date, created_at) VALUES
(1, 'Mme la Présidente du Jury', 'laureat', 1, 87.00, 'Dossier validé à l''unanimité.', '2026-04-25', NOW()),
(2, 'Mme la Présidente du Jury', 'liste_attente', 2, 78.00, 'À retenir en liste d''attente sous réserve.', '2026-04-25', NOW());

INSERT INTO official_results (application_id, published_status, reference_pv, publication_note, publication_date, published_by, created_at) VALUES
(1, 'laureat', 'PV-JURY-2026-001', 'Projet retenu pour financement.', '2026-04-28', 'Coordonnatrice PRETE', NOW()),
(2, 'liste_attente', 'PV-JURY-2026-001', 'Projet classé sur liste d''attente.', '2026-04-28', 'Coordonnatrice PRETE', NOW());

INSERT INTO contracts (application_id, contract_reference, contract_status, signed_at, approved_amount, end_date, notes, created_at) VALUES
(1, 'CONV-PRETE-2026-001', 'signe', '2026-05-05', 45000000, '2026-12-31', 'Convention signée et valide.', NOW());

INSERT INTO disbursements (contract_id, disbursement_label, amount, planned_date, paid_date, disbursement_status, comment, created_at) VALUES
(1, 'Tranche 1', 25000000, '2026-05-10', '2026-05-10', 'verse', 'Décaissement initial effectué.', NOW()),
(1, 'Tranche 2', 20000000, '2026-08-15', NULL, 'prevu', 'Décaissement conditionné au rapport intermédiaire.', NOW());

INSERT INTO monitoring_reports (application_id, report_date, project_status, jobs_created, turnover_amount, execution_rate, achievements, challenges, recommendations, created_at) VALUES
(1, '2026-06-30', 'en_cours', 8, 12000000, 52.5, 'Installation de l''unité pilote et signature de premiers contrats commerciaux.', 'Besoin d''assistance technique sur la chaîne de froid.', 'Renforcer l''accompagnement technique et commercial.', NOW());

INSERT INTO audit_logs (user_name, user_role, action, module, reference_value, details, ip_address, created_at) VALUES
('Système', 'admin', 'Initialisation', 'database', 'schema.sql', 'Base V5 importée avec succès.', '127.0.0.1', NOW());

INSERT INTO email_notifications (recipient_email, subject_line, message_body, context_label, send_status, error_message, created_at) VALUES
('aline@example.com', 'Résultat PRETE', 'Votre résultat officiel a été publié.', 'publication_resultat', 'pending', '', NOW());

INSERT INTO app_settings (setting_key, setting_value, updated_at) VALUES
('institution_name', 'Projet PRETE Burundi', NOW()),
('mail_transport', 'mail', NOW()),
('mail_from', 'noreply@prete.bi', NOW()),
('smtp_host', '', NOW()),
('smtp_port', '587', NOW()),
('smtp_username', '', NOW()),
('smtp_password', '', NOW()),
('smtp_encryption', 'tls', NOW());
