-- CTRA ERP — MySQL schema
-- Run via phpMyAdmin or: mysql -u root -p ctra_erp < database/schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

DROP TABLE IF EXISTS commandes;
DROP TABLE IF EXISTS devis;
DROP TABLE IF EXISTS clients;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    tel VARCHAR(50) DEFAULT NULL,
    role ENUM('admin','manager','utilisateur','lecture') NOT NULL DEFAULT 'utilisateur',
    password_hash VARCHAR(255) NOT NULL,
    notes TEXT DEFAULT NULL,
    last_login DATETIME DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE clients (
    id VARCHAR(20) PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    email VARCHAR(255) DEFAULT NULL,
    tel VARCHAR(50) DEFAULT NULL,
    pays VARCHAR(100) DEFAULT NULL,
    ville VARCHAR(100) DEFAULT NULL,
    secteur VARCHAR(100) DEFAULT NULL,
    statut VARCHAR(20) NOT NULL DEFAULT 'prospect',
    notes TEXT DEFAULT NULL,
    commercial VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_clients_commercial (commercial),
    INDEX idx_clients_statut (statut)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE devis (
    id VARCHAR(30) PRIMARY KEY,
    client_id VARCHAR(20) NOT NULL,
    commercial VARCHAR(255) NOT NULL,
    date DATE DEFAULT NULL,
    exp DATE DEFAULT NULL,
    ht DECIMAL(15,2) NOT NULL DEFAULT 0,
    tva DECIMAL(5,2) NOT NULL DEFAULT 19,
    ttc DECIMAL(15,2) NOT NULL DEFAULT 0,
    heures DECIMAL(10,2) NOT NULL DEFAULT 0,
    statut VARCHAR(20) NOT NULL DEFAULT 'brouillon',
    description TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_devis_client (client_id),
    INDEX idx_devis_commercial (commercial),
    CONSTRAINT fk_devis_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE commandes (
    id VARCHAR(30) PRIMARY KEY,
    client_id VARCHAR(20) NOT NULL,
    commercial VARCHAR(255) NOT NULL,
    devis_id VARCHAR(30) DEFAULT NULL,
    date DATE DEFAULT NULL,
    livraison DATE DEFAULT NULL,
    ht DECIMAL(15,2) NOT NULL DEFAULT 0,
    marge DECIMAL(5,2) NOT NULL DEFAULT 0,
    heures DECIMAL(10,2) NOT NULL DEFAULT 0,
    statut VARCHAR(20) NOT NULL DEFAULT 'confirmée',
    prod_start DATE DEFAULT NULL,
    prod_end DATE DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_cmd_client (client_id),
    INDEX idx_cmd_commercial (commercial),
    CONSTRAINT fk_cmd_client FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    CONSTRAINT fk_cmd_devis FOREIGN KEY (devis_id) REFERENCES devis(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;
