-- ============================================================================
--  Reel Royale · Wettmodul – Datenbankstruktur
--  Virtuelle Coins · KEIN Echtgeld · modular & turnierunabhängig
--  Prefix: bet_   (gemeinsame DB arni_shopping)
--  Engine: InnoDB / utf8mb4 – Fremdschlüssel, Status-Felder, Zeitstempel.
-- ============================================================================

SET NAMES utf8mb4;
SET foreign_key_checks = 0;

-- ---------------------------------------------------------------------------
-- 1) Turniere – Container für beliebige Turniere (WM, EM, Liga, …)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_tournaments` (
  `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name`        VARCHAR(150) NOT NULL,
  `slug`        VARCHAR(80)  NOT NULL,
  `description` VARCHAR(500) DEFAULT NULL,
  `emblem`      VARCHAR(16)  DEFAULT '🏆',          -- Emoji/Kürzel
  `status`      ENUM('upcoming','active','finished','archived') NOT NULL DEFAULT 'upcoming',
  `starts_at`   DATETIME DEFAULT NULL,
  `ends_at`     DATETIME DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_slug` (`slug`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 2) Mannschaften – je Turnier
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_teams` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tournament_id` INT UNSIGNED NOT NULL,
  `name`          VARCHAR(120) NOT NULL,
  `short_name`    VARCHAR(8)   DEFAULT NULL,          -- z. B. GER, BRA
  `flag`          VARCHAR(16)  DEFAULT NULL,          -- Flaggen-Emoji
  `group_label`   VARCHAR(8)   DEFAULT NULL,          -- Gruppe A … L
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_group` (`tournament_id`,`group_label`),
  CONSTRAINT `fk_team_tournament` FOREIGN KEY (`tournament_id`)
      REFERENCES `bet_tournaments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 3) Spiele / Partien
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_matches` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tournament_id` INT UNSIGNED NOT NULL,
  `home_team_id`  INT UNSIGNED DEFAULT NULL,
  `away_team_id`  INT UNSIGNED DEFAULT NULL,
  `stage`         ENUM('group','round32','round16','quarter','semi','third','final','other') NOT NULL DEFAULT 'group',
  `match_group`   VARCHAR(8)  DEFAULT NULL,
  `venue`         VARCHAR(150) DEFAULT NULL,
  `kickoff_at`    DATETIME NOT NULL,
  `status`        ENUM('scheduled','live','finished','cancelled','postponed') NOT NULL DEFAULT 'scheduled',
  `home_score`    TINYINT UNSIGNED DEFAULT NULL,
  `away_score`    TINYINT UNSIGNED DEFAULT NULL,
  `ext_ref`       VARCHAR(64) DEFAULT NULL,           -- externe Match-ID (API-Import, Idempotenz)
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_kickoff` (`kickoff_at`),
  KEY `idx_status` (`status`),
  UNIQUE KEY `uniq_ext_ref` (`tournament_id`,`ext_ref`),
  CONSTRAINT `fk_match_tournament` FOREIGN KEY (`tournament_id`)
      REFERENCES `bet_tournaments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_match_home` FOREIGN KEY (`home_team_id`)
      REFERENCES `bet_teams` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_match_away` FOREIGN KEY (`away_team_id`)
      REFERENCES `bet_teams` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 4) Wettmärkte – pro Spiel ODER turnierweit (Spezialwetten: match_id = NULL)
--    Quoten/Optionen werden IMMER serverseitig hier verwaltet.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_markets` (
  `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tournament_id` INT UNSIGNED NOT NULL,
  `match_id`      INT UNSIGNED DEFAULT NULL,          -- NULL = Spezialwette (Outright)
  `type`          VARCHAR(40) NOT NULL,               -- 1x2, double_chance, correct_score, over_under, btts, outright, special
  `title`         VARCHAR(180) NOT NULL,
  `param`         VARCHAR(40) DEFAULT NULL,           -- z. B. Linie 2.5 bei Over/Under
  `status`        ENUM('open','suspended','closed','settled','cancelled') NOT NULL DEFAULT 'open',
  `multi_winner`  TINYINT(1) NOT NULL DEFAULT 0,      -- 1 = mehrere Gewinn-Optionen möglich
  `close_at`      DATETIME DEFAULT NULL,              -- Wettschluss (sonst = kickoff_at)
  `settled_at`    DATETIME DEFAULT NULL,
  `sort`          SMALLINT NOT NULL DEFAULT 0,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tournament` (`tournament_id`),
  KEY `idx_match` (`match_id`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`type`),
  CONSTRAINT `fk_market_tournament` FOREIGN KEY (`tournament_id`)
      REFERENCES `bet_tournaments` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_market_match` FOREIGN KEY (`match_id`)
      REFERENCES `bet_matches` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 5) Wett-Optionen (Quoten) – gehören zu einem Markt
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_options` (
  `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `market_id`  INT UNSIGNED NOT NULL,
  `code`       VARCHAR(40) DEFAULT NULL,              -- 1 / X / 2 / over / under / yes / no / team_id …
  `label`      VARCHAR(160) NOT NULL,
  `odds`       DECIMAL(8,2) NOT NULL DEFAULT 1.00,    -- Quote, serverseitig
  `status`     ENUM('open','won','lost','void') NOT NULL DEFAULT 'open',
  `is_winner`  TINYINT(1) NOT NULL DEFAULT 0,
  `sort`       SMALLINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_market` (`market_id`),
  CONSTRAINT `fk_option_market` FOREIGN KEY (`market_id`)
      REFERENCES `bet_markets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 6) Abgegebene Wetten der Nutzer
--    Quote wird bei Abgabe eingefroren (odds), Auszahlung = stake * odds.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_user_bets` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`       INT UNSIGNED NOT NULL,
  `tournament_id` INT UNSIGNED NOT NULL,
  `market_id`     INT UNSIGNED NOT NULL,
  `option_id`     INT UNSIGNED NOT NULL,
  `match_id`      INT UNSIGNED DEFAULT NULL,
  `stake`         DECIMAL(20,2) NOT NULL,
  `odds`          DECIMAL(8,2)  NOT NULL,             -- eingefrorene Quote
  `potential_win` DECIMAL(20,2) NOT NULL,
  `payout`        DECIMAL(20,2) NOT NULL DEFAULT 0.00,
  `status`        ENUM('open','won','lost','void','cancelled') NOT NULL DEFAULT 'open',
  `placed_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `settled_at`    DATETIME DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_market` (`market_id`),
  KEY `idx_option` (`option_id`),
  KEY `idx_status` (`status`),
  KEY `idx_tournament` (`tournament_id`),
  CONSTRAINT `fk_bet_user` FOREIGN KEY (`user_id`)
      REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bet_market` FOREIGN KEY (`market_id`)
      REFERENCES `bet_markets` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_bet_option` FOREIGN KEY (`option_id`)
      REFERENCES `bet_options` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 7) Coin-Transaktionen – lückenlose, nachvollziehbare Buchhaltung
--    Jede Coin-Bewegung (Einsatz, Gewinn, Erstattung, Admin) wird gebucht.
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `coin_transactions` (
  `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`       INT UNSIGNED NOT NULL,
  `type`          VARCHAR(30) NOT NULL,               -- bet_stake, bet_payout, bet_refund, admin_adjust, …
  `amount`        DECIMAL(20,2) NOT NULL,             -- vorzeichenbehaftet (− Einsatz, + Gewinn)
  `balance_after` DECIMAL(20,2) NOT NULL,
  `ref_type`      VARCHAR(30) DEFAULT NULL,           -- user_bet, market, …
  `ref_id`        BIGINT UNSIGNED DEFAULT NULL,
  `note`          VARCHAR(255) DEFAULT NULL,
  `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_type` (`type`),
  KEY `idx_ref` (`ref_type`,`ref_id`),
  CONSTRAINT `fk_coin_user` FOREIGN KEY (`user_id`)
      REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- 8) Admin-Aktionsprotokoll – Auditierbarkeit aller Eingriffe
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `bet_admin_log` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id`   INT UNSIGNED DEFAULT NULL,
  `admin_name` VARCHAR(80) DEFAULT NULL,
  `action`     VARCHAR(60) NOT NULL,
  `ref_type`   VARCHAR(30) DEFAULT NULL,
  `ref_id`     BIGINT UNSIGNED DEFAULT NULL,
  `detail`     TEXT DEFAULT NULL,                     -- JSON
  `ip`         VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_admin` (`admin_id`),
  KEY `idx_action` (`action`),
  KEY `idx_ref` (`ref_type`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET foreign_key_checks = 1;