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

SET @OLD_FOREIGN_KEY_CHECKS = @@FOREIGN_KEY_CHECKS;
SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS roulette_plays;
DROP TABLE IF EXISTS roulette_prize_inventory;
DROP TABLE IF EXISTS roulette_segments;
DROP TABLE IF EXISTS roulette_assets;
DROP TABLE IF EXISTS roulettes;
SET FOREIGN_KEY_CHECKS = @OLD_FOREIGN_KEY_CHECKS;

CREATE TABLE roulettes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  bg_image VARCHAR(255) DEFAULT NULL,
  force_pattern ENUM('none','LLW10') DEFAULT 'none',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  start_at DATETIME NULL,
  end_at   DATETIME NULL,
  daily_win_cap INT NULL,
  cooldown_seconds INT NOT NULL DEFAULT 5,
  cost_per_spin DECIMAL(10,2) NULL,
  value_per_spin DECIMAL(10,2) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roulette_assets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roulette_id INT NOT NULL,
  asset_key ENUM('music','tick','inicio','ganhou','perdeu','background') NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  UNIQUE KEY uniq_ra (roulette_id, asset_key),
  CONSTRAINT fk_ra_roulette FOREIGN KEY (roulette_id) REFERENCES roulettes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roulette_segments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roulette_id INT NOT NULL,
  segment_order INT NOT NULL,
  type ENUM('WIN','LOSE') NOT NULL,
  label_primary VARCHAR(32) NOT NULL,
  label_unit VARCHAR(32) NOT NULL,
  spin_count INT NULL,
  color VARCHAR(16) DEFAULT '#bbf7d0',
  weight INT NOT NULL DEFAULT 1,
  game_name VARCHAR(120) DEFAULT NULL,
  UNIQUE KEY uniq_order (roulette_id, segment_order),
  CONSTRAINT fk_rs_roulette FOREIGN KEY (roulette_id) REFERENCES roulettes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roulette_prize_inventory (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roulette_id INT NOT NULL,
  spin_count INT NOT NULL,
  daily_quota INT NULL,
  total_quota INT NULL,
  consumed_today INT NOT NULL DEFAULT 0,
  consumed_total INT NOT NULL DEFAULT 0,
  last_reset DATE NULL,
  UNIQUE KEY uniq_inv (roulette_id, spin_count),
  CONSTRAINT fk_rpi_roulette FOREIGN KEY (roulette_id) REFERENCES roulettes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roulette_plays (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  roulette_id INT NOT NULL,
  segment_id INT NULL,
  result_type ENUM('WIN','LOSE') NOT NULL,
  result_label VARCHAR(64) NOT NULL,
  result_spin_count INT NULL,
  ip VARCHAR(45) NULL,
  user_token VARCHAR(64) NULL,
  seed VARCHAR(64) NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  KEY idx_rp_rid_time (roulette_id, created_at),
  KEY idx_rp_user (user_token),
  KEY idx_rp_ip (ip),
  CONSTRAINT fk_rp_roulette FOREIGN KEY (roulette_id) REFERENCES roulettes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO roulettes (name, slug, is_active, force_pattern)
VALUES ('Roleta Demo', 'demo', 1, 'none');
SET @rid := LAST_INSERT_ID();

INSERT INTO roulette_assets (roulette_id, asset_key, file_path) VALUES
  (@rid, 'music',      '../../uploads/'  /* placeholder, admin fará upload */),
  (@rid, 'tick',       '../../uploads/'  /* placeholder */),
  (@rid, 'inicio',     '../../uploads/'  /* placeholder */),
  (@rid, 'ganhou',     '../../uploads/'  /* placeholder */),
  (@rid, 'perdeu',     '../../uploads/'  /* placeholder */),
  (@rid, 'background', '../../uploads/'  /* placeholder */);

INSERT INTO roulette_segments
(roulette_id, segment_order, type, label_primary, label_unit, spin_count, color, weight, game_name)
VALUES
(@rid, 1, 'LOSE', 'TENTE', 'NOVAMENTE', NULL, '#fecaca', 30, NULL),
(@rid, 2, 'LOSE', 'TENTE', 'NOVAMENTE', NULL, '#fed7aa', 30, NULL),
(@rid, 3, 'WIN',  '10', 'GIROS', 10, '#bbf7d0', 80, 'Seu Jogo'),
(@rid, 4, 'WIN',  '20', 'GIROS', 20, '#93c5fd', 10, 'Seu Jogo'),
(@rid, 5, 'WIN',  '30', 'GIROS', 30, '#e9d5ff', 10, 'Seu Jogo');
