-- Curator votes to delete a movement lineage link (admin performs the actual delete).

CREATE TABLE IF NOT EXISTS movement_lineage_delete_votes (
  id SERIAL PRIMARY KEY,
  link_id INTEGER NOT NULL REFERENCES movement_lineage_links(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT movement_lineage_delete_votes_unique UNIQUE (link_id, user_id),
  CONSTRAINT movement_lineage_delete_votes_reason_len CHECK (char_length(trim(reason)) >= 10)
);

CREATE INDEX IF NOT EXISTS movement_lineage_delete_votes_link_idx
  ON movement_lineage_delete_votes (link_id);
CREATE INDEX IF NOT EXISTS movement_lineage_delete_votes_user_idx
  ON movement_lineage_delete_votes (user_id);