likwid/backend/migrations/20260126140000_advanced_voting.sql

-- Advanced Voting Methods
-- Implements Schulze, STAR, Quadratic, and Ranked Choice voting

-- Voting method enum (extend existing)
DO $$
BEGIN
    -- Add new voting methods if they don't exist
    ALTER TYPE proposal_status ADD VALUE IF NOT EXISTS 'calculating';
EXCEPTION
    WHEN duplicate_object THEN null;
END $$;

-- Ranked ballots for Schulze/Ranked Choice
CREATE TABLE ranked_ballots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    voter_id UUID NOT NULL REFERENCES voting_identities(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id, voter_id)
);

-- Individual rankings within a ballot
CREATE TABLE ranked_ballot_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ballot_id UUID NOT NULL REFERENCES ranked_ballots(id) ON DELETE CASCADE,
    option_id UUID NOT NULL REFERENCES proposal_options(id) ON DELETE CASCADE,
    rank INT NOT NULL, -- 1 = first choice, 2 = second, etc.
    UNIQUE(ballot_id, option_id),
    UNIQUE(ballot_id, rank)
);

-- Score ballots for STAR voting
CREATE TABLE score_ballots (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    voter_id UUID NOT NULL REFERENCES voting_identities(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id, voter_id)
);

-- Individual scores within a ballot (0-5 scale)
CREATE TABLE score_ballot_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    ballot_id UUID NOT NULL REFERENCES score_ballots(id) ON DELETE CASCADE,
    option_id UUID NOT NULL REFERENCES proposal_options(id) ON DELETE CASCADE,
    score INT NOT NULL CHECK (score >= 0 AND score <= 5),
    UNIQUE(ballot_id, option_id)
);

-- Quadratic voting credits and allocations
CREATE TABLE quadratic_budgets (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    voter_id UUID NOT NULL REFERENCES voting_identities(id),
    total_credits INT NOT NULL DEFAULT 100,
    used_credits INT NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id, voter_id)
);

-- Quadratic vote allocations
CREATE TABLE quadratic_allocations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    budget_id UUID NOT NULL REFERENCES quadratic_budgets(id) ON DELETE CASCADE,
    option_id UUID NOT NULL REFERENCES proposal_options(id) ON DELETE CASCADE,
    votes INT NOT NULL DEFAULT 0, -- Actual votes (cost = votes^2)
    UNIQUE(budget_id, option_id)
);

-- Voting results cache (for complex calculations)
CREATE TABLE voting_results (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    voting_method VARCHAR(50) NOT NULL,
    winner_option_id UUID REFERENCES proposal_options(id),
    results_json JSONB NOT NULL, -- Full results including rankings, scores, etc.
    calculated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE(proposal_id)
);

-- Pairwise comparison matrix (for Schulze)
CREATE TABLE pairwise_matrix (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    proposal_id UUID NOT NULL REFERENCES proposals(id) ON DELETE CASCADE,
    option_a_id UUID NOT NULL REFERENCES proposal_options(id) ON DELETE CASCADE,
    option_b_id UUID NOT NULL REFERENCES proposal_options(id) ON DELETE CASCADE,
    a_over_b INT NOT NULL DEFAULT 0, -- Number of voters who prefer A over B
    UNIQUE(proposal_id, option_a_id, option_b_id)
);

-- Add voting method configuration to proposals
ALTER TABLE proposals ADD COLUMN IF NOT EXISTS voting_config JSONB DEFAULT '{}';
ALTER TABLE proposals ADD COLUMN IF NOT EXISTS quadratic_credit_budget INT DEFAULT 100;

-- Indexes
CREATE INDEX idx_ranked_ballots_proposal ON ranked_ballots(proposal_id);
CREATE INDEX idx_ranked_ballot_entries_ballot ON ranked_ballot_entries(ballot_id);
CREATE INDEX idx_score_ballots_proposal ON score_ballots(proposal_id);
CREATE INDEX idx_score_ballot_entries_ballot ON score_ballot_entries(ballot_id);
CREATE INDEX idx_quadratic_budgets_proposal ON quadratic_budgets(proposal_id);
CREATE INDEX idx_quadratic_allocations_budget ON quadratic_allocations(budget_id);
CREATE INDEX idx_voting_results_proposal ON voting_results(proposal_id);
CREATE INDEX idx_pairwise_matrix_proposal ON pairwise_matrix(proposal_id);

-- Triggers
CREATE TRIGGER ranked_ballots_updated_at BEFORE UPDATE ON ranked_ballots
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER score_ballots_updated_at BEFORE UPDATE ON score_ballots
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER quadratic_budgets_updated_at BEFORE UPDATE ON quadratic_budgets
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();