<?php

namespace WPSN\Modules\IndexationRisk;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

final class IndexationRiskRepository
{
    private Logger $logger;

    public function __construct(Logger $logger)
    {
        $this->logger = $logger;
    }

    private function table(): string
    {
        global $wpdb;
        return $wpdb->prefix . 'wpsn_indexation_risk';
    }

    /**
     * @param array{post_id:int,post_type:string,url:string,title:string,score:int,inbound_links:int,word_count:int,in_sitemap:int,indexable:int,reasons?:mixed,scanned_at?:string} $row
     */
    public function upsert(array $row): void
    {
        global $wpdb;

        $postId = (int) ($row['post_id'] ?? 0);
        if ($postId <= 0) {
            return;
        }

        $postType = sanitize_key((string) ($row['post_type'] ?? ''));
        $url = esc_url_raw((string) ($row['url'] ?? ''));
        $title = sanitize_text_field((string) ($row['title'] ?? ''));

        $score = (int) ($row['score'] ?? 0);
        $score = max(0, min(100, $score));

        $inbound = max(0, (int) ($row['inbound_links'] ?? 0));
        $wc = max(0, (int) ($row['word_count'] ?? 0));
        $inSitemap = !empty($row['in_sitemap']) ? 1 : 0;
        $indexable = !empty($row['indexable']) ? 1 : 0;

        $reasonsJson = null;
        if (array_key_exists('reasons', $row)) {
            $reasonsJson = wp_json_encode($row['reasons']);
        }

        $scannedAt = isset($row['scanned_at']) ? (string) $row['scanned_at'] : '';
        if ($scannedAt === '') {
            $scannedAt = current_time('mysql', true);
        }

        $now = current_time('mysql', true);

        // Upsert via REPLACE (safe with PK on post_id).
        $res = $wpdb->replace(
            $this->table(),
            [
                'post_id' => $postId,
                'post_type' => $postType,
                'url' => $url,
                'title' => $title,
                'score' => $score,
                'inbound_links' => $inbound,
                'word_count' => $wc,
                'in_sitemap' => $inSitemap,
                'indexable' => $indexable,
                'reasons_json' => $reasonsJson,
                'scanned_at' => $scannedAt,
                'updated_at' => $now,
            ],
            [
                '%d',
                '%s',
                '%s',
                '%s',
                '%d',
                '%d',
                '%d',
                '%d',
                '%d',
                '%s',
                '%s',
                '%s',
            ]
        );

        if ($res === false) {
            $this->logger->error('IndexationRisk upsert failed: ' . (string) $wpdb->last_error);
        }
    }

    /**
     * @param array{post_type?:string,level?:string,orphan?:int,search?:string,limit?:int,offset?:int} $args
     * @return array{rows:array<int,array<string,mixed>>,total:int}
     */
    public function list(array $args = []): array
    {
        global $wpdb;

        $where = [];
        $params = [];

        if (!empty($args['post_type'])) {
            $where[] = 'post_type = %s';
            $params[] = sanitize_key((string) $args['post_type']);
        }

        if (!empty($args['orphan'])) {
            $where[] = 'inbound_links = 0';
        }

        // level: high|medium|low
        $level = isset($args['level']) ? sanitize_key((string) $args['level']) : '';
        if ($level === 'high') {
            $where[] = 'score < 50';
        } elseif ($level === 'medium') {
            $where[] = 'score >= 50 AND score < 80';
        } elseif ($level === 'low') {
            $where[] = 'score >= 80';
        }

        if (!empty($args['search'])) {
            $s = '%' . $wpdb->esc_like((string) $args['search']) . '%';
            $where[] = '(title LIKE %s OR url LIKE %s)';
            $params[] = $s;
            $params[] = $s;
        }

        $sqlWhere = '';
        if (!empty($where)) {
            $sqlWhere = ' WHERE ' . implode(' AND ', $where);
        }

        $limit = isset($args['limit']) ? (int) $args['limit'] : 50;
        $limit = max(1, min(200, $limit));
        $offset = isset($args['offset']) ? (int) $args['offset'] : 0;
        $offset = max(0, $offset);

        $countSql = 'SELECT COUNT(*) FROM ' . $this->table() . $sqlWhere;
        if (!empty($params)) {
            // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
            $countSql = $wpdb->prepare($countSql, $params);
        }
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $total = (int) $wpdb->get_var($countSql);

        $listSql = 'SELECT * FROM ' . $this->table() . $sqlWhere . ' ORDER BY score ASC, inbound_links ASC, scanned_at DESC LIMIT %d OFFSET %d';
        $listParams = array_merge($params, [$limit, $offset]);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $prepared = $wpdb->prepare($listSql, $listParams);
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $rows = $wpdb->get_results($prepared, ARRAY_A);
        if (!is_array($rows)) {
            $rows = [];
        }

        foreach ($rows as &$r) {
            if (is_array($r)) {
                $r['reasons_decoded'] = null;
                if (isset($r['reasons_json']) && is_string($r['reasons_json']) && $r['reasons_json'] !== '') {
                    $r['reasons_decoded'] = json_decode($r['reasons_json'], true);
                }
            }
        }

        return [
            'rows' => array_values(array_filter($rows, 'is_array')),
            'total' => $total,
        ];
    }

    public function clearAll(): void
    {
        global $wpdb;
        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        $wpdb->query('TRUNCATE TABLE ' . $this->table());
    }
}
