-- Base de données pour La Cave des Amis
-- Système de gestion de cave/bar


-- Création de la base
CREATE DATABASE IF NOT EXISTS `cave_management` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `cave_management`;

-- Table de configuration système
CREATE TABLE `settings` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `key_name` VARCHAR(50) NOT NULL UNIQUE,
    `value` TEXT,
    `description` VARCHAR(255),
    INDEX `idx_key_name` (`key_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des tables du restaurant
CREATE TABLE `tables` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `number` VARCHAR(10) NOT NULL UNIQUE,
    `name` VARCHAR(50),
    `capacity` INT DEFAULT 4,
    `status` ENUM('libre', 'occupée', 'attente_paiement') DEFAULT 'libre',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des catégories de produits
CREATE TABLE `categories` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL,
    `description` TEXT,
    `sort_order` INT DEFAULT 0,
    `is_active` BOOLEAN DEFAULT 1,
    INDEX `idx_active_sort` (`is_active`, `sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des produits
CREATE TABLE `products` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `category_id` INT NOT NULL,
    `description` TEXT,
    `price` DECIMAL(10,2) NOT NULL,
    `cost_price` DECIMAL(10,2) DEFAULT 0,
    `stock_quantity` INT DEFAULT 0,
    `min_stock_alert` INT DEFAULT 5,
    `barcode` VARCHAR(50),
    `is_active` BOOLEAN DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`category_id`) REFERENCES `categories`(`id`),
    INDEX `idx_category_active` (`category_id`, `is_active`),
    INDEX `idx_barcode` (`barcode`),
    INDEX `idx_stock` (`stock_quantity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des commandes par table
CREATE TABLE `orders` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `table_id` INT NOT NULL,
    `server_id` INT NOT NULL,
    `status` ENUM('en_cours', 'fermée') DEFAULT 'en_cours',
    `subtotal` DECIMAL(10,2) DEFAULT 0,
    `tax_amount` DECIMAL(10,2) DEFAULT 0,
    `total_amount` DECIMAL(10,2) DEFAULT 0,
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `closed_at` TIMESTAMP NULL,
    FOREIGN KEY (`table_id`) REFERENCES `tables`(`id`),
    FOREIGN KEY (`server_id`) REFERENCES `users`(`id`),
    INDEX `idx_table_status` (`table_id`, `status`),
    INDEX `idx_server` (`server_id`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des articles de commande
CREATE TABLE `order_items` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `order_id` INT NOT NULL,
    `product_id` INT NOT NULL,
    `quantity` INT NOT NULL,
    `unit_price` DECIMAL(10,2) NOT NULL,
    `subtotal` DECIMAL(10,2) NOT NULL,
    `notes` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`),
    INDEX `idx_order` (`order_id`),
    INDEX `idx_product` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des ventes/factures
CREATE TABLE `sales` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `order_id` INT NOT NULL,
    `cashier_id` INT NOT NULL,
    `invoice_number` VARCHAR(50) NOT NULL UNIQUE,
    `subtotal` DECIMAL(10,2) NOT NULL,
    `tax_amount` DECIMAL(10,2) DEFAULT 0,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `payment_method` ENUM('especes', 'mobile_money', 'carte', 'cheque') NOT NULL,
    `payment_reference` VARCHAR(100),
    `payment_status` ENUM('pending', 'completed', 'failed') DEFAULT 'completed',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`),
    FOREIGN KEY (`cashier_id`) REFERENCES `users`(`id`),
    INDEX `idx_invoice` (`invoice_number`),
    INDEX `idx_cashier` (`cashier_id`),
    INDEX `idx_created` (`created_at`),
    INDEX `idx_payment_status` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des mouvements de stock
CREATE TABLE `stock_movements` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `product_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `type` ENUM('entrée', 'sortie', 'ajustement', 'vente') NOT NULL,
    `quantity` INT NOT NULL,
    `unit_cost` DECIMAL(10,2) DEFAULT 0,
    `reference` VARCHAR(100),
    `notes` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`),
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`),
    INDEX `idx_product_type` (`product_id`, `type`),
    INDEX `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Données de configuration par défaut
INSERT INTO `settings` (`key_name`, `value`, `description`) VALUES
('invoice_start_number', '1000', 'Numéro de départ pour les factures'),
('low_stock_threshold', '10', 'Seuil par défaut pour alerte stock bas'),
('auto_logout_minutes', '120', 'Déconnexion automatique après inactivité (minutes)'),
('allow_negative_stock', '0', 'Autoriser stock négatif (0=non, 1=oui)'),
('default_tax_rate', '19.25', 'Taux TVA par défaut (%)');
-- Mot de passe par défaut: admin123