-- Migration: Add company_id to teams table
-- Date: 2025-01-XX
-- Description: Adds company_id column to teams table to associate teams with companies

-- ===== Add company_id to teams =====
-- Step 1: Add column as nullable first
ALTER TABLE teams 
ADD COLUMN company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER id;

-- Step 2: For existing teams without company_id, set a default company_id
-- Note: This assumes there's at least one company. If no companies exist, this will need manual intervention.
-- We'll try to get the first company, or leave NULL if no companies exist
UPDATE teams t
SET t.company_id = (
  SELECT c.id FROM companies c LIMIT 1
)
WHERE t.company_id IS NULL;

-- Step 3: Make company_id NOT NULL (only if all teams have been assigned a company_id)
-- If there are teams without company_id, this will fail and needs manual intervention
ALTER TABLE teams 
MODIFY COLUMN company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

-- Try to add index, ignore error if it already exists  
ALTER TABLE teams 
ADD INDEX idx_company (company_id);

-- Try to add foreign key, ignore error if it already exists
ALTER TABLE teams 
ADD CONSTRAINT fk_teams_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE;

