-- Migration: Add inappropriate_photo_reports table for reporting inappropriate photos
-- This table tracks photos reported by users as inappropriate, pending admin review
-- Run this in the punimtag_auth database

CREATE TABLE IF NOT EXISTS inappropriate_photo_reports (
  id SERIAL PRIMARY KEY,
  photo_id INTEGER NOT NULL,
  user_id INTEGER NOT NULL,
  status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'reviewed', 'dismissed')),
  reported_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  reviewed_at TIMESTAMP,
  reviewed_by INTEGER,
  review_notes TEXT,
  
  CONSTRAINT fk_inappropriate_photo_reports_user 
    FOREIGN KEY (user_id) 
    REFERENCES users(id) 
    ON DELETE CASCADE,
  
  -- Prevent duplicate reports from same user for same photo
  CONSTRAINT uq_photo_user_report UNIQUE (photo_id, user_id)
);

-- Create indexes for efficient queries
CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_photo_id ON inappropriate_photo_reports(photo_id);
CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_user_id ON inappropriate_photo_reports(user_id);
CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_status ON inappropriate_photo_reports(status);
CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_reported_at ON inappropriate_photo_reports(reported_at);

-- Add comment to table
COMMENT ON TABLE inappropriate_photo_reports IS 'Stores reports of inappropriate photos submitted by users, pending admin review';

