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

CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(140) NOT NULL,
    email VARCHAR(190) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role VARCHAR(80) NOT NULL,
    phone VARCHAR(40) NULL,
    status ENUM('Actif','Suspendu') NOT NULL DEFAULT 'Actif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    phone VARCHAR(40) NULL,
    email VARCHAR(190) NULL,
    address TEXT NULL
);

CREATE TABLE warehouses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(160) NOT NULL,
    address TEXT NULL,
    manager_name VARCHAR(160) NULL,
    phone VARCHAR(40) NULL,
    status ENUM('Actif','Inactif') DEFAULT 'Actif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(120) NOT NULL UNIQUE
);

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reference VARCHAR(80) NOT NULL UNIQUE,
    barcode VARCHAR(120) NULL,
    designation VARCHAR(190) NOT NULL,
    category_id INT NULL,
    subcategory VARCHAR(120) NULL,
    unit VARCHAR(40) NOT NULL,
    brand VARCHAR(120) NULL,
    supplier_id INT NULL,
    stock_current DECIMAL(14,3) NOT NULL DEFAULT 0,
    stock_min DECIMAL(14,3) NOT NULL DEFAULT 0,
    stock_max DECIMAL(14,3) NOT NULL DEFAULT 0,
    avg_purchase_price DECIMAL(14,2) NOT NULL DEFAULT 0,
    location VARCHAR(160) NULL,
    photo VARCHAR(255) NULL,
    description TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
);

CREATE TABLE warehouse_stocks (
    warehouse_id INT NOT NULL,
    article_id INT NOT NULL,
    quantity DECIMAL(14,3) NOT NULL DEFAULT 0,
    PRIMARY KEY (warehouse_id, article_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id) ON DELETE CASCADE,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE CASCADE
);

CREATE TABLE projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(190) NOT NULL,
    address TEXT NULL,
    city VARCHAR(120) NULL,
    manager_name VARCHAR(160) NULL,
    phone VARCHAR(40) NULL,
    start_date DATE NULL,
    expected_end_date DATE NULL,
    budget DECIMAL(14,2) DEFAULT 0,
    status ENUM('En preparation','En cours','Suspendu','Termine') DEFAULT 'En preparation'
);

CREATE TABLE project_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    building VARCHAR(80) NOT NULL,
    floor VARCHAR(80) NULL,
    apartment VARCHAR(80) NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

CREATE TABLE stock_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type ENUM('ENTREE','SORTIE','TRANSFERT','AJUSTEMENT') NOT NULL,
    movement_date DATE NOT NULL,
    article_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    target_warehouse_id INT NULL,
    project_id INT NULL,
    location_id INT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    unit_price DECIMAL(14,2) DEFAULT 0,
    tax_rate DECIMAL(5,2) DEFAULT 0,
    beneficiary VARCHAR(160) NULL,
    beneficiary_function VARCHAR(120) NULL,
    delivery_note VARCHAR(120) NULL,
    invoice_ref VARCHAR(120) NULL,
    invoice_file VARCHAR(255) NULL,
    attachment VARCHAR(255) NULL,
    signature_data MEDIUMTEXT NULL,
    observations TEXT NULL,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (target_warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    FOREIGN KEY (location_id) REFERENCES project_locations(id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE material_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    request_date DATE NOT NULL,
    project_id INT NOT NULL,
    location_id INT NULL,
    requester_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    article_id INT NOT NULL,
    quantity DECIMAL(14,3) NOT NULL,
    urgency ENUM('Normale','Urgente','Critique') DEFAULT 'Normale',
    reason TEXT NULL,
    status ENUM('En attente','Validee','Refusee','Livree') DEFAULT 'En attente',
    decision_by INT NULL,
    decision_at DATETIME NULL,
    decision_note TEXT NULL,
    movement_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id),
    FOREIGN KEY (location_id) REFERENCES project_locations(id) ON DELETE SET NULL,
    FOREIGN KEY (requester_id) REFERENCES users(id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (decision_by) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (movement_id) REFERENCES stock_movements(id) ON DELETE SET NULL
);

CREATE TABLE inventories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_date DATE NOT NULL,
    type ENUM('Complet','Partiel') NOT NULL,
    warehouse_id INT NOT NULL,
    article_id INT NOT NULL,
    theoretical_qty DECIMAL(14,3) NOT NULL,
    real_qty DECIMAL(14,3) NOT NULL,
    variance_qty DECIMAL(14,3) NOT NULL,
    adjusted TINYINT(1) NOT NULL DEFAULT 0,
    created_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(id),
    FOREIGN KEY (article_id) REFERENCES articles(id),
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE documents (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(190) NOT NULL,
    type ENUM('Facture fournisseur','Bon de livraison','Bon entree','Bon sortie','Inventaire','Contrat','Devis') NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    project_id INT NULL,
    supplier_id INT NULL,
    article_id INT NULL,
    reference VARCHAR(120) NULL,
    document_date DATE NULL,
    uploaded_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL,
    FOREIGN KEY (article_id) REFERENCES articles(id) ON DELETE SET NULL,
    FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NULL,
    action VARCHAR(120) NOT NULL,
    entity VARCHAR(120) NOT NULL,
    entity_id INT NULL,
    details JSON NULL,
    ip_address VARCHAR(64) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

INSERT INTO roles (name) VALUES
('Administrateur'),('Magasinier'),('Responsable Chantier'),('Controleur'),('Lecture seule')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO categories (name) VALUES
('Ciment'),('Fer a beton'),('Carrelage'),('Cables electriques'),('Interrupteurs'),
('Robinetterie'),('Peinture'),('Climatisation'),('Outillage'),('Consommables')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO warehouses (name, address, manager_name, phone, status)
VALUES ('Entrepot principal', 'Siege', 'Magasinier principal', '0600000000', 'Actif');

INSERT INTO users (name, email, password, role, status)
VALUES ('Administrateur', 'admin@example.com', '$2y$10$kL024awuawa36rqf4AwOwOsv9Bx6qSVmZVdOIS1KJrPrF2MVMcCRe', 'Administrateur', 'Actif');