-- Light Year Key System schema (MySQL)
CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(32) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(120) NULL,
  role ENUM('admin','reseller') NOT NULL DEFAULT 'reseller',
  status ENUM('active','disabled') NOT NULL DEFAULT 'active',
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  uplink_id INT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX (uplink_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE referrals (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(32) NOT NULL,
  code_hash CHAR(64) NOT NULL UNIQUE,
  set_balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_by INT UNSIGNED NOT NULL,
  used_by INT UNSIGNED NULL,
  used_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX (created_by),
  INDEX (used_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE keys_code (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  game VARCHAR(32) NOT NULL,
  batch_code VARCHAR(40) NULL,
  batch_name VARCHAR(120) NULL,
  user_key VARCHAR(64) NOT NULL UNIQUE,
  duration_hours INT UNSIGNED NOT NULL,
  max_devices INT UNSIGNED NOT NULL,
  devices TEXT NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  registrator_id INT UNSIGNED NOT NULL,
  activated_at DATETIME NULL,
  expired_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX (registrator_id),
  INDEX (game),
  INDEX (batch_code),
  INDEX (activated_at),
  INDEX (expired_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE history (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  key_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  info VARCHAR(255) NOT NULL,
  created_at DATETIME NOT NULL,
  INDEX (key_id),
  INDEX (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE system_settings (
  id TINYINT UNSIGNED PRIMARY KEY,
  mod_name VARCHAR(120) NOT NULL,
  mod_status ENUM('on','off') NOT NULL DEFAULT 'on',
  notice_text TEXT NULL,
  notice_status VARCHAR(120) NULL,
  maintenance_reason VARCHAR(255) NULL,
  allow_self_unbind TINYINT(1) NOT NULL DEFAULT 0,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO system_settings (id, mod_name, mod_status, notice_text, notice_status, maintenance_reason, allow_self_unbind, updated_at)
VALUES (1, '', 'on', '', '', '', 0, NOW())
ON DUPLICATE KEY UPDATE id = id;
