SkyArtShop/backend/validate-db-alignment.js

const { query } = require('./config/database');
const fs = require('fs');
const path = require('path');

async function validateAlignment() {
  console.log('🔍 Validating Database-Backend Alignment...\n');
  
  const issues = [];
  const warnings = [];
  const successes = [];
  
  try {
    // 1. Check required tables exist
    console.log('1️⃣ Checking required tables...');
    const requiredTables = [
      'products', 'product_images', 'blogposts', 'pages',
      'portfolioprojects', 'adminusers', 'customers', 'orders',
      'order_items', 'product_reviews'
    ];
    
    for (const table of requiredTables) {
      const exists = await query(`
        SELECT EXISTS (
          SELECT 1 FROM information_schema.tables 
          WHERE table_name = $1
        ) as exists
      `, [table]);
      
      if (exists.rows[0].exists) {
        successes.push(`✓ Table ${table} exists`);
      } else {
        issues.push(`✗ Missing table: ${table}`);
      }
    }
    
    // 2. Check foreign key relationships
    console.log('\n2️⃣ Checking foreign key relationships...');
    const relationships = [
      { table: 'product_images', column: 'product_id', ref: 'products' },
      { table: 'order_items', column: 'order_id', ref: 'orders' },
      { table: 'order_items', column: 'product_id', ref: 'products' },
      { table: 'product_reviews', column: 'product_id', ref: 'products' },
      { table: 'product_reviews', column: 'customer_id', ref: 'customers' }
    ];
    
    for (const rel of relationships) {
      const exists = await query(`
        SELECT EXISTS (
          SELECT 1 FROM information_schema.table_constraints tc
          JOIN information_schema.key_column_usage kcu
            ON tc.constraint_name = kcu.constraint_name
          WHERE tc.constraint_type = 'FOREIGN KEY'
            AND tc.table_name = $1
            AND kcu.column_name = $2
        ) as exists
      `, [rel.table, rel.column]);
      
      if (exists.rows[0].exists) {
        successes.push(`✓ FK: ${rel.table}.${rel.column} -> ${rel.ref}`);
      } else {
        warnings.push(`⚠ Missing FK: ${rel.table}.${rel.column} -> ${rel.ref}`);
      }
    }
    
    // 3. Check critical indexes
    console.log('\n3️⃣ Checking critical indexes...');
    const criticalIndexes = [
      { table: 'products', column: 'slug' },
      { table: 'products', column: 'isactive' },
      { table: 'product_images', column: 'product_id' },
      { table: 'blogposts', column: 'slug' },
      { table: 'pages', column: 'slug' },
      { table: 'orders', column: 'ordernumber' }
    ];
    
    for (const idx of criticalIndexes) {
      const exists = await query(`
        SELECT EXISTS (
          SELECT 1 FROM pg_indexes
          WHERE tablename = $1 
            AND indexdef LIKE '%' || $2 || '%'
        ) as exists
      `, [idx.table, idx.column]);
      
      if (exists.rows[0].exists) {
        successes.push(`✓ Index on ${idx.table}.${idx.column}`);
      } else {
        warnings.push(`⚠ Missing index on ${idx.table}.${idx.column}`);
      }
    }
    
    // 4. Check constraints
    console.log('\n4️⃣ Checking data constraints...');
    const constraints = [
      { table: 'products', name: 'chk_products_price_positive' },
      { table: 'products', name: 'chk_products_stock_nonnegative' },
      { table: 'product_images', name: 'chk_product_images_order_nonnegative' }
    ];
    
    for (const con of constraints) {
      const exists = await query(`
        SELECT EXISTS (
          SELECT 1 FROM information_schema.table_constraints
          WHERE constraint_name = $1 AND table_name = $2
        ) as exists
      `, [con.name, con.table]);
      
      if (exists.rows[0].exists) {
        successes.push(`✓ Constraint ${con.name}`);
      } else {
        issues.push(`✗ Missing constraint: ${con.name}`);
      }
    }
    
    // 5. Check CASCADE delete setup
    console.log('\n5️⃣ Checking CASCADE delete rules...');
    const cascades = await query(`
      SELECT
        tc.table_name,
        kcu.column_name,
        ccu.table_name AS foreign_table_name,
        rc.delete_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 rc.constraint_name = tc.constraint_name
      WHERE tc.constraint_type = 'FOREIGN KEY'
        AND tc.table_name IN ('product_images', 'order_items', 'product_reviews')
    `);
    
    cascades.rows.forEach(row => {
      if (row.delete_rule === 'CASCADE') {
        successes.push(`✓ CASCADE delete: ${row.table_name}.${row.column_name}`);
      } else {
        warnings.push(`⚠ Non-CASCADE delete: ${row.table_name}.${row.column_name} (${row.delete_rule})`);
      }
    });
    
    // 6. Test query performance
    console.log('\n6️⃣ Testing query performance...');
    const start = Date.now();
    await query(`
      SELECT p.*, 
        COALESCE(
          json_agg(
            json_build_object('id', pi.id, 'image_url', pi.image_url)
            ORDER BY pi.display_order
          ) FILTER (WHERE pi.id IS NOT NULL),
          '[]'::json
        ) as images
      FROM products p
      LEFT JOIN product_images pi ON pi.product_id = p.id
      WHERE p.isactive = true
      GROUP BY p.id
      LIMIT 10
    `);
    const duration = Date.now() - start;
    
    if (duration < 100) {
      successes.push(`✓ Query performance: ${duration}ms (excellent)`);
    } else if (duration < 300) {
      successes.push(`✓ Query performance: ${duration}ms (good)`);
    } else {
      warnings.push(`⚠ Query performance: ${duration}ms (needs optimization)`);
    }
    
    // 7. Check data integrity
    console.log('\n7️⃣ Checking data integrity...');
    
    // Orphaned product images
    const orphanedImages = await query(`
      SELECT COUNT(*) as count
      FROM product_images pi
      LEFT JOIN products p ON p.id = pi.product_id
      WHERE p.id IS NULL
    `);
    
    if (orphanedImages.rows[0].count === '0') {
      successes.push('✓ No orphaned product images');
    } else {
      warnings.push(`⚠ ${orphanedImages.rows[0].count} orphaned product images`);
    }
    
    // Products without images
    const noImages = await query(`
      SELECT COUNT(*) as count
      FROM products p
      LEFT JOIN product_images pi ON pi.product_id = p.id
      WHERE p.isactive = true AND pi.id IS NULL
    `);
    
    if (noImages.rows[0].count === '0') {
      successes.push('✓ All active products have images');
    } else {
      warnings.push(`⚠ ${noImages.rows[0].count} active products without images`);
    }
    
    // Summary
    console.log('\n' + '='.repeat(60));
    console.log('📊 VALIDATION SUMMARY');
    console.log('='.repeat(60));
    
    console.log(`\n✅ Successes: ${successes.length}`);
    if (successes.length > 0 && successes.length <= 10) {
      successes.forEach(s => console.log(`  ${s}`));
    } else if (successes.length > 10) {
      console.log(`  (${successes.length} items validated successfully)`);
    }
    
    if (warnings.length > 0) {
      console.log(`\n⚠️  Warnings: ${warnings.length}`);
      warnings.forEach(w => console.log(`  ${w}`));
    }
    
    if (issues.length > 0) {
      console.log(`\n❌ Issues: ${issues.length}`);
      issues.forEach(i => console.log(`  ${i}`));
    }
    
    console.log('\n' + '='.repeat(60));
    
    if (issues.length === 0) {
      console.log('\n🎉 Database is properly aligned with backend!\n');
      process.exit(0);
    } else {
      console.log('\n⚠️  Database has issues that need attention.\n');
      process.exit(1);
    }
    
  } catch (error) {
    console.error('\n❌ Validation error:', error.message);
    process.exit(1);
  }
}

validateAlignment();