src/scripts/fetch-templates.ts

#!/usr/bin/env node
import { createDatabaseAdapter } from '../database/database-adapter';
import { TemplateService } from '../templates/template-service';
import * as fs from 'fs';
import * as path from 'path';
import * as zlib from 'zlib';
import * as dotenv from 'dotenv';
import type { MetadataRequest } from '../templates/metadata-generator';

// Load environment variables
dotenv.config();

/**
 * Extract node configurations from a template workflow
 */
function extractNodeConfigs(
  templateId: number,
  templateName: string,
  templateViews: number,
  workflowCompressed: string,
  metadata: any
): Array<{
  node_type: string;
  template_id: number;
  template_name: string;
  template_views: number;
  node_name: string;
  parameters_json: string;
  credentials_json: string | null;
  has_credentials: number;
  has_expressions: number;
  complexity: string;
  use_cases: string;
}> {
  try {
    // Decompress workflow
    const decompressed = zlib.gunzipSync(Buffer.from(workflowCompressed, 'base64'));
    const workflow = JSON.parse(decompressed.toString('utf-8'));

    const configs: any[] = [];

    for (const node of workflow.nodes || []) {
      // Skip UI-only nodes (sticky notes, etc.)
      if (node.type.includes('stickyNote') || !node.parameters) {
        continue;
      }

      configs.push({
        node_type: node.type,
        template_id: templateId,
        template_name: templateName,
        template_views: templateViews,
        node_name: node.name,
        parameters_json: JSON.stringify(node.parameters),
        credentials_json: node.credentials ? JSON.stringify(node.credentials) : null,
        has_credentials: node.credentials ? 1 : 0,
        has_expressions: detectExpressions(node.parameters) ? 1 : 0,
        complexity: metadata?.complexity || 'medium',
        use_cases: JSON.stringify(metadata?.use_cases || [])
      });
    }

    return configs;
  } catch (error) {
    console.error(`Error extracting configs from template ${templateId}:`, error);
    return [];
  }
}

/**
 * Detect n8n expressions in parameters
 */
function detectExpressions(params: any): boolean {
  if (!params) return false;
  const json = JSON.stringify(params);
  return json.includes('={{') || json.includes('$json') || json.includes('$node');
}

/**
 * Insert extracted configs into database and rank them
 */
function insertAndRankConfigs(db: any, configs: any[]) {
  if (configs.length === 0) {
    console.log('No configs to insert');
    return;
  }

  // Clear old configs for these templates
  const templateIds = [...new Set(configs.map(c => c.template_id))];
  const placeholders = templateIds.map(() => '?').join(',');
  db.prepare(`DELETE FROM template_node_configs WHERE template_id IN (${placeholders})`).run(...templateIds);

  // Insert new configs
  const insertStmt = db.prepare(`
    INSERT INTO template_node_configs (
      node_type, template_id, template_name, template_views,
      node_name, parameters_json, credentials_json,
      has_credentials, has_expressions, complexity, use_cases
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  `);

  for (const config of configs) {
    insertStmt.run(
      config.node_type,
      config.template_id,
      config.template_name,
      config.template_views,
      config.node_name,
      config.parameters_json,
      config.credentials_json,
      config.has_credentials,
      config.has_expressions,
      config.complexity,
      config.use_cases
    );
  }

  // Rank configs per node_type by template popularity
  db.exec(`
    UPDATE template_node_configs
    SET rank = (
      SELECT COUNT(*) + 1
      FROM template_node_configs AS t2
      WHERE t2.node_type = template_node_configs.node_type
        AND t2.template_views > template_node_configs.template_views
    )
  `);

  // Keep only top 10 per node_type
  db.exec(`
    DELETE FROM template_node_configs
    WHERE id NOT IN (
      SELECT id FROM template_node_configs
      WHERE rank <= 10
      ORDER BY node_type, rank
    )
  `);

  console.log(`✅ Extracted and ranked ${configs.length} node configurations`);
}

/**
 * Extract node configurations from existing templates
 */
async function extractTemplateConfigs(db: any, service: TemplateService) {
  console.log('📦 Extracting node configurations from templates...');
  const repository = (service as any).repository;
  const allTemplates = repository.getAllTemplates();

  const allConfigs: any[] = [];
  let configsExtracted = 0;

  for (const template of allTemplates) {
    if (template.workflow_json_compressed) {
      const metadata = template.metadata_json ? JSON.parse(template.metadata_json) : null;
      const configs = extractNodeConfigs(
        template.id,
        template.name,
        template.views,
        template.workflow_json_compressed,
        metadata
      );
      allConfigs.push(...configs);
      configsExtracted += configs.length;
    }
  }

  if (allConfigs.length > 0) {
    insertAndRankConfigs(db, allConfigs);

    // Show stats
    const configStats = db.prepare(`
      SELECT
        COUNT(DISTINCT node_type) as node_types,
        COUNT(*) as total_configs,
        AVG(rank) as avg_rank
      FROM template_node_configs
    `).get() as any;

    console.log(`📊 Node config stats:`);
    console.log(`   - Unique node types: ${configStats.node_types}`);
    console.log(`   - Total configs stored: ${configStats.total_configs}`);
    console.log(`   - Average rank: ${configStats.avg_rank?.toFixed(1) || 'N/A'}`);
  } else {
    console.log('⚠️  No node configurations extracted');
  }
}

async function fetchTemplates(
  mode: 'rebuild' | 'update' = 'rebuild',
  generateMetadata: boolean = false,
  metadataOnly: boolean = false,
  extractOnly: boolean = false
) {
  // If extract-only mode, skip template fetching and only extract configs
  if (extractOnly) {
    console.log('📦 Extract-only mode: Extracting node configurations from existing templates...\n');

    const db = await createDatabaseAdapter('./data/nodes.db');

    // Ensure template_node_configs table exists
    try {
      const tableExists = db.prepare(`
        SELECT name FROM sqlite_master
        WHERE type='table' AND name='template_node_configs'
      `).get();

      if (!tableExists) {
        console.log('📋 Creating template_node_configs table...');
        const migrationPath = path.join(__dirname, '../../src/database/migrations/add-template-node-configs.sql');
        const migration = fs.readFileSync(migrationPath, 'utf8');
        db.exec(migration);
        console.log('✅ Table created successfully\n');
      }
    } catch (error) {
      console.error('❌ Error checking/creating template_node_configs table:', error);
      if ('close' in db && typeof db.close === 'function') {
        db.close();
      }
      process.exit(1);
    }

    const service = new TemplateService(db);

    await extractTemplateConfigs(db, service);

    if ('close' in db && typeof db.close === 'function') {
      db.close();
    }
    return;
  }

  // If metadata-only mode, skip template fetching entirely
  if (metadataOnly) {
    console.log('🤖 Metadata-only mode: Generating metadata for existing templates...\n');

    if (!process.env.OPENAI_API_KEY) {
      console.error('❌ OPENAI_API_KEY not set in environment');
      process.exit(1);
    }

    const db = await createDatabaseAdapter('./data/nodes.db');
    const service = new TemplateService(db);

    await generateTemplateMetadata(db, service);

    if ('close' in db && typeof db.close === 'function') {
      db.close();
    }
    return;
  }
  
  const modeEmoji = mode === 'rebuild' ? '🔄' : '⬆️';
  const modeText = mode === 'rebuild' ? 'Rebuilding' : 'Updating';
  console.log(`${modeEmoji} ${modeText} n8n workflow templates...\n`);
  
  if (generateMetadata) {
    console.log('🤖 Metadata generation enabled (using OpenAI)\n');
  }
  
  // Ensure data directory exists
  const dataDir = './data';
  if (!fs.existsSync(dataDir)) {
    fs.mkdirSync(dataDir, { recursive: true });
  }
  
  // Initialize database
  const db = await createDatabaseAdapter('./data/nodes.db');
  
  // Handle database schema based on mode
  if (mode === 'rebuild') {
    try {
      // Drop existing tables in rebuild mode
      db.exec('DROP TABLE IF EXISTS templates');
      db.exec('DROP TABLE IF EXISTS templates_fts');
      console.log('🗑️  Dropped existing templates tables (rebuild mode)\n');
      
      // Apply fresh schema
      const schema = fs.readFileSync(path.join(__dirname, '../../src/database/schema.sql'), 'utf8');
      db.exec(schema);
      console.log('📋 Applied database schema\n');
    } catch (error) {
      console.error('❌ Error setting up database schema:', error);
      throw error;
    }
  } else {
    console.log('📊 Update mode: Keeping existing templates and schema\n');
    
    // In update mode, only ensure new columns exist (for migration)
    try {
      // Check if metadata columns exist, add them if not (migration support)
      const columns = db.prepare("PRAGMA table_info(templates)").all() as any[];
      const hasMetadataColumn = columns.some((col: any) => col.name === 'metadata_json');
      
      if (!hasMetadataColumn) {
        console.log('📋 Adding metadata columns to existing schema...');
        db.exec(`
          ALTER TABLE templates ADD COLUMN metadata_json TEXT;
          ALTER TABLE templates ADD COLUMN metadata_generated_at DATETIME;
        `);
        console.log('✅ Metadata columns added\n');
      }
    } catch (error) {
      // Columns might already exist, that's fine
      console.log('📋 Schema is up to date\n');
    }
  }
  
  // FTS5 initialization is handled by TemplateRepository
  // No need to duplicate the logic here
  
  // Create service
  const service = new TemplateService(db);
  
  // Progress tracking
  let lastMessage = '';
  const startTime = Date.now();
  
  try {
    await service.fetchAndUpdateTemplates((message, current, total) => {
      // Clear previous line
      if (lastMessage) {
        process.stdout.write('\r' + ' '.repeat(lastMessage.length) + '\r');
      }
      
      const progress = total > 0 ? Math.round((current / total) * 100) : 0;
      lastMessage = `📊 ${message}: ${current}/${total} (${progress}%)`;
      process.stdout.write(lastMessage);
    }, mode);  // Pass the mode parameter!
    
    console.log('\n'); // New line after progress
    
    // Get stats
    const stats = await service.getTemplateStats();
    const elapsed = Math.round((Date.now() - startTime) / 1000);
    
    console.log('✅ Template fetch complete!\n');
    console.log('📈 Statistics:');
    console.log(`   - Total templates: ${stats.totalTemplates}`);
    console.log(`   - Average views: ${stats.averageViews}`);
    console.log(`   - Time elapsed: ${elapsed} seconds`);
    console.log('\n🔝 Top used nodes:');
    
    stats.topUsedNodes.forEach((node: any, index: number) => {
      console.log(`   ${index + 1}. ${node.node} (${node.count} templates)`);
    });

    // Extract node configurations from templates
    console.log('');
    await extractTemplateConfigs(db, service);

    // Generate metadata if requested
    if (generateMetadata && process.env.OPENAI_API_KEY) {
      console.log('\n🤖 Generating metadata for templates...');
      await generateTemplateMetadata(db, service);
    } else if (generateMetadata && !process.env.OPENAI_API_KEY) {
      console.log('\n⚠️  Metadata generation requested but OPENAI_API_KEY not set');
    }

  } catch (error) {
    console.error('\n❌ Error fetching templates:', error);
    process.exit(1);
  }
  
  // Close database
  if ('close' in db && typeof db.close === 'function') {
    db.close();
  }
}

// Generate metadata for templates using OpenAI
async function generateTemplateMetadata(db: any, service: TemplateService) {
  try {
    const { BatchProcessor } = await import('../templates/batch-processor');
    const repository = (service as any).repository;
    
    // Get templates without metadata (0 = no limit)
    const limit = parseInt(process.env.METADATA_LIMIT || '0');
    const templatesWithoutMetadata = limit > 0 
      ? repository.getTemplatesWithoutMetadata(limit)
      : repository.getTemplatesWithoutMetadata(999999); // Get all
    
    if (templatesWithoutMetadata.length === 0) {
      console.log('✅ All templates already have metadata');
      return;
    }
    
    console.log(`Found ${templatesWithoutMetadata.length} templates without metadata`);
    
    // Create batch processor
    const batchSize = parseInt(process.env.OPENAI_BATCH_SIZE || '50');
    console.log(`Processing in batches of ${batchSize} templates each`);
    
    // Warn if batch size is very large
    if (batchSize > 100) {
      console.log(`⚠️  Large batch size (${batchSize}) may take longer to process`);
      console.log(`   Consider using OPENAI_BATCH_SIZE=50 for faster results`);
    }
    
    const processor = new BatchProcessor({
      apiKey: process.env.OPENAI_API_KEY!,
      model: process.env.OPENAI_MODEL || 'gpt-4o-mini',
      batchSize: batchSize,
      outputDir: './temp/batch'
    });
    
    // Prepare metadata requests
    const requests: MetadataRequest[] = templatesWithoutMetadata.map((t: any) => {
      let workflow = undefined;
      try {
        if (t.workflow_json_compressed) {
          const decompressed = zlib.gunzipSync(Buffer.from(t.workflow_json_compressed, 'base64'));
          workflow = JSON.parse(decompressed.toString());
        } else if (t.workflow_json) {
          workflow = JSON.parse(t.workflow_json);
        }
      } catch (error) {
        console.warn(`Failed to parse workflow for template ${t.id}:`, error);
      }

      // Parse nodes_used safely
      let nodes: string[] = [];
      try {
        if (t.nodes_used) {
          nodes = JSON.parse(t.nodes_used);
          // Ensure it's an array
          if (!Array.isArray(nodes)) {
            console.warn(`Template ${t.id} has invalid nodes_used (not an array), using empty array`);
            nodes = [];
          }
        }
      } catch (error) {
        console.warn(`Failed to parse nodes_used for template ${t.id}:`, error);
        nodes = [];
      }

      return {
        templateId: t.id,
        name: t.name,
        description: t.description,
        nodes: nodes,
        workflow
      };
    });
    
    // Process in batches
    const results = await processor.processTemplates(requests, (message, current, total) => {
      process.stdout.write(`\r📊 ${message}: ${current}/${total}`);
    });
    
    console.log('\n');
    
    // Update database with metadata
    const metadataMap = new Map();
    for (const [templateId, result] of results) {
      if (!result.error) {
        metadataMap.set(templateId, result.metadata);
      }
    }
    
    if (metadataMap.size > 0) {
      repository.batchUpdateMetadata(metadataMap);
      console.log(`✅ Updated metadata for ${metadataMap.size} templates`);
    }
    
    // Show stats
    const stats = repository.getMetadataStats();
    console.log('\n📈 Metadata Statistics:');
    console.log(`   - Total templates: ${stats.total}`);
    console.log(`   - With metadata: ${stats.withMetadata}`);
    console.log(`   - Without metadata: ${stats.withoutMetadata}`);
    console.log(`   - Outdated (>30 days): ${stats.outdated}`);
  } catch (error) {
    console.error('\n❌ Error generating metadata:', error);
  }
}

// Parse command line arguments
function parseArgs(): { mode: 'rebuild' | 'update', generateMetadata: boolean, metadataOnly: boolean, extractOnly: boolean } {
  const args = process.argv.slice(2);

  let mode: 'rebuild' | 'update' = 'rebuild';
  let generateMetadata = false;
  let metadataOnly = false;
  let extractOnly = false;

  // Check for --mode flag
  const modeIndex = args.findIndex(arg => arg.startsWith('--mode'));
  if (modeIndex !== -1) {
    const modeArg = args[modeIndex];
    const modeValue = modeArg.includes('=') ? modeArg.split('=')[1] : args[modeIndex + 1];

    if (modeValue === 'update') {
      mode = 'update';
    }
  }

  // Check for --update flag as shorthand
  if (args.includes('--update')) {
    mode = 'update';
  }

  // Check for --generate-metadata flag
  if (args.includes('--generate-metadata') || args.includes('--metadata')) {
    generateMetadata = true;
  }

  // Check for --metadata-only flag
  if (args.includes('--metadata-only')) {
    metadataOnly = true;
  }

  // Check for --extract-only flag
  if (args.includes('--extract-only') || args.includes('--extract')) {
    extractOnly = true;
  }

  // Show help if requested
  if (args.includes('--help') || args.includes('-h')) {
    console.log('Usage: npm run fetch:templates [options]\n');
    console.log('Options:');
    console.log('  --mode=rebuild|update  Rebuild from scratch or update existing (default: rebuild)');
    console.log('  --update               Shorthand for --mode=update');
    console.log('  --generate-metadata    Generate AI metadata after fetching templates');
    console.log('  --metadata             Shorthand for --generate-metadata');
    console.log('  --metadata-only        Only generate metadata, skip template fetching');
    console.log('  --extract-only         Only extract node configs, skip template fetching');
    console.log('  --extract              Shorthand for --extract-only');
    console.log('  --help, -h             Show this help message');
    process.exit(0);
  }

  return { mode, generateMetadata, metadataOnly, extractOnly };
}

// Run if called directly
if (require.main === module) {
  const { mode, generateMetadata, metadataOnly, extractOnly } = parseArgs();
  fetchTemplates(mode, generateMetadata, metadataOnly, extractOnly).catch(console.error);
}

export { fetchTemplates };