SkyArtShop/backend/analyze-schema.js

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

async function analyzeSchema() {
  console.log("🔬 Analyzing Database Schema...\n");

  try {
    // 1. Check products table columns
    console.log("1️⃣ Products Table Structure:");
    const productCols = await query(`
      SELECT column_name, data_type, is_nullable, column_default
      FROM information_schema.columns 
      WHERE table_name = 'products'
      ORDER BY ordinal_position
    `);
    productCols.rows.forEach((col) => {
      const nullable = col.is_nullable === "YES" ? "(nullable)" : "(NOT NULL)";
      console.log(
        `   ${col.column_name.padEnd(20)} ${col.data_type.padEnd(
          25
        )} ${nullable}`
      );
    });

    // 2. Check products indexes
    console.log("\n2️⃣ Products Table Indexes:");
    const productIndexes = await query(`
      SELECT indexname, indexdef 
      FROM pg_indexes 
      WHERE tablename = 'products'
      ORDER BY indexname
    `);
    productIndexes.rows.forEach((idx) => {
      console.log(`   ${idx.indexname}`);
      console.log(`      ${idx.indexdef.substring(0, 80)}...`);
    });

    // 3. Check portfolio projects structure
    console.log("\n3️⃣ Portfolio Projects Structure:");
    const portfolioCols = await query(`
      SELECT column_name, data_type, is_nullable
      FROM information_schema.columns 
      WHERE table_name = 'portfolioprojects'
      ORDER BY ordinal_position
    `);
    portfolioCols.rows.forEach((col) => {
      const nullable = col.is_nullable === "YES" ? "(nullable)" : "(NOT NULL)";
      console.log(
        `   ${col.column_name.padEnd(20)} ${col.data_type.padEnd(
          25
        )} ${nullable}`
      );
    });

    // 4. Check portfolio indexes
    console.log("\n4️⃣ Portfolio Projects Indexes:");
    const portfolioIndexes = await query(`
      SELECT indexname, indexdef 
      FROM pg_indexes 
      WHERE tablename = 'portfolioprojects'
    `);
    console.log(`   Total: ${portfolioIndexes.rows.length} indexes`);
    portfolioIndexes.rows.forEach((idx) => {
      console.log(`   - ${idx.indexname}`);
    });

    // 5. Check blogposts indexes
    console.log("\n5️⃣ Blog Posts Indexes:");
    const blogIndexes = await query(`
      SELECT indexname, indexdef 
      FROM pg_indexes 
      WHERE tablename = 'blogposts'
    `);
    blogIndexes.rows.forEach((idx) => {
      console.log(`   - ${idx.indexname}`);
    });

    // 6. Check pages indexes
    console.log("\n6️⃣ Pages Indexes:");
    const pagesIndexes = await query(`
      SELECT indexname, indexdef 
      FROM pg_indexes 
      WHERE tablename = 'pages'
    `);
    pagesIndexes.rows.forEach((idx) => {
      console.log(`   - ${idx.indexname}`);
    });

    // 7. Check product_images foreign key
    console.log("\n7️⃣ Product Images Foreign Keys:");
    const piFks = await query(`
      SELECT
        tc.constraint_name,
        kcu.column_name,
        ccu.table_name AS foreign_table,
        rc.delete_rule,
        rc.update_rule
      FROM information_schema.table_constraints AS tc
      JOIN information_schema.key_column_usage AS kcu
        ON tc.constraint_name = kcu.constraint_name
      JOIN information_schema.constraint_column_usage AS ccu
        ON ccu.constraint_name = tc.constraint_name
      JOIN information_schema.referential_constraints AS rc
        ON tc.constraint_name = rc.constraint_name
      WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_name = 'product_images'
    `);
    if (piFks.rows.length === 0) {
      console.log("   ⚠️  No foreign keys found!");
    } else {
      piFks.rows.forEach((fk) => {
        console.log(
          `   ${fk.column_name} → ${fk.foreign_table} (DELETE: ${fk.delete_rule})`
        );
      });
    }

    // 8. Check unique constraints
    console.log("\n8️⃣ Unique Constraints:");
    const uniqueConstraints = await query(`
      SELECT
        tc.table_name,
        tc.constraint_name,
        kcu.column_name
      FROM information_schema.table_constraints tc
      JOIN information_schema.key_column_usage kcu
        ON tc.constraint_name = kcu.constraint_name
      WHERE tc.constraint_type = 'UNIQUE'
        AND tc.table_schema = 'public'
        AND tc.table_name IN ('products', 'blogposts', 'pages', 'portfolioprojects')
      ORDER BY tc.table_name, tc.constraint_name
    `);
    if (uniqueConstraints.rows.length === 0) {
      console.log("   ⚠️  No unique constraints on slug columns!");
    } else {
      uniqueConstraints.rows.forEach((uc) => {
        console.log(
          `   ${uc.table_name}.${uc.column_name} (${uc.constraint_name})`
        );
      });
    }

    console.log("\n✅ Analysis complete!");
  } catch (error) {
    console.error("❌ Error:", error.message);
    console.error(error);
  } finally {
    await pool.end();
  }
}

analyzeSchema();