<?php

/**
 * Validate short-term forecast months (must be 3 sequential months from execution_month).
 *
 * @param int $executionMonth
 * @param int $executionYear
 * @param array $shortTerm
 * @return bool|string true if valid, error message string if invalid
 */
function validateShortTermMonths(int $executionMonth, int $executionYear, array $shortTerm)
{
    if (count($shortTerm) !== 3) {
        return "Short term forecast must contain exactly 3 months";
    }

    for ($i = 0; $i < 3; $i++) {
        $given = $shortTerm[$i] ?? null;
        if (!$given || empty($given["month"]) || empty($given["year"])) {
            return "Missing month/year at position " . ($i + 1);
        }

        // Convert given month name to number
        $monthName = $given["month"];
        $monthNum  = monthNameToNumber($monthName);
        $year      = (int) $given["year"];

        if ($monthNum === null) {
            return "Invalid month name '{$monthName}' at position " . ($i + 1);
        }

        // For first element → set expected values
        if ($i === 0) {
            $expectedMonth = $monthNum;
            $expectedYear  = $year;
        } else {
            // Move expected month forward
            $expectedMonth++;
            if ($expectedMonth > 12) {
                $expectedMonth = 1;
                $expectedYear++;
            }

            // Compare with current given
            if ($monthNum !== $expectedMonth || $year !== $expectedYear) {
                return "Invalid sequence at position " . ($i + 1) .
                    ": expected " . date("F", mktime(0, 0, 0, $expectedMonth, 1)) . " / {$expectedYear}, " .
                    "got {$monthName} / {$year}";
            }
        }
    }

    return true;
}



function getLatestForecastTerms(int $forecastProductId, string $type = 'all'): array
{
    global $conn;
    $result = [
        'short_term'  => [],
        'medium_term' => ['quarter' => null, 'half' => null],
        'long_term'   => null,
    ];

    // --- Short Term ---
    if ($type === 'all' || $type === 'short_term') {
        $stmt = $conn->prepare("
            SELECT *
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
              AND term_type = 'SHORT'
            ORDER BY (period_year + 0)  DESC, (period_label + 0) DESC
            LIMIT 3
        ");
        $stmt->execute([':fpid' => $forecastProductId]);
        $short = $stmt->fetchAll(PDO::FETCH_ASSOC);

        usort($short, fn($a, $b) => $a['period_label'] <=> $b['period_label']);
        $result['short_term'] = $short;
    }

    // --- Medium Term ---
    if ($type === 'all' || $type === 'medium_term') {
        // quarter
        $stmt = $conn->prepare("
            SELECT *
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
              AND term_type = 'MEDIUM_QUARTER'
            ORDER BY CAST(SUBSTRING(period_fy, 3) AS UNSIGNED) DESC,
                        CAST(SUBSTRING(period_label, 2) AS UNSIGNED) DESC
            LIMIT 1
        ");
        $stmt->execute([':fpid' => $forecastProductId]);
        $result['medium_term']['quarter'] = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;

        // half
        $stmt = $conn->prepare("
            SELECT *
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
              AND term_type = 'MEDIUM_HALF'
            ORDER BY CAST(SUBSTRING(period_fy, 3) AS UNSIGNED) DESC,
                        CAST(SUBSTRING(period_label, 2) AS UNSIGNED) DESC
            LIMIT 1
        ");
        $stmt->execute([':fpid' => $forecastProductId]);
        $result['medium_term']['half'] = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    // --- Long Term ---
    if ($type === 'all' || $type === 'long_term') {
        $stmt = $conn->prepare("
            SELECT *
            FROM forecast_terms_data
            WHERE forecast_product_id = :fpid
              AND term_type = 'LONG'
            ORDER BY CAST(SUBSTRING(period_fy, 3) AS UNSIGNED) DESC
            LIMIT 1
        ");
        $stmt->execute([':fpid' => $forecastProductId]);
        $result['long_term'] = $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    return $result;
}

function manageRationale($input)
{
    global $conn;

    if (!$input || !isset($input['forecast_product_id'])) {
        return ['status' => 'error', 'message' => 'Invalid payload'];
    }

    $forecastProductId = $input['forecast_product_id'];
    $rationale        = $input['rationale'] ?? null;
    $executionMonth   = $input['execution_month'] ?? null;
    $executionYear    = $input['execution_year'] ?? null;
    $executionFy      = $input['execution_fy'] ?? null;
    $now              = date('Y-m-d H:i:s');

    try {
        // Delete existing row for same product + execution period (to avoid duplicates)
        $del = $conn->prepare("
            DELETE FROM forecast_monthly_rationale 
            WHERE forecast_product_id = :pid 
              AND execution_month = :month 
              AND execution_year = :year 
              AND execution_fy = :fy
        ");
        $del->execute([
            'pid'   => $forecastProductId,
            'month' => $executionMonth,
            'year'  => $executionYear,
            'fy'    => $executionFy,
        ]);

        // Insert new row
        $sql = "INSERT INTO forecast_monthly_rationale 
            (forecast_product_id, rationale, execution_month, execution_year, execution_fy, created_at, updated_at)
            VALUES (:pid, :rationale, :month, :year, :fy, :created_at, :updated_at)";
        $stmt = $conn->prepare($sql);
        $stmt->execute([
            'pid'        => $forecastProductId,
            'rationale'  => $rationale,
            'month'      => $executionMonth,
            'year'       => $executionYear,
            'fy'         => $executionFy,
            'created_at' => $now,
            'updated_at' => $now,
        ]);

        return ['status' => 'success'];
    } catch (PDOException $e) {
        return ['status' => 'error', 'message' => $e->getMessage()];
    }
}




function refreshForecastLatestData(int $forecastProductId, string $type = 'short_term')
{
    global $conn;

    // 1. Get latest terms
    $latest = getLatestForecastTerms($forecastProductId, $type);

    if (!$latest) {
        return false;
    }

    // 2. Fetch existing row (to preserve other sections)
    $stmt = $conn->prepare("SELECT * FROM forecast_latest_data WHERE forecast_product_id = :pid LIMIT 1");
    $stmt->execute(['pid' => $forecastProductId]);
    $existing = $stmt->fetch(PDO::FETCH_ASSOC) ?: [];

    // base data
    $newData = $existing;
    $newData['forecast_product_id'] = $forecastProductId;
    $newData['created_at'] = date('Y-m-d H:i:s');

    // --- SHORT TERM ---
    if ($type === 'short_term' && !empty($latest['short_term'])) {
        $newData['short_term_updated_on'] = date('Y-m-d H:i:s');

        for ($i = 0; $i < count($latest['short_term']); $i++) {
            $curr = $latest['short_term'][$i];
            $labelKey = "short_term_lable" . ($i + 1);
            $fyKey    = "short_term_lable" . ($i + 1) . '_fy';
            $valKey   = "short_term_value" . ($i + 1);
            $prevKey  = "short_term_prev_value" . ($i + 1);
            $chgKey   = "short_term_change" . ($i + 1);
            $chgKey   = "short_term_change" . ($i + 1);

            $label = $curr['period_label'] ?? null;
            $value = $curr['value'] ?? null;
            $fy    = $curr['period_fy'] ?? null;

            // previous value from old row
            $prevVal = null;
            for ($j = 1; $j <= 3; $j++) {
                if (($existing["short_term_lable$j"] ?? null) == $label) {
                    $prevVal = $existing["short_term_value$j"] ?? null;
                    break;
                }
            }

            $newData[$labelKey] = $label;
            $newData[$fyKey]    = $fy;
            $newData[$valKey]   = $value;
            $newData[$prevKey]  = $prevVal;
            $newData[$chgKey]   = calculateChange($prevVal, $value);
        }
    }

    // --- MEDIUM TERM ---
    if ($type === 'medium_term') {
        $newData['medium_term_updated_on'] = date('Y-m-d H:i:s');

        if (!empty($latest['medium_term']['quarter'])) {
            $curr = $latest['medium_term']['quarter'];
            $newData['medium_term_quarter_lable'] = $curr['period_label'] ?? null;
            $newData['medium_term_quarter_lable_fy'] = $curr['period_fy'] ?? null;
            $newData['medium_term_quarter_value'] = $curr['value'] ?? null;
            $prevVal = null;
            if (($existing['medium_term_quarter_lable'] ?? null) == ($curr['period_label'] ?? null)) {
                $prevVal = $existing['medium_term_quarter_value'] ?? null;
            }
            $newData['medium_term_quarter_prev_value'] = $prevVal;
            $newData['medium_term_quarter_change'] = calculateChange($prevVal, $curr['value'] ?? null);
        }

        if (!empty($latest['medium_term']['half'])) {
            $curr = $latest['medium_term']['half'];
            $newData['medium_term_halfyearly_lable'] = $curr['period_label'] ?? null;
            $newData['medium_term_halfyearly_lable_fy'] = $curr['period_fy'] ?? null;
            $newData['medium_term_halfyearly_value'] = $curr['value'] ?? null;
            $prevVal = null;
            if (($existing['medium_term_halfyearly_lable'] ?? null) == ($curr['period_label'] ?? null)) {
                $prevVal = $existing['medium_term_halfyearly_value'] ?? null;
            }
            $newData['medium_term_halfyearly_prev_value'] = $prevVal;
            $newData['medium_term_halfyearly_change'] = calculateChange($prevVal, $curr['value'] ?? null);
        }
    }

    // --- LONG TERM ---
    if ($type === 'long_term' && !empty($latest['long_term'])) {
        $curr = $latest['long_term'];
        $newData['long_term_updated_on'] = date('Y-m-d H:i:s');
        $newData['long_term_lable'] = $curr['period_fy'] ?? null;
        $newData['long_term_value'] = $curr['value'] ?? null;
        $newData['rationale'] = $curr['rationale'] ?? null;

        $prevVal = null;
        if (($existing['long_term_lable'] ?? null) == ($curr['period_fy'] ?? null)) {
            $prevVal = $existing['long_term_value'] ?? null;
        }
        $newData['long_term_prev_value'] = $prevVal;
        $newData['long_term_change'] = calculateChange($prevVal, $curr['value'] ?? null);
    }

    // 3. Delete + Insert
    $conn->beginTransaction();
    try {
        $del = $conn->prepare("DELETE FROM forecast_latest_data WHERE forecast_product_id = :pid");
        $del->execute(['pid' => $forecastProductId]);

        $cols = array_keys($newData);
        $placeholders = array_map(fn($c) => ':' . $c, $cols);
        $sql = "INSERT INTO forecast_latest_data (" . implode(",", $cols) . ") 
                VALUES (" . implode(",", $placeholders) . ")";
        $ins = $conn->prepare($sql);
        $ins->execute($newData);

        $conn->commit();
        return true;
    } catch (Exception $e) {
        $conn->rollBack();
        throw $e;
    }
}



function calculateChange($prev, $curr)
{
    if ($prev === null || $prev == 0) {
        return null;
    }
    return round((($curr - $prev) / $prev) * 100, 2);
}


function formatChange($changes)
{
    foreach ($changes as $c) {
        if ($c !== null && $c !== "N/A" && $c != 0 && $c != '') {
            if (strpos($c, "-") === 0) {
                return "down"; // Negative change
            } else {
                return "up"; // Positive change
            }
        }
    }
    return "—"; // No data
}

// Auto-calculate Quarter & Half labels
function getQuarterLabel($month)
{
    if ($month >= 1 && $month <= 3) return "Q1";
    if ($month >= 4 && $month <= 6) return "Q2";
    if ($month >= 7 && $month <= 9) return "Q3";
    return "Q4";
}

function getHalfLabel($month)
{
    return ($month <= 6) ? "H1" : "H2";
}

function getPreviousQuarter($month, $year)
{
    $label = getQuarterLabel($month);
    $map = ["Q1" => "Q4", "Q2" => "Q1", "Q3" => "Q2", "Q4" => "Q3"];
    $prevLabel = $map[$label];
    $prevYear = ($label === "Q1") ? $year - 1 : $year;
    return ["label" => $prevLabel, "year" => $prevYear];
}

function getPreviousHalf($month, $year)
{
    $label = getHalfLabel($month);
    $prevLabel = ($label === "H1") ? "H2" : "H1";
    $prevYear = ($label === "H1") ? $year - 1 : $year;
    return ["label" => $prevLabel, "year" => $prevYear];
}
function parseDriverName($name)
{
    // Example: "Finished Steel Production-India"
    // Step 1: Split by "-"
    $parts = explode("-", $name);
    $region = trim($parts[1] ?? ''); // after dash = Region

    // Step 2: Split first part into words
    $words = explode(" ", trim($parts[0]));

    // Assume last word = DataType, rest = Commodity
    $dataType  = array_pop($words);
    $commodity = implode(" ", $words);

    return [
        'commodity' => $commodity, // Finished Steel
        'dataType'  => $dataType,  // Production
        'region'    => $region     // India
    ];
}

function getFinancialYear($month, $year)
{
    // Financial year starts in April (04)
    if ((int)$month >= 4) {
        // FY runs from Apr (current year) → Mar (next year)
        $fy = substr($year, -2) + 1; // next year short form
    } else {
        // Before April → FY belongs to previous year
        $fy = substr($year, -2); // current year short form
    }
    return "FY" . str_pad($fy, 2, "0", STR_PAD_LEFT);
}

function monthNameToNumber($monthName)
{
    $timestamp = strtotime($monthName . " 1 2000"); // dummy year
    if ($timestamp === false) {
        return null;
    }
    return (int) date("n", $timestamp); // 1–12
}
function getYearFromFy($fy)
{
    // FY26 → 2025 (because FY26 means Apr 2025 – Mar 2026)
    return (int) substr($fy, 2) + 2000 - 1;
}
function getPeriodYear($fy, $label, $type)
{
    $startYear = getYearFromFy($fy);

    if ($type === "MEDIUM_QUARTER") {
        switch ($label) {
            case "Q1":
            case "Q2":
            case "Q3":
                return $startYear;
            case "Q4":
                return $startYear + 1;
        }
    }

    if ($type === "MEDIUM_HALF") {
        switch ($label) {
            case "H1":
                return $startYear;
            case "H2":
                return $startYear + 1; // H2 includes Jan–Mar next year
        }
    }

    return null;
}

// ---- Find latest 3 months across all products ----
function fyToYear($fy)
{
    preg_match('/(\d+)/', $fy, $m);
    return isset($m[1]) ? intval($m[1]) : 0;
}

// fiscal month sequence: Apr=1, May=2 ... Mar=12
function fiscalSeq($month)
{
    return (($month - 4 + 12) % 12) + 1;
}

function monthOrderKey($fy, $month)
{
    $fyNum = fyToYear($fy);
    return $fyNum * 100 + fiscalSeq($month);
}

function getLatestForecastPeriods($latestData)
{
    // Helper functions
    $fyToYear = function ($fy) {
        preg_match('/(\d+)/', $fy, $m);
        return isset($m[1]) ? intval($m[1]) : 0;
    };

    // fiscal month sequence: Apr=1, May=2 ... Mar=12
    $fiscalSeq = function ($month) {
        return (($month - 4 + 12) % 12) + 1;
    };

    $monthOrderKey = function ($fy, $month) use ($fyToYear, $fiscalSeq) {
        $fyNum = $fyToYear($fy);
        return $fyNum * 100 + $fiscalSeq($month);
    };

    $allMonthsByProduct = [];
    $allQuarters = [];
    $allHalfYears = [];
    $allLongTerms = [];

    // Collect from all products
    foreach ($latestData as $row) {
        // --- Short term months ---
        $productMonths = [];
        for ($i = 1; $i <= 3; $i++) {
            $fy    = $row["short_term_lable{$i}_fy"] ?? null;
            $month = (int)($row["short_term_lable{$i}"] ?? 0);
            if ($fy && $month > 0) {
                $productMonths[] = [
                    "fy"    => $fy,
                    "month" => $month,
                    "order" => $monthOrderKey($fy, $month)
                ];
            }
        }

        // Store months for this product with max order value
        if (!empty($productMonths)) {
            $maxOrder = max(array_column($productMonths, 'order'));
            $allMonthsByProduct[$row['forecast_product_id']] = [
                'months' => $productMonths,
                'max_order' => $maxOrder
            ];
        }

        // --- Medium term quarters ---
        if (!empty($row['medium_term_quarter_lable']) && !empty($row['medium_term_quarter_lable_fy'])) {
            $qNum = (int) filter_var($row['medium_term_quarter_lable'], FILTER_SANITIZE_NUMBER_INT);
            $allQuarters[] = [
                "fy"      => $row['medium_term_quarter_lable_fy'],
                "quarter" => $row['medium_term_quarter_lable'],
                "order"   => $fyToYear($row['medium_term_quarter_lable_fy']) * 10 + $qNum
            ];
        }

        // --- Medium term half years ---
        if (!empty($row['medium_term_halfyearly_lable']) && !empty($row['medium_term_halfyearly_lable_fy'])) {
            $halfNum = (strtoupper($row['medium_term_halfyearly_lable']) === 'H2') ? 2 : 1;
            $allHalfYears[] = [
                "fy"    => $row['medium_term_halfyearly_lable_fy'],
                "half"  => $row['medium_term_halfyearly_lable'],
                "order" => $fyToYear($row['medium_term_halfyearly_lable_fy']) * 10 + $halfNum
            ];
        }

        // --- Long term ---
        if (!empty($row['long_term_lable'])) {
            $allLongTerms[] = [
                "fy"    => $row['long_term_lable'],
                "order" => $fyToYear($row['long_term_lable'])
            ];
        }
    }

    // Find the product with the greatest (most recent) month
    $latestMonths = [];
    if (!empty($allMonthsByProduct)) {
        // Sort by max_order to find product with newest data
        uasort($allMonthsByProduct, fn($a, $b) => $b['max_order'] <=> $a['max_order']);

        // Get the first (newest) product's months
        $newestProductMonths = reset($allMonthsByProduct)['months'];

        // Sort months by order and get all 3
        usort($newestProductMonths, fn($a, $b) => $a['order'] <=> $b['order']);
        $latestMonths = $newestProductMonths;
    }

    // Sort remaining arrays
    usort($allQuarters, fn($a, $b) => $a['order'] <=> $b['order']);
    usort($allHalfYears, fn($a, $b) => $a['order'] <=> $b['order']);
    usort($allLongTerms, fn($a, $b) => $a['order'] <=> $b['order']);

    // Pick latest
    $latestQuarter = end($allQuarters) ?: null;
    $latestHalf    = end($allHalfYears) ?: null;
    $latestFY      = end($allLongTerms) ?: null;

    // If no data found, generate static labels based on current date
    if (empty($latestMonths) && empty($latestQuarter) && empty($latestHalf) && empty($latestFY)) {
        $currentMonth = (int)date("n");
        $currentYear  = (int)date("Y");
        $fyBaseYear = $currentMonth >= 4 ? $currentYear : $currentYear - 1;
        $shiftedMonth = ($currentMonth - 4 + 12) % 12 + 1;
        $quarterNum   = (int)ceil($shiftedMonth / 3);
        
        $currentFY = 'FY' . (($fyBaseYear + 1) % 100);
        $nextFY = 'FY' . (($fyBaseYear + 2) % 100);
        
        // Generate next 3 months
        $latestMonths = [];
        $month = $currentMonth;
        $year  = $currentYear;
        for ($i = 0; $i < 3; $i++) {
            $month++;
            if ($month > 12) {
                $month = 1;
                $year++;
            }
            $monthFY = ($month >= 4) ? 'FY' . (($year + 1) % 100) : 'FY' . ($year % 100);
            $latestMonths[] = [
                "fy"    => $monthFY,
                "month" => $month,
                "order" => $fyToYear($monthFY) * 100 + $fiscalSeq($month)
            ];
        }
        
        // Current quarter
        $latestQuarter = [
            "fy"      => $currentFY,
            "quarter" => 'Q' . $quarterNum,
            "order"   => $fyToYear($currentFY) * 10 + $quarterNum
        ];
        
        // Current half
        $latestHalf = [
            "fy"    => $currentFY,
            "half"  => ($quarterNum <= 2 ? 'H1' : 'H2'),
            "order" => $fyToYear($currentFY) * 10 + ($quarterNum <= 2 ? 1 : 2)
        ];
        
        // Next FY
        $latestFY = [
            "fy"    => $nextFY,
            "order" => $fyToYear($nextFY)
        ];
    }
    // Build labels
    $shortTermLabels = [];
    foreach ($latestMonths as $monthData) {
        $shortTermLabels[] = date("F", mktime(0, 0, 0, $monthData['month'], 1));
    }

    $mediumTermLabels = [];
    if ($latestQuarter) {
        $mediumTermLabels[] = $latestQuarter['quarter'] . $latestQuarter['fy'];
    }

    if ($latestHalf) {
        $mediumTermLabels[] = $latestHalf['half'] . $latestHalf['fy'];
    }

    $longTermLabel = $latestFY ? $latestFY['fy'] : null;

    return [
        "periods" => [
            "short_term_months"   => $latestMonths,
            "medium_term_quarter" => $latestQuarter,
            "medium_term_half"    => $latestHalf,
            "long_term"           => $latestFY
        ],
        "labels" => [
            "short_term"  => $shortTermLabels,
            "medium_term" => $mediumTermLabels,
            "long_term"   => $longTermLabel
        ]
    ];
}