-- =================================================================
-- Mr Jus - Schéma de base de données unifié
-- =================================================================
-- Ce fichier contient TOUTES les tables nécessaires pour l'application
-- Compatible avec SQLite (pour MySQL, voir schema.sql)
-- =================================================================

-- Supprimer les tables existantes dans l'ordre inverse des dépendances

-- Table des sessions
CREATE TABLE sessions (
    id VARCHAR(255) PRIMARY KEY,
    user_id INTEGER,
    ip_address VARCHAR(45),
    user_agent TEXT,
    payload TEXT NOT NULL,
    last_activity INTEGER NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Table des produits
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL UNIQUE,
    description TEXT,
    ingredients TEXT,
    nutritional_info TEXT, -- JSON
    health_benefits TEXT,
    price DECIMAL(8,2) NOT NULL,
    volume VARCHAR(50),
    image_main VARCHAR(255),
    image_gallery TEXT, -- JSON
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table du stock des produits
CREATE TABLE product_stock (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL UNIQUE,
    quantity INTEGER NOT NULL DEFAULT 0,
    min_threshold INTEGER NOT NULL DEFAULT 10,
    last_restock_date DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);

-- Table des zones de livraison
CREATE TABLE delivery_zones (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    postal_codes TEXT NOT NULL, -- JSON array
    delivery_fee DECIMAL(6,2) NOT NULL DEFAULT 0,
    min_order_amount DECIMAL(8,2) DEFAULT 0,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des créneaux de livraison
CREATE TABLE delivery_slots (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    day_of_week INTEGER NOT NULL CHECK(day_of_week >= 1 AND day_of_week <= 7),
    start_time VARCHAR(5) NOT NULL,
    end_time VARCHAR(5) NOT NULL,
    max_orders INTEGER DEFAULT 10,
    is_active BOOLEAN DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- =================================================================
-- TABLES DES COMMANDES
-- =================================================================

-- Table des commandes
CREATE TABLE orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    user_id INTEGER,
    status VARCHAR(20) DEFAULT 'pending' CHECK(status IN ('pending', 'confirmed', 'preparing', 'ready', 'delivering', 'delivered', 'cancelled')),
    customer_email VARCHAR(255) NOT NULL,
    customer_first_name VARCHAR(100) NOT NULL,
    customer_last_name VARCHAR(100) NOT NULL,
    customer_phone VARCHAR(20),
    delivery_street_address VARCHAR(255) NOT NULL,
    delivery_postal_code VARCHAR(10) NOT NULL,
    delivery_city VARCHAR(100) NOT NULL,
    delivery_country VARCHAR(50) DEFAULT 'Belgique',
    delivery_notes TEXT,
    delivery_date DATE NOT NULL,
    delivery_slot VARCHAR(20) NOT NULL,
    delivery_fee DECIMAL(6,2) NOT NULL DEFAULT 0,
    subtotal DECIMAL(10,2) NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50),
    payment_status VARCHAR(20) DEFAULT 'pending' CHECK(payment_status IN ('pending', 'paid', 'failed', 'refunded')),
    payment_reference VARCHAR(100),
    paid_at DATETIME,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Table des articles de commande
CREATE TABLE order_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    product_name VARCHAR(255) NOT NULL,
    product_price DECIMAL(8,2) NOT NULL,
    quantity INTEGER NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    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 adresses de livraison
CREATE TABLE delivery_addresses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    order_id INTEGER,
    street_address VARCHAR(255) NOT NULL,
    postal_code VARCHAR(10) NOT NULL,
    city VARCHAR(100) NOT NULL,
    country VARCHAR(50) DEFAULT 'Belgique',
    is_default BOOLEAN DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Table de l'historique des statuts
CREATE TABLE order_status_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    order_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    notes TEXT,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Table des paramètres système
CREATE TABLE settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    setting_type VARCHAR(20) DEFAULT 'string' CHECK(setting_type IN ('string', 'integer', 'decimal', 'boolean', 'json')),
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des logs emails
CREATE TABLE email_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    recipient_email VARCHAR(255) NOT NULL,
    subject VARCHAR(255) NOT NULL,
    template VARCHAR(100),
    status VARCHAR(20) DEFAULT 'pending' CHECK(status IN ('sent', 'failed', 'pending')),
    error_message TEXT,
    sent_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table des logs d'activité
CREATE TABLE activity_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50),
    entity_id INTEGER,
    details TEXT, -- JSON
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- =================================================================
-- TABLES ADDITIONNELLES
-- =================================================================

-- Table des avis
CREATE TABLE reviews (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    order_id INTEGER,
    rating INTEGER NOT NULL CHECK(rating >= 1 AND rating <= 5),
    comment TEXT,
    is_verified BOOLEAN DEFAULT 0,
    helpful_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    UNIQUE(user_id, product_id, order_id)
);

-- Table des mouvements de stock
CREATE TABLE stock_movements (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    product_id INTEGER NOT NULL,
    type VARCHAR(20) NOT NULL CHECK(type IN ('in', 'out', 'adjustment')),
    quantity INTEGER NOT NULL,
    reason VARCHAR(100),
    reference_type VARCHAR(50),
    reference_id INTEGER,
    notes TEXT,
    created_by INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Table des contacts
CREATE TABLE contacts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    subject VARCHAR(255),
    message TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'new' CHECK(status IN ('new', 'read', 'replied', 'archived')),
    replied_at DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_active ON products(is_active);
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_payment ON orders(payment_status);
CREATE INDEX idx_orders_date ON orders(delivery_date);
CREATE INDEX idx_delivery_user ON delivery_addresses(user_id);
CREATE INDEX idx_delivery_order ON delivery_addresses(order_id);
CREATE INDEX idx_delivery_postal ON delivery_addresses(postal_code);
CREATE INDEX idx_logs_user ON activity_logs(user_id);
CREATE INDEX idx_logs_entity ON activity_logs(entity_type, entity_id);
CREATE INDEX idx_logs_created ON activity_logs(created_at);
CREATE INDEX idx_email_logs_recipient ON email_logs(recipient_email);
CREATE INDEX idx_email_logs_status ON email_logs(status);
CREATE INDEX idx_email_logs_created ON email_logs(created_at);
CREATE INDEX idx_settings_key ON settings(setting_key);
CREATE INDEX idx_reviews_product ON reviews(product_id);
CREATE INDEX idx_reviews_user ON reviews(user_id);
CREATE INDEX idx_stock_movements_product ON stock_movements(product_id);
CREATE INDEX idx_stock_movements_created ON stock_movements(created_at);

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

-- Produits
INSERT INTO products (name, slug, description, ingredients, price, volume) VALUES
('Bissap Artisanal', 'bissap-artisanal', 'L''Énergie Rouge de l''Afrique. Notre bissap artisanal est une invitation au voyage gustatif, préparé selon une recette traditionnelle authentique.', 'Hibiscus, ananas, menthe, sucre, eau', 8.50, '1L'),
('Gingembre Pur', 'gingembre-pur', 'Le Shot d''Énergie Naturelle. Notre jus de gingembre artisanal est la quintessence de l''énergie naturelle, élaboré exclusivement à partir de rhizomes de gingembre frais.', 'Gingembre frais, sucre, eau', 6.00, '250ml');

-- Stock initial
INSERT INTO product_stock (product_id, quantity, min_threshold) VALUES
(1, 100, 20),
(2, 100, 20);

-- Zones de livraison
INSERT INTO delivery_zones (name, postal_codes, delivery_fee) VALUES
('Bruxelles Centre', '["1000", "1050", "1060"]', 2.00),
('Ixelles', '["1050"]', 2.50),
('Uccle', '["1180"]', 3.00),
('Schaerbeek', '["1030"]', 3.00),
('Saint-Gilles', '["1060"]', 2.50),
('Etterbeek', '["1040"]', 2.50),
('Anderlecht', '["1070"]', 3.50),
('Forest', '["1190"]', 3.50);

-- Créneaux de livraison
INSERT INTO delivery_slots (day_of_week, start_time, end_time, max_orders) VALUES
(2, '14:00', '18:00', 15), -- Mardi
(4, '14:00', '18:00', 15), -- Jeudi
(6, '10:00', '14:00', 20), -- Samedi matin
(6, '14:00', '18:00', 20); -- Samedi après-midi

-- Paramètres système
INSERT INTO settings (setting_key, setting_value, setting_type, description) VALUES
('min_order_amount', '10', 'decimal', 'Montant minimum de commande'),
('free_delivery_threshold', '25', 'decimal', 'Seuil pour livraison gratuite'),
('order_preparation_days', '2', 'integer', 'Délai de préparation en jours'),
('max_login_attempts', '5', 'integer', 'Tentatives de connexion maximum'),
('session_timeout', '7200', 'integer', 'Timeout session en secondes'),
('company_email', 'contact@mrjus.be', 'string', 'Email de contact'),
('company_phone', '0467850011', 'string', 'Téléphone de contact'),
('company_address', 'Bruxelles, Belgique', 'string', 'Adresse de l''entreprise'),
('order_number_prefix', 'MRJ', 'string', 'Préfixe des numéros de commande');