-- Script untuk membuat tabel-tabel Lano Topup di MySQL/phpMyAdmin
-- Cara Pakai: Buat database baru bernama 'lano_topup' di phpMyAdmin, lalu Import file ini.

CREATE DATABASE IF NOT EXISTS `lanotopup`;
USE `lanotopup`;

-- Tabel Pengaturan (Settings) untuk menyimpan API Key Digiflazz
CREATE TABLE IF NOT EXISTS `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `setting_key` varchar(100) NOT NULL,
  `setting_value` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `setting_key` (`setting_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
('digiflazz_username', 'lano_demo'),
('digiflazz_api_key', 'dev-key-12345'),
('markup_pulsa', '150'),
('tripay_api_key', 'DEV-API-KEY'),
('tripay_private_key', 'DEV-PRIVATE-KEY'),
('tripay_merchant_code', 'T0000');

-- Tabel Produk
CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_code` varchar(50) NOT NULL,
  `product_name` varchar(150) NOT NULL,
  `category` varchar(50) NOT NULL,
  `brand` varchar(50) NOT NULL,
  `digiflazz_price` int(11) NOT NULL,
  `sell_price` int(11) NOT NULL,
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `last_sync` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_code` (`product_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `products` (`product_code`, `product_name`, `category`, `brand`, `digiflazz_price`, `sell_price`, `status`) VALUES
('TSEL5', 'Telkomsel 5.000', 'Pulsa', 'Telkomsel', 5050, 5200, 'Active'),
('TSEL10', 'Telkomsel 10.000', 'Pulsa', 'Telkomsel', 10050, 10200, 'Active'),
('PLN20', 'Token PLN 20.000', 'PLN', 'PLN', 20050, 20500, 'Active'),
('OVO50', 'OVO 50.000', 'E-Wallet', 'OVO', 50100, 51000, 'Inactive');

-- Tabel Transaksi
CREATE TABLE IF NOT EXISTS `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(100) NOT NULL,
  `product_code` varchar(50) NOT NULL,
  `target_number` varchar(50) NOT NULL,
  `sell_price` int(11) NOT NULL,
  `status` varchar(20) DEFAULT 'PENDING',
  `digiflazz_ref_id` varchar(100) DEFAULT NULL,
  `tripay_checkout_url` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO `transactions` (`order_id`, `product_code`, `target_number`, `sell_price`, `status`) VALUES
('TRX-001', 'TSEL5', '081234567890', 5200, 'SUCCESS'),
('TRX-002', 'PLN20', '12345678901', 20500, 'SUCCESS'),
('TRX-003', 'OVO50', '08987654321', 51000, 'FAILED');