-- Migration 017: Additional indexes and constraints
-- Performance optimizations for common query patterns

-- Composite indexes for dashboard queries
CREATE INDEX idx_services_address_date ON services(account_address_id, date);
CREATE INDEX idx_projects_customer_date ON projects(customer_id, date);

-- For finding active work by team member
CREATE INDEX idx_service_team_date ON service_team_members(team_profile_id);
CREATE INDEX idx_project_team_date ON project_team_members(team_profile_id);

-- For task completion tracking
CREATE INDEX idx_service_task_completions_year_month ON service_task_completions(year, month);
CREATE INDEX idx_service_task_completions_completed_at ON service_task_completions(completed_at DESC);
CREATE INDEX idx_project_task_completions_completed_at ON project_task_completions(completed_at DESC);

-- For session media queries
CREATE INDEX idx_service_session_images_internal ON service_session_images(internal);
CREATE INDEX idx_service_session_videos_internal ON service_session_videos(internal);
CREATE INDEX idx_project_session_images_internal ON project_session_images(internal);
CREATE INDEX idx_project_session_videos_internal ON project_session_videos(internal);

-- For financial reporting
CREATE INDEX idx_invoices_date_status ON invoices(date, status);
CREATE INDEX idx_labor_start_date ON labor(start_date);
CREATE INDEX idx_revenues_start_date ON revenues(start_date);

-- For scope hierarchy traversal
CREATE INDEX idx_scope_template_areas_order ON scope_template_areas(template_id, "order");
CREATE INDEX idx_scope_template_tasks_order ON scope_template_tasks(area_id, "order");
CREATE INDEX idx_service_scope_areas_order ON service_scope_areas(scope_id, "order");
CREATE INDEX idx_service_scope_tasks_order ON service_scope_tasks(area_id, "order");
CREATE INDEX idx_project_scope_categories_order ON project_scope_categories(scope_id, "order");
CREATE INDEX idx_project_scope_tasks_order ON project_scope_tasks(category_id, "order");

-- For conversation/message pagination
CREATE INDEX idx_messages_conversation_created ON messages(conversation_id, created_at DESC);

-- For notification delivery retry logic
CREATE INDEX idx_notification_deliveries_pending ON notification_deliveries(status, last_attempt_at)
    WHERE status IN ('PENDING', 'QUEUED', 'FAILED');

-- Partial indexes for active records
CREATE INDEX idx_accounts_active ON accounts(customer_id) WHERE status = 'ACTIVE';
CREATE INDEX idx_team_profiles_active ON team_profiles(role) WHERE status = 'ACTIVE';
CREATE INDEX idx_customer_profiles_active ON customer_profiles(id) WHERE status = 'ACTIVE';