#!/usr/bin/env node
const { pool, query } = require("./config/database");

async function applyPartialFixes() {
  console.log("🔧 Applying Database Fixes (User-Level)...\n");

  try {
    console.log("1️⃣ Creating Indexes...");

    // Products indexes
    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_isactive ON products(isactive) WHERE isactive = true`
    );
    console.log("   ✅ idx_products_isactive");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_isfeatured ON products(isfeatured, createdat DESC) WHERE isfeatured = true AND isactive = true`
    );
    console.log("   ✅ idx_products_isfeatured");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_isbestseller ON products(isbestseller, createdat DESC) WHERE isbestseller = true AND isactive = true`
    );
    console.log("   ✅ idx_products_isbestseller");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_category ON products(category, createdat DESC) WHERE isactive = true AND category IS NOT NULL`
    );
    console.log("   ✅ idx_products_category");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_createdat ON products(createdat DESC) WHERE isactive = true`
    );
    console.log("   ✅ idx_products_createdat");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_products_price ON products(price) WHERE isactive = true`
    );
    console.log("   ✅ idx_products_price");

    // Portfolio indexes
    await query(
      `CREATE INDEX IF NOT EXISTS idx_portfolio_isactive ON portfolioprojects(isactive) WHERE isactive = true`
    );
    console.log("   ✅ idx_portfolio_isactive");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_portfolio_category ON portfolioprojects(category) WHERE isactive = true`
    );
    console.log("   ✅ idx_portfolio_category");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_portfolio_displayorder ON portfolioprojects(displayorder ASC, createdat DESC) WHERE isactive = true`
    );
    console.log("   ✅ idx_portfolio_displayorder");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_portfolio_createdat ON portfolioprojects(createdat DESC) WHERE isactive = true`
    );
    console.log("   ✅ idx_portfolio_createdat");

    // Pages indexes
    await query(
      `CREATE INDEX IF NOT EXISTS idx_pages_slug ON pages(slug) WHERE isactive = true`
    );
    console.log("   ✅ idx_pages_slug");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_pages_isactive ON pages(isactive) WHERE isactive = true`
    );
    console.log("   ✅ idx_pages_isactive");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_pages_createdat ON pages(createdat DESC) WHERE isactive = true`
    );
    console.log("   ✅ idx_pages_createdat");

    // Product images indexes
    await query(
      `CREATE INDEX IF NOT EXISTS idx_product_images_color_variant ON product_images(color_variant) WHERE color_variant IS NOT NULL`
    );
    console.log("   ✅ idx_product_images_color_variant");

    await query(
      `CREATE INDEX IF NOT EXISTS idx_product_images_color_code ON product_images(color_code) WHERE color_code IS NOT NULL`
    );
    console.log("   ✅ idx_product_images_color_code");

    console.log("\n2️⃣ Adding Foreign Keys...");
    try {
      await query(`
        DO $$
        BEGIN
          IF NOT EXISTS (
            SELECT 1 FROM information_schema.table_constraints 
            WHERE constraint_name = 'fk_product_images_product'
          ) THEN
            ALTER TABLE product_images 
            ADD CONSTRAINT fk_product_images_product 
            FOREIGN KEY (product_id) REFERENCES products(id) 
            ON DELETE CASCADE;
          END IF;
        END $$;
      `);
      console.log("   ✅ product_images -> products");
    } catch (e) {
      console.log("   ⚠️  product_images FK:", e.message);
    }

    try {
      await query(`
        DO $$
        BEGIN
          UPDATE uploads SET folder_id = NULL 
          WHERE folder_id NOT IN (SELECT id FROM media_folders);
          
          IF NOT EXISTS (
            SELECT 1 FROM information_schema.table_constraints 
            WHERE constraint_name = 'fk_uploads_folder'
          ) THEN
            ALTER TABLE uploads 
            ADD CONSTRAINT fk_uploads_folder 
            FOREIGN KEY (folder_id) REFERENCES media_folders(id) 
            ON DELETE SET NULL;
          END IF;
        END $$;
      `);
      console.log("   ✅ uploads -> media_folders");
    } catch (e) {
      console.log("   ⚠️  uploads FK:", e.message);
    }

    console.log("\n3️⃣ Adding Unique Constraints...");
    try {
      await query(`
        DO $$
        BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'unique_products_slug') THEN
            WITH duplicates AS (
              SELECT slug, array_agg(id) as ids
              FROM products 
              WHERE slug IS NOT NULL
              GROUP BY slug 
              HAVING COUNT(*) > 1
            )
            UPDATE products p
            SET slug = p.slug || '-' || substring(p.id, 1, 8)
            WHERE p.id IN (SELECT unnest(ids[2:]) FROM duplicates);
            
            ALTER TABLE products ADD CONSTRAINT unique_products_slug UNIQUE(slug);
          END IF;
        END $$;
      `);
      console.log("   ✅ products.slug unique constraint");
    } catch (e) {
      console.log("   ⚠️  products.slug:", e.message);
    }

    try {
      await query(`
        DO $$
        BEGIN
          IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'unique_pages_slug') THEN
            WITH duplicates AS (
              SELECT slug, array_agg(id) as ids
              FROM pages 
              WHERE slug IS NOT NULL
              GROUP BY slug 
              HAVING COUNT(*) > 1
            )
            UPDATE pages p
            SET slug = p.slug || '-' || p.id::text
            WHERE p.id IN (SELECT unnest(ids[2:]) FROM duplicates);
            
            ALTER TABLE pages ADD CONSTRAINT unique_pages_slug UNIQUE(slug);
          END IF;
        END $$;
      `);
      console.log("   ✅ pages.slug unique constraint");
    } catch (e) {
      console.log("   ⚠️  pages.slug:", e.message);
    }

    console.log("\n4️⃣ Running ANALYZE...");
    await query("ANALYZE products");
    await query("ANALYZE product_images");
    await query("ANALYZE portfolioprojects");
    await query("ANALYZE blogposts");
    await query("ANALYZE pages");
    console.log("   ✅ Tables analyzed");

    console.log("\n📊 Final Status:");
    const indexCount = await query(`
      SELECT COUNT(*) as count
      FROM pg_indexes 
      WHERE schemaname = 'public'
        AND tablename IN ('products', 'product_images', 'portfolioprojects', 'blogposts', 'pages')
    `);
    console.log(`   Total indexes: ${indexCount.rows[0].count}`);

    const fkCount = await query(`
      SELECT COUNT(*) as count
      FROM information_schema.table_constraints
      WHERE constraint_type = 'FOREIGN KEY' AND table_schema = 'public'
    `);
    console.log(`   Foreign keys: ${fkCount.rows[0].count}`);

    console.log("\n✅ Database fixes applied successfully!");
  } catch (error) {
    console.error("❌ Error:", error.message);
    process.exit(1);
  } finally {
    await pool.end();
  }
}

applyPartialFixes();