<?php
/**
 * Price History — Extended functions for querying, displaying, and alerting.
 *
 * @package Bizmart
 * @license GPL-2.0-or-later
 * @since   3.0
 */
if (!defined('ABSPATH')) exit;

/**
 * Get paginated price history for a product.
 *
 * @param int $product_id
 * @param int $limit
 * @param int $offset
 * @return array [{id, purchase_price, selling_price, margin_pct, supplier_name, quantity, source, created_at}, ...]
 */
function bizmart_get_price_history(int $product_id, int $limit = 50, int $offset = 0): array {
    global $wpdb;

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

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

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $has_supplier_col = (bool) $wpdb->get_var("SHOW COLUMNS FROM {$ph} LIKE 'supplier_id'");

    if ($has_supplier_col) {
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT ph.id, ph.purchase_price, ph.selling_price,
                    ph.invoice_id, ph.supplier_id AS ph_supplier_id,
                    ph.quantity, ph.source, ph.created_at,
                    COALESCE(s.name, inv.supplier_name) AS supplier_name
             FROM {$ph} ph
             LEFT JOIN {$su} s ON ph.supplier_id = s.id
             LEFT JOIN {$inv} inv ON ph.invoice_id = inv.id
             WHERE ph.product_id = %d
               AND (ph.invoice_id IS NULL OR ph.invoice_id = 0 OR inv.deleted_at IS NULL)
             ORDER BY ph.created_at DESC, ph.id DESC
             LIMIT %d OFFSET %d",
            $product_id, $limit, $offset
        ));
    } else {
        // Fallback for pre-v3 schema
        $rows = $wpdb->get_results($wpdb->prepare(
            "SELECT ph.id, ph.purchase_price, ph.selling_price,
                    ph.invoice_id, ph.created_at,
                    inv.supplier_name
             FROM {$ph} ph
             LEFT JOIN {$inv} inv ON ph.invoice_id = inv.id
             WHERE ph.product_id = %d
               AND (ph.invoice_id IS NULL OR ph.invoice_id = 0 OR inv.deleted_at IS NULL)
             ORDER BY ph.created_at DESC, ph.id DESC
             LIMIT %d OFFSET %d",
            $product_id, $limit, $offset
        ));
    }
    // phpcs:enable

    $result = [];
    $last_pp = null;
    $last_sp = null;
    foreach ($rows as $r) {
        $pp = (float) $r->purchase_price;
        $sp = (float) $r->selling_price;
        $pp_cmp = round($pp, 2);
        $sp_cmp = round($sp, 2);

        // Skip consecutive duplicates so the UI doesn't repeat identical prices.
        if ($last_pp !== null && $pp_cmp === $last_pp && $sp_cmp === $last_sp) {
            continue;
        }

        $margin = ($sp > 0) ? round((($sp - $pp) / $sp) * 100, 2) : null;

        $result[] = [
            'id'             => (int) $r->id,
            'purchase_price' => $pp,
            'selling_price'  => $sp,
            'margin_pct'     => $margin,
            'supplier_name'  => $r->supplier_name ?: null,
            'invoice_id'     => $r->invoice_id ? (int) $r->invoice_id : null,
            'quantity'        => (float) ($r->quantity ?? 1),
            'source'         => $r->source ?? 'invoice',
            'created_at'     => $r->created_at,
        ];

        $last_pp = $pp_cmp;
        $last_sp = $sp_cmp;
    }

    return $result;
}

/**
 * Get price statistics (min, max, avg, count, first_date, last_date).
 */
function bizmart_get_price_stats(int $product_id, ?string $since = null): array {
    global $wpdb;
    $ph = $wpdb->prefix . 'bizmart_price_history';
    $inv = $wpdb->prefix . 'bizmart_invoices';

    if (!bizmart_table_exists('bizmart_price_history')) {
        return ['count' => 0, 'min_pp' => 0, 'max_pp' => 0, 'avg_pp' => 0, 'first_date' => null, 'last_date' => null];
    }

    $sql = "SELECT
                COUNT(*) AS cnt,
                MIN(ph.purchase_price) AS min_pp,
                MAX(ph.purchase_price) AS max_pp,
                AVG(ph.purchase_price) AS avg_pp,
                MIN(ph.created_at) AS first_date,
                MAX(ph.created_at) AS last_date
            FROM {$ph} ph
            LEFT JOIN {$inv} i ON ph.invoice_id = i.id
            WHERE ph.product_id = %d AND ph.purchase_price > 0
              AND (ph.invoice_id IS NULL OR ph.invoice_id = 0 OR i.deleted_at IS NULL)";
    $params = [$product_id];

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

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $row = $wpdb->get_row($wpdb->prepare($sql, ...$params));

    return [
        'count'      => (int) ($row->cnt ?? 0),
        'min_pp'     => round((float) ($row->min_pp ?? 0), 2),
        'max_pp'     => round((float) ($row->max_pp ?? 0), 2),
        'avg_pp'     => round((float) ($row->avg_pp ?? 0), 2),
        'first_date' => $row->first_date ?? null,
        'last_date'  => $row->last_date ?? null,
    ];
}

/**
 * Get the purchase price as of a specific date.
 */
function bizmart_get_price_at_date(int $product_id, string $date): ?array {
    global $wpdb;
    $ph = $wpdb->prefix . 'bizmart_price_history';
    $inv = $wpdb->prefix . 'bizmart_invoices';

    if (!bizmart_table_exists('bizmart_price_history')) {
        return null;
    }

    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
    $row = $wpdb->get_row($wpdb->prepare(
        "SELECT ph.purchase_price, ph.selling_price, ph.created_at
         FROM {$ph} ph
         LEFT JOIN {$inv} i ON ph.invoice_id = i.id
         WHERE ph.product_id = %d AND ph.created_at <= %s
           AND (ph.invoice_id IS NULL OR ph.invoice_id = 0 OR i.deleted_at IS NULL)
         ORDER BY ph.created_at DESC, ph.id DESC
         LIMIT 1",
         $product_id, $date . ' 23:59:59'
    ));
    // phpcs:enable

    if (!$row) return null;

    return [
        'purchase_price' => (float) $row->purchase_price,
        'selling_price'  => (float) $row->selling_price,
        'created_at'     => $row->created_at,
    ];
}

/**
 * Check and fire margin/price-change alerts after invoice save.
 * Stores alerts in a transient for the dashboard to display.
 */
function bizmart_check_price_alerts(int $product_id, float $new_pp, float $sp): void {
    if (!function_exists('bizmart_get_option')) return;

    $margin_threshold = (float) bizmart_get_option('biz_margin_alert_threshold', 10);
    $price_change_pct = (float) bizmart_get_option('biz_price_change_alert_pct', 5);
    $alerts = get_transient('bizmart_price_alerts') ?: [];

    // Margin alert
    if ($sp > 0) {
        $margin = (($sp - $new_pp) / $sp) * 100;
        if ($margin < $margin_threshold) {
            $alerts['margin_' . $product_id] = [
                'type'    => 'low_margin',
                'name'    => get_the_title($product_id),
                'margin'  => round($margin, 2),
                'pp'      => $new_pp,
                'sp'      => $sp,
                'date'    => current_time('Y-m-d H:i'),
            ];
        }
    }

    // Price change alert
    $last = function_exists('bizmart_get_last_prices') ? bizmart_get_last_prices($product_id) : null;
    if ($last && $last['purchase_price'] > 0 && $new_pp > 0) {
        $change = abs($new_pp - $last['purchase_price']) / $last['purchase_price'] * 100;
        if ($change >= $price_change_pct) {
            $direction = ($new_pp > $last['purchase_price']) ? '+' : '-';
            $alerts['price_' . $product_id] = [
                'type'      => 'price_change',
                'name'      => get_the_title($product_id),
                'old_pp'    => $last['purchase_price'],
                'new_pp'    => $new_pp,
                'change_pct'=> round($change, 1),
                'direction' => $direction,
                'date'      => current_time('Y-m-d H:i'),
            ];
        }
    }

    // Keep only last 50 alerts, expire after 24h
    $alerts = array_slice($alerts, -50, 50, true);
    set_transient('bizmart_price_alerts', $alerts, DAY_IN_SECONDS);
}

/**
 * Get active alerts for dashboard display.
 */
function bizmart_get_active_alerts(): array {
    $alerts = get_transient('bizmart_price_alerts');
    return is_array($alerts) ? $alerts : [];
}

/**
 * Get low-margin products (below threshold).
 * Uses latest price from price_history.
 *
 * @param float $threshold Margin % threshold
 * @param int   $limit     Max results
 * @return array
 */
function bizmart_get_low_margin_products(float $threshold = 10.0, int $limit = 10): array {
    global $wpdb;
    $ph = $wpdb->prefix . 'bizmart_price_history';

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

    // Get the latest price entry per product using a subquery (excludes soft-deleted invoices)
    $inv = $wpdb->prefix . 'bizmart_invoices';
    // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
    $rows = $wpdb->get_results($wpdb->prepare(
        "SELECT ph.product_id, ph.purchase_price, ph.selling_price, ph.created_at
         FROM {$ph} ph
         INNER JOIN (
             SELECT ph2.product_id, MAX(ph2.id) AS max_id
             FROM {$ph} ph2
             LEFT JOIN {$inv} i ON ph2.invoice_id = i.id
             WHERE ph2.selling_price > 0 AND ph2.purchase_price > 0
               AND (ph2.invoice_id IS NULL OR ph2.invoice_id = 0 OR i.deleted_at IS NULL)
             GROUP BY ph2.product_id
         ) latest ON ph.id = latest.max_id
         WHERE ((ph.selling_price - ph.purchase_price) / ph.selling_price * 100) < %f
         ORDER BY ((ph.selling_price - ph.purchase_price) / ph.selling_price * 100) ASC
         LIMIT %d",
        $threshold, $limit
    ));
    // phpcs:enable

    $result = [];
    foreach ($rows as $r) {
        $pp = (float) $r->purchase_price;
        $sp = (float) $r->selling_price;
        $margin = ($sp > 0) ? round((($sp - $pp) / $sp) * 100, 2) : 0;

        $result[] = [
            'product_id'     => (int) $r->product_id,
            'name'           => get_the_title((int) $r->product_id),
            'purchase_price' => $pp,
            'selling_price'  => $sp,
            'margin_pct'     => $margin,
            'last_change'    => $r->created_at,
        ];
    }

    return $result;
}
