-- Migration: Add template_teams table for N:N relationship
-- Date: 2025-01-XX
-- Description: Creates template_teams table to enable many-to-many relationship between templates and teams

-- ===== Template Teams (Many-to-Many relationship) =====
CREATE TABLE IF NOT EXISTS template_teams (
    template_project_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 (template_project_id, team_id),
    INDEX idx_template (template_project_id),
    INDEX idx_team (team_id),
    FOREIGN KEY (template_project_id) REFERENCES template_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Migrate existing data from template_projects.team_id to template_teams
-- Only migrate if team_id is not NULL
INSERT INTO template_teams (template_project_id, team_id)
SELECT id, team_id
FROM template_projects
WHERE team_id IS NOT NULL
ON DUPLICATE KEY UPDATE template_project_id = template_project_id;

