-- Migration: Add teams and user_teams tables
-- Date: 2025-01-XX
-- Description: Creates teams table and user_teams many-to-many relationship, adds team_id to template_projects and projects

-- ===== Teams =====
CREATE TABLE IF NOT EXISTS teams (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== User Teams (Many-to-Many relationship) =====
CREATE TABLE IF NOT EXISTS user_teams (
    user_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    team_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, team_id),
    INDEX idx_user (user_id),
    INDEX idx_team (team_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Add team_id to template_projects =====
-- Try to add column, ignore error if it already exists
ALTER TABLE template_projects 
ADD COLUMN team_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER description;

-- Try to add index, ignore error if it already exists  
ALTER TABLE template_projects 
ADD INDEX idx_team (team_id);

-- Try to add foreign key, ignore error if it already exists
ALTER TABLE template_projects 
ADD CONSTRAINT fk_template_projects_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL;

-- ===== Add team_id to projects =====
-- Try to add column, ignore error if it already exists
ALTER TABLE projects 
ADD COLUMN team_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER company_id;

-- Try to add index, ignore error if it already exists  
ALTER TABLE projects 
ADD INDEX idx_team (team_id);

-- Try to add foreign key, ignore error if it already exists
ALTER TABLE projects 
ADD CONSTRAINT fk_projects_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE SET NULL;

