-- Base de données pour la plateforme de gestion MNC

-- Table des utilisateurs
CREATE TABLE IF NOT EXISTS utilisateurs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    telephone VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'gestionnaire', 'utilisateur') DEFAULT 'utilisateur',
    actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des permissions
CREATE TABLE IF NOT EXISTS permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    utilisateur_id INT NOT NULL,
    module VARCHAR(50) NOT NULL,
    lecture BOOLEAN DEFAULT FALSE,
    ecriture BOOLEAN DEFAULT FALSE,
    modification BOOLEAN DEFAULT FALSE,
    suppression BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des types de TVA
CREATE TABLE IF NOT EXISTS tva_types (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    taux DECIMAL(5,2) NOT NULL,
    actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de configuration des documents
CREATE TABLE IF NOT EXISTS configuration_documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entreprise_nom VARCHAR(200) NOT NULL,
    entreprise_adresse TEXT,
    entreprise_telephone VARCHAR(20),
    entreprise_email VARCHAR(150),
    entreprise_ice VARCHAR(50),
    entreprise_rc VARCHAR(50),
    entreprise_if VARCHAR(50),
    logo TEXT,
    footer_text TEXT,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des clients
CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(200) NOT NULL,
    adresse TEXT,
    ville VARCHAR(100),
    code_postal VARCHAR(10),
    telephone VARCHAR(20),
    email VARCHAR(150),
    ice VARCHAR(50),
    rc VARCHAR(50),
    if_number VARCHAR(50),
    actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des articles
CREATE TABLE IF NOT EXISTS articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(50) UNIQUE NOT NULL,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    prix_ht DECIMAL(10,2) NOT NULL,
    tva_id INT,
    unite VARCHAR(20) DEFAULT 'Unité',
    actif BOOLEAN DEFAULT TRUE,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tva_id) REFERENCES tva_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table de stock
CREATE TABLE IF NOT EXISTS stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    quantite DECIMAL(10,2) DEFAULT 0,
    seuil_alerte DECIMAL(10,2) DEFAULT 10,
    emplacement VARCHAR(100),
    derniere_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des mouvements de stock
CREATE TABLE IF NOT EXISTS mouvements_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    article_id INT NOT NULL,
    type_mouvement ENUM('entree', 'sortie', 'ajustement') NOT NULL,
    quantite DECIMAL(10,2) NOT NULL,
    motif VARCHAR(200),
    reference_document VARCHAR(50),
    utilisateur_id INT,
    date_mouvement TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des devis
CREATE TABLE IF NOT EXISTS devis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    date_devis DATE NOT NULL,
    date_validite DATE,
    statut ENUM('brouillon', 'envoye', 'accepte', 'refuse', 'expire') DEFAULT 'brouillon',
    montant_ht DECIMAL(10,2) DEFAULT 0,
    montant_tva DECIMAL(10,2) DEFAULT 0,
    montant_ttc DECIMAL(10,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    conditions TEXT,
    utilisateur_id INT,
    converti_en_facture BOOLEAN DEFAULT FALSE,
    facture_id INT,
    converti_en_bl BOOLEAN DEFAULT FALSE,
    bl_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lignes de devis
CREATE TABLE IF NOT EXISTS devis_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    devis_id INT NOT NULL,
    article_id INT,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    quantite DECIMAL(10,2) NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    tva_taux DECIMAL(5,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    montant_ht DECIMAL(10,2) NOT NULL,
    montant_tva DECIMAL(10,2) NOT NULL,
    montant_ttc DECIMAL(10,2) NOT NULL,
    ordre INT DEFAULT 0,
    FOREIGN KEY (devis_id) REFERENCES devis(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des bons de commande
CREATE TABLE IF NOT EXISTS bon_commande (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    date_commande DATE NOT NULL,
    date_livraison_prevue DATE,
    statut ENUM('en_attente', 'confirme', 'en_preparation', 'livre', 'annule') DEFAULT 'en_attente',
    montant_ht DECIMAL(10,2) DEFAULT 0,
    montant_tva DECIMAL(10,2) DEFAULT 0,
    montant_ttc DECIMAL(10,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    utilisateur_id INT,
    converti_en_bl BOOLEAN DEFAULT FALSE,
    bl_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lignes de bon de commande
CREATE TABLE IF NOT EXISTS bon_commande_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bon_commande_id INT NOT NULL,
    article_id INT,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    quantite DECIMAL(10,2) NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    tva_taux DECIMAL(5,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    montant_ht DECIMAL(10,2) NOT NULL,
    montant_tva DECIMAL(10,2) NOT NULL,
    montant_ttc DECIMAL(10,2) NOT NULL,
    ordre INT DEFAULT 0,
    FOREIGN KEY (bon_commande_id) REFERENCES bon_commande(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des bons de livraison
CREATE TABLE IF NOT EXISTS bon_livraison (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    date_livraison DATE NOT NULL,
    statut ENUM('en_preparation', 'expedie', 'livre', 'retour') DEFAULT 'en_preparation',
    bon_commande_id INT,
    devis_id INT,
    notes TEXT,
    utilisateur_id INT,
    converti_en_facture BOOLEAN DEFAULT FALSE,
    facture_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (bon_commande_id) REFERENCES bon_commande(id),
    FOREIGN KEY (devis_id) REFERENCES devis(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lignes de bon de livraison
CREATE TABLE IF NOT EXISTS bon_livraison_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bon_livraison_id INT NOT NULL,
    article_id INT,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    quantite DECIMAL(10,2) NOT NULL,
    ordre INT DEFAULT 0,
    FOREIGN KEY (bon_livraison_id) REFERENCES bon_livraison(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des factures
CREATE TABLE IF NOT EXISTS factures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    client_id INT NOT NULL,
    date_facture DATE NOT NULL,
    date_echeance DATE,
    statut ENUM('brouillon', 'envoyee', 'payee', 'partielle', 'en_retard', 'annulee') DEFAULT 'brouillon',
    montant_ht DECIMAL(10,2) DEFAULT 0,
    montant_tva DECIMAL(10,2) DEFAULT 0,
    montant_ttc DECIMAL(10,2) DEFAULT 0,
    montant_paye DECIMAL(10,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    conditions_paiement TEXT,
    devis_id INT,
    bon_livraison_id INT,
    utilisateur_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (devis_id) REFERENCES devis(id),
    FOREIGN KEY (bon_livraison_id) REFERENCES bon_livraison(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lignes de factures
CREATE TABLE IF NOT EXISTS factures_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_id INT NOT NULL,
    article_id INT,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    quantite DECIMAL(10,2) NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    tva_taux DECIMAL(5,2) DEFAULT 0,
    remise DECIMAL(10,2) DEFAULT 0,
    montant_ht DECIMAL(10,2) NOT NULL,
    montant_tva DECIMAL(10,2) NOT NULL,
    montant_ttc DECIMAL(10,2) NOT NULL,
    ordre INT DEFAULT 0,
    FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des factures avoir
CREATE TABLE IF NOT EXISTS factures_avoir (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    facture_origine_id INT NOT NULL,
    client_id INT NOT NULL,
    date_avoir DATE NOT NULL,
    motif TEXT,
    montant_ht DECIMAL(10,2) DEFAULT 0,
    montant_tva DECIMAL(10,2) DEFAULT 0,
    montant_ttc DECIMAL(10,2) DEFAULT 0,
    notes TEXT,
    utilisateur_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (facture_origine_id) REFERENCES factures(id),
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des lignes de factures avoir
CREATE TABLE IF NOT EXISTS factures_avoir_lignes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_avoir_id INT NOT NULL,
    article_id INT,
    designation VARCHAR(200) NOT NULL,
    description TEXT,
    quantite DECIMAL(10,2) NOT NULL,
    prix_unitaire DECIMAL(10,2) NOT NULL,
    tva_taux DECIMAL(5,2) DEFAULT 0,
    montant_ht DECIMAL(10,2) NOT NULL,
    montant_tva DECIMAL(10,2) NOT NULL,
    montant_ttc DECIMAL(10,2) NOT NULL,
    ordre INT DEFAULT 0,
    FOREIGN KEY (facture_avoir_id) REFERENCES factures_avoir(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des paiements
CREATE TABLE IF NOT EXISTS paiements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_id INT NOT NULL,
    montant DECIMAL(10,2) NOT NULL,
    date_paiement DATE NOT NULL,
    methode_paiement ENUM('especes', 'cheque', 'virement', 'carte', 'autre') NOT NULL,
    reference VARCHAR(100),
    notes TEXT,
    utilisateur_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (facture_id) REFERENCES factures(id),
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des dépenses
CREATE TABLE IF NOT EXISTS depenses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(50) UNIQUE NOT NULL,
    date_depense DATE NOT NULL,
    categorie VARCHAR(100) NOT NULL,
    fournisseur VARCHAR(200),
    montant_ht DECIMAL(10,2) NOT NULL,
    montant_tva DECIMAL(10,2) DEFAULT 0,
    montant_ttc DECIMAL(10,2) NOT NULL,
    description TEXT,
    mode_paiement ENUM('especes', 'cheque', 'virement', 'carte', 'autre'),
    reference_facture VARCHAR(100),
    fichier_joint TEXT,
    utilisateur_id INT,
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    date_modification TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des notifications
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    utilisateur_id INT,
    type VARCHAR(50) NOT NULL,
    titre VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    lu BOOLEAN DEFAULT FALSE,
    lien VARCHAR(200),
    date_creation TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (utilisateur_id) REFERENCES utilisateurs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Index pour améliorer les performances
CREATE INDEX idx_devis_client ON devis(client_id);
CREATE INDEX idx_devis_statut ON devis(statut);
CREATE INDEX idx_factures_client ON factures(client_id);
CREATE INDEX idx_factures_statut ON factures(statut);
CREATE INDEX idx_stock_article ON stock(article_id);
CREATE INDEX idx_notifications_utilisateur ON notifications(utilisateur_id, lu);