-- ============================================
-- ABS Esports - F1 Setups Platform
-- Database Schema
-- ============================================

CREATE DATABASE IF NOT EXISTS quentinh_abs_esports CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE quentinh_abs_esports;

-- ============================================
-- Circuits (tracks)
-- ============================================
CREATE TABLE IF NOT EXISTS circuits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,          -- Ex: "Australia"
    slug VARCHAR(100) NOT NULL UNIQUE,   -- Ex: "australia"
    country VARCHAR(100),
    flag_emoji VARCHAR(10),
    track_image VARCHAR(255),            -- chemin image du circuit
    active TINYINT(1) DEFAULT 1,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Setups par circuit
-- ============================================
CREATE TABLE IF NOT EXISTS setups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id INT NOT NULL,
    game_version VARCHAR(20) DEFAULT 'F1 26',  -- F1 25, F1 26...
    setup_type ENUM('safe','esports','inter','wet','career') NOT NULL,
    -- Aerodynamics
    aero_front_q VARCHAR(20),            -- Ex: "11" (Q) ou "16" (fixed)
    aero_rear_q VARCHAR(20),
    aero_front_r VARCHAR(20),            -- Ex: "7" (R)
    aero_rear_r VARCHAR(20),
    -- Transmission
    diff_on_throttle INT,               -- Ex: 100
    diff_off_throttle INT,              -- Ex: 25
    -- Suspension Geometry
    front_camber DECIMAL(5,2),           -- Ex: -3.50
    rear_camber DECIMAL(5,2),            -- Ex: -2.00
    front_toe DECIMAL(5,2),              -- Ex: 0.00
    rear_toe DECIMAL(5,2),               -- Ex: 0.10
    -- Suspension
    front_suspension INT,
    rear_suspension INT,
    front_anti_roll INT,
    rear_anti_roll INT,
    front_ride_height INT,
    rear_ride_height INT,
    -- Brakes
    brake_pressure INT,                  -- Ex: 53
    brake_bias INT,                      -- Ex: 100
    -- Tyres
    front_left_tyre DECIMAL(4,1),        -- Ex: 29.5
    front_right_tyre DECIMAL(4,1),
    rear_left_tyre DECIMAL(4,1),
    rear_right_tyre DECIMAL(4,1),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE,
    UNIQUE KEY unique_circuit_type_version (circuit_id, setup_type, game_version)
);

-- ============================================
-- Stratégies par circuit
-- ============================================
CREATE TABLE IF NOT EXISTS strategies (
    id INT AUTO_INCREMENT PRIMARY KEY,
    circuit_id INT NOT NULL,
    game_version VARCHAR(20) DEFAULT 'F1 26',
    -- Race info
    total_laps INT,
    fuel_load DECIMAL(5,1),              -- Ex: +1.2
    pit_stop_loss_sec INT,               -- Ex: 18
    -- Best strategy
    best_strat_1 VARCHAR(50),            -- Ex: "M-H (LAP 11>14)"
    best_strat_1_optimal_lap INT,        -- Ex: 12
    best_strat_2 VARCHAR(50),            -- Ex: "H-M (LAP 15>17)"
    best_strat_2_optimal_lap INT,        -- Ex: 16
    -- Alternative strategies
    alt_strat_1 VARCHAR(50),             -- Ex: "H-M-M"
    alt_strat_1_optimal_laps VARCHAR(50),-- Ex: "11, 20"
    alt_strat_2 VARCHAR(50),             -- Ex: "M-M-H"
    alt_strat_2_optimal_laps VARCHAR(50),-- Ex: "9, 18"
    -- Tyre life
    soft_life INT,                       -- Ex: 10
    medium_life INT,                     -- Ex: 14
    hard_life INT,                       -- Ex: 17
    soft_compound VARCHAR(10),           -- Ex: "C5"
    medium_compound VARCHAR(10),         -- Ex: "C4"
    hard_compound VARCHAR(10),           -- Ex: "C3"
    -- ERS
    ers_gain_per_lap INT,                -- Ex: 20 (%)
    ers_gain_in_pits INT,                -- Ex: 5 (%)
    -- Qualifying
    quali_tyre_temp_min INT,             -- Ex: 75
    quali_tyre_temp_max INT,             -- Ex: 85
    quali_exit_pits_time VARCHAR(20),    -- Ex: "2:00"
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE,
    UNIQUE KEY unique_circuit_strategy_version (circuit_id, game_version)
);

-- ============================================
-- Produits WooCommerce
-- ============================================
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    woo_product_id INT NOT NULL UNIQUE,  -- ID WooCommerce
    name VARCHAR(255) NOT NULL,
    type ENUM('full_pack_setups_strategies','full_pack_setups','single_track_setups_strategies','single_track_setups','multi_track_setups_strategies','multi_track_setups') NOT NULL,
    includes_strategies TINYINT(1) DEFAULT 0,
    is_full_pack TINYINT(1) DEFAULT 0,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- Association produit ↔ circuits (pour single/multi tracks)
-- ============================================
CREATE TABLE IF NOT EXISTS product_circuits (
    product_id INT NOT NULL,
    circuit_id INT NOT NULL,
    PRIMARY KEY (product_id, circuit_id),
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE CASCADE
);

-- ============================================
-- Clients
-- ============================================
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    discord_tag VARCHAR(100),
    discord_id VARCHAR(30),
    password_hash VARCHAR(255),          -- NULL = pas encore défini le mot de passe
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- ============================================
-- Commandes WooCommerce
-- ============================================
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    woo_order_id INT NOT NULL UNIQUE,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,             -- produit principal acheté
    status ENUM('pending','completed','refunded','cancelled') DEFAULT 'completed',
    total DECIMAL(10,2),
    coupon_code VARCHAR(100),
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- ============================================
-- Accès clients (ce à quoi ils ont droit)
-- ============================================
CREATE TABLE IF NOT EXISTS customer_access (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    circuit_id INT,                      -- NULL = accès à tous les circuits
    includes_strategies TINYINT(1) DEFAULT 0,
    is_full_pack TINYINT(1) DEFAULT 0,
    order_id INT NOT NULL,
    game_version VARCHAR(20) DEFAULT 'F1 26',
    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
    FOREIGN KEY (circuit_id) REFERENCES circuits(id) ON DELETE SET NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- ============================================
-- Tokens de réinitialisation de mot de passe
-- ============================================
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    token VARCHAR(64) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    used TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- ============================================
-- Admins
-- ============================================
CREATE TABLE IF NOT EXISTS admins (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('superadmin','admin','editor') DEFAULT 'editor',
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- ============================================
-- Logs webhook
-- ============================================
CREATE TABLE IF NOT EXISTS webhook_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    woo_order_id INT,
    payload JSON,
    status ENUM('success','error','ignored') DEFAULT 'success',
    message TEXT,
    received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ============================================
-- DATA INITIALE : Circuits F1
-- ============================================
INSERT INTO circuits (name, slug, country, flag_emoji, sort_order) VALUES
('Australia', 'australia', 'Australia', '🇦🇺', 1),
('Bahrain', 'bahrain', 'Bahrain', '🇧🇭', 2),
('Saudi Arabia', 'saudi-arabia', 'Saudi Arabia', '🇸🇦', 3),
('Japan', 'japan', 'Japan', '🇯🇵', 4),
('China', 'china', 'China', '🇨🇳', 5),
('Miami', 'miami', 'USA', '🇺🇸', 6),
('Emilia Romagna', 'emilia-romagna', 'Italy', '🇮🇹', 7),
('Monaco', 'monaco', 'Monaco', '🇲🇨', 8),
('Canada', 'canada', 'Canada', '🇨🇦', 9),
('Spain', 'spain', 'Spain', '🇪🇸', 10),
('Austria', 'austria', 'Austria', '🇦🇹', 11),
('Great Britain', 'great-britain', 'United Kingdom', '🇬🇧', 12),
('Hungary', 'hungary', 'Hungary', '🇭🇺', 13),
('Belgium', 'belgium', 'Belgium', '🇧🇪', 14),
('Netherlands', 'netherlands', 'Netherlands', '🇳🇱', 15),
('Italy', 'italy', 'Italy', '🇮🇹', 16),
('Azerbaijan', 'azerbaijan', 'Azerbaijan', '🇦🇿', 17),
('Singapore', 'singapore', 'Singapore', '🇸🇬', 18),
('USA', 'usa', 'USA', '🇺🇸', 19),
('Mexico', 'mexico', 'Mexico', '🇲🇽', 20),
('Brazil', 'brazil', 'Brazil', '🇧🇷', 21),
('Las Vegas', 'las-vegas', 'USA', '🇺🇸', 22),
('Qatar', 'qatar', 'Qatar', '🇶🇦', 23),
('Abu Dhabi', 'abu-dhabi', 'UAE', '🇦🇪', 24)
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Exemple setup Australia (SAFE) pour F1 26
INSERT INTO setups (circuit_id, game_version, setup_type,
    aero_front_q, aero_rear_q, aero_front_r, aero_rear_r,
    diff_on_throttle, diff_off_throttle,
    front_camber, rear_camber, front_toe, rear_toe,
    front_suspension, rear_suspension, front_anti_roll, rear_anti_roll, front_ride_height, rear_ride_height,
    brake_pressure, brake_bias,
    front_left_tyre, front_right_tyre, rear_left_tyre, rear_right_tyre)
VALUES
((SELECT id FROM circuits WHERE slug='australia'), 'F1 26', 'safe',
    '11', '7', '7', '4', 100, 25,
    -3.50, -2.00, 0.00, 0.10,
    41, 3, 1, 9, 20, 42,
    53, 100,
    29.5, 29.5, 22.5, 22.5)
ON DUPLICATE KEY UPDATE diff_on_throttle=VALUES(diff_on_throttle);

-- Admin par défaut (mot de passe: Admin@ABS2026 — À CHANGER !)
INSERT INTO admins (username, email, password_hash, role) VALUES
('admin', 'admin@abs-esports.com', '$2y$12$LK.examplehashchangethisXXXXXXXXXXXXXXXXXXXXXXXX', 'superadmin')
ON DUPLICATE KEY UPDATE username=username;