-- file: sql/schema.sql
CREATE TABLE IF NOT EXISTS sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  status ENUM('ACTIVE','PASSIVE') NOT NULL DEFAULT 'ACTIVE',
  betco_host VARCHAR(255) NOT NULL,
  betco_resource VARCHAR(120) NOT NULL,
  betco_sid VARCHAR(60) NOT NULL,
  betco_secret VARCHAR(120) NOT NULL,
  check_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS panel_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('SUPER_ADMIN','FINANCE','ACCOUNTING') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  totp_secret VARCHAR(64) NULL,
  totp_enabled TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL,
  CONSTRAINT fk_panel_users_site FOREIGN KEY (site_id) REFERENCES sites(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ip VARCHAR(64) NOT NULL,
  email VARCHAR(190) NOT NULL,
  attempts INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uk_login_attempts_ip_email (ip, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bank_accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  bank_name VARCHAR(120) NOT NULL,
  iban VARCHAR(64) NOT NULL,
  account_holder VARCHAR(190) NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bank_accounts_site FOREIGN KEY (site_id) REFERENCES sites(id),
  KEY idx_bank_site_active (site_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS deposit_requests (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NOT NULL,
  public_id VARCHAR(32) NOT NULL UNIQUE,
  betco_txn_id BIGINT NOT NULL UNIQUE,
  betco_account VARCHAR(120) NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  selected_bank_account_id INT NULL,
  status ENUM('CREATED','BANK_SHOWN','PENDING','APPROVING','APPROVED','BETCO_FAILED','REJECTED','REJECTED_TIMEOUT') NOT NULL DEFAULT 'CREATED',
  info_text TEXT NULL,
  user_receipt_path VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at DATETIME NOT NULL,
  decided_at DATETIME NULL,
  decided_by INT NULL,
  reject_reason VARCHAR(255) NULL,
  CONSTRAINT fk_deposit_site FOREIGN KEY (site_id) REFERENCES sites(id),
  CONSTRAINT fk_deposit_bank FOREIGN KEY (selected_bank_account_id) REFERENCES bank_accounts(id),
  CONSTRAINT fk_deposit_decider FOREIGN KEY (decided_by) REFERENCES panel_users(id),
  KEY idx_deposit_site_status (site_id, status),
  KEY idx_deposit_created (created_at),
  KEY idx_deposit_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS betco_calls (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  site_id INT NULL,
  deposit_request_id BIGINT NULL,
  command VARCHAR(20) NOT NULL,
  request_url TEXT NOT NULL,
  request_hash VARCHAR(64) NOT NULL,
  http_code INT NULL,
  response_body LONGTEXT NULL,
  is_success TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_betco_site (site_id),
  KEY idx_betco_deposit (deposit_request_id),
  CONSTRAINT fk_betco_site FOREIGN KEY (site_id) REFERENCES sites(id),
  CONSTRAINT fk_betco_deposit FOREIGN KEY (deposit_request_id) REFERENCES deposit_requests(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  actor_user_id INT NULL,
  site_id INT NULL,
  action VARCHAR(120) NOT NULL,
  entity VARCHAR(120) NOT NULL,
  entity_id VARCHAR(64) NULL,
  meta_json TEXT NULL,
  ip VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_audit_site (site_id),
  KEY idx_audit_actor (actor_user_id),
  KEY idx_audit_action (action),
  CONSTRAINT fk_audit_actor FOREIGN KEY (actor_user_id) REFERENCES panel_users(id),
  CONSTRAINT fk_audit_site FOREIGN KEY (site_id) REFERENCES sites(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;