-- ============================================================
-- Mitrametrik — Skema Database
-- Prinsip: time-series universal (daily_metrics) buat semua angka,
-- content (JSON) buat list (post/video/campaign/keyword),
-- accounts buat multi-akun, data_sources buat koneksi (token terenkripsi).
-- Manual input & API sync sama-sama nulis ke daily_metrics.
-- ============================================================

-- ---------- SISTEM ----------
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(64) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  name VARCHAR(128) NOT NULL,
  role ENUM('admin','viewer') NOT NULL DEFAULT 'viewer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
  k VARCHAR(64) PRIMARY KEY,
  v TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- KONEKSI SUMBER DATA ----------
CREATE TABLE IF NOT EXISTS data_sources (
  source_key VARCHAR(48) PRIMARY KEY,     -- 'meta_ads','instagram','facebook','google_ads','shopee',...
  label VARCHAR(96) NOT NULL,
  mode ENUM('api','upload','off') NOT NULL DEFAULT 'off',
  status VARCHAR(32) NOT NULL DEFAULT 'belum',  -- connected|need_cred|syncing|error|off
  last_sync DATETIME NULL,
  cred_enc TEXT NULL,                     -- token/credential terenkripsi (AES-256-GCM)
  config TEXT NULL                        -- JSON (account ids, dsb)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- akun per platform (1 sumber bisa banyak akun)
CREATE TABLE IF NOT EXISTS accounts (
  id INT AUTO_INCREMENT PRIMARY KEY,
  source_key VARCHAR(48) NOT NULL,
  ext_id VARCHAR(128) NOT NULL,           -- id akun di platform (IG user id, ad account id, page id)
  name VARCHAR(160) NOT NULL,
  selected TINYINT(1) NOT NULL DEFAULT 0,   -- 1 = akun aktif (1 per source_key)
  meta TEXT NULL,                           -- JSON ekstra (igId, page token ref, dll)
  UNIQUE KEY uniq_acc (source_key, ext_id),
  INDEX idx_acc_src (source_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- METRIK TIME-SERIES (universal) ----------
CREATE TABLE IF NOT EXISTS daily_metrics (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tanggal DATE NOT NULL,
  source VARCHAR(64) NOT NULL,            -- key sumber: 'paid_meta','social_instagram',...
  account_id VARCHAR(48) NOT NULL DEFAULT '',  -- akun (ext_id); '' = manual/tanpa akun
  metric VARCHAR(48) NOT NULL,            -- 'impressions','reach','followers','spend','revenue',...
  value DECIMAL(18,2) NOT NULL DEFAULT 0,
  UNIQUE KEY uniq_dms (tanggal, source, account_id, metric),
  INDEX idx_dms_src (source, account_id, tanggal)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- KONTEN / ITEM (post, video, campaign, keyword) ----------
CREATE TABLE IF NOT EXISTS content (
  id INT AUTO_INCREMENT PRIMARY KEY,
  source VARCHAR(64) NOT NULL,
  account_id VARCHAR(48) NOT NULL DEFAULT '',
  type VARCHAR(24) NOT NULL,              -- 'post','video','campaign','keyword','broadcast'
  ext_id VARCHAR(160) NULL,
  title VARCHAR(255) NULL,
  posted_at DATETIME NULL,
  metrics TEXT NULL,                      -- JSON: {reach,likes,comments,ctr,...}
  UNIQUE KEY uniq_content (source, type, account_id, ext_id),
  INDEX idx_content (source, account_id, type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- LOG SYNC API ----------
CREATE TABLE IF NOT EXISTS sync_log (
  id INT AUTO_INCREMENT PRIMARY KEY,
  source_key VARCHAR(48) NOT NULL,
  started_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(16) NOT NULL,            -- ok|error
  message VARCHAR(255) NULL,
  rows_synced INT DEFAULT 0,
  INDEX idx_synclog (source_key, started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- E-COMMERCE (Shopee / TikTok Shop) — data transaksional, mirror sheet GAS
-- Baris mentah disimpan sbg JSON (semua kolom export), + kolom kunci di-extract.
-- ============================================================

-- header kolom per (platform, dtype) — biar tau kolom-kolomnya
CREATE TABLE IF NOT EXISTS ecom_headers (
  platform VARCHAR(24) NOT NULL,
  dtype VARCHAR(24) NOT NULL,
  headers JSON NOT NULL,
  PRIMARY KEY (platform, dtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- mapping kolom per (platform, dtype)
CREATE TABLE IF NOT EXISTS ecom_config (
  platform VARCHAR(24) NOT NULL,
  dtype VARCHAR(24) NOT NULL,
  config JSON NULL,
  PRIMARY KEY (platform, dtype)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;