<?php

namespace UltraguardPro\modules\uptime_monitor\db;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Uptime checks table intentionally performs live status/history queries for monitoring.
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Uptime aggregation queries use validated internal table identifiers only.

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

class UptimeChecksTable extends BaseTable {

    protected $table_name  = 'ultraguard_uptime_checks';
    protected $cache_group = 'ultraguard_uptime_checks';

    public function get_defaults(): array {
        return [
            'id'            => 0,
            'check_url'     => '',
            'status'        => 'up',
            'response_code' => 0,
            'response_time' => 0,
            'error_message' => '',
            'check_source'  => 'local',
            'created_at'    => '',
        ];
    }

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

    protected function sanitize_data( $data ): array {
        $out = [];
        foreach ( $data as $key => $value ) {
            if ( in_array( $key, [ 'response_code', 'response_time' ], true ) ) {
                $out[ $key ] = absint( $value );
            } elseif ( 'error_message' === $key ) {
                $out[ $key ] = sanitize_textarea_field( (string) $value );
            } else {
                $out[ $key ] = is_string( $value ) ? sanitize_text_field( $value ) : $value;
            }
        }
        return $out;
    }

    public function get_latest(): ?array {
        $rows = $this->get_by( [], 1, 'created_at', 'DESC' );
        return ! empty( $rows[0] ) ? $rows[0] : null;
    }

    public function get_latest_for_url( string $url ): ?array {
        $rows = $this->get_by( [ 'check_url' => $url ], 1, 'created_at', 'DESC' );
        return ! empty( $rows[0] ) ? $rows[0] : null;
    }

    public function calculate_uptime_percentage( int $hours ): float {
        $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $hours ) * HOUR_IN_SECONDS ) );
        $total = DatabaseCrud::count_since( 'ultraguard_uptime_checks', [], 'created_at', $cutoff );
        $up    = DatabaseCrud::count_since( 'ultraguard_uptime_checks', [ 'status' => 'up' ], 'created_at', $cutoff );

        return $total > 0 ? ( $up / $total ) * 100 : 0.0;
    }

    public function calculate_average_response_time( int $hours ): float {
        $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( max( 1, $hours ) * HOUR_IN_SECONDS ) );
        return DatabaseCrud::average_since( 'ultraguard_uptime_checks', 'response_time', [], 'created_at', $cutoff, 1 );
    }

    public function get_recent_incidents( int $limit = 20 ): array {
        return (array) DatabaseCrud::get_records( 'ultraguard_uptime_checks', [
            'where'       => [ 'status' => [ 'down', 'timeout', 'error', 'degraded' ] ],
            'orderby'     => 'created_at',
            'order'       => 'DESC',
            'limit'       => max( 1, min( 200, $limit ) ),
            'output_type' => ARRAY_A,
        ] );
    }

    public function get_response_time_chart( int $hours = 24 ): array {
        global $wpdb;
        $hours  = max( 1, min( 72, $hours ) );
        $table  = $wpdb->prefix . $this->table_name;
        $cutoff = gmdate( 'Y-m-d H:i:s', time() - ( $hours * HOUR_IN_SECONDS ) );
        $rows   = $wpdb->get_results(  // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- query built via prepare(); table name is a validated internal identifier.
            $wpdb->prepare(
                "SELECT DATE_FORMAT(created_at, '%%Y-%%m-%%d %%H:%%i:00') AS bucket,
                        AVG(response_time) AS avg_response
                 FROM {$table}
                 WHERE created_at >= %s
                 GROUP BY YEAR(created_at), MONTH(created_at), DAY(created_at), HOUR(created_at), FLOOR(MINUTE(created_at) / 15)
                 ORDER BY bucket ASC",
                $cutoff
            ),
            ARRAY_A
        );

        $labels = [];
        $values = [];
        foreach ( (array) $rows as $row ) {
            $labels[] = gmdate( 'H:i', strtotime( (string) $row['bucket'] . ' UTC' ) );
            $values[] = round( (float) $row['avg_response'], 2 );
        }

        return [ 'labels' => $labels, 'values' => $values ];
    }

    public function get_daily_uptime_chart( int $days = 30 ): array {
        $days   = max( 7, min( 90, $days ) );
        $cutoff = gmdate( 'Y-m-d', strtotime( '-' . $days . ' days' ) );
        $all_rows = DatabaseCrud::get_daily_count( 'ultraguard_uptime_checks', 'created_at' );
        $up_rows  = DatabaseCrud::get_daily_count( 'ultraguard_uptime_checks', 'created_at', [ 'status' => 'up' ] );
        $all_map  = [];
        $up_map   = [];

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

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

        $labels = [];
        $values = [];
        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' ) );
            $total    = (int) ( $all_map[ $date ] ?? 0 );
            $up       = (int) ( $up_map[ $date ] ?? 0 );
            $values[] = $total > 0 ? round( ( $up / $total ) * 100, 2 ) : 0;
        }

        return [ 'labels' => $labels, 'values' => $values ];
    }
}
