-- https://www.phpmyadmin.net/
--

START TRANSACTION;



-- --------------------------------------------------------

--
-- Table structure for table `bank_statements`
--

CREATE TABLE `bank_statements` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `channel_id` tinyint(3) UNSIGNED NOT NULL,
  `transaction_date` bigint(20) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `description` varchar(500) DEFAULT NULL,
  `ref_number` varchar(100) DEFAULT NULL,
  `balance` decimal(15,2) DEFAULT NULL,
  `direction` enum('credit','debit') NOT NULL,
  `matched_payment_request_id` bigint(20) UNSIGNED DEFAULT NULL,
  `raw_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`raw_data`)),
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `channel`
--

CREATE TABLE `channel` (
  `id` tinyint(3) UNSIGNED NOT NULL,
  `channel_code` varchar(50) NOT NULL,
  `provider` varchar(50) NOT NULL,
  `label` varchar(100) DEFAULT NULL,
  `config` text NOT NULL COMMENT 'encrypted JSON',
  `environment` varchar(20) DEFAULT 'sandbox',
  `fee_flat` decimal(12,2) DEFAULT 0.00,
  `fee_percent` decimal(5,4) DEFAULT 0.0000,
  `is_active` tinyint(1) DEFAULT 1,
  `priority` int(11) NOT NULL DEFAULT 1,
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `channel`
--

INSERT INTO `channel` (`id`, `channel_code`, `provider`, `label`, `config`, `environment`, `fee_flat`, `fee_percent`, `is_active`, `priority`, `created_at`) VALUES
(1, 'direct_va_bni', 'bni_direct', 'BNI VA Langsung', '{}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(2, 'direct_va_bri', 'bri_direct', 'BRI VA Langsung', '{}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(3, 'direct_va_bsi', 'bsi_direct', 'BSI VA Langsung', '{}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(4, 'direct_va_mandiri', 'mandiri_direct', 'Mandiri VA Langsung', '{}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(5, 'direct_va_muamalat', 'muamalat_direct', 'Muamalat VA Langsung', '{}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(6, 'xendit_qris', 'xendit', 'QRIS via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(7, 'xendit_va_bni', 'xendit', 'BNI VA via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(8, 'xendit_va_bri', 'xendit', 'BRI VA via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(9, 'xendit_va_bsi', 'xendit', 'BSI VA via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(10, 'xendit_va_mandiri', 'xendit', 'Mandiri VA via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616),
(11, 'xendit_va_muamalat', 'xendit', 'Muamalat VA via Xendit', '{\"api_key\":\"xnd_development_hLgN057H9rKLpDMsvMK64wbl7WQAHifhLznpERsfLTCCcmOr37lQI7e934idi7C\"}', 'sandbox', 0.00, 0.0000, 1, 1, 1784812616);

-- --------------------------------------------------------

--
-- Table structure for table `idempotency_keys`
--

CREATE TABLE `idempotency_keys` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `tenant_id` char(4) NOT NULL,
  `idempotency_key` varchar(100) NOT NULL,
  `request_hash` varchar(64) NOT NULL COMMENT 'SHA-256 dari request body',
  `response_body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`response_body`)),
  `http_status` smallint(6) NOT NULL DEFAULT 201,
  `created_at` bigint(20) NOT NULL,
  `expired_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `payment_request`
--

CREATE TABLE `payment_request` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `tenant_id` char(4) NOT NULL,
  `channel_id` tinyint(3) UNSIGNED NOT NULL,
  `external_id` varchar(64) NOT NULL,
  `payer_name` varchar(200) DEFAULT NULL,
  `payer_email` varchar(200) DEFAULT NULL,
  `payer_phone` varchar(30) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `fee` decimal(12,2) DEFAULT 0.00,
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=pending, 2=paid, 3=settled, 4=expired, 5=cancelled, 6=refunded',
  `va_number` varchar(24) DEFAULT NULL,
  `payment_url` varchar(500) DEFAULT NULL,
  `qr_string` text DEFAULT NULL,
  `payment_method` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=va, 2=qris, 3=bank_transfer, 4=ewallet',
  `paid_at` bigint(20) DEFAULT NULL,
  `expired_at` bigint(20) DEFAULT NULL,
  `provider_ref` varchar(64) DEFAULT NULL,
  `metadata` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`metadata`)),
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `payment_request`
--

INSERT INTO `payment_request` (`id`, `tenant_id`, `channel_id`, `external_id`, `payer_name`, `payer_email`, `payer_phone`, `description`, `amount`, `fee`, `status`, `va_number`, `payment_url`, `qr_string`, `payment_method`, `paid_at`, `expired_at`, `provider_ref`, `metadata`, `created_at`, `updated_at`) VALUES
(1, 'MOYA', 2, 'LT-PEWE-0000001', 'Pendaftar PewE 1', 'loadtest_pewe_1@example.com', '081200000001', 'Pembayaran Load Test Transaksi #1', 411000.00, 0.00, 2, '2621044037745510', NULL, NULL, 1, 1757225915, 1757311739, 'pr-51031a43-e931', NULL, 1757225339, 1757225915),
(2, 'TSJL', 3, 'LT-PEWE-0000002', 'Pendaftar PewE 2', 'loadtest_pewe_2@example.com', '081200000002', 'Pembayaran Load Test Transaksi #2', 2298000.00, 0.00, 1, '9347312869306020', NULL, NULL, 1, NULL, 1763936795, 'pr-6d7fc2bc-74d9', NULL, 1763850395, 1763850395),
(3, 'MOYA', 4, 'LT-PEWE-0000003', 'Pendaftar PewE 3', 'loadtest_pewe_3@example.com', '081200000003', 'Pembayaran Load Test Transaksi #3', 1496000.00, 0.00, 1, '8890460883850450', NULL, NULL, 1, NULL, 1753589174, 'pr-a78ee866-bc92', NULL, 1753502774, 1753502774),
(4, 'TSJL', 5, 'LT-PEWE-0000004', 'Pendaftar PewE 4', 'loadtest_pewe_4@example.com', '081200000004', 'Pembayaran Load Test Transaksi #4', 1100000.00, 0.00, 2, '1099422158802820', NULL, NULL, 1, 1757537001, 1757616981, 'pr-c75c4a80-517d', NULL, 1757530581, 1757537001),
(5, 'MOYA', 6, 'LT-PEWE-0000005', 'Pendaftar PewE 5', 'loadtest_pewe_5@example.com', '081200000005', 'Pembayaran Load Test Transaksi #5', 1402000.00, 0.00, 2, NULL, NULL, '00020101021226670016COM.XENDIT.WWW01189360091100000000005204581253033605802ID5910TASJIL PEWE6007JAKARTA61051211062070703A016304E4DA', 2, 1765288970, 1765368864, 'pr-3cecfa08-1674', NULL, 1765282464, 1765288970),
(6, 'TSJL', 7, 'LT-PEWE-0000006', 'Pendaftar PewE 6', 'loadtest_pewe_6@example.com', '081200000006', 'Pembayaran Load Test Transaksi #6', 1915000.00, 0.00, 2, '8808355808613195', NULL, NULL, 1, 1774743197, 1774829015, 'pr-40dcbeda-57b5', NULL, 1774742615, 1774743197),
(7, 'MOYA', 8, 'LT-PEWE-0000007', 'Pendaftar PewE 7', 'loadtest_pewe_7@example.com', '081200000007', 'Pembayaran Load Test Transaksi #7', 2299000.00, 0.00, 1, '2621880762579459', NULL, NULL, 1, NULL, 1780517807, 'pr-091c3648-088c', NULL, 1780431407, 1780431407),
(8, 'TSJL', 9, 'LT-PEWE-0000008', 'Pendaftar PewE 8', 'loadtest_pewe_8@example.com', '081200000008', 'Pembayaran Load Test Transaksi #8', 229000.00, 0.00, 2, '9347035777546437', NULL, NULL, 1, 1758421876, 1758507698, 'pr-0f4e102f-87ca', NULL, 1758421298, 1758421876),
(9, 'MOYA', 10, 'LT-PEWE-0000009', 'Pendaftar PewE 9', 'loadtest_pewe_9@example.com', '081200000009', 'Pembayaran Load Test Transaksi #9', 755000.00, 0.00, 1, '8890384034701117', NULL, NULL, 1, NULL, 1756627706, 'pr-86b92821-4714', NULL, 1756541306, 1756541306),
(10, 'TSJL', 11, 'LT-PEWE-0000010', 'Pendaftar PewE 10', 'loadtest_pewe_10@example.com', '081200000010', 'Pembayaran Load Test Transaksi #10', 1055000.00, 0.00, 2, '1099095252155973', NULL, NULL, 1, 1775487003, 1775567351, 'pr-4e4a3db7-92a0', NULL, 1775480951, 1775487003),
(11, 'MOYA', 1, 'LT-PEWE-0000011', 'Pendaftar PewE 11', 'loadtest_pewe_11@example.com', '081200000011', 'Pembayaran Load Test Transaksi #11', 1156000.00, 0.00, 2, '8808305335161758', NULL, NULL, 1, 1756821925, 1756903548, 'pr-ee8a3605-9502', NULL, 1756817148, 1756821925),
(12, 'TSJL', 2, 'LT-PEWE-0000012', 'Pendaftar PewE 12', 'loadtest_pewe_12@example.com', '081200000012', 'Pembayaran Load Test Transaksi #12', 1396000.00, 0.00, 2, '2621198651998215', NULL, NULL, 1, 1769884729, 1769965968, 'pr-32d5aa82-dd50', NULL, 1769879568, 1769884729),
(13, 'MOYA', 3, 'LT-PEWE-0000013', 'Pendaftar PewE 13', 'loadtest_pewe_13@example.com', '081200000013', 'Pembayaran Load Test Transaksi #13', 241000.00, 0.00, 2, '9347315878620565', NULL, NULL, 1, 1753465868, 1753546191, 'pr-eea28920-fa9a', NULL, 1753459791, 1753465868),
(14, 'TSJL', 4, 'LT-PEWE-0000014', 'Pendaftar PewE 14', 'loadtest_pewe_14@example.com', '081200000014', 'Pembayaran Load Test Transaksi #14', 1489000.00, 0.00, 2, '8890057882468237', NULL, NULL, 1, 1767524559, 1767609913, 'pr-a2159476-e8dd', NULL, 1767523513, 1767524559),
(15, 'MOYA', 5, 'LT-PEWE-0000015', 'Pendaftar PewE 15', 'loadtest_pewe_15@example.com', '081200000015', 'Pembayaran Load Test Transaksi #15', 929000.00, 0.00, 2, '1099081423010869', NULL, NULL, 1, 1761388447, 1761473167, 'pr-2673c317-e692', NULL, 1761386767, 1761388447),
(16, 'TSJL', 6, 'LT-PEWE-0000016', 'Pendaftar PewE 16', 'loadtest_pewe_16@example.com', '081200000016', 'Pembayaran Load Test Transaksi #16', 2340000.00, 0.00, 4, NULL, NULL, '00020101021226670016COM.XENDIT.WWW01189360091100000000005204581253033605802ID5910TASJIL PEWE6007JAKARTA61051211062070703A016304C74D', 2, NULL, 1783313534, 'pr-a1eacd5b-7fa9', NULL, 1783227134, 1783227134),
(17, 'MOYA', 7, 'LT-PEWE-0000017', 'Pendaftar PewE 17', 'loadtest_pewe_17@example.com', '081200000017', 'Pembayaran Load Test Transaksi #17', 1491000.00, 0.00, 1, '8808728189289881', NULL, NULL, 1, NULL, 1784175491, 'pr-a28906ab-219b', NULL, 1784089091, 1784089091),
(18, 'TSJL', 8, 'LT-PEWE-0000018', 'Pendaftar PewE 18', 'loadtest_pewe_18@example.com', '081200000018', 'Pembayaran Load Test Transaksi #18', 758000.00, 0.00, 4, '2621456748654067', NULL, NULL, 1, NULL, 1767889919, 'pr-b2486c55-7e29', NULL, 1767803519, 1767803519),
(19, 'MOYA', 9, 'LT-PEWE-0000019', 'Pendaftar PewE 19', 'loadtest_pewe_19@example.com', '081200000019', 'Pembayaran Load Test Transaksi #19', 1210000.00, 0.00, 2, '9347596616596262', NULL, NULL, 1, 1754819936, 1754901653, 'pr-41de53cc-4532', NULL, 1754815253, 1754819936),
(20, 'TSJL', 10, 'LT-PEWE-0000020', 'Pendaftar PewE 20', 'loadtest_pewe_20@example.com', '081200000020', 'Pembayaran Load Test Transaksi #20', 400000.00, 0.00, 2, '8890105205890677', NULL, NULL, 1, 1764959572, 1765045904, 'pr-e5b8b6d6-26af', NULL, 1764959504, 1764959572),
(21, 'TSJL', 9, 'TJKnb4xnpZKPlD4', 'rizfi4641', 'peserta@example.com', NULL, 'Pembayaran Tagihan TSJL-INV-20260723-A0PYL - Seminar Nasional Pertanian 2026 (Langsung)', 50000.00, 0.00, 1, '934799993755', NULL, NULL, 0, NULL, 1784932707, 'pr-17b0a4b1-3398-431c-ba0f-2b7d0321e99b', NULL, 1784821107, 1784821107),
(22, 'TSJL', 6, 'TJYlsVkEtlDcaAj', 'rizfi4641', 'peserta@example.com', NULL, 'Pembayaran Tagihan TSJL-INV-20260723-8EEFZ - Seminar Nasional Pertanian 2026 (Langsung)', 50000.00, 0.00, 1, NULL, NULL, 'some-random-qr-string', 0, NULL, 1784932761, 'pr-929cc869-f850-4738-8c30-9bb6a0787f74', NULL, 1784821161, 1784821161),
(23, 'TSJL', 8, 'TJjZXrRFSMf9Rca', 'rizfi4641', 'peserta@example.com', NULL, 'Pembayaran Tagihan TSJL-INV-20260723-8EEFZ - Seminar Nasional Pertanian 2026 (Langsung)', 50000.00, 0.00, 2, '262159999713152', NULL, NULL, 0, 1784821738, 1784933324, 'pr-9f1ac8ab-90a5-4f01-8c5d-f5b1dcfac8b4', NULL, 1784821724, 1784821738);

-- --------------------------------------------------------

--
-- Table structure for table `transaction_log`
--

CREATE TABLE `transaction_log` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `payment_request_id` bigint(20) UNSIGNED NOT NULL,
  `event` varchar(50) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`payload`)),
  `created_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `transaction_log`
--

INSERT INTO `transaction_log` (`id`, `payment_request_id`, `event`, `payload`, `created_at`) VALUES
(1, 10, 'resend_webhook', '{\"source\":\"dashboard_resend\",\"delivery_id\":\"2\",\"delivery_status\":2}', 1784819155),
(2, 21, 'tenant_request', '{\"request\":{\"external_id\":\"TJKnb4xnpZKPlD4\",\"amount\":50000,\"payer_name\":\"rizfi4641\",\"payer_email\":\"peserta@example.com\",\"description\":\"Pembayaran Tagihan TSJL-INV-20260723-A0PYL - Seminar Nasional Pertanian 2026 (Langsung)\",\"channel\":\"xendit_va_bsi\",\"payment_method\":\"va\",\"expired_at\":\"2026-07-24 22:38:27\"}}', 1784821107),
(3, 21, 'vendor_request', '{\"reference_id\":\"TJKnb4xnpZKPlD4\",\"amount\":50000,\"currency\":\"IDR\",\"payment_method\":{\"type\":\"VIRTUAL_ACCOUNT\",\"reusability\":\"ONE_TIME_USE\",\"reference_id\":\"TJKnb4xnpZKPlD4\",\"virtual_account\":{\"channel_code\":\"BSI\",\"channel_properties\":{\"customer_name\":\"rizfi4641\",\"expires_at\":\"2026-07-24T22:38:27Z\"}}}}', 1784821107),
(4, 21, 'vendor_response', '{\"status\":201,\"body\":{\"id\":\"pr-17b0a4b1-3398-431c-ba0f-2b7d0321e99b\",\"country\":\"ID\",\"amount\":50000,\"currency\":\"IDR\",\"business_id\":\"5e8e8e1bda59cf4152433a73\",\"reference_id\":\"TJKnb4xnpZKPlD4\",\"payment_method\":{\"id\":\"pm-f05c97a9-c369-4381-8879-1e9c7af24109\",\"type\":\"VIRTUAL_ACCOUNT\",\"reference_id\":\"TJKnb4xnpZKPlD4\",\"description\":null,\"created\":\"2026-07-23T15:38:27.578605412Z\",\"updated\":\"2026-07-23T15:38:27.811235041Z\",\"card\":null,\"ewallet\":null,\"direct_debit\":null,\"direct_bank_transfer\":null,\"over_the_counter\":null,\"virtual_account\":{\"amount\":50000,\"currency\":\"IDR\",\"channel_code\":\"BSI\",\"channel_properties\":{\"customer_name\":\"rizfi4641\",\"virtual_account_number\":\"934799993755\",\"expires_at\":\"2026-07-24T22:38:27Z\"}},\"qr_code\":null,\"metadata\":null,\"billing_information\":{\"country\":\"\",\"street_line1\":null,\"street_line2\":null,\"city\":null,\"province_state\":null,\"postal_code\":null},\"reusability\":\"ONE_TIME_USE\",\"status\":\"PENDING\"},\"description\":null,\"metadata\":null,\"customer_id\":null,\"capture_method\":\"AUTOMATIC\",\"initiator\":null,\"card_verification_results\":null,\"created\":\"2026-07-23T15:38:27.566369887Z\",\"updated\":\"2026-07-23T15:38:27.566369887Z\",\"status\":\"PENDING\",\"actions\":[],\"failure_code\":null,\"channel_properties\":null,\"shipping_information\":null,\"items\":null}}', 1784821107),
(5, 21, 'tenant_response', '{\"response\":{\"payment_request_id\":\"21\",\"external_id\":\"TJKnb4xnpZKPlD4\",\"status\":\"pending\",\"amount\":50000,\"fee\":0,\"total\":50000,\"channel\":\"xendit_va_bsi\",\"payment_method\":\"va\",\"va_number\":\"934799993755\",\"qr_string\":null,\"payment_url\":null,\"expired_at\":1784932707},\"http_code\":201}', 1784821107),
(6, 22, 'tenant_request', '{\"request\":{\"external_id\":\"TJYlsVkEtlDcaAj\",\"amount\":50000,\"payer_name\":\"rizfi4641\",\"payer_email\":\"peserta@example.com\",\"description\":\"Pembayaran Tagihan TSJL-INV-20260723-8EEFZ - Seminar Nasional Pertanian 2026 (Langsung)\",\"channel\":\"xendit_qris\",\"payment_method\":\"va\",\"expired_at\":\"2026-07-24 22:39:21\"}}', 1784821161),
(7, 22, 'vendor_request', '{\"reference_id\":\"TJYlsVkEtlDcaAj\",\"amount\":50000,\"currency\":\"IDR\",\"payment_method\":{\"type\":\"QR_CODE\",\"reusability\":\"ONE_TIME_USE\",\"reference_id\":\"TJYlsVkEtlDcaAj\",\"qr_code\":{\"channel_code\":\"QRIS\"}}}', 1784821161),
(8, 22, 'vendor_response', '{\"status\":201,\"body\":{\"id\":\"pr-929cc869-f850-4738-8c30-9bb6a0787f74\",\"country\":\"ID\",\"amount\":50000,\"currency\":\"IDR\",\"business_id\":\"5e8e8e1bda59cf4152433a73\",\"reference_id\":\"TJYlsVkEtlDcaAj\",\"payment_method\":{\"id\":\"pm-71c2f5c8-36ef-4b3c-bb1f-8c51203fb8c9\",\"type\":\"QR_CODE\",\"reference_id\":\"TJYlsVkEtlDcaAj\",\"description\":null,\"created\":\"2026-07-23T15:39:21.582010934Z\",\"updated\":\"2026-07-23T15:39:21.582010934Z\",\"card\":null,\"ewallet\":null,\"direct_debit\":null,\"direct_bank_transfer\":null,\"over_the_counter\":null,\"virtual_account\":null,\"qr_code\":{\"amount\":50000,\"currency\":\"IDR\",\"channel_code\":\"QRIS\",\"channel_properties\":{\"qr_string\":\"some-random-qr-string\"}},\"metadata\":null,\"billing_information\":{\"country\":\"\",\"street_line1\":null,\"street_line2\":null,\"city\":null,\"province_state\":null,\"postal_code\":null},\"reusability\":\"ONE_TIME_USE\",\"status\":\"ACTIVE\"},\"description\":null,\"metadata\":null,\"customer_id\":null,\"capture_method\":\"AUTOMATIC\",\"initiator\":null,\"card_verification_results\":null,\"created\":\"2026-07-23T15:39:21.439100781Z\",\"updated\":\"2026-07-23T15:39:21.439100781Z\",\"status\":\"PENDING\",\"actions\":[],\"failure_code\":null,\"channel_properties\":null,\"shipping_information\":null,\"items\":null}}', 1784821161),
(9, 22, 'tenant_response', '{\"response\":{\"payment_request_id\":\"22\",\"external_id\":\"TJYlsVkEtlDcaAj\",\"status\":\"pending\",\"amount\":50000,\"fee\":0,\"total\":50000,\"channel\":\"xendit_qris\",\"payment_method\":\"qris\",\"va_number\":null,\"qr_string\":\"some-random-qr-string\",\"payment_url\":null,\"expired_at\":1784932761},\"http_code\":201}', 1784821161),
(10, 23, 'tenant_request', '{\"request\":{\"external_id\":\"TJjZXrRFSMf9Rca\",\"amount\":50000,\"payer_name\":\"rizfi4641\",\"payer_email\":\"peserta@example.com\",\"description\":\"Pembayaran Tagihan TSJL-INV-20260723-8EEFZ - Seminar Nasional Pertanian 2026 (Langsung)\",\"channel\":\"xendit_va_bri\",\"payment_method\":\"va\",\"expired_at\":\"2026-07-24 22:48:44\"}}', 1784821724),
(11, 23, 'vendor_request', '{\"reference_id\":\"TJjZXrRFSMf9Rca\",\"amount\":50000,\"currency\":\"IDR\",\"payment_method\":{\"type\":\"VIRTUAL_ACCOUNT\",\"reusability\":\"ONE_TIME_USE\",\"reference_id\":\"TJjZXrRFSMf9Rca\",\"virtual_account\":{\"channel_code\":\"BRI\",\"channel_properties\":{\"customer_name\":\"rizfi4641\",\"expires_at\":\"2026-07-24T22:48:44Z\"}}}}', 1784821724),
(12, 23, 'vendor_response', '{\"status\":201,\"body\":{\"id\":\"pr-9f1ac8ab-90a5-4f01-8c5d-f5b1dcfac8b4\",\"country\":\"ID\",\"amount\":50000,\"currency\":\"IDR\",\"business_id\":\"5e8e8e1bda59cf4152433a73\",\"reference_id\":\"TJjZXrRFSMf9Rca\",\"payment_method\":{\"id\":\"pm-b7611158-5caa-400d-9b93-2000afdf4db2\",\"type\":\"VIRTUAL_ACCOUNT\",\"reference_id\":\"TJjZXrRFSMf9Rca\",\"description\":null,\"created\":\"2026-07-23T15:48:44.76631876Z\",\"updated\":\"2026-07-23T15:48:45.120115445Z\",\"card\":null,\"ewallet\":null,\"direct_debit\":null,\"direct_bank_transfer\":null,\"over_the_counter\":null,\"virtual_account\":{\"amount\":50000,\"currency\":\"IDR\",\"channel_code\":\"BRI\",\"channel_properties\":{\"customer_name\":\"rizfi4641\",\"virtual_account_number\":\"262159999713152\",\"expires_at\":\"2026-07-24T22:48:44Z\"}},\"qr_code\":null,\"metadata\":null,\"billing_information\":{\"country\":\"\",\"street_line1\":null,\"street_line2\":null,\"city\":null,\"province_state\":null,\"postal_code\":null},\"reusability\":\"ONE_TIME_USE\",\"status\":\"PENDING\"},\"description\":null,\"metadata\":null,\"customer_id\":null,\"capture_method\":\"AUTOMATIC\",\"initiator\":null,\"card_verification_results\":null,\"created\":\"2026-07-23T15:48:44.752747581Z\",\"updated\":\"2026-07-23T15:48:44.752747581Z\",\"status\":\"PENDING\",\"actions\":[],\"failure_code\":null,\"channel_properties\":null,\"shipping_information\":null,\"items\":null}}', 1784821725),
(13, 23, 'tenant_response', '{\"response\":{\"payment_request_id\":\"23\",\"external_id\":\"TJjZXrRFSMf9Rca\",\"status\":\"pending\",\"amount\":50000,\"fee\":0,\"total\":50000,\"channel\":\"xendit_va_bri\",\"payment_method\":\"va\",\"va_number\":\"262159999713152\",\"qr_string\":null,\"payment_url\":null,\"expired_at\":1784933324},\"http_code\":201}', 1784821725),
(14, 23, 'simulated_payment', '{\"source\":\"dashboard_simulation\",\"message\":\"Simulated incoming provider webhook\",\"provider_payload\":{\"external_id\":\"TJjZXrRFSMf9Rca\",\"amount\":\"50000.00\",\"status\":\"PAID\",\"paid_at\":1784821738,\"payment_method\":0,\"channel\":8}}', 1784821738);

-- --------------------------------------------------------

--
-- Table structure for table `va_pool`
--

CREATE TABLE `va_pool` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `channel_id` tinyint(3) UNSIGNED NOT NULL,
  `va_number` varchar(24) NOT NULL,
  `provider_ref` varchar(64) DEFAULT NULL,
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=available, 2=assigned, 3=expired',
  `assigned_payment_request_id` bigint(20) UNSIGNED DEFAULT NULL,
  `created_at` bigint(20) NOT NULL,
  `assigned_at` bigint(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `webhook_deliveries`
--

CREATE TABLE `webhook_deliveries` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `payment_request_id` bigint(20) UNSIGNED NOT NULL,
  `tenant_id` char(4) NOT NULL,
  `event` varchar(50) NOT NULL,
  `payload` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`payload`)),
  `attempt` int(11) DEFAULT 0,
  `max_attempts` int(11) DEFAULT 6,
  `next_retry_at` bigint(20) DEFAULT NULL,
  `last_response_code` smallint(6) DEFAULT NULL,
  `last_response_body` text DEFAULT NULL,
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT 1 COMMENT '1=pending, 2=delivered, 3=failed',
  `created_at` bigint(20) NOT NULL,
  `updated_at` bigint(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `webhook_deliveries`
--

INSERT INTO `webhook_deliveries` (`id`, `payment_request_id`, `tenant_id`, `event`, `payload`, `attempt`, `max_attempts`, `next_retry_at`, `last_response_code`, `last_response_body`, `status`, `created_at`, `updated_at`) VALUES
(1, 10, 'TSJL', 'payment_request.paid', '{\"payment_request_id\":\"10\",\"external_id\":\"LT-PEWE-0000010\",\"status\":\"paid\",\"amount\":\"1055000.00\",\"paid_at\":1775487003,\"payment_method\":\"va\",\"bank\":\"xendit_va_muamalat\",\"va_number\":\"1099095252155973\",\"qr_string\":null}', 1, 6, 1784818721, 200, '{\"status\":\"not_found\",\"message\":\"No payment attempt found for external_id LT-PEWE-0000010\"}', 2, 1784818721, 1784818721),
(2, 10, 'TSJL', 'payment_request.paid', '{\"payment_request_id\":\"10\",\"external_id\":\"LT-PEWE-0000010\",\"status\":\"paid\",\"amount\":\"1055000.00\",\"paid_at\":1775487003,\"payment_method\":\"va\",\"bank\":\"xendit_va_muamalat\",\"va_number\":\"1099095252155973\",\"qr_string\":null}', 1, 6, 1784819155, 200, '{\"status\":\"not_found\",\"message\":\"No payment attempt found for external_id LT-PEWE-0000010\"}', 2, 1784819155, 1784819155),
(3, 23, 'TSJL', 'payment_request.paid', '{\"payment_request_id\":\"23\",\"external_id\":\"TJjZXrRFSMf9Rca\",\"status\":\"paid\",\"amount\":\"50000.00\",\"paid_at\":1784821738,\"payment_method\":\"va\",\"bank\":\"xendit_va_bri\",\"va_number\":\"262159999713152\",\"qr_string\":null}', 1, 6, 1784821738, 200, '{\"status\":\"success\",\"message\":\"Processed status paid for external_id TJjZXrRFSMf9Rca\"}', 2, 1784821738, 1784821738);

--
-- Indexes for table `bank_statements`
--
ALTER TABLE `bank_statements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_unmatched` (`channel_id`,`matched_payment_request_id`,`direction`),
  ADD KEY `idx_amount` (`amount`);

--
-- Indexes for table `channel`
--
ALTER TABLE `channel`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_channel_code` (`channel_code`);

--
-- Indexes for table `idempotency_keys`
--
ALTER TABLE `idempotency_keys`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_tenant_key` (`tenant_id`,`idempotency_key`),
  ADD KEY `idx_expired` (`expired_at`);

--
-- Indexes for table `payment_request`
--
ALTER TABLE `payment_request`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_tenant_external` (`tenant_id`,`external_id`),
  ADD KEY `idx_tenant_status_created` (`tenant_id`,`status`,`created_at`),
  ADD KEY `idx_status_expired` (`status`,`expired_at`),
  ADD KEY `idx_channel_status` (`channel_id`,`status`),
  ADD KEY `idx_provider_ref` (`provider_ref`),
  ADD KEY `idx_created_at` (`created_at`),
  ADD KEY `idx_status_amount` (`status`,`amount`);

--
-- Indexes for table `transaction_log`
--
ALTER TABLE `transaction_log`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_invoice` (`payment_request_id`);

--
-- Indexes for table `va_pool`
--
ALTER TABLE `va_pool`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `uk_va` (`channel_id`,`va_number`),
  ADD KEY `idx_available` (`channel_id`,`status`);

--
-- Indexes for table `webhook_deliveries`
--
ALTER TABLE `webhook_deliveries`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_retry` (`status`,`next_retry_at`),
  ADD KEY `idx_pr` (`payment_request_id`),
  ADD KEY `fk_wd_tenant` (`tenant_id`);

--
-- AUTO_INCREMENT for table `bank_statements`
--
ALTER TABLE `bank_statements`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `channel`
--
ALTER TABLE `channel`
  MODIFY `id` tinyint(3) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `idempotency_keys`
--
ALTER TABLE `idempotency_keys`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `payment_request`
--
ALTER TABLE `payment_request`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=24;

--
-- AUTO_INCREMENT for table `transaction_log`
--
ALTER TABLE `transaction_log`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `va_pool`
--
ALTER TABLE `va_pool`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `webhook_deliveries`
--
ALTER TABLE `webhook_deliveries`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `bank_statements`
--
ALTER TABLE `bank_statements`
  ADD CONSTRAINT `fk_bs_channel` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`);

--
-- Constraints for table `idempotency_keys`
--
ALTER TABLE `idempotency_keys`
  ADD CONSTRAINT `fk_ik_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenant` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `payment_request`
--
ALTER TABLE `payment_request`
  ADD CONSTRAINT `fk_pr_channel` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`),
  ADD CONSTRAINT `fk_pr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenant` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `transaction_log`
--
ALTER TABLE `transaction_log`
  ADD CONSTRAINT `fk_tl_pr` FOREIGN KEY (`payment_request_id`) REFERENCES `payment_request` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `va_pool`
--
ALTER TABLE `va_pool`
  ADD CONSTRAINT `fk_vapool_channel` FOREIGN KEY (`channel_id`) REFERENCES `channel` (`id`);

--
-- Constraints for table `webhook_deliveries`
--
ALTER TABLE `webhook_deliveries`
  ADD CONSTRAINT `fk_wd_pr` FOREIGN KEY (`payment_request_id`) REFERENCES `payment_request` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `fk_wd_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenant` (`id`) ON DELETE CASCADE;
COMMIT;

