<?php

declare(strict_types=1);

namespace PrettyLinks\Pro\Rotation;

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

/**
 * CRUD for `prli_link_rotations`. Rotations are never edited in
 * isolation — the admin UI always saves the complete list for a link, so
 * `replaceAll()` is the normal write path. Individual `create` / `update`
 * / `delete` methods exist for completeness but the controller prefers
 * the atomic replace to keep positions and weights consistent.
 */
final class RotationsRepository
{
    /**
     * Returns all rotation rows for a link, ordered by position.
     *
     * @param  integer $linkId Link ID to load rotations for.
     * @return array<int, array<string, mixed>>
     */
    public function forLink(int $linkId): array
    {
        global $wpdb;
        $rows = $wpdb->get_results(
            $wpdb->prepare(
                "SELECT id, link_id, url, weight, position
                 FROM {$wpdb->prefix}prli_link_rotations
                 WHERE link_id = %d
                 ORDER BY position ASC, id ASC",
                $linkId
            ),
            ARRAY_A
        ) ?: [];

        return array_map(
            static fn(array $r): array => [
                'id'       => (int) $r['id'],
                'link_id'  => (int) $r['link_id'],
                'url'      => (string) $r['url'],
                'weight'   => (int) $r['weight'],
                'position' => (int) $r['position'],
            ],
            $rows
        );
    }

    /**
     * Replace the full list for a link atomically. Rows missing from
     * `$entries` are deleted; entries with ids are updated in place;
     * ids that don't exist are inserted. Positions re-sequenced 0..n-1
     * in array order, regardless of input ordering.
     *
     * @param  integer                                                      $linkId  Link ID to replace rotations for.
     * @param  array<int, array{url?: string, weight?: int, id?: int|null}> $entries Full ordered list of rotation entries.
     * @return array<int, array<string, mixed>>
     */
    public function replaceAll(int $linkId, array $entries): array
    {
        global $wpdb;
        $table = $wpdb->prefix . 'prli_link_rotations';

        $keepIds = [];
        $pos     = 0;
        foreach ($entries as $entry) {
            $url = trim(str_replace(["\r", "\n"], '', (string) ($entry['url'] ?? '')));
            if ($url === '') {
                continue;
            }
            $weight = max(1, (int) ($entry['weight'] ?? 1));
            $id     = isset($entry['id']) ? (int) $entry['id'] : 0;

            if ($id > 0) {
                $wpdb->update(
                    $table,
                    [
                        'url'      => $url,
                        'weight'   => $weight,
                        'position' => $pos,
                    ],
                    [
                        'id'      => $id,
                        'link_id' => $linkId,
                    ],
                    ['%s', '%d', '%d'],
                    ['%d', '%d']
                );
                $keepIds[] = $id;
            } else {
                $wpdb->insert(
                    $table,
                    [
                        'link_id'    => $linkId,
                        'url'        => $url,
                        'weight'     => $weight,
                        'position'   => $pos,
                        'created_at' => current_time('mysql', true),
                    ],
                    ['%d', '%s', '%d', '%d', '%s']
                );
                $newId = (int) $wpdb->insert_id;
                if ($newId > 0) {
                    $keepIds[] = $newId;
                }
            }
            ++$pos;
        }

        // Drop rows that weren't in the incoming set.
        if ($keepIds === []) {
            $wpdb->delete($table, ['link_id' => $linkId], ['%d']);
        } else {
            $placeholders = implode(',', array_fill(0, count($keepIds), '%d'));
            $params       = array_merge([$linkId], $keepIds);
            $wpdb->query(
                $wpdb->prepare(
                    "DELETE FROM {$table}
                     WHERE link_id = %d AND id NOT IN ({$placeholders})",
                    ...$params
                )
            );
        }

        return $this->forLink($linkId);
    }

    /**
     * Deletes all rotation rows for a link.
     *
     * @param  integer $linkId Link ID to delete rotations for.
     * @return integer
     */
    public function deleteForLink(int $linkId): int
    {
        global $wpdb;
        return (int) $wpdb->delete(
            $wpdb->prefix . 'prli_link_rotations',
            ['link_id' => $linkId],
            ['%d']
        );
    }
}
