-- Crie o banco e tabelas para v4 Truco Online (MySQL/MariaDB)
CREATE DATABASE IF NOT EXISTS truco CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE truco;

CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(64) PRIMARY KEY,
  email VARCHAR(191) UNIQUE NOT NULL,
  password VARCHAR(191) NOT NULL,
  name VARCHAR(191) NULL,
  isAdmin TINYINT(1) NOT NULL DEFAULT 0,
  banned TINYINT(1) NOT NULL DEFAULT 0,
  createdAt BIGINT NOT NULL,
  birthDate VARCHAR(32) NULL,
  kycCpf VARCHAR(32) NULL,
  kycVerified TINYINT(1) NOT NULL DEFAULT 0,
  selfExcludedUntil BIGINT NULL,
  rating INT NOT NULL DEFAULT 1200,
  wins INT NOT NULL DEFAULT 0,
  losses INT NOT NULL DEFAULT 0,
  playCoins INT NOT NULL DEFAULT 0,
  realBalance INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS txs (
  id VARCHAR(64) PRIMARY KEY,
  userId VARCHAR(64) NOT NULL,
  ts BIGINT NOT NULL,
  currency VARCHAR(8) NOT NULL,
  type VARCHAR(16) NOT NULL,
  amount INT NOT NULL,
  descr VARCHAR(255) NULL,
  ref VARCHAR(128) NULL,
  INDEX(userId),
  FOREIGN KEY (userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
