vexplor/backend-node/scripts/install-dataflow-indexes.js

/**
 * 🔥 버튼 제어관리 성능 최적화 인덱스 설치 스크립트
 *
 * 사용법:
 * node scripts/install-dataflow-indexes.js
 */

const { PrismaClient } = require("@prisma/client");
const fs = require("fs");
const path = require("path");

const prisma = new PrismaClient();

async function installDataflowIndexes() {
  try {
    console.log("🔥 Starting Button Dataflow Performance Optimization...\n");

    // SQL 파일 읽기
    const sqlFilePath = path.join(
      __dirname,
      "../database/migrations/add_button_dataflow_indexes.sql"
    );
    const sqlContent = fs.readFileSync(sqlFilePath, "utf8");

    console.log("📖 Reading SQL migration file...");
    console.log(`📁 File: ${sqlFilePath}\n`);

    // 데이터베이스 연결 확인
    console.log("🔍 Checking database connection...");
    await prisma.$queryRaw`SELECT 1`;
    console.log("✅ Database connection OK\n");

    // 기존 인덱스 상태 확인
    console.log("🔍 Checking existing indexes...");
    const existingIndexes = await prisma.$queryRaw`
      SELECT indexname, tablename 
      FROM pg_indexes 
      WHERE tablename = 'dataflow_diagrams' 
      AND indexname LIKE 'idx_dataflow%'
      ORDER BY indexname;
    `;

    if (existingIndexes.length > 0) {
      console.log("📋 Existing dataflow indexes:");
      existingIndexes.forEach((idx) => {
        console.log(`   - ${idx.indexname}`);
      });
    } else {
      console.log("📋 No existing dataflow indexes found");
    }
    console.log("");

    // 테이블 상태 확인
    console.log("🔍 Checking dataflow_diagrams table stats...");
    const tableStats = await prisma.$queryRaw`
      SELECT 
        COUNT(*) as total_rows,
        COUNT(*) FILTER (WHERE control IS NOT NULL) as with_control,
        COUNT(*) FILTER (WHERE plan IS NOT NULL) as with_plan,
        COUNT(*) FILTER (WHERE category IS NOT NULL) as with_category,
        COUNT(DISTINCT company_code) as companies
      FROM dataflow_diagrams;
    `;

    if (tableStats.length > 0) {
      const stats = tableStats[0];
      console.log(`📊 Table Statistics:`);
      console.log(`   - Total rows: ${stats.total_rows}`);
      console.log(`   - With control: ${stats.with_control}`);
      console.log(`   - With plan: ${stats.with_plan}`);
      console.log(`   - With category: ${stats.with_category}`);
      console.log(`   - Companies: ${stats.companies}`);
    }
    console.log("");

    // SQL 실행
    console.log("🚀 Installing performance indexes...");
    console.log("⏳ This may take a few minutes for large datasets...\n");

    const startTime = Date.now();

    // SQL을 문장별로 나누어 실행 (PostgreSQL 함수 때문에)
    const sqlStatements = sqlContent
      .split(/;\s*(?=\n|$)/)
      .filter(
        (stmt) =>
          stmt.trim().length > 0 &&
          !stmt.trim().startsWith("--") &&
          !stmt.trim().startsWith("/*")
      );

    for (let i = 0; i < sqlStatements.length; i++) {
      const statement = sqlStatements[i].trim();
      if (statement.length === 0) continue;

      try {
        // DO 블록이나 복합 문장 처리
        if (
          statement.includes("DO $$") ||
          statement.includes("CREATE OR REPLACE VIEW")
        ) {
          console.log(
            `⚡ Executing statement ${i + 1}/${sqlStatements.length}...`
          );
          await prisma.$executeRawUnsafe(statement + ";");
        } else if (statement.startsWith("CREATE INDEX")) {
          const indexName =
            statement.match(/CREATE INDEX[^"]*"?([^"\s]+)"?/)?.[1] || "unknown";
          console.log(`🔧 Creating index: ${indexName}...`);
          await prisma.$executeRawUnsafe(statement + ";");
        } else if (statement.startsWith("ANALYZE")) {
          console.log(`📊 Analyzing table statistics...`);
          await prisma.$executeRawUnsafe(statement + ";");
        } else {
          await prisma.$executeRawUnsafe(statement + ";");
        }
      } catch (error) {
        // 이미 존재하는 인덱스 에러는 무시
        if (error.message.includes("already exists")) {
          console.log(`⚠️  Index already exists, skipping...`);
        } else {
          console.error(`❌ Error executing statement: ${error.message}`);
          console.error(`📝 Statement: ${statement.substring(0, 100)}...`);
        }
      }
    }

    const endTime = Date.now();
    const executionTime = (endTime - startTime) / 1000;

    console.log(
      `\n✅ Index installation completed in ${executionTime.toFixed(2)} seconds!`
    );

    // 설치된 인덱스 확인
    console.log("\n🔍 Verifying installed indexes...");
    const newIndexes = await prisma.$queryRaw`
      SELECT 
        indexname,
        pg_size_pretty(pg_relation_size(indexrelid)) as size
      FROM pg_stat_user_indexes 
      WHERE tablename = 'dataflow_diagrams'
      AND indexname LIKE 'idx_dataflow%'
      ORDER BY indexname;
    `;

    if (newIndexes.length > 0) {
      console.log("📋 Installed indexes:");
      newIndexes.forEach((idx) => {
        console.log(`   ✅ ${idx.indexname} (${idx.size})`);
      });
    }

    // 성능 통계 조회
    console.log("\n📊 Performance statistics:");
    try {
      const perfStats =
        await prisma.$queryRaw`SELECT * FROM dataflow_performance_stats;`;
      if (perfStats.length > 0) {
        const stats = perfStats[0];
        console.log(`   - Table size: ${stats.table_size}`);
        console.log(`   - Total diagrams: ${stats.total_rows}`);
        console.log(`   - With control: ${stats.with_control}`);
        console.log(`   - Companies: ${stats.companies}`);
      }
    } catch (error) {
      console.log("   ⚠️  Performance view not available yet");
    }

    console.log("\n🎯 Performance Optimization Complete!");
    console.log("Expected improvements:");
    console.log("  - Button dataflow lookup: 500ms+ → 10-50ms ⚡");
    console.log("  - Category filtering: 200ms+ → 5-20ms ⚡");
    console.log("  - Company queries: 100ms+ → 5-15ms ⚡");

    console.log("\n💡 Monitor performance with:");
    console.log("  SELECT * FROM dataflow_performance_stats;");
    console.log("  SELECT * FROM dataflow_index_efficiency;");
  } catch (error) {
    console.error("\n❌ Error installing dataflow indexes:", error);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

// 실행
if (require.main === module) {
  installDataflowIndexes()
    .then(() => {
      console.log("\n🎉 Installation completed successfully!");
      process.exit(0);
    })
    .catch((error) => {
      console.error("\n💥 Installation failed:", error);
      process.exit(1);
    });
}

module.exports = { installDataflowIndexes };