-- ============================================================
-- GESTION PARC AUTOMOBILE - Province Al Haouz v2.1
-- Système de permissions granulaires par rôle
-- ============================================================

CREATE DATABASE IF NOT EXISTS gestion_parc CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gestion_parc;

-- Permissions
CREATE TABLE IF NOT EXISTS permissions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(80) NOT NULL UNIQUE,
    module VARCHAR(50) NOT NULL,
    label_fr VARCHAR(150) NOT NULL,
    description VARCHAR(255) NULL,
    ordre INT UNSIGNED DEFAULT 0
) ENGINE=InnoDB;

-- Rôles
CREATE TABLE IF NOT EXISTS roles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    is_system TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Pivot rôle ↔ permission
CREATE TABLE IF NOT EXISTS role_permissions (
    role_id INT UNSIGNED NOT NULL,
    permission_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE,
    FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Utilisateurs
CREATE TABLE IF NOT EXISTS users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    cnie VARCHAR(20) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NULL,
    password VARCHAR(255) NOT NULL,
    role_id INT UNSIGNED NOT NULL DEFAULT 3,
    observation TEXT NULL,
    remember_token VARCHAR(100) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
) ENGINE=InnoDB;

-- Exercices
CREATE TABLE IF NOT EXISTS exercices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    annee INT NOT NULL UNIQUE,
    observation VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Budgets
CREATE TABLE IF NOT EXISTS budgets (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    type_budget VARCHAR(100) NOT NULL UNIQUE,
    description VARCHAR(255) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Divisions
CREATE TABLE IF NOT EXISTS divisions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ref_division VARCHAR(30) NOT NULL UNIQUE,
    nom_division VARCHAR(200) NOT NULL,
    responsable VARCHAR(150) NULL,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Affectations
CREATE TABLE IF NOT EXISTS affectations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    intitule_siege VARCHAR(255) NOT NULL,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Carburants
CREATE TABLE IF NOT EXISTS carburants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ref_carburant VARCHAR(30) NOT NULL UNIQUE,
    nom_carburant VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Types d'intervention
CREATE TABLE IF NOT EXISTS type_interventions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ref VARCHAR(20) NOT NULL UNIQUE,
    intervention VARCHAR(150) NOT NULL,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Prestataires
CREATE TABLE IF NOT EXISTS prestataires (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ref_prestataire VARCHAR(30) NOT NULL UNIQUE,
    raison_sociale VARCHAR(200) NOT NULL,
    responsable VARCHAR(150) NULL,
    gsm VARCHAR(30) NULL,
    telephone VARCHAR(30) NULL,
    fax VARCHAR(30) NULL,
    email VARCHAR(100) NULL,
    adresse VARCHAR(255) NULL,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Véhicules
CREATE TABLE IF NOT EXISTS vehicules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    matricule VARCHAR(20) NOT NULL UNIQUE,
    marque VARCHAR(100) NOT NULL,
    date_mise_service DATE NULL,
    affectation_id INT UNSIGNED NULL,
    qualite VARCHAR(100) NULL,
    observation TEXT NULL,
    statut ENUM('actif','en_panne','reforme') DEFAULT 'actif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (affectation_id) REFERENCES affectations(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Missions
CREATE TABLE IF NOT EXISTS missions (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    n_bon VARCHAR(20) NOT NULL UNIQUE,
    beneficiaire VARCHAR(150) NOT NULL,
    division_id INT UNSIGNED NULL,
    mission TEXT NULL,
    date_mission DATE NOT NULL,
    destination VARCHAR(200) NULL,
    vehicule_id INT UNSIGNED NULL,
    budget_id INT UNSIGNED NULL,
    quantite DECIMAL(10,2) DEFAULT 0,
    carburant_id INT UNSIGNED NULL,
    type_mission ENUM('normale','exceptionnelle') DEFAULT 'normale',
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (division_id) REFERENCES divisions(id) ON DELETE SET NULL,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE SET NULL,
    FOREIGN KEY (budget_id) REFERENCES budgets(id) ON DELETE SET NULL,
    FOREIGN KEY (carburant_id) REFERENCES carburants(id) ON DELETE SET NULL,
    INDEX idx_date_mission (date_mission)
) ENGINE=InnoDB;

-- Vignettes
CREATE TABLE IF NOT EXISTS vignettes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    n_decharge INT NOT NULL UNIQUE,
    exercice_id INT UNSIGNED NULL,
    budget_id INT UNSIGNED NULL,
    beneficiaire VARCHAR(150) NULL,
    affectation_id INT UNSIGNED NULL,
    date_attribution DATE NULL,
    n_carnet_b VARCHAR(50) NULL,
    vehicule_id INT UNSIGNED NULL,
    montant DECIMAL(10,2) DEFAULT 0,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (exercice_id) REFERENCES exercices(id) ON DELETE SET NULL,
    FOREIGN KEY (budget_id) REFERENCES budgets(id) ON DELETE SET NULL,
    FOREIGN KEY (affectation_id) REFERENCES affectations(id) ON DELETE SET NULL,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Opérations véhicules
CREATE TABLE IF NOT EXISTS operations_vehicules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicule_id INT UNSIGNED NOT NULL,
    type_intervention_id INT UNSIGNED NULL,
    date_operation DATE NOT NULL,
    prestataire_id INT UNSIGNED NULL,
    cout DECIMAL(12,2) DEFAULT 0,
    observation TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE CASCADE,
    FOREIGN KEY (type_intervention_id) REFERENCES type_interventions(id) ON DELETE SET NULL,
    FOREIGN KEY (prestataire_id) REFERENCES prestataires(id) ON DELETE SET NULL,
    INDEX idx_date_operation (date_operation)
) ENGINE=InnoDB;

-- ============================================================
-- DONNÉES DE BASE
-- ============================================================
INSERT INTO roles (id, nom, description, is_system) VALUES
(1, 'SuperAdmin', 'Accès complet - tous les droits', 1),
(2, 'Administrateur', 'Gestion complète sauf rôles système', 0),
(3, 'Utilisateur', 'Consultation et saisie limitée', 0);

INSERT INTO exercices (annee) VALUES (2022),(2023),(2024),(2025),(2026),(2027),(2028),(2029),(2030);

INSERT INTO budgets (type_budget, description) VALUES
('Général', 'Ministère de l''Intérieur'),
('I.N.D.H', 'Initiative Nationale pour le Développement Humain Al Haouz');

INSERT INTO carburants (ref_carburant, nom_carburant) VALUES ('Essence','Essence'),('Gasoil','Gasoil');

INSERT INTO type_interventions (ref, intervention) VALUES ('R100','Changement de Roues'),('V100','Vidange'),('V200','Vitres');

-- ============================================================
-- PERMISSIONS
-- ============================================================
INSERT INTO permissions (code, module, label_fr, description, ordre) VALUES
('dashboard_view',          'Tableau de bord',  'Voir le tableau de bord',              NULL, 10),
('vehicules_view',          'Véhicules',        'Consulter les véhicules',              NULL, 20),
('vehicules_create',        'Véhicules',        'Ajouter un véhicule',                  NULL, 21),
('vehicules_edit',          'Véhicules',        'Modifier un véhicule',                 NULL, 22),
('vehicules_delete',        'Véhicules',        'Supprimer un véhicule',                NULL, 23),
('missions_view',           'Bons Carburant',   'Consulter les bons carburant',         NULL, 30),
('missions_create',         'Bons Carburant',   'Ajouter un bon carburant',             NULL, 31),
('missions_edit',           'Bons Carburant',   'Modifier un bon carburant',            NULL, 32),
('missions_delete',         'Bons Carburant',   'Supprimer un bon carburant',           NULL, 33),
('operations_view',         'Maintenance',      'Consulter les opérations',             NULL, 40),
('operations_create',       'Maintenance',      'Ajouter une opération',                NULL, 41),
('operations_edit',         'Maintenance',      'Modifier une opération',               NULL, 42),
('operations_delete',       'Maintenance',      'Supprimer une opération',              NULL, 43),
('vignettes_view',          'Vignettes',        'Consulter les vignettes',              NULL, 50),
('vignettes_create',        'Vignettes',        'Ajouter une vignette',                 NULL, 51),
('vignettes_edit',          'Vignettes',        'Modifier une vignette',                NULL, 52),
('vignettes_delete',        'Vignettes',        'Supprimer une vignette',               NULL, 53),
('rapports_view',           'Rapports',         'Consulter les rapports',               NULL, 60),
('rapports_export',         'Rapports',         'Exporter les données',                 NULL, 61),
('param_divisions_view',    'Param. Divisions',     'Voir les divisions',               NULL, 70),
('param_divisions_edit',    'Param. Divisions',     'Gérer les divisions',              NULL, 71),
('param_prestataires_view', 'Param. Prestataires',  'Voir les prestataires',            NULL, 72),
('param_prestataires_edit', 'Param. Prestataires',  'Gérer les prestataires',           NULL, 73),
('param_budgets_view',      'Param. Budgets',       'Voir les budgets',                 NULL, 74),
('param_budgets_edit',      'Param. Budgets',       'Gérer les budgets',                NULL, 75),
('param_affectations_view', 'Param. Affectations',  'Voir les affectations',            NULL, 76),
('param_affectations_edit', 'Param. Affectations',  'Gérer les affectations',           NULL, 77),
('param_carburants_view',   'Param. Carburants',    'Voir les carburants',              NULL, 78),
('param_carburants_edit',   'Param. Carburants',    'Gérer les carburants',             NULL, 79),
('param_interventions_view','Param. Interventions',  'Voir les types intervention',     NULL, 80),
('param_interventions_edit','Param. Interventions',  'Gérer les types intervention',    NULL, 81),
('param_exercices_view',    'Param. Exercices',     'Voir les exercices',               NULL, 82),
('param_exercices_edit',    'Param. Exercices',     'Gérer les exercices',              NULL, 83),
('users_view',              'Utilisateurs',     'Voir les utilisateurs',                NULL, 90),
('users_create',            'Utilisateurs',     'Créer un utilisateur',                 NULL, 91),
('users_edit',              'Utilisateurs',     'Modifier un utilisateur',              NULL, 92),
('users_delete',            'Utilisateurs',     'Supprimer un utilisateur',             NULL, 93),
('roles_view',              'Rôles',            'Voir les rôles',                       NULL, 94),
('roles_manage',            'Rôles',            'Gérer les rôles & permissions',        NULL, 95);

-- SuperAdmin : toutes les permissions
INSERT INTO role_permissions (role_id, permission_id) SELECT 1, id FROM permissions;

-- Administrateur : tout sauf gestion rôles & suppression users
INSERT INTO role_permissions (role_id, permission_id) SELECT 2, id FROM permissions WHERE code NOT IN ('roles_manage','users_delete');

-- Utilisateur : consultation + saisie basique
INSERT INTO role_permissions (role_id, permission_id)
SELECT 3, id FROM permissions WHERE code IN (
    'dashboard_view','vehicules_view',
    'missions_view','missions_create','missions_edit',
    'operations_view','operations_create','operations_edit',
    'vignettes_view',
    'param_divisions_view','param_prestataires_view','param_budgets_view',
    'param_affectations_view','param_carburants_view','param_interventions_view','param_exercices_view'
);

-- Admin par défaut (mot de passe: password)
INSERT INTO users (cnie, nom, username, password, role_id) VALUES
('ADMIN01', 'Administrateur Système', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 1);