-- ==============================================================================
-- PAISA REMIT 3.0 - UTILITY & RECHARGE PERSISTENCE (v1.0.1)
-- ==============================================================================

-- 1. MOBILE OPERATORS
CREATE TABLE IF NOT EXISTS `mobile_operators` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `code` VARCHAR(20) NOT NULL UNIQUE,
  `name_en` VARCHAR(100) NOT NULL,
  `name_bn` VARCHAR(100) NOT NULL,
  `prefix` VARCHAR(50) NOT NULL, -- e.g. 017, 013
  `sending_rate` DECIMAL(12,4) DEFAULT 32.45, -- BDT per 1 SAR
  `color_hex` BIGINT UNSIGNED DEFAULT 0xFF00A651,
  `is_active` TINYINT(1) DEFAULT 1,
  `display_order` INT UNSIGNED DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. MOBILE RECHARGES
CREATE TABLE IF NOT EXISTS `mobile_recharges` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `operator_id` INT UNSIGNED NOT NULL,
  `phone_number` VARCHAR(25) NOT NULL,
  `recharge_type` ENUM('PREPAID', 'POSTPAID') DEFAULT 'PREPAID',
  `amount_bdt` DECIMAL(18,2) NOT NULL,
  `amount_sar` DECIMAL(18,2) NOT NULL,
  `status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`operator_id`) REFERENCES `mobile_operators`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. UTILITY BILLS
CREATE TABLE IF NOT EXISTS `utility_bills` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `transaction_id` BIGINT UNSIGNED NOT NULL,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `provider_id` INT UNSIGNED NOT NULL,
  `account_number` VARCHAR(50) NOT NULL,
  `bill_reference` VARCHAR(50) NULL,
  `amount_bdt` DECIMAL(18,2) NOT NULL,
  `amount_sar` DECIMAL(18,2) NOT NULL,
  `status` ENUM('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED') DEFAULT 'PENDING',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`transaction_id`) REFERENCES `transactions`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`provider_id`) REFERENCES `utility_providers`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;