-- =====================================================
-- 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);

-- API Providers table indexes
CREATE INDEX IF NOT EXISTS idx_api_providers_status ON api_providers(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 3: VERIFY API_PROVIDERS TABLE STRUCTURE
-- =====================================================

-- Verify api_providers table has all required columns
ALTER TABLE api_providers ADD COLUMN IF NOT EXISTS description TEXT NULL AFTER name;
ALTER TABLE api_providers ADD COLUMN IF NOT EXISTS status VARCHAR(20) DEFAULT 'active' AFTER api_key;
ALTER TABLE api_providers ADD COLUMN IF NOT EXISTS created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
ALTER TABLE api_providers ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

-- =====================================================
-- STEP 4: CREATE OWLET API PROVIDER IF NOT EXISTS
-- =====================================================

INSERT INTO api_providers (name, description, api_url, api_key, status, created_at, updated_at)
VALUES (
  'Owlet Panel',
  'Primary SMM service provider - Owlet Panel',
  'https://the-owlet.com/api/v2',
  '8ff5fe92957cf94a2649f589c5cb918c',
  'active',
  NOW(),
  NOW()
)
ON DUPLICATE KEY UPDATE
  name = VALUES(name),
  status = VALUES(status);

-- =====================================================
-- 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);

-- =====================================================
-- STEP 8: VERIFY SAMPLE DATA
-- =====================================================

-- Check if we have any services linked to Owlet provider
SELECT COUNT(*) as service_count, 'Total Owlet Services' as description
FROM services
WHERE api_provider_id = (SELECT id FROM api_providers WHERE name = 'Owlet Panel' LIMIT 1);

-- 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;

-- Final verification query
SELECT
  'Database Status' as check_point,
  (SELECT COUNT(*) FROM users) as total_users,
  (SELECT COUNT(*) FROM services) as total_services,
  (SELECT COUNT(*) FROM orders) as total_orders,
  (SELECT COUNT(*) FROM api_providers WHERE status = 'active') as active_providers,
  NOW() as last_checked;
