CREATE DATABASE IF NOT EXISTS host_sepay
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE host_sepay;

CREATE TABLE IF NOT EXISTS tb_transactions (
    id INT(11) NOT NULL AUTO_INCREMENT,
    gateway VARCHAR(100) NOT NULL,
    transaction_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    account_number VARCHAR(100) DEFAULT NULL,
    sub_account VARCHAR(250) DEFAULT NULL,
    amount_in DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    amount_out DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    accumulated DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    code VARCHAR(250) DEFAULT NULL,
    transaction_content TEXT DEFAULT NULL,
    reference_number VARCHAR(255) DEFAULT NULL,
    body TEXT DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_sepay_transaction_id (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tb_orders (
    id INT(11) NOT NULL AUTO_INCREMENT,
    total DECIMAL(20,2) NOT NULL DEFAULT 0.00,
    payment_status ENUM(
        'Unpaid',
        'Paid',
        'Cancelled',
        'Refunded'
    ) NOT NULL DEFAULT 'Unpaid',
    name VARCHAR(250) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;