<?php
declare(strict_types=1);
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- This repository uses safely generated dynamic placeholder lists with $wpdb->prepare().
defined('ABSPATH') || exit;

class DSProofwp_Review_Media_Repository
{
    private wpdb $wpdb;
    private string $table;

    public function __construct(wpdb $wpdb)
    {
        $this->wpdb  = $wpdb;
        $this->table = $wpdb->prefix . 'dsproofwp_review_media';
    }

    public function find_by_review_ids(array $review_ids): array
    {
        $review_ids = array_values(array_filter(array_map('absint', $review_ids)));

        if ($review_ids === []) {
            return [];
        }

        $placeholders = implode(', ', array_fill(0, count($review_ids), '%d'));
        $query        = $this->wpdb->prepare(
            "SELECT id, review_id, attachment_id, media_type, url, created_at
             FROM {$this->table}
             WHERE review_id IN ({$placeholders})
             ORDER BY id ASC",
            $review_ids
        );

        $rows    = $this->wpdb->get_results($query, ARRAY_A);
        $grouped = [];

        foreach ($rows as $row) {
            $review_id = (int) $row['review_id'];

            if (! isset($grouped[$review_id])) {
                $grouped[$review_id] = [];
            }

            $grouped[$review_id][] = [
                'id'            => (int) $row['id'],
                'review_id'     => $review_id,
                'attachment_id' => $row['attachment_id'] !== null ? (int) $row['attachment_id'] : null,
                'media_type'    => (string) $row['media_type'],
                'url'           => (string) $row['url'],
                'created_at'    => (string) $row['created_at'],
            ];
        }

        return $grouped;
    }

    public function replace_for_review(int $review_id, array $media_items): array
    {
        $review_id   = absint($review_id);
        $media_items = $this->sanitize_media_items($media_items);
        $current_media = $this->find_by_review_ids([$review_id])[$review_id] ?? [];
        $matched_ids   = [];
        $media_to_add  = [];

        foreach ($media_items as $item) {
            $matched_media_id = null;

            foreach ($current_media as $existing_item) {
                if (in_array($existing_item['id'], $matched_ids, true)) {
                    continue;
                }

                if ($existing_item['media_type'] === $item['media_type'] && $existing_item['url'] === $item['url']) {
                    $matched_media_id = (int) $existing_item['id'];
                    break;
                }
            }

            if ($matched_media_id !== null) {
                $matched_ids[] = $matched_media_id;
                continue;
            }

            $media_to_add[] = [
                'review_id'     => $review_id,
                'attachment_id' => $item['attachment_id'] ?? null,
                'media_type'    => $item['media_type'],
                'url'           => $item['url'],
            ];
        }

        $media_to_remove = array_values(array_map(
            static function (array $existing_item): int {
                return (int) $existing_item['id'];
            },
            array_filter(
                $current_media,
                static function (array $existing_item) use ($matched_ids): bool {
                    return ! in_array((int) $existing_item['id'], $matched_ids, true);
                }
            )
        ));

        if ($media_to_remove !== []) {
            $this->delete_media_by_ids($review_id, $media_to_remove);
        }

        if ($media_to_add !== []) {
            $this->insert_media_items($media_to_add);
        }

        $grouped = $this->find_by_review_ids([$review_id]);

        return $grouped[$review_id] ?? [];
    }

    public function store_for_review(int $review_id, array $media_items): array
    {
        $review_id   = absint($review_id);
        $media_items = $this->sanitize_media_items($media_items);
        $insert_data = array_map(
            static function (array $item) use ($review_id): array {
                return [
                    'review_id'     => $review_id,
                    'attachment_id' => $item['attachment_id'] ?? null,
                    'media_type'    => $item['media_type'],
                    'url'           => $item['url'],
                ];
            },
            $media_items
        );

        $this->insert_media_items($insert_data);

        $grouped = $this->find_by_review_ids([$review_id]);

        return $grouped[$review_id] ?? [];
    }

    public function delete_by_review_id(int $review_id): void
    {
        $this->wpdb->delete($this->table, ['review_id' => $review_id], ['%d']);
    }

    private function delete_media_by_ids(int $review_id, array $media_ids): void
    {
        $media_ids = array_values(array_filter(array_map('absint', $media_ids)));

        if ($media_ids === []) {
            return;
        }

        $placeholders = implode(', ', array_fill(0, count($media_ids), '%d'));
        $query        = $this->wpdb->prepare(
            "DELETE FROM {$this->table}
             WHERE review_id = %d
             AND id IN ({$placeholders})",
            array_merge([$review_id], $media_ids)
        );

        $this->wpdb->query($query);
    }

    /**
     * @param array<int, array{review_id:int, attachment_id:?int, media_type:string, url:string}> $insert_data
     */
    private function insert_media_items(array $insert_data): void
    {
        if ($insert_data === []) {
            return;
        }

        $placeholders = implode(', ', array_fill(0, count($insert_data), '(%d, %d, %s, %s)'));
        $values       = [];

        foreach ($insert_data as $data) {
            $values[] = $data['review_id'];
            $values[] = $data['attachment_id'] !== null ? (int) $data['attachment_id'] : null;
            $values[] = $data['media_type'];
            $values[] = $data['url'];
        }

        $query = $this->wpdb->prepare(
            "INSERT INTO {$this->table} (review_id, attachment_id, media_type, url)
             VALUES {$placeholders}",
            $values
        );

        $this->wpdb->query($query);
    }

    private function sanitize_media_items(array $media_items): array
    {
        $sanitized = [];

        foreach ($media_items as $item) {
            if (! is_array($item)) {
                continue;
            }

            $media_type    = isset($item['media_type']) ? sanitize_key((string) $item['media_type']) : '';
            $url           = isset($item['url']) ? esc_url_raw((string) $item['url']) : '';
            $attachment_id = isset($item['attachment_id']) ? absint($item['attachment_id']) : null;

            if ($url === '' || ! in_array($media_type, ['image', 'video'], true)) {
                continue;
            }

            $sanitized[] = [
                'attachment_id' => $attachment_id ?: null,
                'media_type'    => $media_type,
                'url'           => $url,
            ];
        }

        return $sanitized;
    }
}
