-- --------------------------------------------------------
-- STRUKTUR DATABASE: db_pulsa_konversi
-- --------------------------------------------------------

-- 1. Tabel: rates (Untuk menyimpan data operator dan rate)
CREATE TABLE `rates` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `operator_name` VARCHAR(50) NOT NULL,
  `slug` VARCHAR(50) NOT NULL UNIQUE,
  `rate` DECIMAL(4,3) NOT NULL COMMENT 'Contoh: 0.85 untuk 85%',
  `min_nominal` INT(11) NOT NULL DEFAULT 10000,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Tabel: transactions (Untuk menyimpan riwayat transaksi konversi)
CREATE TABLE `transactions` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `invoice_code` VARCHAR(20) NOT NULL UNIQUE,
  `user_id` INT(11) DEFAULT NULL COMMENT 'ID pengguna yang bertransaksi (bisa null jika tamu)',
  `operator_id` INT(11) NOT NULL,
  `nominal_pulsa` INT(11) NOT NULL,
  `rate_used` DECIMAL(4,3) NOT NULL,
  `received_amount` INT(11) NOT NULL COMMENT 'Jumlah uang yang diterima (nominal_pulsa * rate_used)',
  `recipient_bank` VARCHAR(50) NOT NULL,
  `recipient_number` VARCHAR(50) NOT NULL,
  `sender_phone` VARCHAR(20) NOT NULL,
  `status` ENUM('PENDING','PROCESSING','COMPLETED','FAILED','CANCELED') NOT NULL DEFAULT 'PENDING',
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `fk_operator_id` (`operator_id`),
  FOREIGN KEY (`operator_id`) REFERENCES `rates`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- --------------------------------------------------------
-- DATA AWAL (Initial Data)
-- --------------------------------------------------------

-- Data Rate Operator
INSERT INTO `rates` (`operator_name`, `slug`, `rate`, `min_nominal`, `is_active`) VALUES
('Tri', 'tri', '0.860', 30000, 1),
('XL/Axis', 'xl_axis', '0.845', 25000, 1),
('Telkomsel', 'telkomsel', '0.820', 15000, 1),
('Indosat', 'indosat', '0.855', 20000, 1);