-- Migration: Add photo_favorites table for user favorites
-- This table tracks photos favorited by users
-- Run this in the punimtag_auth database

CREATE TABLE IF NOT EXISTS photo_favorites (
  id SERIAL PRIMARY KEY,
  photo_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  favorited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  
  CONSTRAINT fk_photo_favorites_user 
    FOREIGN KEY (user_id) 
    REFERENCES users(id) 
    ON DELETE CASCADE,
  
  -- Prevent duplicate favorites from same user for same photo
  CONSTRAINT uq_photo_user_favorite UNIQUE (photo_id, user_id)
);

-- Create indexes for efficient queries
CREATE INDEX IF NOT EXISTS idx_photo_favorites_photo_id ON photo_favorites(photo_id);
CREATE INDEX IF NOT EXISTS idx_photo_favorites_user_id ON photo_favorites(user_id);
CREATE INDEX IF NOT EXISTS idx_photo_favorites_favorited_at ON photo_favorites(favorited_at);

-- Add comment to table
COMMENT ON TABLE photo_favorites IS 'Stores user favorites for photos';






