tilbudgivern/archive/analysis/analytics-api-server-direct.js

const express = require('express');
const cors = require('cors');
const mysql = require('mysql2/promise');

const app = express();
const PORT = process.env.ANALYTICS_PORT || 3001;

// Database connection
const dbConfig = {
    host: 'localhost',
    user: 'analytics_user',
    password: process.env.DB_PASSWORD,
    database: 'tilbudgivern'
};

// Middleware
app.use(cors());
app.use(express.json());

// Database connection helper
let connection = null;
async function getConnection() {
    if (!connection) {
        connection = await mysql.createConnection(dbConfig);
    }
    return connection;
}

// Health check
app.get('/health', (req, res) => {
    res.json({ status: 'OK', service: 'Analytics API', timestamp: new Date().toISOString() });
});

// ===================================
// ANALYTICS ENDPOINTS
// ===================================

// Overall KPIs
app.get('/api/analytics/kpis', async (req, res) => {
    try {
        const conn = await getConnection();
        
        const [kpiResults] = await conn.execute(`
            SELECT 
                COUNT(*) as total_projects,
                SUM(total_quote_value) as total_revenue,
                COUNT(DISTINCT customer_id) as active_customers,
                AVG(profit_margin) as avg_profit_margin,
                SUM(total_hours) as total_hours,
                AVG(total_quote_value) as avg_project_value
            FROM project_analytics
        `);
        
        // Count active employees (with hours in last 3 months) from actual timesheet data
        const [activeEmployeesResult] = await conn.execute(`
            SELECT COUNT(DISTINCT emp_id) as active_employees
            FROM ordrestyring_local.hours 
            WHERE FROM_UNIXTIME(created_at) >= DATE_SUB(NOW(), INTERVAL 3 MONTH)
              AND emp_id IS NOT NULL
        `);

        const kpi = kpiResults[0];
        const activeEmployees = activeEmployeesResult[0];
        
        res.json({
            success: true,
            data: {
                totalProjects: parseInt(kpi.total_projects) || 0,
                totalRevenue: parseFloat(kpi.total_revenue) || 0,
                activeCustomers: parseInt(kpi.active_customers) || 0,
                activeEmployees: parseInt(activeEmployees.active_employees) || 0,
                avgProfitMargin: parseFloat(kpi.avg_profit_margin) || 0,
                totalHours: parseFloat(kpi.total_hours) || 0,
                avgProjectValue: parseFloat(kpi.avg_project_value) || 0,
                lastUpdated: new Date().toISOString()
            }
        });
    } catch (error) {
        console.error('KPIs error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Project Analytics
app.get('/api/analytics/projects', async (req, res) => {
    try {
        const conn = await getConnection();
        const limit = parseInt(req.query.limit) || 50;
        const offset = parseInt(req.query.offset) || 0;
        
        const [projects] = await conn.execute(`
            SELECT 
                project_id,
                project_name,
                customer_name,
                employee_name,
                total_hours,
                total_quote_value,
                profit_margin,
                quote_accuracy_percentage,
                project_start_date,
                is_active
            FROM project_analytics
            ORDER BY total_quote_value DESC
            LIMIT ? OFFSET ?
        `, [limit, offset]);

        res.json({
            success: true,
            data: projects,
            pagination: { limit, offset, count: projects.length }
        });
    } catch (error) {
        console.error('Projects error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Single Project Details
app.get('/api/analytics/projects/:id', async (req, res) => {
    try {
        const conn = await getConnection();
        const projectId = req.params.id;
        
        const [project] = await conn.execute(`
            SELECT 
                project_id,
                project_name,
                customer_name,
                employee_name,
                total_hours,
                total_material_cost,
                total_labor_cost,
                total_quote_value,
                actual_cost,
                profit_amount,
                profit_margin,
                quote_accuracy_percentage,
                project_start_date,
                project_end_date,
                project_duration_days,
                is_active
            FROM project_analytics
            WHERE project_id = ?
        `, [projectId]);

        if (project.length === 0) {
            return res.status(404).json({ success: false, error: 'Project not found' });
        }

        res.json({
            success: true,
            data: project[0]
        });
    } catch (error) {
        console.error('Single project error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Employee Performance
app.get('/api/analytics/employees', async (req, res) => {
    try {
        const conn = await getConnection();
        
        const [employees] = await conn.execute(`
            SELECT 
                employee_id,
                employee_name,
                period_month,
                period_year,
                total_projects,
                total_revenue,
                total_profit,
                avg_profit_margin,
                total_hours_worked,
                revenue_per_hour
            FROM employee_performance_analytics
            WHERE period_year = YEAR(CURRENT_DATE) 
              AND period_month = MONTH(CURRENT_DATE)
            ORDER BY total_revenue DESC
        `);

        res.json({
            success: true,
            data: employees
        });
    } catch (error) {
        console.error('Employees error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Customer Analytics
app.get('/api/analytics/customers', async (req, res) => {
    try {
        const conn = await getConnection();
        
        const [customers] = await conn.execute(`
            SELECT 
                customer_id,
                customer_name,
                total_projects,
                total_revenue,
                avg_project_value,
                total_profit,
                avg_profit_margin,
                customer_tier,
                is_high_value,
                is_frequent,
                first_project_date,
                last_project_date
            FROM customer_analytics
            ORDER BY total_revenue DESC
            LIMIT 100
        `);

        res.json({
            success: true,
            data: customers
        });
    } catch (error) {
        console.error('Customers error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Executive Summary
app.get('/api/analytics/executive-summary', async (req, res) => {
    try {
        const conn = await getConnection();
        
        // Get overview stats
        const [overview] = await conn.execute(`
            SELECT 
                COUNT(*) as total_projects,
                SUM(total_quote_value) as total_revenue,
                AVG(profit_margin) as avg_profit_margin,
                COUNT(DISTINCT customer_id) as total_customers
            FROM project_analytics
        `);

        // Get top performers
        const [topEmployees] = await conn.execute(`
            SELECT employee_name, total_revenue, avg_profit_margin
            FROM employee_performance_analytics
            WHERE period_year = YEAR(CURRENT_DATE) 
            ORDER BY total_revenue DESC
            LIMIT 5
        `);

        // Get high value customers
        const [topCustomers] = await conn.execute(`
            SELECT customer_name, total_revenue, customer_tier
            FROM customer_analytics
            WHERE total_revenue > 0
            ORDER BY total_revenue DESC
            LIMIT 5
        `);

        const stats = overview[0];
        const businessHealth = stats.avg_profit_margin > 35 ? 'Excellent' : 
                             stats.avg_profit_margin > 25 ? 'Good' :
                             stats.avg_profit_margin > 15 ? 'Fair' : 'Needs Attention';

        res.json({
            success: true,
            data: {
                businessHealth,
                totalRevenue: parseFloat(stats.total_revenue) || 0,
                totalProjects: parseInt(stats.total_projects) || 0,
                avgProfitMargin: parseFloat(stats.avg_profit_margin) || 0,
                totalCustomers: parseInt(stats.total_customers) || 0,
                topPerformers: topEmployees,
                topCustomers: topCustomers,
                keyInsights: [
                    `${stats.total_projects} projekter analyseret`,
                    `${(stats.total_revenue / 1000000).toFixed(1)}M DKK i total revenue`,
                    `${stats.avg_profit_margin?.toFixed(1)}% gennemsnitlig profit margin`,
                    `${stats.total_customers} aktive kunder`
                ]
            }
        });
    } catch (error) {
        console.error('Executive summary error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Benchmarks
app.get('/api/analytics/benchmarks', async (req, res) => {
    try {
        const conn = await getConnection();
        
        const [benchmarks] = await conn.execute(`
            SELECT 
                metric_name,
                target_value,
                current_value,
                performance_score,
                variance_from_target,
                unit_of_measure,
                description
            FROM business_benchmarks
            ORDER BY performance_score DESC
        `);

        res.json({
            success: true,
            data: benchmarks
        });
    } catch (error) {
        console.error('Benchmarks error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Search Projects
app.get('/api/analytics/search', async (req, res) => {
    try {
        const conn = await getConnection();
        const query = req.query.q || '';
        const limit = parseInt(req.query.limit) || 20;
        
        if (!query) {
            return res.json({ success: true, data: [] });
        }
        
        const [results] = await conn.execute(`
            SELECT 
                project_id,
                project_name,
                customer_name,
                employee_name,
                total_quote_value,
                profit_margin,
                project_start_date
            FROM project_analytics
            WHERE project_name LIKE ? 
               OR customer_name LIKE ?
               OR employee_name LIKE ?
               OR project_id = ?
            ORDER BY total_quote_value DESC
            LIMIT ?
        `, [`%${query}%`, `%${query}%`, `%${query}%`, query, limit]);

        res.json({
            success: true,
            data: results
        });
    } catch (error) {
        console.error('Search error:', error);
        res.status(500).json({ success: false, error: error.message });
    }
});

// Start server
app.listen(PORT, () => {
    console.log(`🚀 Analytics API server running on port ${PORT}`);
    console.log(`📊 Available endpoints:`);
    console.log(`   GET /api/analytics/kpis - Overall KPIs`);
    console.log(`   GET /api/analytics/projects - Project list`);
    console.log(`   GET /api/analytics/projects/:id - Single project`);
    console.log(`   GET /api/analytics/employees - Employee performance`);
    console.log(`   GET /api/analytics/customers - Customer analytics`);
    console.log(`   GET /api/analytics/executive-summary - Executive dashboard`);
    console.log(`   GET /api/analytics/benchmarks - Performance benchmarks`);
    console.log(`   GET /api/analytics/search?q=term - Search projects`);
});

// Graceful shutdown
process.on('SIGTERM', async () => {
    console.log('Shutting down analytics API server...');
    if (connection) {
        await connection.end();
    }
    process.exit(0);
});