-- Migration: Add Missing Columns to withdrawal_pins Table
-- Date: October 20, 2025
-- Purpose: Fix schema mismatch - add status, expiry_date, name columns
-- Related: WITHDRAWAL_FLOW_AUDIT_CRITICAL.md

-- Step 1: Add status column if missing (replaces boolean 'used' with enum)
SET @db := DATABASE();
SET @has_status := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='status'
);
SET @sql := IF(@has_status=0,
  "ALTER TABLE `withdrawal_pins` ADD COLUMN `status` ENUM('active','used','cancelled') DEFAULT 'active' AFTER `pin`",
  'SELECT 1'
);
PREPARE s1 FROM @sql; EXECUTE s1; DEALLOCATE PREPARE s1;

-- Step 2: Add expiry_date column for PIN expiration feature (if missing)
SET @has_expiry := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='expiry_date'
);
SET @sql := IF(@has_expiry=0,
  "ALTER TABLE `withdrawal_pins` ADD COLUMN `expiry_date` DATETIME NULL AFTER `status`",
  'SELECT 1'
);
PREPARE s2 FROM @sql; EXECUTE s2; DEALLOCATE PREPARE s2;

-- Step 3: Add name column for PIN naming/labeling (if missing)
SET @has_name := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='name'
);
SET @sql := IF(@has_name=0,
  "ALTER TABLE `withdrawal_pins` ADD COLUMN `name` VARCHAR(100) NULL AFTER `user_id`",
  'SELECT 1'
);
PREPARE s3 FROM @sql; EXECUTE s3; DEALLOCATE PREPARE s3;

-- Step 4: Add indexes for performance (if missing)
SET @has_idx_status := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND INDEX_NAME='idx_status'
);
SET @sql := IF(@has_idx_status=0,
  'CREATE INDEX idx_status ON `withdrawal_pins`(`status`)',
  'SELECT 1'
);
PREPARE s4 FROM @sql; EXECUTE s4; DEALLOCATE PREPARE s4;

SET @has_idx_expiry := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND INDEX_NAME='idx_expiry'
);
SET @sql := IF(@has_idx_expiry=0,
  'CREATE INDEX idx_expiry ON `withdrawal_pins`(`expiry_date`)',
  'SELECT 1'
);
PREPARE s5 FROM @sql; EXECUTE s5; DEALLOCATE PREPARE s5;

-- Step 5: Migrate existing data from 'used' to 'status' (only if both columns exist)
SET @has_used := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='used'
);
SET @has_status := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='status'
);
SET @sql := IF(@has_used=1 AND @has_status=1,
  "UPDATE `withdrawal_pins` SET `status`=CASE WHEN `used`=1 THEN 'used' ELSE 'active' END",
  'SELECT 1'
);
PREPARE s6 FROM @sql; EXECUTE s6; DEALLOCATE PREPARE s6;

-- Step 6: Optional - Set expiry_date for existing PINs (30 days from creation)
-- UPDATE withdrawal_pins 
-- SET expiry_date = DATE_ADD(created_at, INTERVAL 30 DAY)
-- WHERE expiry_date IS NULL AND status = 'active';

-- Step 7: Add withdrawal_pin_required column to users table (if missing)
SET @has_upr := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='users' AND COLUMN_NAME='withdrawal_pin_required'
);
SET @sql := IF(@has_upr=0,
  'ALTER TABLE `users` ADD COLUMN `withdrawal_pin_required` TINYINT(1) DEFAULT 1 AFTER `balance`',
  'SELECT 1'
);
PREPARE s7 FROM @sql; EXECUTE s7; DEALLOCATE PREPARE s7;

-- Step 8: Add transaction_id and updated_at to withdrawal_pins (if missing)
SET @has_tx := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='transaction_id'
);
SET @sql := IF(@has_tx=0,
  'ALTER TABLE `withdrawal_pins` ADD COLUMN `transaction_id` INT NULL AFTER `expiry_date`',
  'SELECT 1'
);
PREPARE s8 FROM @sql; EXECUTE s8; DEALLOCATE PREPARE s8;

SET @has_upd := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='withdrawal_pins' AND COLUMN_NAME='updated_at'
);
SET @sql := IF(@has_upd=0,
  'ALTER TABLE `withdrawal_pins` ADD COLUMN `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP',
  'SELECT 1'
);
PREPARE s9 FROM @sql; EXECUTE s9; DEALLOCATE PREPARE s9;

-- Step 9: Add missing columns to payment_methods table
-- These columns were referenced in code but didn't exist
ALTER TABLE payment_methods 
ADD COLUMN currency VARCHAR(10) DEFAULT 'USD' AFTER type,
ADD COLUMN min_deposit DECIMAL(18,8) DEFAULT 10.00 AFTER currency,
ADD COLUMN min_withdrawal DECIMAL(18,8) DEFAULT 10.00 AFTER min_deposit,
ADD COLUMN max_withdrawal DECIMAL(18,8) NULL AFTER min_withdrawal,
ADD COLUMN withdrawal_fee_percent DECIMAL(5,2) DEFAULT 0.00 AFTER max_withdrawal;

-- Verification queries:
-- SELECT * FROM withdrawal_pins LIMIT 5;
-- SHOW COLUMNS FROM withdrawal_pins;
-- SELECT status, COUNT(*) FROM withdrawal_pins GROUP BY status;
-- SHOW COLUMNS FROM users WHERE Field = 'withdrawal_pin_required';
