-- Create verification_requests table
CREATE TABLE IF NOT EXISTS verification_requests (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  artist_id UUID NOT NULL REFERENCES artists(id) ON DELETE CASCADE,
  status VARCHAR(50) NOT NULL DEFAULT 'pending', -- pending, approved, rejected
  reason TEXT, -- Artist's reason for requesting verification
  rejection_reason TEXT, -- Admin's reason for rejection (if any)
  documents JSONB DEFAULT '[]', -- Array of document URLs for verification
  social_proof JSONB DEFAULT '{}', -- Links to social media, press, etc.
  reviewed_by UUID REFERENCES users(id),
  reviewed_at TIMESTAMP,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create unique index only for pending requests to allow one pending at a time
CREATE UNIQUE INDEX IF NOT EXISTS idx_verification_requests_artist_pending 
  ON verification_requests(artist_id) WHERE status = 'pending';

-- Create indexes for faster lookups
CREATE INDEX IF NOT EXISTS idx_verification_requests_artist ON verification_requests(artist_id);
CREATE INDEX IF NOT EXISTS idx_verification_requests_status ON verification_requests(status);
CREATE INDEX IF NOT EXISTS idx_verification_requests_created ON verification_requests(created_at DESC);