-- Migration 001: Add GitHub Integration Tables (PostgreSQL Only)
-- This migration adds support for GitHub repository integration

-- Create table for GitHub repositories
CREATE TABLE IF NOT EXISTS github_repositories (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID REFERENCES templates(id) ON DELETE CASCADE,
  repository_url VARCHAR(500) NOT NULL,
  repository_name VARCHAR(200) NOT NULL,
  owner_name VARCHAR(100) NOT NULL,
  branch_name VARCHAR(100) DEFAULT 'main',
  is_public BOOLEAN DEFAULT true,
  requires_auth BOOLEAN DEFAULT false,
  last_synced_at TIMESTAMP,
  sync_status VARCHAR(50) DEFAULT 'pending',
  metadata JSONB,
  codebase_analysis JSONB,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create table for feature-codebase mappings
CREATE TABLE IF NOT EXISTS feature_codebase_mappings (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  template_id UUID REFERENCES templates(id) ON DELETE CASCADE,
  feature_id UUID REFERENCES template_features(id) ON DELETE CASCADE,
  repository_id UUID REFERENCES github_repositories(id) ON DELETE CASCADE,
  file_paths TEXT[],
  code_snippets JSONB,
  implementation_notes TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_github_repos_template_id ON github_repositories(template_id);
CREATE INDEX IF NOT EXISTS idx_github_repos_owner_name ON github_repositories(owner_name);
CREATE INDEX IF NOT EXISTS idx_feature_mappings_feature_id ON feature_codebase_mappings(feature_id);
CREATE INDEX IF NOT EXISTS idx_feature_mappings_repo_id ON feature_codebase_mappings(repository_id);

-- Add trigger to update timestamp
CREATE TRIGGER update_github_repos_updated_at BEFORE UPDATE ON github_repositories
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- =============================================
-- GitHub OAuth Tables
-- =============================================

-- Create table to store GitHub OAuth tokens
CREATE TABLE IF NOT EXISTS github_user_tokens (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  access_token TEXT NOT NULL,
  github_username VARCHAR(100) NOT NULL,
  github_user_id INTEGER NOT NULL,
  scopes JSONB,
  expires_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_github_user_tokens_github_username ON github_user_tokens(github_username);
