-- =========================================================
-- SIGRDA-GN : Base de données MySQL
-- SGBD : MySQL 8+
-- Encodage : UTF8MB4
-- =========================================================

CREATE DATABASE IF NOT EXISTS sigrda_gn
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE sigrda_gn;

-- =========================
-- TABLE: roles
-- =========================
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
) ENGINE=InnoDB;

-- =========================
-- TABLE: users
-- =========================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    fullname VARCHAR(255),
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role_id INT NOT NULL,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_role
        FOREIGN KEY (role_id)
        REFERENCES roles(id)
        ON UPDATE CASCADE
) ENGINE=InnoDB;

-- =========================
-- TABLE: asylum_applications
-- =========================
CREATE TABLE asylum_applications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nud VARCHAR(20) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    date_naissance DATE NOT NULL,
    nationalite VARCHAR(50),
    pays_origine VARCHAR(50),
    motif TEXT,
    status ENUM('EN_COURS','ACCEPTEE','REJETEE','APPEL') DEFAULT 'EN_COURS',
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_update DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- =========================
-- TABLE: decisions
-- =========================
CREATE TABLE decisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    statut ENUM('EN_COURS','ACCEPTEE','REJETEE','APPEL') NOT NULL,
    date_decision DATETIME DEFAULT CURRENT_TIMESTAMP,
    commentaire TEXT,
    decision_by INT,
    CONSTRAINT fk_decision_application
        FOREIGN KEY (application_id)
        REFERENCES asylum_applications(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_decision_user
        FOREIGN KEY (decision_by)
        REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- TABLE: refugees
-- =========================
CREATE TABLE refugees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asylum_id INT NOT NULL UNIQUE,
    carte_numero VARCHAR(50) UNIQUE,
    date_reconnaissance DATE,
    statut VARCHAR(50),
    region VARCHAR(100),
    commune VARCHAR(100),
    CONSTRAINT fk_refugee_asylum
        FOREIGN KEY (asylum_id)
        REFERENCES asylum_applications(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- TABLE: families
-- =========================
CREATE TABLE families (
    id INT AUTO_INCREMENT PRIMARY KEY,
    refugee_id INT NOT NULL,
    nom VARCHAR(100),
    prenom VARCHAR(100),
    date_naissance DATE,
    lien_parent VARCHAR(50),
    CONSTRAINT fk_family_refugee
        FOREIGN KEY (refugee_id)
        REFERENCES refugees(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- TABLE: documents
-- =========================
CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    application_id INT NOT NULL,
    type_document VARCHAR(100),
    chemin_fichier TEXT,
    date_upload DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_document_application
        FOREIGN KEY (application_id)
        REFERENCES asylum_applications(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

-- =========================
-- TABLE: organizations
-- =========================
CREATE TABLE organizations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(150) NOT NULL,
    type VARCHAR(50),
    zone_intervention VARCHAR(150),
    contact VARCHAR(150)
) ENGINE=InnoDB;

-- =========================
-- TABLE: aid_services
-- =========================
CREATE TABLE aid_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    refugee_id INT NOT NULL,
    organization_id INT,
    type_aide VARCHAR(100),
    date_attribution DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50),
    CONSTRAINT fk_aid_refugee
        FOREIGN KEY (refugee_id)
        REFERENCES refugees(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_aid_org
        FOREIGN KEY (organization_id)
        REFERENCES organizations(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- TABLE: audit_logs
-- =========================
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(150),
    date_action DATETIME DEFAULT CURRENT_TIMESTAMP,
    details TEXT,
    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

-- =========================
-- INDEXES (performance)
-- =========================
CREATE INDEX idx_asylum_nud ON asylum_applications(nud);
CREATE INDEX idx_refugee_region ON refugees(region);
CREATE INDEX idx_aid_type ON aid_services(type_aide);
CREATE INDEX idx_audit_date ON audit_logs(date_action);

-- =========================
-- DONNÉES INITIALES (ROLES)
-- =========================
INSERT INTO roles (name, description) VALUES
('ADMIN', 'Administrateur national'),
('AGENT_ASILE', 'Agent d’enregistrement des demandes'),
('DECIDEUR', 'Commission de décision'),
('HCR', 'Haut Commissariat pour les Réfugiés'),
('ONG', 'Organisation humanitaire'),
('SECURITE', 'Accès lecture seule');

CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT NOT NULL
) ENGINE=InnoDB;

INSERT INTO settings (setting_key, setting_value) VALUES
('app_name', 'Système de gestion des réfugiés'),
('country', 'Guinée'),
('organization', 'Commission Nationale Asile'),
('email', 'contact@asile.gov.gn'),
('phone', '+224 000000000'),
('address', 'Conakry');

-- =========================================================
-- FIN DU SCRIPT
-- =========================================================
