-- Buat database baru (opsional, sesuaikan dengan nama database Anda)
CREATE DATABASE IF NOT EXISTS h2h_system;
USE apiotom1_h2h_system;

-- ==========================================================
-- Database Schema untuk Sistem H2H (OkeConnect x BukaOlShop)
-- ==========================================================

SET FOREIGN_KEY_CHECKS = 0;

-- --------------------------------------------------------
-- 1. Struktur dari tabel `admin_users`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `admin_users`;
CREATE TABLE `admin_users` (
  `id` varchar(36) NOT NULL,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `role` enum('owner','admin') NOT NULL DEFAULT 'admin',
  `status` enum('active','inactive') DEFAULT 'active',
  `lastLogin` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_username` (`username`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Admin users dengan role (owner/admin)';


-- --------------------------------------------------------
-- 2. Struktur dari tabel `integration_settings`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `integration_settings`;
CREATE TABLE `integration_settings` (
  `id` varchar(36) NOT NULL,
  `type` enum('okeconnect','bukaolshop') NOT NULL,
  `memberId` varchar(255) DEFAULT NULL,
  `token` longtext DEFAULT NULL,
  `apiKey` longtext DEFAULT NULL COMMENT 'API Key untuk Closed API atau service credentials',
  `pin` varchar(255) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `encryptedCredentials` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Untuk menyimpan credentials terenkripsi' CHECK (json_valid(`encryptedCredentials`)),
  `isActive` tinyint(1) DEFAULT 1,
  `callbackUrl` varchar(255) DEFAULT NULL,
  `lastSync` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `externalId` varchar(255) DEFAULT NULL COMMENT 'External ID from service (e.g., OkeConnect ID)',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_type` (`type`),
  KEY `idx_isActive` (`isActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Integrasi OkeConnect dan BukaOlShop credentials';


-- --------------------------------------------------------
-- 3. Struktur dari tabel `jobs`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `jobs`;
CREATE TABLE `jobs` (
  `id` char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `type` varchar(255) NOT NULL COMMENT 'Job type: process_transaction, refund_transaction, etc',
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT 'Job data/parameters' CHECK (json_valid(`payload`)),
  `status` enum('pending','processing','completed','failed') DEFAULT 'pending',
  `attempts` int(11) DEFAULT 0 COMMENT 'Number of attempts',
  `maxAttempts` int(11) DEFAULT 3 COMMENT 'Maximum number of retry attempts',
  `nextRetryAt` datetime DEFAULT NULL COMMENT 'Time for next retry (exponential backoff)',
  `error` text DEFAULT NULL COMMENT 'Last error message',
  `result` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT 'Job result data' CHECK (json_valid(`result`)),
  `processedAt` datetime DEFAULT NULL,
  `failedAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_type` (`type`),
  KEY `idx_next_retry_at` (`nextRetryAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- 4. Struktur dari tabel `pricing_rules`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `pricing_rules`;
CREATE TABLE `pricing_rules` (
  `id` varchar(36) NOT NULL,
  `level` enum('global','category','provider','product') NOT NULL COMMENT 'Level penerapan markup',
  `targetId` varchar(100) DEFAULT NULL COMMENT 'Category, Provider, atau Product Code (null untuk global)',
  `markupType` enum('fixed','percentage') NOT NULL DEFAULT 'fixed',
  `markupValue` bigint(20) NOT NULL,
  `isActive` tinyint(1) NOT NULL DEFAULT 1,
  `priority` int(11) NOT NULL DEFAULT 0 COMMENT 'Priority lebih tinggi = lebih diprioritaskan',
  `notes` longtext DEFAULT NULL,
  `createdAt` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedAt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_level` (`level`),
  KEY `idx_targetId` (`targetId`),
  KEY `idx_isActive` (`isActive`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Dynamic pricing rules';


-- --------------------------------------------------------
-- 5. Struktur dari tabel `products` 
-- (Ditambahkan karena dibutuhkan oleh tabel transactions)
-- --------------------------------------------------------
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
  `id` varchar(36) NOT NULL,
  `code` varchar(50) NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `provider` varchar(100) DEFAULT NULL,
  `price` bigint(20) NOT NULL DEFAULT 0,
  `status` enum('active','inactive') DEFAULT 'active',
  `createdAt` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedAt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_code` (`code`),
  KEY `idx_category` (`category`),
  KEY `idx_provider` (`provider`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Daftar produk H2H';


-- --------------------------------------------------------
-- 6. Struktur dari tabel `sessions`
-- (Ditambahkan karena ada permintaan index session)
-- --------------------------------------------------------
DROP TABLE IF EXISTS `sessions`;
CREATE TABLE `sessions` (
  `session_id` varchar(128) NOT NULL,
  `session_data` text NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`session_id`),
  KEY `idx_last_activity` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- --------------------------------------------------------
-- 7. Struktur dari tabel `transactions`
-- --------------------------------------------------------
DROP TABLE IF EXISTS `transactions`;
CREATE TABLE `transactions` (
  `id` varchar(36) NOT NULL,
  `refId` varchar(50) NOT NULL COMMENT 'Reference ID untuk okeconnect',
  `transactionId` varchar(50) DEFAULT NULL COMMENT 'Transaction ID dari okeconnect (T#xxxxx)',
  `idUser` varchar(50) NOT NULL COMMENT 'ID User dari bukaolshop (dari shortcode)',
  `emailUser` varchar(100) DEFAULT NULL COMMENT 'Email user bukaolshop',
  `tokenUser` varchar(255) DEFAULT NULL COMMENT 'Token user bukaolshop untuk validasi',
  `productCode` varchar(50) NOT NULL,
  `destination` varchar(50) NOT NULL COMMENT 'Nomor tujuan (untuk denom), atau tujuan pembayaran',
  `quantity` bigint(20) DEFAULT NULL COMMENT 'Untuk produk open denom',
  `basePrice` bigint(20) NOT NULL COMMENT 'Harga dari okeconnect',
  `sellingPrice` bigint(20) NOT NULL COMMENT 'Harga yang dikenakan ke user bukaolshop',
  `adminProfit` bigint(20) NOT NULL DEFAULT 0 COMMENT 'Selisih harga (sellingPrice - basePrice)',
  `totalAmount` bigint(20) NOT NULL COMMENT 'Total amount yang dibayarkan user',
  `status` enum('pending','processing','success','failed','cancelled') NOT NULL DEFAULT 'pending',
  `statusMessage` longtext DEFAULT NULL COMMENT 'Response dari okeconnect',
  `serialNumber` varchar(100) DEFAULT NULL COMMENT 'Serial number dari okeconnect (SN: xxxxx)',
  `errorCode` varchar(50) DEFAULT NULL,
  `errorMessage` longtext DEFAULT NULL,
  `processedAt` datetime DEFAULT NULL COMMENT 'Waktu ketika transaksi diproses ke okeconnect',
  `completedAt` datetime DEFAULT NULL COMMENT 'Waktu ketika callback diterima dari okeconnect',
  `createdAt` datetime NOT NULL DEFAULT current_timestamp(),
  `updatedAt` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_unique_refId` (`refId`),
  KEY `idx_transactionId` (`transactionId`),
  KEY `idx_idUser` (`idUser`),
  KEY `idx_productCode` (`productCode`),
  KEY `idx_status` (`status`),
  KEY `idx_createdAt` (`createdAt`),
  CONSTRAINT `fk_transactions_productCode` FOREIGN KEY (`productCode`) REFERENCES `products` (`code`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Transaction history dengan user data dari shortcode';

SET FOREIGN_KEY_CHECKS = 1;