-- =====================================================================
-- Migration 005: Adds Browser ID for anonymous User Identification
-- =====================================================================

-- Adds browser_id Column to Contributions
ALTER TABLE contributions
    ADD COLUMN browser_id VARCHAR(36) DEFAULT NULL;

-- Adds browser_id Column to Votes
-- Replaces voter_name for Identification
ALTER TABLE votes
    ADD COLUMN browser_id VARCHAR(36) DEFAULT NULL;

-- Index for fast Vote Lookup by Browser
CREATE INDEX idx_votes_browser ON votes(browser_id);


-- New UNIQUE Constraint: One Vote per Browser per Contribution

-- Drops old Constraint voter_name based
ALTER TABLE votes
    DROP CONSTRAINT IF EXISTS votes_unique_per_voter;

-- Creates new Constraint browser_id based
ALTER TABLE votes
    ADD CONSTRAINT votes_contribution_browser_unique
    UNIQUE (contribution_id, browser_id);