CREATE DATABASE IF NOT EXISTS apiotom1_ppob CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE apiotom1_ppob;

-- ===========================
-- TABEL PRODUK
-- ===========================
CREATE TABLE produk (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kode VARCHAR(30) NOT NULL UNIQUE,
    nama VARCHAR(150) NOT NULL,
    kategori VARCHAR(100),
    provider VARCHAR(50),
    harga_modal BIGINT DEFAULT 0,
    markup BIGINT DEFAULT 0,
    harga_jual BIGINT DEFAULT 0,
    status ENUM('Aktif','Nonaktif') DEFAULT 'Aktif',
    last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ===========================
-- TABEL TRANSAKSI
-- ===========================
CREATE TABLE transaksi (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(50) UNIQUE,
    username VARCHAR(50),
    kode_produk VARCHAR(30),
    tujuan VARCHAR(50),
    harga BIGINT,
    sn TEXT,
    status VARCHAR(30),
    provider_ref VARCHAR(100),
    response LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ===========================
-- TABEL MUTASI SALDO
-- ===========================
CREATE TABLE mutasi_saldo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    tipe ENUM('Debit','Kredit'),
    nominal BIGINT,
    saldo_awal BIGINT,
    saldo_akhir BIGINT,
    keterangan TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===========================
-- TABEL SETTING
-- ===========================
CREATE TABLE setting (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama_setting VARCHAR(100) UNIQUE,
    nilai TEXT
);

INSERT INTO setting (nama_setting,nilai) VALUES
('maintenance','0'),
('markup_default','500');

-- ===========================
-- TABEL CALLBACK LOG
-- ===========================
CREATE TABLE callback_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data LONGTEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===========================
-- TABEL API LOG
-- ===========================
CREATE TABLE api_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(100),
    request LONGTEXT,
    response LONGTEXT,
    ip_address VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);