<?php

namespace UltraguardPro\modules\reputation_monitor\db;

if ( ! defined( 'ABSPATH' ) ) { exit; }

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Reputation history table intentionally performs live change-history queries.

use UltraguardPro\core\DatabaseSchema;
use UltraguardPro\db\BaseTable;
use UltraguardPro\core\DatabaseCrud;

class ReputationHistoryTable extends BaseTable {

    protected $table_name  = 'ultraguard_reputation_history';
    protected $cache_group = 'ultraguard_reputation_history';

    public function get_defaults(): array {
        return [
            'id'          => 0,
            'source_id'   => '',
            'target'      => '',
            'old_status'  => '',
            'new_status'  => 'clean',
            'threat_type' => '',
            'note'        => '',
            'created_at'  => '',
        ];
    }

    protected function validate_data( $data ) {
        return $this->validate_required( (array) $data, [ 'source_id', 'target', 'new_status' ] ) ? $data : false;
    }

    protected function sanitize_data( $data ): array {
        $out = [];
        foreach ( $data as $key => $value ) {
            $out[ $key ] = 'note' === $key ? sanitize_textarea_field( (string) $value ) : sanitize_text_field( (string) $value );
        }

        return $out;
    }

    public function log_transition( array $data ): bool {
        if ( ! $this->table_exists() ) {
            return false;
        }

        return false !== $this->insert( $data );
    }

    public function get_recent_history( int $limit = 50 ): array {
        if ( ! $this->table_exists() ) {
            return [];
        }

        return (array) $this->get_by( [], max( 1, min( 200, $limit ) ), 'created_at', 'DESC' );
    }

    public function get_chart_stats( int $days = 90 ): array {
        if ( ! $this->table_exists() ) {
            return [
                'labels'      => [],
                'clean'       => [],
                'blacklisted' => [],
            ];
        }

        $days   = max( 7, min( 365, $days ) );
        $cutoff = gmdate( 'Y-m-d', strtotime( '-' . $days . ' days' ) );
        $clean_rows = DatabaseCrud::get_daily_count( 'ultraguard_reputation_history', 'created_at', [ 'new_status' => 'clean' ] );
        $black_rows = DatabaseCrud::get_daily_count( 'ultraguard_reputation_history', 'created_at', [ 'new_status' => 'blacklisted' ] );
        $clean_map  = [];
        $black_map  = [];
        $labels      = [];
        $clean       = [];
        $blacklisted = [];

        foreach ( (array) $clean_rows as $row ) {
            $day = is_object( $row ) ? (string) ( $row->date ?? '' ) : (string) ( $row['date'] ?? '' );
            if ( $day >= $cutoff ) {
                $clean_map[ $day ] = (int) ( is_object( $row ) ? ( $row->count ?? 0 ) : ( $row['count'] ?? 0 ) );
            }
        }

        foreach ( (array) $black_rows as $row ) {
            $day = is_object( $row ) ? (string) ( $row->date ?? '' ) : (string) ( $row['date'] ?? '' );
            if ( $day >= $cutoff ) {
                $black_map[ $day ] = (int) ( is_object( $row ) ? ( $row->count ?? 0 ) : ( $row['count'] ?? 0 ) );
            }
        }

        for ( $i = $days - 1; $i >= 0; $i-- ) {
            $date          = gmdate( 'Y-m-d', strtotime( '-' . $i . ' days' ) );
            $labels[]      = gmdate( 'M j', strtotime( $date . ' 00:00:00 UTC' ) );
            $clean[]       = (int) ( $clean_map[ $date ] ?? 0 );
            $blacklisted[] = (int) ( $black_map[ $date ] ?? 0 );
        }

        return [
            'labels'      => $labels,
            'clean'       => $clean,
            'blacklisted' => $blacklisted,
        ];
    }

    public function get_last_blacklist_clear_date(): string {
        if ( ! $this->table_exists() ) {
            return '';
        }

        $rows = $this->get_by( [ 'new_status' => 'clean' ], 1, 'created_at', 'DESC' );
        return ! empty( $rows[0]['created_at'] ) ? (string) $rows[0]['created_at'] : '';
    }

    private function table_exists(): bool {
        global $wpdb;

        $table_name = DatabaseSchema::get_table_name( $this->get_table_name_raw() );
        if ( empty( $table_name ) ) {
            return false;
        }

        return $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) ) === $table_name; // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- query uses prepare(); table name is supplied as a value placeholder.
    }
}
