-- =============================================================
-- ASA CDN WAHID — Skema Database (Fase 1)
-- Database target: asak8696_asabackup (Rumahweb, TERPISAH TOTAL
-- dari database ASABACKUP Node u817471353_asabackup di Hostinger)
--
-- Cara pakai: import lewat phpMyAdmin -> pilih database
-- asak8696_asabackup -> tab "Import" -> upload file ini -> Go.
-- Aman dijalankan berkali-kali (pakai IF NOT EXISTS).
-- =============================================================

-- ── Tabel 1: catatan file yang tersimpan di ASA CDN WAHID ──────
-- `cache_key` sengaja dibuat PERSIS format yang sama dengan
-- Node (platform:contentId:episode:type) supaya gampang di-cross
-- reference nanti, tapi TIDAK dipakai sebagai nama file fisik
-- (lihat storage_token) karena contentId bisa mengandung karakter
-- base64 (+, /, =) yang tidak aman untuk filesystem/URL.
CREATE TABLE IF NOT EXISTS cdn_files (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  cache_key VARCHAR(600) NOT NULL,
  storage_token CHAR(64) NOT NULL COMMENT 'sha256(cache_key) hex — dipakai sebagai nama file fisik di /storage, aman dari karakter aneh',
  platform VARCHAR(100) NOT NULL,
  content_id VARCHAR(255) NOT NULL,
  episode VARCHAR(50) NOT NULL,
  type VARCHAR(10) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  checksum_sha256 CHAR(64) DEFAULT NULL,
  status ENUM('uploading','ready','failed') NOT NULL DEFAULT 'uploading',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_cache_key (cache_key),
  UNIQUE KEY uniq_storage_token (storage_token),
  KEY idx_platform (platform),
  KEY idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel 2: API key yang sah (buat Node -> ASA CDN WAHID) ─────
-- Key ASLI/plain TIDAK PERNAH disimpan di sini — cuma hash-nya
-- (sha256), sama pola kayak password. Saat request masuk, key dari
-- header di-hash lalu dicocokkan ke kolom key_hash.
CREATE TABLE IF NOT EXISTS cdn_api_keys (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(100) NOT NULL COMMENT 'nama pengenal, mis. "Node cloud.asanonton.com"',
  key_hash CHAR(64) NOT NULL COMMENT 'sha256(api_key_plain) hex',
  key_prefix CHAR(8) NOT NULL COMMENT '8 karakter pertama key asli, buat identifikasi di admin tanpa expose full key',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  last_used_at DATETIME DEFAULT NULL,
  revoked_at DATETIME DEFAULT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_key_hash (key_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Tabel 3: log request (buat audit & debugging) ──────────────
CREATE TABLE IF NOT EXISTS cdn_request_log (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  endpoint VARCHAR(50) NOT NULL COMMENT 'store / exists / delete / status / stream',
  cache_key VARCHAR(600) DEFAULT NULL,
  result ENUM('ok','rejected','error') NOT NULL,
  reason VARCHAR(255) DEFAULT NULL,
  ip VARCHAR(45) DEFAULT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_created (created_at),
  KEY idx_endpoint (endpoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;