inventory/backend/api/alv_laskenta.php

<?php
header('Content-Type: application/json');
header('Access-Control-Allow-Origin: *');
header('Access-Control-Allow-Methods: GET, OPTIONS');
header('Access-Control-Allow-Headers: Content-Type, Authorization');

require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../models/AccountingEntry.php';

// Handle preflight requests
if ($_SERVER['REQUEST_METHOD'] === 'OPTIONS') {
    exit(0);
}

try {
    $database = new Database();
    $conn = $database->getConnection();
    
    if (!$conn) {
        throw new Exception('Database connection failed');
    }

    // Get query parameters
    $period = $_GET['period'] ?? 'current';
    $month = $_GET['month'] ?? null;
    $year = $_GET['year'] ?? null;
    $report_type = $_GET['report_type'] ?? 'standard'; // 'standard' or 'monthly_summary'
    
    // Determine date range based on period
    $dateConditions = getDateConditions($period, $month, $year);
    
    if ($report_type === 'monthly_summary') {
        // Generate monthly summary report
        error_log("Generating monthly summary for period: " . $period . " from " . $dateConditions['start_date'] . " to " . $dateConditions['end_date']);
        $monthlyData = generateMonthlyVATSummary($conn, $dateConditions);
        error_log("Monthly summary data: " . json_encode($monthlyData));
        
        echo json_encode([
            'success' => true,
            'data' => [
                'report_type' => 'monthly_summary',
                'period' => $period,
                'date_range' => $dateConditions,
                'monthly_summary' => $monthlyData
            ]
        ]);
    } else {
        // Standard VAT calculation
        // Fetch accounting entries for VAT calculation
        $sql = "SELECT 
                    ae.*,
                    ac.category_name,
                    ac.category_type,
                    ac.vat_percentage as category_vat
                FROM accounting_entries ae
                LEFT JOIN accounting_categories ac ON ae.category = ac.category_code
                WHERE ae.entry_date BETWEEN :start_date AND :end_date
                ORDER BY ae.entry_date";
        
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':start_date', $dateConditions['start_date']);
        $stmt->bindParam(':end_date', $dateConditions['end_date']);
        $stmt->execute();
        
        $entries = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        // Calculate VAT breakdown
        $vatBreakdown = calculateVATBreakdown($entries);
        
        // Get previous period data for comparison
        $previousDateConditions = getPreviousDateConditions($period, $month, $year);
        $previousData = getPreviousPeriodVATData($conn, $previousDateConditions);
        
        // Combine current and previous data
        $result = [
            'period' => $period,
            'date_range' => $dateConditions,
            'vat_breakdown' => $vatBreakdown,
            'previous_period' => $previousData,
            'totals' => calculateVATTotals($vatBreakdown)
        ];
        
        echo json_encode([
            'success' => true,
            'data' => $result
        ]);
    }
    
} catch (Exception $e) {
    http_response_code(500);
    echo json_encode([
        'success' => false,
        'message' => $e->getMessage()
    ]);
}

function getDateConditions($period, $month, $year) {
    $currentYear = $year ?: date('Y');
    $currentMonth = $month ? date('m', strtotime($month)) : date('m');
    
    switch ($period) {
        case 'month':
            return [
                'start_date' => "$currentYear-$currentMonth-01",
                'end_date' => date('Y-m-t', strtotime("$currentYear-$currentMonth-01"))
            ];
        case 'quarter':
            $quarter = isset($_GET['quarter']) ? intval($_GET['quarter']) : ceil($currentMonth / 3);
            $startMonth = ($quarter - 1) * 3 + 1;
            $endMonth = $quarter * 3;
            return [
                'start_date' => "$currentYear-" . str_pad($startMonth, 2, '0', STR_PAD_LEFT) . "-01",
                'end_date' => "$currentYear-" . str_pad($endMonth, 2, '0', STR_PAD_LEFT) . "-" . date('t', strtotime("$currentYear-$endMonth-01"))
            ];
        case 'year':
            return [
                'start_date' => "$currentYear-01-01",
                'end_date' => "$currentYear-12-31"
            ];
        case 'current':
        default:
            return [
                'start_date' => date('Y-01-01'),
                'end_date' => date('Y-m-d')
            ];
    }
}

function getPreviousDateConditions($period, $month, $year) {
    $currentYear = $year ?: date('Y');
    $currentMonth = $month ? date('m', strtotime($month)) : date('m');
    
    switch ($period) {
        case 'month':
            $previousDate = date('Y-m', strtotime("$currentYear-$currentMonth-01 -1 month"));
            $prevYear = date('Y', strtotime($previousDate));
            $prevMonth = date('m', strtotime($previousDate));
            return [
                'start_date' => "$prevYear-$prevMonth-01",
                'end_date' => "$prevYear-$prevMonth-" . date('t', strtotime("$prevYear-$prevMonth-01"))
            ];
        case 'quarter':
            $quarter = ceil($currentMonth / 3);
            if ($quarter == 1) {
                $prevYear = $currentYear - 1;
                $prevQuarter = 4;
            } else {
                $prevYear = $currentYear;
                $prevQuarter = $quarter - 1;
            }
            $startMonth = ($prevQuarter - 1) * 3 + 1;
            $endMonth = $prevQuarter * 3;
            return [
                'start_date' => "$prevYear-" . str_pad($startMonth, 2, '0', STR_PAD_LEFT) . "-01",
                'end_date' => "$prevYear-" . str_pad($endMonth, 2, '0', STR_PAD_LEFT) . "-" . date('t', strtotime("$prevYear-$endMonth-01"))
            ];
        case 'year':
            return [
                'start_date' => ($currentYear - 1) . "-01-01",
                'end_date' => ($currentYear - 1) . "-12-31"
            ];
        case 'current':
        default:
            return [
                'start_date' => (date('Y') - 1) . "-01-01",
                'end_date' => (date('Y') - 1) . "-12-31"
            ];
    }
}

function calculateVATBreakdown($entries) {
    $vatData = [
        '0' => [
            'taxable_sales' => 0,
            'taxable_purchases' => 0,
            'payable_vat' => 0,
            'deductible_vat' => 0,
            'net_vat' => 0
        ],
        '10' => [
            'taxable_sales' => 0,
            'taxable_purchases' => 0,
            'payable_vat' => 0,
            'deductible_vat' => 0,
            'net_vat' => 0
        ],
        '14' => [
            'taxable_sales' => 0,
            'taxable_purchases' => 0,
            'payable_vat' => 0,
            'deductible_vat' => 0,
            'net_vat' => 0
        ],
        '24' => [
            'taxable_sales' => 0,
            'taxable_purchases' => 0,
            'payable_vat' => 0,
            'deductible_vat' => 0,
            'net_vat' => 0
        ],
        '25.5' => [
            'taxable_sales' => 0,
            'taxable_purchases' => 0,
            'payable_vat' => 0,
            'deductible_vat' => 0,
            'net_vat' => 0
        ]
    ];
    
    foreach ($entries as $entry) {
        $vatRate = floatval($entry['vat_percentage']);
        $vatKey = normalizeVatRate($vatRate);
        
        if (!isset($vatData[$vatKey])) {
            $vatData[$vatKey] = [
                'taxable_sales' => 0,
                'taxable_purchases' => 0,
                'payable_vat' => 0,
                'deductible_vat' => 0,
                'net_vat' => 0
            ];
        }
        
        $taxFreeAmount = floatval($entry['tax_free_amount']);
        $vatAmount = floatval($entry['vat_amount']);
        
        if ($entry['entry_type'] === 'Tulo') {
            // Sales - VAT is payable
            $vatData[$vatKey]['taxable_sales'] += $taxFreeAmount;
            $vatData[$vatKey]['payable_vat'] += $vatAmount;
        } else {
            // Purchases - VAT is deductible
            $vatData[$vatKey]['taxable_purchases'] += $taxFreeAmount;
            $vatData[$vatKey]['deductible_vat'] += $vatAmount;
        }
    }
    
    // Calculate net VAT for each rate
    foreach ($vatData as $rate => &$data) {
        $data['net_vat'] = $data['payable_vat'] - $data['deductible_vat'];
    }
    
    return $vatData;
}

function normalizeVatRate($rate) {
    // Normalize VAT rates to standard keys
    if ($rate == 0) return '0';
    if ($rate == 10) return '10';
    if ($rate == 14) return '14';
    if ($rate == 24) return '24';
    if ($rate == 25.5) return '25.5';
    
    // Handle edge cases
    if ($rate > 24 && $rate <= 26) return '25.5';
    if ($rate > 14 && $rate <= 15) return '14';
    if ($rate > 9 && $rate <= 11) return '10';
    
    return '0'; // Default to 0% if unknown
}

function calculateVATTotals($vatBreakdown) {
    $totals = [
        'taxable_sales' => 0,
        'taxable_purchases' => 0,
        'payable_vat' => 0,
        'deductible_vat' => 0,
        'net_vat' => 0
    ];
    
    foreach ($vatBreakdown as $data) {
        $totals['taxable_sales'] += $data['taxable_sales'];
        $totals['taxable_purchases'] += $data['taxable_purchases'];
        $totals['payable_vat'] += $data['payable_vat'];
        $totals['deductible_vat'] += $data['deductible_vat'];
        $totals['net_vat'] += $data['net_vat'];
    }
    
    return $totals;
}

function getPreviousPeriodVATData($conn, $dateConditions) {
    $sql = "SELECT 
                ae.*,
                ac.category_name,
                ac.category_type,
                ac.vat_percentage as category_vat
            FROM accounting_entries ae
            LEFT JOIN accounting_categories ac ON ae.category = ac.category_code
            WHERE ae.entry_date BETWEEN :start_date AND :end_date
            ORDER BY ae.entry_date";
    
    $stmt = $conn->prepare($sql);
    $stmt->bindParam(':start_date', $dateConditions['start_date']);
    $stmt->bindParam(':end_date', $dateConditions['end_date']);
    $stmt->execute();
    
    $entries = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return calculateVATBreakdown($entries);
}

function generateMonthlyVATSummary($conn, $dateConditions) {
    $monthlySummary = [];
    
    // Get all months within the date range
    $start = new DateTime($dateConditions['start_date']);
    $end = new DateTime($dateConditions['end_date']);
    $interval = new DateInterval('P1M');
    $period = new DatePeriod($start, $interval, $end->modify('last day of this month'));
    
    foreach ($period as $month) {
        $monthStart = $month->format('Y-m-01');
        $monthEnd = $month->format('Y-m-t');
        
        // Fetch accounting entries for this month
        $sql = "SELECT 
                    ae.*,
                    ac.category_name,
                    ac.category_type,
                    ac.vat_percentage as category_vat
                FROM accounting_entries ae
                LEFT JOIN accounting_categories ac ON ae.category = ac.category_code
                WHERE ae.entry_date BETWEEN :start_date AND :end_date
                ORDER BY ae.entry_date";
        
        $stmt = $conn->prepare($sql);
        $stmt->bindParam(':start_date', $monthStart);
        $stmt->bindParam(':end_date', $monthEnd);
        $stmt->execute();
        
        $entries = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $vatBreakdown = calculateVATBreakdown($entries);
        $totals = calculateVATTotals($vatBreakdown);
        
        // Finnish month names
        $finnishMonths = [
            'January' => 'Tammikuu',
            'February' => 'Helmikuu',
            'March' => 'Maaliskuu',
            'April' => 'Huhtikuu',
            'May' => 'Toukokuu',
            'June' => 'Kesäkuu',
            'July' => 'Heinäkuu',
            'August' => 'Elokuu',
            'September' => 'Syyskuu',
            'October' => 'Lokakuu',
            'November' => 'Marraskuu',
            'December' => 'Joulukuu'
        ];
        
        $englishMonthName = $month->format('F');
        $finnishMonthName = $finnishMonths[$englishMonthName] ?? $englishMonthName;
        
        $monthlySummary[] = [
            'month' => $month->format('Y-m'),
            'month_name' => $finnishMonthName . ' ' . $month->format('Y'),
            'payable' => $totals['payable_vat'] ?? 0,
            'deductible' => $totals['deductible_vat'] ?? 0,
            'net' => $totals['net_vat'] ?? 0,
            'turnover' => $totals['taxable_sales'] ?? 0,
            'vat_breakdown' => $vatBreakdown
        ];
    }
    
    return $monthlySummary;
}
?>