buchhaltung/backend/routes/dashboard.js

const express = require('express');
const { Pool } = require('pg');

const router = express.Router();

// Database pool
const pool = new Pool({
  host: process.env.DB_HOST || 'db',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'steuer',
  user: process.env.DB_USER || 'app',
  password: process.env.DB_PASSWORD || 'app123',
});

// GET /api/dashboard/summary
router.get('/summary', async (req, res) => {
  try {
    const currentYear = new Date().getFullYear();
    
    // Gesamtübersichten parallel abfragen
    const [
      rechnungenResult,
      belegeResult,
      krediteResult,
      stundenResult,
      kundenResult
    ] = await Promise.all([
      // Rechnungen dieses Jahr
      pool.query(`
        SELECT 
          COUNT(*) as total_count,
          COALESCE(SUM(CASE WHEN status = 'bezahlt' THEN betrag ELSE 0 END), 0) as paid_total,
          COALESCE(SUM(CASE WHEN status = 'offen' THEN betrag ELSE 0 END), 0) as open_total,
          COALESCE(SUM(betrag), 0) as total
        FROM rechnungen 
        WHERE EXTRACT(YEAR FROM datum) = $1
      `, [currentYear]),
      
      // Belege/Ausgaben dieses Jahr
      pool.query(`
        SELECT 
          COUNT(*) as total_count,
          COALESCE(SUM(betrag), 0) as total
        FROM belege 
        WHERE EXTRACT(YEAR FROM date) = $1
      `, [currentYear]),
      
      // Kredite
      pool.query(`
        SELECT 
          COUNT(*) as total_count,
          COUNT(CASE WHEN status = 'aktiv' THEN 1 END) as active_count,
          COALESCE(SUM(CASE WHEN status = 'aktiv' THEN restschuld ELSE 0 END), 0) as total_restschuld,
          COALESCE(SUM(CASE WHEN status = 'aktiv' THEN monatsrate ELSE 0 END), 0) as total_rate
        FROM kredite
      `),
      
      // Stunden dieses Jahr
      pool.query(`
        SELECT 
          COUNT(*) as total_count,
          COALESCE(SUM(stunden), 0) as total_stunden,
          COALESCE(SUM(betrag), 0) as total_umsatz
        FROM stunden 
        WHERE EXTRACT(YEAR FROM datum) = $1
      `, [currentYear]),
      
      // Kunden
      pool.query(`
        SELECT COUNT(*) as total_count FROM kunden
      `)
    ]);
    
    const rechnungen = rechnungenResult.rows[0];
    const belege = belegeResult.rows[0];
    const kredite = krediteResult.rows[0];
    const stunden = stundenResult.rows[0];
    const kunden = kundenResult.rows[0];
    
    res.json({
      success: true,
      year: currentYear,
      summary: {
        einnahmen: {
          total: parseFloat(rechnungen.total) || 0,
          paid: parseFloat(rechnungen.paid_total) || 0,
          open: parseFloat(rechnungen.open_total) || 0,
          count: parseInt(rechnungen.total_count) || 0
        },
        ausgaben: {
          total: parseFloat(belege.total) || 0,
          count: parseInt(belege.total_count) || 0
        },
        kredite: {
          total: parseInt(kredite.total_count) || 0,
          active: parseInt(kredite.active_count) || 0,
          restschuld: parseFloat(kredite.total_restschuld) || 0,
          monatsrate: parseFloat(kredite.total_rate) || 0
        },
        stunden: {
          total: parseFloat(stunden.total_stunden) || 0,
          umsatz: parseFloat(stunden.total_umsatz) || 0,
          count: parseInt(stunden.total_count) || 0
        },
        kunden: {
          total: parseInt(kunden.total_count) || 0
        },
        gewinn: (parseFloat(rechnungen.total) || 0) - (parseFloat(belege.total) || 0)
      }
    });
  } catch (error) {
    console.error('Dashboard Summary Error:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// GET /api/dashboard/cashflow
router.get('/cashflow', async (req, res) => {
  try {
    const { months = 6 } = req.query;
    
    // Monatliche Cashflow-Daten
    const cashflowData = [];
    
    for (let i = months - 1; i >= 0; i--) {
      const date = new Date();
      date.setMonth(date.getMonth() - i);
      const year = date.getFullYear();
      const month = date.getMonth() + 1;
      
      const [einnahmenResult, ausgabenResult] = await Promise.all([
        pool.query(`
          SELECT COALESCE(SUM(betrag), 0) as total 
          FROM rechnungen 
          WHERE EXTRACT(YEAR FROM datum) = $1 AND EXTRACT(MONTH FROM datum) = $2
          AND status = 'bezahlt'
        `, [year, month]),
        pool.query(`
          SELECT COALESCE(SUM(betrag), 0) as total 
          FROM belege 
          WHERE EXTRACT(YEAR FROM date) = $1 AND EXTRACT(MONTH FROM date) = $2
        `, [year, month])
      ]);
      
      const einnahmen = parseFloat(einnahmenResult.rows[0].total) || 0;
      const ausgaben = parseFloat(ausgabenResult.rows[0].total) || 0;
      
      cashflowData.push({
        year,
        month,
        monthName: date.toLocaleString('de-DE', { month: 'short' }),
        einnahmen,
        ausgaben,
        saldo: einnahmen - ausgaben
      });
    }
    
    res.json({
      success: true,
      cashflow: cashflowData
    });
  } catch (error) {
    console.error('Dashboard Cashflow Error:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

// GET /api/dashboard/tax-preview
router.get('/tax-preview', async (req, res) => {
  try {
    const { year = new Date().getFullYear() } = req.query;
    
    const [einnahmenResult, ausgabenResult, stundenResult] = await Promise.all([
      pool.query(`
        SELECT COALESCE(SUM(betrag), 0) as total 
        FROM rechnungen 
        WHERE EXTRACT(YEAR FROM datum) = $1 AND status = 'bezahlt'
      `, [year]),
      pool.query(`
        SELECT COALESCE(SUM(betrag), 0) as total 
        FROM belege 
        WHERE EXTRACT(YEAR FROM date) = $1
      `, [year]),
      pool.query(`
        SELECT COALESCE(SUM(betrag), 0) as total 
        FROM stunden 
        WHERE EXTRACT(YEAR FROM datum) = $1 AND status = 'bezahlt'
      `, [year])
    ]);
    
    const einnahmen = parseFloat(einnahmenResult.rows[0].total) || 0;
    const ausgaben = parseFloat(ausgabenResult.rows[0].total) || 0;
    const stundenEinnahmen = parseFloat(stundenResult.rows[0].total) || 0;
    const gesamtEinnahmen = einnahmen + stundenEinnahmen;
    const gewinn = gesamtEinnahmen - ausgaben;
    
    // Vereinfachte Steuerberechnung (nür Hinweis)
    const estVorschuss = gewinn > 0 ? gewinn * 0.25 : 0; // ca. 25% ESt
    
    res.json({
      success: true,
      year: parseInt(year),
      preview: {
        einnahmen: {
          rechnungen: einnahmen,
          stunden: stundenEinnahmen,
          gesamt: gesamtEinnahmen
        },
        ausgaben,
        gewinn,
        estVorschuss: Math.max(0, estVorschuss)
      }
    });
  } catch (error) {
    console.error('Dashboard Tax Preview Error:', error);
    res.status(500).json({ success: false, error: error.message });
  }
});

module.exports = router;