-- Migration: add hardware_fingerprint and ip_address to voter_tokens
-- Run once against the electvote database
-- Replaces the previous device_fingerprint column if it exists

USE electvote;

-- Add hardware_fingerprint (browser canvas/WebGL hash, stable across incognito)
ALTER TABLE voter_tokens
  ADD COLUMN IF NOT EXISTS hardware_fingerprint VARCHAR(64) NULL DEFAULT NULL;

-- Add ip_address (server-side, immune to all client-side tricks)
ALTER TABLE voter_tokens
  ADD COLUMN IF NOT EXISTS ip_address VARCHAR(45) NULL DEFAULT NULL;

-- Drop old column from previous migration if it exists
ALTER TABLE voter_tokens
  DROP COLUMN IF EXISTS device_fingerprint;

-- Indexes for fast duplicate lookups
ALTER TABLE voter_tokens
  ADD INDEX IF NOT EXISTS idx_hw_fp_session  (hardware_fingerprint, session_id),
  ADD INDEX IF NOT EXISTS idx_ip_session     (ip_address, session_id);