-- Création de la base de données (optionnel si tu l'as déjà fait)
CREATE DATABASE IF NOT EXISTS faux_paris_fiflouz DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE faux_paris_fiflouz;

-- 1. Table des utilisateurs connectés par Discord
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    discord_id VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(255) NULL,
    fiflouzes INT DEFAULT 200,
    last_daily_claim TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 2. Table de l'historique brut des soutiens (ton cron de scrap l'alimente)
CREATE TABLE IF NOT EXISTS history_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    captured_at TIMESTAMP NOT NULL UNIQUE,
    soutiens_count INT NOT NULL
) ENGINE=InnoDB;

-- 3. Table des Paris (générés 24h à l'avance)
CREATE TABLE IF NOT EXISTS bets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    target_time TIMESTAMP NOT NULL UNIQUE, 
    central_score INT NOT NULL,          
    lower_bound INT NOT NULL,            
    upper_bound INT NOT NULL,            
    cote_less NUMERIC(5,2) DEFAULT 3.00, 
    cote_between NUMERIC(5,2) DEFAULT 3.00,
    cote_more NUMERIC(5,2) DEFAULT 3.00,  
    status ENUM('open', 'closed', 'resolved') DEFAULT 'open',
    winning_option ENUM('less', 'between', 'more') NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- 4. Table des Mises des joueurs
CREATE TABLE IF NOT EXISTS user_bets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    bet_id INT NOT NULL,
    amount INT NOT NULL,                 
    chosen_option ENUM('less', 'between', 'more') NOT NULL,
    cote_at_bet NUMERIC(5,2) NOT NULL,   
    placed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (bet_id) REFERENCES bets(id) ON DELETE CASCADE
) ENGINE=InnoDB;