CREATE DATABASE IF NOT EXISTS darlink_erp CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE darlink_erp;

CREATE TABLE IF NOT EXISTS migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    migration VARCHAR(190) NOT NULL,
    executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS parametres_societe (
    id INT AUTO_INCREMENT PRIMARY KEY,
    raison_sociale VARCHAR(150) NOT NULL DEFAULT 'Darlink',
    pays VARCHAR(100) DEFAULT 'Maroc',
    devise VARCHAR(10) DEFAULT 'MAD',
    taux_tva_defaut DECIMAL(5,2) DEFAULT 20.00,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO parametres_societe (raison_sociale, pays, devise, taux_tva_defaut)
SELECT 'Darlink', 'Maroc', 'MAD', 20.00
WHERE NOT EXISTS (SELECT 1 FROM parametres_societe WHERE id = 1);


-- Sprint 2 : Module Clients
CREATE TABLE IF NOT EXISTS types_clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    libelle VARCHAR(80) NOT NULL,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT INTO types_clients (libelle)
SELECT 'Hôtel' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Hôtel');
INSERT INTO types_clients (libelle)
SELECT 'Restaurant' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Restaurant');
INSERT INTO types_clients (libelle)
SELECT 'Café' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Café');
INSERT INTO types_clients (libelle)
SELECT 'Particulier' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Particulier');
INSERT INTO types_clients (libelle)
SELECT 'Entreprise' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Entreprise');
INSERT INTO types_clients (libelle)
SELECT 'Société de nettoyage' WHERE NOT EXISTS (SELECT 1 FROM types_clients WHERE libelle = 'Société de nettoyage');

CREATE TABLE IF NOT EXISTS clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    type_client_id INT NULL,
    code_client VARCHAR(20) NOT NULL UNIQUE,
    raison_sociale VARCHAR(150) NOT NULL,
    contact VARCHAR(150),
    adresse TEXT,
    code_postal VARCHAR(20),
    ville VARCHAR(100),
    pays VARCHAR(100) DEFAULT 'Maroc',
    telephone VARCHAR(30),
    mobile VARCHAR(30),
    email VARCHAR(150),
    site_web VARCHAR(150),
    ice VARCHAR(30),
    identifiant_fiscal VARCHAR(30),
    registre_commerce VARCHAR(30),
    commercial VARCHAR(100),
    remise DECIMAL(5,2) DEFAULT 0.00,
    mode_reglement VARCHAR(50),
    delai_paiement INT DEFAULT 0,
    plafond_credit DECIMAL(12,2) DEFAULT 0,
    observations TEXT,
    actif TINYINT(1) DEFAULT 1,
    date_creation DATETIME DEFAULT CURRENT_TIMESTAMP,
    date_modification DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_clients_type (type_client_id),
    INDEX idx_clients_raison (raison_sociale),
    INDEX idx_clients_ville (ville),
    FOREIGN KEY (type_client_id) REFERENCES types_clients(id)
);

CREATE TABLE IF NOT EXISTS clients_historique (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    commentaire TEXT,
    utilisateur VARCHAR(100) DEFAULT 'Admin',
    date_action DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id)
);

INSERT INTO migrations (migration)
SELECT '002_clients'
WHERE NOT EXISTS (SELECT 1 FROM migrations WHERE migration = '002_clients');