<?php

namespace WPSN\Modules\Audit;

use WPSN\Core\Logger;

defined('ABSPATH') || exit;

/**
 * Stores per-scan normalized SEO title keys for duplicate detection.
 *
 * Table: wp_wpsn_audit_scan_title_keys
 */
final class AuditTitleKeyRepository
{
    private Logger $logger;

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

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

    public function upsert(int $scanId, string $objectType, int $objectId, string $objectSubType, string $titleKey): void
    {
        global $wpdb;

        $scanId = (int) $scanId;
        $objectType = sanitize_key($objectType);
        $objectId = (int) $objectId;
        $objectSubType = sanitize_key($objectSubType);
        $titleKey = (string) $titleKey;

        if ($scanId <= 0 || $objectType === '' || $objectId <= 0 || $titleKey === '') {
            return;
        }

        $table = $this->table();
        $now = current_time('mysql');

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->replace(
                $table,
                [
                    'scan_id' => $scanId,
                    'object_type' => $objectType,
                    'object_id' => $objectId,
                    'object_subtype' => $objectSubType,
                    'title_key' => $titleKey,
                    'created_at' => $now,
                ],
                ['%d', '%s', '%d', '%s', '%s', '%s']
            );
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key upsert failed: ' . $e->getMessage());
        }
    }

    public function deleteForObject(int $scanId, string $objectType, int $objectId): int
    {
        global $wpdb;

        $scanId = (int) $scanId;
        $objectType = sanitize_key($objectType);
        $objectId = (int) $objectId;
        if ($scanId <= 0 || $objectType === '' || $objectId <= 0) {
            return 0;
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            return (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE scan_id = %d AND object_type = %s AND object_id = %d",
                $scanId,
                $objectType,
                $objectId
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key deleteForObject failed: ' . $e->getMessage());
            return 0;
        }
    }

    public function keyForObject(int $scanId, string $objectType, int $objectId): string
    {
        global $wpdb;

        $scanId = (int) $scanId;
        $objectType = sanitize_key($objectType);
        $objectId = (int) $objectId;
        if ($scanId <= 0 || $objectType === '' || $objectId <= 0) {
            return '';
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $value = $wpdb->get_var($wpdb->prepare(
                "SELECT title_key FROM {$table} WHERE scan_id = %d AND object_type = %s AND object_id = %d LIMIT 1",
                $scanId,
                $objectType,
                $objectId
            ));
            return is_string($value) ? $value : '';
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key keyForObject failed: ' . $e->getMessage());
            return '';
        }
    }

    /**
     * @return array<int,array{object_id:int,object_subtype:string}>
     */
    public function rowsForKey(int $scanId, string $objectType, string $titleKey): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        $objectType = sanitize_key($objectType);
        $titleKey = (string) $titleKey;
        if ($scanId <= 0 || $objectType === '' || $titleKey === '') {
            return [];
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $rows = $wpdb->get_results($wpdb->prepare(
                "SELECT object_id, object_subtype FROM {$table} WHERE scan_id = %d AND object_type = %s AND title_key = %s ORDER BY object_id ASC",
                $scanId,
                $objectType,
                $titleKey
            ), ARRAY_A);
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key rowsForKey failed: ' . $e->getMessage());
            return [];
        }

        if (!is_array($rows)) {
            return [];
        }

        $out = [];
        foreach ($rows as $row) {
            if (!is_array($row)) {
                continue;
            }
            $objectId = isset($row['object_id']) ? (int) $row['object_id'] : 0;
            if ($objectId <= 0) {
                continue;
            }
            $out[] = [
                'object_id' => $objectId,
                'object_subtype' => isset($row['object_subtype']) ? sanitize_key((string) $row['object_subtype']) : '',
            ];
        }

        return $out;
    }

    /**
     * @return array<int,array{object_type:string,key:string,rows:array<int,array{object_id:int,object_subtype:string}>}>
     */
    public function duplicateGroups(int $scanId): array
    {
        global $wpdb;

        $scanId = (int) $scanId;
        if ($scanId <= 0) {
            return [];
        }

        $table = $this->table();

        // Group by object_type + key so post/page duplicates stay together, but posts never mix with terms.
        $sql = "SELECT t.object_type, t.object_id, t.object_subtype, t.title_key
                FROM {$table} t
                INNER JOIN (
                    SELECT object_type, title_key
                    FROM {$table}
                    WHERE scan_id = %d
                    GROUP BY object_type, title_key
                    HAVING COUNT(*) > 1
                ) d ON d.object_type = t.object_type AND d.title_key = t.title_key
                WHERE t.scan_id = %d
                ORDER BY t.object_type ASC, t.title_key ASC, t.object_id ASC";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        $rows = $wpdb->get_results($wpdb->prepare($sql, $scanId, $scanId), ARRAY_A);
        if (!is_array($rows) || empty($rows)) {
            return [];
        }

        return AuditDuplicateKeyGroups::build($rows, 'title_key');
    }

    public function deleteForOlderScans(int $thresholdScanId): int
    {
        global $wpdb;

        $thresholdScanId = (int) $thresholdScanId;
        if ($thresholdScanId <= 0) {
            return 0;
        }

        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            return (int) $wpdb->query($wpdb->prepare(
                "DELETE FROM {$table} WHERE scan_id < %d",
                $thresholdScanId
            ));
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key purge failed: ' . $e->getMessage());
            return 0;
        }
    }

    public function clearAll(): void
    {
        global $wpdb;
        $table = $this->table();

        try {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
            $wpdb->query("DELETE FROM {$table}");
        } catch (\Throwable $e) {
            $this->logger->error('Audit title-key clear failed: ' . $e->getMessage());
        }
    }
}
