<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Tools;

defined('ABSPATH') || exit;

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared
// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
// phpcs:disable WordPress.DB.DirectDatabaseQuery.NoCaching
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_key
// phpcs:disable WordPress.DB.SlowDBQuery.slow_db_query_meta_value
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter
// Custom plugin tables (prli_*): table names interpolated from $wpdb->prefix (trusted),
// user values bind through $wpdb->prepare(). No caching: these tables are the source
// of truth for click/redirect data and must read-through. "meta_key"/"meta_value" here
// refer to our own prli_link_metas table, not wp_postmeta.
use PrettyLinks\Pro\Replacements\KeywordRepository;
use PrettyLinks\Pro\Repositories\Categories;
use PrettyLinks\Pro\Repositories\Tags;
use PrettyLinks\Repositories\LinkMetas;
use PrettyLinks\Repositories\Links;

/**
 * Extends the Lite CSV import/export with Pro-specific columns:
 * link_categories, link_tags, delay, keywords, url_replacements.
 *
 * All multi-value columns use pipe (|) as the separator. Pipes never
 * appear in URLs, slugs, or normal keyword text, so a pipe-only convention
 * keeps users from accidentally breaking a CSV by typing keywords like
 * "Hello, World" — which is otherwise a real footgun on a comma-delimited
 * file format.
 */
class CsvExtension
{
    private const META_URL_REPLACEMENTS = 'prli-url-replacements';

    /**
     * Per-instance memoization of the term id → slug maps for category and
     * tag taxonomies. Hydrated on first chunk of an export and reused for
     * every subsequent chunk in that same request — terms don't change
     * mid-export, and the lookup is constant across chunks (it's "every
     * term", not "every link's terms"). Without this, a 10-chunk export
     * runs 20 redundant SELECTs against `prli_categories` / `prli_tags`.
     *
     * @var array<int, string>|null
     */
    private ?array $catSlugsMemo = null;

    /**
     * Per-instance memoization of the tag term id → slug map.
     *
     * @var array<int, string>|null
     */
    private ?array $tagSlugsMemo = null;

    /**
     * Register the CSV import/export filters and actions.
     *
     * @return void
     */
    public function init(): void
    {
        add_filter('prli_csv_export_columns', [$this, 'addColumns']);
        add_filter('prli_csv_export_rows', [$this, 'populateRows'], 10, 2);
        add_action('prli_csv_import_link_saved', [$this, 'handleSaved'], 10, 2);
        add_filter('prli_csv_sample_rows', [$this, 'addSampleRows'], 10, 2);
    }

    /**
     * Append the Pro-specific export column headers.
     *
     * @param  string[] $columns The existing export column headers.
     * @return string[]
     */
    public function addColumns(array $columns): array
    {
        return array_merge($columns, ['link_categories', 'link_tags', 'delay', 'keywords', 'url_replacements']);
    }

    /**
     * Bulk-populate Pro columns across all export rows in three queries total
     * (one for all category slugs, one for all tag slugs, one for delay metas).
     *
     * @param  array<int, array<string, string>> $rows    The export rows to populate.
     * @param  string[]                          $columns The export column headers.
     * @return array<int, array<string, string>>
     */
    public function populateRows(array $rows, array $columns): array
    {
        if (!$rows) {
            return $rows;
        }

        $linkIds = array_column($rows, 'id');

        $termsMap = (new Links())->getTermsForLinks(array_map('intval', $linkIds));
        // Memoize the constant maps across chunks within a single export.
        $catSlugs      = $this->catSlugsMemo ??= $this->allTermSlugs('category');
        $tagSlugs      = $this->tagSlugsMemo ??= $this->allTermSlugs('tag');
        $delayMap      = $this->bulkMeta($linkIds, 'delay');
        $keywordsMap   = (new KeywordRepository())->textForLinks(array_map('intval', $linkIds));
        $urlReplaceMap = $this->bulkMetaList($linkIds, self::META_URL_REPLACEMENTS);

        foreach ($rows as &$row) {
            $id    = (int) ($row['id'] ?? 0);
            $terms = $termsMap[$id] ?? [
                'categories' => [],
                'tags'       => [],
            ];

            $catNames = array_values(array_filter(array_map(
                static function (int $tid) use ($catSlugs): string {
                    return $catSlugs[$tid] ?? '';
                },
                $terms['categories']
            )));
            $tagNames = array_values(array_filter(array_map(
                static function (int $tid) use ($tagSlugs): string {
                    return $tagSlugs[$tid] ?? '';
                },
                $terms['tags']
            )));

            $row['link_categories']  = implode('|', $catNames);
            $row['link_tags']        = implode('|', $tagNames);
            $row['delay']            = (string) ($delayMap[$id] ?? '');
            $row['keywords']         = implode('|', $keywordsMap[$id] ?? []);
            $row['url_replacements'] = implode('|', $urlReplaceMap[$id] ?? []);
        }
        unset($row);

        return $rows;
    }

    /**
     * Append Pro-specific sample rows demonstrating categories, tags, and delay.
     *
     * @param  array<int, array<string, string>> $rows    The existing sample rows.
     * @param  string[]                          $columns The export column headers.
     * @return array<int, array<string, string>>
     */
    public function addSampleRows(array $rows, array $columns): array
    {
        $rows[] = [
            'id'               => '',
            'slug'             => 'sample-pro-cats-tags',
            'url'              => 'https://example.com/product-d',
            'name'             => 'Sample Pro Link (categories + tags)',
            'description'      => 'Multi-value cells use pipe (|) as the separator.',
            'redirect_type'    => '301',
            'param_forwarding' => '1',
            'track_me'         => '1',
            'nofollow'         => '1',
            'sponsored'        => '1',
            'new_window'       => '0',
            'link_categories'  => 'affiliates|deals',
            'link_tags'        => 'featured|sale',
            'delay'            => '',
            'keywords'         => 'best widget|top widget',
            'url_replacements' => '',
        ];
        $rows[] = [
            'id'               => '',
            'slug'             => 'sample-pro-delay',
            'url'              => 'https://example.com/product-e',
            'name'             => 'Sample Pro Link (meta-refresh + delay + URL replacements)',
            'description'      => 'delay is seconds before redirect fires. Multi-value cells use pipe (|).',
            'redirect_type'    => 'metarefresh',
            'param_forwarding' => '0',
            'track_me'         => '1',
            'nofollow'         => '0',
            'sponsored'        => '0',
            'new_window'       => '0',
            'link_categories'  => 'affiliates',
            'link_tags'        => '',
            'delay'            => '3',
            'keywords'         => '',
            'url_replacements' => 'https://example.com/old-url|https://example.com/legacy',
        ];
        return $rows;
    }

    /**
     * After a link is created or updated via import, apply any Pro-specific
     * columns present in the CSV row.
     *
     * @param  integer               $linkId The saved link id.
     * @param  array<string, string> $row    The CSV row that was imported.
     * @return void
     */
    public function handleSaved(int $linkId, array $row): void
    {
        $linksRepo = new Links();

        if (isset($row['link_categories'])) {
            $cats   = new Categories();
            $slugs  = array_filter(array_map('trim', explode('|', $row['link_categories'])));
            $catIds = [];
            foreach ($slugs as $slug) {
                $cat = $cats->findOrCreateBySlug($slug);
                if (!empty($cat['id'])) {
                    $catIds[] = (int) $cat['id'];
                }
            }
            $linksRepo->setTerms($linkId, 'category', $catIds);
        }

        if (isset($row['link_tags'])) {
            $tags   = new Tags();
            $slugs  = array_filter(array_map('trim', explode('|', $row['link_tags'])));
            $tagIds = [];
            foreach ($slugs as $slug) {
                $tag = $tags->findOrCreateBySlug($slug);
                if (!empty($tag['id'])) {
                    $tagIds[] = (int) $tag['id'];
                }
            }
            $linksRepo->setTerms($linkId, 'tag', $tagIds);
        }

        if (isset($row['delay']) && $row['delay'] !== '') {
            (new LinkMetas())->set($linkId, 'delay', (string) (int) $row['delay']);
        }

        if (isset($row['keywords'])) {
            $keywords = self::parseList($row['keywords']);
            // KeywordRepository::setForLink (wired by Pro Bootstrap on this
            // action) handles diff/delete/insert and post-cache invalidation.
            do_action('prli_link_save_keywords', $linkId, $keywords);
        }

        if (isset($row['url_replacements'])) {
            $urls = [];
            foreach (self::parseList($row['url_replacements']) as $u) {
                $u = esc_url_raw($u);
                if ($u !== '' && !in_array($u, $urls, true)) {
                    $urls[] = $u;
                }
            }
            $previous = $this->getUrlReplacements($linkId);
            $this->setUrlReplacements($linkId, $urls);
            do_action('prli_link_save_url_replacements', $linkId, $urls, $previous);
        }
    }

    /**
     * Split a CSV cell on pipes, trim, drop empties, dedup.
     *
     * @param  string $value The raw pipe-delimited cell value.
     * @return string[]
     */
    private static function parseList(string $value): array
    {
        $out = [];
        foreach (explode('|', $value) as $item) {
            $item = trim($item);
            if ($item !== '' && !in_array($item, $out, true)) {
                $out[] = $item;
            }
        }
        return $out;
    }

    /**
     * Replace the per-link URL replacement list. Mirrors LinkPayloadExtension's
     * private setUrlReplacements — same meta_key, same multi-row layout with
     * meta_order preserving order so the CSV round-trips identically.
     *
     * @param  integer  $linkId The link id to write replacements for.
     * @param  string[] $urls   The ordered list of replacement URLs.
     * @return void
     */
    private function setUrlReplacements(int $linkId, array $urls): void
    {
        global $wpdb;
        $table = $wpdb->prefix . 'prli_link_metas';
        $wpdb->delete($table, [
            'link_id'  => $linkId,
            'meta_key' => self::META_URL_REPLACEMENTS,
        ]);
        if (!$urls) {
            return;
        }
        $now = current_time('mysql', true);
        foreach ($urls as $i => $url) {
            $wpdb->insert($table, [
                'link_id'    => $linkId,
                'meta_key'   => self::META_URL_REPLACEMENTS,
                'meta_value' => $url,
                'meta_order' => (int) $i,
                'created_at' => $now,
            ]);
        }
    }

    /**
     * Read the ordered per-link URL replacement list.
     *
     * @param  integer $linkId The link id to read replacements for.
     * @return string[]
     */
    private function getUrlReplacements(int $linkId): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'prli_link_metas';
        $rows  = (array) $wpdb->get_col(
            $wpdb->prepare(
                "SELECT meta_value FROM {$table}
                 WHERE link_id = %d AND meta_key = %s
                 ORDER BY meta_order ASC, id ASC",
                $linkId,
                self::META_URL_REPLACEMENTS
            )
        );
        return array_map('strval', $rows);
    }

    /**
     * Bulk-fetch a multi-row meta key (one row per value, ordered by meta_order)
     * for a list of link IDs. Used for URL replacements.
     *
     * @param  string[]|int[] $linkIds The link ids to fetch meta for.
     * @param  string         $key     The meta key to fetch.
     * @return array<int, string[]>  link_id → ordered values
     */
    private function bulkMetaList(array $linkIds, string $key): array
    {
        if (!$linkIds) {
            return [];
        }
        global $wpdb;
        $ids = array_values(array_unique(array_filter(array_map('intval', $linkIds))));
        if (!$ids) {
            return [];
        }
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $rows         = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT link_id, meta_value
                   FROM {$wpdb->prefix}prli_link_metas
                  WHERE meta_key = %s AND link_id IN ({$placeholders})
                  ORDER BY link_id ASC, meta_order ASC, id ASC",
                ...array_merge([$key], $ids)
            ),
            ARRAY_A
        ) ?: [];
        $map          = [];
        foreach ($rows as $r) {
            $id         = (int) $r['link_id'];
            $map[$id]   = $map[$id] ?? [];
            $map[$id][] = (string) $r['meta_value'];
        }
        return $map;
    }

    /**
     * Build an id → slug map for every term in the given taxonomy.
     *
     * @param  string $taxonomy The taxonomy to map (`category` or `tag`).
     * @return array<int, string>
     */
    private function allTermSlugs(string $taxonomy): array
    {
        global $wpdb;
        $table = $taxonomy === 'category'
            ? $wpdb->prefix . 'prli_categories'
            : $wpdb->prefix . 'prli_tags';
        $rows  = $wpdb->get_results("SELECT id, slug FROM {$table}", ARRAY_A) ?: [];
        $map   = [];
        foreach ($rows as $r) {
            $map[(int) $r['id']] = (string) $r['slug'];
        }
        return $map;
    }

    /**
     * Fetch one meta key for a list of link IDs in a single query.
     *
     * @param  string[]|int[] $linkIds The link ids to fetch meta for.
     * @param  string         $key     The meta key to fetch.
     * @return array<int, string>  link_id → meta_value
     */
    private function bulkMeta(array $linkIds, string $key): array
    {
        if (!$linkIds) {
            return [];
        }
        global $wpdb;
        $ids          = array_values(array_unique(array_filter(array_map('intval', $linkIds))));
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        $rows         = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT link_id, meta_value FROM {$wpdb->prefix}prli_link_metas
                  WHERE meta_key = %s AND link_id IN ({$placeholders})",
                ...array_merge([$key], $ids)
            ),
            ARRAY_A
        ) ?: [];
        $map          = [];
        foreach ($rows as $r) {
            $map[(int) $r['link_id']] = (string) $r['meta_value'];
        }
        return $map;
    }
}
