-- Migration 006: Add Gitea and Multi-Provider Support
-- This migration adds fields for supporting multiple Git providers like Gitea

-- Add new columns to projects table
ALTER TABLE projects 
ADD COLUMN IF NOT EXISTS provider VARCHAR(50) DEFAULT 'github';

ALTER TABLE projects 
ADD COLUMN IF NOT EXISTS provider_base_url VARCHAR(255) NULL;

ALTER TABLE projects 
ADD COLUMN IF NOT EXISTS auto_assignment BOOLEAN DEFAULT true;

-- Rename metadata column to avoid SQLAlchemy conflict
ALTER TABLE projects 
RENAME COLUMN metadata TO project_metadata;

-- Update existing records to have default provider
UPDATE projects SET provider = 'github' WHERE provider IS NULL;

-- Create index for provider for better queries
CREATE INDEX IF NOT EXISTS idx_projects_provider ON projects(provider);
CREATE INDEX IF NOT EXISTS idx_projects_bzzz_enabled ON projects(bzzz_enabled);
CREATE INDEX IF NOT EXISTS idx_projects_auto_assignment ON projects(auto_assignment);

-- Add comments for documentation
COMMENT ON COLUMN projects.provider IS 'Git provider type: github, gitea, gitlab, etc.';
COMMENT ON COLUMN projects.provider_base_url IS 'Base URL for self-hosted providers like Gitea';
COMMENT ON COLUMN projects.auto_assignment IS 'Enable automatic task assignment to agents';
COMMENT ON COLUMN projects.project_metadata IS 'Additional project metadata as JSON';