import { PrismaClient as PrismaClientAuth } from '../node_modules/.prisma/client-auth';
import * as dotenv from 'dotenv';

// Load environment variables
dotenv.config({ path: '.env' });

const prisma = new PrismaClientAuth({
  datasourceUrl: process.env.DATABASE_URL_AUTH,
});

async function runMigration() {
  try {
    console.log('Running migration to ensure inappropriate_photo_reports table exists...\n');

    // Check if table already exists
    let tableExists = true;
    try {
      await prisma.$queryRaw`SELECT id FROM inappropriate_photo_reports LIMIT 1`;
      console.log('✅ Table inappropriate_photo_reports already exists');
    } catch (error: any) {
      if (error.message?.includes('inappropriate_photo_reports') || error.code === '42P01') {
        console.log('Table does not exist, creating it...');
        tableExists = false;
      } else {
        throw error;
      }
    }

    if (!tableExists) {
      // Create the table (using same approach as setup-auth.ts)
      console.log('Creating inappropriate_photo_reports table...');
      try {
        await prisma.$executeRawUnsafe(`
          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,
            report_comment TEXT,
            
            CONSTRAINT fk_inappropriate_photo_reports_user 
              FOREIGN KEY (user_id) 
              REFERENCES users(id) 
              ON DELETE CASCADE,
            
            CONSTRAINT uq_photo_user_report UNIQUE (photo_id, user_id)
          );
        `);
        console.log('✅ Table created');
      } catch (error: any) {
        // If permission denied, try without IF NOT EXISTS (sometimes works)
        if (error.message?.includes('permission denied') || error.code === '42501') {
          console.log('Permission denied with IF NOT EXISTS, trying without...');
          await prisma.$executeRawUnsafe(`
            CREATE TABLE 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,
              report_comment TEXT,
              
              CONSTRAINT fk_inappropriate_photo_reports_user 
                FOREIGN KEY (user_id) 
                REFERENCES users(id) 
                ON DELETE CASCADE,
              
              CONSTRAINT uq_photo_user_report UNIQUE (photo_id, user_id)
            );
          `);
          console.log('✅ Table created');
        } else {
          throw error;
        }
      }
    }

    // Ensure the report_comment column exists (for older deployments)
    try {
      console.log('Ensuring report_comment column exists...');
      await prisma.$executeRawUnsafe(`
        ALTER TABLE inappropriate_photo_reports
        ADD COLUMN IF NOT EXISTS report_comment TEXT;
      `);
      console.log('✅ report_comment column ready');
    } catch (error: any) {
      console.log('⚠️  Could not ensure report_comment column (non-critical)', error.message);
    }

    // Create indexes
    console.log('Creating indexes...');
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_photo_id ON inappropriate_photo_reports(photo_id);`
    );
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_user_id ON inappropriate_photo_reports(user_id);`
    );
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_status ON inappropriate_photo_reports(status);`
    );
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_inappropriate_photo_reports_reported_at ON inappropriate_photo_reports(reported_at);`
    );
    console.log('✅ Indexes created');

    // Add comment (may fail if user doesn't have permission, but that's okay)
    try {
      console.log('Adding table comment...');
      await prisma.$executeRawUnsafe(`
        COMMENT ON TABLE inappropriate_photo_reports IS 'Stores reports of inappropriate photos submitted by users, pending admin review';
      `);
      console.log('✅ Comment added');
    } catch (error: any) {
      console.log('⚠️  Could not add comment (non-critical)');
    }

    // Verify
    const result = await prisma.$queryRaw<Array<{table_name: string}>>`
      SELECT table_name 
      FROM information_schema.tables 
      WHERE table_schema = 'public' AND table_name = 'inappropriate_photo_reports';
    `;

    if (result.length > 0) {
      console.log('\n✅ Migration completed successfully!');
      console.log('Table inappropriate_photo_reports is ready to use.');
    } else {
      throw new Error('Table was not created');
    }
  } catch (error: any) {
    console.error('\n❌ Migration failed:');
    console.error(error.message);
    if (error.code) {
      console.error(`Error code: ${error.code}`);
    }
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

runMigration();

