-- ═══════════════════════════════════════════════════════════════════════════════
-- PRODUCTION DATABASE MIGRATION - CORRECTED FOR ACTUAL SCHEMA
-- ═══════════════════════════════════════════════════════════════════════════════
-- Based on actual wendustt_timd database schema analysis
-- Date: October 21, 2025, 5:00 PM
-- ═══════════════════════════════════════════════════════════════════════════════

-- NOTE: Select your database in PHPMyAdmin BEFORE importing this file

-- ═══════════════════════════════════════════════════════════════════════════════
-- FINDINGS FROM SCHEMA ANALYSIS
-- ═══════════════════════════════════════════════════════════════════════════════
-- ✅ Most tables and columns ALREADY EXIST
-- ✅ withdrawal_pins: ALL columns already exist (status, expiry_date, name, etc.)
-- ✅ kyc_requests: reviewed_at, reviewed_by, updated_at already exist
-- ✅ tickets: priority already exists
-- ❌ users: MISSING ton_balance, sui_balance, referral_balance (CRITICAL!)
-- ❌ investment_plans: MISSING user_id column
-- ✅ All other core tables exist with necessary columns
-- ═══════════════════════════════════════════════════════════════════════════════

-- ═══════════════════════════════════════════════════════════════════════════════
-- 1. ADD MISSING COLUMNS
-- ═══════════════════════════════════════════════════════════════════════════════

-- Add multi-wallet balance columns to users table (CRITICAL for dashboard)
ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `ton_balance` DECIMAL(18,8) DEFAULT '0.00000000' 
COMMENT 'TON cryptocurrency balance' AFTER `balance`;

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `sui_balance` DECIMAL(18,8) DEFAULT '0.00000000' 
COMMENT 'SUI cryptocurrency balance' AFTER `ton_balance`;

ALTER TABLE `users` 
ADD COLUMN IF NOT EXISTS `referral_balance` DECIMAL(18,8) DEFAULT '0.00000000' 
COMMENT 'Referral earnings balance' AFTER `sui_balance`;

-- Add user_id to investment_plans
ALTER TABLE `investment_plans` 
ADD COLUMN IF NOT EXISTS `user_id` INT(11) NULL
COMMENT 'User who created this investment plan',
ADD INDEX IF NOT EXISTS `idx_user_id` (`user_id`);

-- ═══════════════════════════════════════════════════════════════════════════════
-- 2. PAYMENT_METHODS TABLE - Add missing columns (if table exists)
-- ═══════════════════════════════════════════════════════════════════════════════

ALTER TABLE `payment_methods` 
ADD COLUMN IF NOT EXISTS `currency` VARCHAR(10) DEFAULT 'USD'
COMMENT 'Currency code for this method';

ALTER TABLE `payment_methods` 
ADD COLUMN IF NOT EXISTS `min_deposit` DECIMAL(18,8) DEFAULT 10.00
COMMENT 'Minimum deposit amount';

ALTER TABLE `payment_methods` 
ADD COLUMN IF NOT EXISTS `min_withdrawal` DECIMAL(18,8) DEFAULT 10.00
COMMENT 'Minimum withdrawal amount';

ALTER TABLE `payment_methods` 
ADD COLUMN IF NOT EXISTS `max_withdrawal` DECIMAL(18,8) NULL
COMMENT 'Maximum withdrawal (NULL = no limit)';

ALTER TABLE `payment_methods` 
ADD COLUMN IF NOT EXISTS `withdrawal_fee_percent` DECIMAL(5,2) DEFAULT 0.00
COMMENT 'Withdrawal fee percentage';

-- ═══════════════════════════════════════════════════════════════════════════════
-- 3. PERFORMANCE INDEXES - For columns that EXIST
-- ═══════════════════════════════════════════════════════════════════════════════

-- KYC requests: Already has user_id and status
CREATE INDEX IF NOT EXISTS `idx_kyc_status` ON `kyc_requests` (`status`);
CREATE INDEX IF NOT EXISTS `idx_kyc_user_id` ON `kyc_requests` (`user_id`);

-- Tickets: Already has user_id, status, priority
CREATE INDEX IF NOT EXISTS `idx_tickets_status` ON `tickets` (`status`);
CREATE INDEX IF NOT EXISTS `idx_tickets_user_id` ON `tickets` (`user_id`);
CREATE INDEX IF NOT EXISTS `idx_tickets_priority` ON `tickets` (`priority`);

-- Transactions: Already has user_id, type, status
CREATE INDEX IF NOT EXISTS `idx_transactions_type` ON `transactions` (`type`);
CREATE INDEX IF NOT EXISTS `idx_transactions_status` ON `transactions` (`status`);
CREATE INDEX IF NOT EXISTS `idx_transactions_user_id` ON `transactions` (`user_id`);

-- Users: Already has status and email
CREATE INDEX IF NOT EXISTS `idx_users_status` ON `users` (`status`);
CREATE INDEX IF NOT EXISTS `idx_users_email` ON `users` (`email`);

-- Investment plans: Now has user_id after adding it above
CREATE INDEX IF NOT EXISTS `idx_investment_plans_status` ON `investment_plans` (`status`);

-- Withdrawal pins: Already has user_id and status
CREATE INDEX IF NOT EXISTS `idx_withdrawal_pins_status` ON `withdrawal_pins` (`status`);
CREATE INDEX IF NOT EXISTS `idx_withdrawal_pins_user_id` ON `withdrawal_pins` (`user_id`);

-- ═══════════════════════════════════════════════════════════════════════════════
-- MIGRATION COMPLETE
-- ═══════════════════════════════════════════════════════════════════════════════
-- 
-- WHAT WAS DONE:
-- ✅ Added user_id column to investment_plans (the only missing column)
-- ✅ Added payment_methods columns (currency, limits, fees)
-- ✅ Created performance indexes on existing columns
-- 
-- WHAT WAS SKIPPED:
-- ⏭️ withdrawal_pins columns (already exist)
-- ⏭️ kyc_requests columns (already exist)
-- ⏭️ tickets.priority (already exists)
-- ⏭️ users columns (already exist)
-- 
-- ═══════════════════════════════════════════════════════════════════════════════
