-- ==========================================
-- Database Optimization Script
-- Tambahkan indexes untuk faster queries
-- ==========================================

-- 1. Tokens Table Indexes
CREATE INDEX IF NOT EXISTS idx_tokens_user_id ON tokens(user_id);
CREATE INDEX IF NOT EXISTS idx_tokens_website_id ON tokens(website_id);
CREATE INDEX IF NOT EXISTS idx_tokens_user_website_active ON tokens(user_id, website_id, is_active);
CREATE INDEX IF NOT EXISTS idx_tokens_expires_at ON tokens(expires_at);
CREATE INDEX IF NOT EXISTS idx_tokens_token_code ON tokens(token_code);

-- 2. Users Table Indexes
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);

-- 3. Activity Logs Indexes (untuk faster filtering)
CREATE INDEX IF NOT EXISTS idx_activity_logs_user_id ON activity_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_logs_created_at ON activity_logs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_activity_logs_type ON activity_logs(action);
CREATE INDEX IF NOT EXISTS idx_activity_logs_user_created ON activity_logs(user_id, created_at DESC);

-- 4. Device Access Indexes
CREATE INDEX IF NOT EXISTS idx_device_access_user_website ON device_access(user_id, website_id);
CREATE INDEX IF NOT EXISTS idx_device_access_device_id ON device_access(device_id);
CREATE INDEX IF NOT EXISTS idx_device_access_last_seen ON device_access(last_seen_at DESC);

-- 5. Cookie Groups Indexes
CREATE INDEX IF NOT EXISTS idx_cookie_groups_website_id ON cookie_groups(website_id);
CREATE INDEX IF NOT EXISTS idx_cookie_groups_created_at ON cookie_groups(created_at DESC);

-- 6. Websites Indexes
CREATE INDEX IF NOT EXISTS idx_websites_slug ON websites(slug);
CREATE INDEX IF NOT EXISTS idx_websites_is_active ON websites(is_active);

-- 7. System Settings Index
CREATE INDEX IF NOT EXISTS idx_system_settings_key ON system_settings(`key`);

-- ==========================================
-- Check existing indexes
-- ==========================================
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS COLUMNS
FROM 
    INFORMATION_SCHEMA.STATISTICS
WHERE 
    TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME IN ('tokens', 'users', 'activity_logs', 'device_access', 'cookie_groups', 'websites', 'system_settings')
GROUP BY 
    TABLE_NAME, INDEX_NAME
ORDER BY 
    TABLE_NAME, INDEX_NAME;

-- ==========================================
-- Analyze tables for query optimizer
-- ==========================================
ANALYZE TABLE tokens;
ANALYZE TABLE users;
ANALYZE TABLE activity_logs;
ANALYZE TABLE device_access;
ANALYZE TABLE cookie_groups;
ANALYZE TABLE websites;
ANALYZE TABLE system_settings;

-- ==========================================
-- Check table sizes and row counts
-- ==========================================
SELECT 
    table_name AS 'Table',
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
    table_rows AS 'Rows'
FROM 
    information_schema.TABLES
WHERE 
    table_schema = DATABASE()
ORDER BY 
    (data_length + index_length) DESC;
