-- ══════════════════════════════════════════════════════════════════
-- MIGRATION COMPLÈTE JETEGAGEQUE
-- À exécuter UNE SEULE FOIS dans phpMyAdmin ou MySQL
-- ══════════════════════════════════════════════════════════════════

-- 1. Fixer le type config_value (TEXT pour supporter les templates email)
ALTER TABLE admin_config MODIFY COLUMN config_value TEXT DEFAULT NULL;

-- 2. Colonnes manquantes users
ALTER TABLE users ADD COLUMN IF NOT EXISTS theme_id INT DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS interaction_points INT DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS nickname VARCHAR(50) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone VARCHAR(20) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS phone_private TINYINT(1) DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS email_private TINYINT(1) DEFAULT 1;
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_private TINYINT(1) DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS wallet_private TINYINT(1) DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS font_preference VARCHAR(50) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS profile_bg_image VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS recovery_code VARCHAR(255) DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_spin_at TIMESTAMP NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS role_status ENUM('online','offline','dnd') DEFAULT 'online';
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_admin_store TINYINT(1) DEFAULT 0;
ALTER TABLE users ADD COLUMN IF NOT EXISTS renewal_date DATE DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- 3. Colonnes manquantes bets
ALTER TABLE bets ADD COLUMN IF NOT EXISTS acceptance_status ENUM('pending_p2','accepted','refused','cancelled') DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS honored TINYINT(1) DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS not_honored TINYINT(1) DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS hidden TINYINT(1) DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS is_global TINYINT(1) DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS vote_oui INT DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS vote_non INT DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS wallet_stake INT DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS bet_code VARCHAR(10) DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS nb_photos INT DEFAULT 0;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS owner_id INT DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS parieur2_id INT DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS echeance DATETIME DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS cellulaire VARCHAR(20) DEFAULT NULL;
ALTER TABLE bets ADD COLUMN IF NOT EXISTS courriel VARCHAR(150) DEFAULT NULL;

-- 4. Colonnes manquantes messages
ALTER TABLE messages ADD COLUMN IF NOT EXISTS icon_emoji VARCHAR(10) DEFAULT NULL;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS text_color VARCHAR(7) DEFAULT NULL;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS photo_path VARCHAR(255) DEFAULT NULL;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS is_flagged TINYINT(1) DEFAULT 0;
ALTER TABLE messages ADD COLUMN IF NOT EXISTS flag_count INT DEFAULT 0;

-- 5. Colonnes manquantes circles
ALTER TABLE circles ADD COLUMN IF NOT EXISTS oracle_id INT DEFAULT NULL;
ALTER TABLE circles ADD COLUMN IF NOT EXISTS invite_code VARCHAR(20) DEFAULT NULL;

-- 6. Colonnes manquantes chat_icons
ALTER TABLE chat_icons ADD COLUMN IF NOT EXISTS image_path VARCHAR(500) DEFAULT NULL;

-- 7. Colonnes manquantes store_items
ALTER TABLE store_items ADD COLUMN IF NOT EXISTS price_jetons INT DEFAULT 0;

-- 8. Colonnes site_appearance
ALTER TABLE site_appearance ADD COLUMN IF NOT EXISTS is_default TINYINT(1) DEFAULT 0;
ALTER TABLE site_appearance ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- 9. Nouvelles tables

CREATE TABLE IF NOT EXISTS circle_join_requests (
    id INT AUTO_INCREMENT PRIMARY KEY,
    circle_id INT NOT NULL,
    user_id INT NOT NULL,
    status ENUM('pending','approved','refused') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq(circle_id, user_id)
);

CREATE TABLE IF NOT EXISTS admin_notes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    contenu TEXT NOT NULL,
    categorie VARCHAR(50) DEFAULT 'general',
    priorite ENUM('haute','normale','basse') DEFAULT 'normale',
    statut ENUM('todo','en_cours','fait') DEFAULT 'todo',
    auteur_id INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS commandites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nom VARCHAR(100) NOT NULL,
    niveau ENUM('bronze','argent','or','platine') DEFAULT 'bronze',
    logo_url VARCHAR(500) DEFAULT NULL,
    site_url VARCHAR(500) DEFAULT NULL,
    slogan VARCHAR(255) DEFAULT NULL,
    date_debut DATE DEFAULT NULL,
    date_fin DATE DEFAULT NULL,
    afficher_dashboard TINYINT(1) DEFAULT 1,
    afficher_marquise TINYINT(1) DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 10. Valeurs par défaut admin_config
INSERT IGNORE INTO admin_config (config_key, config_value) VALUES
('pts_chat', '1'),
('pts_message', '10'),
('pts_vote', '5'),
('pts_login', '2'),
('wallet_enabled', '1'),
('wallet_daily_free', '100'),
('wallet_daily_premium', '150'),
('wallet_daily_premplus', '200'),
('wallet_bet_enabled', '0'),
('wallet_roll_min', '10'),
('wallet_roll_max', '500'),
('wallet_shop_enabled', '1'),
('limit_bets_free', '10'),
('limit_bets_premium', '50'),
('limit_bets_premplus', '200'),
('limit_bets_admin', '9999'),
('limit_friends_free', '20'),
('limit_friends_premium', '100'),
('limit_friends_premplus', '500'),
('limit_friends_admin', '9999'),
('limit_photos_free', '2'),
('limit_photos_premium', '10'),
('limit_photos_premplus', '30'),
('limit_photos_admin', '9999'),
('limit_pm_free', '20'),
('limit_pm_premium', '100'),
('limit_pm_premplus', '500'),
('limit_pm_admin', '9999'),
('limit_circles_free', '1'),
('limit_circles_premium', '3'),
('limit_circles_premplus', '10'),
('limit_circles_admin', '9999'),
('marquee_bg', '#0d0d0d'),
('flash_enabled', '1'),
('flash_duration', '5'),
('flash_delay', '2'),
('block_media', '0');

-- 11. Mettre le premier thème comme défaut
UPDATE site_appearance SET is_default=1 WHERE id=(SELECT MIN(id) FROM (SELECT id FROM site_appearance) t);

-- 12. Index utiles pour performance
CREATE INDEX IF NOT EXISTS idx_bets_user ON bets(user_id);
CREATE INDEX IF NOT EXISTS idx_bets_parieur2 ON bets(parieur2_id);
CREATE INDEX IF NOT EXISTS idx_bets_circle ON bets(circle_id);
CREATE INDEX IF NOT EXISTS idx_bets_status ON bets(status);
CREATE INDEX IF NOT EXISTS idx_notifications_user ON notifications(user_id, is_read);
CREATE INDEX IF NOT EXISTS idx_friends_user ON friends(user_id, status);
CREATE INDEX IF NOT EXISTS idx_messages_circle ON messages(circle_id);
CREATE INDEX IF NOT EXISTS idx_wallet_user ON wallet_transactions(user_id);


-- 13. Table bet_votes (anti double-vote)
CREATE TABLE IF NOT EXISTS bet_votes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    bet_id INT NOT NULL,
    voter_id INT DEFAULT NULL,
    vote ENUM('oui','non','1','0') NOT NULL,
    ip_address VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_user_vote (bet_id, voter_id),
    KEY idx_bet_ip (bet_id, ip_address)
);

-- 14. Table password_resets
CREATE TABLE IF NOT EXISTS password_resets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uniq_token (token),
    KEY idx_user (user_id)
);

-- 15. Table login_attempts (anti brute force)
CREATE TABLE IF NOT EXISTS login_attempts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    attempts INT DEFAULT 1,
    last_attempt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    locked_until DATETIME DEFAULT NULL,
    KEY idx_ip (ip)
);

-- 16. Nettoyer les anciens tokens (à mettre dans le cron)
-- DELETE FROM password_resets WHERE expires_at < NOW() OR used=1;
-- DELETE FROM login_attempts WHERE last_attempt < DATE_SUB(NOW(), INTERVAL 24 HOUR);

-- 17. Champs date de naissance et statut mineur
ALTER TABLE users ADD COLUMN IF NOT EXISTS birthdate DATE DEFAULT NULL;
ALTER TABLE users ADD COLUMN IF NOT EXISTS is_minor TINYINT(1) DEFAULT 0;
