<?php
include "db.php";

/**
 * Σταθερό χρώμα ανά όνομα (για να μην αλλάζουν κάθε refresh)
 */
function colorFromName($name) {
    return '#' . substr(md5($name), 0, 6);
}

/**
 * Τελευταίοι 12 μήνες (YYYY-MM)
 */
$months = [];
for ($i = 11; $i >= 0; $i--) {
    $months[] = date('Y-m', strtotime("-$i months"));
}

/**
 * ---------------------------------------------------------
 *  Υλικά – ιστορικό τιμών με fill forward
 * ---------------------------------------------------------
 */
$ingredients = [];
$res_ing = $db->query("
    SELECT i.id, i.name, u.name AS unit_name
    FROM ingredients i
    LEFT JOIN units u ON u.id = i.unit_id
    ORDER BY i.name
");

while ($ing = $res_ing->fetch_assoc()) {
    $monthly_prices = [];
    $last_price = 0;

    foreach ($months as $m) {
        $month_end = $m . '-31';

        $stmt = $db->prepare("
            SELECT price
            FROM ingredient_prices
            WHERE ingredient_id = ?
              AND DATE(date) <= ?
            ORDER BY date DESC
            LIMIT 1
        ");
        $stmt->bind_param("is", $ing['id'], $month_end);
        $stmt->execute();
        $r = $stmt->get_result()->fetch_assoc();
        $stmt->close();

        if ($r && $r['price'] !== null) {
            $last_price = (float)$r['price'];
        }

        $monthly_prices[] = $last_price;
    }

    $ingredients[] = [
        'name'       => $ing['name'],
        'unit_name'  => $ing['unit_name'], // 👈 Προστέθηκε
        'prices'     => $monthly_prices,
        'color'      => colorFromName($ing['name'])
    ];
}

/**
 * ---------------------------------------------------------
 *  Προϊόντα – ιστορικό κόστους με fill forward
 * ---------------------------------------------------------
 */
$products = [];
$res_prod = $db->query("SELECT id, name FROM products ORDER BY name");

while ($prod = $res_prod->fetch_assoc()) {
    $monthly_costs = [];

    foreach ($months as $m) {
        $month_end = $m . '-31';
        $total = 0;

        $stmt = $db->prepare("
            SELECT pi.quantity, pi.ingredient_id
            FROM product_ingredients pi
            WHERE pi.product_id = ?
        ");
        $stmt->bind_param("i", $prod['id']);
        $stmt->execute();
        $ings = $stmt->get_result();

        while ($row = $ings->fetch_assoc()) {
            $stmt2 = $db->prepare("
                SELECT price
                FROM ingredient_prices
                WHERE ingredient_id = ?
                  AND DATE(date) <= ?
                ORDER BY date DESC
                LIMIT 1
            ");
            $stmt2->bind_param("is", $row['ingredient_id'], $month_end);
            $stmt2->execute();
            $pr = $stmt2->get_result()->fetch_assoc();
            $stmt2->close();

            // fill forward logic
            if ($pr && $pr['price'] !== null) {
                $price = (float)$pr['price'];
            } else {
                // προηγούμενη τιμή για το υλικό
                $stmt3 = $db->prepare("
                    SELECT price
                    FROM ingredient_prices
                    WHERE ingredient_id = ?
                    ORDER BY date ASC
                    LIMIT 1
                ");
                $stmt3->bind_param("i", $row['ingredient_id']);
                $stmt3->execute();
                $fallback = $stmt3->get_result()->fetch_assoc();
                $stmt3->close();

                $price = $fallback ? (float)$fallback['price'] : 0;
            }

            $total += $row['quantity'] * $price;
        }

        $stmt->close();
        $monthly_costs[] = round($total, 2);
    }

    $products[] = [
        'name'  => $prod['name'],
        'costs' => $monthly_costs,
        'color' => colorFromName($prod['name'])
    ];
}

/**
 * ---------------------------------------------------------
 *  Συνταγές – 12μηνο ιστορικό + Μ.Ο.
 * ---------------------------------------------------------
 */
$recipes_12m = [];
$res_prod->data_seek(0);
while ($prod = $res_prod->fetch_assoc()) {
    $monthly_costs = [];

    foreach ($months as $m) {
        $month_end = $m . '-31';
        $total = 0;

        $stmt = $db->prepare("
            SELECT pi.quantity, pi.ingredient_id
            FROM product_ingredients pi
            WHERE pi.product_id = ?
        ");
        $stmt->bind_param("i", $prod['id']);
        $stmt->execute();
        $ings = $stmt->get_result();

        while ($ing = $ings->fetch_assoc()) {
            $stmt2 = $db->prepare("
                SELECT price
                FROM ingredient_prices
                WHERE ingredient_id = ?
                  AND DATE(date) <= ?
                ORDER BY date DESC
                LIMIT 1
            ");
            $stmt2->bind_param("is", $ing['ingredient_id'], $month_end);
            $stmt2->execute();
            $pr = $stmt2->get_result()->fetch_assoc();
            $stmt2->close();

            if ($pr && $pr['price'] !== null) {
                $price = (float)$pr['price'];
            } else {
                $stmt3 = $db->prepare("
                    SELECT price
                    FROM ingredient_prices
                    WHERE ingredient_id = ?
                    ORDER BY date ASC
                    LIMIT 1
                ");
                $stmt3->bind_param("i", $ing['ingredient_id']);
                $stmt3->execute();
                $fallback = $stmt3->get_result()->fetch_assoc();
                $stmt3->close();

                $price = $fallback ? (float)$fallback['price'] : 0;
            }

            $total += $ing['quantity'] * $price;
        }

        $stmt->close();
        $monthly_costs[] = round($total, 2);
    }

    $avg12 = round(array_sum($monthly_costs)/12,2);
    $avg6  = round(array_sum(array_slice($monthly_costs,-6))/6,2);
    $avg3  = round(array_sum(array_slice($monthly_costs,-3))/3,2);
    $last  = round(end($monthly_costs),2);

    $recipes_12m[] = [
        'name'   => $prod['name'],
        'months' => $monthly_costs,
        'avg12'  => $avg12,
        'avg6'   => $avg6,
        'avg3'   => $avg3,
        'last'   => $last
    ];
}

/**
 * ---------------------------------------------------------
 *  JSON Output
 * ---------------------------------------------------------
 */
header('Content-Type: application/json');
echo json_encode([
    'months_12'   => $months,
    'ingredients' => $ingredients,
    'products'    => $products,
    'recipes_12m' => $recipes_12m
]);
