-- ===========================================
-- FLOWBOT DCI - PERFORMANCE INDICES MIGRATION
-- ===========================================
-- Run this migration to improve query performance for massive scale
-- Version: 1.0.1 (MySQL compatible)
-- Date: 2026-01-19
--
-- IMPORTANT: Run during low-traffic period
-- Errors about duplicate index names can be safely ignored

-- ============================================
-- DASHBOARD_PROCESSES TABLE INDICES
-- ============================================

-- Drop existing indices if they exist (safe to ignore errors)
DROP INDEX idx_processes_status ON dashboard_processes;
DROP INDEX idx_processes_created ON dashboard_processes;
DROP INDEX idx_processes_user_status ON dashboard_processes;
DROP INDEX idx_processes_status_created ON dashboard_processes;

-- Create indices
CREATE INDEX idx_processes_status ON dashboard_processes(status);
CREATE INDEX idx_processes_created ON dashboard_processes(created_at);
CREATE INDEX idx_processes_user_status ON dashboard_processes(user_id, status);
CREATE INDEX idx_processes_status_created ON dashboard_processes(status, created_at);

-- ============================================
-- PINFEEDS TABLE INDICES
-- ============================================
-- Note: Table name is 'pinfeeds' (with 's') as defined in PinFeed.php

-- Drop existing indices if they exist (safe to ignore errors)
DROP INDEX idx_pinfeeds_link ON pinfeeds;
DROP INDEX idx_pinfeeds_domain ON pinfeeds;
DROP INDEX idx_pinfeeds_created ON pinfeeds;
DROP INDEX idx_pinfeeds_link_date ON pinfeeds;

-- PERF-001: Create indices for fast duplicate checking and queries
-- Index on link column for fast EXISTS checks (existsBatch uses this)
CREATE INDEX idx_pinfeeds_link ON pinfeeds(link(255));

-- Index on source_domain for domain filtering
CREATE INDEX idx_pinfeeds_domain ON pinfeeds(source_domain(100));

-- Index on pubDate for recent entries queries
CREATE INDEX idx_pinfeeds_created ON pinfeeds(pubDate);

-- Composite index for link + date queries
CREATE INDEX idx_pinfeeds_link_date ON pinfeeds(link(255), pubDate);

-- ============================================
-- DOMAINS TABLE INDICES (if table exists)
-- ============================================

-- Drop existing indices if they exist
DROP INDEX idx_domains_name ON domains;
DROP INDEX idx_domains_status ON domains;
DROP INDEX idx_domains_name_status ON domains;

-- Create indices
CREATE INDEX idx_domains_name ON domains(domain(100));
CREATE INDEX idx_domains_status ON domains(status);
CREATE INDEX idx_domains_name_status ON domains(domain(100), status);

-- ============================================
-- VERIFY INDICES
-- ============================================
-- SHOW INDEX FROM dashboard_processes;
-- SHOW INDEX FROM pinfeeds;
-- SHOW INDEX FROM domains;
