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

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

-- Step 2: For existing template_projects without company_id, set a default company_id
-- If companies table is empty, create a default company first
-- This ensures all template_projects can be assigned a company_id
-- Use INSERT IGNORE to avoid errors if company already exists
INSERT IGNORE INTO companies (id, name)
SELECT UUID(), 'Default Company'
WHERE NOT EXISTS (SELECT 1 FROM companies LIMIT 1);

-- Now update all template_projects to use the first available company
UPDATE template_projects tp
SET tp.company_id = (
  SELECT c.id FROM companies c LIMIT 1
)
WHERE tp.company_id IS NULL;

-- Step 3: Make company_id NOT NULL
-- At this point, all template_projects should have a company_id assigned
ALTER TABLE template_projects 
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 template_projects 
ADD INDEX idx_company (company_id);

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

