-- Database Optimization: Indexes for Better Query Performance
-- Run this manually or as part of deployment

-- ============================================
-- WORKFLOW INDEXES
-- ============================================

-- Index for filtering workflows by status and creator
CREATE INDEX IF NOT EXISTS idx_workflows_status_creator 
ON workflows(status, createdBy);

-- Index for searching workflows by name
CREATE INDEX IF NOT EXISTS idx_workflows_name 
ON workflows(name);

-- Index for filtering workflows by trigger type
CREATE INDEX IF NOT EXISTS idx_workflows_trigger_type 
ON workflows(triggerType);

-- Index for created date (for sorting recent workflows)
CREATE INDEX IF NOT EXISTS idx_workflows_created_at 
ON workflows(createdAt DESC);

-- ============================================
-- EXECUTION INDEXES
-- ============================================

-- Index for filtering executions by workflow
CREATE INDEX IF NOT EXISTS idx_executions_workflow 
ON workflow_executions(workflowId);

-- Index for filtering executions by status
CREATE INDEX IF NOT EXISTS idx_executions_status 
ON workflow_executions(status);

-- Index for filtering by trigger type
CREATE INDEX IF NOT EXISTS idx_executions_trigger 
ON workflow_executions(trigger);

-- Composite index for workflow + status (common query)
CREATE INDEX IF NOT EXISTS idx_executions_workflow_status 
ON workflow_executions(workflowId, status);

-- Index for date-based queries
CREATE INDEX IF NOT EXISTS idx_executions_created_at 
ON workflow_executions(createdAt DESC);

-- Index for finding executions by user
CREATE INDEX IF NOT EXISTS idx_executions_executed_by 
ON workflow_executions(executedBy);

-- ============================================
-- TRIGGER INDEXES
-- ============================================

-- Index for finding triggers by workflow
CREATE INDEX IF NOT EXISTS idx_triggers_workflow 
ON workflow_triggers(workflowId);

-- Index for active scheduled triggers
CREATE INDEX IF NOT EXISTS idx_triggers_type_status 
ON workflow_triggers(type, status);

-- ============================================
-- USER INDEXES
-- ============================================

-- Index for login (email lookup)
CREATE INDEX IF NOT EXISTS idx_users_email 
ON users(email);

-- Index for username lookup
CREATE INDEX IF NOT EXISTS idx_users_username 
ON users(username);

-- Index for role-based queries
CREATE INDEX IF NOT EXISTS idx_users_role 
ON users(roleId);

-- ============================================
-- CREDENTIAL INDEXES
-- ============================================

-- Index for filtering credentials by user
CREATE INDEX IF NOT EXISTS idx_credentials_user 
ON credentials(userId);

-- Index for filtering by type
CREATE INDEX IF NOT EXISTS idx_credentials_type 
ON credentials(type);

-- Composite index for user + type
CREATE INDEX IF NOT EXISTS idx_credentials_user_type 
ON credentials(userId, type);

-- ============================================
-- PERMISSION INDEXES
-- ============================================

-- Index for finding permissions by workflow
CREATE INDEX IF NOT EXISTS idx_permissions_workflow 
ON workflow_permissions(workflowId);

-- Index for finding permissions by user
CREATE INDEX IF NOT EXISTS idx_permissions_user 
ON workflow_permissions(userId);

-- ============================================
-- API KEY INDEXES
-- ============================================

-- Index for API key lookup (for authentication)
CREATE INDEX IF NOT EXISTS idx_api_keys_key_hash 
ON api_keys(keyHash);

-- Index for finding keys by user
CREATE INDEX IF NOT EXISTS idx_api_keys_user 
ON api_keys(userId);

-- Index for active keys
CREATE INDEX IF NOT EXISTS idx_api_keys_active 
ON api_keys(isActive);

-- ============================================
-- REFRESH TOKEN INDEXES
-- ============================================

-- Index for token lookup
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_token 
ON refresh_tokens(token);

-- Index for finding user's tokens
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_user 
ON refresh_tokens(userId);

-- Index for cleaning up expired tokens
CREATE INDEX IF NOT EXISTS idx_refresh_tokens_expires 
ON refresh_tokens(expiresAt);

-- ============================================
-- AUDIT LOG INDEXES
-- ============================================

-- Index for finding logs by user
CREATE INDEX IF NOT EXISTS idx_audit_logs_user 
ON audit_logs(userId);

-- Index for filtering by action
CREATE INDEX IF NOT EXISTS idx_audit_logs_action 
ON audit_logs(action);

-- Index for date-based queries
CREATE INDEX IF NOT EXISTS idx_audit_logs_timestamp 
ON audit_logs(timestamp DESC);

-- ============================================
-- WORKFLOW VERSION INDEXES
-- ============================================

-- Index for finding versions of a workflow
CREATE INDEX IF NOT EXISTS idx_workflow_versions_workflow 
ON workflow_versions(workflowId);

-- Index for version number lookup
CREATE INDEX IF NOT EXISTS idx_workflow_versions_version 
ON workflow_versions(workflowId, version DESC);

-- Index for finding versions by creator
CREATE INDEX IF NOT EXISTS idx_workflow_versions_creator 
ON workflow_versions(createdBy);

-- ============================================
-- VACUUM AND ANALYZE (PostgreSQL only)
-- ============================================

-- Uncomment these lines when running on PostgreSQL
-- VACUUM ANALYZE workflows;
-- VACUUM ANALYZE workflow_executions;
-- VACUUM ANALYZE workflow_triggers;
-- VACUUM ANALYZE users;
-- VACUUM ANALYZE credentials;

-- ============================================
-- CONNECTION POOL SETTINGS (PostgreSQL)
-- ============================================

-- Adjust these in your DATABASE_URL connection string:
-- ?connection_limit=20           (max connections, default 10)
-- ?pool_timeout=30               (wait time in seconds)
-- ?connect_timeout=10            (connection timeout)

-- Example:
-- DATABASE_URL="postgresql://user:pass@localhost:5432/db?connection_limit=20&pool_timeout=30"

-- ============================================
-- MONITORING QUERIES
-- ============================================

-- Check index usage (PostgreSQL):
-- SELECT schemaname, tablename, indexname, idx_scan 
-- FROM pg_stat_user_indexes 
-- ORDER BY idx_scan ASC;

-- Check table sizes:
-- SELECT tablename, pg_size_pretty(pg_total_relation_size(tablename::text)) as size
-- FROM pg_tables
-- WHERE schemaname = 'public'
-- ORDER BY pg_total_relation_size(tablename::text) DESC;



