-- Create UPI Pool Management System
-- This allows admin to add multiple UPI IDs that rotate automatically

-- 1. Create upi_pool table for storing multiple UPI IDs
CREATE TABLE IF NOT EXISTS `upi_pool` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `upi_id` VARCHAR(100) NOT NULL,
  `upi_name` VARCHAR(100) NOT NULL,
  `status` ENUM('active', 'inactive') DEFAULT 'active',
  `daily_limit` DECIMAL(12,2) DEFAULT 100000.00,
  `current_usage` DECIMAL(12,2) DEFAULT 0.00,
  `last_used` DATETIME DEFAULT NULL,
  `total_transactions` INT(11) DEFAULT 0,
  `success_rate` DECIMAL(5,2) DEFAULT 100.00,
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_upi` (`upi_id`),
  KEY `idx_status` (`status`),
  KEY `idx_last_used` (`last_used`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Add gateway settings columns to settings table
ALTER TABLE `settings` 
ADD COLUMN IF NOT EXISTS `default_gateway` ENUM('rspay', 'bank_upi', 'auto') DEFAULT 'rspay' AFTER `default_game_user`,
ADD COLUMN IF NOT EXISTS `bank_upi_commission` DECIMAL(5,2) DEFAULT 2.00 COMMENT 'Commission % for bank UPI' AFTER `default_gateway`,
ADD COLUMN IF NOT EXISTS `bank_upi_fixed` DECIMAL(10,2) DEFAULT 0.00 COMMENT 'Fixed fee for bank UPI' AFTER `bank_upi_commission`,
ADD COLUMN IF NOT EXISTS `upi_rotation_enabled` TINYINT(1) DEFAULT 1 COMMENT 'Enable UPI auto-rotation' AFTER `bank_upi_fixed`,
ADD COLUMN IF NOT EXISTS `upi_rotation_interval` INT(11) DEFAULT 60 COMMENT 'Rotation interval in seconds' AFTER `upi_rotation_enabled`;

-- 3. Create upi_rotation_log table to track UPI usage
CREATE TABLE IF NOT EXISTS `upi_rotation_log` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `upi_id` VARCHAR(100) NOT NULL,
  `order_id` VARCHAR(100) NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `assigned_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `status` VARCHAR(20) DEFAULT 'assigned',
  PRIMARY KEY (`id`),
  KEY `idx_upi_id` (`upi_id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_assigned_at` (`assigned_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. Insert default settings
UPDATE `settings` 
SET 
  `default_gateway` = 'rspay',
  `bank_upi_commission` = 2.00,
  `bank_upi_fixed` = 0.00,
  `upi_rotation_enabled` = 1,
  `upi_rotation_interval` = 60
WHERE `id` = 1;

-- 5. Insert sample UPI IDs (you can modify these)
INSERT INTO `upi_pool` (`upi_id`, `upi_name`, `status`, `daily_limit`) VALUES
('merchant1@paytm', 'Paytm UPI 1', 'active', 100000.00),
('merchant2@phonepe', 'PhonePe UPI 1', 'active', 100000.00),
('merchant3@ybl', 'Google Pay UPI 1', 'active', 100000.00)
ON DUPLICATE KEY UPDATE `upi_name` = VALUES(`upi_name`);

-- Verify installation
SELECT 'UPI Pool System Installed Successfully!' as status;
SELECT COUNT(*) as total_upis FROM upi_pool WHERE status = 'active';
