-- Add votes column to gallery table
ALTER TABLE gallery ADD COLUMN IF NOT EXISTS votes INTEGER DEFAULT 0;

-- Create gallery_votes tracking table to prevent duplicate votes
CREATE TABLE IF NOT EXISTS gallery_votes (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  gallery_id UUID NOT NULL REFERENCES gallery(id) ON DELETE CASCADE,
  ip_hash VARCHAR(16) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  UNIQUE(gallery_id, ip_hash)
);

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_gallery_votes_gallery_id ON gallery_votes(gallery_id);
CREATE INDEX IF NOT EXISTS idx_gallery_votes_ip_hash ON gallery_votes(ip_hash);

-- Create function to increment gallery votes
CREATE OR REPLACE FUNCTION increment_gallery_votes(row_id UUID)
RETURNS void AS \$\$
BEGIN
  UPDATE gallery
  SET votes = votes + 1
  WHERE id = row_id;
END;
\$\$ LANGUAGE plpgsql;