-- V009: Add Performance Optimization Indexes
-- Created: 2025-10-01
-- Purpose: Optimize frequently queried tables with additional indexes

-- ==================== Users Table ====================

-- Index for email lookup (already exists as unique, but adding comment)
COMMENT ON INDEX users_email_key IS 'Optimized index for user authentication by email';

-- Index for phone lookup
CREATE INDEX IF NOT EXISTS idx_users_phone ON users(phone) WHERE phone IS NOT NULL;
COMMENT ON INDEX idx_users_phone IS 'Optimized index for user lookup by phone';

-- ==================== Children Table ====================

-- Composite index for user's children with active status first
CREATE INDEX IF NOT EXISTS idx_children_user_birthdate ON children(user_id, birth_date DESC);
COMMENT ON INDEX idx_children_user_birthdate IS 'Optimized for fetching user children ordered by age';

-- Index for family children queries
CREATE INDEX IF NOT EXISTS idx_children_family ON children(family_id) WHERE family_id IS NOT NULL;
COMMENT ON INDEX idx_children_family IS 'Optimized for family child queries';

-- ==================== Activities Table ====================

-- Composite index for child activities with timestamp
CREATE INDEX IF NOT EXISTS idx_activities_child_timestamp ON activities(child_id, timestamp DESC);
COMMENT ON INDEX idx_activities_child_timestamp IS 'Optimized for activity timeline queries';

-- Index for activity type filtering
CREATE INDEX IF NOT EXISTS idx_activities_type_timestamp ON activities(type, timestamp DESC);
COMMENT ON INDEX idx_activities_type_timestamp IS 'Optimized for activity type queries';

-- Partial index for recent activities (last 30 days)
CREATE INDEX IF NOT EXISTS idx_activities_recent
  ON activities(child_id, timestamp DESC)
  WHERE timestamp > NOW() - INTERVAL '30 days';
COMMENT ON INDEX idx_activities_recent IS 'Optimized partial index for recent activity queries';

-- ==================== Family Members Table ====================

-- Index for user's families lookup
CREATE INDEX IF NOT EXISTS idx_family_members_user_role ON family_members(user_id, role);
COMMENT ON INDEX idx_family_members_user_role IS 'Optimized for user family lookup with role';

-- Index for family member lookup
CREATE INDEX IF NOT EXISTS idx_family_members_family ON family_members(family_id, role);
COMMENT ON INDEX idx_family_members_family IS 'Optimized for family member queries';

-- ==================== Refresh Tokens Table ====================

-- Index for token expiration cleanup
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires
  ON refresh_tokens(expires_at)
  WHERE revoked = false;
COMMENT ON INDEX idx_refresh_tokens_expires IS 'Optimized for token expiration queries';

-- Composite index for user active tokens
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user_active
  ON refresh_tokens(user_id, expires_at)
  WHERE revoked = false;
COMMENT ON INDEX idx_refresh_tokens_user_active IS 'Optimized for user active token queries';

-- ==================== Device Registry Table ====================

-- Composite index for trusted device lookup
CREATE INDEX IF NOT EXISTS idx_device_registry_user_trusted
  ON device_registry(user_id, trusted, last_seen DESC);
COMMENT ON INDEX idx_device_registry_user_trusted IS 'Optimized for trusted device queries';

-- ==================== Audit Log Table ====================

-- Composite index for user audit queries
CREATE INDEX IF NOT EXISTS idx_audit_log_user_timestamp
  ON audit_log(user_id, timestamp DESC)
  WHERE user_id IS NOT NULL;
COMMENT ON INDEX idx_audit_log_user_timestamp IS 'Optimized for user audit log queries';

-- Index for event type filtering
CREATE INDEX IF NOT EXISTS idx_audit_log_event_timestamp
  ON audit_log(event_type, timestamp DESC);
COMMENT ON INDEX idx_audit_log_event_timestamp IS 'Optimized for event type queries';

-- Partial index for failed operations
CREATE INDEX IF NOT EXISTS idx_audit_log_failures
  ON audit_log(timestamp DESC)
  WHERE status = 'failure';
COMMENT ON INDEX idx_audit_log_failures IS 'Optimized for failure log queries';

-- ==================== Photos Table ====================

-- Index already exists: idx_photos_child_created
-- Index already exists: idx_photos_activity
-- Index already exists: idx_photos_user

-- Additional index for recent photos
CREATE INDEX IF NOT EXISTS idx_photos_recent
  ON photos(user_id, created_at DESC)
  WHERE created_at > NOW() - INTERVAL '90 days';
COMMENT ON INDEX idx_photos_recent IS 'Optimized partial index for recent photo queries';

-- ==================== Notifications Table ====================

-- Index for unread notifications (if table exists)
-- CREATE INDEX IF NOT EXISTS idx_notifications_user_unread
--   ON notifications(user_id, created_at DESC)
--   WHERE read = false;

-- ==================== Performance Statistics ====================

-- Create a view for monitoring index usage
CREATE OR REPLACE VIEW v_index_usage AS
SELECT
  schemaname,
  tablename,
  indexname,
  idx_scan as scans,
  idx_tup_read as tuples_read,
  idx_tup_fetch as tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

COMMENT ON VIEW v_index_usage IS 'Monitor index usage for performance optimization';

-- Create a view for table statistics
CREATE OR REPLACE VIEW v_table_stats AS
SELECT
  schemaname,
  tablename,
  seq_scan as sequential_scans,
  seq_tup_read as seq_tuples_read,
  idx_scan as index_scans,
  idx_tup_fetch as idx_tuples_fetched,
  n_tup_ins as inserts,
  n_tup_upd as updates,
  n_tup_del as deletes,
  n_live_tup as live_tuples,
  n_dead_tup as dead_tuples,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

COMMENT ON VIEW v_table_stats IS 'Monitor table statistics for performance optimization';

-- ==================== Vacuum and Analyze ====================

-- Analyze all tables to update statistics
ANALYZE users;
ANALYZE children;
ANALYZE activities;
ANALYZE family_members;
ANALYZE families;
ANALYZE refresh_tokens;
ANALYZE device_registry;
ANALYZE audit_log;
ANALYZE photos;
