-- Migration 003: Add user_id to tables that reference template_id
-- This ensures we always track which user owns/initiated records tied to a template

-- Add user_id to github_repositories
ALTER TABLE IF EXISTS github_repositories
  ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id) ON DELETE CASCADE;

-- Indexes for github_repositories
CREATE INDEX IF NOT EXISTS idx_github_repos_user_id ON github_repositories(user_id);
CREATE INDEX IF NOT EXISTS idx_github_repos_template_user ON github_repositories(template_id, user_id);

-- Add user_id to feature_codebase_mappings
ALTER TABLE IF EXISTS feature_codebase_mappings
  ADD COLUMN IF NOT EXISTS user_id UUID REFERENCES users(id) ON DELETE CASCADE;

-- Indexes for feature_codebase_mappings
CREATE INDEX IF NOT EXISTS idx_feature_mappings_user_id ON feature_codebase_mappings(user_id);
CREATE INDEX IF NOT EXISTS idx_feature_mappings_template_user ON feature_codebase_mappings(template_id, user_id);

-- Note: Columns are nullable to allow backfill before enforcing NOT NULL if desired

