-- =====================================================
-- WakaTraffic Database Setup & Optimization
-- Performance indexes and schema verification
-- =====================================================

-- =====================================================
-- STEP 1: CREATE PERFORMANCE INDEXES
-- =====================================================

-- Orders table indexes (Critical for order lookup)
CREATE INDEX IF NOT EXISTS idx_orders_user_status ON orders(user_id, status);
CREATE INDEX IF NOT EXISTS idx_orders_provider_id ON orders(provider_order_id);
CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
CREATE INDEX IF NOT EXISTS idx_orders_service_id ON orders(service_id);
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at DESC);

-- Services table indexes
CREATE INDEX IF NOT EXISTS idx_services_provider ON services(api_provider_id);
CREATE INDEX IF NOT EXISTS idx_services_category ON services(category_id);
CREATE INDEX IF NOT EXISTS idx_services_status ON services(status);

-- Users table indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);

-- Transactions table indexes
CREATE INDEX IF NOT EXISTS idx_transactions_user ON transactions(user_id);
CREATE INDEX IF NOT EXISTS idx_transactions_order ON transactions(order_id);
CREATE INDEX IF NOT EXISTS idx_transactions_type ON transactions(type);

-- Deposits table indexes
CREATE INDEX IF NOT EXISTS idx_deposits_user ON deposits(user_id);
CREATE INDEX IF NOT EXISTS idx_deposits_status ON deposits(status);

-- =====================================================
-- STEP 2: VERIFY CRITICAL COLUMNS EXIST
-- =====================================================

-- Check if provider_order_id column exists, if not add it
ALTER TABLE orders ADD COLUMN IF NOT EXISTS provider_order_id VARCHAR(50) NULL UNIQUE AFTER id;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS start_count INT NULL AFTER provider_order_id;
ALTER TABLE orders ADD COLUMN IF NOT EXISTS remains INT NULL AFTER start_count;

-- Add index for provider_order_id if columns exist
CREATE INDEX IF NOT EXISTS idx_orders_provider_order_id ON orders(provider_order_id);

-- =====================================================
-- STEP 5: VERIFY CRITICAL TABLES
-- =====================================================

-- Check cron_logs table exists
CREATE TABLE IF NOT EXISTS cron_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  execution_time TIMESTAMP,
  duration_seconds INT,
  status VARCHAR(20),
  actions_performed JSON,
  errors JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create index on cron_logs
CREATE INDEX IF NOT EXISTS idx_cron_logs_execution_time ON cron_logs(execution_time DESC);

-- =====================================================
-- STEP 6: VERIFY TRANSACTIONS TABLE
-- =====================================================

-- Ensure transactions table has all needed columns
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS user_id INT NOT NULL FIRST;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS order_id INT AFTER user_id;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS type VARCHAR(20) NOT NULL AFTER order_id;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS amount DECIMAL(10,2) NOT NULL AFTER type;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'completed' AFTER amount;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS description TEXT AFTER status;
ALTER TABLE transactions ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

-- =====================================================
-- STEP 7: DATA CLEANUP & VERIFICATION
-- =====================================================

-- Remove any orphaned orders (orders with deleted services)
DELETE FROM orders WHERE service_id NOT IN (SELECT id FROM services);

-- Remove any orphaned transactions (transactions with deleted orders)
DELETE FROM transactions WHERE order_id IS NOT NULL AND order_id NOT IN (SELECT id FROM orders);

-- Check order statistics
SELECT
  'Order Statistics' as metric,
  COUNT(*) as total_orders,
  SUM(CASE WHEN status = 'complete' THEN 1 ELSE 0 END) as completed,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) as pending,
  SUM(CASE WHEN status = 'processing' THEN 1 ELSE 0 END) as processing,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) as cancelled,
  SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) as refunded
FROM orders;

-- =====================================================
-- STEP 9: PERFORMANCE ANALYSIS
-- =====================================================

-- Show index usage statistics (for MySQL 5.7+)
SELECT
  object_schema,
  object_name,
  count_star as total_access,
  count_read as total_reads,
  count_write as total_writes
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = 'wakatraffic'
ORDER BY count_star DESC
LIMIT 20;

-- =====================================================
-- COMPLETION MESSAGE
-- =====================================================

-- Display summary
SELECT 'Setup Complete!' as message;
SELECT '✅ All indexes created' as step1;
SELECT '✅ Tables verified' as step2;
SELECT '✅ Owlet provider configured' as step3;
SELECT '✅ Data cleaned up' as step4;
