-- ============================================
-- Withdrawal PIN System Enhancement Migration
-- ============================================
-- This migration upgrades the withdrawal_pins table
-- to match the advanced implementation with expiry,
-- status tracking, and user settings.
--
-- Run this AFTER initial installation if upgrading
-- from basic to advanced PIN system.
-- ============================================

-- Step 1: Add missing columns to withdrawal_pins table
ALTER TABLE `withdrawal_pins` 
  ADD COLUMN `name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'Human-readable PIN name' AFTER `pin`,
  ADD COLUMN `status` enum('active','used','cancelled','expired') COLLATE utf8mb4_unicode_ci DEFAULT 'active' AFTER `name`,
  ADD COLUMN `expiry_date` timestamp NULL DEFAULT NULL AFTER `status`,
  ADD COLUMN `notes` text COLLATE utf8mb4_unicode_ci AFTER `expiry_date`,
  ADD COLUMN `transaction_id` int DEFAULT NULL COMMENT 'Links to transactions table when PIN is used' AFTER `notes`,
  ADD COLUMN `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP AFTER `used_at`;

-- Step 2: Migrate existing data (convert 'used' boolean to 'status' enum)
UPDATE `withdrawal_pins` 
SET `status` = CASE 
    WHEN `used` = 1 THEN 'used'
    ELSE 'active'
END
WHERE `status` IS NULL OR `status` = 'active';

-- Step 3: Add performance indexes
ALTER TABLE `withdrawal_pins` 
  ADD INDEX `idx_status` (`status`),
  ADD INDEX `idx_expiry_date` (`expiry_date`),
  ADD INDEX `idx_user_status` (`user_id`, `status`),
  ADD INDEX `idx_transaction_id` (`transaction_id`);

-- Step 4: Create user_pin_settings table (if not exists)
CREATE TABLE IF NOT EXISTS `user_pin_settings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `pin_enabled` tinyint(1) DEFAULT '1' COMMENT 'Whether PIN is required for withdrawals',
  `max_pins` int DEFAULT '5' COMMENT 'Maximum active PINs allowed per user',
  `pin_expiry_days` int DEFAULT '30' COMMENT 'Default days until PIN expires',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 5: Add withdrawal_pin_required column to users table (if missing)
ALTER TABLE `users` 
  ADD COLUMN IF NOT EXISTS `withdrawal_pin_required` tinyint(1) DEFAULT '1' AFTER `total_earned`;

-- Success message (will show in console if run manually)
SELECT 'Withdrawal PIN system upgraded successfully!' AS message;
