<?php

namespace CleantalkSP\SpbctWP\VulnerabilityAlarm;

use CleantalkSP\SpbctWP\DB;

class PscCertifiedPluginsCacheRepository
{
    const SOURCE_INSTALLED = 'installed';
    const SOURCE_CATALOG = 'catalog';

    /**
     * Rewrite the whole cache. Truncate keeps the table free of leftovers from previous warm-ups.
     *
     * @param array<int, array{slug: string, source: string, sort_order: int, plugin_data: string, badge_data: string}> $rows
     * @return void
     */
    public static function replaceAll($rows)
    {
        global $wpdb;

        DB::getInstance()->execute('TRUNCATE TABLE ' . SPBC_TBL_PSC_PLUGINS_CACHE);

        $updated_at = time();

        foreach ($rows as $row) {
            $wpdb->replace(
                SPBC_TBL_PSC_PLUGINS_CACHE,
                [
                    'slug'        => $row['slug'],
                    'source'      => $row['source'],
                    'sort_order'  => (int) $row['sort_order'],
                    'plugin_data' => $row['plugin_data'],
                    'badge_data'  => $row['badge_data'],
                    'updated_at'  => $updated_at,
                ],
                ['%s', '%s', '%d', '%s', '%s', '%d']
            );
        }
    }

    /**
     * Installed plugins always go first, then the catalog ones in the order they came from the API.
     *
     * @param string|null $source
     * @param int $offset
     * @param int $limit
     * @return array<int, array<string, mixed>>
     */
    public static function getRows($source = null, $offset = 0, $limit = 0)
    {
        global $wpdb;

        $sql = 'SELECT slug, source, sort_order, plugin_data, badge_data, updated_at FROM ' . SPBC_TBL_PSC_PLUGINS_CACHE;
        $args = [];

        if ($source !== null) {
            $sql .= ' WHERE source = %s';
            $args[] = $source;
        }

        $sql .= " ORDER BY CASE source WHEN 'installed' THEN 0 ELSE 1 END ASC, sort_order ASC";

        if ($limit > 0) {
            $sql .= ' LIMIT %d OFFSET %d';
            $args[] = $limit;
            $args[] = (int) $offset;
        }

        $prepared = empty($args) ? $sql : $wpdb->prepare($sql, $args);
        $rows = DB::getInstance()->fetchAll($prepared);

        return is_array($rows) ? $rows : [];
    }

    /**
     * @param string|null $source
     * @return int
     */
    public static function countRows($source = null)
    {
        global $wpdb;

        $sql = 'SELECT COUNT(*) AS cnt FROM ' . SPBC_TBL_PSC_PLUGINS_CACHE;

        if ($source !== null) {
            $sql = $wpdb->prepare($sql . ' WHERE source = %s', $source);
        }

        $row = DB::getInstance()->fetch($sql);

        return isset($row->cnt) ? (int) $row->cnt : 0;
    }

    /**
     * @param string $slug
     * @param string $source
     * @param string $plugin_data
     * @return void
     */
    public static function updatePluginData($slug, $source, $plugin_data)
    {
        global $wpdb;

        $wpdb->update(
            SPBC_TBL_PSC_PLUGINS_CACHE,
            [
                'plugin_data' => $plugin_data,
                'updated_at'  => time(),
            ],
            [
                'slug'   => $slug,
                'source' => $source,
            ],
            ['%s', '%d'],
            ['%s', '%s']
        );
    }

    /**
     * @param array<int, string> $slugs
     * @return array<int, array<string, mixed>>
     */
    public static function getRowsBySlugs(array $slugs)
    {
        global $wpdb;

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

        $placeholders = implode(', ', array_fill(0, count($slugs), '%s'));
        $sql = 'SELECT slug, source, sort_order, plugin_data, badge_data, updated_at FROM ' . SPBC_TBL_PSC_PLUGINS_CACHE
            . ' WHERE slug IN (' . $placeholders . ')'
            . " ORDER BY CASE source WHEN 'installed' THEN 0 ELSE 1 END ASC, sort_order ASC";

        $rows = DB::getInstance()->fetchAll($wpdb->prepare($sql, $slugs));

        return is_array($rows) ? $rows : [];
    }
}