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

-- Table de configuration système
CREATE TABLE settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key_name VARCHAR(50) NOT NULL UNIQUE,
    value TEXT,
    description VARCHAR(255)
);

-- Table des tables du restaurant
CREATE TABLE tables (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    number VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(50),
    capacity INTEGER DEFAULT 4,
    status VARCHAR(20) DEFAULT 'libre' CHECK(status IN ('libre', 'occupée', 'attente_paiement')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des catégories de produits
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT 1
);

-- Table des produits
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    category_id INTEGER NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) DEFAULT 0,
    stock_quantity INTEGER DEFAULT 0,
    min_stock_alert INTEGER DEFAULT 5,
    barcode VARCHAR(50),
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- Table des commandes par table
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    table_id INTEGER NOT NULL,
    server_id INTEGER NOT NULL,
    status VARCHAR(20) DEFAULT 'en_cours' CHECK(status IN ('en_cours', 'fermée')),
    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,
    closed_at TIMESTAMP NULL,
    FOREIGN KEY (table_id) REFERENCES tables(id),
    FOREIGN KEY (server_id) REFERENCES users(id)
);

-- Table des articles de commande
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER 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)
);

-- Table des ventes/factures
CREATE TABLE sales (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    cashier_id INTEGER 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 VARCHAR(20) NOT NULL CHECK(payment_method IN ('especes', 'mobile_money', 'carte', 'cheque')),
    payment_reference VARCHAR(100),
    payment_status VARCHAR(20) DEFAULT 'completed' CHECK(payment_status IN ('pending', 'completed', 'failed')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (cashier_id) REFERENCES users(id)
);

-- Table des mouvements de stock
CREATE TABLE stock_movements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    type VARCHAR(20) NOT NULL CHECK(type IN ('entrée', 'sortie', 'ajustement', 'vente')),
    quantity INTEGER 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)
);
CREATE INDEX idx_tables_status ON tables(status);
CREATE INDEX idx_categories_active_sort ON categories(is_active, sort_order);
CREATE INDEX idx_products_category_active ON products(category_id, is_active);
CREATE INDEX idx_products_barcode ON products(barcode);
CREATE INDEX idx_products_stock ON products(stock_quantity);
CREATE INDEX idx_orders_table_status ON orders(table_id, status);
CREATE INDEX idx_orders_server ON orders(server_id);
CREATE INDEX idx_orders_created ON orders(created_at);
CREATE INDEX idx_order_items_order ON order_items(order_id);
CREATE INDEX idx_order_items_product ON order_items(product_id);
CREATE INDEX idx_sales_invoice ON sales(invoice_number);
CREATE INDEX idx_sales_cashier ON sales(cashier_id);
CREATE INDEX idx_sales_created ON sales(created_at);
CREATE INDEX idx_sales_payment_status ON sales(payment_status);
CREATE INDEX idx_stock_movements_product_type ON stock_movements(product_id, type);
CREATE INDEX idx_stock_movements_created ON stock_movements(created_at);

-- 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 (%)');