<?php
header("Access-Control-Allow-Origin: *"); // allow all domains (or specify yours)
header("Access-Control-Allow-Methods: POST, GET, OPTIONS");
header("Access-Control-Allow-Headers: Content-Type, Authorization");
header("Content-Type: application/json");
include_once 'config/config.php';

$input = json_decode(file_get_contents("php://input"), true);
//$input = $_REQUEST;
if (!isset($input['action'])) {
    echo json_encode(['status' => 'error', 'message' => 'No action specified']);
    exit;
}

$action = $input['action'];
if ($action == "short_term_forecast") {

    $data = $input;

    // ✅ Check only mandatory fields
    if (
        !$data ||
        !isset($data["forecast_product_id"], $data["short_term"])
    ) {
        echo json_encode(["status" => "error", "message" => "Invalid or missing parameters"]);
        exit;
    }

    // ✅ Default execution_month and execution_year if not provided
    $executionMonth = $data["execution_month"] ?? date("m");
    $executionYear  = $data["execution_year"] ?? date("Y");

    // ✅ Generate execution_fy if not provided
    if (empty($data["execution_fy"])) {
        $data["execution_fy"] = getFinancialYear($executionMonth, $executionYear);
    }

    // ✅ Replace with defaulted values
    $data["execution_month"] = $executionMonth;
    $data["execution_year"]  = $executionYear;

    $validation = validateShortTermMonths(
        (int)$data["execution_month"],
        (int)$data["execution_year"],
        $data["short_term"]
    );

    if ($validation !== true) {
        echo json_encode(["status" => "error", "message" => $validation]);
        exit;
    }

    try {
        $conn->beginTransaction();

        $stmtInsert = $conn->prepare("
            INSERT INTO forecast_terms_data
            (forecast_product_id, execution_month, execution_year, execution_fy, term_type, period_label, period_year, period_fy, value, min_value, max_value, percent_change)
            VALUES (:forecast_product_id, :execution_month, :execution_year, :execution_fy, 'SHORT', :period_label, :period_year, :period_fy, :value, :min_value, :max_value, :percent_change)
        ");

        $stmtSelect = $conn->prepare("
            SELECT id, value 
            FROM forecast_terms_data 
            WHERE forecast_product_id = :forecast_product_id
            AND term_type = 'SHORT'
            AND period_label = :period_label
            AND period_year = :period_year
        ");


        $stmtDelete = $conn->prepare("DELETE FROM forecast_terms_data WHERE id = :id");

        $stmtSelectProduct = $conn->prepare("
            SELECT id, accuracy_range_1m, accuracy_range_2m, accuracy_range_3m 
            FROM forecast_products 
            WHERE id = :forecast_product_id
        ");

        $stmtSelectProduct->execute([":forecast_product_id" => $data["forecast_product_id"]]);
        $product = $stmtSelectProduct->fetch(PDO::FETCH_ASSOC);

        foreach ($data["short_term"] as $index => $term) {
            // Convert month name to number
            $monthName = $term["month"] ?? '';
            $month = monthNameToNumber($monthName);

            if ($month === null || $month < 1 || $month > 12) {
                throw new Exception("Invalid period_label (month). Must be a valid month name (January–December). Given: $monthName");
            }

            // Calculate FY if missing
            $targetYear = (int) ($term["year"] ?? $data["execution_year"]);
            $periodFy = $term["fy"] ?? getFinancialYear($month, $targetYear);

            // Check if a row exists
            $stmtSelect->execute([
                ":forecast_product_id" => $data["forecast_product_id"],
                ":period_label" => $month,
                ":period_year" => $targetYear
            ]);
            $existing = $stmtSelect->fetch(PDO::FETCH_ASSOC);

            $percentChange = null;
            if ($existing) {
                $oldValue = floatval($existing["value"] ?? 0);
                $newValue = floatval($term["value"] ?? 0);
                if ($oldValue != 0) {
                    $percentChange = calculateChange($oldValue, $newValue);
                }
                $stmtDelete->execute([":id" => $existing["id"]]);
            }

            // --- Pick accuracy range based on index (0 = 1m, 1 = 2m, 2 = 3m) ---
            if ($index === 0) {
                $accuracyRange = floatval($product["accuracy_range_1m"] ?? 0);
            } elseif ($index === 1) {
                $accuracyRange = floatval($product["accuracy_range_2m"] ?? 0);
            } else {
                $accuracyRange = floatval($product["accuracy_range_3m"] ?? 0);
            }

            $value = floatval($term["value"] ?? 0);
            $minValue = $value - (($accuracyRange / 100) * $value);
            $maxValue = $value + (($accuracyRange / 100) * $value);

            // Insert new row
            $stmtInsert->execute([
                ":forecast_product_id" => $data["forecast_product_id"],
                ":execution_month" => $data["execution_month"],
                ":execution_year" => $data["execution_year"],
                ":execution_fy" => $data["execution_fy"],
                ":period_label" => $month,
                ":period_year" => $targetYear,
                ":period_fy" => $periodFy,
                ":value" => $term["value"] ?? null,
                ":min_value" => $minValue ?? $term["min_value"] ?? null,
                ":max_value" => $maxValue ?? $term["max_value"] ?? null,
                ":percent_change" => $percentChange
            ]);
        }

        $conn->commit();

        //update Rationale
        manageRationale($data);

        // Update latest summary
        refreshForecastLatestData($data["forecast_product_id"], 'short_term');

        echo json_encode(["status" => "success", "message" => "Short term forecast data saved"]);
    } catch (Exception $e) {
        if ($conn->inTransaction()) {
            $conn->rollBack();
        }
        echo json_encode([
            "status"  => "error",
            "message" => $e->getMessage()
        ]);
    }
    die();
}

if ($action == "medium_term_forecast") {

    $data = $input;

    if (
        !$data ||
        !isset(
            $data["forecast_product_id"],
            $data["medium_term"]
        )
    ) {
        echo json_encode(["status" => "error", "message" => "Invalid or missing parameters"]);
        exit;
    }

    // ✅ Default execution_month and execution_year if not provided
    $executionMonth = $data["execution_month"] ?? date("m");
    $executionYear  = $data["execution_year"] ?? date("Y");

    // ✅ Generate execution_fy if not provided
    if (empty($data["execution_fy"])) {
        $data["execution_fy"] = getFinancialYear($executionMonth, $executionYear);
    }

    // ✅ Replace with defaulted values
    $data["execution_month"] = $executionMonth;
    $data["execution_year"]  = $executionYear;

    try {
        $conn->beginTransaction();

        $stmtInsert = $conn->prepare("
            INSERT INTO forecast_terms_data
            (forecast_product_id, execution_month, execution_year, execution_fy, term_type, period_label, period_year, period_fy, value, percent_change)
            VALUES (:forecast_product_id, :execution_month, :execution_year, :execution_fy, :term_type, :period_label, :period_year, :period_fy, :value, :percent_change)
        ");

        $stmtSelect = $conn->prepare("
            SELECT id, value
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
            AND term_type = :term_type
            AND period_label = :period_label
            AND period_fy = :period_fy
        ");

        $stmtDelete = $conn->prepare("DELETE FROM forecast_terms_data WHERE id = :id");

        $forecastProductId = $data["forecast_product_id"];
        $executionMonth    = $data["execution_month"];
        $executionYear     = $data["execution_year"];
        $executionFy       = $data["execution_fy"];

        // --- Handle Quarter ---
        if (!empty($data["medium_term"]["quarter"])) {
            $term  = $data["medium_term"]["quarter"];

            $label = $term["label"] ?? null;
            $fy    = $term["fy"] ?? null;
            $periodYear = getPeriodYear($fy, $label, "MEDIUM_QUARTER");

            $stmtSelect->execute([
                ":fpid"         => $forecastProductId,
                ":term_type"    => "MEDIUM_QUARTER",
                ":period_label" => $label,
                ":period_fy"  => $fy
            ]);

            $existing = $stmtSelect->fetch(PDO::FETCH_ASSOC);
            $percentChange = null;

            if ($existing) {
                $oldValue = floatval($existing["value"]);
                $newValue = floatval($term["value"] ?? 0);

                if ($oldValue != 0) {
                    $percentChange = calculateChange($oldValue, $newValue);
                }

                $stmtDelete->execute([":id" => $existing["id"]]);
            }

            $stmtInsert->execute([
                ":forecast_product_id" => $forecastProductId,
                ":execution_month"     => $executionMonth,
                ":execution_year"      => $executionYear,
                ":execution_fy"        => $executionFy,
                ":term_type"           => "MEDIUM_QUARTER",
                ":period_label"        => $label,
                ":period_year"         => $periodYear,
                ":period_fy"           => $fy,
                ":value"               => $term["value"] ?? null,
                ":percent_change"      => $percentChange
            ]);
        }

        // --- Handle Half-Year ---
        if (!empty($data["medium_term"]["half"])) {
            $term  = $data["medium_term"]["half"];
            $label = $term["label"] ?? null;
            $fy    = $term["fy"] ?? null;
            $periodYear = getPeriodYear($fy, $label, "MEDIUM_HALF");


            $stmtSelect->execute([
                ":fpid"         => $forecastProductId,
                ":term_type"    => "MEDIUM_HALF",
                ":period_label" => $label,
                ":period_fy"  => $fy
            ]);

            $existing = $stmtSelect->fetch(PDO::FETCH_ASSOC);
            $percentChange = null;

            if ($existing) {
                $oldValue = floatval($existing["value"]);
                $newValue = floatval($term["value"] ?? 0);

                if ($oldValue != 0) {
                    $percentChange = calculateChange($oldValue, $newValue);
                }

                $stmtDelete->execute([":id" => $existing["id"]]);
            }

            $stmtInsert->execute([
                ":forecast_product_id" => $forecastProductId,
                ":execution_month"     => $executionMonth,
                ":execution_year"      => $executionYear,
                ":execution_fy"        => $executionFy,
                ":term_type"           => "MEDIUM_HALF",
                ":period_label"        => $label,
                ":period_year"         => $periodYear,
                ":period_fy"           => $fy,
                ":value"               => $term["value"] ?? null,
                ":percent_change"      => $percentChange
            ]);
        }

        $conn->commit();

        //update Rationale
        manageRationale($data);

        // Update latest summary
        refreshForecastLatestData($forecastProductId, 'medium_term');

        echo json_encode(["status" => "success", "message" => "Medium-term forecast data saved"]);
    } catch (Exception $e) {
        if ($conn->inTransaction()) {
            $conn->rollBack();
        }
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
    die();
}


if ($action == "long_term_forecast") {

    $data = $input;

    if (
        !$data ||
        !isset($data["forecast_product_id"], $data["long_term"])
    ) {
        echo json_encode(["status" => "error", "message" => "Invalid or missing parameters"]);
        exit;
    }

    $executionMonth = $data["execution_month"] ?? date("m");
    $executionYear  = $data["execution_year"] ?? date("Y");

    if (empty($data["execution_fy"])) {
        $data["execution_fy"] = getFinancialYear($executionMonth, $executionYear);
    }

    $data["execution_month"] = $executionMonth;
    $data["execution_year"]  = $executionYear;

    try {
        $conn->beginTransaction();

        $stmtInsert = $conn->prepare("
            INSERT INTO forecast_terms_data
            (forecast_product_id, execution_month, execution_year, execution_fy, term_type, period_label, period_fy, value, percent_change)
            VALUES (:forecast_product_id, :execution_month, :execution_year, :execution_fy, 'LONG', :period_label, :period_fy, :value, :percent_change)
        ");

        $stmtSelect = $conn->prepare("
            SELECT id, value
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
            AND term_type = 'LONG'
            AND period_fy = :period_fy
        ");

        $stmtDelete = $conn->prepare("DELETE FROM forecast_terms_data WHERE id = :id");

        $forecastProductId = $data["forecast_product_id"];

        if (count($data["long_term"]) !== 1) {
            throw new Exception("Only one LONG-term entry is allowed.");
        }

        $term = $data["long_term"][0];
        $periodFy = $term["fy"] ?? null;

        if (!$periodFy) {
            throw new Exception("Missing period_fy in LONG-term entry.");
        }

        $periodLabel = $periodFy; // can also be same as period_fy

        // Check for existing record
        $stmtSelect->execute([
            ":fpid" => $forecastProductId,
            ":period_fy" => $periodFy
        ]);

        $existing = $stmtSelect->fetch(PDO::FETCH_ASSOC);
        $percentChange = null;

        if ($existing) {
            $oldValue = floatval($existing["value"]);
            $newValue = floatval($term["value"] ?? 0);

            if ($oldValue != 0) {
                $percentChange = calculateChange($oldValue, $newValue);
            }

            $stmtDelete->execute([":id" => $existing["id"]]);
        }

        // Insert new row
        $stmtInsert->execute([
            ":forecast_product_id" => $forecastProductId,
            ":execution_month"     => $executionMonth,
            ":execution_year"      => $executionYear,
            ":execution_fy"        => $data["execution_fy"],
            ":period_label"        => $periodLabel,
            ":period_fy"           => $periodFy,
            ":value"               => $term["value"] ?? null,
            ":percent_change"      => $percentChange
        ]);

        $conn->commit();

        manageRationale($data);
        refreshForecastLatestData($forecastProductId, 'long_term');

        echo json_encode(["status" => "success", "message" => "Long-term forecast data saved"]);
    } catch (Exception $e) {
        if ($conn->inTransaction()) {
            $conn->rollBack();
        }
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
    die();
}



if ($action == "get_forecast_terms") {

    try {

        // get current timeline info
        $currentMonth   = (int)date("n");   // 1–12
        $nextMonth      = $currentMonth == 12 ? 1 : $currentMonth + 1;
        $currentYear    = (int)date("Y");

        // Determine FY base year
        $fyBaseYear = $currentMonth >= 4 ? $currentYear : $currentYear - 1;

        // Shift months so April = 1, May = 2, …, Mar = 12
        $shiftedMonth = ($currentMonth - 4 + 12) % 12 + 1;
        $quarterNum   = (int)ceil($shiftedMonth / 3);

        // Quarter label → e.g., Q1FY25
        $currentQuarter = 'Q' . $quarterNum;


        // Half year label → H1FY25 or H2FY25
        $currentHalf = ($quarterNum <= 2 ? 'H1' : 'H2');


        // Current FY → FY25 (Apr 2024 – Mar 2025)
        $currentFY = 'FY' . (($fyBaseYear + 1) % 100);

        // Long-term FY → FY26
        $nextFY = 'FY' . (($fyBaseYear + 2) % 100);

        $stmt = $conn->prepare("
            SELECT 
                fp.id AS product_id,
                fp.forecast_name AS product_name,
                fp.f_market AS market,

                -- Short term
                fl.short_term_updated_on,
                fl.short_term_lable1, fl.short_term_change1,fl.short_term_lable1_fy,
                fl.short_term_lable2, fl.short_term_change2,fl.short_term_lable2_fy,
                fl.short_term_lable3, fl.short_term_change3,fl.short_term_lable3_fy,

                -- Medium term
                fl.medium_term_updated_on,
                fl.medium_term_quarter_lable, fl.medium_term_quarter_change,fl.medium_term_quarter_lable_fy,
                fl.medium_term_halfyearly_lable, fl.medium_term_halfyearly_change,fl.medium_term_halfyearly_lable_fy,

                -- Long term
                fl.long_term_updated_on,
                fl.long_term_lable, fl.long_term_change

            FROM forecast_products fp
            LEFT JOIN forecast_latest_data fl 
                ON fl.forecast_product_id = fp.id
            ORDER BY 
                (fl.forecast_product_id IS NULL),
                fp.f_market,
                fp.forecast_name
        ");
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $grouped = [];

        foreach ($rows as $row) {
            // Split f_market by comma and trim spaces
            $markets = array_map('trim', explode(',', $row["market"] ?? 'Unknown'));

            foreach ($markets as $market) {
                $grouped[$market][] = [
                    "product" => $row["product_name"],
                    "product_id" => $row["product_id"],

                    // Short Term
                    "short_term"            => formatChange([$row["short_term_change1"] ?? 0]),
                    "short_term_updated_on" => $row["short_term_updated_on"],

                    // Medium Term
                    "medium_term"            => formatChange([$row["medium_term_quarter_change"] ?? 0]),
                    "medium_term_updated_on" => $row["medium_term_updated_on"],

                    // Long Term
                    "long_term"            => formatChange([$row["long_term_change"] ?? 0]),
                    "long_term_updated_on" => $row["long_term_updated_on"],
                ];
            }
        }


        echo json_encode(
            ["status" => "success", "data" => $grouped],
            JSON_PRETTY_PRINT
        );
    } catch (Exception $e) {
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
}

function getSubscribedProducts($userId, $withNames = false)
{
    global $conn;

    $stmt = $conn->prepare("
        SELECT selected_products, f_market
        FROM tbl_reg_logs
        WHERE memid   = :uid
          AND f_product = 'FO'
          AND status   = 'Active'
          AND inv_no  <> ''
    ");
    $stmt->execute(['uid' => $userId]);
    $regLogs = $stmt->fetchAll(PDO::FETCH_ASSOC);

    if (!$regLogs) {
        return [];
    }

    // Collect product IDs
    $productIds = [];

    foreach ($regLogs as $row) {
        // Selected products (comma-separated)
        if (!empty($row['selected_products'])) {
            $ids = array_filter(array_map('trim', explode(',', $row['selected_products'])));
            $productIds = array_merge($productIds, $ids);
        }

        // Products from markets
        if (!empty($row['f_market'])) {
            $markets = array_filter(array_map('trim', explode(',', $row['f_market'])));

            if ($markets) {
                $conditions = [];
                foreach ($markets as $m) {
                    $conditions[] = "FIND_IN_SET(?, f_market)";
                }

                $sql = "SELECT id FROM forecast_products WHERE " . implode(" OR ", $conditions);
                $stmtM = $conn->prepare($sql);
                $stmtM->execute($markets);
                $marketProducts = $stmtM->fetchAll(PDO::FETCH_COLUMN);
                $productIds = array_merge($productIds, $marketProducts);
            }
        }
    }

    // Remove duplicates
    $productIds = array_unique($productIds);

    if (empty($productIds)) {
        return [];
    }

    // If only IDs are needed
    if (!$withNames) {
        return array_values($productIds);
    }

    // Otherwise fetch names as well
    $in = str_repeat('?,', count($productIds) - 1) . '?';
    $stmtP = $conn->prepare("
        SELECT id, forecast_name 
        FROM forecast_products 
        WHERE id IN ($in)
    ");

    $stmtP->execute($productIds);
    return $stmtP->fetchAll(PDO::FETCH_ASSOC);
}

if ($action == "get_subscribed_products") {

    $userId = (int)($input["memid"] ?? 0);
    if ($userId <= 0) {
        echo json_encode(["status" => "error", 'msg' => 'Invalid MemID', "data" => []]);
        exit;
    }

    $productIds = getSubscribedProducts($userId, true) ?? [];
    if (count($productIds) == 0) {
        echo json_encode(["status" => "error", "data" => []]);
        exit;
    }
    echo json_encode(["status" => "success", "data" => $productIds]);
    exit;
}


if ($action == "get_watchlist") {
    try {
        $userId = (int)($input["user_id"] ?? 0);

        // Remove duplicates
        $productIds = getSubscribedProducts($userId);

        if (empty($productIds)) {
            echo json_encode(["status" => "success", "data" => []]);
            exit;
        }

        // Step 2: Fetch product details
        $in  = str_repeat('?,', count($productIds) - 1) . '?';
        $stmt = $conn->prepare("SELECT id AS product_id, forecast_name, currency FROM forecast_products WHERE id IN ($in)");
        $stmt->execute($productIds);
        $products = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $stmt = $conn->prepare("SELECT * FROM forecast_latest_data WHERE forecast_product_id IN ($in)");
        $stmt->execute($productIds);
        $latestData = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $latestDataMap = getLatestForecastPeriods($latestData);

        // Extract periods and labels
        $periods = $latestDataMap['periods'] ?? [];
        $labels = $latestDataMap['labels'] ?? [];

        $result = [];

        foreach ($products as $product) {
            // Fetch latest forecast data for this product
            $stmt = $conn->prepare("SELECT * FROM forecast_latest_data WHERE forecast_product_id = :pid LIMIT 1");
            $stmt->execute(['pid' => $product['product_id']]);
            $fl = $stmt->fetch(PDO::FETCH_ASSOC);

            // --- Short Term: Use months from periods ---
            $shortTerm = [];
            if (!empty($periods['short_term_months'])) {
                foreach ($periods['short_term_months'] as $monthData) {
                    $entry = [
                        "label"    => date("F", mktime(0, 0, 0, $monthData['month'], 1)),
                        "fy"       => $monthData['fy'],
                        "new"      => null,
                        "previous" => null,
                        "change"   => null
                    ];

                    // Match against this product's data
                    if ($fl) {
                        for ($i = 1; $i <= 3; $i++) {
                            $productMonth = (int)($fl["short_term_lable$i"] ?? 0);
                            $productFY = $fl["short_term_lable{$i}_fy"] ?? '';

                            if ($productMonth === $monthData['month'] && $productFY === $monthData['fy']) {
                                $entry["new"]      = $fl["short_term_value$i"] ?? null;
                                $entry["previous"] = $fl["short_term_prev_value$i"] ?? null;
                                $entry["change"]   = $fl["short_term_change$i"] ?? null;
                                break;
                            }
                        }
                    }
                    $shortTerm[] = $entry;
                }
            }

            // --- Medium Term: Use quarter and half from periods ---
            $mediumTerm = [];

            // Quarter
            if ($periods['medium_term_quarter']) {
                $targetQuarter = $periods['medium_term_quarter']['quarter'];
                $targetQuarterFY = $periods['medium_term_quarter']['fy'];

                $quarterEntry = [
                    "label"    => $targetQuarter . $targetQuarterFY,
                    "new"      => null,
                    "previous" => null,
                    "change"   => null
                ];

                if (
                    $fl && ($fl['medium_term_quarter_lable'] ?? '') === $targetQuarter &&
                    ($fl['medium_term_quarter_lable_fy'] ?? '') === $targetQuarterFY
                ) {
                    $quarterEntry["new"]      = $fl['medium_term_quarter_value'] ?? null;
                    $quarterEntry["previous"] = $fl['medium_term_quarter_prev_value'] ?? null;
                    $quarterEntry["change"]   = $fl['medium_term_quarter_change'] ?? null;
                }

                $mediumTerm[] = $quarterEntry;
            }

            // Half Year
            if ($periods['medium_term_half']) {
                $targetHalf = $periods['medium_term_half']['half'];
                $targetHalfFY = $periods['medium_term_half']['fy'];

                $halfEntry = [
                    "label"    => $targetHalf . $targetHalfFY,
                    "new"      => null,
                    "previous" => null,
                    "change"   => null
                ];

                if (
                    $fl && ($fl['medium_term_halfyearly_lable'] ?? '') === $targetHalf &&
                    ($fl['medium_term_halfyearly_lable_fy'] ?? '') === $targetHalfFY
                ) {
                    $halfEntry["new"]      = $fl['medium_term_halfyearly_value'] ?? null;
                    $halfEntry["previous"] = $fl['medium_term_halfyearly_prev_value'] ?? null;
                    $halfEntry["change"]   = $fl['medium_term_halfyearly_change'] ?? null;
                }

                $mediumTerm[] = $halfEntry;
            }

            // --- Long Term: Use FY from periods ---
            $longTerm = [];
            if ($periods['long_term']) {
                $targetFY = $periods['long_term']['fy'];

                $longTermEntry = [
                    "label"    => $targetFY,
                    "new"      => null,
                    "previous" => null,
                    "change"   => null
                ];

                if ($fl && ($fl['long_term_lable'] ?? '') === $targetFY) {
                    $longTermEntry["new"]      = $fl['long_term_value'] ?? null;
                    $longTermEntry["previous"] = $fl['long_term_prev_value'] ?? null;
                    $longTermEntry["change"]   = $fl['long_term_change'] ?? null;
                }

                $longTerm[] = $longTermEntry;
            }

            // Get spot price
            $stmt = $conn->prepare("SELECT price AS spot_price, date_added FROM tbl_price_static WHERE relation_id = :pid LIMIT 1");
            $stmt->execute(['pid' => $product['product_id']]);
            $staticPrice = $stmt->fetch(PDO::FETCH_ASSOC);

            $result[] = [
                "product"               => $product['forecast_name'],
                "product_id"            => $product['product_id'],
                "currency"              => $product['currency'],
                "spot_price"            => $staticPrice['spot_price'] ?? '-',
                "as_on"                 => !empty($staticPrice['date_added']) ? date('Y-m-d', strtotime($staticPrice['date_added'])) : '-',

                "short_term"            => $shortTerm,
                "short_term_updated_on" => $fl['short_term_updated_on'] ?? null,

                "medium_term"           => $mediumTerm,
                "medium_term_updated_on" => $fl['medium_term_updated_on'] ?? null,

                "long_term"             => $longTerm,
                "long_term_updated_on"  => $fl['long_term_updated_on'] ?? null,
            ];
        }

        echo json_encode(["status" => "success", "data" => $result, 'labels' => $labels], JSON_PRETTY_PRINT);
    } catch (Exception $e) {
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
}

if ($action == "dashboard_summary") {
    $forecastProductId = $input['forecast_product_id'] ?? null;
    $term = strtolower($input['term'] ?? ''); // short | medium | long

    if (!$forecastProductId || !in_array($term, ['short', 'medium', 'long'])) {
        echo json_encode(["status" => "error", "message" => "Missing or invalid parameters"]);
        exit;
    }

    // ---------------- FORECAST PRODUCT ASSESSMENT ----------------
    $stmt = $conn->prepare("
        SELECT assessment_id
        FROM forecast_products
        WHERE id = :id
    ");
    $stmt->execute([":id" => $forecastProductId]);
    $fpRow = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$fpRow) {
        echo json_encode(["status" => "error", "message" => "Invalid forecast product"]);
        exit;
    }

    $forecastAssessmentId = (int)$fpRow['assessment_id']; // separate variable

    try {


        // get latest available data from DB
        $stmt = $conn->prepare("
            SELECT period_year, period_label
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
            ORDER BY period_year DESC, period_label DESC
            LIMIT 1
        ");
        $stmt->execute([":fpid" => $forecastProductId]);
        $lastRow = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($lastRow) {
            $latestYear  = (int)$lastRow['period_year'];
            $latestMonth = (int)$lastRow['period_label']; // assuming 1–12
        } else {
            // fallback if no data exists
            $latestYear  = (int)date("Y");
            $latestMonth = (int)date("n");
        }

        $currentYear = $latestYear;
        $currentMonth = $latestMonth;

        $startMonth = $currentMonth - 8; // ensures window includes current + next 3
        if ($startMonth <= 0) {
            $startMonth += 12;
            $startYear = $currentYear - 1;
        } else {
            $startYear = $currentYear;
        }

        $months = $categories = [];
        // anchor on latest available DB date
        $endDate   = strtotime("$latestYear-$latestMonth-01");
        // start = 11 months before end
        $startDate = strtotime("-11 months", $endDate);

        for ($i = 0; $i < 12; $i++) {
            $curDate = strtotime("+$i months", $startDate);

            $months[]     = date("M Y", $curDate);
            $categories[] = date("M-y", $curDate);
        }

        // Spot data from API
        $spotPriceFilters = [];
        $spotPriceFilters['assessment_id'] = $forecastAssessmentId;
        $spotPriceFilters['selected_market'] = 'ferrous';
        $spotPriceFilters['from_date'] = date("Y-m-01", $startDate);
        $spotPriceFilters['to_date'] = date("Y-m-t", $endDate);
        $apiData = fetchSpotPrices($spotPriceFilters);



        $spotData = [];
        $apiMap = [];

        // Map API data into month-year => value
        if (isset($apiData['data'])):
            foreach ($apiData['data'] as $entry) {
                $dateParts = explode("-", $entry["x1"]); // "2024-6-1"
                $year = $dateParts[0];
                $month = (int)$dateParts[1];
                $key = date("M Y", strtotime("$year-$month-01"));
                $apiMap[$key] = (float)$entry["y"];
            }
        endif;

        // Align with generated $months array
        foreach ($months as $m) {
            $spotData[] = $apiMap[$m] ?? null;
        }

        $result = ["status" => "success", "term" => $term];

        // ---------------- SHORT TERM ----------------
        if ($term === "short") {

            $startPeriod = (int)date("Y", $startDate) * 100 + (int)date("n", $startDate);
            $endPeriod   = (int)date("Y", $endDate) * 100 + (int)date("n", $endDate);

            // Fetch data using period_year
            $stmt = $conn->prepare("
                SELECT period_year, period_label, value, min_value, max_value
                FROM forecast_terms_data
                WHERE forecast_product_id = :fpid 
                AND term_type = 'SHORT'
                AND (period_year * 100 + period_label) BETWEEN :startPeriod AND :endPeriod
                ORDER BY period_year, period_label
            ");
            $stmt->execute([
                ":fpid" => $forecastProductId,
                ":startPeriod" => $startPeriod,
                ":endPeriod"   => $endPeriod,
            ]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Build forecast array keyed by YYYY-MM
            $shortForecast = [];
            foreach ($rows as $r) {
                $key = $r['period_year'] . '-' . str_pad($r['period_label'], 2, '0', STR_PAD_LEFT);
                $shortForecast[$key] = [
                    'value' => (float)$r['value'],
                    'min_value' => isset($r['min_value']) ? (float)$r['min_value'] : null,
                    'max_value' => isset($r['max_value']) ? (float)$r['max_value'] : null,
                ];
            }

            // Align with 12-month categories
            $forecastSeries = [];
            $forecastMin = [];
            $forecastMax = [];

            foreach ($months as $m) {
                $dt = DateTime::createFromFormat("M Y", $m);
                $key = $dt->format("Y-m"); // YYYY-MM

                $forecastSeries[] = $shortForecast[$key]['value'] ?? null;
                $forecastMin[]   = $shortForecast[$key]['min_value'] ?? null;
                $forecastMax[]   = $shortForecast[$key]['max_value'] ?? null;
            }

            // Assign to result
            $result["categories"] = $categories;
            $result["spot"] = $spotData;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            // Delta calculation
            $delta = [];
            for ($i = 0; $i < count($result["categories"]); $i++) {
                $spotVal = $result["spot"][$i] ?? null;
                $forecastVal = $result["forecast"][$i] ?? null;

                if ($spotVal !== null && $forecastVal !== null && $spotVal != 0) {
                    $delta[] = round((($forecastVal - $spotVal) / $spotVal) * 100, 2);
                } else {
                    $delta[] = null;
                }
            }
            $result["delta"] = $delta;
        }

        // ---------------- MEDIUM TERM ----------------
        // ---------------- MEDIUM TERM ----------------
        if ($term === "medium") {
            // Use same categories but remove last 3 months
            $shortMonths = array_slice($months, 0, count($months) - 3);
            $shortCategories = array_slice($categories, 0, count($categories) - 3);

            // Fetch medium-term data
            $stmt = $conn->prepare("
                SELECT term_type, period_label, period_fy, value, min_value, max_value
                FROM forecast_terms_data
                WHERE forecast_product_id = :fpid
                  AND term_type IN ('MEDIUM_QUARTER','MEDIUM_HALF')
                  ORDER BY term_type, period_label DESC
                LIMIT 2
            ");
            $stmt->execute([":fpid" => $forecastProductId]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $labels = $shortCategories;
            $forecastSeries = [];
            $forecastMin = [];
            $forecastMax = [];

            // Fill forecast for short-term months
            foreach ($shortMonths as $m) {
                $dt = DateTime::createFromFormat("M Y", $m);
                $key = $dt->format("Y-m");
                // Fetch short-term forecast
                $stmt = $conn->prepare("
                    SELECT value, min_value, max_value
                    FROM forecast_terms_data
                    WHERE forecast_product_id = :fpid
                      AND term_type = 'SHORT'
                      AND period_year * 100 + period_label = :ym
                ");
                $stmt->execute([":fpid" => $forecastProductId, ":ym" => $dt->format("Ym")]);
                $row = $stmt->fetch(PDO::FETCH_ASSOC);

                $forecastSeries[] = isset($row['value']) ? (float)$row['value'] : null;
                $forecastMin[] = isset($row['min_value']) ? (float)$row['min_value'] : null;
                $forecastMax[] = isset($row['max_value']) ? (float)$row['max_value'] : null;
            }

            // Append latest medium-term
            foreach ($rows as $r) {
                $labels[] = $r['period_label'] . $r['period_fy']; // Q/H label
                $forecastSeries[] = (float)$r['value'];
                $forecastMin[] = isset($r['min_value']) ? (float)$r['min_value'] : null;
                $forecastMax[] = isset($r['max_value']) ? (float)$r['max_value'] : null;
            }

            $spotSeries = array_slice($spotData, 0, count($shortMonths));
            foreach ($rows as $r) {
                $spotSeries[] = null;
            }

            $result["categories"] = $labels;
            $result["spot"] = $spotSeries;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            $delta = [];
            for ($i = 0; $i < count($labels); $i++) {
                $spotVal = $spotSeries[$i] ?? null;
                $forecastVal = $forecastSeries[$i] ?? null;
                $delta[] = ($spotVal !== null && $forecastVal !== null && $spotVal != 0)
                    ? round((($forecastVal - $spotVal) / $spotVal) * 100, 2)
                    : null;
            }
            $result["delta"] = $delta;
        }

        // ---------------- LONG TERM ----------------
        if ($term === "long") {
            $shortMonths = array_slice($months, 0, count($months) - 3);
            $shortCategories = array_slice($categories, 0, count($categories) - 3);

            $stmt = $conn->prepare("
                SELECT period_fy, value, min_value, max_value
                FROM forecast_terms_data
                WHERE forecast_product_id = :fpid
                  AND term_type = 'LONG'
                ORDER BY period_fy DESC
                LIMIT 1
            ");
            $stmt->execute([":fpid" => $forecastProductId]);
            $row = $stmt->fetch(PDO::FETCH_ASSOC);

            $labels = $shortCategories;
            $forecastSeries = [];
            $forecastMin = [];
            $forecastMax = [];

            foreach ($shortMonths as $m) {
                $dt = DateTime::createFromFormat("M Y", $m);
                $key = $dt->format("Y-m");
                // Fetch short-term forecast
                $stmt = $conn->prepare("
                    SELECT value, min_value, max_value
                    FROM forecast_terms_data
                    WHERE forecast_product_id = :fpid
                      AND term_type = 'SHORT'
                      AND period_year * 100 + period_label = :ym
                ");
                $stmt->execute([":fpid" => $forecastProductId, ":ym" => $dt->format("Ym")]);
                $srow = $stmt->fetch(PDO::FETCH_ASSOC);
                $forecastSeries[] = isset($srow['value']) ? (float)$srow['value'] : null;
                $forecastMin[] = isset($srow['min_value']) ? (float)$srow['min_value'] : null;
                $forecastMax[] = isset($srow['max_value']) ? (float)$srow['max_value'] : null;
            }

            // Append last FY value
            if ($row) {
                $labels[] = $row['period_fy'];
                $forecastSeries[] = (float)$row['value'];
                $forecastMin[] = isset($row['min_value']) ? (float)$row['min_value'] : null;
                $forecastMax[] = isset($row['max_value']) ? (float)$row['max_value'] : null;
            }

            $spotSeries = array_slice($spotData, 0, count($shortMonths));
            $spotSeries[] = null;

            $result["categories"] = $labels;
            $result["spot"] = $spotSeries;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            $delta = [];
            for ($i = 0; $i < count($labels); $i++) {
                $spotVal = $spotSeries[$i] ?? null;
                $forecastVal = $forecastSeries[$i] ?? null;
                $delta[] = ($spotVal !== null && $forecastVal !== null && $spotVal != 0)
                    ? round((($forecastVal - $spotVal) / $spotVal) * 100, 2)
                    : null;
            }
            $result["delta"] = $delta;
        }

        echo json_encode($result);
    } catch (Exception $e) {
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
    exit;
}

function fetchSpotPrices($filters)
{
    global $conn; // use global PDO connection

    $itemID = $filters['assessment_id'] ?? null;
    $market = $filters['selected_market'] ?? null;
    $toDate = $filters['to_date'] ?? null;
    $fromDate = $filters['from_date'] ?? null;



    // Fetch row from DB
    $stmt = $conn->prepare("SELECT trade_type, cnf_fob, t_frequency, currency FROM tbl_op_commodity_master WHERE id = :id LIMIT 1");
    $stmt->execute([':id' => $itemID]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$row) {
        throw new Exception("Commodity not found in database");
    }
    // currencyName from DB
    $currency = !empty($row['currency']) ? $row['currency'] : "INR";

    // priceType logic
    $tradeType = strtolower(trim($row['trade_type']));
    if (in_array($tradeType, ["dom", "global dom"])) {
        $priceType = "f";
    } elseif ($tradeType === "exim") {
        $priceType = !empty($row['cnf_fob']) ? $row['cnf_fob'] : "cnf";
    } else {
        $priceType = "cnf"; // fallback
    }

    // assesment from DB
    $assesment = !empty($row['t_frequency']) ? $row['t_frequency'] : "Daily";

    $url = "https://webapi.bigmint.co/apibm/public/v2019/prices/graph";

    $payload = [
        "resourceType"   => "web",
        "itemID"         => $itemID,
        "currencyName"   => $currency,
        "priceType"      => $priceType,
        "assesment"      => $assesment,
        "convertType"    => "Monthly",
        "toDate"         => $toDate,
        "fromDate"       => $fromDate,
        "filters"        => [
            "selected_market" => $market
        ]
    ];


    $ch = curl_init($url);
    curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
    curl_setopt($ch, CURLOPT_HTTPHEADER, ["Content-Type: application/json"]);
    curl_setopt($ch, CURLOPT_POST, true);
    curl_setopt($ch, CURLOPT_POSTFIELDS, json_encode($payload));

    $response = curl_exec($ch);
    if (curl_errno($ch)) {
        throw new Exception("cURL error: " . curl_error($ch));
    }
    curl_close($ch);

    $data = json_decode($response, true);

    if (!isset($data["status"]["code"]) || $data["status"]["code"] != 1001) {
        //throw new Exception("API error: " . ($data["status"]["message"] ?? "Unknown error"));
    }

    return $data;
}

if ($action == "market_drivers_categories") {
    try {
        $stmt = $conn->query("SELECT DISTINCT commodity_market_drivers_category 
                             FROM commodity_market_drivers 
                             WHERE commodity_market_drivers_category IS NOT NULL 
                             ORDER BY commodity_market_drivers_category ASC");

        $categories = $stmt->fetchAll(PDO::FETCH_COLUMN);

        echo json_encode([
            "status" => "success",
            "data" => $categories
        ]);
    } catch (Exception $e) {
        echo json_encode([
            "status" => "error",
            "message" => $e->getMessage()
        ]);
    }
}

if ($action == "market_drivers") {
    $category = $input['category'] ?? null;

    if (!$category) {
        echo json_encode([
            "status" => "error",
            "message" => "Missing category parameter"
        ]);
        exit;
    }

    try {
        $stmt = $conn->prepare("SELECT id, assessment_id, commodity_market_drivers_name, commodity_market_drivers_unit 
                               FROM commodity_market_drivers 
                               WHERE commodity_market_drivers_category = :category 
                               ORDER BY commodity_market_drivers_name ASC");
        $stmt->execute([':category' => $category]);

        $drivers = $stmt->fetchAll(PDO::FETCH_ASSOC);

        echo json_encode([
            "status" => "success",
            "data" => $drivers
        ]);
    } catch (Exception $e) {
        echo json_encode([
            "status" => "error",
            "message" => $e->getMessage()
        ]);
    }
}

if ($action == "market_drivers_all") {
    try {
        // Step 1: Fetch categories
        $stmt = $conn->query("SELECT DISTINCT commodity_market_drivers_category 
                             FROM commodity_market_drivers 
                             WHERE commodity_market_drivers_category IS NOT NULL 
                             ORDER BY sequence ASC");

        $categories = $stmt->fetchAll(PDO::FETCH_COLUMN);

        $result = [];

        // Step 2: For each category, fetch its drivers
        foreach ($categories as $category) {
            $stmtDrivers = $conn->prepare("SELECT id, assessment_id, commodity_market_drivers_name, commodity_market_drivers_unit 
                                           FROM commodity_market_drivers 
                                           WHERE commodity_market_drivers_category = :category 
                                           ORDER BY sequence ASC");
            $stmtDrivers->execute([':category' => $category]);
            $drivers = $stmtDrivers->fetchAll(PDO::FETCH_ASSOC);

            $result[] = [
                'category' => $category,
                'drivers' => $drivers
            ];
        }

        echo json_encode([
            "status" => "success",
            "data" => $result
        ]);
    } catch (Exception $e) {
        echo json_encode([
            "status" => "error",
            "message" => $e->getMessage()
        ]);
    }
}


if ($action == "comodity_market_drivers_chart") {
    $forecastProductId = $input['forecast_product_id'] ?? null;
    $marketDriverId    = $input['market_driver_id'] ?? null;
    $memid    = $input['memid'] ?? null;

    if (!$forecastProductId || !$marketDriverId) {
        echo json_encode(["status" => "error", "message" => "Missing parameters"]);
        exit;
    }

    // ---------------- FORECAST PRODUCT ASSESSMENT ----------------
    $stmt = $conn->prepare("
        SELECT assessment_id,f_market,forecast_name
        FROM forecast_products
        WHERE id = :id
    ");
    $stmt->execute([":id" => $forecastProductId]);
    $fpRow = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$fpRow) {
        echo json_encode(["status" => "error", "message" => "Invalid forecast product"]);
        exit;
    }

    $forecastAssessmentId = (int)$fpRow['assessment_id']; // separate variable
    $forecastMarket = $fpRow['f_market'];
    $forecastName = $fpRow['forecast_name'];


    // ---------------- MARKET DRIVER ASSESSMENT ----------------
    $stmt = $conn->prepare("
        SELECT id, assessment_id, f_market, commodity_market_drivers_unit, commodity_market_drivers_name
        FROM commodity_market_drivers
        WHERE id = :id
    ");
    $stmt->execute([":id" => $marketDriverId]);
    $driver = $stmt->fetch(PDO::FETCH_ASSOC);

    if (!$driver) {
        echo json_encode(["status" => "error", "message" => "Invalid market driver"]);
        exit;
    }

    $driverAssessmentId = (int)$driver['assessment_id'];
    $driverMarket       = $driver['f_market'];
    $driverName         = $driver['commodity_market_drivers_name'];
    $driverUnit         = $driver['commodity_market_drivers_unit'];

    // ---------------- MONTHLY WINDOW ----------------
    $currentYear  = date("Y");
    $currentMonth = (int)date("n"); // 1–12
    $startMonth   = $currentMonth - 8;
    if ($startMonth <= 0) {
        $startMonth += 12;
        $startYear = $currentYear - 1;
    } else {
        $startYear = $currentYear;
    }

    $months = $categories = [];
    for ($i = 0; $i < 12; $i++) {
        $monthNum = (($startMonth + $i - 1) % 12) + 1;
        $year     = $startYear + floor(($startMonth + $i - 1) / 12);
        $months[]     = date("M Y", strtotime("$year-$monthNum-01"));
        $categories[] = date("M-y", strtotime("$year-$monthNum-01"));
    }

    // ---------------- SPOT PRICES ----------------
    $spotData = [];
    $apiMap   = [];

    $currency = "INR"; // hardcoded for now, can be dynamic later

    $spotPriceFilters = [];
    $spotPriceFilters['assessment_id'] = $forecastAssessmentId;
    $spotPriceFilters['selected_market'] = $forecastMarket;

    $apiData = fetchSpotPrices($spotPriceFilters); // main spot

    if (isset($apiData['data'])) {
        foreach ($apiData['data'] as $entry) {
            $dateParts = explode("-", $entry["x1"]);
            $key       = date("M Y", strtotime($dateParts[0] . "-" . $dateParts[1] . "-01"));
            $apiMap[$key] = (float)$entry["y"];
        }
        $currency = $apiData['currency'] ?? $currency;
    }
    foreach ($months as $m) {
        $spotData[] = $apiMap[$m] ?? null;
    }

    // ---------------- COST OF PRODUCTION ----------------
    $costOfProduction = [];
    if ($driverAssessmentId > 0) {
        $spotPriceFilters = [];
        $spotPriceFilters['assessment_id'] = $driverAssessmentId;
        $spotPriceFilters['selected_market'] = $driverMarket;
        $copApiData = fetchSpotPrices($spotPriceFilters);
        $copMap     = [];
        if (isset($copApiData['data'])):
            foreach ($copApiData['data'] as $entry) {
                $dateParts = explode("-", $entry["x1"]);
                $key       = date("M Y", strtotime($dateParts[0] . "-" . $dateParts[1] . "-01"));
                $copMap[$key] = (float)$entry["y"];
            }
        endif;
        foreach ($months as $m) {
            $costOfProduction[] = $copMap[$m] ?? null;
        }
    } else {
        // call your separate API for COP
        $costOfProduction = fetchCostOfProductionFromCustomApi($driverName, $driverMarket, $months, $driverUnit, $memid);
    }

    // ---------------- FORECAST PRICES ----------------
    $forecastSeries = [];

    $stmt = $conn->prepare("
            SELECT period_label, value
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
              AND term_type = 'SHORT'
              AND period_year = :pyear
            ORDER BY period_label ASC
        ");
    $stmt->execute([":fpid" => $forecastProductId, ":pyear" => $currentYear]);
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

    $shortForecast = [];
    foreach ($rows as $r) {
        $shortForecast[(int)$r['period_label']] = (float)$r['value'];
    }

    for ($m = 1; $m <= 12; $m++) {
        $forecastSeries[] = $shortForecast[$m] ?? null;
    }


    // ---------------- RESULT ----------------
    $result = [
        "status"            => "success",
        "currency"  => $currency,
        "driver_unit"  => $driverUnit,
        "forecast_product"  => $forecastName,
        "market_driver"     => $driverName,
        "categories"        => $categories,
        "spot"              => $spotData,
        "cost_of_production" => $costOfProduction,
        "forecast"          => $forecastSeries,
        "graph_type"    => $driverAssessmentId > 0 ? 'line' : 'bar'
    ];

    echo json_encode($result);
}

function fetchCostOfProductionFromCustomApi($driverName, $driverMarket, $months, $driverUnit, $memid)
{
    $parsed = parseDriverName($driverName);

    $apiUrl = "https://webapi.bigmint.co/apibm/public/v2019/statistics/graph";

    $fromDate = $months[0];
    $toDate   = $months[count($months) - 1];

    $formData = [
        'resourceType'          => 'web',
        'region'                => $parsed['region'],
        'fromDate'              => $fromDate,
        'toDate'                => $toDate,
        'commodity'             => $parsed['commodity'],
        'periodFormat'          => 'monthly',
        'columnNameOfRegion'    => 'country',
        'columnNameOfCommodity' => 'commodity_new',
        'dataType'              => strtolower($parsed['dataType']), // ensure lowercase
        'unit'                  => $driverUnit,
        'memid'                 => $memid,
        'mainCommodity'         => $parsed['commodity'],
        'yearType'              => 'CY',
        'selected_market'       => $driverMarket
    ];



    try {
        $ch = curl_init($apiUrl);
        curl_setopt($ch, CURLOPT_POST, true);
        curl_setopt($ch, CURLOPT_POSTFIELDS, http_build_query($formData));
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

        $response = curl_exec($ch);
        curl_close($ch);

        if (!$response) {
            return array_fill(0, count($months), null);
        }

        $data = json_decode($response, true);

        // ✅ Check success
        if (!isset($data['status']['status']) || $data['status']['status'] != 1001) {
            return array_fill(0, count($months), null);
        }

        if (!isset($data['data']['graphValues']) || !is_array($data['data']['graphValues'])) {
            return array_fill(0, count($months), null);
        }

        // ✅ Parse graphValues
        $copMap = [];
        foreach ($data['data']['graphValues'] as $entry) {
            if (!isset($entry['x']) || !isset($entry['y'])) continue;

            $key = date("M Y", strtotime($entry["x"])); // e.g. "Jan 2025"
            $copMap[$key] = (float)$entry["y"];
        }

        // ✅ Build series aligned to $months
        $costOfProduction = [];
        foreach ($months as $m) {
            $costOfProduction[] = $copMap[$m] ?? null;
        }

        return $costOfProduction;
    } catch (Exception $e) {
        return array_fill(0, count($months), null);
    }
}

if ($action == "get_latest_rationale") {
    $forecastProductId = $input['forecast_product_id'] ?? null;

    if (!$forecastProductId) {
        echo json_encode([
            'status' => 'error',
            'message' => 'Missing forecast_product_id'
        ]);
        exit;
    }

    try {
        $stmt = $conn->prepare("
            SELECT * 
            FROM forecast_monthly_rationale 
            WHERE forecast_product_id = :pid  and rationale <> ''
            ORDER BY id DESC 
            LIMIT 1
        ");
        $stmt->execute(['pid' => $forecastProductId]);
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row) {
            echo json_encode([
                'status' => 'success',
                'data'   => $row
            ]);
        } else {
            echo json_encode([
                'status' => 'success',
                'data'   => null
            ]);
        }
    } catch (PDOException $e) {
        echo json_encode([
            'status' => 'error',
            'message' => $e->getMessage()
        ]);
    }

    exit;
}