-- Migration 009b: Migrate Existing Projects to Personal Teams
-- Creates personal teams for existing users and assigns their projects to those teams.
-- This migration is idempotent and can be run multiple times safely.

-- Create personal teams for users who own projects but don't have a personal team yet
INSERT INTO teams (name, slug, description, created_by, settings)
SELECT DISTINCT
    u.username || '''s Team' AS name,
    LOWER(u.username) || '-personal' AS slug,
    'Personal team for ' || u.username AS description,
    u.username AS created_by,
    '{"personal": true}'::jsonb AS settings
FROM users u
JOIN projects p ON p.created_by = u.username
WHERE NOT EXISTS (
    SELECT 1 FROM teams t
    WHERE t.slug = LOWER(u.username) || '-personal'
)
AND p.team_id IS NULL
ON CONFLICT (slug) DO NOTHING;

-- Add users as owners of their personal teams
INSERT INTO team_memberships (team_id, user_id, role, invited_by)
SELECT
    t.id AS team_id,
    u.id AS user_id,
    'owner' AS role,
    u.username AS invited_by
FROM teams t
JOIN users u ON t.created_by = u.username
WHERE t.slug LIKE '%-personal'
AND NOT EXISTS (
    SELECT 1 FROM team_memberships tm
    WHERE tm.team_id = t.id
    AND tm.user_id = u.id
)
ON CONFLICT DO NOTHING;

-- Assign projects without a team to their creator's personal team
UPDATE projects p
SET team_id = t.id
FROM teams t
WHERE t.slug = LOWER(p.created_by) || '-personal'
AND p.team_id IS NULL;

-- Handle orphaned projects (created_by doesn't match any user)
-- Create a special orphaned projects team if there are any
DO $$
DECLARE
    orphan_count INTEGER;
    orphan_team_id UUID;
BEGIN
    -- Count orphaned projects
    SELECT COUNT(*) INTO orphan_count
    FROM projects p
    WHERE p.team_id IS NULL
    AND NOT EXISTS (
        SELECT 1 FROM users u WHERE u.username = p.created_by
    );

    IF orphan_count > 0 THEN
        -- Create or get the orphaned projects team
        INSERT INTO teams (name, slug, description, created_by, settings)
        VALUES (
            'Orphaned Projects',
            'orphaned-projects',
            'Projects whose original creators no longer exist',
            'system',
            '{"system": true}'::jsonb
        )
        ON CONFLICT (slug) DO UPDATE SET name = teams.name
        RETURNING id INTO orphan_team_id;

        -- Assign orphaned projects to this team
        UPDATE projects
        SET team_id = orphan_team_id
        WHERE team_id IS NULL
        AND NOT EXISTS (
            SELECT 1 FROM users u WHERE u.username = projects.created_by
        );

        RAISE NOTICE 'Migrated % orphaned project(s) to orphaned-projects team', orphan_count;
    END IF;
END $$;

-- Log migration results
DO $$
DECLARE
    teams_created INTEGER;
    memberships_created INTEGER;
    projects_migrated INTEGER;
BEGIN
    SELECT COUNT(*) INTO teams_created FROM teams WHERE slug LIKE '%-personal';
    SELECT COUNT(*) INTO memberships_created FROM team_memberships;
    SELECT COUNT(*) INTO projects_migrated FROM projects WHERE team_id IS NOT NULL;

    RAISE NOTICE 'Migration complete: % personal teams, % memberships, % projects with teams',
        teams_created, memberships_created, projects_migrated;
END $$;