-- =============================================================================
-- SCHÉMA DE BASE DE DONNÉES - CAISSE NF525
-- Conforme Loi Anti-Fraude TVA (Article 286 du CGI)
-- =============================================================================
-- Version: 1.0.0
-- Date: 2025-01-05
-- Moteur: InnoDB (transactions ACID obligatoires)
-- Charset: utf8mb4 (support complet Unicode)
-- =============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------------------------------
-- TABLE: companies
-- Informations de l'entreprise utilisatrice
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `companies` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL COMMENT 'Raison sociale',
    `legal_form` VARCHAR(50) DEFAULT NULL COMMENT 'Forme juridique (SARL, SAS, etc.)',
    `capital` DECIMAL(15,2) DEFAULT NULL COMMENT 'Capital social',
    `siret` VARCHAR(14) NOT NULL COMMENT 'Numéro SIRET (14 chiffres)',
    `siren` VARCHAR(9) NOT NULL COMMENT 'Numéro SIREN (9 chiffres)',
    `vat_number` VARCHAR(20) DEFAULT NULL COMMENT 'N° TVA Intracommunautaire',
    `ape_code` VARCHAR(10) DEFAULT NULL COMMENT 'Code APE/NAF',
    `rcs_city` VARCHAR(100) DEFAULT NULL COMMENT 'Ville RCS',
    `address_line1` VARCHAR(255) NOT NULL,
    `address_line2` VARCHAR(255) DEFAULT NULL,
    `postal_code` VARCHAR(10) NOT NULL,
    `city` VARCHAR(100) NOT NULL,
    `country` VARCHAR(2) NOT NULL DEFAULT 'FR',
    `phone` VARCHAR(20) DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `website` VARCHAR(255) DEFAULT NULL,
    `logo_path` VARCHAR(255) DEFAULT NULL,
    `invoice_footer` TEXT DEFAULT NULL COMMENT 'Pied de page factures',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_siret` (`siret`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: users
-- Utilisateurs du système (caissiers, managers, admin)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `email` VARCHAR(255) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL COMMENT 'Hash Argon2id',
    `pin_hash` VARCHAR(255) DEFAULT NULL COMMENT 'Code PIN pour accès rapide caisse',
    `first_name` VARCHAR(100) NOT NULL,
    `last_name` VARCHAR(100) NOT NULL,
    `role` ENUM('super_admin', 'manager', 'supervisor', 'cashier', 'inventory') NOT NULL DEFAULT 'cashier',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `failed_attempts` INT UNSIGNED NOT NULL DEFAULT 0,
    `locked_until` DATETIME DEFAULT NULL,
    `last_login_at` DATETIME DEFAULT NULL,
    `last_login_ip` VARCHAR(45) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_email` (`email`),
    INDEX `idx_role` (`role`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: terminals
-- Terminaux de caisse (postes de vente)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `terminals` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `uuid` VARCHAR(36) NOT NULL COMMENT 'Identifiant unique UUID',
    `name` VARCHAR(100) NOT NULL COMMENT 'Nom du terminal (ex: Caisse 1)',
    `location` VARCHAR(255) DEFAULT NULL,
    `printer_type` ENUM('thermal_58mm', 'thermal_80mm', 'a4_laser', 'a4_inkjet', 'none') NOT NULL DEFAULT 'thermal_80mm',
    `printer_config` JSON DEFAULT NULL,
    `has_barcode_scanner` TINYINT(1) NOT NULL DEFAULT 1,
    `has_cash_drawer` TINYINT(1) NOT NULL DEFAULT 1,
    `payment_terminal_type` VARCHAR(50) DEFAULT NULL,
    `payment_terminal_config` JSON DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_uuid` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: categories
-- Catégories de produits
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `parent_id` INT UNSIGNED DEFAULT NULL,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `color` VARCHAR(7) DEFAULT '#6366f1' COMMENT 'Couleur hex pour affichage',
    `icon` VARCHAR(50) DEFAULT NULL,
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_slug` (`slug`),
    INDEX `idx_parent` (`parent_id`),
    CONSTRAINT `fk_categories_parent` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: brands
-- Marques
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `brands` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(100) NOT NULL,
    `logo_path` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: suppliers
-- Fournisseurs
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `suppliers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `contact_name` VARCHAR(255) DEFAULT NULL,
    `email` VARCHAR(255) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `address` TEXT DEFAULT NULL,
    `notes` TEXT DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: products
-- Catalogue produits
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `products` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reference` VARCHAR(50) NOT NULL COMMENT 'Référence interne',
    `barcode` VARCHAR(20) DEFAULT NULL COMMENT 'Code-barres EAN',
    `name` VARCHAR(255) NOT NULL,
    `short_description` VARCHAR(500) DEFAULT NULL,
    `description` TEXT DEFAULT NULL,
    `category_id` INT UNSIGNED DEFAULT NULL,
    `brand_id` INT UNSIGNED DEFAULT NULL,
    `supplier_id` INT UNSIGNED DEFAULT NULL,

    -- Prix
    `purchase_price_ht` DECIMAL(10,2) DEFAULT NULL COMMENT 'Prix achat HT',
    `selling_price_ht` DECIMAL(10,2) NOT NULL COMMENT 'Prix vente HT',
    `vat_rate` DECIMAL(5,2) NOT NULL DEFAULT 20.00 COMMENT 'Taux TVA',
    `selling_price_ttc` DECIMAL(10,2) GENERATED ALWAYS AS (
        ROUND(`selling_price_ht` * (1 + `vat_rate` / 100), 2)
    ) STORED COMMENT 'Prix TTC calculé',

    -- Stock
    `manage_stock` TINYINT(1) NOT NULL DEFAULT 1 COMMENT 'Gestion de stock active',
    `stock_quantity` INT NOT NULL DEFAULT 0,
    `stock_alert_threshold` INT NOT NULL DEFAULT 5,
    `stock_location` VARCHAR(100) DEFAULT NULL,
    `allow_backorder` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Autoriser vente si rupture',

    -- Attributs
    `has_variants` TINYINT(1) NOT NULL DEFAULT 0,
    `weight` DECIMAL(8,3) DEFAULT NULL COMMENT 'Poids en kg',
    `is_weighable` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Produit vendu au poids',

    -- Affichage
    `image_path` VARCHAR(255) DEFAULT NULL,
    `color` VARCHAR(7) DEFAULT NULL COMMENT 'Couleur affichage grille',
    `sort_order` INT NOT NULL DEFAULT 0,
    `is_favorite` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Affiché en raccourci',

    -- Statut
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_reference` (`reference`),
    UNIQUE KEY `uk_barcode` (`barcode`),
    INDEX `idx_category` (`category_id`),
    INDEX `idx_brand` (`brand_id`),
    INDEX `idx_active` (`is_active`),
    INDEX `idx_favorite` (`is_favorite`),
    INDEX `idx_stock_alert` (`stock_quantity`, `stock_alert_threshold`),

    CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_products_brand` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_products_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: product_variants
-- Variantes de produits (taille, couleur, etc.)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `product_variants` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` INT UNSIGNED NOT NULL,
    `sku` VARCHAR(50) NOT NULL COMMENT 'SKU unique de la variante',
    `barcode` VARCHAR(20) DEFAULT NULL,
    `attributes` JSON NOT NULL COMMENT '{"taille": "M", "couleur": "Rouge"}',
    `price_adjustment` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Ajustement prix vs produit parent',
    `stock_quantity` INT NOT NULL DEFAULT 0,
    `image_path` VARCHAR(255) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_sku` (`sku`),
    UNIQUE KEY `uk_barcode` (`barcode`),
    INDEX `idx_product` (`product_id`),

    CONSTRAINT `fk_variants_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: promotions
-- Promotions et remises
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `promotions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `description` TEXT DEFAULT NULL,
    `type` ENUM('percentage', 'fixed_amount', 'fixed_price', 'buy_x_get_y') NOT NULL,
    `value` DECIMAL(10,2) NOT NULL COMMENT 'Valeur de la remise',
    `buy_quantity` INT DEFAULT NULL COMMENT 'Pour buy_x_get_y: quantité à acheter',
    `get_quantity` INT DEFAULT NULL COMMENT 'Pour buy_x_get_y: quantité offerte',

    -- Conditions
    `min_quantity` INT DEFAULT NULL,
    `min_amount` DECIMAL(10,2) DEFAULT NULL,
    `applicable_days` JSON DEFAULT NULL COMMENT '[1,2,3,4,5] = lun-ven',

    -- Période de validité
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `start_time` TIME DEFAULT NULL,
    `end_time` TIME DEFAULT NULL,

    -- Application
    `apply_to` ENUM('all', 'category', 'product', 'brand') NOT NULL DEFAULT 'product',
    `target_ids` JSON DEFAULT NULL COMMENT 'IDs des cibles',

    `priority` INT NOT NULL DEFAULT 0 COMMENT 'Priorité (plus élevé = prioritaire)',
    `is_cumulative` TINYINT(1) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_dates` (`start_date`, `end_date`),
    INDEX `idx_active` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: customers
-- Fichier clients
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `customers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `customer_code` VARCHAR(20) NOT NULL COMMENT 'Code client unique',
    `type` ENUM('individual', 'company') NOT NULL DEFAULT 'individual',

    -- Identité
    `company_name` VARCHAR(255) DEFAULT NULL,
    `siret` VARCHAR(14) DEFAULT NULL,
    `vat_number` VARCHAR(20) DEFAULT NULL,
    `first_name` VARCHAR(100) DEFAULT NULL,
    `last_name` VARCHAR(100) DEFAULT NULL,

    -- Contact
    `email` VARCHAR(255) DEFAULT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `mobile` VARCHAR(20) DEFAULT NULL,

    -- Adresse
    `address_line1` VARCHAR(255) DEFAULT NULL,
    `address_line2` VARCHAR(255) DEFAULT NULL,
    `postal_code` VARCHAR(10) DEFAULT NULL,
    `city` VARCHAR(100) DEFAULT NULL,
    `country` VARCHAR(2) DEFAULT 'FR',

    -- Fidélité
    `loyalty_points` INT NOT NULL DEFAULT 0,
    `loyalty_tier` ENUM('bronze', 'silver', 'gold', 'platinum') DEFAULT 'bronze',

    -- Métadonnées
    `notes` TEXT DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_customer_code` (`customer_code`),
    INDEX `idx_email` (`email`),
    INDEX `idx_name` (`last_name`, `first_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: cash_sessions
-- Sessions de caisse (ouverture/fermeture)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `cash_sessions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `terminal_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL COMMENT 'Caissier',
    `session_number` VARCHAR(20) NOT NULL COMMENT 'Numéro de session',

    -- Ouverture
    `opened_at` DATETIME NOT NULL,
    `opening_amount` DECIMAL(10,2) NOT NULL COMMENT 'Fond de caisse ouverture',
    `opening_signature` VARCHAR(64) NOT NULL,

    -- Fermeture
    `closed_at` DATETIME DEFAULT NULL,
    `closing_amount_expected` DECIMAL(10,2) DEFAULT NULL COMMENT 'Montant théorique',
    `closing_amount_actual` DECIMAL(10,2) DEFAULT NULL COMMENT 'Montant réel compté',
    `closing_difference` DECIMAL(10,2) DEFAULT NULL COMMENT 'Écart',
    `closing_signature` VARCHAR(64) DEFAULT NULL,

    -- Statistiques
    `total_sales` DECIMAL(12,2) DEFAULT 0.00,
    `total_refunds` DECIMAL(12,2) DEFAULT 0.00,
    `total_cancellations` DECIMAL(12,2) DEFAULT 0.00,
    `transaction_count` INT DEFAULT 0,

    -- Totaux par mode de paiement (JSON)
    `payment_totals` JSON DEFAULT NULL,

    -- Statut
    `status` ENUM('open', 'closed', 'suspended') NOT NULL DEFAULT 'open',
    `z_ticket_number` VARCHAR(20) DEFAULT NULL,
    `z_ticket_generated_at` DATETIME DEFAULT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_session_number` (`session_number`),
    INDEX `idx_terminal` (`terminal_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_opened` (`opened_at`),

    CONSTRAINT `fk_sessions_terminal` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`id`),
    CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: transactions
-- Transactions (ventes, avoirs, annulations) - TABLE CRITIQUE NF525
-- Cette table est INALTÉRABLE après insertion
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_number` VARCHAR(30) NOT NULL COMMENT 'Numéro unique séquentiel',
    `cash_session_id` INT UNSIGNED NOT NULL,
    `terminal_id` INT UNSIGNED NOT NULL,
    `user_id` INT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,

    -- Type de transaction
    `type` ENUM('sale', 'refund', 'cancel') NOT NULL DEFAULT 'sale',
    `status` ENUM('completed', 'pending', 'cancelled') NOT NULL DEFAULT 'completed',

    -- Référence transaction liée (pour avoir/annulation)
    `parent_transaction_id` BIGINT UNSIGNED DEFAULT NULL,

    -- Montants
    `total_ht` DECIMAL(12,2) NOT NULL,
    `total_vat` DECIMAL(12,2) NOT NULL,
    `total_ttc` DECIMAL(12,2) NOT NULL,
    `total_discount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,

    -- Détail TVA par taux (JSON)
    `vat_breakdown` JSON NOT NULL COMMENT '{"20.00": {"ht": 100, "vat": 20}, ...}',

    -- Commentaire
    `notes` TEXT DEFAULT NULL,

    -- SIGNATURES NF525 - INALTÉRABLES
    `previous_signature` VARCHAR(64) DEFAULT NULL COMMENT 'Signature transaction N-1',
    `signature` VARCHAR(64) NOT NULL COMMENT 'Signature de cette transaction',
    `signature_counter` BIGINT UNSIGNED NOT NULL COMMENT 'Compteur séquentiel absolu',

    -- Horodatage
    `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) COMMENT 'Précision microseconde',

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_transaction_number` (`transaction_number`),
    UNIQUE KEY `uk_signature` (`signature`),
    UNIQUE KEY `uk_signature_counter` (`signature_counter`),
    INDEX `idx_session` (`cash_session_id`),
    INDEX `idx_terminal` (`terminal_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_customer` (`customer_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_parent` (`parent_transaction_id`),

    CONSTRAINT `fk_transactions_session` FOREIGN KEY (`cash_session_id`) REFERENCES `cash_sessions` (`id`),
    CONSTRAINT `fk_transactions_terminal` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`id`),
    CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
    CONSTRAINT `fk_transactions_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`),
    CONSTRAINT `fk_transactions_parent` FOREIGN KEY (`parent_transaction_id`) REFERENCES `transactions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: transaction_items
-- Lignes de transaction - INALTÉRABLES
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `transaction_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_id` BIGINT UNSIGNED NOT NULL,
    `product_id` INT UNSIGNED DEFAULT NULL,
    `variant_id` INT UNSIGNED DEFAULT NULL,

    -- Copie des données produit au moment de la vente (historisation)
    `product_reference` VARCHAR(50) NOT NULL,
    `product_name` VARCHAR(255) NOT NULL,
    `product_barcode` VARCHAR(20) DEFAULT NULL,

    -- Quantité et prix
    `quantity` DECIMAL(10,3) NOT NULL COMMENT 'Décimal pour produits au poids',
    `unit_price_ht` DECIMAL(10,2) NOT NULL,
    `vat_rate` DECIMAL(5,2) NOT NULL,
    `unit_price_ttc` DECIMAL(10,2) NOT NULL,

    -- Remise sur ligne
    `discount_type` ENUM('none', 'percentage', 'fixed') DEFAULT 'none',
    `discount_value` DECIMAL(10,2) DEFAULT 0.00,
    `discount_amount` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Montant remise calculé',

    -- Totaux ligne
    `line_total_ht` DECIMAL(12,2) NOT NULL,
    `line_total_vat` DECIMAL(12,2) NOT NULL,
    `line_total_ttc` DECIMAL(12,2) NOT NULL,

    -- Promotion appliquée
    `promotion_id` INT UNSIGNED DEFAULT NULL,
    `promotion_name` VARCHAR(255) DEFAULT NULL,

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_transaction` (`transaction_id`),
    INDEX `idx_product` (`product_id`),

    CONSTRAINT `fk_items_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`),
    CONSTRAINT `fk_items_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
    CONSTRAINT `fk_items_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: payments
-- Paiements (peut y avoir plusieurs paiements par transaction)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_id` BIGINT UNSIGNED NOT NULL,

    `method` ENUM('cash', 'card', 'check', 'meal_voucher', 'transfer', 'credit_note', 'other') NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,

    -- Détails spécifiques selon méthode
    `reference` VARCHAR(100) DEFAULT NULL COMMENT 'N° chèque, N° autorisation CB, etc.',
    `details` JSON DEFAULT NULL,

    -- Pour espèces
    `amount_given` DECIMAL(10,2) DEFAULT NULL,
    `change_returned` DECIMAL(10,2) DEFAULT NULL,

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_transaction` (`transaction_id`),
    INDEX `idx_method` (`method`),

    CONSTRAINT `fk_payments_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: invoices
-- Factures générées (PDF stockés)
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `invoices` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_number` VARCHAR(30) NOT NULL,
    `transaction_id` BIGINT UNSIGNED NOT NULL,
    `customer_id` INT UNSIGNED DEFAULT NULL,

    `type` ENUM('invoice', 'credit_note', 'receipt') NOT NULL DEFAULT 'invoice',

    -- Montants
    `total_ht` DECIMAL(12,2) NOT NULL,
    `total_vat` DECIMAL(12,2) NOT NULL,
    `total_ttc` DECIMAL(12,2) NOT NULL,

    -- Échéance
    `due_date` DATE DEFAULT NULL,
    `payment_terms` VARCHAR(255) DEFAULT NULL,

    -- Fichier PDF
    `pdf_path` VARCHAR(255) NOT NULL,
    `pdf_hash` VARCHAR(64) NOT NULL COMMENT 'SHA256 du PDF pour vérification',

    -- Signature
    `signature` VARCHAR(64) NOT NULL,

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_invoice_number` (`invoice_number`),
    INDEX `idx_transaction` (`transaction_id`),
    INDEX `idx_customer` (`customer_id`),

    CONSTRAINT `fk_invoices_transaction` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`),
    CONSTRAINT `fk_invoices_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: stock_movements
-- Mouvements de stock
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `stock_movements` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `product_id` INT UNSIGNED NOT NULL,
    `variant_id` INT UNSIGNED DEFAULT NULL,

    `type` ENUM('sale', 'refund', 'purchase', 'adjustment', 'transfer', 'loss', 'inventory') NOT NULL,
    `quantity` INT NOT NULL COMMENT 'Positif = entrée, négatif = sortie',
    `stock_before` INT NOT NULL,
    `stock_after` INT NOT NULL,

    `reference_type` VARCHAR(50) DEFAULT NULL COMMENT 'transaction, purchase_order, etc.',
    `reference_id` BIGINT UNSIGNED DEFAULT NULL,

    `notes` TEXT DEFAULT NULL,
    `user_id` INT UNSIGNED DEFAULT NULL,

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    INDEX `idx_product` (`product_id`),
    INDEX `idx_variant` (`variant_id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_created` (`created_at`),

    CONSTRAINT `fk_movements_product` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
    CONSTRAINT `fk_movements_variant` FOREIGN KEY (`variant_id`) REFERENCES `product_variants` (`id`),
    CONSTRAINT `fk_movements_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: jet_audit_log
-- Journal d'Événements Techniques - CRITIQUE NF525
-- Cette table est STRICTEMENT INALTÉRABLE
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `jet_audit_log` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_datetime` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `event_type` VARCHAR(50) NOT NULL,
    `event_code` VARCHAR(20) NOT NULL,

    -- Contexte
    `user_id` INT UNSIGNED DEFAULT NULL,
    `terminal_id` INT UNSIGNED DEFAULT NULL,
    `session_id` INT UNSIGNED DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,

    -- Données de l'événement
    `event_data` JSON NOT NULL,

    -- CHAÎNAGE NF525
    `previous_signature` VARCHAR(64) DEFAULT NULL,
    `signature` VARCHAR(64) NOT NULL,
    `signature_counter` BIGINT UNSIGNED NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_signature` (`signature`),
    UNIQUE KEY `uk_signature_counter` (`signature_counter`),
    INDEX `idx_datetime` (`event_datetime`),
    INDEX `idx_type` (`event_type`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_terminal` (`terminal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: archives
-- Index des archives périodiques
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `archives` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `type` ENUM('daily', 'monthly', 'yearly', 'manual') NOT NULL,
    `period_start` DATE NOT NULL,
    `period_end` DATE NOT NULL,

    -- Fichier archive
    `file_path` VARCHAR(255) NOT NULL,
    `file_hash` VARCHAR(64) NOT NULL,
    `file_size` BIGINT UNSIGNED NOT NULL,

    -- Statistiques
    `transaction_count` INT NOT NULL,
    `total_sales` DECIMAL(15,2) NOT NULL,
    `total_vat` DECIMAL(15,2) NOT NULL,

    -- Signatures
    `first_signature` VARCHAR(64) NOT NULL COMMENT 'Première signature de la période',
    `last_signature` VARCHAR(64) NOT NULL COMMENT 'Dernière signature de la période',
    `archive_signature` VARCHAR(64) NOT NULL COMMENT 'Signature de l\'archive elle-même',

    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_by` INT UNSIGNED DEFAULT NULL,

    PRIMARY KEY (`id`),
    INDEX `idx_type` (`type`),
    INDEX `idx_period` (`period_start`, `period_end`),

    CONSTRAINT `fk_archives_user` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- TABLE: settings
-- Paramètres système
-- -----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category` VARCHAR(50) NOT NULL,
    `key` VARCHAR(100) NOT NULL,
    `value` TEXT DEFAULT NULL,
    `type` ENUM('string', 'integer', 'float', 'boolean', 'json') NOT NULL DEFAULT 'string',
    `description` VARCHAR(255) DEFAULT NULL,
    `is_system` TINYINT(1) NOT NULL DEFAULT 0 COMMENT 'Paramètre système non modifiable',
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_category_key` (`category`, `key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- DONNÉES INITIALES
-- -----------------------------------------------------------------------------

-- Paramètres par défaut
INSERT INTO `settings` (`category`, `key`, `value`, `type`, `description`, `is_system`) VALUES
('general', 'installed_at', NULL, 'string', 'Date d\'installation', 1),
('general', 'version', '1.0.0', 'string', 'Version du logiciel', 1),
('general', 'signature_counter', '0', 'integer', 'Compteur de signatures', 1),
('general', 'jet_counter', '0', 'integer', 'Compteur JET', 1),
('invoice', 'next_invoice_number', '1', 'integer', 'Prochain numéro de facture', 0),
('invoice', 'next_credit_note_number', '1', 'integer', 'Prochain numéro d\'avoir', 0),
('invoice', 'payment_terms', 'Paiement à réception', 'string', 'Conditions de paiement par défaut', 0),
('invoice', 'late_penalty_rate', '3', 'float', 'Taux pénalités de retard (x taux légal)', 0),
('receipt', 'footer_line1', 'Merci de votre visite !', 'string', 'Pied de ticket ligne 1', 0),
('receipt', 'footer_line2', '', 'string', 'Pied de ticket ligne 2', 0),
('stock', 'default_alert_threshold', '5', 'integer', 'Seuil d\'alerte stock par défaut', 0),
('stock', 'allow_negative_stock', '0', 'boolean', 'Autoriser stock négatif', 0);

SET FOREIGN_KEY_CHECKS = 1;

-- =============================================================================
-- FIN DU SCHÉMA (TRIGGERS CRÉÉS SÉPARÉMENT)
-- =============================================================================