<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Repositories;

// 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.

/**
 * Repository for link tag terms backed by the prli_tags table.
 */
class Tags
{
    /**
     * Bulk lookup of tag name/id pairs for a set of link ids.
     * Returns `[linkId => [{id, name}, …]]`, alphabetized by name.
     *
     * @param  int[] $linkIds The link ids to look up tags for.
     * @return array<int, array<int, array{id:int, name:string}>>
     */
    public function namesForLinks(array $linkIds): array
    {
        $ids = array_values(array_unique(array_filter(array_map('intval', $linkIds))));
        if (!$ids) {
            return [];
        }
        global $wpdb;
        $placeholders = implode(',', array_fill(0, count($ids), '%d'));
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared
        $sql = "SELECT lt.link_id, t.id AS term_id, t.name
                   FROM {$wpdb->prefix}prli_link_terms lt
                   INNER JOIN {$wpdb->prefix}prli_tags t ON t.id = lt.term_id
                  WHERE lt.taxonomy = 'tag'
                    AND lt.link_id IN ({$placeholders})
                  ORDER BY t.name ASC";
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = (array) $wpdb->get_results($wpdb->prepare($sql, ...$ids), ARRAY_A);
        $out  = [];
        foreach ($rows as $row) {
            $lid         = (int) $row['link_id'];
            $out[$lid][] = [
                'id'   => (int) $row['term_id'],
                'name' => (string) $row['name'],
            ];
        }
        return $out;
    }

    /**
     * Cheap existence check. Used by the admin bootstrap to decide
     * whether to surface tag UI (menu item, link column, link-form
     * picker, bulk actions). Tags are deprecated — new sites never see
     * the UI; legacy installs with at least one tag keep seeing it.
     */
    public static function hasAny(): bool
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        return (bool) $wpdb->get_var(
            "SELECT 1 FROM {$wpdb->prefix}prli_tags LIMIT 1"
        );
    }

    /**
     * Return all tags with their link counts, ordered by name.
     *
     * @return array<int, array<string, mixed>>
     */
    public function all(): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            "SELECT t.id, t.name, t.slug,
                    COALESCE(lt.cnt, 0) AS count
             FROM {$wpdb->prefix}prli_tags t
             LEFT JOIN (
               SELECT term_id, COUNT(*) AS cnt
               FROM {$wpdb->prefix}prli_link_terms
               WHERE taxonomy = 'tag'
               GROUP BY term_id
             ) lt ON lt.term_id = t.id
             ORDER BY t.name ASC",
            ARRAY_A
        ) ?: [];
        return array_map([self::class, 'hydrate'], $rows);
    }

    /**
     * Create a new tag from the given data.
     *
     * @param  array<string, mixed> $data The tag fields (name, slug).
     * @return array<string, mixed>
     */
    public function create(array $data): array
    {
        global $wpdb;
        $name = trim((string) ($data['name'] ?? ''));
        if ($name === '') {
            return ['error' => 'name_required'];
        }
        $row = [
            'name'  => $name,
            'slug'  => (string) ($data['slug'] ?? sanitize_title($name)),
            'count' => 0,
        ];
        $wpdb->insert($wpdb->prefix . 'prli_tags', $row);
        $row['id'] = (int) $wpdb->insert_id;
        return self::hydrate($row);
    }

    /**
     * Update an existing tag by id with the given fields.
     *
     * @param  integer              $id   The tag id to update.
     * @param  array<string, mixed> $data The tag fields to patch.
     * @return array<string, mixed>|null
     */
    public function update(int $id, array $data): ?array
    {
        global $wpdb;
        $patch = [];
        foreach (['name', 'slug'] as $key) {
            if (array_key_exists($key, $data)) {
                $patch[$key] = (string) $data[$key];
            }
        }
        if (!$patch) {
            return null;
        }
        $wpdb->update($wpdb->prefix . 'prli_tags', $patch, ['id' => $id]);
        $row = $wpdb->get_row(
            $wpdb->prepare("SELECT * FROM {$wpdb->prefix}prli_tags WHERE id = %d", $id),
            ARRAY_A
        );
        return is_array($row) ? self::hydrate($row) : null;
    }

    /**
     * Delete a tag by id and detach it from any links.
     *
     * @param  integer $id The tag id to delete.
     * @return boolean
     */
    public function delete(int $id): bool
    {
        global $wpdb;
        $wpdb->delete(
            $wpdb->prefix . 'prli_link_terms',
            [
                'term_id'  => $id,
                'taxonomy' => 'tag',
            ]
        );
        return (bool) $wpdb->delete($wpdb->prefix . 'prli_tags', ['id' => $id]);
    }

    /**
     * Return the tag with the given slug, creating it if it doesn't exist.
     * Used by the CSV importer to resolve slug strings to IDs without requiring
     * the user to pre-create every tag.
     *
     * @param  string $slug The tag slug to find or create.
     * @param  string $name Optional display name used when creating a new tag.
     * @return array<string, mixed>
     */
    public function findOrCreateBySlug(string $slug, string $name = ''): array
    {
        global $wpdb;
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT id, name, slug FROM {$wpdb->prefix}prli_tags WHERE slug = %s LIMIT 1",
                $slug
            ),
            ARRAY_A
        );
        if (is_array($row)) {
            return self::hydrate(array_merge(['count' => 0], $row));
        }
        return $this->create([
            'name' => $name !== '' ? $name : $slug,
            'slug' => $slug,
        ]);
    }

    /**
     * Normalize a raw database row into the tag array shape.
     *
     * @param  array<string, mixed> $row The raw database row.
     * @return array<string, mixed>
     */
    private static function hydrate(array $row): array
    {
        return [
            'id'    => (int) $row['id'],
            'name'  => (string) $row['name'],
            'slug'  => (string) $row['slug'],
            'count' => (int) ($row['count'] ?? 0),
        ];
    }
}
