-- =========================================================
-- نظام إدارة المبيعات - قاعدة البيانات
-- =========================================================

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

-- =========================
-- المستخدمين والأدوار
-- =========================
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,              -- owner, sales
    label VARCHAR(100) NOT NULL,            -- مدير, مبيعات
    permissions JSON NOT NULL,              -- {"ventes": true, "clients": true, "produits": false, ...}
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    full_name VARCHAR(150) NOT NULL,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar VARCHAR(255) DEFAULT NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (role_id) REFERENCES roles(id)
);

-- =========================
-- العملاء
-- =========================
CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    owner_user_id INT NOT NULL,             -- المبيعات المسؤول على هاد العميل
    full_name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    email VARCHAR(150),
    address TEXT,
    notes TEXT,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner_user_id) REFERENCES users(id)
);

-- =========================
-- الكتالوج
-- =========================
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(30),
    email VARCHAR(150),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT,
    supplier_id INT,
    name VARCHAR(150) NOT NULL,
    sku VARCHAR(50) UNIQUE,
    description TEXT,
    image VARCHAR(255),
    purchase_price DECIMAL(10,2) DEFAULT 0,
    sale_price DECIMAL(10,2) DEFAULT 0,
    min_sale_price DECIMAL(10,2) DEFAULT NULL,
    stock_qty INT DEFAULT 0,
    stock_alert_threshold INT DEFAULT 5,    -- "Stock bas"
    status ENUM('actif','brouillon') DEFAULT 'actif',
    deleted_at TIMESTAMP NULL DEFAULT NULL,     -- Corbeille (حذف ناعم قابل للاسترجاع)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE purchases (                    -- Achats
    id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_id INT NOT NULL,
    user_id INT NOT NULL,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('pending','received','cancelled') DEFAULT 'pending',
    purchase_date DATE NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE purchase_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    purchase_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (purchase_id) REFERENCES purchases(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- =========================
-- المبيعات
-- =========================
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_id INT NOT NULL,
    user_id INT NOT NULL,                   -- المبيعات لي دار البيع
    subtotal DECIMAL(10,2) DEFAULT 0,
    remise_percent DECIMAL(5,2) DEFAULT 0,      -- Remise (%) على الفاتورة كاملة
    remise_amount DECIMAL(10,2) DEFAULT 0,      -- Remise (montant) المحسوبة
    tva_percent DECIMAL(5,2) DEFAULT 0,
    tva_amount DECIMAL(10,2) DEFAULT 0,
    livraison_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0,    -- المبلغ المؤدى (الباقي = total - paid) لـ "À encaisser"
    payment_method ENUM('especes','virement','cheque','carte','agence_livraison','autre') DEFAULT 'especes',
    status ENUM('nouvelle','en_cours','livree','annulee') DEFAULT 'nouvelle',           -- حالة التسليم (Workflow existant, ماتبدلش)
    invoice_status ENUM('brouillon','validee','payee','annulee') DEFAULT 'brouillon',   -- حالة الفاتورة (Facture workflow جديد)
    stock_deducted TINYINT(1) DEFAULT 0,        -- علامة: هل تنقص الستوك ديال هاد البيع (باش مانخصوش مرتين)
    delivery_address TEXT,
    sale_date DATE NOT NULL,
    notes TEXT,
    show_stamp_zone TINYINT(1) DEFAULT 0,       -- زون Cachet et signature: قرار لكل بيع على حدة، مخفية بالافتراض
    deleted_at TIMESTAMP NULL DEFAULT NULL,     -- Corbeille (حذف ناعم قابل للاسترجاع)
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    remise_percent DECIMAL(5,2) DEFAULT 0,      -- Remise (%) على السطر
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- =========================
-- المالية
-- =========================
CREATE TABLE expenses (                     -- Dépenses
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    supplier_id INT DEFAULT NULL,
    title VARCHAR(150) NOT NULL,
    category VARCHAR(100),
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('especes','virement','cheque','carte','autre') DEFAULT 'especes',
    status ENUM('paye','en_attente') DEFAULT 'en_attente',
    receipt_path VARCHAR(255) DEFAULT NULL,
    expense_date DATE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id)
);

CREATE TABLE internal_transfers (           -- Virements internes
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    from_account VARCHAR(100) NOT NULL,
    to_account VARCHAR(100) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    transfer_date DATE NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE external_income (              -- Revenu Externe
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    source VARCHAR(150),
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('virement','cheque','especes','agence_livraison') DEFAULT 'virement',
    receipt_path VARCHAR(255),
    income_date DATE NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =========================
-- الوسائط (Médiathèque)
-- =========================
CREATE TABLE media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(255) NOT NULL,
    file_type VARCHAR(50),
    uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =========================
-- إعدادات الشركة
-- =========================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_name VARCHAR(150) DEFAULT 'EZ Lock',
    description TEXT,
    currency VARCHAR(10) DEFAULT 'MAD',
    country VARCHAR(80) DEFAULT 'Maroc',
    default_lang VARCHAR(5) DEFAULT 'fr',
    address VARCHAR(255) DEFAULT NULL,
    city VARCHAR(100) DEFAULT NULL,
    phone VARCHAR(30) DEFAULT NULL,
    gsm VARCHAR(30) DEFAULT NULL,
    email VARCHAR(150) DEFAULT NULL,
    website VARCHAR(150) DEFAULT NULL,
    ice VARCHAR(30) DEFAULT NULL,
    if_number VARCHAR(30) DEFAULT NULL,
    rc VARCHAR(30) DEFAULT NULL,
    activity_type VARCHAR(100) DEFAULT NULL,
    team_size VARCHAR(30) DEFAULT NULL,
    logo_path VARCHAR(255) DEFAULT NULL,
    logo_shape ENUM('carre','rectangulaire') DEFAULT 'carre',
    theme_color VARCHAR(20) DEFAULT '#A000F5',
    invoice_prefix VARCHAR(20) DEFAULT 'BV-',
    facture_prefix VARCHAR(20) DEFAULT 'FAC-',
    facture_padding INT DEFAULT 4,
    invoice_padding INT DEFAULT 4,
    default_tva DECIMAL(5,2) DEFAULT 0,
    invoice_footer TEXT,
    invoice_terms TEXT,
    show_logo_on_invoice TINYINT(1) DEFAULT 1,
    invoice_logo_position ENUM('gauche','centre','droite') DEFAULT 'gauche',
    invoice_show_company_info TINYINT(1) DEFAULT 1,
    invoice_table_header_color VARCHAR(20) DEFAULT '#A000F5',
    invoice_table_text_size ENUM('petit','normal','grand') DEFAULT 'normal',
    invoice_columns VARCHAR(255) DEFAULT 'reference,designation,quantite,prix_ht,remise,tva,total_ht,total_ttc',
    invoice_show_footer TINYINT(1) DEFAULT 1,
    invoice_client_label VARCHAR(50) DEFAULT 'Client',
    invoice_show_client_phone TINYINT(1) DEFAULT 1,
    invoice_show_client_email TINYINT(1) DEFAULT 1,
    invoice_show_client_address TINYINT(1) DEFAULT 1,
    invoice_header_image VARCHAR(255) DEFAULT NULL,
    invoice_footer_image VARCHAR(255) DEFAULT NULL,
    invoice_show_amount_words TINYINT(1) DEFAULT 1,
    invoice_show_stamp_zone TINYINT(1) DEFAULT 1,
    invoice_stamp_image VARCHAR(255) DEFAULT NULL,
    invoice_show_stamp_image TINYINT(1) DEFAULT 0
);
INSERT INTO settings (company_name, currency, default_lang, city, country) VALUES ('EZ Lock', 'MAD', 'fr', 'Casablanca', 'Maroc');

-- =========================
-- تتبع دفعات كل بيع (Historique des paiements)
-- =========================
CREATE TABLE sale_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('especes','virement','cheque','carte','agence_livraison','autre') DEFAULT 'especes',
    payment_date DATE NOT NULL,
    notes VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =========================
-- Mouvements de stock (Historique كامل لكل تغيير فالمخزون)
-- =========================
CREATE TABLE stock_movements (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    user_id INT,
    type ENUM('vente','achat','inventaire','ajustement','annulation','restauration') NOT NULL,
    quantity_change INT NOT NULL,            -- موجب = دخول، سالب = خروج
    stock_before INT NOT NULL,
    stock_after INT NOT NULL,
    reference_type VARCHAR(30) DEFAULT NULL, -- sale, purchase, inventory_session...
    reference_id INT DEFAULT NULL,
    notes VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =========================
-- نسب TVA قابلة للتعديل من Paramètres (ماشي Hard-coded)
-- =========================
CREATE TABLE tva_rates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    label VARCHAR(50) NOT NULL,
    rate DECIMAL(5,2) NOT NULL,
    is_default TINYINT(1) DEFAULT 0,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO tva_rates (label, rate, is_default, active) VALUES
('Exonéré', 0, 0, 1),
('TVA 10%', 10, 0, 1),
('TVA 20%', 20, 1, 1);

-- =========================
-- Inventaire (Stock théorique vs réel + Écart)
-- =========================
CREATE TABLE inventory_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(150) DEFAULT NULL,
    status ENUM('brouillon','validee') DEFAULT 'brouillon',
    inventory_date DATE NOT NULL,
    notes TEXT,
    validated_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE inventory_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_session_id INT NOT NULL,
    product_id INT NOT NULL,
    stock_theorique INT NOT NULL,
    stock_reel INT DEFAULT NULL,
    ecart INT DEFAULT 0,
    FOREIGN KEY (inventory_session_id) REFERENCES inventory_sessions(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- =========================
-- Facturation (وحدة مستقلة كليا عن Bons de vente — بلا أي علاقة)
-- =========================
CREATE TABLE factures (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(30) UNIQUE,
    client_id INT NOT NULL,
    user_id INT NOT NULL,
    subtotal DECIMAL(10,2) DEFAULT 0,
    remise_percent DECIMAL(5,2) DEFAULT 0,
    remise_amount DECIMAL(10,2) DEFAULT 0,
    tva_percent DECIMAL(5,2) DEFAULT 0,
    tva_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    paid_amount DECIMAL(10,2) DEFAULT 0,
    payment_method ENUM('especes','virement','cheque','carte','agence_livraison','autre') DEFAULT 'especes',
    status ENUM('brouillon','validee','payee','annulee') DEFAULT 'brouillon',
    facture_date DATE NOT NULL,
    notes TEXT,
    show_stamp_zone TINYINT(1) DEFAULT 0,       -- زون Cachet et signature: قرار لكل فاتورة على حدة، مخفية بالافتراض
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE facture_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_id INT NOT NULL,
    product_id INT DEFAULT NULL,          -- اختياري: تقدر تختار من الكتالوج ولا تكتب وصف حر
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    remise_percent DECIMAL(5,2) DEFAULT 0,
    FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE facture_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    facture_id INT NOT NULL,
    user_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    payment_method ENUM('especes','virement','cheque','carte','agence_livraison','autre') DEFAULT 'especes',
    payment_date DATE NOT NULL,
    notes VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (facture_id) REFERENCES factures(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- =========================
-- Devis (indépendant، بلا تأثير على الستوك)
-- =========================
CREATE TABLE devis (
    id INT AUTO_INCREMENT PRIMARY KEY,
    numero VARCHAR(30) UNIQUE,
    client_id INT NOT NULL,
    user_id INT NOT NULL,
    subtotal DECIMAL(10,2) DEFAULT 0,
    tva_percent DECIMAL(5,2) DEFAULT 0,
    tva_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    status ENUM('brouillon','envoye','accepte','refuse','expire') DEFAULT 'brouillon',
    valid_until DATE,
    notes TEXT,
    devis_date DATE NOT NULL,
    show_stamp_zone TINYINT(1) DEFAULT 0,       -- زون Cachet et signature: قرار لكل ديفي على حدة، مخفية بالافتراض
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE devis_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    devis_id INT NOT NULL,
    product_id INT DEFAULT NULL,
    description VARCHAR(255) NOT NULL,
    quantity DECIMAL(10,2) NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (devis_id) REFERENCES devis(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- =========================
-- بيانات أولية: الأدوار
-- =========================
INSERT INTO roles (name, label, permissions) VALUES
('owner', 'مدير', JSON_OBJECT(
    'dashboard', true, 'ventes', true, 'clients', true,
    'produits', true, 'categories', true, 'fournisseurs', true, 'achats', true,
    'depenses', true, 'virements', true, 'revenu_externe', true,
    'equipe', true, 'roles', true, 'mediatheque', true, 'parametres', true,
    'rapports', true, 'view_all_data', true, 'facturation', true
)),
('sales', 'مبيعات', JSON_OBJECT(
    'dashboard', true, 'ventes', true, 'clients', true,
    'produits', true, 'categories', false, 'fournisseurs', false, 'achats', false,
    'depenses', false, 'virements', false, 'revenu_externe', false,
    'equipe', false, 'roles', false, 'mediatheque', true, 'parametres', false,
    'rapports', true, 'view_all_data', false, 'facturation', true
));

-- ملاحظة: كلمة السر الافتراضية هي "admin123" (خاصك تبدلها بعد أول دخول)
-- الهاش تحتاج تتولد من PHP (password_hash) - شوف setup.php