-- =========================================================
-- migration.sql
-- شغّل هاد الملف فـ phpMyAdmin غير إلا كنت رفعتي database.sql من قبل
-- (يعني عندك ديجا الجداول والداتا وبغيتي غير تزيد الحقول الجداد)
-- إلا كانت هادي أول مرة كترفع النظام، سيري مباشرة لـ database.sql وما خاصك هاد الملف.
-- =========================================================

ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS address VARCHAR(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS city VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS phone VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS activity_type VARCHAR(100) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS team_size VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS logo_path VARCHAR(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS theme_color VARCHAR(20) DEFAULT '#A000F5';

UPDATE settings SET currency = 'MAD' WHERE currency = 'DH';
UPDATE settings SET city = 'Casablanca' WHERE city IS NULL AND id = 1;

CREATE TABLE IF NOT EXISTS 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)
);

ALTER TABLE products
    ADD COLUMN IF NOT EXISTS description TEXT,
    ADD COLUMN IF NOT EXISTS min_sale_price DECIMAL(10,2) DEFAULT NULL;

ALTER TABLE sales
    ADD COLUMN IF NOT EXISTS subtotal DECIMAL(10,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS tva_percent DECIMAL(5,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS tva_amount DECIMAL(10,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS livraison_amount DECIMAL(10,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS delivery_address TEXT;

ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS description TEXT,
    ADD COLUMN IF NOT EXISTS country VARCHAR(80) DEFAULT 'Maroc',
    ADD COLUMN IF NOT EXISTS invoice_prefix VARCHAR(20) DEFAULT 'BV-',
    ADD COLUMN IF NOT EXISTS invoice_padding INT DEFAULT 4,
    ADD COLUMN IF NOT EXISTS default_tva DECIMAL(5,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS invoice_footer TEXT,
    ADD COLUMN IF NOT EXISTS invoice_terms TEXT,
    ADD COLUMN IF NOT EXISTS show_logo_on_invoice TINYINT(1) DEFAULT 1;
UPDATE settings SET country = 'Maroc' WHERE country IS NULL AND id = 1;

ALTER TABLE expenses
    ADD COLUMN IF NOT EXISTS supplier_id INT DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS title VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS payment_method ENUM('especes','virement','cheque','carte','autre') DEFAULT 'especes',
    ADD COLUMN IF NOT EXISTS status ENUM('paye','en_attente') DEFAULT 'en_attente',
    ADD COLUMN IF NOT EXISTS receipt_path VARCHAR(255) DEFAULT NULL;
UPDATE expenses SET title = category WHERE title IS NULL;
ALTER TABLE expenses MODIFY title VARCHAR(150) NOT NULL;

ALTER TABLE external_income
    ADD COLUMN IF NOT EXISTS payment_method ENUM('virement','cheque','especes','agence_livraison') DEFAULT 'virement',
    ADD COLUMN IF NOT EXISTS receipt_path VARCHAR(255) DEFAULT NULL;

-- Corbeille (حذف ناعم قابل للاسترجاع) للمنتجات والمبيعات
ALTER TABLE products ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL DEFAULT NULL;
ALTER TABLE sales ADD COLUMN IF NOT EXISTS deleted_at TIMESTAMP NULL DEFAULT NULL;

-- =========================================================
-- تطوير Facture + Inventaire + Paramètres Facture
-- =========================================================

-- Remise وحالة الفاتورة الجديدة وحماية خصم الستوك المزدوج
ALTER TABLE sales
    ADD COLUMN IF NOT EXISTS remise_percent DECIMAL(5,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS remise_amount DECIMAL(10,2) DEFAULT 0,
    ADD COLUMN IF NOT EXISTS invoice_status ENUM('brouillon','validee','payee','annulee') DEFAULT 'brouillon',
    ADD COLUMN IF NOT EXISTS stock_deducted TINYINT(1) DEFAULT 0;
-- الفواتير الموجودة من قبل خصمو الستوك ديالهم ديجا عند الإنشاء، فكنعلمو عليها باش ما يتعاودش الخصم
UPDATE sales SET stock_deducted = 1 WHERE status != 'annulee' AND stock_deducted = 0;
UPDATE sales SET invoice_status = CASE
    WHEN status = 'annulee' THEN 'annulee'
    WHEN paid_amount >= total_amount AND total_amount > 0 THEN 'payee'
    ELSE 'validee'
END WHERE invoice_status = 'brouillon';

ALTER TABLE sale_items ADD COLUMN IF NOT EXISTS remise_percent DECIMAL(5,2) DEFAULT 0;

-- Mouvements de stock
CREATE TABLE IF NOT EXISTS 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,
    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 rates قابلة للتعديل
CREATE TABLE IF NOT EXISTS 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)
SELECT * FROM (SELECT 'Exonéré' AS label, 0 AS rate, 0 AS is_default, 1 AS active
    UNION ALL SELECT 'TVA 10%', 10, 0, 1
    UNION ALL SELECT 'TVA 20%', 20, 1, 1) AS tmp
WHERE NOT EXISTS (SELECT 1 FROM tva_rates);

-- Inventaire
CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS 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)
);

-- معلومات الشركة الإضافية + تخصيص الفاتورة الكامل
ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS gsm VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS email VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS website VARCHAR(150) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS ice VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS if_number VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS rc VARCHAR(30) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS invoice_logo_position ENUM('gauche','centre','droite') DEFAULT 'gauche',
    ADD COLUMN IF NOT EXISTS invoice_show_company_info TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_table_header_color VARCHAR(20) DEFAULT '#A000F5',
    ADD COLUMN IF NOT EXISTS invoice_table_text_size ENUM('petit','normal','grand') DEFAULT 'normal',
    ADD COLUMN IF NOT EXISTS invoice_columns VARCHAR(255) DEFAULT 'reference,designation,quantite,prix_ht,remise,tva,total_ht,total_ttc',
    ADD COLUMN IF NOT EXISTS invoice_show_footer TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS facture_prefix VARCHAR(20) DEFAULT 'FAC-',
    ADD COLUMN IF NOT EXISTS facture_padding INT DEFAULT 4,
    ADD COLUMN IF NOT EXISTS invoice_client_label VARCHAR(50) DEFAULT 'Client',
    ADD COLUMN IF NOT EXISTS invoice_show_client_phone TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_show_client_email TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_show_client_address TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_header_image VARCHAR(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS invoice_footer_image VARCHAR(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS invoice_show_amount_words TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_show_stamp_zone TINYINT(1) DEFAULT 1,
    ADD COLUMN IF NOT EXISTS invoice_stamp_image VARCHAR(255) DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS invoice_show_stamp_image TINYINT(1) DEFAULT 0;

-- =========================================================
-- Facturation : وحدة مستقلة كليا عن Bons de vente (بلا أي علاقة)
-- =========================================================
CREATE TABLE IF NOT EXISTS 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,
    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 IF NOT EXISTS 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 IF NOT EXISTS 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)
);

-- كنزيدو صلاحية 'facturation' للأدوار الموجودة من قبل (JSON_SET كتزيد المفتاح إلا ماكانش موجود)
UPDATE roles SET permissions = JSON_SET(permissions, '$.facturation', true) WHERE JSON_EXTRACT(permissions, '$.facturation') IS NULL;

-- شكل اللوغو (مربع أو مستطيل) لعرض أفضل ديال اللوغو فالوثائق
ALTER TABLE settings
    ADD COLUMN IF NOT EXISTS logo_shape ENUM('carre','rectangulaire') DEFAULT 'carre';

-- =========================================================
-- Module Devis (indépendant, sans impact sur le stock)
-- =========================================================
CREATE TABLE IF NOT EXISTS 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,
    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 IF NOT EXISTS 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)
);
UPDATE roles SET permissions = JSON_SET(permissions, '$.ventes', true) WHERE JSON_EXTRACT(permissions, '$.ventes') IS NOT NULL;

-- زون "Cachet et signature" دابا قرار لكل وثيقة على حدة (ماشي إعداد عام) — مخفية بالافتراض
ALTER TABLE sales ADD COLUMN IF NOT EXISTS show_stamp_zone TINYINT(1) DEFAULT 0;
ALTER TABLE factures ADD COLUMN IF NOT EXISTS show_stamp_zone TINYINT(1) DEFAULT 0;
ALTER TABLE devis ADD COLUMN IF NOT EXISTS show_stamp_zone TINYINT(1) DEFAULT 0;