-- =====================================================
-- Withdrawal Requests Table SQL
-- For User Profile & Withdrawal Management System
-- Date: November 26, 2025
-- =====================================================

-- Drop table if exists (CAUTION: This will delete all data!)
-- DROP TABLE IF EXISTS `withdrawal_requests`;

-- Create withdrawal_requests table
CREATE TABLE IF NOT EXISTS `withdrawal_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `user_id` BIGINT UNSIGNED NOT NULL,
    `amount` DECIMAL(15, 2) NOT NULL,
    `type` ENUM('withdrawal', 'deposit') NOT NULL DEFAULT 'withdrawal',
    `payment_method` VARCHAR(255) NOT NULL DEFAULT 'shop_withdrawal' COMMENT 'Only shop withdrawal supported',
    `account_details` TEXT NULL,
    `status` ENUM('pending', 'approved', 'rejected', 'processing') NOT NULL DEFAULT 'pending',
    `balance_before` DECIMAL(15, 2) NULL COMMENT 'User balance before processing',
    `balance_after` DECIMAL(15, 2) NULL COMMENT 'User balance after processing',
    `processed_by` BIGINT UNSIGNED NULL COMMENT 'Admin/Moderator user_id who processed the request',
    `processed_at` TIMESTAMP NULL COMMENT 'When the request was processed',
    `rejection_reason` TEXT NULL COMMENT 'Reason for rejection if status is rejected',
    `notes` TEXT NULL COMMENT 'Additional notes from admin/moderator',
    `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Indexes for performance
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_status` (`status`),
    INDEX `idx_processed_by` (`processed_by`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='User withdrawal requests table';

-- =====================================================
-- Optional: Add foreign key constraints (if needed)
-- =====================================================
-- Uncomment the following if you want foreign key constraints:

-- ALTER TABLE `withdrawal_requests`
--     ADD CONSTRAINT `fk_withdrawal_user`
--         FOREIGN KEY (`user_id`) 
--         REFERENCES `users`(`user_id`) 
--         ON DELETE CASCADE 
--         ON UPDATE CASCADE,
--     ADD CONSTRAINT `fk_withdrawal_processor`
--         FOREIGN KEY (`processed_by`) 
--         REFERENCES `users`(`user_id`) 
--         ON DELETE SET NULL 
--         ON UPDATE CASCADE;

-- =====================================================
-- Verification Query
-- =====================================================
-- Run this to verify the table was created successfully:
-- DESCRIBE `withdrawal_requests`;
-- SELECT COUNT(*) as row_count FROM `withdrawal_requests`;

-- =====================================================
-- Sample Queries for Testing
-- =====================================================

-- Get all pending withdrawals
-- SELECT * FROM `withdrawal_requests` WHERE `status` = 'pending' ORDER BY `created_at` DESC;

-- Get withdrawals for a specific user
-- SELECT * FROM `withdrawal_requests` WHERE `user_id` = 108 ORDER BY `created_at` DESC;

-- Get withdrawal statistics
-- SELECT 
--     `status`, 
--     COUNT(*) as count, 
--     SUM(`amount`) as total_amount 
-- FROM `withdrawal_requests` 
-- GROUP BY `status`;
