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 photo_favorites table exists...\n');

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

    if (!tableExists) {
      // Create the table
      console.log('Creating photo_favorites table...');
      try {
        await prisma.$executeRawUnsafe(`
          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,
            
            CONSTRAINT uq_photo_user_favorite 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 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,
              
              CONSTRAINT uq_photo_user_favorite UNIQUE (photo_id, user_id)
            );
          `);
          console.log('✅ Table created');
        } else {
          throw error;
        }
      }
    }

    // Create indexes
    console.log('Creating indexes...');
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_photo_favorites_photo_id ON photo_favorites(photo_id);`
    );
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_photo_favorites_user_id ON photo_favorites(user_id);`
    );
    await prisma.$executeRawUnsafe(
      `CREATE INDEX IF NOT EXISTS idx_photo_favorites_favorited_at ON photo_favorites(favorited_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 photo_favorites IS 'Stores user favorites for photos';
      `);
      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 = 'photo_favorites';
    `;

    if (result.length > 0) {
      console.log('\n✅ Migration completed successfully!');
      console.log('Table photo_favorites 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();






