-- ============================================================================
-- COPER — MULTI-GAME ONLINE BETTING PLATFORM
-- Database: coper
-- Full real-time PHP backend. Server-authoritative ledger + full audit trail.
-- Games: 8-Ball Pool, Checkers, Last Card. Platform: wallet, shop, RG, fairness.
-- ============================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------------------------------------------------------
-- USERS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `username`      VARCHAR(32)  NOT NULL,
  `email`         VARCHAR(160) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `mobile`        VARCHAR(20)  NULL COMMENT 'Tanzanian mobile (+255…) used for deposits/withdrawals',
  `terms_accepted` TINYINT(1)  NOT NULL DEFAULT 0,
  `reset_code_hash`   CHAR(64) NULL COMMENT 'SHA-256 of the password-reset code',
  `reset_code_expires` DATETIME NULL,
  `avatar`        VARCHAR(255) NULL,
  `country`       CHAR(2)      NULL COMMENT 'ISO 3166-1 alpha-2',
  `lang`          CHAR(5)      NOT NULL DEFAULT 'en',
  `role`          ENUM('player','moderator','admin') NOT NULL DEFAULT 'player',
  `status`        ENUM('active','suspended','banned','self_excluded') NOT NULL DEFAULT 'active',
  `kyc_status`    ENUM('unverified','pending','verified','rejected','escalated') NOT NULL DEFAULT 'unverified',
  `age_verified`  TINYINT(1)   NOT NULL DEFAULT 0,
  `self_exclude_until` DATETIME NULL,
  `daily_deposit_limit` DECIMAL(14,2) NULL,
  `daily_stake_limit`   DECIMAL(14,2) NULL,
  `loss_limit`          DECIMAL(14,2) NULL,
  `session_limit_min`   INT UNSIGNED NULL,
  `is_online`     TINYINT(1)   NOT NULL DEFAULT 0,
  `last_seen_at`  TIMESTAMP    NULL,
  `stats_total_matches` INT UNSIGNED NOT NULL DEFAULT 0,
  `stats_wins`          INT UNSIGNED NOT NULL DEFAULT 0,
  `stats_losses`        INT UNSIGNED NOT NULL DEFAULT 0,
  `rating`        INT NOT NULL DEFAULT 1000 COMMENT 'ELO-style rating',
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_users_email` (`email`),
  UNIQUE KEY `uq_users_username` (`username`),
  UNIQUE KEY `uq_users_mobile` (`mobile`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- WALLET  (one account per user; all movement via wallet_ledger)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallet` (
  `user_id`      BIGINT UNSIGNED PRIMARY KEY,
  `currency`     CHAR(3) NOT NULL DEFAULT 'TZS',
  `balance`      DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `bonus`        DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `locked`       DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `total_deposits` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `total_withdrawals` DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `total_wagered`   DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `total_wins`      DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `total_losses`    DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `version`      INT UNSIGNED NOT NULL DEFAULT 0,
  `updated_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_wallet_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- WALLET LEDGER (double-entry, immutable audit trail — source of truth)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `wallet_ledger` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `ref_type`     ENUM('deposit','withdrawal','game_stake','game_payout','bonus_issue',
                      'bonus_release','cancellation','refund','adjustment','fee') NOT NULL,
  `ref_id`       BIGINT UNSIGNED NULL,
  `direction`    ENUM('in','out') NOT NULL,
  `amount`       DECIMAL(16,2) NOT NULL,
  `balance_after` DECIMAL(16,2) NOT NULL,
  `currency`     CHAR(3) NOT NULL DEFAULT 'TZS',
  `meta`         JSON NULL,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_ledger_user_time` (`user_id`,`created_at`),
  INDEX `idx_ledger_ref` (`ref_type`,`ref_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- GAMES (multi-game lobby / matchmaking)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `games` (
  `id`            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `game_type`     ENUM('8ball','checkers','lastcard','blackjack','roulette','crash','darts','poker','other') NOT NULL DEFAULT '8ball',
  `room_name`     VARCHAR(64) NULL,
  `room_code`     CHAR(8)  NULL,
  `creator_id`    BIGINT UNSIGNED NOT NULL,
  `stake`         DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `currency`      CHAR(3) NOT NULL DEFAULT 'TZS',
  `max_players`   TINYINT NOT NULL DEFAULT 2,
  `turn_limit_ms` INT NOT NULL DEFAULT 45000,
  `is_private`    TINYINT(1) NOT NULL DEFAULT 0,
  `state`         ENUM('waiting','ready','in_progress','stakes_held','payout_pending','finished','cancelled','abandoned') NOT NULL DEFAULT 'waiting',
  `winner_id`     BIGINT UNSIGNED NULL,
  `outcome_json`  JSON NULL,
  `starts_at`     TIMESTAMP NULL,
  `ends_at`       TIMESTAMP NULL,
  `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_games_state` (`state`,`game_type`,`created_at`),
  INDEX `idx_games_code` (`room_code`),
  CONSTRAINT `fk_games_creator` FOREIGN KEY (`creator_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `game_players` (
  `game_id`      BIGINT UNSIGNED NOT NULL,
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `seat`         TINYINT NOT NULL DEFAULT 0,
  `team`         VARCHAR(16) NOT NULL DEFAULT 'none' COMMENT 'pool: solids/stripes; checkers: black/red; lastcard: order',
  `stake_held`   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `status`       ENUM('active','forfeit','left','won','lost','draw') NOT NULL DEFAULT 'active',
  `joined_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `payload`      JSON NULL,
  PRIMARY KEY (`game_id`,`user_id`),
  CONSTRAINT `fk_gp_game` FOREIGN KEY (`game_id`) REFERENCES `games`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_gp_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- MATCH STATE (persistent authoritative state per game so reconnect = seamless)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_state` (
  `game_id`   BIGINT UNSIGNED PRIMARY KEY,
  `engine`    VARCHAR(32) NOT NULL DEFAULT '8ball',
  `turn_user_id` BIGINT UNSIGNED NULL,
  `turn_until`   DATETIME NULL,
  `body`      JSON NULL COMMENT 'game-specific authoritative state',
  `seq`       INT UNSIGNED NOT NULL DEFAULT 0,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT `fk_gs_game` FOREIGN KEY (`game_id`) REFERENCES `games`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- MATCH EVENTS (replay / dispute / fair-play auditing)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_events` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `game_id`     BIGINT UNSIGNED NOT NULL,
  `event`       VARCHAR(32) NOT NULL,
  `seq`         INT UNSIGNED NOT NULL,
  `actor_id`    BIGINT UNSIGNED NULL,
  `payload`     JSON NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_events_game` (`game_id`,`seq`),
  CONSTRAINT `fk_events_game` FOREIGN KEY (`game_id`) REFERENCES `games`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- CHAT (in-match and room)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `chat_messages` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `game_id`     BIGINT UNSIGNED NULL,
  `user_id`     BIGINT UNSIGNED NOT NULL,
  `text`        VARCHAR(255) NOT NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_chat_game` (`game_id`,`id`),
  CONSTRAINT `fk_chat_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- DEPOSITS / WITHDRAWALS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payments` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `kind`         ENUM('deposit','withdrawal') NOT NULL,
  `provider`     VARCHAR(32) NOT NULL DEFAULT 'manual',
  `provider_ref` VARCHAR(128) NULL,
  `amount`       DECIMAL(16,2) NOT NULL,
  `fee`          DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `currency`     CHAR(3) NOT NULL DEFAULT 'TZS',
  `status`       ENUM('pending','approved','declined','cancelled','refunded') NOT NULL DEFAULT 'pending',
  `reviewed_by`  BIGINT UNSIGNED NULL,
  `meta`         JSON NULL,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_pay_user` (`user_id`,`created_at`),
  CONSTRAINT `fk_pay_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- SESSIONS (server-side, revocable auth)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `sessions` (
  `id`           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `token_hash`   CHAR(64) NOT NULL,
  `ip`           VARCHAR(45) NULL,
  `user_agent`   VARCHAR(255) NULL,
  `device_id`    VARCHAR(64) NULL,
  `expires_at`   DATETIME NOT NULL,
  `revoked_at`   DATETIME NULL,
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_sess_token` (`token_hash`),
  INDEX `idx_sess_user` (`user_id`),
  CONSTRAINT `fk_sess_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- FRIENDS / BLOCKLIST
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `friendships` (
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `friend_id`    BIGINT UNSIGNED NOT NULL,
  `status`       ENUM('pending','accepted','blocked') NOT NULL DEFAULT 'pending',
  `created_at`   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`friend_id`),
  CONSTRAINT `fk_friend_u` FOREIGN KEY (`user_id`)   REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_friend_f` FOREIGN KEY (`friend_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- TOURNAMENTS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `tournaments` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`        VARCHAR(64) NOT NULL,
  `game_type`   ENUM('8ball','checkers','lastcard','blackjack','roulette','crash','darts','poker','other') NOT NULL DEFAULT '8ball',
  `entry_fee`   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `max_participants` INT UNSIGNED NOT NULL DEFAULT 16,
  `prize_pool`  DECIMAL(16,2) NOT NULL DEFAULT 0.00,
  `status`      ENUM('open','in_progress','completed','cancelled') NOT NULL DEFAULT 'open',
  `starts_at`   TIMESTAMP NULL,
  `ends_at`     TIMESTAMP NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_tour_creator` FOREIGN KEY (`id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- SHOP / INVENTORY (cosmetics — characters & 3D items)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `items` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `kind`        ENUM('cue','table','board','cards','avatar','character','emote','effect','theme') NOT NULL,
  `name`        VARCHAR(64) NOT NULL,
  `sku`         VARCHAR(64) NOT NULL,
  `price`       DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `currency`    CHAR(3) NOT NULL DEFAULT 'TZS',
  `rarity`      ENUM('common','rare','epic','legendary') NOT NULL DEFAULT 'common',
  `glb_url`     VARCHAR(255) NULL,
  `preview_url` VARCHAR(255) NULL,
  `payload`     JSON NULL,
  `is_active`   TINYINT(1) NOT NULL DEFAULT 1,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_items_sku` (`sku`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `inventory` (
  `user_id`   BIGINT UNSIGNED NOT NULL,
  `item_id`   BIGINT UNSIGNED NOT NULL,
  `equipped`  TINYINT(1) NOT NULL DEFAULT 0,
  `acquired_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`,`item_id`),
  CONSTRAINT `fk_inv_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_inv_item` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- NOTIFICATIONS
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`     BIGINT UNSIGNED NOT NULL,
  `kind`        VARCHAR(32) NOT NULL DEFAULT 'info',
  `title`       VARCHAR(128) NOT NULL,
  `body`        VARCHAR(255) NULL,
  `payload`     JSON NULL,
  `is_read`     TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_notif_user` (`user_id`,`created_at`),
  CONSTRAINT `fk_notif_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- ADMIN AUDIT
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_audit` (
  `id`          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `admin_id`    BIGINT UNSIGNED NOT NULL,
  `action`      VARCHAR(64) NOT NULL,
  `target_user` BIGINT UNSIGNED NULL,
  `payload`     JSON NULL,
  `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `fk_audit_admin` FOREIGN KEY (`admin_id`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;