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

START TRANSACTION;



--
-- Database: `pulsa_db`
--

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

--
-- Table structure for table `produk`
--

CREATE TABLE `produk` (
  `id` int(11) NOT NULL,
  `nama_produk` varchar(100) DEFAULT NULL,
  `kode_supplier` varchar(50) DEFAULT NULL,
  `operator` varchar(20) DEFAULT NULL,
  `nominal` varchar(20) DEFAULT NULL,
  `format_payload` text DEFAULT NULL,
  `status` enum('aktif','nonaktif') DEFAULT 'aktif',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `status_mapping`
--

CREATE TABLE `status_mapping` (
  `id` int(11) NOT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `kode_status` varchar(50) DEFAULT NULL,
  `status_app` enum('sukses','gagal','pending') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `supplier_route`
--

CREATE TABLE `supplier_route` (
  `id` int(11) NOT NULL,
  `produk_id` int(11) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `prioritas` int(11) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `template_payload`
--

CREATE TABLE `template_payload` (
  `id` int(11) NOT NULL,
  `produk_id` int(11) DEFAULT NULL,
  `json_template` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

--
-- Table structure for table `transaksi_log`
--

CREATE TABLE `transaksi_log` (
  `id` bigint(20) NOT NULL,
  `ref_id` varchar(100) DEFAULT NULL,
  `produk_id` int(11) DEFAULT NULL,
  `nomor_tujuan` varchar(20) DEFAULT NULL,
  `supplier_id` int(11) DEFAULT NULL,
  `status` enum('pending','sukses','gagal','retry') DEFAULT 'pending',
  `sn` varchar(100) DEFAULT NULL,
  `jumlah_retry` int(11) DEFAULT 0,
  `waktu_input` datetime DEFAULT current_timestamp(),
  `waktu_respon` datetime DEFAULT NULL,
  `pesan_balasan` text DEFAULT NULL,
  `raw_request` text DEFAULT NULL,
  `raw_response` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `produk`
--
ALTER TABLE `produk`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `status_mapping`
--
ALTER TABLE `status_mapping`
  ADD PRIMARY KEY (`id`),
  ADD KEY `supplier_id` (`supplier_id`);

--
-- Indexes for table `supplier_route`
--
ALTER TABLE `supplier_route`
  ADD PRIMARY KEY (`id`),
  ADD KEY `produk_id` (`produk_id`),
  ADD KEY `supplier_id` (`supplier_id`);

--
-- Indexes for table `template_payload`
--
ALTER TABLE `template_payload`
  ADD PRIMARY KEY (`id`),
  ADD KEY `produk_id` (`produk_id`);

--
-- Indexes for table `transaksi_log`
--
ALTER TABLE `transaksi_log`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ref_id` (`ref_id`),
  ADD KEY `produk_id` (`produk_id`),
  ADD KEY `supplier_id` (`supplier_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `produk`
--
ALTER TABLE `produk`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `status_mapping`
--
ALTER TABLE `status_mapping`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `supplier_route`
--
ALTER TABLE `supplier_route`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `template_payload`
--
ALTER TABLE `template_payload`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `status_mapping`
--
ALTER TABLE `status_mapping`
  ADD CONSTRAINT `status_mapping_ibfk_1` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`);

--
-- Constraints for table `supplier_route`
--
ALTER TABLE `supplier_route`
  ADD CONSTRAINT `supplier_route_ibfk_1` FOREIGN KEY (`produk_id`) REFERENCES `produk` (`id`),
  ADD CONSTRAINT `supplier_route_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`);

--
-- Constraints for table `template_payload`
--
ALTER TABLE `template_payload`
  ADD CONSTRAINT `template_payload_ibfk_1` FOREIGN KEY (`produk_id`) REFERENCES `produk` (`id`);

--
-- Constraints for table `transaksi_log`
--
ALTER TABLE `transaksi_log`
  ADD CONSTRAINT `transaksi_log_ibfk_1` FOREIGN KEY (`produk_id`) REFERENCES `produk` (`id`),
  ADD CONSTRAINT `transaksi_log_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `supplier` (`id`);
COMMIT;

