CREATE DATABASE IF NOT EXISTS green_color_app
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE green_color_app;

CREATE TABLE roles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(191) NOT NULL,
  email VARCHAR(191) NOT NULL UNIQUE,
  passwordHash VARCHAR(191) NOT NULL,
  roleId INT NOT NULL,
  isBlocked TINYINT(1) NOT NULL DEFAULT 0,
  agentId INT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_users_role FOREIGN KEY (roleId) REFERENCES roles(id)
);

CREATE TABLE wallets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL UNIQUE,
  balance INT NOT NULL DEFAULT 0,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_wallets_user FOREIGN KEY (userId) REFERENCES users(id)
);

CREATE TABLE game_rounds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  roundKey VARCHAR(100) NOT NULL UNIQUE,
  status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
  openAt DATETIME(3) NOT NULL,
  closeAt DATETIME(3) NOT NULL,
  settledAt DATETIME(3) NULL,
  resultColor VARCHAR(20) NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

CREATE TABLE bets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL,
  gameRoundId INT NOT NULL,
  color VARCHAR(20) NOT NULL,
  amount INT NOT NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_bets_user FOREIGN KEY (userId) REFERENCES users(id),
  CONSTRAINT fk_bets_round FOREIGN KEY (gameRoundId) REFERENCES game_rounds(id)
);

CREATE TABLE withdraw_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL,
  amount INT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
  requestedBy VARCHAR(20) NOT NULL,
  approvedBy VARCHAR(20) NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updatedAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_withdraw_user FOREIGN KEY (userId) REFERENCES users(id)
);

CREATE TABLE login_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  userId INT NOT NULL,
  ipAddress VARCHAR(100) NULL,
  userAgent VARCHAR(255) NULL,
  location VARCHAR(255) NULL,
  success TINYINT(1) NOT NULL DEFAULT 1,
  reason VARCHAR(100) NULL,
  createdAt DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  CONSTRAINT fk_login_user FOREIGN KEY (userId) REFERENCES users(id)
);
