-- Skema database kantor AI (MySQL / MariaDB)
-- Isi: klien, agen, papan tugas, log aktivitas + biaya token, tarif model

SET NAMES utf8mb4;

CREATE TABLE klien (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(120) NOT NULL,
  akun_instagram VARCHAR(80) NULL,
  akun_threads VARCHAR(80) NULL,
  paket ENUM('dasar','menengah','lengkap') NOT NULL DEFAULT 'dasar',
  posting_per_bulan SMALLINT UNSIGNED NOT NULL DEFAULT 12,
  catatan_brand TEXT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1,
  dibuat TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE agen (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  kode VARCHAR(30) NOT NULL UNIQUE,
  nama VARCHAR(80) NOT NULL,
  peran VARCHAR(255) NOT NULL,
  model VARCHAR(60) NOT NULL,
  aktif TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tarif_model (
  model VARCHAR(60) PRIMARY KEY,
  harga_input_per_juta DECIMAL(10,4) NOT NULL,
  harga_output_per_juta DECIMAL(10,4) NOT NULL,
  berlaku_sejak DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE tugas (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  klien_id INT UNSIGNED NOT NULL,
  judul VARCHAR(200) NOT NULL,
  jenis ENUM('rencana','caption','periksa','posting','laporan') NOT NULL,
  platform ENUM('instagram','threads') NULL,
  status ENUM('baru','dikerjakan','review','selesai','ditolak','berhenti') NOT NULL DEFAULT 'baru',
  agen_id INT UNSIGNED NULL,
  isi MEDIUMTEXT NULL,
  putaran_revisi TINYINT UNSIGNED NOT NULL DEFAULT 0,
  maks_revisi TINYINT UNSIGNED NOT NULL DEFAULT 3,
  tanggal_tayang DATETIME NULL,
  disetujui_oleh VARCHAR(80) NULL,
  disetujui_pada DATETIME NULL,
  dibuat TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  diubah TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_status (status),
  KEY idx_klien (klien_id),
  KEY idx_tayang (tanggal_tayang),
  CONSTRAINT fk_tugas_klien FOREIGN KEY (klien_id) REFERENCES klien(id),
  CONSTRAINT fk_tugas_agen FOREIGN KEY (agen_id) REFERENCES agen(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE log_aktivitas (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tugas_id INT UNSIGNED NOT NULL,
  agen_id INT UNSIGNED NULL,
  aksi VARCHAR(60) NOT NULL,
  status_dari VARCHAR(20) NULL,
  status_ke VARCHAR(20) NULL,
  model VARCHAR(60) NULL,
  token_input INT UNSIGNED NOT NULL DEFAULT 0,
  token_output INT UNSIGNED NOT NULL DEFAULT 0,
  biaya_usd DECIMAL(10,6) NOT NULL DEFAULT 0,
  catatan TEXT NULL,
  dibuat TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_tugas (tugas_id),
  KEY idx_dibuat (dibuat),
  CONSTRAINT fk_log_tugas FOREIGN KEY (tugas_id) REFERENCES tugas(id),
  CONSTRAINT fk_log_agen FOREIGN KEY (agen_id) REFERENCES agen(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE VIEW v_biaya_bulanan AS
SELECT
  DATE_FORMAT(l.dibuat, '%Y-%m') AS bulan,
  k.nama AS klien,
  COUNT(DISTINCT t.id) AS jumlah_tugas,
  SUM(l.token_input) AS total_token_input,
  SUM(l.token_output) AS total_token_output,
  SUM(l.biaya_usd) AS total_biaya_usd
FROM log_aktivitas l
JOIN tugas t ON t.id = l.tugas_id
JOIN klien k ON k.id = t.klien_id
GROUP BY bulan, k.id, k.nama;

-- Data awal. Cek harga terbaru di dokumentasi sebelum dipakai.
INSERT INTO agen (kode, nama, peran, model) VALUES
('manajer',   'Agen manajer',   'Membagi tugas dan menggabungkan hasil', 'claude-sonnet-5-5'),
('perencana', 'Agen perencana', 'Menyusun ide dan kalender konten',      'claude-sonnet-5-5'),
('penulis',   'Agen penulis',   'Menulis caption dan teks Threads',      'claude-sonnet-5-5'),
('pemeriksa', 'Agen pemeriksa', 'Mengecek kualitas, gaya bahasa, format','claude-haiku-5-5');

INSERT INTO tarif_model (model, harga_input_per_juta, harga_output_per_juta, berlaku_sejak) VALUES
('claude-sonnet-5-5', 2.0000, 10.0000, '2026-10-30'),
('claude-haiku-5-5',  0.1000,  0.5000, '2026-10-30');

-- Cara menghitung biaya satu panggilan AI (dari token yang dilaporkan API):
-- biaya_usd = (token_input * harga_input_per_juta + token_output * harga_output_per_juta) / 1000000
-- Contoh query untuk mengisi biaya saat mencatat log:
--   SELECT (? * harga_input_per_juta + ? * harga_output_per_juta) / 1000000
--   FROM tarif_model WHERE model = ?;

-- Contoh laporan biaya bulan ini per klien:
--   SELECT * FROM v_biaya_bulanan WHERE bulan = DATE_FORMAT(NOW(), '%Y-%m');