-- ============================================================
-- KARGEST - Schéma de base de données MySQL
-- Solution de Gestion de Flotte Automobile
-- ============================================================

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS pleins_carburant;
DROP TABLE IF EXISTS cartes_carburant;
DROP TABLE IF EXISTS inspections;
DROP TABLE IF EXISTS sinistres;
DROP TABLE IF EXISTS affectations;
DROP TABLE IF EXISTS documents;
DROP TABLE IF EXISTS conducteurs;
DROP TABLE IF EXISTS vehicules;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS company_settings;
DROP TABLE IF EXISTS licences;
SET FOREIGN_KEY_CHECKS=1;

-- ------------------------------------------------------------
-- 1. SYSTEME DE LICENCES & PROPRIETE (KARGEST LOCK)
-- ------------------------------------------------------------
CREATE TABLE licences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    license_key VARCHAR(100) NOT NULL UNIQUE,
    client_name VARCHAR(150) NOT NULL,
    max_vehicules INT NOT NULL DEFAULT 10,
    max_conducteurs INT NOT NULL DEFAULT 10,
    modules_json TEXT, -- Modules activés ex: ["pneus", "sinistres", "inspections"]
    starts_at DATETIME NOT NULL,
    expires_at DATETIME NOT NULL,
    status ENUM('active', 'suspended', 'expired') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 2. PARAMETRES ENTREPRISE (PERSONNALISATION SANS WHITE-LABEL DU NOM)
-- ------------------------------------------------------------
CREATE TABLE company_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_name VARCHAR(50) NOT NULL DEFAULT 'KARGEST', -- NOM INCHANGEABLE
    company_name VARCHAR(150) NOT NULL DEFAULT 'Ma Société',
    company_email VARCHAR(150),
    company_phone VARCHAR(50),
    logo_path VARCHAR(255),
    logo_pdf_path VARCHAR(255),
    primary_color VARCHAR(10) DEFAULT '#1E293B',
    accent_color VARCHAR(10) DEFAULT '#2563EB',
    currency VARCHAR(10) DEFAULT 'EUR',
    timezone VARCHAR(50) DEFAULT 'Europe/Paris',
    date_format VARCHAR(20) DEFAULT 'Y-m-d',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. ROLES ET PERMISSIONS (RBAC)
-- ------------------------------------------------------------
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    label VARCHAR(100) NOT NULL,
    description VARCHAR(255)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO roles (name, label, description) VALUES
('super_admin', 'Super Administrateur', 'Accès global système et licences'),
('admin', 'Administrateur', 'Gestion complète de l entreprise'),
('gestionnaire', 'Gestionnaire de Flotte', 'Gestion des véhicules, affectations, alertes'),
('maintenance', 'Responsable Maintenance', 'Suivi entretiens, ordres de travail, pneus'),
('dispatcher', 'Exploitant / Dispatcher', 'Planning et affectations terrain'),
('comptable', 'Comptable / Contrôleur', 'Suivi financier et dépenses'),
('conducteur', 'Conducteur Mobile', 'Accès application mobile terrain'),
('auditeur', 'Auditeur / Lecture seule', 'Consultation uniquement');

-- ------------------------------------------------------------
-- 4. UTILISATEURS
-- ------------------------------------------------------------
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(30),
    status ENUM('active', 'inactive', 'suspended') DEFAULT 'active',
    last_login DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 5. VEHICULES
-- ------------------------------------------------------------
CREATE TABLE vehicules (
    id INT AUTO_INCREMENT PRIMARY KEY,
    internal_id VARCHAR(50) NOT NULL UNIQUE,
    immatriculation VARCHAR(30) NOT NULL UNIQUE,
    vin VARCHAR(50),
    marque VARCHAR(50) NOT NULL,
    modele VARCHAR(50) NOT NULL,
    finition VARCHAR(50),
    categorie ENUM('vp', 'vu', 'poids_lourd', 'deux_roues', 'autre') DEFAULT 'vp',
    energie ENUM('essence', 'diesel', 'hybride', 'electrique', 'gpl', 'autre') DEFAULT 'diesel',
    boite ENUM('manuelle', 'automatique') DEFAULT 'manuelle',
    couleur VARCHAR(30),
    date_mise_en_circulation DATE,
    date_acquisition DATE,
    mode_acquisition ENUM('achat', 'leasing', 'location_courte', 'location_longue') DEFAULT 'achat',
    bailleur_fournisseur VARCHAR(100),
    valeur_achat DECIMAL(10,2) DEFAULT 0.00,
    valeur_actuelle DECIMAL(10,2) DEFAULT 0.00,
    kilometrage_initial INT DEFAULT 0,
    kilometrage_actuel INT DEFAULT 0,
    centre_de_cout VARCHAR(100),
    agence_site VARCHAR(100),
    statut ENUM('actif', 'en_maintenance', 'en_reserve', 'cede', 'archive') DEFAULT 'actif',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 6. CONDUCTEURS
-- ------------------------------------------------------------
CREATE TABLE conducteurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE,
    matricule VARCHAR(50) NOT NULL UNIQUE,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(150),
    telephone VARCHAR(30),
    adresse TEXT,
    date_naissance DATE,
    contact_urgence VARCHAR(150),
    agence_rattachement VARCHAR(100),
    statut ENUM('actif', 'suspendu', 'inactif') DEFAULT 'actif',
    type_contrat VARCHAR(50),
    categories_permis VARCHAR(100),
    date_obtention_permis DATE,
    date_expiration_permis DATE,
    solde_points INT DEFAULT 12,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 7. CARTES CARBURANT (AVEC CODE PIN SÉCURISÉ)
-- ------------------------------------------------------------
CREATE TABLE cartes_carburant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero_carte VARCHAR(50) NOT NULL UNIQUE,
    fournisseur VARCHAR(100) NOT NULL,
    code_pin VARCHAR(20) NOT NULL, -- CODE PIN INCLUS
    plafond_mensuel DECIMAL(10,2) DEFAULT 0.00,
    type_carburant_autorise VARCHAR(100) DEFAULT 'Tous',
    date_expiration DATE,
    vehicule_id INT NULL,
    conducteur_id INT NULL,
    statut ENUM('active', 'bloquee', 'expiree', 'inactive') DEFAULT 'active',
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE SET NULL,
    FOREIGN KEY (conducteur_id) REFERENCES conducteurs(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. PLEINS DE CARBURANT
-- ------------------------------------------------------------
CREATE TABLE pleins_carburant (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicule_id INT NOT NULL,
    conducteur_id INT NOT NULL,
    carte_carburant_id INT NULL,
    date_plein DATETIME NOT NULL,
    station VARCHAR(100),
    localisation VARCHAR(255),
    kilometrage INT NOT NULL,
    quantite_litres DECIMAL(8,2) NOT NULL,
    prix_unitaire DECIMAL(6,3) NOT NULL,
    montant_total DECIMAL(10,2) NOT NULL,
    type_carburant VARCHAR(50) NOT NULL,
    photo_ticket VARCHAR(255),
    commentaires TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE CASCADE,
    FOREIGN KEY (conducteur_id) REFERENCES conducteurs(id) ON DELETE CASCADE,
    FOREIGN KEY (carte_carburant_id) REFERENCES cartes_carburant(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. AFFECTATIONS VEHICULE - CONDUCTEUR
-- ------------------------------------------------------------
CREATE TABLE affectations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicule_id INT NOT NULL,
    conducteur_id INT NOT NULL,
    date_debut DATETIME NOT NULL,
    date_fin_prevue DATETIME,
    date_fin_effective DATETIME,
    km_depart INT NOT NULL,
    km_retour INT,
    niveau_carburant_depart VARCHAR(20),
    niveau_carburant_retour VARCHAR(20),
    etat_general_depart TEXT,
    etat_general_retour TEXT,
    statut ENUM('en_cours', 'terminee', 'planifiee', 'annulee') DEFAULT 'en_cours',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE CASCADE,
    FOREIGN KEY (conducteur_id) REFERENCES conducteurs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. INSPECTIONS NUMÉRIQUES (CHECKLISTS MOBILE)
-- ------------------------------------------------------------
CREATE TABLE inspections (
    id INT AUTO_INCREMENT PRIMARY KEY,
    vehicule_id INT NOT NULL,
    conducteur_id INT NOT NULL,
    type_inspection ENUM('pre_depart', 'post_trajet', 'restitution', 'echange') DEFAULT 'pre_depart',
    date_inspection DATETIME NOT NULL,
    kilometrage INT NOT NULL,
    conforme TINYINT(1) DEFAULT 1,
    details_json TEXT,
    signature_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (vehicule_id) REFERENCES vehicules(id) ON DELETE CASCADE,
    FOREIGN KEY (conducteur_id) REFERENCES conducteurs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. AUDIT LOGS
-- ------------------------------------------------------------
CREATE TABLE audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(100) NOT NULL,
    description TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;