<?php

require_once __DIR__ . '/SystemSettings.php';

/**
 * Financial Projections engine.
 *
 * Produces revenue, expense and net-profit forecasts from historical
 * sold_items / operational_expenses records using a trend-adjusted
 * moving average (3-month moving average blended with a least-squares
 * linear regression trend line). Shared by the on-screen report
 * (views/admin/financial_projections.php) and its PDF export
 * (views/admin/generate_financial_projections_pdf.php) so both always
 * agree on the numbers.
 *
 * Net profit uses the same accounting model as views/reports/profit_loss.php:
 * `sold_items.total_price` is the gross sold price, and the Settings-page
 * `profit_percentage` (SystemSettings::profitRate()) is the share of that
 * price which is actual profit margin. Net = (Sales x profit rate) - Expenses.
 */
class Projections {
    private $conn;
    private $profitPercentage;
    private $profitRate;

    const MOVING_AVERAGE_WINDOW = 3;

    public function __construct($conn) {
        $this->conn = $conn;
        $systemSettings = new SystemSettings();
        $this->profitPercentage = $systemSettings->profitPercentage();
        $this->profitRate = $systemSettings->profitRate();
    }

    public function getProfitPercentage() {
        return $this->profitPercentage;
    }

    public function getBranches() {
        $stmt = $this->conn->prepare("SELECT branch_id, branch_name FROM branches ORDER BY branch_name");
        $stmt->execute();
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    /**
     * Full report for a single branch (or all branches, when $branchId is null).
     */
    public function buildReport($branchId, $monthsBack, $horizon) {
        $months = $this->buildMonthKeys($monthsBack);

        $salesByMonth = $this->getMonthlySalesSeries($branchId, $monthsBack);
        $expensesByMonth = $this->getMonthlyExpenseSeries($branchId, $monthsBack);

        $salesValues = [];
        $expenseValues = [];
        foreach ($months as $m) {
            $salesValues[] = $salesByMonth[$m] ?? 0.0;
            $expenseValues[] = $expensesByMonth[$m] ?? 0.0;
        }

        $salesProjection = self::project($salesValues, $horizon);
        $expenseProjection = self::project($expenseValues, $horizon);

        $grossProfitHistory = [];
        $netHistory = [];
        foreach ($months as $i => $m) {
            $grossProfitHistory[] = round($salesValues[$i] * $this->profitRate, 2);
            $netHistory[] = round($grossProfitHistory[$i] - $expenseValues[$i], 2);
        }

        $grossProfitProjected = [];
        $netProjected = [];
        foreach ($salesProjection['projected'] as $i => $s) {
            $grossProfitProjected[] = round($s * $this->profitRate, 2);
            $netProjected[] = round($grossProfitProjected[$i] - $expenseProjection['projected'][$i], 2);
        }

        $monthToDateSales = $this->getMonthToDateSales($branchId);
        $monthToDateExpenses = $this->getMonthToDateExpenses($branchId);
        $monthToDateGrossProfit = round($monthToDateSales * $this->profitRate, 2);

        return [
            'branch_id' => $branchId,
            'profit_percentage' => $this->profitPercentage,
            'months' => $months,
            'sales_history' => $salesValues,
            'expense_history' => $expenseValues,
            'gross_profit_history' => $grossProfitHistory,
            'net_history' => $netHistory,
            'sales_projection' => $salesProjection,
            'expense_projection' => $expenseProjection,
            'gross_profit_projected' => $grossProfitProjected,
            'net_projected' => $netProjected,
            'month_to_date_sales' => $monthToDateSales,
            'month_to_date_expenses' => $monthToDateExpenses,
            'month_to_date_gross_profit' => $monthToDateGrossProfit,
            'sales_target' => $this->getSalesTarget($branchId),
            'expense_allocation' => $this->getExpenseAllocation($branchId),
            'category_breakdown' => $this->getExpenseCategoryBreakdown($branchId, $monthsBack),
        ];
    }

    /**
     * Next-month projection per branch, ranked best to worst, for the
     * "All Branches" consolidated view.
     */
    public function getPerBranchSummary($monthsBack) {
        $months = $this->buildMonthKeys($monthsBack);
        $summary = [];

        foreach ($this->getBranches() as $branch) {
            $salesByMonth = $this->getMonthlySalesSeries($branch['branch_id'], $monthsBack);
            $expensesByMonth = $this->getMonthlyExpenseSeries($branch['branch_id'], $monthsBack);

            $salesValues = [];
            $expenseValues = [];
            foreach ($months as $m) {
                $salesValues[] = $salesByMonth[$m] ?? 0.0;
                $expenseValues[] = $expensesByMonth[$m] ?? 0.0;
            }

            $salesProjection = self::project($salesValues, 1);
            $expenseProjection = self::project($expenseValues, 1);
            $projectedSales = $salesProjection['projected'][0] ?? 0.0;
            $projectedExpenses = $expenseProjection['projected'][0] ?? 0.0;

            $projectedGrossProfit = round($projectedSales * $this->profitRate, 2);

            $summary[] = [
                'branch_id' => $branch['branch_id'],
                'branch_name' => $branch['branch_name'],
                'projected_sales' => $projectedSales,
                'projected_expenses' => $projectedExpenses,
                'projected_gross_profit' => $projectedGrossProfit,
                'projected_net' => round($projectedGrossProfit - $projectedExpenses, 2),
                'growth_rate_pct' => $salesProjection['growth_rate_pct'],
                'confidence' => $salesProjection['confidence'],
            ];
        }

        usort($summary, function ($a, $b) {
            return $b['projected_net'] <=> $a['projected_net'];
        });

        return $summary;
    }

    /**
     * Trend-adjusted moving average forecast.
     *
     * Baseline = average of the last MOVING_AVERAGE_WINDOW actual months.
     * Trend = least-squares linear regression slope across the full history.
     * Each projected month blends the flat baseline with the trend line's
     * extrapolated value, so a sustained upward/downward trend is reflected
     * without over-reacting to a single unusual month.
     */
    public static function project(array $orderedValues, $horizonMonths) {
        $n = count($orderedValues);

        if ($n === 0) {
            return [
                'moving_average' => 0.0,
                'trend_slope' => 0.0,
                'projected' => array_fill(0, $horizonMonths, 0.0),
                'growth_rate_pct' => 0.0,
                'confidence' => 'Insufficient Data',
                'method' => 'No historical data available',
                'data_points' => 0,
            ];
        }

        $window = array_slice($orderedValues, -self::MOVING_AVERAGE_WINDOW);
        $movingAverage = array_sum($window) / count($window);

        $sumX = 0.0; $sumY = 0.0; $sumXY = 0.0; $sumX2 = 0.0;
        foreach ($orderedValues as $i => $y) {
            $sumX += $i;
            $sumY += $y;
            $sumXY += $i * $y;
            $sumX2 += $i * $i;
        }
        $mean = $sumY / $n;
        $denominator = ($n * $sumX2) - ($sumX * $sumX);
        $slope = ($n > 1 && $denominator != 0) ? ((($n * $sumXY) - ($sumX * $sumY)) / $denominator) : 0.0;
        $intercept = $mean - ($slope * ($sumX / $n));
        $growthRatePct = $mean != 0 ? ($slope / $mean) * 100 : 0.0;

        $projected = [];
        for ($i = 1; $i <= $horizonMonths; $i++) {
            $trendValue = $intercept + $slope * ($n - 1 + $i);
            $blended = ($movingAverage + $trendValue) / 2;
            $projected[] = max(0.0, round($blended, 2));
        }

        if ($n < 3) {
            $confidence = 'Low';
        } else {
            $variance = 0.0;
            foreach ($orderedValues as $y) {
                $variance += ($y - $mean) ** 2;
            }
            $variance /= $n;
            $coefficientOfVariation = $mean != 0 ? sqrt($variance) / $mean : 1.0;

            if ($n >= 6 && $coefficientOfVariation < 0.25) {
                $confidence = 'High';
            } elseif ($n >= 4 && $coefficientOfVariation < 0.5) {
                $confidence = 'Medium';
            } else {
                $confidence = 'Low';
            }
        }

        return [
            'moving_average' => round($movingAverage, 2),
            'trend_slope' => round($slope, 2),
            'projected' => $projected,
            'growth_rate_pct' => round($growthRatePct, 2),
            'confidence' => $confidence,
            'method' => self::MOVING_AVERAGE_WINDOW . '-month moving average blended with linear trend',
            'data_points' => $n,
        ];
    }

    /**
     * Chronological 'Y-m' keys for the N complete calendar months preceding
     * the current (in-progress) month, oldest first.
     */
    private function buildMonthKeys($monthsBack) {
        $keys = [];
        $base = new DateTime(date('Y-m-01'));
        for ($i = $monthsBack; $i >= 1; $i--) {
            $d = clone $base;
            $d->modify("-$i month");
            $keys[] = $d->format('Y-m');
        }
        return $keys;
    }

    private function windowDates($monthsBack) {
        $toDate = date('Y-m-01');
        $fromDate = (new DateTime($toDate))->modify("-$monthsBack month")->format('Y-m-d');
        return [$fromDate, $toDate];
    }

    public function getMonthlySalesSeries($branchId, $monthsBack) {
        list($fromDate, $toDate) = $this->windowDates($monthsBack);

        $sql = "SELECT DATE_FORMAT(sold_at, '%Y-%m') AS ym, SUM(total_price) AS total
                FROM sold_items
                WHERE COALESCE(is_ims_income, 1) = 1
                AND sold_at >= :from_date AND sold_at < :to_date";
        if ($branchId) {
            $sql .= " AND branch_id = :branch_id";
        }
        $sql .= " GROUP BY ym";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':from_date', $fromDate);
        $stmt->bindValue(':to_date', $toDate);
        if ($branchId) {
            $stmt->bindValue(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();

        $out = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $out[$row['ym']] = (float) $row['total'];
        }
        return $out;
    }

    public function getMonthlyExpenseSeries($branchId, $monthsBack) {
        list($fromDate, $toDate) = $this->windowDates($monthsBack);

        $sql = "SELECT DATE_FORMAT(expense_date, '%Y-%m') AS ym, SUM(amount) AS total
                FROM operational_expenses
                WHERE expense_date >= :from_date AND expense_date < :to_date";
        if ($branchId) {
            $sql .= " AND branch_id = :branch_id";
        }
        $sql .= " GROUP BY ym";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':from_date', $fromDate);
        $stmt->bindValue(':to_date', $toDate);
        if ($branchId) {
            $stmt->bindValue(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();

        $out = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $out[$row['ym']] = (float) $row['total'];
        }
        return $out;
    }

    public function getMonthToDateSales($branchId) {
        $sql = "SELECT SUM(total_price) AS total FROM sold_items
                WHERE COALESCE(is_ims_income, 1) = 1
                AND sold_at >= :from_date";
        if ($branchId) {
            $sql .= " AND branch_id = :branch_id";
        }
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':from_date', date('Y-m-01'));
        if ($branchId) {
            $stmt->bindValue(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();
        return (float) ($stmt->fetchColumn() ?: 0);
    }

    public function getMonthToDateExpenses($branchId) {
        $sql = "SELECT SUM(amount) AS total FROM operational_expenses
                WHERE expense_date >= :from_date";
        if ($branchId) {
            $sql .= " AND branch_id = :branch_id";
        }
        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':from_date', date('Y-m-01'));
        if ($branchId) {
            $stmt->bindValue(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();
        return (float) ($stmt->fetchColumn() ?: 0);
    }

    public function getSalesTarget($branchId) {
        if ($branchId) {
            $stmt = $this->conn->prepare("SELECT target_amount FROM sales_targets WHERE branch_id = ?");
            $stmt->execute([$branchId]);
            return (float) ($stmt->fetchColumn() ?: 0);
        }
        $stmt = $this->conn->prepare("SELECT SUM(target_amount) FROM sales_targets");
        $stmt->execute();
        return (float) ($stmt->fetchColumn() ?: 0);
    }

    public function getExpenseAllocation($branchId) {
        if ($branchId) {
            $stmt = $this->conn->prepare("SELECT allocation_amount FROM expense_allocation WHERE allocation_branch_id = ?");
            $stmt->execute([$branchId]);
            return (float) ($stmt->fetchColumn() ?: 0);
        }
        $stmt = $this->conn->prepare("SELECT SUM(allocation_amount) FROM expense_allocation");
        $stmt->execute();
        return (float) ($stmt->fetchColumn() ?: 0);
    }

    public function getExpenseCategoryBreakdown($branchId, $monthsBack) {
        list($fromDate, $toDate) = $this->windowDates($monthsBack);

        $sql = "SELECT ec.category_name, SUM(oe.amount) AS total
                FROM operational_expenses oe
                JOIN expense_categories ec ON ec.category_id = oe.expense_category_id
                WHERE oe.expense_date >= :from_date AND oe.expense_date < :to_date";
        if ($branchId) {
            $sql .= " AND oe.branch_id = :branch_id";
        }
        $sql .= " GROUP BY ec.category_name ORDER BY total DESC";

        $stmt = $this->conn->prepare($sql);
        $stmt->bindValue(':from_date', $fromDate);
        $stmt->bindValue(':to_date', $toDate);
        if ($branchId) {
            $stmt->bindValue(':branch_id', $branchId, PDO::PARAM_INT);
        }
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
}
