<?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_lable2, fl.short_term_change2,
                fl.short_term_lable3, fl.short_term_change3,

                -- Medium term
                fl.medium_term_updated_on,
                fl.medium_term_quarter_lable, fl.medium_term_quarter_change,
                fl.medium_term_halfyearly_lable, fl.medium_term_halfyearly_change,

                -- 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) {


            // --- Short Term: include current + next month ---
            $shortTermChanges = [];
            for ($i = 1; $i <= 3; $i++) {
                $label  = (int)($row["short_term_lable$i"] ?? 0);
                $change = $row["short_term_change$i"] ?? null;

                if ($label === $currentMonth || $label === $nextMonth) {
                    $shortTermChanges[] = $change;
                }
            }

            // --- Medium Term: current quarter and half-year ---
            $mediumTermChanges = [];
            if (($row["medium_term_quarter_lable"] ?? '') === $currentQuarter) {
                $mediumTermChanges[] = $row["medium_term_quarter_change"];
            }
            if (($row["medium_term_halfyearly_lable"] ?? '') === $currentHalf) {
                $mediumTermChanges[] = $row["medium_term_halfyearly_change"];
            }

            // --- Long Term: current year ---
            $longTermChanges = [];
            if (($row["long_term_lable"] ?? '') === $nextFY) {
                $longTermChanges[] = $row["long_term_change"];
            }

            // 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($shortTermChanges),
                    "short_term_updated_on" => $row["short_term_updated_on"],

                    // Medium Term
                    "medium_term"            => formatChange($mediumTermChanges),
                    "medium_term_updated_on" => $row["medium_term_updated_on"],

                    // Long Term
                    "long_term"            => formatChange($longTermChanges),
                    "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)
{
    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) {
                $in  = str_repeat('?,', count($markets) - 1) . '?';
                $stmtM = $conn->prepare("SELECT id FROM forecast_products WHERE f_market IN ($in)");
                $stmtM->execute($markets);
                $marketProducts = $stmtM->fetchAll(PDO::FETCH_COLUMN);
                $productIds = array_merge($productIds, $marketProducts);
            }
        }
    }

    // Remove duplicates
    return array_unique($productIds);
}

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) ?? [];
    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);


        // timeline info (Indian FY)
        $currentMonth = (int)date("n");   // 1–12
        $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);

        // build next 3 months (exclude current month)
        $months = [];
        $month = $currentMonth;
        $year  = $currentYear;

        for ($i = 0; $i < 3; $i++) {
            $month++;
            if ($month > 12) {
                $month = 1;
                $year++;
            }
            $months[] = [
                'month' => $month,
                'year'  => $year,
                'label' => date("F", mktime(0, 0, 0, $month, 1)) // "October"
            ];
        }

        // 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);

        $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 ---
            $shortTerm = [];
            foreach ($months as $m) {
                $entry = [
                    "label"    => $m['label'],
                    "new"      => null,
                    "previous" => null,
                    "change"   => null
                ];
                for ($i = 1; $i <= 3; $i++) {
                    if ((int)($fl["short_term_lable$i"] ?? 0) === $m['month']) {
                        $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 ---
            $mediumTerm = [];
            if (($fl['medium_term_quarter_lable'] ?? '') === $currentQuarter) {
                $mediumTerm[] = [
                    "label"    => $currentQuarter . $currentFY,
                    "new"      => $fl['medium_term_quarter_value'] ?? null,
                    "previous" => $fl['medium_term_quarter_prev_value'] ?? null,
                    "change"   => calculateChange($fl['medium_term_quarter_prev_value'] ?? null, $fl['medium_term_quarter_value'] ?? null)
                ];
            }
            if (($fl['medium_term_halfyearly_lable'] ?? '') === $currentHalf) {
                $mediumTerm[] = [
                    "label"    => $currentHalf . $currentFY,
                    "new"      => $fl['medium_term_halfyearly_value'] ?? null,
                    "previous" => $fl['medium_term_halfyearly_prev_value'] ?? null,
                    "change"   => $fl['medium_term_quarter_change'] ?? null,
                ];
            }

            // --- Long Term ---
            $longTerm = [];
            if (($fl['long_term_lable'] ?? '') === $nextFY) {
                $longTerm[] = [
                    "label"    => $nextFY,
                    "new"      => $fl['long_term_value'] ?? null,
                    "previous" => $fl['long_term_prev_value'] ?? null,
                    "change"   => $fl['long_term_change'] ?? null,
                ];
            }

            // get spot price from API
            $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], 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 {
        $currentYear = date("Y");
        $currentMonth = (int)date("n"); // 1-12

        $startMonth = $currentMonth - 8; // ensures window includes current + next 3
        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 data from API
        $apiData = fetchSpotPrices($forecastAssessmentId, $market = "ferrous");



        $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") {
            $stmt = $conn->prepare("
                SELECT period_label, value, min_value, max_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']] = [
                    '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,
                ];
            }

            $forecastSeries = [];
            $forecastMin = [];
            $forecastMax = [];
            for ($m = 1; $m <= 12; $m++) {
                $forecastSeries[] = $shortForecast[$m]['value'] ?? null;
                $forecastMin[] = $shortForecast[$m]['min_value'] ?? null;
                $forecastMax[] = $shortForecast[$m]['max_value'] ?? null;
            }

            $result["categories"] = $categories;
            $result["spot"] = $spotData;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            // delta
            $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 ----------------
        if ($term === "medium") {
            $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')
                  AND period_year = :pyear
            ");
            $stmt->execute([":fpid" => $forecastProductId, ":pyear" => $currentYear]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $labels = array_slice($categories, 0, $currentMonth);
            $forecastSeries = array_fill(0, $currentMonth, null);
            $forecastMin = array_fill(0, $currentMonth, null);
            $forecastMax = array_fill(0, $currentMonth, null);


            foreach ($rows as $r) {
                $labels[] = $r['period_fy'] . $r['period_label'];   // Q3, H2 etc
                $forecastSeries[] = (float)$r['value'];
                $forecastMax[] = (float)$r['max_value'];
                $forecastMin[] = (float)$r['min_value'];
            }

            $spotSeries = array_slice($spotData, 0, $currentMonth);
            foreach ($rows as $r) {
                $spotSeries[] = null; // no spot for Q/H
            }

            $result["categories"] = $labels;
            $result["spot"] = $spotSeries;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            // delta
            $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;
        }

        // ---------------- LONG TERM ----------------
        if ($term === "long") {
            $stmt = $conn->prepare("
                SELECT period_label,period_fy, value, max_value, min_value
                FROM forecast_terms_data
                WHERE forecast_product_id = :fpid
                  AND term_type = 'LONG'
            ");
            $stmt->execute([":fpid" => $forecastProductId]);
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

            $labels = array_slice($categories, 0, $currentMonth);
            $forecastSeries = array_fill(0, $currentMonth, null);
            $forecastMin = array_fill(0, $currentMonth, null);
            $forecastMax = array_fill(0, $currentMonth, null);

            foreach ($rows as $r) {
                $labels[] = $r['period_fy'];   // e.g. 2026
                $forecastSeries[] = (float)$r['value'];
                $forecastMax[] = (float)$r['max_value'];
                $forecastMin[] = (float)$r['min_value'];
            }

            $spotSeries = array_slice($spotData, 0, $currentMonth);
            foreach ($rows as $r) {
                $spotSeries[] = null; // no spot for future years
            }

            $result["categories"] = $labels;
            $result["spot"] = $spotSeries;
            $result["forecast"] = $forecastSeries;
            $result["forecast_min"] = $forecastMin;
            $result["forecast_max"] = $forecastMax;

            // delta
            $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;
        }

        echo json_encode($result);
    } catch (Exception $e) {
        echo json_encode(["status" => "error", "message" => $e->getMessage()]);
    }
    exit;
}

function fetchSpotPrices($itemID = 65, $market = "ferrous")
{
    global $conn; // use global PDO connection

    // 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://www.dataunu.in/apibm/public/v2019/prices/graph";

    $payload = [
        "resourceType"   => "web",
        "itemID"         => $itemID,
        "currencyName"   => $currency,
        "priceType"      => $priceType,
        "assesment"      => $assesment,
        "convertType"    => "Monthly",
        "toDate"         => date("Y-m-01"), // current month
        "fromDate"       => date("Y-m-01", strtotime("-8 months")),
        "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

    $apiData = fetchSpotPrices($forecastAssessmentId, $forecastMarket); // 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) {
        $copApiData = fetchSpotPrices($driverAssessmentId, $driverMarket, $driverUnit);
        $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://www.dataunu.in/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 
            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;
}