lti-api/internal/database/migrations/20251117092514_adjustment_projectflock_period.up.sql

BEGIN;

ALTER TABLE project_flock_kandangs
    ADD COLUMN IF NOT EXISTS period INT;

UPDATE project_flock_kandangs pfk
SET period = pf.period
FROM project_flocks pf
WHERE pfk.project_flock_id = pf.id
  AND (pfk.period IS NULL OR pfk.period = 0)
  AND pf.period IS NOT NULL;

ALTER TABLE project_flock_kandangs
    ALTER COLUMN period SET DEFAULT 0;

UPDATE project_flock_kandangs
SET period = 0
WHERE period IS NULL;

ALTER TABLE project_flock_kandangs
    ALTER COLUMN period SET NOT NULL;

-- Drop period from project_flocks as the source of truth
DROP INDEX IF EXISTS project_flocks_base_period_unique;

ALTER TABLE project_flocks
    DROP COLUMN IF EXISTS period;

COMMIT;