-- migrations/001_init.sql

CREATE TABLE cirokaget (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ciro_kaget VARCHAR(50) NOT NULL,
  jumlah_saldo BIGINT NOT NULL,
  saldo_per_user BIGINT NOT NULL,
  target INT NOT NULL,
  tipe ENUM('acak','rata') NOT NULL,
  token VARCHAR(32) NOT NULL UNIQUE,
  status ENUM('aktif','proses','nonaktif') NOT NULL DEFAULT 'aktif',
  sukses_x INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX (status),
  INDEX (token)
);

CREATE TABLE cirokaget_x (
  id INT AUTO_INCREMENT PRIMARY KEY,
  cirokaget_id INT NOT NULL,
  nama_user VARCHAR(255),
  email_user VARCHAR(255),
  id_user VARCHAR(100),
  nomor_telepon VARCHAR(50),
  foto_profil TEXT,
  token_x VARCHAR(32) NOT NULL,
  tanggal_diambil DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  status_diambil TINYINT(1) NOT NULL DEFAULT 1,
  jumlah_saldo_diambil BIGINT NOT NULL,
  urutan_ambil INT NOT NULL,
  penambahan_saldo TINYINT(1) NOT NULL DEFAULT 0, -- 0=belum,1=sudah,2=in_progress
  eksternal_response JSON DEFAULT NULL,
  UNIQUE (cirokaget_id, id_user),
  INDEX (cirokaget_id),
  INDEX (token_x)
);

-- table untuk menyimpan log notifikasi/telegram jika perlu
CREATE TABLE cirokaget_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  level VARCHAR(20),
  message TEXT,
  payload JSON DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);