lti-api/internal/database/migrations/20251028110309_adjustments_flock_project_table.up.sql

BEGIN;

ALTER TABLE project_flocks
    ADD COLUMN IF NOT EXISTS flock_name VARCHAR(255);

WITH generated_names AS (
    SELECT
        pf.id,
        COALESCE(f.name, CONCAT('Project Flock ', pf.id)) AS base_name,
        pf.period,
        ROW_NUMBER() OVER (PARTITION BY COALESCE(f.name, CONCAT('Project Flock ', pf.id)) ORDER BY pf.id) AS rn
    FROM project_flocks pf
    LEFT JOIN flocks f ON f.id = pf.flock_id
)
UPDATE project_flocks pf
SET flock_name = CASE
        WHEN gn.period IS NOT NULL THEN
            CASE
                WHEN gn.rn = 1 THEN CONCAT(gn.base_name, ' ', gn.period)
                ELSE CONCAT(gn.base_name, ' ', gn.period, ' ', gn.rn)
            END
        ELSE
            CASE
                WHEN gn.rn = 1 THEN gn.base_name
                ELSE CONCAT(gn.base_name, ' ', gn.rn)
            END
    END
FROM generated_names gn
WHERE pf.id = gn.id
  AND (pf.flock_name IS NULL OR pf.flock_name = '');

UPDATE project_flocks
SET flock_name = CONCAT('Project Flock ', id)
WHERE flock_name IS NULL OR flock_name = '';

ALTER TABLE project_flocks
    ALTER COLUMN flock_name SET NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS project_flocks_flock_name_unique
    ON project_flocks (flock_name)
    WHERE deleted_at IS NULL;

DROP INDEX IF EXISTS project_flocks_flock_period_unique;

CREATE UNIQUE INDEX IF NOT EXISTS project_flocks_base_period_unique
    ON project_flocks (
        LOWER(TRIM(regexp_replace(flock_name, '\\s+\\d+(\\s+\\d+)*$', '', 'g'))),
        period
    )
    WHERE deleted_at IS NULL;

ALTER TABLE project_flocks
    DROP COLUMN IF EXISTS flock_id;

COMMIT;