br-office-suite/database/migrations/024_decision_voting.sql

BEGIN;

ALTER TABLE decision
  ADD COLUMN IF NOT EXISTS voting_open BOOLEAN NOT NULL DEFAULT FALSE,
  ADD COLUMN IF NOT EXISTS voting_started_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS voting_started_by BIGINT REFERENCES app_user(id),
  ADD COLUMN IF NOT EXISTS voting_closed_at TIMESTAMPTZ,
  ADD COLUMN IF NOT EXISTS voting_closed_by BIGINT REFERENCES app_user(id);

CREATE TABLE IF NOT EXISTS decision_vote (
  decision_id BIGINT NOT NULL REFERENCES decision(id) ON DELETE CASCADE,
  user_id BIGINT NOT NULL REFERENCES app_user(id) ON DELETE CASCADE,
  vote_value VARCHAR(20) NOT NULL CHECK (vote_value IN ('yes', 'no', 'abstain')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (decision_id, user_id)
);

CREATE INDEX IF NOT EXISTS idx_decision_vote_decision_id
  ON decision_vote(decision_id);

CREATE INDEX IF NOT EXISTS idx_decision_vote_user_id
  ON decision_vote(user_id);

DO $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'trg_decision_vote_updated_at') THEN
    CREATE TRIGGER trg_decision_vote_updated_at
      BEFORE UPDATE ON decision_vote
      FOR EACH ROW
      EXECUTE FUNCTION set_updated_at();
  END IF;
END $$;

ALTER TABLE decision_vote ENABLE ROW LEVEL SECURITY;
ALTER TABLE decision_vote FORCE ROW LEVEL SECURITY;

-- Benutzer desselben Gremiums dürfen sich gegenseitig für Abstimmungslisten sehen.
-- Admin/chair behalten den bisherigen erweiterten Zugriff; jeder Benutzer sieht sich weiterhin selbst.
DROP POLICY IF EXISTS user_select ON app_user;
CREATE POLICY user_select ON app_user FOR SELECT USING (
  app_role() IN ('admin','chair')
  OR id = app_user_id()
  OR EXISTS (
    SELECT 1
      FROM committee_member mine
      JOIN committee_member other_member ON other_member.committee_id = mine.committee_id
     WHERE mine.user_id = app_user_id()
       AND other_member.user_id = app_user.id
  )
);

INSERT INTO app_permission(permission_key, label, category, description)
VALUES
  ('decisions.vote.manage', 'Abstimmungen verwalten', 'Beschlüsse', 'Beschluss-Abstimmungen öffnen, schließen und auswerten')
ON CONFLICT (permission_key) DO UPDATE
  SET label = EXCLUDED.label,
      category = EXCLUDED.category,
      description = EXCLUDED.description;

INSERT INTO app_role_permission(role_profile_id, permission_key, granted)
SELECT id, 'decisions.vote.manage', TRUE
  FROM app_role_profile
 WHERE code IN ('admin', 'chair', 'secretary')
ON CONFLICT (role_profile_id, permission_key) DO UPDATE SET granted = TRUE;

DROP POLICY IF EXISTS decision_modify_policy ON decision;
CREATE POLICY decision_modify_policy ON decision FOR ALL USING (
  app_has_permission('decisions.write')
  OR app_has_permission('decisions.vote.manage')
  OR responsible_user_id = app_user_id()
  OR EXISTS (
    SELECT 1
      FROM meeting m
      JOIN committee_member cm ON cm.committee_id = m.committee_id
     WHERE m.id = decision.meeting_id
       AND cm.user_id = app_user_id()
  )
) WITH CHECK (
  app_has_permission('decisions.write')
  OR app_has_permission('decisions.vote.manage')
  OR responsible_user_id = app_user_id()
  OR EXISTS (
    SELECT 1
      FROM meeting m
      JOIN committee_member cm ON cm.committee_id = m.committee_id
     WHERE m.id = decision.meeting_id
       AND cm.user_id = app_user_id()
  )
);

DROP POLICY IF EXISTS decision_vote_select_policy ON decision_vote;
DROP POLICY IF EXISTS decision_vote_insert_policy ON decision_vote;
DROP POLICY IF EXISTS decision_vote_update_policy ON decision_vote;
DROP POLICY IF EXISTS decision_vote_delete_policy ON decision_vote;

CREATE POLICY decision_vote_select_policy ON decision_vote FOR SELECT USING (
  user_id = app_user_id()
  OR app_has_permission('decisions.vote.manage')
  OR EXISTS (
    SELECT 1
      FROM decision d
      JOIN meeting m ON m.id = d.meeting_id
      JOIN committee_member cm ON cm.committee_id = m.committee_id
     WHERE d.id = decision_vote.decision_id
       AND cm.user_id = app_user_id()
  )
);

CREATE POLICY decision_vote_insert_policy ON decision_vote FOR INSERT WITH CHECK (
  user_id = app_user_id()
  AND app_role() <> 'advisor'
  AND EXISTS (
    SELECT 1
      FROM decision d
      JOIN meeting m ON m.id = d.meeting_id
      JOIN committee_member cm ON cm.committee_id = m.committee_id
     WHERE d.id = decision_vote.decision_id
       AND d.voting_open = TRUE
       AND m.status = 'gestartet'
       AND cm.user_id = app_user_id()
  )
);

CREATE POLICY decision_vote_update_policy ON decision_vote FOR UPDATE USING (
  user_id = app_user_id()
  AND app_role() <> 'advisor'
  AND EXISTS (
    SELECT 1
      FROM decision d
      JOIN meeting m ON m.id = d.meeting_id
      JOIN committee_member cm ON cm.committee_id = m.committee_id
     WHERE d.id = decision_vote.decision_id
       AND d.voting_open = TRUE
       AND m.status = 'gestartet'
       AND cm.user_id = app_user_id()
  )
) WITH CHECK (
  user_id = app_user_id()
  AND app_role() <> 'advisor'
);

CREATE POLICY decision_vote_delete_policy ON decision_vote FOR DELETE USING (
  app_has_permission('decisions.vote.manage')
);


CREATE OR REPLACE FUNCTION sync_decision_vote_counts(p_decision_id BIGINT)
RETURNS TABLE(votes_yes INTEGER, votes_no INTEGER, votes_abstain INTEGER) AS $$
DECLARE
  yes_count INTEGER;
  no_count INTEGER;
  abstain_count INTEGER;
BEGIN
  SELECT COUNT(*) FILTER (WHERE vote_value = 'yes')::int,
         COUNT(*) FILTER (WHERE vote_value = 'no')::int,
         COUNT(*) FILTER (WHERE vote_value = 'abstain')::int
    INTO yes_count, no_count, abstain_count
    FROM decision_vote
   WHERE decision_id = p_decision_id;

  UPDATE decision
     SET votes_yes = COALESCE(yes_count, 0),
         votes_no = COALESCE(no_count, 0),
         votes_abstain = COALESCE(abstain_count, 0),
         updated_at = NOW()
   WHERE id = p_decision_id;

  RETURN QUERY SELECT COALESCE(yes_count, 0), COALESCE(no_count, 0), COALESCE(abstain_count, 0);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

GRANT EXECUTE ON FUNCTION sync_decision_vote_counts(BIGINT) TO PUBLIC;

COMMIT;