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

-- ===== Project Teams (Many-to-Many relationship) =====
CREATE TABLE IF NOT EXISTS project_teams (
    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 (project_id, team_id),
    INDEX idx_project (project_id),
    INDEX idx_team (team_id),
    FOREIGN KEY (project_id) REFERENCES 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 projects.team_id to project_teams
-- Only migrate if team_id is not NULL
INSERT INTO project_teams (project_id, team_id)
SELECT id, team_id
FROM projects
WHERE team_id IS NOT NULL
ON DUPLICATE KEY UPDATE project_id = project_id;

