-- Base de données pour Cdiscount Manager
CREATE DATABASE IF NOT EXISTS cdiscount_manager CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE cdiscount_manager;

-- Table des produits
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ean VARCHAR(13) NOT NULL UNIQUE,
    seller_product_id VARCHAR(100) NOT NULL,
    title VARCHAR(255) NOT NULL,
    brand VARCHAR(100),
    description TEXT,
    category VARCHAR(255),
    price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    vat_rate DECIMAL(5,2) DEFAULT 20.00,
    weight DECIMAL(8,2),
    length DECIMAL(8,2),
    width DECIMAL(8,2),
    height DECIMAL(8,2),
    image_url_1 VARCHAR(500),
    image_url_2 VARCHAR(500),
    image_url_3 VARCHAR(500),
    image_url_4 VARCHAR(500),
    shipping_delay INT DEFAULT 3,
    status ENUM('draft', 'pending', 'sent', 'error') DEFAULT 'draft',
    cdiscount_product_id VARCHAR(100),
    cdiscount_status VARCHAR(50),
    cdiscount_status_message TEXT,
    is_online TINYINT(1) DEFAULT 0,
    last_sync DATETIME,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_ean (ean),
    INDEX idx_status (status),
    INDEX idx_seller_product_id (seller_product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des logs d'API
CREATE TABLE IF NOT EXISTS api_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    action VARCHAR(50) NOT NULL,
    request_data TEXT,
    response_data TEXT,
    status_code INT,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product_id (product_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Table des attributs produits (pour les spécifications techniques)
CREATE TABLE IF NOT EXISTS product_attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT NOT NULL,
    attribute_name VARCHAR(100) NOT NULL,
    attribute_value TEXT,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertion de quelques produits d'exemple
INSERT INTO products (ean, seller_product_id, title, brand, description, category, price, quantity, weight, length, width, height, image_url_1, status) VALUES
('3700123456789', 'PROD001', 'Exemple Produit 1', 'Ma Marque', 'Description du produit 1', 'Électronique > Accessoires', 29.99, 10, 0.5, 20, 15, 5, 'https://example.com/image1.jpg', 'draft'),
('3700123456790', 'PROD002', 'Exemple Produit 2', 'Ma Marque', 'Description du produit 2', 'Maison > Décoration', 49.99, 5, 1.2, 30, 25, 10, 'https://example.com/image2.jpg', 'draft');

-- Note: Le mot de passe par défaut est "admin123" - CHANGEZ-LE immédiatement après installation !
