<?php
defined('ABSPATH') || exit;

/**
 * Persistent offer ID translation table.
 *
 * When this plugin is installed on a site whose products already exist in
 * Google Merchant Center under different offer IDs (e.g. pushed from a stock
 * system using internal IDs), this class maps the offer ID this plugin would
 * generate ("local") to the one Google already knows ("google").
 *
 * The mapping is permanent — removing a row will cause the next sync to
 * create a new product under the local offer ID and the old Google listing
 * will eventually be pruned, losing any accumulated performance history.
 *
 * Table: {prefix}jra_merchant_offer_id_map
 *   local_offer_id  — what this plugin generates, e.g. "SKU-123"   (PK)
 *   google_offer_id — what Google currently knows it as, e.g. "SKU-456"  (UQ)
 *   note            — optional free-text label (e.g. "stock system import")
 *   created_at      — UTC datetime of import
 */
class JRA_Merchant_Offer_Id_Map {

    /** In-memory cache: [ local_offer_id => google_offer_id ] */
    private array $cache;

    public function __construct() {
        $this->cache = $this->loadAll();
    }

    // -------------------------------------------------------------------------
    // Lookup (used by product mapper on every push)
    // -------------------------------------------------------------------------

    /**
     * Returns the Google offer ID to use, or null if no mapping exists
     * (in which case the caller should use the local offer ID as-is).
     */
    public function lookup(string $localOfferId): ?string {
        return $this->cache[$localOfferId] ?? null;
    }

    /**
     * Resolve an offer ID and return full context for display.
     *
     * @return array{local: string, effective: string, is_mapped: bool}
     */
    public function resolve(string $localOfferId): array {
        $mapped = $this->cache[$localOfferId] ?? null;
        return [
            'local'     => $localOfferId,
            'effective' => $mapped ?? $localOfferId,
            'is_mapped' => $mapped !== null,
        ];
    }

    // -------------------------------------------------------------------------
    // Read
    // -------------------------------------------------------------------------

    public function getAll(int $limit = 100, int $offset = 0): array {
        global $wpdb;
        $table = $wpdb->prefix . 'jra_merchant_offer_id_map';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT local_offer_id, google_offer_id, note, created_at FROM `{$table}` ORDER BY created_at DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
                $limit,
                $offset
            ),
            ARRAY_A
        ) ?: [];
    }

    public function count(): int {
        global $wpdb;
        $table = $wpdb->prefix . 'jra_merchant_offer_id_map';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");
    }

    // -------------------------------------------------------------------------
    // Write
    // -------------------------------------------------------------------------

    /**
     * Add or update a single mapping row.
     * If local_offer_id already exists the row is replaced.
     */
    /**
     * Add or update a single mapping row.
     * Returns null on success, or an error string on failure.
     */
    public function add(string $localOfferId, string $googleOfferId, string $note = ''): ?string {
        global $wpdb;
        $table  = $wpdb->prefix . 'jra_merchant_offer_id_map';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->replace(
            $table,
            [
                'local_offer_id'  => $localOfferId,
                'google_offer_id' => $googleOfferId,
                'note'            => $note,
                'created_at'      => current_time('mysql', true),
            ],
            ['%s', '%s', '%s', '%s']
        );
        if ($result !== false) {
            $this->cache[$localOfferId] = $googleOfferId;
            return null;
        }
        $dbError = $wpdb->last_error;
        if ($dbError && stripos($dbError, 'duplicate') !== false) {
            return "Google offer ID '{$googleOfferId}' is already mapped to a different local ID.";
        }
        if ($dbError && stripos($dbError, "doesn't exist") !== false) {
            return 'Database table missing — try deactivating and reactivating the plugin.';
        }
        return $dbError ?: 'Unknown database error.';
    }

    /**
     * Delete a mapping row by its local offer ID.
     */
    public function delete(string $localOfferId): bool {
        global $wpdb;
        $table  = $wpdb->prefix . 'jra_merchant_offer_id_map';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->delete($table, ['local_offer_id' => $localOfferId], ['%s']);
        if ($result) {
            unset($this->cache[$localOfferId]);
        }
        return (bool) $result;
    }

    /**
     * Import an array of rows. Each row must have keys:
     *   local_offer_id, google_offer_id, [note]
     *
     * Rows with blank local or google IDs are silently skipped.
     * Duplicate local_offer_id rows replace the existing entry.
     *
     * @return array{imported: int, skipped: int, errors: string[]}
     */
    public function importRows(array $rows, string $defaultNote = ''): array {
        $imported = 0;
        $skipped  = 0;
        $errors   = [];

        foreach ($rows as $i => $row) {
            $local  = trim((string) ($row['local_offer_id']  ?? ''));
            $google = trim((string) ($row['google_offer_id'] ?? ''));
            $note   = trim((string) ($row['note']            ?? $defaultNote));

            if ($local === '' || $google === '') {
                $skipped++;
                continue;
            }

            $err = $this->add($local, $google, $note);
            if ($err === null) {
                $imported++;
            } else {
                $errors[] = 'Row ' . ($i + 1) . " ({$local} → {$google}): {$err}";
            }
        }

        return compact('imported', 'skipped', 'errors');
    }

    /**
     * Parse a CSV string and import it.
     * Expected columns (header row required): local_offer_id, google_offer_id[, note]
     *
     * @return array{imported: int, skipped: int, errors: string[], parse_error: string}
     */
    public function importCsv(string $csvContent, string $defaultNote = ''): array {
        $lines      = str_getcsv(trim($csvContent), "\n");
        $parseError = '';
        $rows       = [];

        if (empty($lines)) {
            return ['imported' => 0, 'skipped' => 0, 'errors' => [], 'parse_error' => 'CSV is empty.'];
        }

        // Parse header
        $header = array_map('trim', str_getcsv(array_shift($lines)));
        $colLocal  = array_search('local_offer_id',  $header, true);
        $colGoogle = array_search('google_offer_id', $header, true);
        $colNote   = array_search('note',            $header, true);

        if ($colLocal === false || $colGoogle === false) {
            return [
                'imported'    => 0,
                'skipped'     => 0,
                'errors'      => [],
                'parse_error' => 'CSV must have columns: local_offer_id, google_offer_id (and optionally note).',
            ];
        }

        foreach ($lines as $line) {
            $line = trim($line);
            if ($line === '') continue;
            $cols   = str_getcsv($line);
            $rows[] = [
                'local_offer_id'  => $cols[$colLocal]  ?? '',
                'google_offer_id' => $cols[$colGoogle] ?? '',
                'note'            => $colNote !== false ? ($cols[$colNote] ?? '') : $defaultNote,
            ];
        }

        $result               = $this->importRows($rows, $defaultNote);
        $result['parse_error'] = $parseError;
        return $result;
    }

    // -------------------------------------------------------------------------
    // Schema
    // -------------------------------------------------------------------------

    public static function createTable(): void {
        global $wpdb;
        $table   = $wpdb->prefix . 'jra_merchant_offer_id_map';
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            local_offer_id  varchar(100) NOT NULL,
            google_offer_id varchar(100) NOT NULL,
            note            varchar(255) NOT NULL DEFAULT '',
            created_at      datetime     NOT NULL,
            PRIMARY KEY  (local_offer_id),
            UNIQUE KEY  uq_google_offer_id (google_offer_id)
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql);
    }

    // -------------------------------------------------------------------------
    // Private
    // -------------------------------------------------------------------------

    private function loadAll(): array {
        global $wpdb;
        $table = $wpdb->prefix . 'jra_merchant_offer_id_map';
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows  = $wpdb->get_results(
            "SELECT local_offer_id, google_offer_id FROM `{$table}`", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            ARRAY_A
        ) ?: [];
        $map = [];
        foreach ($rows as $row) {
            $map[$row['local_offer_id']] = $row['google_offer_id'];
        }
        return $map;
    }
}
