-- Migration: create payment_requests table

CREATE TABLE IF NOT EXISTS `payment_requests` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `loan_repayment_id` INT UNSIGNED DEFAULT NULL,
  `gateway` VARCHAR(50) DEFAULT NULL,
  `reference` VARCHAR(191) DEFAULT NULL,
  `authorization_url` TEXT DEFAULT NULL,
  `amount` DECIMAL(10,2) DEFAULT 0,
  `status` VARCHAR(50) DEFAULT 'pending',
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX (`user_id`),
  INDEX (`loan_repayment_id`),
  INDEX (`reference`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- insert default setting for loan_auto_deduction_enabled
INSERT INTO `settings` (`group`, `key`, `value`, `type`, `description`) 
SELECT 'payment','loan_auto_deduction_enabled','false','boolean','Enable/disable automatic loan deduction reminders' 
FROM DUAL WHERE NOT EXISTS (SELECT 1 FROM `settings` WHERE `key` = 'loan_auto_deduction_enabled');