-- 005_indexes.sql
-- Crea índices útiles para /dashboard, tareas y listados, solo si no existen.

-- Helper pattern: usa information_schema + PREPARE para evitar errores si ya existen.

-- task_completions: búsquedas por periodo y por (persona, periodo)
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_completions' AND index_name='idx_period' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_period ON task_completions (period_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_completions' AND index_name='idx_person_period' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_person_period ON task_completions (person_id, period_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Nota: si ya tienes UNIQUE (task_id, period_id) (p.ej. uk_general_once), no añadimos otro índice equivalente.

-- task_assignments: por persona y por tarea
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_assignments' AND index_name='idx_person' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_person ON task_assignments (person_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='task_assignments' AND index_name='idx_task' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_task ON task_assignments (task_id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- tasks: filtrar activas / generales
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='tasks' AND index_name='idx_active_general' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_active_general ON tasks (is_active, is_general)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- screensaver_images: listar activas ordenadas
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='screensaver_images' AND index_name='idx_active_sort' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_active_sort ON screensaver_images (is_active, sort_order, id)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- people: listar activos ordenados por nombre
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='people' AND index_name='idx_active_name' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_active_name ON people (is_active, name)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- weather_cache: limpieza por expiración (opcional)
SET @ix := (SELECT 1 FROM information_schema.statistics
            WHERE table_schema = DATABASE() AND table_name='weather_cache' AND index_name='idx_expires_at' LIMIT 1);
SET @sql := IF(@ix IS NULL, 'CREATE INDEX idx_expires_at ON weather_cache (expires_at)', 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;
