BEGIN;

CREATE TABLE IF NOT EXISTS committee_member (
  committee_id BIGINT NOT NULL REFERENCES committee(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by BIGINT NULL REFERENCES app_user(id) ON DELETE SET NULL,
  PRIMARY KEY (committee_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_committee_member_user_id
  ON committee_member (user_id);

CREATE INDEX IF NOT EXISTS idx_committee_member_committee_id
  ON committee_member (committee_id);

INSERT INTO committee_member (committee_id, user_id, created_at, created_by)
SELECT u.committee_id, u.id, NOW(), NULL
  FROM app_user u
 WHERE u.committee_id IS NOT NULL
ON CONFLICT (committee_id, user_id) DO NOTHING;

COMMIT;