// const express = require('express');
// const http = require('http');
// const socketIo = require('socket.io');
// const { Pool } = require('pg');
// const Redis = require('redis');
// const cors = require('cors');
// const axios = require('axios');

// const app = express();
// const server = http.createServer(app);
// const io = socketIo(server, {
//   cors: { origin: "*", methods: ["GET", "POST", "PUT", "DELETE"] }
// });

// // Database connections
// const pgPool = new Pool({
//   host: process.env.POSTGRES_HOST || 'pipeline_postgres',
//   port: process.env.POSTGRES_PORT || 5432,
//   database: process.env.POSTGRES_DB || 'dev_pipeline',
//   user: process.env.POSTGRES_USER || 'pipeline_admin',
//   password: process.env.POSTGRES_PASSWORD || 'secure_pipeline_2024',
//   max: 20,
//   idleTimeoutMillis: 30000,
//   connectionTimeoutMillis: 2000,
// });

// const redisClient = Redis.createClient({
//   socket: {
//     host: process.env.REDIS_HOST || 'pipeline_redis',
//     port: process.env.REDIS_PORT || 6379
//   },
//   password: process.env.REDIS_PASSWORD || 'redis_secure_2024'
// });

// redisClient.on('error', (err) => console.log('Redis Client Error', err));

// // Services configuration
// const SERVICES = {
//   'api-gateway': { 
//     port: 8000, 
//     name: 'API Gateway', 
//     container: 'pipeline_api_gateway',
//     url: 'http://pipeline_api_gateway:8000'
//   },
//   'requirement-processor': { 
//     port: 8001, 
//     name: 'Requirement Processor', 
//     container: 'pipeline_requirement_processor',
//     url: 'http://pipeline_requirement_processor:8001'
//   },
//   'tech-stack-selector': { 
//     port: 8002, 
//     name: 'Tech Stack Selector', 
//     container: 'pipeline_tech_stack_selector',
//     url: 'http://pipeline_tech_stack_selector:8002'
//   },
//   'architecture-designer': { 
//     port: 8003, 
//     name: 'Architecture Designer', 
//     container: 'pipeline_architecture_designer',
//     url: 'http://pipeline_architecture_designer:8003'
//   },
//   'code-generator': { 
//     port: 8004, 
//     name: 'Code Generator', 
//     container: 'pipeline_code_generator',
//     url: 'http://pipeline_code_generator:8004'
//   },
//   'test-generator': { 
//     port: 8005, 
//     name: 'Test Generator', 
//     container: 'pipeline_test_generator',
//     url: 'http://pipeline_test_generator:8005'
//   },
//   'deployment-manager': { 
//     port: 8006, 
//     name: 'Deployment Manager', 
//     container: 'pipeline_deployment_manager',
//     url: 'http://pipeline_deployment_manager:8006'
//   },
//   'self-improving-generator': { 
//     port: 8007, 
//     name: 'Self-Improving Generator', 
//     container: 'pipeline_self_improving_generator',
//     url: 'http://pipeline_self_improving_generator:8007'
//   }
// };

// // Middleware
// app.use(cors());
// app.use(express.json({ limit: '50mb' }));
// app.use(express.urlencoded({ extended: true, limit: '50mb' }));

// // Database service - FIXED to work with your actual database structure
// class DatabaseService {
//   static async getProjects(filters = {}) {
//     try {
//       console.log('🔍 Querying projects from database...');
      
//       // Simple query for project_contexts table - NO JOINS
//       let query = `
//         SELECT 
//           id,
//           project_name,
//           technology_stack,
//           all_features,
//           completed_features,
//           pending_features,
//           project_path,
//           created_at,
//           updated_at
//         FROM project_contexts
//       `;

//       const conditions = [];
//       const values = [];
//       let paramCount = 0;

//       if (filters.project_name) {
//         conditions.push(`project_name ILIKE $${++paramCount}`);
//         values.push(`%${filters.project_name}%`);
//       }

//       if (conditions.length > 0) {
//         query += ` WHERE ${conditions.join(' AND ')}`;
//       }

//       query += ` ORDER BY created_at DESC LIMIT 20`;

//       console.log('🔍 Executing query:', query);
      
//       const result = await pgPool.query(query, values);
//       console.log('✅ Query result:', result.rows.length, 'projects found');
      
//       // Get file counts for each project separately - SAFE QUERIES
//       for (let project of result.rows) {
//         try {
//           const fileCountResult = await pgPool.query(
//             'SELECT COUNT(*) FROM code_files WHERE project_id = $1',
//             [project.id]
//           );
//           project.file_count = parseInt(fileCountResult.rows[0].count);
//         } catch (fileError) {
//           console.log('⚠️ Could not get file count for project', project.id);
//           project.file_count = 0;
//         }
//       }

//       return result.rows;
//     } catch (error) {
//       console.error('❌ Database query error:', error.message);
//       throw error;
//     }
//   }

//   static async getSystemStats() {
//     try {
//       console.log('🔍 Getting system stats from database...');
      
//       // Get counts from each table - SIMPLE QUERIES
//       const projectCountResult = await pgPool.query('SELECT COUNT(*) FROM project_contexts');
//       const fileCountResult = await pgPool.query('SELECT COUNT(*) FROM code_files');
//       const improvementCountResult = await pgPool.query('SELECT COUNT(*) FROM improvement_history');

//       // Get recent projects (last 24 hours)
//       const recentResult = await pgPool.query(`
//         SELECT COUNT(*) FROM project_contexts 
//         WHERE created_at > NOW() - INTERVAL '24 hours'
//       `);

//       const stats = {
//         project_contexts: parseInt(projectCountResult.rows[0].count),
//         code_files: parseInt(fileCountResult.rows[0].count),
//         improvement_history: parseInt(improvementCountResult.rows[0].count),
//         recent_projects: parseInt(recentResult.rows[0].count)
//       };

//       console.log('📊 System stats:', stats);
//       return stats;
//     } catch (error) {
//       console.error('❌ System stats error:', error);
//       return {
//         project_contexts: 0,
//         code_files: 0,
//         improvement_history: 0,
//         recent_projects: 0
//       };
//     }
//   }
// }

// // WebSocket connection handling
// io.on('connection', (socket) => {
//   console.log(`Dashboard client connected: ${socket.id}`);
  
//   socket.emit('connected', { 
//     message: 'Connected to AI Pipeline Dashboard',
//     timestamp: new Date().toISOString()
//   });
  
//   socket.on('disconnect', () => {
//     console.log(`Dashboard client disconnected: ${socket.id}`);
//   });
// });

// // API Routes
// app.get('/api/health', (req, res) => {
//   res.json({ 
//     status: 'healthy', 
//     timestamp: new Date().toISOString(),
//     service: 'AI Pipeline Dashboard',
//     version: '2.0.0'
//   });
// });

// // Debug endpoint
// app.get('/api/debug/database', async (req, res) => {
//   try {
//     console.log('🔍 Database debug endpoint called');
    
//     const connectionTest = await pgPool.query('SELECT NOW()');
//     console.log('✅ Database connection successful');
    
//     const projectCount = await pgPool.query('SELECT COUNT(*) FROM project_contexts');
//     const fileCount = await pgPool.query('SELECT COUNT(*) FROM code_files');
//     const improvementCount = await pgPool.query('SELECT COUNT(*) FROM improvement_history');
    
//     // Get sample project names
//     let sampleProjects = [];
//     const sampleResult = await pgPool.query('SELECT id, project_name, created_at FROM project_contexts ORDER BY created_at DESC LIMIT 3');
//     sampleProjects = sampleResult.rows;
    
//     res.json({
//       connection: 'OK',
//       timestamp: connectionTest.rows[0].now,
//       tables: {
//         project_contexts: parseInt(projectCount.rows[0].count),
//         code_files: parseInt(fileCount.rows[0].count),
//         improvement_history: parseInt(improvementCount.rows[0].count)
//       },
//       sampleProjects: sampleProjects
//     });
    
//   } catch (error) {
//     console.error('❌ Database debug error:', error);
//     res.status(500).json({ 
//       error: error.message,
//       connection: 'FAILED' 
//     });
//   }
// });

// // System status with real data
// app.get('/api/system/status', async (req, res) => {
//   try {
//     console.log('🔍 System status endpoint called');
    
//     let healthyServices = 0;
//     const serviceChecks = [];

//     // Check services
//     for (const [key, service] of Object.entries(SERVICES)) {
//       try {
//         await axios.get(`${service.url}/health`, { timeout: 5000 });
//         healthyServices++;
//         serviceChecks.push({ service: key, status: 'healthy' });
//       } catch (error) {
//         serviceChecks.push({ service: key, status: 'unhealthy', error: error.message });
//       }
//     }

//     // Get database stats
//     const dbStats = await DatabaseService.getSystemStats();

//     res.json({
//       healthyServices,
//       totalServices: Object.keys(SERVICES).length,
//       totalProjects: dbStats.project_contexts,
//       totalFiles: dbStats.code_files,
//       activeProjects: dbStats.recent_projects,
//       improvements: dbStats.improvement_history,
//       serviceChecks,
//       timestamp: new Date().toISOString()
//     });
//   } catch (error) {
//     console.error('❌ System status error:', error);
//     res.status(500).json({ error: error.message });
//   }
// });

// // Projects endpoint
// app.get('/api/projects', async (req, res) => {
//   try {
//     console.log('🔍 Projects endpoint called');
//     const projects = await DatabaseService.getProjects(req.query);
//     console.log('✅ Projects returned:', projects.length);
//     res.json({ projects });
//   } catch (error) {
//     console.error('❌ Projects endpoint error:', error);
//     res.status(500).json({ 
//       error: error.message,
//       details: 'Check server logs for database connection issues'
//     });
//   }
// });

// // Services health endpoint
// app.get('/api/services/health', async (req, res) => {
//   console.log('🔍 Services health check called');
  
//   const healthChecks = await Promise.allSettled(
//     Object.entries(SERVICES).map(async ([key, service]) => {
//       const startTime = Date.now();
//       try {
//         await axios.get(`${service.url}/health`, { timeout: 5000 });
//         return {
//           name: service.name,
//           status: 'healthy',
//           port: service.port,
//           container: service.container,
//           responseTime: Date.now() - startTime,
//           lastCheck: new Date().toISOString()
//         };
//       } catch (error) {
//         return {
//           name: service.name,
//           status: 'unhealthy',
//           port: service.port,
//           container: service.container,
//           responseTime: Date.now() - startTime,
//           error: error.message,
//           lastCheck: new Date().toISOString()
//         };
//       }
//     })
//   );

//   const services = healthChecks.map(result => 
//     result.status === 'fulfilled' ? result.value : result.reason
//   );

//   res.json({ services });
// });

// // Code Editor API endpoints
// app.get('/api/projects/:projectId/files', async (req, res) => {
//   try {
//     const projectId = req.params.projectId;
//     console.log('🔍 Getting files for project:', projectId);
    
//     const result = await pgPool.query(
//       'SELECT id, file_name, file_path, file_type, created_at FROM code_files WHERE project_id = $1 ORDER BY file_path',
//       [projectId]
//     );
    
//     res.json({ files: result.rows });
//   } catch (error) {
//     console.error('❌ Error getting project files:', error);
//     res.status(500).json({ error: error.message });
//   }
// });

// app.get('/api/files/:fileId/content', async (req, res) => {
//   try {
//     const fileId = req.params.fileId;
//     console.log('🔍 Getting content for file:', fileId);
    
//     const result = await pgPool.query(
//       'SELECT file_content, file_name, file_type FROM code_files WHERE id = $1',
//       [fileId]
//     );
    
//     if (result.rows.length === 0) {
//       return res.status(404).json({ error: 'File not found' });
//     }
    
//     res.json({ 
//       content: result.rows[0].file_content,
//       fileName: result.rows[0].file_name,
//       fileType: result.rows[0].file_type
//     });
//   } catch (error) {
//     console.error('❌ Error getting file content:', error);
//     res.status(500).json({ error: error.message });
//   }
// });

// app.put('/api/files/:fileId/content', async (req, res) => {
//   try {
//     const fileId = req.params.fileId;
//     const { content } = req.body;
//     console.log('🔍 Updating content for file:', fileId);
    
//     await pgPool.query(
//       'UPDATE code_files SET file_content = $1, updated_at = NOW() WHERE id = $2',
//       [content, fileId]
//     );
    
//     res.json({ success: true });
//   } catch (error) {
//     console.error('❌ Error updating file content:', error);
//     res.status(500).json({ error: error.message });
//   }
// });

// // Main dashboard page
// app.get('/', (req, res) => {
//   res.send(`
//     <!DOCTYPE html>
//     <html>
//     <head>
//         <title>AI Pipeline Dashboard</title>
//         <meta name="viewport" content="width=device-width, initial-scale=1.0">
//         <script src="/socket.io/socket.io.js"></script>
//         <style>
//             * { margin: 0; padding: 0; box-sizing: border-box; }
//             body { 
//                 font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif; 
//                 background: #0f172a; 
//                 color: #e2e8f0; 
//                 min-height: 100vh;
//             }
//             .dashboard { display: flex; height: 100vh; }
//             .sidebar { 
//                 width: 280px; 
//                 background: #1e293b; 
//                 border-right: 1px solid #334155; 
//                 overflow-y: auto;
//             }
//             .main-content { 
//                 flex: 1; 
//                 overflow-y: auto; 
//                 padding: 2rem; 
//             }
//             .nav-item { 
//                 display: block; 
//                 padding: 1rem 1.5rem; 
//                 color: #94a3b8; 
//                 text-decoration: none; 
//                 border-bottom: 1px solid #334155; 
//                 transition: all 0.3s;
//             }
//             .nav-item:hover, .nav-item.active { 
//                 background: #334155; 
//                 color: #60a5fa; 
//                 border-left: 4px solid #60a5fa;
//             }
//             .header { 
//                 padding: 1.5rem; 
//                 border-bottom: 1px solid #334155; 
//                 text-align: center;
//             }
//             .header h1 { 
//                 color: #60a5fa; 
//                 font-size: 1.5rem; 
//                 margin-bottom: 0.5rem;
//             }
//             .stats-grid { 
//                 display: grid; 
//                 grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); 
//                 gap: 1.5rem; 
//                 margin-bottom: 2rem;
//             }
//             .stat-card { 
//                 background: #1e293b; 
//                 border: 1px solid #334155; 
//                 border-radius: 8px; 
//                 padding: 1.5rem; 
//                 text-align: center;
//             }
//             .stat-value { 
//                 font-size: 2.5rem; 
//                 font-weight: bold; 
//                 color: #60a5fa; 
//                 margin-bottom: 0.5rem;
//             }
//             .stat-label { 
//                 color: #94a3b8; 
//                 font-size: 0.875rem;
//             }
//             .card { 
//                 background: #1e293b; 
//                 border: 1px solid #334155; 
//                 border-radius: 8px; 
//                 padding: 1.5rem; 
//                 margin-bottom: 1.5rem;
//             }
//             .btn { 
//                 background: #3b82f6; 
//                 color: white; 
//                 border: none; 
//                 padding: 0.5rem 1rem; 
//                 border-radius: 6px; 
//                 cursor: pointer; 
//                 font-size: 0.875rem; 
//                 transition: background 0.3s;
//             }
//             .btn:hover { background: #2563eb; }
//             .loading { 
//                 text-align: center; 
//                 padding: 2rem; 
//                 color: #94a3b8;
//             }
//             .status-indicator { 
//                 display: inline-block; 
//                 width: 8px; 
//                 height: 8px; 
//                 border-radius: 50%; 
//                 margin-right: 8px;
//             }
//             .status-healthy { background: #10b981; }
//             .status-unhealthy { background: #ef4444; }
//             .debug-info {
//                 background: #374151;
//                 color: #f9fafb;
//                 padding: 1rem;
//                 border-radius: 8px;
//                 margin: 1rem 0;
//                 font-family: monospace;
//                 font-size: 0.875rem;
//                 white-space: pre-wrap;
//             }
//             .error-info {
//                 background: #7f1d1d;
//                 color: #fca5a5;
//                 padding: 1rem;
//                 border-radius: 8px;
//                 margin: 1rem 0;
//                 font-family: monospace;
//                 font-size: 0.875rem;
//             }
//             #content { margin-top: 2rem; }
//         </style>
//     </head>
//     <body>
//         <div class="dashboard">
//             <div class="sidebar">
//                 <div class="header">
//                     <h1>⚡ AI Pipeline</h1>
//                     <p style="color: #94a3b8; font-size: 0.875rem;">Development Dashboard</p>
//                     <div style="margin-top: 1rem;">
//                         <span class="status-indicator status-healthy" id="connectionStatus"></span>
//                         <span style="font-size: 0.75rem; color: #94a3b8;" id="connectionText">Connected</span>
//                     </div>
//                 </div>
//                 <nav>
//                     <a href="#" class="nav-item active" onclick="showSection('overview')">📊 System Overview</a>
//                     <a href="#" class="nav-item" onclick="showSection('debug')">🔍 Database Debug</a>
//                     <a href="#" class="nav-item" onclick="showSection('projects')">📁 Project Gallery</a>
//                     <a href="#" class="nav-item" onclick="showSection('live-generation')">⚡ Live Generation</a>
//                     <a href="#" class="nav-item" onclick="showSection('code-editor')">💻 Code Editor</a>
//                     <a href="#" class="nav-item" onclick="showSection('quality-dashboard')">📈 Quality Dashboard</a>
//                     <a href="#" class="nav-item" onclick="showSection('pipeline-monitor')">🔄 Pipeline Monitor</a>
//                     <a href="#" class="nav-item" onclick="showSection('settings')">⚙️ Settings</a>
//                 </nav>
//             </div>

//             <div class="main-content">
//                 <div id="content"></div>
//             </div>
//         </div>

//         <script>
//             // Initialize Socket.IO
//             const socket = io();
            
//             socket.on('connect', () => {
//                 console.log('Connected to AI Pipeline Dashboard');
//                 updateConnectionStatus(true);
//             });
            
//             socket.on('disconnect', () => {
//                 console.log('Disconnected from AI Pipeline Dashboard');
//                 updateConnectionStatus(false);
//             });
            
//             function updateConnectionStatus(connected) {
//                 const indicator = document.getElementById('connectionStatus');
//                 const text = document.getElementById('connectionText');
                
//                 if (connected) {
//                     indicator.className = 'status-indicator status-healthy';
//                     text.textContent = 'Connected';
//                 } else {
//                     indicator.className = 'status-indicator status-unhealthy';
//                     text.textContent = 'Disconnected';
//                 }
//             }
            
//             // Navigation
//             function showSection(section) {
//                 document.querySelectorAll('.nav-item').forEach(item => {
//                     item.classList.remove('active');
//                 });
//                 event.target.classList.add('active');
                
//                 switch(section) {
//                     case 'overview':
//                         loadSystemOverview();
//                         break;
//                     case 'debug':
//                         loadDatabaseDebug();
//                         break;
//                     case 'projects':
//                         loadProjectGallery();
//                         break;
//                     case 'live-generation':
//                         loadLiveGeneration();
//                         break;
//                     case 'code-editor':
//                         loadCodeEditor();
//                         break;
//                     case 'quality-dashboard':
//                         loadQualityDashboard();
//                         break;
//                     case 'pipeline-monitor':
//                         loadPipelineMonitor();
//                         break;
//                     case 'settings':
//                         loadSettings();
//                         break;
//                 }
//             }
            
//             // Database debug page
//             async function loadDatabaseDebug() {
//                 const content = document.getElementById('content');
//                 content.innerHTML = '<div class="loading">Loading database debug info...</div>';
                
//                 try {
//                     const response = await fetch('/api/debug/database');
//                     const data = await response.json();
                    
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Database Debug</h2>
                        
//                         <div class="card">
//                             <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Connection Status</h3>
//                             <div class="debug-info">Connection: \${data.connection}
// Timestamp: \${data.timestamp}</div>
//                         </div>
                        
//                         <div class="card">
//                             <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Database Tables</h3>
//                             <div class="debug-info">project_contexts: \${data.tables.project_contexts} records
// code_files: \${data.tables.code_files} records
// improvement_history: \${data.tables.improvement_history} records</div>
//                         </div>
                        
//                         <div class="card">
//                             <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Sample Projects</h3>
//                             <div class="debug-info">\${data.sampleProjects.map(p => \`\${p.project_name} (ID: \${p.id})\`).join('\\n')}</div>
//                         </div>
                        
//                         <div class="card">
//                             <h3 style="color: #f1f5f9; margin-bottom: 1rem;">API Tests</h3>
//                             <button class="btn" onclick="testSystemStatus()">Test System Status</button>
//                             <button class="btn" onclick="testProjects()" style="margin-left: 0.5rem;">Test Projects</button>
//                             <button class="btn" onclick="testServices()" style="margin-left: 0.5rem;">Test Services</button>
//                             <div id="apiResults" style="margin-top: 1rem;"></div>
//                         </div>
//                     \`;
//                 } catch (error) {
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Database Debug</h2>
//                         <div class="error-info">Error: \${error.message}
// Could not connect to database</div>
//                     \`;
//                 }
//             }
            
//             async function testSystemStatus() {
//                 const resultsDiv = document.getElementById('apiResults');
//                 try {
//                     const response = await fetch('/api/system/status');
//                     const data = await response.json();
//                     resultsDiv.innerHTML = '<div class="debug-info">System Status: ' + JSON.stringify(data, null, 2) + '</div>';
//                 } catch (error) {
//                     resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
//                 }
//             }
            
//             async function testProjects() {
//                 const resultsDiv = document.getElementById('apiResults');
//                 try {
//                     const response = await fetch('/api/projects');
//                     const data = await response.json();
//                     resultsDiv.innerHTML = '<div class="debug-info">Projects: ' + JSON.stringify(data, null, 2) + '</div>';
//                 } catch (error) {
//                     resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
//                 }
//             }
            
//             async function testServices() {
//                 const resultsDiv = document.getElementById('apiResults');
//                 try {
//                     const response = await fetch('/api/services/health');
//                     const data = await response.json();
//                     resultsDiv.innerHTML = '<div class="debug-info">Services: ' + JSON.stringify(data, null, 2) + '</div>';
//                 } catch (error) {
//                     resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
//                 }
//             }
            
//             // System overview with better error handling
//             async function loadSystemOverview() {
//                 const content = document.getElementById('content');
//                 content.innerHTML = '<div class="loading">Loading system overview...</div>';
                
//                 try {
//                     const [statusRes, projectsRes, servicesRes] = await Promise.all([
//                         fetch('/api/system/status'),
//                         fetch('/api/projects'),
//                         fetch('/api/services/health')
//                     ]);
                    
//                     const status = await statusRes.json();
//                     const projects = await projectsRes.json();
//                     const services = await servicesRes.json();
                    
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">System Overview</h2>
                        
//                         <div class="stats-grid">
//                             <div class="stat-card">
//                                 <div class="stat-value">\${status.totalServices}</div>
//                                 <div class="stat-label">Total Services</div>
//                             </div>
//                             <div class="stat-card">
//                                 <div class="stat-value">\${status.healthyServices}</div>
//                                 <div class="stat-label">Healthy Services</div>
//                             </div>
//                             <div class="stat-card">
//                                 <div class="stat-value">\${status.totalProjects}</div>
//                                 <div class="stat-label">Total Projects</div>
//                             </div>
//                             <div class="stat-card">
//                                 <div class="stat-value">\${status.totalFiles}</div>
//                                 <div class="stat-label">Generated Files</div>
//                             </div>
//                             <div class="stat-card">
//                                 <div class="stat-value">\${status.activeProjects}</div>
//                                 <div class="stat-label">Active Projects</div>
//                             </div>
//                         </div>
                        
//                         <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 1.5rem;">
//                             <div class="card">
//                                 <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Recent Projects</h3>
//                                 <div style="max-height: 300px; overflow-y: auto;">
//                                     \${projects.projects.length > 0 ? 
//                                         projects.projects.slice(0, 5).map(project => \`
//                                             <div style="padding: 1rem; border-bottom: 1px solid #334155;">
//                                                 <div style="font-weight: bold; color: #f1f5f9;">\${project.project_name}</div>
//                                                 <div style="color: #94a3b8; font-size: 0.875rem;">\${project.file_count} files • \${new Date(project.created_at).toLocaleDateString()}</div>
//                                             </div>
//                                         \`).join('')
//                                         : '<div style="padding: 1rem; color: #94a3b8;">No projects found in database</div>'
//                                     }
//                                 </div>
//                             </div>
                            
//                             <div class="card">
//                                 <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Service Health</h3>
//                                 <div style="max-height: 300px; overflow-y: auto;">
//                                     \${services.services.map(service => \`
//                                         <div style="padding: 0.5rem; margin-bottom: 0.5rem; background: #334155; border-radius: 4px;">
//                                             <div style="display: flex; justify-content: space-between; align-items: center;">
//                                                 <span style="font-weight: bold; color: #f1f5f9;">\${service.name}</span>
//                                                 <span class="status-indicator status-\${service.status}"></span>
//                                             </div>
//                                             <div style="color: #94a3b8; font-size: 0.75rem;">Port: \${service.port} • \${service.responseTime}ms</div>
//                                         </div>
//                                     \`).join('')}
//                                 </div>
//                             </div>
//                         </div>
//                     \`;
//                 } catch (error) {
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">System Overview</h2>
//                         <div class="error-info">Error loading overview: \${error.message}
// Check the Database Debug tab for more information</div>
//                     \`;
//                 }
//             }
            
//             // Project Gallery
//             async function loadProjectGallery() {
//                 const content = document.getElementById('content');
//                 content.innerHTML = '<div class="loading">Loading projects...</div>';
                
//                 try {
//                     const response = await fetch('/api/projects');
//                     const data = await response.json();
                    
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Project Gallery</h2>
                        
//                         <div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(400px, 1fr)); gap: 1.5rem;">
//                             \${data.projects.map(project => \`
//                                 <div class="card" style="hover:border-color: #60a5fa; transition: border-color 0.3s;">
//                                     <h3 style="color: #f1f5f9; margin-bottom: 1rem;">\${project.project_name}</h3>
//                                     <div style="color: #94a3b8; font-size: 0.875rem; margin-bottom: 1rem;">
//                                         Files: \${project.file_count} • 
//                                         Created: \${new Date(project.created_at).toLocaleDateString()}
//                                     </div>
//                                     <div style="color: #94a3b8; font-size: 0.875rem;">
//                                         ID: \${project.id}
//                                     </div>
//                                 </div>
//                             \`).join('')}
//                         </div>
//                     \`;
//                 } catch (error) {
//                     content.innerHTML = \`
//                         <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Project Gallery</h2>
//                         <div class="error-info">Error loading projects: \${error.message}</div>
//                     \`;
//                 }
//             }
            
//             // Other sections (simplified)
//             function loadLiveGeneration() {
//                 document.getElementById('content').innerHTML = \`
//                     <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Live Code Generation</h2>
//                     <div class="card">
//                         <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Real-time Generation Monitoring</h3>
//                         <p style="color: #94a3b8;">Feature implementation in progress...</p>
//                     </div>
//                 \`;
//             }
            
//             function loadCodeEditor() {
//                 document.getElementById('content').innerHTML = \`
//                     <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Code Editor</h2>
//                     <div class="card">
//                         <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Monaco Editor Integration</h3>
//                         <p style="color: #94a3b8;">Code editing interface coming soon...</p>
//                     </div>
//                 \`;
//             }
            
//             function loadQualityDashboard() {
//                 document.getElementById('content').innerHTML = \`
//                     <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Quality Dashboard</h2>
//                     <div class="card">
//                         <h3 style="color: #f1f5f9; margin-bottom: 1rem;">AI-Powered Quality Improvements</h3>
//                         <p style="color: #94a3b8;">Quality metrics and improvement analytics coming soon...</p>
//                     </div>
//                 \`;
//             }
            
//             function loadPipelineMonitor() {
//                 document.getElementById('content').innerHTML = \`
//                     <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Pipeline Monitor</h2>
//                     <div class="card">
//                         <h3 style="color: #f1f5f9; margin-bottom: 1rem;">n8n Workflow Integration</h3>
//                         <p style="color: #94a3b8;">Pipeline monitoring integration coming soon...</p>
//                         <div style="margin-top: 1rem;">
//                             <button class="btn" onclick="window.open('http://localhost:5678', '_blank')">Open n8n Interface</button>
//                         </div>
//                     </div>
//                 \`;
//             }
            
//             function loadSettings() {
//                 document.getElementById('content').innerHTML = \`
//                     <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Settings</h2>
//                     <div class="card">
//                         <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Dashboard Configuration</h3>
//                         <p style="color: #94a3b8;">Settings and configuration options coming soon...</p>
//                     </div>
//                 \`;
//             }
            
//             // Load initial section
//             loadSystemOverview();
//         </script>
//     </body>
//     </html>
//   `);
// });

// // Initialize connections and start server
// async function startServer() {
//   try {
//     await pgPool.query('SELECT NOW()');
//     console.log('✅ Connected to PostgreSQL database (dev_pipeline)');
    
//     await redisClient.connect();
//     await redisClient.ping();
//     console.log('✅ Connected to Redis cache');
    
//     const PORT = process.env.PORT || 8008;
//     server.listen(PORT, '0.0.0.0', () => {
//       console.log(`🚀 AI Pipeline Dashboard running on port ${PORT}`);
//       console.log(`📊 Dashboard URL: http://localhost:${PORT}`);
//       console.log(`🔗 Integrated with existing database: dev_pipeline`);
//       console.log(`📁 Monitoring projects: ${Object.keys(SERVICES).length} services`);
//     });
//   } catch (error) {
//     console.error('❌ Failed to start dashboard:', error);
//     process.exit(1);
//   }
// }

// startServer();


const express = require('express');
const http = require('http');
const socketIo = require('socket.io');
const { Pool } = require('pg');
const Redis = require('redis');
const cors = require('cors');
const axios = require('axios');
const { exec } = require('child_process');
const util = require('util');
const execPromise = util.promisify(exec);

const app = express();
const server = http.createServer(app);
const io = socketIo(server, {
  cors: { origin: "*", methods: ["GET", "POST", "PUT", "DELETE"] }
});

// Database connections
const pgPool = new Pool({
  host: process.env.POSTGRES_HOST || 'pipeline_postgres',
  port: process.env.POSTGRES_PORT || 5432,
  database: process.env.POSTGRES_DB || 'dev_pipeline',
  user: process.env.POSTGRES_USER || 'pipeline_admin',
  password: process.env.POSTGRES_PASSWORD || 'secure_pipeline_2024',
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

const redisClient = Redis.createClient({
  socket: {
    host: process.env.REDIS_HOST || 'pipeline_redis',
    port: process.env.REDIS_PORT || 6379
  },
  password: process.env.REDIS_PASSWORD || 'redis_secure_2024'
});

redisClient.on('error', (err) => console.log('Redis Client Error', err));

// Services configuration
const SERVICES = {
  'api-gateway': { 
    port: 8000, 
    name: 'API Gateway', 
    container: 'pipeline_api_gateway',
    url: 'http://pipeline_api_gateway:8000'
  },
  'requirement-processor': { 
    port: 8001, 
    name: 'Requirement Processor', 
    container: 'pipeline_requirement_processor',
    url: 'http://pipeline_requirement_processor:8001'
  },
  'tech-stack-selector': { 
    port: 8002, 
    name: 'Tech Stack Selector', 
    container: 'pipeline_tech_stack_selector',
    url: 'http://pipeline_tech_stack_selector:8002'
  },
  'architecture-designer': { 
    port: 8003, 
    name: 'Architecture Designer', 
    container: 'pipeline_architecture_designer',
    url: 'http://pipeline_architecture_designer:8003'
  },
  'code-generator': { 
    port: 8004, 
    name: 'Code Generator', 
    container: 'pipeline_code_generator',
    url: 'http://pipeline_code_generator:8004'
  },
  'test-generator': { 
    port: 8005, 
    name: 'Test Generator', 
    container: 'pipeline_test_generator',
    url: 'http://pipeline_test_generator:8005'
  },
  'deployment-manager': { 
    port: 8006, 
    name: 'Deployment Manager', 
    container: 'pipeline_deployment_manager',
    url: 'http://pipeline_deployment_manager:8006'
  },
  'self-improving-generator': { 
    port: 8007, 
    name: 'Self-Improving Generator', 
    container: 'pipeline_self_improving_generator',
    url: 'http://pipeline_self_improving_generator:8007'
  }
};

// Middleware
app.use(cors());
app.use(express.json({ limit: '50mb' }));
app.use(express.urlencoded({ extended: true, limit: '50mb' }));

// Database service - FIXED to work with your actual database structure
class DatabaseService {
  static async getProjects(filters = {}) {
    try {
      console.log('🔍 Querying projects from database...');
      
      // Simple query for project_contexts table - NO JOINS
      let query = `
        SELECT 
          id,
          project_name,
          technology_stack,
          all_features,
          completed_features,
          pending_features,
          project_path,
          created_at,
          updated_at
        FROM project_contexts
      `;

      const conditions = [];
      const values = [];
      let paramCount = 0;

      if (filters.project_name) {
        conditions.push(`project_name ILIKE $${++paramCount}`);
        values.push(`%${filters.project_name}%`);
      }

      if (conditions.length > 0) {
        query += ` WHERE ${conditions.join(' AND ')}`;
      }

      query += ` ORDER BY created_at DESC LIMIT 20`;

      console.log('🔍 Executing query:', query);
      
      const result = await pgPool.query(query, values);
      console.log('✅ Query result:', result.rows.length, 'projects found');
      
      // Get file counts for each project separately - SAFE QUERIES
      for (let project of result.rows) {
        try {
          const fileCountResult = await pgPool.query(
            'SELECT COUNT(*) FROM code_files WHERE project_id = $1',
            [project.id]
          );
          project.file_count = parseInt(fileCountResult.rows[0].count);
        } catch (fileError) {
          console.log('⚠️ Could not get file count for project', project.id);
          project.file_count = 0;
        }
      }

      return result.rows;
    } catch (error) {
      console.error('❌ Database query error:', error.message);
      throw error;
    }
  }

  static async getSystemStats() {
    try {
      console.log('🔍 Getting system stats from database...');
      
      // Get counts from each table - SIMPLE QUERIES
      const projectCountResult = await pgPool.query('SELECT COUNT(*) FROM project_contexts');
      const fileCountResult = await pgPool.query('SELECT COUNT(*) FROM code_files');
      const improvementCountResult = await pgPool.query('SELECT COUNT(*) FROM improvement_history');

      // Get recent projects (last 24 hours)
      const recentResult = await pgPool.query(`
        SELECT COUNT(*) FROM project_contexts 
        WHERE created_at > NOW() - INTERVAL '24 hours'
      `);

      const stats = {
        project_contexts: parseInt(projectCountResult.rows[0].count),
        code_files: parseInt(fileCountResult.rows[0].count),
        improvement_history: parseInt(improvementCountResult.rows[0].count),
        recent_projects: parseInt(recentResult.rows[0].count)
      };

      console.log('📊 System stats:', stats);
      return stats;
    } catch (error) {
      console.error('❌ System stats error:', error);
      return {
        project_contexts: 0,
        code_files: 0,
        improvement_history: 0,
        recent_projects: 0
      };
    }
  }
}

// WebSocket connection handling
io.on('connection', (socket) => {
  console.log(`Dashboard client connected: ${socket.id}`);
  
  socket.emit('connected', { 
    message: 'Connected to AI Pipeline Dashboard',
    timestamp: new Date().toISOString()
  });
  
  socket.on('disconnect', () => {
    console.log(`Dashboard client disconnected: ${socket.id}`);
  });
});

// API Routes
app.get('/api/health', (req, res) => {
  res.json({ 
    status: 'healthy', 
    timestamp: new Date().toISOString(),
    service: 'AI Pipeline Dashboard',
    version: '2.0.0'
  });
});

// Debug endpoint
app.get('/api/debug/database', async (req, res) => {
  try {
    console.log('🔍 Database debug endpoint called');
    
    const connectionTest = await pgPool.query('SELECT NOW()');
    console.log('✅ Database connection successful');
    
    const projectCount = await pgPool.query('SELECT COUNT(*) FROM project_contexts');
    const fileCount = await pgPool.query('SELECT COUNT(*) FROM code_files');
    const improvementCount = await pgPool.query('SELECT COUNT(*) FROM improvement_history');
    
    // Get sample project names
    let sampleProjects = [];
    const sampleResult = await pgPool.query('SELECT id, project_name, created_at FROM project_contexts ORDER BY created_at DESC LIMIT 3');
    sampleProjects = sampleResult.rows;
    
    res.json({
      connection: 'OK',
      timestamp: connectionTest.rows[0].now,
      tables: {
        project_contexts: parseInt(projectCount.rows[0].count),
        code_files: parseInt(fileCount.rows[0].count),
        improvement_history: parseInt(improvementCount.rows[0].count)
      },
      sampleProjects: sampleProjects
    });
    
  } catch (error) {
    console.error('❌ Database debug error:', error);
    res.status(500).json({ 
      error: error.message,
      connection: 'FAILED' 
    });
  }
});

// System status with real data
app.get('/api/system/status', async (req, res) => {
  try {
    console.log('🔍 System status endpoint called');
    
    let healthyServices = 0;
    const serviceChecks = [];

    // Check services
    for (const [key, service] of Object.entries(SERVICES)) {
      try {
        await axios.get(`${service.url}/health`, { timeout: 5000 });
        healthyServices++;
        serviceChecks.push({ service: key, status: 'healthy' });
      } catch (error) {
        serviceChecks.push({ service: key, status: 'unhealthy', error: error.message });
      }
    }

    // Get database stats
    const dbStats = await DatabaseService.getSystemStats();

    res.json({
      healthyServices,
      totalServices: Object.keys(SERVICES).length,
      totalProjects: dbStats.project_contexts,
      totalFiles: dbStats.code_files,
      activeProjects: dbStats.recent_projects,
      improvements: dbStats.improvement_history,
      serviceChecks,
      timestamp: new Date().toISOString()
    });
  } catch (error) {
    console.error('❌ System status error:', error);
    res.status(500).json({ error: error.message });
  }
});

// Projects endpoint
app.get('/api/projects', async (req, res) => {
  try {
    console.log('🔍 Projects endpoint called');
    const projects = await DatabaseService.getProjects(req.query);
    console.log('✅ Projects returned:', projects.length);
    res.json({ projects });
  } catch (error) {
    console.error('❌ Projects endpoint error:', error);
    res.status(500).json({ 
      error: error.message,
      details: 'Check server logs for database connection issues'
    });
  }
});

// Services health endpoint
app.get('/api/services/health', async (req, res) => {
  console.log('🔍 Services health check called');
  
  const healthChecks = await Promise.allSettled(
    Object.entries(SERVICES).map(async ([key, service]) => {
      const startTime = Date.now();
      try {
        await axios.get(`${service.url}/health`, { timeout: 5000 });
        return {
          name: service.name,
          status: 'healthy',
          port: service.port,
          container: service.container,
          responseTime: Date.now() - startTime,
          lastCheck: new Date().toISOString()
        };
      } catch (error) {
        return {
          name: service.name,
          status: 'unhealthy',
          port: service.port,
          container: service.container,
          responseTime: Date.now() - startTime,
          error: error.message,
          lastCheck: new Date().toISOString()
        };
      }
    })
  );

  const services = healthChecks.map(result => 
    result.status === 'fulfilled' ? result.value : result.reason
  );

  res.json({ services });
});

// Code Editor API endpoints - FIXED to handle missing files gracefully
app.get('/api/projects/:projectId/files', async (req, res) => {
  try {
    const projectId = req.params.projectId;
    console.log('🔍 Getting files for project:', projectId);
    
    const result = await pgPool.query(
      'SELECT id, file_path, file_type, created_at FROM code_files WHERE project_id = $1 ORDER BY file_path',
      [projectId]
    );
    
    res.json({ files: result.rows });
  } catch (error) {
    console.error('❌ Error getting project files:', error);
    res.status(500).json({ error: error.message });
  }
});

// Helper function to try reading file from different containers
async function tryReadFileFromContainers(filePath, projectId) {
  const containers = ['pipeline_code_generator', 'pipeline_self_improving_generator'];
  const possiblePaths = [
    `/tmp/generated-projects/${projectId}/${filePath}`,
    `/tmp/projects/${projectId}/${filePath}`,
    `/app/projects/${projectId}/${filePath}`,
    `/tmp/${filePath}`,
    `/app/${filePath}`
  ];

  for (const container of containers) {
    for (const path of possiblePaths) {
      try {
        const { stdout } = await execPromise(`docker exec ${container} cat "${path}" 2>/dev/null`);
        if (stdout) {
          console.log(`✅ Found file in ${container} at ${path}`);
          return stdout;
        }
      } catch (error) {
        // Continue trying other paths
      }
    }
  }

  return null;
}

app.get('/api/files/:fileId/content', async (req, res) => {
  try {
    const fileId = req.params.fileId;
    console.log('🔍 Getting content for file:', fileId);
    
    // Get file info from database
    const result = await pgPool.query(
      'SELECT file_path, file_type, project_id FROM code_files WHERE id = $1',
      [fileId]
    );
    
    if (result.rows.length === 0) {
      return res.status(404).json({ error: 'File not found' });
    }
    
    const file = result.rows[0];
    const fileName = file.file_path.split('/').pop() || file.file_path;
    
    // Try to read file from containers
    let content = await tryReadFileFromContainers(file.file_path, file.project_id);
    
    if (!content) {
      // Generate sample content based on file type
      content = generateSampleContent(file.file_path, file.file_type);
    }
    
    res.json({ 
      content: content,
      fileName: fileName,
      fileType: file.file_type
    });
  } catch (error) {
    console.error('❌ Error getting file content:', error);
    res.status(500).json({ error: error.message });
  }
});

// Generate sample content when files are not found
function generateSampleContent(filePath, fileType) {
  const fileName = filePath.split('/').pop();
  const ext = fileName.split('.').pop();
  
  switch (ext) {
    case 'tsx':
    case 'jsx':
      return `// ${fileName}
// This file was generated but the content is no longer available on disk
// The file structure and metadata are preserved in the database

import React from 'react';

const ${fileName.split('.')[0]} = () => {
  return (
    <div>
      <h1>Generated Component: ${fileName}</h1>
      <p>Original file path: ${filePath}</p>
      <p>File type: ${fileType}</p>
      <p>Note: This is placeholder content. The original generated code is not available.</p>
    </div>
  );
};

export default ${fileName.split('.')[0]};`;

    case 'ts':
    case 'js':
      return `// ${fileName}
// This file was generated but the content is no longer available on disk
// The file structure and metadata are preserved in the database

/**
 * Original file path: ${filePath}
 * File type: ${fileType}
 * Note: This is placeholder content. The original generated code is not available.
 */

export default function ${fileName.split('.')[0]}() {
  console.log('Generated file: ${fileName}');
  
  // Original implementation would be here
  return {
    message: 'This file was generated but content is not available',
    path: '${filePath}',
    type: '${fileType}'
  };
}`;

    case 'py':
      return `# ${fileName}
# This file was generated but the content is no longer available on disk
# The file structure and metadata are preserved in the database

"""
Original file path: ${filePath}
File type: ${fileType}
Note: This is placeholder content. The original generated code is not available.
"""

def main():
    print("Generated file: ${fileName}")
    print("Original path: ${filePath}")
    print("File type: ${fileType}")
    # Original implementation would be here
    pass

if __name__ == "__main__":
    main()`;

    case 'html':
      return `<!-- ${fileName} -->
<!-- This file was generated but the content is no longer available on disk -->
<!-- The file structure and metadata are preserved in the database -->

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>${fileName}</title>
</head>
<body>
    <h1>Generated File: ${fileName}</h1>
    <p>Original file path: ${filePath}</p>
    <p>File type: ${fileType}</p>
    <p><em>Note: This is placeholder content. The original generated code is not available.</em></p>
</body>
</html>`;

    case 'css':
      return `/* ${fileName} */
/* This file was generated but the content is no longer available on disk */
/* The file structure and metadata are preserved in the database */

/*
Original file path: ${filePath}
File type: ${fileType}
Note: This is placeholder content. The original generated code is not available.
*/

body {
    font-family: Arial, sans-serif;
    margin: 0;
    padding: 20px;
    background-color: #f5f5f5;
}

.container {
    max-width: 800px;
    margin: 0 auto;
    background: white;
    padding: 20px;
    border-radius: 8px;
    box-shadow: 0 2px 4px rgba(0,0,0,0.1);
}

/* Original styles would be here */`;

    default:
      return `// ${fileName}
// This file was generated but the content is no longer available on disk
// The file structure and metadata are preserved in the database

/*
Original file path: ${filePath}
File type: ${fileType}
Note: This is placeholder content. The original generated code is not available.

To fix this issue, you need to:
1. Ensure generated files are stored in a persistent volume
2. Mount the volume between the code generator and dashboard containers
3. Or implement a file storage service to persist generated code
*/`;
  }
}

app.put('/api/files/:fileId/content', async (req, res) => {
  try {
    const fileId = req.params.fileId;
    const { content } = req.body;
    console.log('🔍 Updating content for file:', fileId);
    
    // For now, just return success since files aren't persisted
    // In a real implementation, you'd save the content to a persistent storage
    console.log('⚠️ File content updated in memory only (not persisted)');
    
    res.json({ 
      success: true,
      message: 'Content updated (note: changes are not persisted to disk)'
    });
  } catch (error) {
    console.error('❌ Error updating file content:', error);
    res.status(500).json({ error: error.message });
  }
});

// Main dashboard page
app.get('/', (req, res) => {
  res.send(`
    <!DOCTYPE html>
    <html>
    <head>
        <title>AI Pipeline Dashboard</title>
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <script src="/socket.io/socket.io.js"></script>
        <style>
            * { margin: 0; padding: 0; box-sizing: border-box; }
            body { 
                font-family: 'Inter', -apple-system, BlinkMacSystemFont, sans-serif; 
                background: #0f172a; 
                color: #e2e8f0; 
                min-height: 100vh;
            }
            .dashboard { display: flex; height: 100vh; }
            .sidebar { 
                width: 280px; 
                background: #1e293b; 
                border-right: 1px solid #334155; 
                overflow-y: auto;
            }
            .main-content { 
                flex: 1; 
                overflow-y: auto; 
                padding: 2rem; 
            }
            .nav-item { 
                display: block; 
                padding: 1rem 1.5rem; 
                color: #94a3b8; 
                text-decoration: none; 
                border-bottom: 1px solid #334155; 
                transition: all 0.3s;
            }
            .nav-item:hover, .nav-item.active { 
                background: #334155; 
                color: #60a5fa; 
                border-left: 4px solid #60a5fa;
            }
            .header { 
                padding: 1.5rem; 
                border-bottom: 1px solid #334155; 
                text-align: center;
            }
            .header h1 { 
                color: #60a5fa; 
                font-size: 1.5rem; 
                margin-bottom: 0.5rem;
            }
            .stats-grid { 
                display: grid; 
                grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); 
                gap: 1.5rem; 
                margin-bottom: 2rem;
            }
            .stat-card { 
                background: #1e293b; 
                border: 1px solid #334155; 
                border-radius: 8px; 
                padding: 1.5rem; 
                text-align: center;
            }
            .stat-value { 
                font-size: 2.5rem; 
                font-weight: bold; 
                color: #60a5fa; 
                margin-bottom: 0.5rem;
            }
            .stat-label { 
                color: #94a3b8; 
                font-size: 0.875rem;
            }
            .card { 
                background: #1e293b; 
                border: 1px solid #334155; 
                border-radius: 8px; 
                padding: 1.5rem; 
                margin-bottom: 1.5rem;
            }
            .btn { 
                background: #3b82f6; 
                color: white; 
                border: none; 
                padding: 0.5rem 1rem; 
                border-radius: 6px; 
                cursor: pointer; 
                font-size: 0.875rem; 
                transition: background 0.3s;
            }
            .btn:hover { background: #2563eb; }
            .loading { 
                text-align: center; 
                padding: 2rem; 
                color: #94a3b8;
            }
            .status-indicator { 
                display: inline-block; 
                width: 8px; 
                height: 8px; 
                border-radius: 50%; 
                margin-right: 8px;
            }
            .status-healthy { background: #10b981; }
            .status-unhealthy { background: #ef4444; }
            .debug-info {
                background: #374151;
                color: #f9fafb;
                padding: 1rem;
                border-radius: 8px;
                margin: 1rem 0;
                font-family: monospace;
                font-size: 0.875rem;
                white-space: pre-wrap;
            }
            .error-info {
                background: #7f1d1d;
                color: #fca5a5;
                padding: 1rem;
                border-radius: 8px;
                margin: 1rem 0;
                font-family: monospace;
                font-size: 0.875rem;
            }
            .warning-info {
                background: #92400e;
                color: #fcd34d;
                padding: 1rem;
                border-radius: 8px;
                margin: 1rem 0;
                font-family: monospace;
                font-size: 0.875rem;
            }
            #content { margin-top: 2rem; }
        </style>
    </head>
    <body>
        <div class="dashboard">
            <div class="sidebar">
                <div class="header">
                    <h1>⚡ AI Pipeline</h1>
                    <p style="color: #94a3b8; font-size: 0.875rem;">Development Dashboard</p>
                    <div style="margin-top: 1rem;">
                        <span class="status-indicator status-healthy" id="connectionStatus"></span>
                        <span style="font-size: 0.75rem; color: #94a3b8;" id="connectionText">Connected</span>
                    </div>
                </div>
                <nav>
                    <a href="#" class="nav-item active" onclick="showSection('overview')">📊 System Overview</a>
                    <a href="#" class="nav-item" onclick="showSection('debug')">🔍 Database Debug</a>
                    <a href="#" class="nav-item" onclick="showSection('projects')">📁 Project Gallery</a>
                    <a href="#" class="nav-item" onclick="showSection('live-generation')">⚡ Live Generation</a>
                    <a href="#" class="nav-item" onclick="showSection('code-editor')">💻 Code Editor</a>
                    <a href="#" class="nav-item" onclick="showSection('quality-dashboard')">📈 Quality Dashboard</a>
                    <a href="#" class="nav-item" onclick="showSection('pipeline-monitor')">🔄 Pipeline Monitor</a>
                    <a href="#" class="nav-item" onclick="showSection('settings')">⚙️ Settings</a>
                </nav>
            </div>

            <div class="main-content">
                <div id="content"></div>
            </div>
        </div>

        <script>
            // Initialize Socket.IO
            const socket = io();
            
            socket.on('connect', () => {
                console.log('Connected to AI Pipeline Dashboard');
                updateConnectionStatus(true);
            });
            
            socket.on('disconnect', () => {
                console.log('Disconnected from AI Pipeline Dashboard');
                updateConnectionStatus(false);
            });
            
            function updateConnectionStatus(connected) {
                const indicator = document.getElementById('connectionStatus');
                const text = document.getElementById('connectionText');
                
                if (connected) {
                    indicator.className = 'status-indicator status-healthy';
                    text.textContent = 'Connected';
                } else {
                    indicator.className = 'status-indicator status-unhealthy';
                    text.textContent = 'Disconnected';
                }
            }
            
            // Navigation
            function showSection(section) {
                document.querySelectorAll('.nav-item').forEach(item => {
                    item.classList.remove('active');
                });
                event.target.classList.add('active');
                
                switch(section) {
                    case 'overview':
                        loadSystemOverview();
                        break;
                    case 'debug':
                        loadDatabaseDebug();
                        break;
                    case 'projects':
                        loadProjectGallery();
                        break;
                    case 'live-generation':
                        loadLiveGeneration();
                        break;
                    case 'code-editor':
                        loadCodeEditor();
                        break;
                    case 'quality-dashboard':
                        loadQualityDashboard();
                        break;
                    case 'pipeline-monitor':
                        loadPipelineMonitor();
                        break;
                    case 'settings':
                        loadSettings();
                        break;
                }
            }
            
            // Database debug page
            async function loadDatabaseDebug() {
                const content = document.getElementById('content');
                content.innerHTML = '<div class="loading">Loading database debug info...</div>';
                
                try {
                    const response = await fetch('/api/debug/database');
                    const data = await response.json();
                    
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Database Debug</h2>
                        
                        <div class="card">
                            <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Connection Status</h3>
                            <div class="debug-info">Connection: \${data.connection}
Timestamp: \${data.timestamp}</div>
                        </div>
                        
                        <div class="card">
                            <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Database Tables</h3>
                            <div class="debug-info">project_contexts: \${data.tables.project_contexts} records
code_files: \${data.tables.code_files} records
improvement_history: \${data.tables.improvement_history} records</div>
                        </div>
                        
                        <div class="card">
                            <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Sample Projects</h3>
                            <div class="debug-info">\${data.sampleProjects.map(p => \`\${p.project_name} (ID: \${p.id})\`).join('\\n')}</div>
                        </div>
                        
                        <div class="card">
                            <h3 style="color: #f1f5f9; margin-bottom: 1rem;">API Tests</h3>
                            <button class="btn" onclick="testSystemStatus()">Test System Status</button>
                            <button class="btn" onclick="testProjects()" style="margin-left: 0.5rem;">Test Projects</button>
                            <button class="btn" onclick="testServices()" style="margin-left: 0.5rem;">Test Services</button>
                            <div id="apiResults" style="margin-top: 1rem;"></div>
                        </div>
                    \`;
                } catch (error) {
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Database Debug</h2>
                        <div class="error-info">Error: \${error.message}
Could not connect to database</div>
                    \`;
                }
            }
            
            async function testSystemStatus() {
                const resultsDiv = document.getElementById('apiResults');
                try {
                    const response = await fetch('/api/system/status');
                    const data = await response.json();
                    resultsDiv.innerHTML = '<div class="debug-info">System Status: ' + JSON.stringify(data, null, 2) + '</div>';
                } catch (error) {
                    resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
                }
            }
            
            async function testProjects() {
                const resultsDiv = document.getElementById('apiResults');
                try {
                    const response = await fetch('/api/projects');
                    const data = await response.json();
                    resultsDiv.innerHTML = '<div class="debug-info">Projects: ' + JSON.stringify(data, null, 2) + '</div>';
                } catch (error) {
                    resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
                }
            }
            
            async function testServices() {
                const resultsDiv = document.getElementById('apiResults');
                try {
                    const response = await fetch('/api/services/health');
                    const data = await response.json();
                    resultsDiv.innerHTML = '<div class="debug-info">Services: ' + JSON.stringify(data, null, 2) + '</div>';
                } catch (error) {
                    resultsDiv.innerHTML = '<div class="error-info">Error: ' + error.message + '</div>';
                }
            }
            
            // System overview with better error handling
            async function loadSystemOverview() {
                const content = document.getElementById('content');
                content.innerHTML = '<div class="loading">Loading system overview...</div>';
                
                try {
                    const [statusRes, projectsRes, servicesRes] = await Promise.all([
                        fetch('/api/system/status'),
                        fetch('/api/projects'),
                        fetch('/api/services/health')
                    ]);
                    
                    const status = await statusRes.json();
                    const projects = await projectsRes.json();
                    const services = await servicesRes.json();
                    
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">System Overview</h2>
                        
                        <div class="stats-grid">
                            <div class="stat-card">
                                <div class="stat-value">\${status.totalServices}</div>
                                <div class="stat-label">Total Services</div>
                            </div>
                            <div class="stat-card">
                                <div class="stat-value">\${status.healthyServices}</div>
                                <div class="stat-label">Healthy Services</div>
                            </div>
                            <div class="stat-card">
                                <div class="stat-value">\${status.totalProjects}</div>
                                <div class="stat-label">Total Projects</div>
                            </div>
                            <div class="stat-card">
                                <div class="stat-value">\${status.totalFiles}</div>
                                <div class="stat-label">Generated Files</div>
                            </div>
                            <div class="stat-card">
                                <div class="stat-value">\${status.activeProjects}</div>
                                <div class="stat-label">Active Projects</div>
                            </div>
                        </div>
                        
                        <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 1.5rem;">
                            <div class="card">
                                <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Recent Projects</h3>
                                <div style="max-height: 300px; overflow-y: auto;">
                                    \${projects.projects.length > 0 ? 
                                        projects.projects.slice(0, 5).map(project => \`
                                            <div style="padding: 1rem; border-bottom: 1px solid #334155;">
                                                <div style="font-weight: bold; color: #f1f5f9;">\${project.project_name}</div>
                                                <div style="color: #94a3b8; font-size: 0.875rem;">\${project.file_count} files • \${new Date(project.created_at).toLocaleDateString()}</div>
                                            </div>
                                        \`).join('')
                                        : '<div style="padding: 1rem; color: #94a3b8;">No projects found in database</div>'
                                    }
                                </div>
                            </div>
                            
                            <div class="card">
                                <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Service Health</h3>
                                <div style="max-height: 300px; overflow-y: auto;">
                                    \${services.services.map(service => \`
                                        <div style="padding: 0.5rem; margin-bottom: 0.5rem; background: #334155; border-radius: 4px;">
                                            <div style="display: flex; justify-content: space-between; align-items: center;">
                                                <span style="font-weight: bold; color: #f1f5f9;">\${service.name}</span>
                                                <span class="status-indicator status-\${service.status}"></span>
                                            </div>
                                            <div style="color: #94a3b8; font-size: 0.75rem;">Port: \${service.port} • \${service.responseTime}ms</div>
                                        </div>
                                    \`).join('')}
                                </div>
                            </div>
                        </div>
                    \`;
                } catch (error) {
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">System Overview</h2>
                        <div class="error-info">Error loading overview: \${error.message}
Check the Database Debug tab for more information</div>
                    \`;
                }
            }
            
            // Project Gallery
            async function loadProjectGallery() {
                const content = document.getElementById('content');
                content.innerHTML = '<div class="loading">Loading projects...</div>';
                
                try {
                    const response = await fetch('/api/projects');
                    const data = await response.json();
                    
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Project Gallery</h2>
                        
                        <div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(400px, 1fr)); gap: 1.5rem;">
                            \${data.projects.map(project => \`
                                <div class="card" style="hover:border-color: #60a5fa; transition: border-color 0.3s;">
                                    <h3 style="color: #f1f5f9; margin-bottom: 1rem;">\${project.project_name}</h3>
                                    <div style="color: #94a3b8; font-size: 0.875rem; margin-bottom: 1rem;">
                                        Files: \${project.file_count} • 
                                        Created: \${new Date(project.created_at).toLocaleDateString()}
                                    </div>
                                    <div style="color: #94a3b8; font-size: 0.875rem;">
                                        ID: \${project.id}
                                    </div>
                                </div>
                            \`).join('')}
                        </div>
                    \`;
                } catch (error) {
                    content.innerHTML = \`
                        <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Project Gallery</h2>
                        <div class="error-info">Error loading projects: \${error.message}</div>
                    \`;
                }
            }
            
            // Other sections (simplified)
            function loadLiveGeneration() {
                document.getElementById('content').innerHTML = \`
                    <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Live Code Generation</h2>
                    <div class="card">
                        <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Real-time Generation Monitoring</h3>
                        <p style="color: #94a3b8;">Feature implementation in progress...</p>
                    </div>
                \`;
            }
            
            // Enhanced Code Editor with File Not Found handling
            function loadCodeEditor() {
                document.getElementById('content').innerHTML = \`
                    <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Code Editor</h2>
                    
                    <div class="warning-info">
                        <strong>⚠️ Note:</strong> Generated files are not persisted between container restarts. 
                        You'll see placeholder content for files that are no longer available on disk.
                        The file structure and metadata are preserved in the database.
                    </div>
                    
                    <div style="display: grid; grid-template-columns: 300px 1fr; gap: 1.5rem; height: 75vh;">
                        <div class="card" style="height: 100%; overflow-y: auto;">
                            <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Projects</h3>
                            <div id="projectsList">Loading projects...</div>
                        </div>
                        
                        <div class="card" style="height: 100%; display: flex; flex-direction: column;">
                            <div style="display: flex; justify-content: space-between; align-items: center; margin-bottom: 1rem;">
                                <h3 style="color: #f1f5f9;" id="currentFileName">Select a file to edit</h3>
                                <button class="btn" onclick="saveCurrentFile()" id="saveBtn" style="display: none;">Save</button>
                            </div>
                            
                            <div style="flex: 1; display: flex; flex-direction: column;">
                                <div id="fileContent" style="flex: 1; background: #0f172a; border: 1px solid #334155; border-radius: 4px; padding: 1rem; font-family: monospace; color: #e2e8f0; overflow-y: auto;">
                                    <div style="text-align: center; color: #94a3b8; padding: 2rem;">
                                        Select a project and file to view/edit code
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>
                \`;
                
                loadProjectsForEditor();
            }
            
            // Code Editor functions
            async function loadProjectsForEditor() {
                try {
                    const response = await fetch('/api/projects');
                    const data = await response.json();
                    
                    let projectsHtml = '';
                    data.projects.forEach(project => {
                        projectsHtml += '<div style="padding: 0.5rem; margin-bottom: 0.5rem; background: #334155; border-radius: 4px; cursor: pointer;" data-project-id="' + project.id + '">';
                        projectsHtml += '<div style="font-weight: bold; color: #f1f5f9;">' + project.project_name + '</div>';
                        projectsHtml += '<div style="color: #94a3b8; font-size: 0.75rem;">' + project.file_count + ' files</div>';
                        projectsHtml += '</div>';
                    });
                    
                    document.getElementById('projectsList').innerHTML = projectsHtml;
                    
                    // Add event listeners
                    document.querySelectorAll('[data-project-id]').forEach(element => {
                        element.addEventListener('click', function() {
                            const projectId = this.getAttribute('data-project-id');
                            loadProjectFiles(projectId);
                        });
                    });
                } catch (error) {
                    document.getElementById('projectsList').innerHTML = '<div style="color: #ef4444; padding: 1rem;">Error loading projects</div>';
                }
            }
            
            async function loadProjectFiles(projectId) {
                try {
                    const response = await fetch('/api/projects/' + projectId + '/files');
                    const data = await response.json();
                    
                    let filesHtml = '<button class="btn" onclick="loadProjectsForEditor()" style="width: 100%; margin-bottom: 1rem;">← Back to Projects</button>';
                    
                    data.files.forEach(file => {
                        const fileName = file.file_path.split('/').pop() || file.file_path;
                        filesHtml += '<div style="padding: 0.5rem; margin-bottom: 0.25rem; background: #1e293b; border-radius: 4px; cursor: pointer;" data-file-id="' + file.id + '">';
                        filesHtml += '<div style="font-weight: bold; color: #f1f5f9; font-size: 0.875rem;">' + fileName + '</div>';
                        filesHtml += '<div style="color: #94a3b8; font-size: 0.75rem;">' + file.file_path + '</div>';
                        filesHtml += '</div>';
                    });
                    
                    document.getElementById('projectsList').innerHTML = filesHtml;
                    
                    // Add event listeners for files
                    document.querySelectorAll('[data-file-id]').forEach(element => {
                        element.addEventListener('click', function() {
                            const fileId = this.getAttribute('data-file-id');
                            loadFileContent(fileId);
                        });
                    });
                } catch (error) {
                    document.getElementById('projectsList').innerHTML = '<div style="color: #ef4444; padding: 1rem;">Error loading files</div>';
                }
            }
            
            let currentFileId = null;
            
            async function loadFileContent(fileId) {
                try {
                    const response = await fetch('/api/files/' + fileId + '/content');
                    const data = await response.json();
                    
                    currentFileId = fileId;
                    document.getElementById('currentFileName').textContent = data.fileName;
                    document.getElementById('saveBtn').style.display = 'block';
                    
                    document.getElementById('fileContent').innerHTML = 
                        '<textarea id="codeEditor" style="width: 100%; height: 100%; background: #0f172a; border: none; color: #e2e8f0; font-family: monospace; font-size: 14px; padding: 1rem; resize: none; outline: none;">' + 
                        (data.content || '') + 
                        '</textarea>';
                } catch (error) {
                    document.getElementById('fileContent').innerHTML = '<div style="color: #ef4444; padding: 1rem;">Error loading file: ' + error.message + '</div>';
                }
            }
            
            async function saveCurrentFile() {
                if (!currentFileId) return;
                
                const saveBtn = document.getElementById('saveBtn');
                saveBtn.textContent = 'Saving...';
                saveBtn.disabled = true;
                
                try {
                    const editor = document.getElementById('codeEditor');
                    const response = await fetch('/api/files/' + currentFileId + '/content', {
                        method: 'PUT',
                        headers: { 'Content-Type': 'application/json' },
                        body: JSON.stringify({ content: editor.value })
                    });
                    
                    if (response.ok) {
                        const result = await response.json();
                        saveBtn.textContent = 'Saved!';
                        saveBtn.style.background = '#10b981';
                        
                        // Show warning if changes aren't persisted
                        if (result.message) {
                            alert(result.message);
                        }
                        
                        setTimeout(() => {
                            saveBtn.textContent = 'Save';
                            saveBtn.style.background = '#3b82f6';
                            saveBtn.disabled = false;
                        }, 2000);
                    } else {
                        throw new Error('Failed to save');
                    }
                } catch (error) {
                    saveBtn.textContent = 'Error!';
                    saveBtn.style.background = '#ef4444';
                    setTimeout(() => {
                        saveBtn.textContent = 'Save';
                        saveBtn.style.background = '#3b82f6';
                        saveBtn.disabled = false;
                    }, 2000);
                }
            }
            
            function loadQualityDashboard() {
                document.getElementById('content').innerHTML = \`
                    <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Quality Dashboard</h2>
                    <div class="card">
                        <h3 style="color: #f1f5f9; margin-bottom: 1rem;">AI-Powered Quality Improvements</h3>
                        <p style="color: #94a3b8;">Quality metrics and improvement analytics coming soon...</p>
                    </div>
                \`;
            }
            
            function loadPipelineMonitor() {
                document.getElementById('content').innerHTML = \`
                    <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Pipeline Monitor</h2>
                    <div class="card">
                        <h3 style="color: #f1f5f9; margin-bottom: 1rem;">n8n Workflow Integration</h3>
                        <p style="color: #94a3b8;">Pipeline monitoring integration coming soon...</p>
                        <div style="margin-top: 1rem;">
                            <button class="btn" onclick="window.open('http://localhost:5678', '_blank')">Open n8n Interface</button>
                        </div>
                    </div>
                \`;
            }
            
            function loadSettings() {
                document.getElementById('content').innerHTML = \`
                    <h2 style="color: #f1f5f9; margin-bottom: 2rem; font-size: 2rem;">Settings</h2>
                    <div class="card">
                        <h3 style="color: #f1f5f9; margin-bottom: 1rem;">Dashboard Configuration</h3>
                        <p style="color: #94a3b8;">Settings and configuration options coming soon...</p>
                    </div>
                \`;
            }
            
            // Load initial section
            loadSystemOverview();
        </script>
    </body>
    </html>
  `);
});

// Initialize connections and start server
async function startServer() {
  try {
    await pgPool.query('SELECT NOW()');
    console.log('✅ Connected to PostgreSQL database (dev_pipeline)');
    
    await redisClient.connect();
    await redisClient.ping();
    console.log('✅ Connected to Redis cache');
    
    const PORT = process.env.PORT || 8008;
    server.listen(PORT, '0.0.0.0', () => {
      console.log(`🚀 AI Pipeline Dashboard running on port ${PORT}`);
      console.log(`📊 Dashboard URL: http://localhost:${PORT}`);
      console.log(`🔗 Integrated with existing database: dev_pipeline`);
      console.log(`📁 Monitoring projects: ${Object.keys(SERVICES).length} services`);
    });
  } catch (error) {
    console.error('❌ Failed to start dashboard:', error);
    process.exit(1);
  }
}

startServer();