<?php
/**
 * Repository for custom thresholds.
 *
 * @package StockGuardianPro
 */

declare(strict_types=1);

namespace StockGuardianPro\LowStock\Services;

use DateTimeImmutable;
use DateTimeZone;
use StockGuardianPro\LowStock\Plugin;

\defined('ABSPATH') || exit;

final class ThresholdRepository
{
    private Plugin $plugin;

    public function __construct(Plugin $plugin)
    {
        $this->plugin = $plugin;
    }

    public function get_threshold(int $product_id, int $variation_id = 0): ?int
    {
        global $wpdb;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $table = $wpdb->prefix . 'low_stock_thresholds';
        $sql   = $wpdb->prepare(
            "SELECT threshold FROM {$table} WHERE product_id = %d AND variation_id = %d LIMIT 1",
            (int) $product_id,
            (int) $variation_id
        );
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $threshold = $wpdb->get_var($sql);
        // phpcs:enable

        if ($threshold === null) {
            return null;
        }

        return (int) $threshold;
    }

    public function set_threshold(int $product_id, int $threshold, int $variation_id = 0): void
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $table = $wpdb->prefix . 'low_stock_thresholds';

        $now = new DateTimeImmutable('now', new DateTimeZone(wp_timezone_string()));

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->replace(
            $table,
            [
                'product_id'   => $product_id,
                'variation_id' => $variation_id,
                'threshold'    => $threshold,
                'created_at'   => $now->format('Y-m-d H:i:s'),
                'updated_at'   => $now->format('Y-m-d H:i:s'),
            ],
            [
                '%d',
                '%d',
                '%d',
                '%s',
                '%s',
            ]
        );
    }

    public function delete_threshold(int $product_id, int $variation_id = 0): void
    {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $table = $wpdb->prefix . 'low_stock_thresholds';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->delete(
            $table,
            [
                'product_id'   => $product_id,
                'variation_id' => $variation_id,
            ],
            [
                '%d',
                '%d',
            ]
        );
    }

    public function truncate_all(): void
    {
        global $wpdb;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $table = $wpdb->prefix . 'low_stock_thresholds';
        $wpdb->query("DELETE FROM {$table}");
        // phpcs:enable
    }

    public function count_all(): int
    {
        global $wpdb;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $table = $wpdb->prefix . 'low_stock_thresholds';
        $count = $wpdb->get_var("SELECT COUNT(*) FROM {$table}");
        // phpcs:enable

        return (int) $count;
    }

    /**
     * @return array<int, array<string, int>>
     */
    public function all(): array
    {
        global $wpdb;

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $table = $wpdb->prefix . 'low_stock_thresholds';
        // Limita a 10000 record per evitare problemi di memoria con store molto grandi
        // In pratica, store con più di 10000 prodotti con threshold personalizzati sono rari
        $rows  = $wpdb->get_results(
            "SELECT product_id, variation_id, threshold FROM {$table} ORDER BY product_id, variation_id LIMIT 10000",
            ARRAY_A
        );
        // phpcs:enable

        return array_map(
            static function (array $row): array {
                return [
                    'product_id'   => (int) $row['product_id'],
                    'variation_id' => (int) $row['variation_id'],
                    'threshold'    => (int) $row['threshold'],
                ];
            },
            $rows ?? []
        );
    }
}

