CREATE TABLE `audit_log` (
  `id_audit` int(11) NOT NULL AUTO_INCREMENT,
  `date_action` datetime NOT NULL,
  `id_user` int(11) NOT NULL,
  `action` varchar(64) NOT NULL,
  `details` text DEFAULT NULL,
  PRIMARY KEY (`id_audit`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `audit_log_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `utilisateurs` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `avances` (
  `id_avance` int(11) NOT NULL AUTO_INCREMENT,
  `date_avance` datetime NOT NULL,
  `montant` decimal(15,2) NOT NULL,
  `id_user_op` int(11) NOT NULL,
  `receptionneur` varchar(255) DEFAULT NULL,
  `motif` text DEFAULT NULL,
  `statut` varchar(50) DEFAULT 'Non remboursée',
  `montant_restant` decimal(15,2) NOT NULL,
  PRIMARY KEY (`id_avance`),
  KEY `id_user_op` (`id_user_op`),
  CONSTRAINT `avances_ibfk_1` FOREIGN KEY (`id_user_op`) REFERENCES `utilisateurs` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `bons_commande` (
  `id_bc` int(11) NOT NULL AUTO_INCREMENT,
  `numero_bc` varchar(50) DEFAULT NULL,
  `id_fournisseur` int(11) DEFAULT NULL,
  `remise_doc_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva_doc` int(11) DEFAULT NULL,
  `total_lignes_ht` decimal(12,2) DEFAULT 0.00,
  `remise_doc_montant` decimal(12,2) DEFAULT 0.00,
  `base_tva` decimal(12,2) DEFAULT 0.00,
  `total_tva` decimal(12,2) DEFAULT 0.00,
  `total_ttc` decimal(12,2) DEFAULT 0.00,
  `statut` enum('en_attente','validé','rejeté') DEFAULT 'en_attente',
  `date_creation` datetime DEFAULT current_timestamp(),
  `commentaire` text DEFAULT NULL,
  PRIMARY KEY (`id_bc`),
  KEY `id_fournisseur` (`id_fournisseur`),
  KEY `id_tva_doc` (`id_tva_doc`),
  CONSTRAINT `bons_commande_ibfk_1` FOREIGN KEY (`id_fournisseur`) REFERENCES `fournisseurs` (`id_fournisseur`),
  CONSTRAINT `bons_commande_ibfk_2` FOREIGN KEY (`id_tva_doc`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `comptes_comptables` (
  `id_compte` int(11) NOT NULL AUTO_INCREMENT,
  `code_compte` varchar(20) DEFAULT NULL,
  `libelle` varchar(255) DEFAULT NULL,
  `type` enum('actif','passif','charge','produit') NOT NULL,
  PRIMARY KEY (`id_compte`),
  UNIQUE KEY `code_compte` (`code_compte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `details_bc` (
  `id_detail_bc` int(11) NOT NULL AUTO_INCREMENT,
  `id_bc` int(11) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `quantite` decimal(12,3) NOT NULL DEFAULT 1.000,
  `prix_unitaire` decimal(12,2) NOT NULL,
  `remise_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva` int(11) DEFAULT NULL,
  `montant_ht` decimal(12,2) DEFAULT 0.00,
  `montant_tva` decimal(12,2) DEFAULT 0.00,
  `montant_ttc` decimal(12,2) DEFAULT 0.00,
  PRIMARY KEY (`id_detail_bc`),
  KEY `id_bc` (`id_bc`),
  KEY `id_tva` (`id_tva`),
  CONSTRAINT `details_bc_ibfk_1` FOREIGN KEY (`id_bc`) REFERENCES `bons_commande` (`id_bc`) ON DELETE CASCADE,
  CONSTRAINT `details_bc_ibfk_2` FOREIGN KEY (`id_tva`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `details_facture` (
  `id_detail_facture` int(11) NOT NULL AUTO_INCREMENT,
  `id_facture` int(11) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `quantite` decimal(12,3) NOT NULL DEFAULT 1.000,
  `prix_unitaire` decimal(12,2) NOT NULL,
  `remise_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva` int(11) DEFAULT NULL,
  `montant_ht` decimal(12,2) DEFAULT 0.00,
  `montant_tva` decimal(12,2) DEFAULT 0.00,
  `montant_ttc` decimal(12,2) DEFAULT 0.00,
  PRIMARY KEY (`id_detail_facture`),
  KEY `id_facture` (`id_facture`),
  KEY `id_tva` (`id_tva`),
  CONSTRAINT `details_facture_ibfk_1` FOREIGN KEY (`id_facture`) REFERENCES `factures` (`id_facture`) ON DELETE CASCADE,
  CONSTRAINT `details_facture_ibfk_2` FOREIGN KEY (`id_tva`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `details_proforma` (
  `id_detail_proforma` int(11) NOT NULL AUTO_INCREMENT,
  `id_proforma` int(11) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `quantite` decimal(12,3) NOT NULL DEFAULT 1.000,
  `prix_unitaire` decimal(12,2) NOT NULL,
  `remise_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva` int(11) DEFAULT NULL,
  `montant_ht` decimal(12,2) DEFAULT 0.00,
  `montant_tva` decimal(12,2) DEFAULT 0.00,
  `montant_ttc` decimal(12,2) DEFAULT 0.00,
  PRIMARY KEY (`id_detail_proforma`),
  KEY `id_proforma` (`id_proforma`),
  KEY `id_tva` (`id_tva`),
  CONSTRAINT `details_proforma_ibfk_1` FOREIGN KEY (`id_proforma`) REFERENCES `proformas` (`id_proforma`) ON DELETE CASCADE,
  CONSTRAINT `details_proforma_ibfk_2` FOREIGN KEY (`id_tva`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `documents` (
  `id_document` int(11) NOT NULL AUTO_INCREMENT,
  `nom_document` varchar(255) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `chemin` text DEFAULT NULL,
  `id_projet` int(11) DEFAULT NULL,
  `date_upload` datetime DEFAULT current_timestamp(),
  `id_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_document`),
  KEY `id_projet` (`id_projet`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`id_projet`) REFERENCES `projets` (`id_projet`),
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `utilisateurs` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `ecritures_comptables` (
  `id_ecriture` int(11) NOT NULL AUTO_INCREMENT,
  `date_ecriture` datetime NOT NULL,
  `libelle` varchar(255) DEFAULT NULL,
  `id_compte_debit` int(11) DEFAULT NULL,
  `id_compte_credit` int(11) DEFAULT NULL,
  `montant` decimal(12,2) NOT NULL,
  `source_type` varchar(50) DEFAULT NULL,
  `id_source` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_ecriture`),
  KEY `id_compte_debit` (`id_compte_debit`),
  KEY `id_compte_credit` (`id_compte_credit`),
  CONSTRAINT `ecritures_comptables_ibfk_1` FOREIGN KEY (`id_compte_debit`) REFERENCES `comptes_comptables` (`id_compte`),
  CONSTRAINT `ecritures_comptables_ibfk_2` FOREIGN KEY (`id_compte_credit`) REFERENCES `comptes_comptables` (`id_compte`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `factures` (
  `id_facture` int(11) NOT NULL AUTO_INCREMENT,
  `numero_facture` varchar(50) DEFAULT NULL,
  `id_bc` int(11) DEFAULT NULL,
  `remise_doc_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva_doc` int(11) DEFAULT NULL,
  `total_lignes_ht` decimal(12,2) DEFAULT 0.00,
  `remise_doc_montant` decimal(12,2) DEFAULT 0.00,
  `base_tva` decimal(12,2) DEFAULT 0.00,
  `total_tva` decimal(12,2) DEFAULT 0.00,
  `total_ttc` decimal(12,2) DEFAULT 0.00,
  `date_emission` datetime DEFAULT current_timestamp(),
  `comptabilisee` tinyint(1) DEFAULT 0,
  `commentaire` text DEFAULT NULL,
  PRIMARY KEY (`id_facture`),
  KEY `id_bc` (`id_bc`),
  KEY `id_tva_doc` (`id_tva_doc`),
  CONSTRAINT `factures_ibfk_1` FOREIGN KEY (`id_bc`) REFERENCES `bons_commande` (`id_bc`),
  CONSTRAINT `factures_ibfk_2` FOREIGN KEY (`id_tva_doc`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `fournisseurs` (
  `id_fournisseur` int(11) NOT NULL AUTO_INCREMENT,
  `nom_fournisseur` varchar(255) DEFAULT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `adresse` text DEFAULT NULL,
  PRIMARY KEY (`id_fournisseur`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `modules_projet` (
  `id_module` int(11) NOT NULL AUTO_INCREMENT,
  `nom_module` varchar(255) DEFAULT NULL,
  `actif` tinyint(1) DEFAULT 1,
  `id_projet` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_module`),
  KEY `id_projet` (`id_projet`),
  CONSTRAINT `modules_projet_ibfk_1` FOREIGN KEY (`id_projet`) REFERENCES `projets` (`id_projet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `paiements` (
  `id_paiement` int(11) NOT NULL AUTO_INCREMENT,
  `id_facture` int(11) DEFAULT NULL,
  `montant` decimal(12,2) DEFAULT NULL,
  `date_paiement` datetime DEFAULT NULL,
  `mode_paiement` varchar(50) DEFAULT NULL,
  `id_user` int(11) DEFAULT NULL,
  PRIMARY KEY (`id_paiement`),
  KEY `id_facture` (`id_facture`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `paiements_ibfk_1` FOREIGN KEY (`id_facture`) REFERENCES `factures` (`id_facture`),
  CONSTRAINT `paiements_ibfk_2` FOREIGN KEY (`id_user`) REFERENCES `utilisateurs` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `proformas` (
  `id_proforma` int(11) NOT NULL AUTO_INCREMENT,
  `numero_proforma` varchar(50) DEFAULT NULL,
  `id_fournisseur` int(11) DEFAULT NULL,
  `remise_doc_percent` decimal(5,2) DEFAULT 0.00,
  `id_tva_doc` int(11) DEFAULT NULL,
  `total_lignes_ht` decimal(12,2) DEFAULT 0.00,
  `remise_doc_montant` decimal(12,2) DEFAULT 0.00,
  `base_tva` decimal(12,2) DEFAULT 0.00,
  `total_tva` decimal(12,2) DEFAULT 0.00,
  `total_ttc` decimal(12,2) DEFAULT 0.00,
  `date_emission` datetime DEFAULT current_timestamp(),
  `statut` enum('en_attente','validé','rejeté') DEFAULT 'en_attente',
  `commentaire` text DEFAULT NULL,
  PRIMARY KEY (`id_proforma`),
  KEY `id_fournisseur` (`id_fournisseur`),
  KEY `id_tva_doc` (`id_tva_doc`),
  CONSTRAINT `proformas_ibfk_1` FOREIGN KEY (`id_fournisseur`) REFERENCES `fournisseurs` (`id_fournisseur`),
  CONSTRAINT `proformas_ibfk_2` FOREIGN KEY (`id_tva_doc`) REFERENCES `tva_taux` (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



CREATE TABLE `projets` (
  `id_projet` int(11) NOT NULL AUTO_INCREMENT,
  `nom_projet` varchar(255) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `actif` tinyint(1) DEFAULT 1,
  `date_creation` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id_projet`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tokens` (
  `id_token` int(11) NOT NULL AUTO_INCREMENT,
  `id_user` int(11) DEFAULT NULL,
  `token` varchar(512) DEFAULT NULL,
  `expiration` datetime DEFAULT NULL,
  `actif` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id_token`),
  KEY `id_user` (`id_user`),
  CONSTRAINT `tokens_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `utilisateurs` (`id_user`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `tva_taux` (
  `id_tva` int(11) NOT NULL AUTO_INCREMENT,
  `libelle` varchar(100) NOT NULL,
  `taux` decimal(5,2) NOT NULL,
  `actif` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id_tva`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


CREATE TABLE `utilisateurs` (
  `id_user` int(11) NOT NULL AUTO_INCREMENT,
  `nom` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `mot_de_passe` varchar(255) DEFAULT NULL,
  `role` enum('admin','manager','comptable','invité') DEFAULT 'invité',
  `date_creation` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id_user`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



insert  into `utilisateurs`(`id_user`,`nom`,`email`,`mot_de_passe`,`role`,`date_creation`) values 
(1,'Tiekoumy','tiekoumy19@gmail.com','$2y$10$VOGyy30JYRCFzWaV6g30Xus6.cR3oYmdNZlmdD0Tors9aq5ESMCju','invité','2025-11-20 12:00:48');