<?php

declare(strict_types=1);

namespace SiteFort\Modules\Scanner\Repository;

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

/**
 * Database access for scan queue rows.
 */
class ScanQueueRepository {

    const TABLE_NAME = 'sitefort_scan_queue';
    private const ACTIVE_STATUSES = [ 'pending', 'running' ];
    /** @var array<string, mixed>|null */
    private static ?array $active_job_cache = null;
    private static bool $active_job_loaded = false;

    /**
     * Escape a plugin-owned table identifier for direct interpolation.
     */
    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 '`' . str_replace( '`', '``', $table ) . '`';
    }

    /**
     * Create a new scan job entry.
     *
     * @param array $data Formatted data for insertion.
     * @return int|false The ID of the inserted row or false on error.
     */
    public function create( array $data ) {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Inserts a plugin-owned queue row.
        $result = $wpdb->insert( $table, $data );
        self::invalidate_active_job_cache();

        return $result ? $wpdb->insert_id : false;
    }

    /**
     * Atomically create a scan job only when no other active job exists.
     *
     * Prevents parallel request races where two scan starts both pass a prior
     * "is active" check and insert concurrently.
     *
     * @param array $data Formatted data for insertion.
     * @return int|false Insert ID on success, false when blocked or failed.
     */
    public function create_if_no_active( array $data ) {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;
        $table_identifier = self::table_identifier( $table );

        $defaults = [
            'source'             => 'local',
            'status'             => 'pending',
            'processed_count'    => 0,
            'total_count'        => 0,
            'cloud_stats'        => wp_json_encode( [ 'files' => [ 'pending' => 0, 'complete' => 0, 'failed' => 0 ] ] ),
            'component_status'   => wp_json_encode( [] ),
            'cloud_error_count'  => 0,
            'next_run_at'        => 0,
            'last_dispatch_at'   => 0,
            'task_runner_lease_token' => '',
            'task_runner_lease_until' => 0,
            'created_at'         => time(),
            'updated_at'         => time(),
            'last_ping'          => time(),
        ];

        $payload = wp_parse_args( $data, $defaults );

        foreach ( [ 'job_id', 'scan_type' ] as $required ) {
            if ( empty( $payload[ $required ] ) ) {
                return false;
            }
        }

        // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $query = $wpdb->prepare(
            "INSERT INTO {$table_identifier} (
                job_id,
                scan_type,
                source,
                status,
                processed_count,
                total_count,
                cloud_stats,
                component_status,
                cloud_error_count,
                next_run_at,
                last_dispatch_at,
                task_runner_lease_token,
                task_runner_lease_until,
                created_at,
                updated_at,
                last_ping
            )
             SELECT %s, %s, %s, %s, %d, %d, %s, %s, %d, %d, %d, %s, %d, %d, %d, %d
             FROM DUAL
             WHERE NOT EXISTS (
                SELECT 1
                FROM {$table_identifier}
                WHERE status IN ('pending', 'running')
                LIMIT 1
             )",
            $payload['job_id'],
            $payload['scan_type'],
            $payload['source'],
            $payload['status'],
            (int) $payload['processed_count'],
            (int) $payload['total_count'],
            $payload['cloud_stats'],
            $payload['component_status'],
            (int) $payload['cloud_error_count'],
            (int) $payload['next_run_at'],
            (int) $payload['last_dispatch_at'],
            (string) $payload['task_runner_lease_token'],
            (int) $payload['task_runner_lease_until'],
            (int) $payload['created_at'],
            (int) $payload['updated_at'],
            (int) $payload['last_ping']
        );
        // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

	        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Executes an atomic insert-if-empty statement against a plugin-owned queue table.
	        $inserted = $wpdb->query( $query );
        if ( (int) $inserted !== 1 ) {
            return false;
        }

        self::invalidate_active_job_cache();

        return (int) $wpdb->insert_id;
    }

    /**
     * Ensure database tables exist.
     */
    public static function ensure_tables() {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;
        $table_identifier = self::table_identifier( $table );
        $charset_collate = \SiteFort\Core\Database\SchemaInspector::validated_charset_collate( $wpdb );

        if ( ! class_exists( TaskQueueRepository::class ) ) {
            require_once __DIR__ . '/TaskQueueRepository.php';
        }

        $sql = "CREATE TABLE $table (
            id bigint(20) NOT NULL AUTO_INCREMENT,
            job_id varchar(64) NOT NULL,
            scan_type varchar(50) NOT NULL,
            source varchar(20) DEFAULT 'local',
            status varchar(20) NOT NULL,
            processed_count int(11) DEFAULT 0,
            total_count int(11) DEFAULT 0,
            cloud_stats longtext DEFAULT NULL,
            component_status longtext DEFAULT NULL,
            cloud_error_count int(10) UNSIGNED DEFAULT 0,
            malware_signature_version varchar(20) DEFAULT NULL,
            next_run_at int(11) DEFAULT 0,
            last_dispatch_at int(11) DEFAULT 0,
            task_runner_lease_token varchar(64) DEFAULT '',
            task_runner_lease_until int(11) NOT NULL DEFAULT 0,
            last_ping int(11) DEFAULT 0,
            created_at int(11) NOT NULL,
            updated_at int(11) NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY job_id (job_id),
            KEY status (status),
            KEY created_at (created_at)
        ) $charset_collate;";

		require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
		dbDelta( $sql );
		\SiteFort\Core\Database\SchemaInspector::flush_cache( $table );
		\SiteFort\Core\Database\SchemaInspector::ensure_table_uses_innodb( $table );
		TaskQueueRepository::ensure_table();

		self::ensure_column_exists( $table_identifier, 'last_ping', 'last_ping int(11) DEFAULT 0 AFTER cloud_stats' );
        self::ensure_column_exists( $table_identifier, 'component_status', 'component_status longtext DEFAULT NULL AFTER cloud_stats' );
        self::ensure_column_exists( $table_identifier, 'cloud_error_count', 'cloud_error_count int(10) UNSIGNED DEFAULT 0 AFTER component_status' );
        self::ensure_column_exists( $table_identifier, 'malware_signature_version', 'malware_signature_version varchar(20) DEFAULT NULL AFTER cloud_error_count' );
        self::ensure_column_exists( $table_identifier, 'next_run_at', 'next_run_at int(11) DEFAULT 0 AFTER malware_signature_version' );
        self::ensure_column_exists( $table_identifier, 'last_dispatch_at', 'last_dispatch_at int(11) DEFAULT 0 AFTER next_run_at' );
        self::ensure_column_exists( $table_identifier, 'task_runner_lease_token', "task_runner_lease_token varchar(64) DEFAULT '' AFTER last_dispatch_at" );
        self::ensure_column_exists( $table_identifier, 'task_runner_lease_until', 'task_runner_lease_until int(11) NOT NULL DEFAULT 0 AFTER task_runner_lease_token' );

    }

    /**
     * Add a fallback column to the plugin-owned scan queue table when needed.
     */
    private static function ensure_column_exists( string $table_identifier, string $column, string $definition ): void {
        global $wpdb;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_results( "SHOW COLUMNS FROM {$table_identifier} LIKE '{$column}'" );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
        if ( ! empty( $row ) ) {
            return;
        }

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
        $wpdb->query( "ALTER TABLE {$table_identifier} ADD COLUMN {$definition}" );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.DirectDatabaseQuery.SchemaChange,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
    }

    /**
     * Find a scan job by its Job UUID.
     *
     * @param string $job_id
     * @return array|null
     */
    public function find_by_job_id( $job_id ) {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        $row = $wpdb->get_row( 
            $wpdb->prepare( "SELECT * FROM {$table} WHERE job_id = %s", $job_id ), 
            ARRAY_A 
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        return $row ?: null;
    }

    /**
     * Get the most recent active job.
     *
     * @return array|null
     */
    public function find_active() {
        if ( self::$active_job_loaded ) {
            return self::$active_job_cache;
        }

        global $wpdb;
        $table = self::table_identifier( $wpdb->prefix . self::TABLE_NAME );

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
        $row = $wpdb->get_row(
            $wpdb->prepare(
                "SELECT * FROM {$table} WHERE status IN (%s, %s) ORDER BY id DESC LIMIT 1",
                self::ACTIVE_STATUSES[0],
                self::ACTIVE_STATUSES[1]
            ),
            ARRAY_A
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

        self::$active_job_cache = $row ?: null;
        self::$active_job_loaded = true;

        return self::$active_job_cache;
    }

    /**
     * Update a scan job via Job ID.
     *
     * @param string $job_id
     * @param array $data Key-value pairs to update.
     * @param array $format Optional format specifiers.
     * @return int|false
     */
    public function update( $job_id, array $data, array $format = [] ) {
        global $wpdb;
        $table = $wpdb->prefix . self::TABLE_NAME;
        
        // Add updated_at if not present
        if ( ! isset( $data['updated_at'] ) ) {
            $data['updated_at'] = time();
        }

        // Pass null for format to let wpdb auto-detect (treat as strings)
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching -- Updates a plugin-owned queue row by job ID.
        $result = $wpdb->update( 
            $table, 
            $data, 
            [ 'job_id' => $job_id ], 
            null, 
            [ '%s' ] 
        );

        self::invalidate_active_job_cache();

        return $result;
    }

    /**
     * Update the single active scan row (create_if_no_active guarantees at most one).
     *
     * @param array $data Column => value pairs.
     * @return int|false Rows affected, or false on error/empty update.
     */
    public function update_active( array $data ) {
        global $wpdb;
        $table = self::table_identifier( $wpdb->prefix . self::TABLE_NAME );

        if ( ! isset( $data['updated_at'] ) ) {
            $data['updated_at'] = time();
        }

        $set_parts = [];
        $params    = [];
        foreach ( $data as $column => $value ) {
            if ( ! preg_match( '/^[A-Za-z0-9_]+$/', (string) $column ) ) {
                continue;
            }

            if ( is_int( $value ) || is_float( $value ) || is_bool( $value ) ) {
                $set_parts[] = "`{$column}` = %d";
                $params[]    = (int) $value;
            } else {
                $set_parts[] = "`{$column}` = %s";
                $params[]    = (string) $value;
            }
        }

        if ( empty( $set_parts ) ) {
            return false;
        }

        $params[] = self::ACTIVE_STATUSES[0];
        $params[] = self::ACTIVE_STATUSES[1];

        $sql = "UPDATE {$table} SET " . implode( ', ', $set_parts ) . ' WHERE status IN (%s, %s) ORDER BY id DESC LIMIT 1';

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- SET clause is built from regex-validated column identifiers and placeholder fragments; every value is bound through prepare() below.
        $prepared = $wpdb->prepare( $sql, ...$params );

        // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,PluginCheck.Security.DirectDB.UnescapedDBParameter -- Prepared update against the plugin-owned queue table.
        $result = $wpdb->query( $prepared );

        self::invalidate_active_job_cache();

        return $result;
    }

    /**
     * Atomically increment the cloud error counter for a job.
     *
     * @param string $job_id
     * @return int New counter value, 0 when the row is missing.
     */
    public function increment_cloud_error( string $job_id ): int {
        global $wpdb;
        $table = self::table_identifier( $wpdb->prefix . self::TABLE_NAME );
        $now = time();

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter
        $updated = $wpdb->query(
            $wpdb->prepare(
                "UPDATE {$table}
                SET cloud_error_count = cloud_error_count + 1,
                    last_ping = %d,
                    updated_at = %d
                WHERE job_id = %s",
                $now,
                $now,
                $job_id
            )
        );

        if ( ! $updated ) {
            return 0;
        }

        self::invalidate_active_job_cache();

        $count = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT cloud_error_count FROM {$table} WHERE job_id = %s",
                $job_id
            )
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter

        return (int) $count;
    }

    /**
     * Clear the request-local active scan cache after queue mutations.
     *
     * @return void
     */
    private static function invalidate_active_job_cache(): void {
        self::$active_job_cache  = null;
        self::$active_job_loaded = false;
    }

}
