-- ==========================================================
-- EVAL PRO PRETE NYUNGANIRA - SQL V4.5.2.2 PROPRE
-- Import phpMyAdmin / cPanel
-- Base cible : à sélectionner manuellement dans phpMyAdmin
-- Date génération : 2026-04-28 10:29:40
-- ==========================================================

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
SET NAMES utf8mb4;

-- IMPORTANT :
-- Cette instruction sélectionne automatiquement la base cible.
-- La base doit déjà exister dans cPanel/phpMyAdmin.
SET FOREIGN_KEY_CHECKS=0;

-- EVAL PRO PRETE NYUNGANIRA - SQL V4.5.2.2 COMPATIBLE PHPMYADMIN/CPANEL
-- IMPORTANT : sélectionnez d’abord la base rele8100_ERP-VALPRO dans phpMyAdmin, puis importez ce fichier.
-- Ce script ne contient ni CREATE DATABASE, ni USE, ni LONGTEXT, ni contraintes FK complexes.
-- Il est prévu pour éviter les erreurs d'import sur MariaDB/MySQL mutualisé.

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET NAMES utf8mb4;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS backups;
DROP TABLE IF EXISTS messages;
DROP TABLE IF EXISTS semantic_analyses;
DROP TABLE IF EXISTS procedure_minutes;
DROP TABLE IF EXISTS evaluation_summaries;
DROP TABLE IF EXISTS evaluations;
DROP TABLE IF EXISTS bidder_compliance;
DROP TABLE IF EXISTS criteria;
DROP TABLE IF EXISTS bidder_documents;
DROP TABLE IF EXISTS bidders;
DROP TABLE IF EXISTS procedure_members;
DROP TABLE IF EXISTS procedures;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    full_name VARCHAR(180) NOT NULL,
    username VARCHAR(80) NOT NULL,
    email VARCHAR(180) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(40) NOT NULL DEFAULT 'evaluator',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_users_username (username),
    UNIQUE KEY uq_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE procedures (
    id INT NOT NULL AUTO_INCREMENT,
    reference_no VARCHAR(120) NOT NULL,
    type_code VARCHAR(120) NOT NULL,
    title VARCHAR(255) NOT NULL,
    nature_submission VARCHAR(120) DEFAULT NULL,
    selection_method VARCHAR(120) NOT NULL,
    financier VARCHAR(180) DEFAULT NULL,
    evaluation_grid VARCHAR(255) DEFAULT NULL,
    tor_summary LONGTEXT DEFAULT NULL,
    planned_bidders INT NOT NULL DEFAULT 0,
    submission_deadline DATE DEFAULT NULL,
    evaluation_date DATE DEFAULT NULL,
    meeting_time TIME DEFAULT NULL,
    start_date DATE DEFAULT NULL,
    end_date DATE DEFAULT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'programmed',
    created_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_procedures_reference (reference_no),
    KEY idx_procedures_status (status),
    KEY idx_procedures_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE procedure_members (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    user_id INT NOT NULL,
    member_role VARCHAR(40) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_member (procedure_id, user_id, member_role),
    KEY idx_member_procedure (procedure_id),
    KEY idx_member_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bidders (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    bidder_name VARCHAR(255) NOT NULL,
    submission_date DATE DEFAULT NULL,
    technical_submission_date DATE DEFAULT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'received',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_bidders_procedure (procedure_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bidder_documents (
    id INT NOT NULL AUTO_INCREMENT,
    bidder_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(120) DEFAULT NULL,
    uploaded_by INT DEFAULT NULL,
    uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_doc_bidder (bidder_id),
    KEY idx_doc_user (uploaded_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE criteria (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    category VARCHAR(40) NOT NULL,
    label VARCHAR(255) NOT NULL,
    weight DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    max_score DECIMAL(8,2) NOT NULL DEFAULT 100.00,
    is_eliminatory TINYINT(1) NOT NULL DEFAULT 0,
    display_order INT NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_criteria_procedure (procedure_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bidder_compliance (
    id INT NOT NULL AUTO_INCREMENT,
    bidder_id INT NOT NULL,
    criterion_id INT NOT NULL,
    is_compliant TINYINT(1) NOT NULL DEFAULT 0,
    remarks TEXT DEFAULT NULL,
    checked_by INT DEFAULT NULL,
    checked_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_bidder_compliance (bidder_id, criterion_id),
    KEY idx_bc_criterion (criterion_id),
    KEY idx_bc_user (checked_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE evaluations (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    bidder_id INT NOT NULL,
    criterion_id INT NOT NULL,
    evaluator_id INT NOT NULL,
    score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    comments TEXT DEFAULT NULL,
    status VARCHAR(40) NOT NULL DEFAULT 'draft',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_eval (procedure_id, bidder_id, criterion_id, evaluator_id),
    KEY idx_eval_procedure (procedure_id),
    KEY idx_eval_bidder (bidder_id),
    KEY idx_eval_criterion (criterion_id),
    KEY idx_eval_user (evaluator_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE evaluation_summaries (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    bidder_id INT NOT NULL,
    technical_score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    financial_score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    combined_score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    rank_no INT DEFAULT NULL,
    recommendation VARCHAR(120) DEFAULT NULL,
    eliminated TINYINT(1) NOT NULL DEFAULT 0,
    remarks TEXT DEFAULT NULL,
    workflow_status VARCHAR(40) NOT NULL DEFAULT 'draft',
    generated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_summary (procedure_id, bidder_id),
    KEY idx_summary_procedure (procedure_id),
    KEY idx_summary_bidder (bidder_id),
    KEY idx_summary_rank (rank_no)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE procedure_minutes (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    meeting_date DATE DEFAULT NULL,
    meeting_place VARCHAR(255) DEFAULT NULL,
    chair_name VARCHAR(180) DEFAULT NULL,
    minutes_text LONGTEXT DEFAULT NULL,
    legal_observation TEXT DEFAULT NULL,
    coordinator_observation TEXT DEFAULT NULL,
    updated_by INT DEFAULT NULL,
    updated_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_minutes_procedure (procedure_id),
    KEY idx_minutes_user (updated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE semantic_analyses (
    id INT NOT NULL AUTO_INCREMENT,
    procedure_id INT NOT NULL,
    bidder_id INT NOT NULL,
    coverage_score DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    matched_keywords_json LONGTEXT DEFAULT NULL,
    missing_keywords_json LONGTEXT DEFAULT NULL,
    strengths TEXT DEFAULT NULL,
    weaknesses TEXT DEFAULT NULL,
    gaps TEXT DEFAULT NULL,
    analysis_narrative LONGTEXT DEFAULT NULL,
    analysis_text LONGTEXT DEFAULT NULL,
    source_files_json LONGTEXT DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_semantic (procedure_id, bidder_id),
    KEY idx_semantic_procedure (procedure_id),
    KEY idx_semantic_bidder (bidder_id),
    KEY idx_semantic_user (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE messages (
    id INT NOT NULL AUTO_INCREMENT,
    sender_id INT NOT NULL,
    recipient_id INT NOT NULL,
    subject VARCHAR(255) NOT NULL,
    body LONGTEXT NOT NULL,
    channel_type VARCHAR(40) NOT NULL DEFAULT 'internal',
    sent_email_to VARCHAR(180) DEFAULT NULL,
    is_read TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    read_at DATETIME DEFAULT NULL,
    PRIMARY KEY (id),
    KEY idx_msg_sender (sender_id),
    KEY idx_msg_recipient (recipient_id),
    KEY idx_msg_read (is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE backups (
    id INT NOT NULL AUTO_INCREMENT,
    backup_date DATE NOT NULL,
    zip_path VARCHAR(255) NOT NULL,
    sql_path VARCHAR(255) NOT NULL,
    generated_by INT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_backup_date (backup_date),
    KEY idx_backup_user (generated_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE audit_logs (
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT DEFAULT NULL,
    action_label VARCHAR(255) NOT NULL,
    entity_type VARCHAR(100) NOT NULL,
    entity_id INT DEFAULT NULL,
    payload_json LONGTEXT DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_log_user (user_id),
    KEY idx_log_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO users (id, full_name, username, email, password_hash, role, is_active) VALUES
(1, 'Coordonnatrice du Projet', 'coord', 'coord@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'coordinator', 1),
(2, 'Spécialiste Passation des Marchés', 'spm', 'spm@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'spm', 1),
(3, 'Président Commission', 'president', 'president@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'president', 1),
(4, 'Évaluateur Technique 1', 'eval1', 'eval1@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'evaluator', 1),
(5, 'Rapporteur de la commission', 'rapporteur', 'rapporteur@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'rapporteur', 1),
(6, 'Conseiller juridique', 'legal', 'legal@evalpro.local', '$2y$12$4h9ZZQrKbP5ZnQiB8PJYA.Hm92g1hYPcG3WZkhCSPE./jfFlDGTEO', 'legal', 1);

INSERT INTO procedures (id, reference_no, type_code, title, nature_submission, selection_method, financier, evaluation_grid, tor_summary, planned_bidders, submission_deadline, evaluation_date, meeting_time, start_date, end_date, status, created_by) VALUES
(1, 'PRETE/NYU/AMI/001/2026', 'Manifestation d''intérêt', 'Sélection d''un cabinet pour l''audit et la stratégie de communication du projet PRETE NYUNGANIRA', 'AMI', 'SBQC', 'Banque mondiale / BAD', 'Grille AMI consultants - 100 points', 'Évaluation fondée sur la compréhension de la mission, la méthodologie, les références similaires, les experts mobilisés, la qualité de l’offre et la cohérence avec les TDR.', 3, '2026-03-20', '2026-03-28', '09:00:00', '2026-03-28', '2026-04-05', 'validated', 2);

INSERT INTO procedure_members (procedure_id, user_id, member_role) VALUES
(1, 3, 'president'),
(1, 4, 'evaluator'),
(1, 5, 'rapporteur');

INSERT INTO bidders (id, procedure_id, bidder_name, submission_date, technical_submission_date, status) VALUES
(1, 1, 'Génix International', '2026-03-18', '2026-03-18', 'under_review'),
(2, 1, 'Agence CID Mada', '2026-03-18', '2026-03-18', 'under_review'),
(3, 1, 'Intelligence Groupe', '2026-03-19', '2026-03-19', 'under_review');

INSERT INTO criteria (id, procedure_id, category, label, weight, max_score, is_eliminatory, display_order) VALUES
(1, 1, 'administratif', 'Dossier administratif complet et signé', 0.00, 100.00, 1, 1),
(2, 1, 'eligibilite', 'Éligibilité du soumissionnaire et absence d''incompatibilité', 0.00, 100.00, 1, 2),
(3, 1, 'technique', 'Compréhension des TDR et des objectifs', 25.00, 100.00, 0, 3),
(4, 1, 'technique', 'Qualité de la méthodologie proposée', 25.00, 100.00, 0, 4),
(5, 1, 'technique', 'Expériences et références similaires', 25.00, 100.00, 0, 5),
(6, 1, 'technique', 'Qualification de l’équipe clé', 15.00, 100.00, 0, 6),
(7, 1, 'financier', 'Pertinence de l’offre financière / rapport qualité-prix', 10.00, 100.00, 0, 7);

INSERT INTO bidder_compliance (bidder_id, criterion_id, is_compliant, remarks, checked_by, checked_at) VALUES
(1, 1, 1, 'Dossier administratif reçu.', 3, NOW()),
(1, 2, 1, 'Soumissionnaire éligible.', 3, NOW()),
(2, 1, 1, 'Dossier administratif reçu.', 3, NOW()),
(2, 2, 1, 'Soumissionnaire éligible.', 3, NOW()),
(3, 1, 1, 'Dossier administratif reçu.', 3, NOW()),
(3, 2, 1, 'Soumissionnaire éligible.', 3, NOW());

INSERT INTO evaluations (procedure_id, bidder_id, criterion_id, evaluator_id, score, comments, status) VALUES
(1, 1, 3, 4, 88.00, 'Bonne compréhension des TDR.', 'submitted'),
(1, 1, 4, 4, 86.00, 'Méthodologie cohérente et opérationnelle.', 'submitted'),
(1, 1, 5, 4, 90.00, 'Références solides et proches de la mission.', 'submitted'),
(1, 1, 6, 4, 84.00, 'Équipe clé qualifiée.', 'submitted'),
(1, 1, 7, 4, 78.00, 'Offre financière acceptable.', 'submitted'),
(1, 2, 3, 4, 82.00, 'Compréhension satisfaisante.', 'submitted'),
(1, 2, 4, 4, 80.00, 'Méthodologie correcte.', 'submitted'),
(1, 2, 5, 4, 89.00, 'Bonnes références similaires.', 'submitted'),
(1, 2, 6, 4, 80.00, 'Équipe acceptable.', 'submitted'),
(1, 2, 7, 4, 74.00, 'Rapport qualité-prix moyen.', 'submitted'),
(1, 3, 3, 4, 74.00, 'Compréhension à renforcer.', 'submitted'),
(1, 3, 4, 4, 76.00, 'Méthodologie perfectible.', 'submitted'),
(1, 3, 5, 4, 80.00, 'Références correctes.', 'submitted'),
(1, 3, 6, 4, 78.00, 'Équipe correcte.', 'submitted'),
(1, 3, 7, 4, 75.00, 'Offre financière acceptable.', 'submitted');

INSERT INTO evaluation_summaries (procedure_id, bidder_id, technical_score, financial_score, combined_score, rank_no, recommendation, eliminated, remarks, workflow_status) VALUES
(1, 1, 87.20, 78.00, 86.28, 1, 'Retenu pour négociation / étape suivante', 0, 'Dossier globalement solide.', 'submitted'),
(1, 2, 82.75, 74.00, 81.25, 2, 'Retenu sous réserve de clarifications', 0, 'Dossier conforme avec améliorations possibles.', 'submitted'),
(1, 3, 77.00, 75.00, 76.80, 3, 'Non prioritaire', 0, 'Dossier recevable mais moins compétitif.', 'submitted');

INSERT INTO procedure_minutes (procedure_id, meeting_date, meeting_place, chair_name, minutes_text, legal_observation, coordinator_observation, updated_by, updated_at) VALUES
(1, '2026-03-28', 'Salle de réunion PRETE NYUNGANIRA', 'Président Commission', 'PV provisoire de la séance d’évaluation. Les membres de la commission sont Président Commission, Évaluateur Technique 1 et Rapporteur de la commission.', 'Aucune réserve juridique majeure signalée à ce stade.', 'Programmation validée, commission constituée, évaluation à consolider.', 5, NOW());

INSERT INTO semantic_analyses (procedure_id, bidder_id, coverage_score, matched_keywords_json, missing_keywords_json, strengths, weaknesses, gaps, analysis_narrative, analysis_text, source_files_json, created_by) VALUES
(1, 1, 86.00, '["TDR","méthodologie","communication","références"]', '["plan de transfert"]', 'Bonne couverture des TDR et méthodologie claire.', 'Quelques détails opérationnels à préciser.', 'Renforcer le plan de transfert des compétences.', 'Analyse automatique indicative favorable.', 'Texte analysé de démonstration.', '[]', 3),
(1, 2, 81.00, '["TDR","références","équipe"]', '["risques"]', 'Références pertinentes et équipe correcte.', 'Analyse des risques insuffisamment détaillée.', 'Compléter le dispositif de suivi des risques.', 'Analyse automatique indicative satisfaisante.', 'Texte analysé de démonstration.', '[]', 3),
(1, 3, 77.00, '["communication","diagnostic"]', '["méthodologie","calendrier"]', 'Bonne compréhension générale.', 'Méthodologie et calendrier à renforcer.', 'Préciser les livrables et le séquençage.', 'Analyse automatique indicative moyenne.', 'Texte analysé de démonstration.', '[]', 3);

INSERT INTO messages (sender_id, recipient_id, subject, body, channel_type, is_read) VALUES
(2, 1, 'Programmation d’évaluation à valider', 'La procédure PRETE/NYU/AMI/001/2026 est programmée et soumise à validation.', 'internal', 0),
(1, 3, 'Commission créée', 'Votre accès Président est actif pour la procédure PRETE/NYU/AMI/001/2026.', 'internal', 0);

INSERT INTO backups (backup_date, zip_path, sql_path, generated_by) VALUES
(CURDATE(), 'BACKUPS/evalpro_package_demo.zip', 'BACKUPS/evalpro_schema_demo.sql', 1);

INSERT INTO audit_logs (user_id, action_label, entity_type, entity_id, payload_json) VALUES
(1, 'Initialisation de la base V4.5.2.2 compatible', 'system', 1, '{"version":"4.5.2.2"}');

SET FOREIGN_KEY_CHECKS=1;

-- Fin du script SQL V4.5.2.2 propre

;