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;
    
    // Determine date range based on period
    $dateConditions = getDateConditions($period, $month, $year);
    
    // 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 = 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);
}
?>