<?php
/**
 * Expense Analytics — Category breakdown, trends, budgets, P&L.
 *
 * @package Bizmart
 * @license GPL-2.0-or-later
 * @since   3.0
 */
if (!defined('ABSPATH')) exit;

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

/**
 * Get expense totals grouped by category for a date range.
 *
 * @return array [{category, total, count}, ...]
 */
function bizmart_get_expenses_by_category(string $start, string $end): array {
    global $wpdb;
    $table = $wpdb->prefix . 'bizmart_expenses';

    if (!bizmart_table_exists('bizmart_expenses')) {
        return [];
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
        "SELECT category, SUM(amount) AS total, COUNT(*) AS cnt
         FROM {$table}
         WHERE expense_date >= %s AND expense_date <= %s
         GROUP BY category
         ORDER BY total DESC",
        $start, $end
    ));

    $result = [];
    foreach ($rows as $r) {
        $result[] = [
            'category' => $r->category,
            'total'    => round((float) $r->total, 2),
            'count'    => (int) $r->cnt,
        ];
    }
    return $result;
}

/**
 * Get monthly expense trend (optionally per category).
 *
 * @return array [{month, category, total}, ...]
 */
function bizmart_get_expense_trend(string $start, string $end, ?string $category = null): array {
    global $wpdb;
    $table = $wpdb->prefix . 'bizmart_expenses';

    if (!bizmart_table_exists('bizmart_expenses')) {
        return [];
    }

    $sql = "SELECT
                DATE_FORMAT(expense_date, '%%Y-%%m') AS month,
                category,
                SUM(amount) AS total
            FROM {$table}
            WHERE expense_date >= %s AND expense_date <= %s";
    $params = [$start, $end];

    if ($category) {
        $sql .= " AND category = %s";
        $params[] = $category;
    }

    $sql .= " GROUP BY month, category ORDER BY month ASC";

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare($sql, ...$params));

    $result = [];
    foreach ($rows as $r) {
        $result[] = [
            'month'    => $r->month,
            'category' => $r->category,
            'total'    => round((float) $r->total, 2),
        ];
    }
    return $result;
}

/**
 * Get daily expense trend (optionally per category).
 *
 * @return array [{day, category, total}, ...]
 */
function bizmart_get_expense_trend_daily(string $start, string $end, ?string $category = null): array {
    global $wpdb;
    $table = $wpdb->prefix . 'bizmart_expenses';

    if (!bizmart_table_exists('bizmart_expenses')) {
        return [];
    }

    $sql = "SELECT
                DATE(expense_date) AS day,
                category,
                SUM(amount) AS total
            FROM {$table}
            WHERE expense_date >= %s AND expense_date <= %s";
    $params = [$start, $end];

    if ($category) {
        $sql .= " AND category = %s";
        $params[] = $category;
    }

    $sql .= " GROUP BY day, category ORDER BY day ASC";

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($wpdb->prepare($sql, ...$params));

    $result = [];
    foreach ($rows as $r) {
        $result[] = [
            'day'      => $r->day,
            'category' => $r->category,
            'total'    => round((float) $r->total, 2),
        ];
    }
    return $result;
}

/**
 * Get budget vs actual for a specific month.
 *
 * @return array [{category, budget, actual, variance_pct}, ...]
 */
function bizmart_get_budget_vs_actual(string $month): array {
    global $wpdb;
    $table = $wpdb->prefix . 'bizmart_expenses';

    $budgets = function_exists('bizmart_get_option')
        ? (array) bizmart_get_option('biz_expense_budgets', [])
        : [];

    if (empty($budgets)) {
        return [];
    }

    // Get actual expenses for the month
    $actuals = [];
    if (bizmart_table_exists('bizmart_expenses')) {
        $month_start = $month . '-01';
        $month_end   = gmdate('Y-m-t', strtotime($month_start));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT category, SUM(amount) AS total
             FROM {$table}
             WHERE expense_date >= %s AND expense_date <= %s
             GROUP BY category",
            $month_start, $month_end
        ));

        foreach ($rows as $r) {
            $actuals[$r->category] = (float) $r->total;
        }
    }

    $result = [];
    foreach ($budgets as $cat => $budget_amount) {
        $budget = (float) $budget_amount;
        $actual = $actuals[$cat] ?? 0.0;
        $variance = ($budget > 0) ? round(($actual - $budget) / $budget * 100, 1) : 0;

        $result[] = [
            'category'     => $cat,
            'budget'       => round($budget, 2),
            'actual'       => round($actual, 2),
            'variance_pct' => $variance,
            'over_budget'  => $actual > $budget,
        ];
    }

    return $result;
}

/**
 * Project recurring expenses for next N months.
 *
 * @return array {monthly_total, projected_total, items: [{category, amount}, ...]}
 */
function bizmart_project_recurring(int $months = 3): array {
    global $wpdb;
    $table = $wpdb->prefix . 'bizmart_expenses';

    if (!bizmart_table_exists('bizmart_expenses')) {
        return ['monthly_total' => 0, 'projected_total' => 0, 'items' => []];
    }

    $rows = $wpdb->get_results(
        $wpdb->prepare(
            "SELECT category, AVG(amount) AS avg_amount, COUNT(*) AS occurrences
             FROM {$table}
             WHERE is_recurring = %d
             GROUP BY category
             ORDER BY avg_amount DESC",
            1
        )
    );

    $items = [];
    $monthly_total = 0.0;
    foreach ($rows as $r) {
        $avg = round((float) $r->avg_amount, 2);
        $monthly_total += $avg;
        $items[] = [
            'category'    => $r->category,
            'amount'      => $avg,
            'occurrences' => (int) $r->occurrences,
        ];
    }

    return [
        'monthly_total'  => round($monthly_total, 2),
        'projected_total' => round($monthly_total * $months, 2),
        'months'          => $months,
        'items'           => $items,
    ];
}

/**
 * Simple P&L for a date range.
 *
 * Revenue = WC order totals
 * COGS    = Σ(qty × purchase_price) from order items
 * Expenses = Σ expenses in range
 *
 * @return array {revenue, cogs, gross_profit, gross_margin_pct, expenses, net_profit, net_margin_pct}
 */
function bizmart_get_pnl(string $start, string $end): array {
    $revenue  = 0.0;
    $cogs     = 0.0;
    $expenses = 0.0;

    // Revenue + COGS from WooCommerce orders
    if (function_exists('wc_get_orders')) {
        $orders = wc_get_orders([
            'limit'       => -1,
            'status'      => ['completed', 'processing', 'on-hold'],
            'type'        => 'shop_order',
            'date_after'  => $start,
            'date_before' => $end . ' 23:59:59',
        ]);

        foreach ($orders as $order) {
            if ($order instanceof WC_Order_Refund) continue;
            $revenue += (float) $order->get_total();

            foreach ($order->get_items() as $item) {
                $product = $item->get_product();
                if (!$product) continue;

                $pid = $product->get_id();
                $qty = (int) $item->get_quantity();

                // Try frozen cost first, then purchase price
                $cost = $item->get_meta('_bizmart_item_cost');
                if ($cost === '' || $cost === null || $cost === false) {
                    $pp = null;
                    if (function_exists('bizmart_analytics_get_purchase_price')) {
                        $pp = bizmart_analytics_get_purchase_price($pid);
                    }
                    if ($pp === null && $product->is_type('variation')) {
                        $parent_id = (int) $product->get_parent_id();
                        if ($parent_id && function_exists('bizmart_analytics_get_purchase_price')) {
                            $pp = bizmart_analytics_get_purchase_price($parent_id);
                        }
                    }
                    $cost = ($pp !== null) ? (float) $pp : 0.0;
                } else {
                    $cost = (float) $cost;
                }

                $cogs += $cost * $qty;
            }
        }
    }

    // Expenses
    if (function_exists('bizmart_analytics_get_expenses_range')) {
        $exp_rows = bizmart_analytics_get_expenses_range($start, $end);
        if (is_array($exp_rows)) {
            foreach ($exp_rows as $e) {
                $expenses += (float) ($e->amount ?? 0);
            }
        }
    }

    $gross_profit = $revenue - $cogs;
    $net_profit   = $gross_profit - $expenses;

    return [
        'revenue'          => round($revenue, 2),
        'cogs'             => round($cogs, 2),
        'gross_profit'     => round($gross_profit, 2),
        'gross_margin_pct' => ($revenue > 0) ? round(($gross_profit / $revenue) * 100, 1) : 0,
        'expenses'         => round($expenses, 2),
        'net_profit'       => round($net_profit, 2),
        'net_margin_pct'   => ($revenue > 0) ? round(($net_profit / $revenue) * 100, 1) : 0,
    ];
}

/**
 * Supplier cost comparison for a product.
 * Requires supplier_id column in price_history.
 *
 * @return array [{supplier_id, supplier_name, avg_price, min_price, max_price, order_count, last_order}, ...]
 */
function bizmart_compare_suppliers(int $product_id): array {
    global $wpdb;

    $ph = $wpdb->prefix . 'bizmart_price_history';
    $su = $wpdb->prefix . 'bizmart_suppliers';

    if (!bizmart_table_exists('bizmart_price_history') || !bizmart_table_exists('bizmart_suppliers')) {
        return [];
    }

    // Check if supplier_id column exists
    // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name only, schema check
    $cols = $wpdb->get_col("SHOW COLUMNS FROM {$ph}");
    if (!in_array('supplier_id', $cols)) {
        return [];
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $rows = $wpdb->get_results($wpdb->prepare(
        "SELECT
            ph.supplier_id,
            s.name AS supplier_name,
            COUNT(*) AS order_count,
            AVG(ph.purchase_price) AS avg_price,
            MIN(ph.purchase_price) AS min_price,
            MAX(ph.purchase_price) AS max_price,
            MAX(ph.created_at) AS last_order
         FROM {$ph} ph
         JOIN {$su} s ON ph.supplier_id = s.id
         WHERE ph.product_id = %d
           AND ph.supplier_id IS NOT NULL
           AND ph.purchase_price > 0
         GROUP BY ph.supplier_id
         ORDER BY avg_price ASC",
        $product_id
    ));

    $result = [];
    foreach ($rows as $r) {
        $result[] = [
            'supplier_id'   => (int) $r->supplier_id,
            'supplier_name' => $r->supplier_name,
            'avg_price'     => round((float) $r->avg_price, 2),
            'min_price'     => round((float) $r->min_price, 2),
            'max_price'     => round((float) $r->max_price, 2),
            'order_count'   => (int) $r->order_count,
            'last_order'    => $r->last_order,
        ];
    }

    return $result;
}

