-- ============================================
-- HOTFIX: Add Missing Columns to state_change_requests
-- Date: November 27, 2025
-- Issue: SQLSTATE[42S22] - Column 'approved_at' not found
-- ============================================

-- This script adds all missing columns to existing state_change_requests table
-- Safe to run multiple times - checks if columns exist before adding

USE `final_taekwondo_db`;

-- Add request_code column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'request_code');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `request_code` VARCHAR(50) NULL COMMENT "Unique request code" AFTER `user_id`',
    'SELECT "✓ request_code exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add UNIQUE index on request_code
SET @check_idx = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND INDEX_NAME = 'idx_request_code');
SET @sql = IF(@check_idx = 0,
    'CREATE UNIQUE INDEX `idx_request_code` ON `state_change_requests`(`request_code`)',
    'SELECT "✓ idx_request_code exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add/Update action_type column (match migration: only 'change' and 'revert')
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'action_type');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `action_type` ENUM("change","revert") DEFAULT "change" COMMENT "Type of request" AFTER `request_code`',
    'SELECT "✓ action_type exists, will update ENUM values" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Always update the ENUM to ensure it matches migration
ALTER TABLE `state_change_requests` 
MODIFY COLUMN `action_type` ENUM("change","revert") DEFAULT "change" COMMENT "Type of request";

-- Rename from_state_id to current_state_id if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'from_state_id');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'current_state_id');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `from_state_id` `current_state_id` BIGINT(20) UNSIGNED NOT NULL COMMENT "Current state at time of request"',
    'SELECT "✓ current_state_id exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename from_district_id to current_district_id if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'from_district_id');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'current_district_id');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `from_district_id` `current_district_id` BIGINT(20) UNSIGNED NULL COMMENT "Current district"',
    'SELECT "✓ current_district_id exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename to_state_id to requested_state_id if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'to_state_id');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'requested_state_id');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `to_state_id` `requested_state_id` BIGINT(20) UNSIGNED NOT NULL COMMENT "Requested new state"',
    'SELECT "✓ requested_state_id exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename to_district_id to requested_district_id if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'to_district_id');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'requested_district_id');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `to_district_id` `requested_district_id` BIGINT(20) UNSIGNED NULL COMMENT "Requested new district"',
    'SELECT "✓ requested_district_id exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add requested_city_name column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'requested_city_name');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `requested_city_name` VARCHAR(255) NULL COMMENT "City name if applicable" AFTER `requested_district_id`',
    'SELECT "✓ requested_city_name exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add new_address column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'new_address');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `new_address` TEXT NULL COMMENT "New address details" AFTER `requested_city_name`',
    'SELECT "✓ new_address exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add new_pincode column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'new_pincode');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `new_pincode` VARCHAR(20) NULL COMMENT "New pincode" AFTER `new_address`',
    'SELECT "✓ new_pincode exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add revert_target column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'revert_target');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `revert_target` ENUM("home_state","previous_state") NULL COMMENT "Target for revert action" AFTER `new_pincode`',
    'SELECT "✓ revert_target exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add requested_effective_date column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'requested_effective_date');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `requested_effective_date` DATETIME NULL COMMENT "When user wants change to take effect" AFTER `revert_target`',
    'SELECT "✓ requested_effective_date exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Update status ENUM to match migration
ALTER TABLE `state_change_requests` 
MODIFY COLUMN `status` ENUM('pending','approved','applied','rejected','cancelled','expired') DEFAULT 'pending' COMMENT 'Request status';

-- Rename reason to reason_user if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'reason');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'reason_user');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `reason` `reason_user` TEXT NULL COMMENT "User-provided reason"',
    'SELECT "✓ reason_user exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename admin_comments to admin_note if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'admin_comments');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'admin_note');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `admin_comments` `admin_note` TEXT NULL COMMENT "Admin comments"',
    'SELECT "✓ admin_note exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Rename reviewed_by to approved_by if needed
SET @check_old = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'reviewed_by');
SET @check_new = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'approved_by');
SET @sql = IF(@check_old > 0 AND @check_new = 0,
    'ALTER TABLE `state_change_requests` CHANGE COLUMN `reviewed_by` `approved_by` BIGINT(20) UNSIGNED NULL COMMENT "FK to admins who approved"',
    'SELECT "✓ approved_by exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- *** CRITICAL FIX: Add approved_at column ***
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'approved_at');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `approved_at` DATETIME NULL COMMENT "When request was approved" AFTER `approved_by`',
    'SELECT "✓ approved_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add applied_at column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'applied_at');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `applied_at` DATETIME NULL COMMENT "When change was actually applied" AFTER `approved_at`',
    'SELECT "✓ applied_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add rejected_at column
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'rejected_at');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `rejected_at` DATETIME NULL COMMENT "When request was rejected" AFTER `applied_at`',
    'SELECT "✓ rejected_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add lock_check_snapshot column (JSON type per migration)
SET @check_col = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND COLUMN_NAME = 'lock_check_snapshot');
SET @sql = IF(@check_col = 0,
    'ALTER TABLE `state_change_requests` ADD COLUMN `lock_check_snapshot` JSON NULL COMMENT "Snapshot of lock status at request time" AFTER `rejection_reason`',
    'SELECT "✓ lock_check_snapshot exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Ensure column is JSON type (not DATETIME)
ALTER TABLE `state_change_requests` 
MODIFY COLUMN `lock_check_snapshot` JSON NULL COMMENT "Snapshot of lock status at request time";

-- Add indexes for performance
SET @check_idx = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND INDEX_NAME = 'idx_approved_at');
SET @sql = IF(@check_idx = 0,
    'CREATE INDEX `idx_approved_at` ON `state_change_requests`(`approved_at`)',
    'SELECT "✓ idx_approved_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @check_idx = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND INDEX_NAME = 'idx_applied_at');
SET @sql = IF(@check_idx = 0,
    'CREATE INDEX `idx_applied_at` ON `state_change_requests`(`applied_at`)',
    'SELECT "✓ idx_applied_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Remove old indexes if they exist
SET @check_idx = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND INDEX_NAME = 'idx_requested_at');
SET @sql = IF(@check_idx > 0,
    'DROP INDEX `idx_requested_at` ON `state_change_requests`',
    'SELECT "✓ idx_requested_at already removed" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Add new created_at index for better performance
SET @check_idx = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'state_change_requests' AND INDEX_NAME = 'idx_created_at');
SET @sql = IF(@check_idx = 0,
    'CREATE INDEX `idx_created_at` ON `state_change_requests`(`created_at`)',
    'SELECT "✓ idx_created_at exists" AS msg');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Remove old columns if they still exist after renames (cleanup)
-- This is safe because we've already renamed them above

-- Verification
SELECT '
╔═══════════════════════════════════════════════════════════╗
║         HOTFIX APPLIED: Missing Columns Added             ║
╚═══════════════════════════════════════════════════════════╝
' AS status;

-- Show all columns in state_change_requests
SELECT 
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_DEFAULT,
    COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'state_change_requests'
ORDER BY ORDINAL_POSITION;

-- Verify critical columns exist
SELECT CASE 
    WHEN COUNT(*) >= 3 THEN '✓ PASS: Critical timestamp columns exist (approved_at, applied_at, rejected_at)'
    ELSE CONCAT('✗ FAIL: Missing columns, found only ', COUNT(*))
END AS critical_columns_check
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'state_change_requests'
  AND COLUMN_NAME IN ('approved_at', 'applied_at', 'rejected_at');

-- Test the query that was failing
SELECT '
Testing the query that was causing the error:
SELECT * FROM state_change_requests WHERE user_id = 24555 AND status = "approved" ORDER BY approved_at DESC LIMIT 1
' AS test_query;

-- Show count of requests per user (for user 24555 if exists)
SELECT 
    user_id,
    status,
    COUNT(*) AS request_count,
    MAX(approved_at) AS latest_approval
FROM state_change_requests
WHERE user_id = 24555
GROUP BY user_id, status;

SELECT '
╔═══════════════════════════════════════════════════════════╗
║                  HOTFIX COMPLETE ✅                        ║
║                                                           ║
║  ✓ All missing columns added                              ║
║  ✓ Column renames applied                                 ║
║  ✓ Indexes optimized                                      ║
║  ✓ approved_at column now exists                          ║
║                                                           ║
║  NEXT STEPS:                                              ║
║  1. Clear Laravel caches:                                 ║
║     php artisan cache:clear                               ║
║     php artisan config:clear                              ║
║     php artisan view:clear                                ║
║                                                           ║
║  2. Test state change request views in user section       ║
║                                                           ║
║  3. Verify sorting by approved_at works                   ║
╚═══════════════════════════════════════════════════════════╝
' AS completion;
