lti-api/internal/database/migrations/20251018072532_project_flock_kandangs.up.sql

CREATE TABLE project_flock_kandangs (
    id BIGSERIAL PRIMARY KEY,
    project_flock_id BIGINT NOT NULL REFERENCES project_flocks (id) ON DELETE CASCADE ON UPDATE CASCADE,
    kandang_id BIGINT NOT NULL REFERENCES kandangs (id) ON DELETE CASCADE ON UPDATE CASCADE,
    created_by BIGINT REFERENCES users (id) ON DELETE SET NULL ON UPDATE CASCADE,
    assigned_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    detached_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_project_flock_kandangs_project ON project_flock_kandangs (project_flock_id);
CREATE INDEX idx_project_flock_kandangs_kandang ON project_flock_kandangs (kandang_id);

CREATE UNIQUE INDEX idx_project_flock_kandangs_active ON project_flock_kandangs (project_flock_id, kandang_id)
WHERE
    detached_at IS NULL;