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

-- ===========================
-- TABEL USERS
-- ===========================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    apikey VARCHAR(64) NOT NULL UNIQUE,
    nama VARCHAR(100),
    email VARCHAR(100),
    nohp VARCHAR(20),
    saldo BIGINT DEFAULT 0,
    status ENUM('Aktif','Nonaktif') DEFAULT 'Aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ===========================
-- 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
);

-- ===========================
-- TABEL PROVIDER
-- ===========================
CREATE TABLE provider (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nama VARCHAR(50),
    memberid VARCHAR(30),
    pin VARCHAR(20),
    password VARCHAR(100),
    endpoint VARCHAR(255),
    status ENUM('Aktif','Nonaktif') DEFAULT 'Aktif'
);

INSERT INTO provider
(nama,memberid,pin,password,endpoint,status)
VALUES
('OKCONNECT','OK642992','PIN_ANDA','PASSWORD_ANDA','https://h2h.okeconnect.com/trx','Aktif');