CREATE DATABASE IF NOT EXISTS ppob_db;
USE ppob_db;

-- Tabel Admin
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    balance DECIMAL(15, 2) DEFAULT 0.00,
    api_key VARCHAR(64) UNIQUE,
    role ENUM('admin', 'client') DEFAULT 'client',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Produk
CREATE TABLE products (
    sku VARCHAR(50) PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50) NOT NULL,
    harga_modal DECIMAL(15, 2) DEFAULT 0.00,
    price DECIMAL(15, 2) DEFAULT 0.00,
    status ENUM('active', 'inactive') DEFAULT 'active',
    last_sync DATETIME NULL
);

-- Tabel Transaksi
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(50) UNIQUE NOT NULL,
    user_id INT,
    client_name VARCHAR(50),
    customer_no VARCHAR(50) NOT NULL,
    sku VARCHAR(50) NOT NULL,
    price DECIMAL(15, 2) NOT NULL,
    status ENUM('pending', 'success', 'failed') DEFAULT 'pending',
    sn VARCHAR(100),
    message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Tabel Log Webhook
CREATE TABLE webhook_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ref_id VARCHAR(50),
    event_type VARCHAR(20),
    payload TEXT,
    received_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Tabel Pengaturan Sistem
CREATE TABLE settings (
    setting_key VARCHAR(50) PRIMARY KEY,
    setting_value TEXT NOT NULL
);

-- Data Default
INSERT INTO users (username, password, balance, api_key, role) VALUES 
('admin', MD5('admin123'), 0, NULL, 'admin'),
('client1', MD5('password123'), 100000.00, 'APIKEY-CLIENT1-RAHASIA', 'client');

INSERT INTO settings (setting_key, setting_value) VALUES 
('server_deposit', '0'),
('markup_global', '500'),
('last_sync', 'Belum pernah'),
('webhook_secret', 'GANTI_INI_DENGAN_STRING_ACAK_123');