<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

use SiteFort\Core\Database\SchemaInspector;

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

class QuarantineRepository {

    private const TABLE_NAME = 'sitefort_quarantine_items';
    private static $table_ready = false;

    public static function ensure_table(): void {
        if ( self::$table_ready ) {
            return;
        }

        global $wpdb;

        $table = $wpdb->prefix . self::TABLE_NAME;
        $charset_collate = \SiteFort\Core\Database\SchemaInspector::validated_charset_collate( $wpdb );

        $sql = "CREATE TABLE {$table} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            issue_hash varchar(64) NOT NULL DEFAULT '',
            scan_id varchar(64) DEFAULT NULL,
            issue_type varchar(50) NOT NULL DEFAULT '',
            original_relative_path text NOT NULL,
            blob_rel_path varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',
            quarantine_mode varchar(32) NOT NULL DEFAULT '',
            status varchar(32) NOT NULL DEFAULT 'staged',
            original_sha256 varchar(64) NOT NULL DEFAULT '',
            original_size bigint(20) unsigned NOT NULL DEFAULT 0,
            mime_type varchar(191) NOT NULL DEFAULT '',
            created_by bigint(20) unsigned NOT NULL DEFAULT 0,
            created_at int(11) unsigned NOT NULL DEFAULT 0,
            expires_at int(11) unsigned NOT NULL DEFAULT 0,
            restored_at int(11) unsigned NOT NULL DEFAULT 0,
            purged_at int(11) unsigned NOT NULL DEFAULT 0,
            issue_snapshot longtext DEFAULT NULL,
            meta longtext DEFAULT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY blob_rel_path (blob_rel_path),
            KEY issue_hash (issue_hash),
            KEY status_expires (status, expires_at),
            KEY created_at (created_at)
        ) {$charset_collate};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta( $sql );
        SchemaInspector::flush_cache( $table );
        $portable = self::ensure_blob_path_column_portable( $table );
        $innodb = SchemaInspector::ensure_table_uses_innodb( $table );
        self::$table_ready = SchemaInspector::table_exists( $table ) && $portable && $innodb;
    }

    public static function is_schema_current(): bool {
        $table = self::get_table_name();
        if ( ! SchemaInspector::table_exists( $table ) || ! SchemaInspector::table_engine_ready( $table ) ) {
            return false;
        }

        if ( ! SchemaInspector::column_uses_collation( $table, 'blob_rel_path', 'ascii_bin' ) ) {
            return false;
        }

        $column = SchemaInspector::get_column( $table, 'status' );
        if ( ! is_array( $column ) ) {
            return false;
        }

        $type = strtolower( (string) ( $column['Type'] ?? '' ) );
        $null = strtoupper( (string) ( $column['Null'] ?? '' ) );
        $default = SchemaInspector::normalize_default_value( $column['Default'] ?? '' );

        return $type === 'varchar(32)' && $null === 'NO' && $default === 'staged';
    }

    private static function ensure_blob_path_column_portable( string $table ): bool {
        if ( ! SchemaInspector::table_exists( $table ) ) {
            return false;
        }

        if ( SchemaInspector::column_uses_collation( $table, 'blob_rel_path', 'ascii_bin' ) ) {
            return true;
        }

        global $wpdb;
        $table_identifier = SchemaInspector::table_identifier( $table );
        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Repairs a plugin-owned indexed path column to a portable collation.
        $result = $wpdb->query( "ALTER TABLE {$table_identifier} MODIFY blob_rel_path varchar(255) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT ''" );
        SchemaInspector::flush_cache( $table );

        if ( false === $result ) {
            sitefort_log( '[SiteFort] DB error making quarantine blob_rel_path index portable: ' . $wpdb->last_error );
            return false;
        }

        return SchemaInspector::column_uses_collation( $table, 'blob_rel_path', 'ascii_bin' );
    }

    public static function get_table_name(): string {
        global $wpdb;

        return $wpdb->prefix . self::TABLE_NAME;
    }

    public static function create( array $data ) {
        global $wpdb;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Persists quarantine records immediately in a plugin-owned table.
        $result = $wpdb->insert(
            self::get_table_name(),
            [
                'issue_hash'              => sanitize_text_field( (string) ( $data['issue_hash'] ?? '' ) ),
                'scan_id'                 => sanitize_text_field( (string) ( $data['scan_id'] ?? '' ) ),
                'issue_type'              => sanitize_key( (string) ( $data['issue_type'] ?? '' ) ),
                'original_relative_path'  => ltrim( wp_normalize_path( (string) ( $data['original_relative_path'] ?? '' ) ), '/' ),
                'blob_rel_path'           => ltrim( wp_normalize_path( (string) ( $data['blob_rel_path'] ?? '' ) ), '/' ),
                'quarantine_mode'         => sanitize_key( (string) ( $data['quarantine_mode'] ?? '' ) ),
                'status'                  => sanitize_key( (string) ( $data['status'] ?? 'staged' ) ),
                'original_sha256'         => sanitize_text_field( (string) ( $data['original_sha256'] ?? '' ) ),
                'original_size'           => max( 0, (int) ( $data['original_size'] ?? 0 ) ),
                'mime_type'               => sanitize_text_field( (string) ( $data['mime_type'] ?? '' ) ),
                'created_by'              => max( 0, (int) ( $data['created_by'] ?? 0 ) ),
                'created_at'              => max( 0, (int) ( $data['created_at'] ?? time() ) ),
                'expires_at'              => max( 0, (int) ( $data['expires_at'] ?? 0 ) ),
                'restored_at'             => max( 0, (int) ( $data['restored_at'] ?? 0 ) ),
                'purged_at'               => max( 0, (int) ( $data['purged_at'] ?? 0 ) ),
                'issue_snapshot'          => isset( $data['issue_snapshot'] ) ? wp_json_encode( $data['issue_snapshot'] ) : null,
                'meta'                    => isset( $data['meta'] ) ? wp_json_encode( $data['meta'] ) : null,
            ],
            [
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%d',
                '%s',
                '%d',
                '%d',
                '%d',
                '%d',
                '%d',
                '%s',
                '%s',
            ]
        );

        if ( false === $result ) {
            return false;
        }

        return (int) $wpdb->insert_id;
    }

    public static function get_by_id( int $item_id ): ?array {
        if ( $item_id <= 0 ) {
            return null;
        }

        global $wpdb;
        $table = self::table_identifier( self::get_table_name() );
        $row = self::get_row_prepared(
            "SELECT * FROM {$table} WHERE id = %d LIMIT 1",
            $item_id
        );

        return self::normalize_row( $row );
    }

    public static function list_items( int $limit = 50, int $offset = 0, array $statuses = [ 'active', 'restored' ], array $modes = [] ): array {
        $limit = max( 1, min( 200, $limit ) );
        $offset = max( 0, $offset );
        $safe_statuses = self::sanitize_statuses( $statuses );
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_statuses ) ) {
            $safe_statuses = [ 'active' ];
        }

        $status_placeholders = implode( ',', array_fill( 0, count( $safe_statuses ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );
        $sql = "SELECT * FROM {$table} WHERE status IN ({$status_placeholders})";
        $params = $safe_statuses;

        if ( ! empty( $safe_modes ) ) {
            $mode_placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
            $sql .= " AND quarantine_mode IN ({$mode_placeholders})";
            $params = array_merge( $params, $safe_modes );
        }

        $sql .= ' ORDER BY created_at DESC LIMIT %d OFFSET %d';
        $rows = self::get_results_prepared(
            $sql,
            ...array_merge( $params, [ $limit, $offset ] )
        );

        return array_values(
            array_filter(
                array_map( [ self::class, 'normalize_row' ], $rows )
            )
        );
    }

    public static function count_items( array $statuses = [ 'active', 'restored' ], array $modes = [] ): int {
        $safe_statuses = self::sanitize_statuses( $statuses );
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_statuses ) ) {
            $safe_statuses = [ 'active' ];
        }

        $status_placeholders = implode( ',', array_fill( 0, count( $safe_statuses ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );

        $sql = "SELECT COUNT(*) FROM {$table} WHERE status IN ({$status_placeholders})";
        $params = $safe_statuses;
        if ( ! empty( $safe_modes ) ) {
            $mode_placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
            $sql .= " AND quarantine_mode IN ({$mode_placeholders})";
            $params = array_merge( $params, $safe_modes );
        }

        return max( 0, (int) self::get_var_prepared( $sql, ...$params ) );
    }

    public static function get_max_item_id( array $statuses = [ 'active', 'restored' ], array $modes = [] ): int {
        $safe_statuses = self::sanitize_statuses( $statuses );
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_statuses ) ) {
            $safe_statuses = [ 'active' ];
        }

        $status_placeholders = implode( ',', array_fill( 0, count( $safe_statuses ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );

        $sql = "SELECT MAX(id) FROM {$table} WHERE status IN ({$status_placeholders})";
        $params = $safe_statuses;
        if ( ! empty( $safe_modes ) ) {
            $mode_placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
            $sql .= " AND quarantine_mode IN ({$mode_placeholders})";
            $params = array_merge( $params, $safe_modes );
        }

        return max( 0, (int) self::get_var_prepared( $sql, ...$params ) );
    }

    public static function list_items_after_id( int $limit, int $after_id, int $max_id, array $statuses = [ 'active', 'restored' ], array $modes = [] ): array {
        $limit = max( 1, min( 200, $limit ) );
        $after_id = max( 0, $after_id );
        $max_id = max( 0, $max_id );
        if ( $max_id <= 0 || $after_id >= $max_id ) {
            return [];
        }

        $safe_statuses = self::sanitize_statuses( $statuses );
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_statuses ) ) {
            $safe_statuses = [ 'active' ];
        }

        $status_placeholders = implode( ',', array_fill( 0, count( $safe_statuses ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );
        $sql = "SELECT * FROM {$table} WHERE id > %d AND id <= %d AND status IN ({$status_placeholders})";
        $params = array_merge( [ $after_id, $max_id ], $safe_statuses );

        if ( ! empty( $safe_modes ) ) {
            $mode_placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
            $sql .= " AND quarantine_mode IN ({$mode_placeholders})";
            $params = array_merge( $params, $safe_modes );
        }

        $sql .= ' ORDER BY id ASC LIMIT %d';
        $params[] = $limit;

        $rows = self::get_results_prepared( $sql, ...$params );

        return array_values(
            array_filter(
                array_map( [ self::class, 'normalize_row' ], $rows )
            )
        );
    }

    public static function list_items_by_modes( array $modes, int $limit = 200 ): array {
        $limit = max( 1, min( 500, $limit ) );
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_modes ) ) {
            return [];
        }

        $placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );
        $rows = self::get_results_prepared(
            "SELECT * FROM {$table} WHERE quarantine_mode IN ({$placeholders}) ORDER BY id ASC LIMIT %d",
            ...array_merge( $safe_modes, [ $limit ] )
        );

        return array_values(
            array_filter(
                array_map( [ self::class, 'normalize_row' ], $rows )
            )
        );
    }

    public static function count_items_by_modes( array $modes ): int {
        $safe_modes = self::sanitize_modes( $modes );

        if ( empty( $safe_modes ) ) {
            return 0;
        }

        $placeholders = implode( ',', array_fill( 0, count( $safe_modes ), '%s' ) );
        $table = self::table_identifier( self::get_table_name() );

        return max( 0, (int) self::get_var_prepared(
            "SELECT COUNT(*) FROM {$table} WHERE quarantine_mode IN ({$placeholders})",
            ...$safe_modes
        ) );
    }

    public static function get_expired_items( int $timestamp, int $limit = 50 ): array {
        $timestamp = max( 0, $timestamp );
        $limit = max( 1, min( 200, $limit ) );
        $table = self::table_identifier( self::get_table_name() );
        $rows = self::get_results_prepared(
            "SELECT * FROM {$table} WHERE status IN (%s, %s) AND expires_at > 0 AND expires_at <= %d ORDER BY expires_at ASC LIMIT %d",
            'active',
            'restored',
            $timestamp,
            $limit
        );

        return array_values(
            array_filter(
                array_map( [ self::class, 'normalize_row' ], $rows )
            )
        );
    }

    public static function mark_restored( int $item_id, int $timestamp ): bool {
        return self::update_status( $item_id, 'restored', [ 'restored_at' => $timestamp ] );
    }

    public static function mark_active( int $item_id ): bool {
        return self::update_status( $item_id, 'active' );
    }

    public static function mark_purged( int $item_id, int $timestamp ): bool {
        return self::update_status( $item_id, 'purged', [ 'purged_at' => $timestamp ] );
    }

    public static function get_stale_staged_items( int $timestamp, int $limit = 50 ): array {
        $timestamp = max( 0, $timestamp );
        $limit = max( 1, min( 200, $limit ) );
        $table = self::table_identifier( self::get_table_name() );
        $rows = self::get_results_prepared(
            "SELECT * FROM {$table} WHERE status = %s AND created_at > 0 AND created_at <= %d ORDER BY created_at ASC LIMIT %d",
            'staged',
            $timestamp,
            $limit
        );

        return array_values(
            array_filter(
                array_map( [ self::class, 'normalize_row' ], $rows )
            )
        );
    }

    public static function delete_old_purged_records( int $older_than_timestamp, int $limit = 100 ): int {
        $older_than_timestamp = max( 0, $older_than_timestamp );
        $limit = max( 1, min( 500, $limit ) );
        $table = self::table_identifier( self::get_table_name() );
        $ids = self::get_results_prepared(
            "SELECT id FROM {$table} WHERE status = %s AND purged_at > 0 AND purged_at <= %d ORDER BY purged_at ASC LIMIT %d",
            'purged',
            $older_than_timestamp,
            $limit
        );

        if ( empty( $ids ) ) {
            return 0;
        }

        global $wpdb;
        $deleted = 0;
        foreach ( $ids as $row ) {
            $item_id = isset( $row['id'] ) ? (int) $row['id'] : 0;
            if ( $item_id <= 0 ) {
                continue;
            }

            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Purges expired quarantine records from a plugin-owned table.
            $result = $wpdb->delete( self::get_table_name(), [ 'id' => $item_id ], [ '%d' ] );
            if ( false !== $result ) {
                $deleted += (int) $result;
            }
        }

        return max( 0, $deleted );
    }

    public static function delete_item( int $item_id ): bool {
        if ( $item_id <= 0 ) {
            return false;
        }

        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Deletes a selected quarantine record from a plugin-owned table.
        $deleted = $wpdb->delete( self::get_table_name(), [ 'id' => $item_id ], [ '%d' ] );

        return false !== $deleted;
    }

    private static function update_status( int $item_id, string $status, array $extra = [] ): bool {
        if ( $item_id <= 0 ) {
            return false;
        }

        global $wpdb;

        $data = array_merge(
            [ 'status' => sanitize_key( $status ) ],
            $extra
        );

        $formats = [];
        foreach ( $data as $value ) {
            $formats[] = is_int( $value ) ? '%d' : '%s';
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates quarantine lifecycle state immediately in a plugin-owned table.
        $updated = $wpdb->update(
            self::get_table_name(),
            $data,
            [ 'id' => $item_id ],
            $formats,
            [ '%d' ]
        );

        return false !== $updated;
    }

    private static function normalize_row( ?array $row ): ?array {
        if ( ! is_array( $row ) ) {
            return null;
        }

        $row['id'] = isset( $row['id'] ) ? (int) $row['id'] : 0;
        $row['original_size'] = isset( $row['original_size'] ) ? (int) $row['original_size'] : 0;
        $row['created_by'] = isset( $row['created_by'] ) ? (int) $row['created_by'] : 0;
        $row['created_at'] = isset( $row['created_at'] ) ? (int) $row['created_at'] : 0;
        $row['expires_at'] = isset( $row['expires_at'] ) ? (int) $row['expires_at'] : 0;
        $row['restored_at'] = isset( $row['restored_at'] ) ? (int) $row['restored_at'] : 0;
        $row['purged_at'] = isset( $row['purged_at'] ) ? (int) $row['purged_at'] : 0;
        $row['issue_hash'] = sanitize_text_field( (string) ( $row['issue_hash'] ?? '' ) );
        $row['scan_id'] = sanitize_text_field( (string) ( $row['scan_id'] ?? '' ) );
        $row['issue_type'] = sanitize_key( (string) ( $row['issue_type'] ?? '' ) );
        $row['original_relative_path'] = ltrim( wp_normalize_path( (string) ( $row['original_relative_path'] ?? '' ) ), '/' );
        $row['blob_rel_path'] = ltrim( wp_normalize_path( (string) ( $row['blob_rel_path'] ?? '' ) ), '/' );
        $row['quarantine_mode'] = sanitize_key( (string) ( $row['quarantine_mode'] ?? '' ) );
        $row['status'] = sanitize_key( (string) ( $row['status'] ?? '' ) );
        $row['original_sha256'] = sanitize_text_field( (string) ( $row['original_sha256'] ?? '' ) );
        $row['mime_type'] = sanitize_text_field( (string) ( $row['mime_type'] ?? '' ) );

        if ( isset( $row['issue_snapshot'] ) && is_string( $row['issue_snapshot'] ) && $row['issue_snapshot'] !== '' ) {
            $decoded = json_decode( $row['issue_snapshot'], true );
            if ( is_array( $decoded ) ) {
                $row['issue_snapshot'] = $decoded;
            }
        }

        if ( isset( $row['meta'] ) && is_string( $row['meta'] ) && $row['meta'] !== '' ) {
            $decoded = json_decode( $row['meta'], true );
            if ( is_array( $decoded ) ) {
                $row['meta'] = $decoded;
            }
        }

        return $row;
    }

    private static function sanitize_statuses( array $statuses ): array {
        return array_values(
            array_filter(
                array_map(
                    static function ( $status ): string {
                        return sanitize_key( (string) $status );
                    },
                    $statuses
                )
            )
        );
    }

    private static function sanitize_modes( array $modes ): array {
        return array_values(
            array_filter(
                array_map(
                    static function ( $mode ): string {
                        return sanitize_key( (string) $mode );
                    },
                    $modes
                )
            )
        );
    }

    private static function table_identifier( string $table ): string {
        if ( $table === '' || strlen( $table ) > 64 || ! preg_match( '/^[A-Za-z0-9_]+$/', $table ) ) {
            throw new \InvalidArgumentException( 'Invalid table identifier.' );
        }

        return '`' . $table . '`';
    }

    private static function get_results_prepared( string $sql, ...$params ): array {
        global $wpdb;

        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return [];
        }

        $results = $wpdb->get_results( $prepared, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter

        return is_array( $results ) ? $results : [];
    }

    private static function get_var_prepared( string $sql, ...$params ) {
        global $wpdb;

        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return null;
        }

        return $wpdb->get_var( $prepared ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    private static function get_row_prepared( string $sql, ...$params ): ?array {
        global $wpdb;

        $prepared = empty( $params ) ? $sql : $wpdb->prepare( $sql, ...$params ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
        if ( ! is_string( $prepared ) || '' === $prepared ) {
            return null;
        }

        $row = $wpdb->get_row( $prepared, ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter

        return is_array( $row ) ? $row : null;
    }
}
