ALTER TABLE org_divisions
    ADD COLUMN description VARCHAR(500);

CREATE TABLE org_division_members (
    id BIGSERIAL PRIMARY KEY,
    division_id BIGINT NOT NULL REFERENCES org_divisions (id) ON DELETE CASCADE,
    user_id BIGINT NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    org_role VARCHAR(64) NOT NULL DEFAULT 'staff',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (division_id, user_id)
);

CREATE TRIGGER tr_org_division_members_updated
    BEFORE UPDATE ON org_division_members
    FOR EACH ROW EXECUTE FUNCTION workpulse_set_updated_at();

CREATE INDEX idx_org_division_members_division ON org_division_members (division_id);
CREATE INDEX idx_org_division_members_user ON org_division_members (user_id);

INSERT INTO org_division_members (division_id, user_id, org_role)
SELECT primary_org_division_id, id, 'staff'
FROM users
WHERE primary_org_division_id IS NOT NULL;

ALTER TABLE users DROP COLUMN IF EXISTS primary_org_division_id;
