lti-api/internal/database/migrations/20260309085123_alter_table_employee_kandangs.up.sql

BEGIN;

ALTER TABLE employee_kandangs
    DROP CONSTRAINT IF EXISTS fk_employee_kandangs_kandang;

ALTER TABLE employee_kandangs
    DROP CONSTRAINT IF EXISTS uq_employee_kandangs;

DO $$
BEGIN
    IF EXISTS (
        SELECT 1
        FROM employee_kandangs ek
        LEFT JOIN kandangs k ON k.id = ek.kandang_id
        WHERE k.kandang_group_id IS NULL
    ) THEN
        RAISE EXCEPTION 'Cannot migrate employee_kandangs: kandang_id has no kandang_group_id mapping';
    END IF;
END $$;

UPDATE employee_kandangs ek
SET kandang_id = k.kandang_group_id,
    updated_at = NOW()
FROM kandangs k
WHERE k.id = ek.kandang_id;

DELETE FROM employee_kandangs ek1
USING employee_kandangs ek2
WHERE ek1.id > ek2.id
  AND ek1.employee_id = ek2.employee_id
  AND ek1.kandang_id = ek2.kandang_id;

ALTER TABLE employee_kandangs
    ADD CONSTRAINT fk_employee_kandangs_kandang
        FOREIGN KEY (kandang_id) REFERENCES kandang_groups (id)
            ON DELETE CASCADE;

ALTER TABLE employee_kandangs
    ADD CONSTRAINT uq_employee_kandangs UNIQUE (employee_id, kandang_id);

COMMIT;