USE aihub;

CREATE TABLE IF NOT EXISTS fairness_seeds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  server_seed VARCHAR(128) NOT NULL,
  server_seed_hash VARCHAR(64) NOT NULL,
  client_seed VARCHAR(64) NOT NULL DEFAULT 'default',
  nonce INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  revealed_at DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_active (user_id, is_active)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fairness_rounds (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  reference_id INT NOT NULL,
  reference_table VARCHAR(40) NOT NULL,
  game_code VARCHAR(40) NULL,
  server_seed_hash VARCHAR(64) NOT NULL,
  server_seed_revealed VARCHAR(128) NULL,
  client_seed VARCHAR(64) NOT NULL,
  nonce INT NOT NULL,
  result_hash VARCHAR(64) NOT NULL,
  outcome_json JSON NULL,
  verified TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_fair_ref (reference_table, reference_id),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user (user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS fairness_audit_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NULL,
  admin_id INT NULL,
  action VARCHAR(60) NOT NULL,
  detail JSON NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_created (created_at)
) ENGINE=InnoDB;

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('provably_fair_enabled', '1'),
('provably_fair_min_client_seed_len', '4'),
('provably_fair_max_client_seed_len', '64');

ALTER TABLE crash_rounds
  ADD COLUMN server_seed_hash VARCHAR(64) NULL,
  ADD COLUMN server_seed VARCHAR(128) NULL,
  ADD COLUMN client_seed VARCHAR(64) NULL DEFAULT 'house',
  ADD COLUMN fair_nonce INT NULL,
  ADD COLUMN result_hash VARCHAR(64) NULL;