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

CREATE TABLE admins (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(80) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE payment_accounts (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, type ENUM('bank','ewallet','qris') NOT NULL, name VARCHAR(100) NOT NULL, number VARCHAR(120) NOT NULL, fee_type ENUM('percent','fixed') DEFAULT 'fixed', fee_value DECIMAL(12,2) DEFAULT 0, status TINYINT(1) DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE topup_transactions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, token_topup VARCHAR(120) NOT NULL, email_user VARCHAR(190), nama_user VARCHAR(120), nomor_telepon VARCHAR(40), metode VARCHAR(40), jumlah_topup BIGINT UNSIGNED NOT NULL, kode_unik SMALLINT UNSIGNED NOT NULL, total_bayar BIGINT UNSIGNED NOT NULL, status ENUM('pending','success','failed','expired') DEFAULT 'pending', api_response JSON NULL, proof_path VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX(status, created_at), INDEX(total_bayar, status));
CREATE TABLE webhook_logs (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, app_source VARCHAR(100), notif_text TEXT, matched_transaction_id BIGINT UNSIGNED NULL, result VARCHAR(40), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE settings (setting_key VARCHAR(80) PRIMARY KEY, setting_value TEXT NOT NULL);
INSERT INTO admins (username, password_hash) VALUES ('admin', '$2y$10$REPLACE_WITH_PASSWORD_HASH');
INSERT INTO settings VALUES ('invoice_ttl_minutes','15'),('fee_type','fixed'),('fee_value','1000');