<?php
/**
 * Handles Database Operations for Guard Dog.
 *
 * @package GuardDog
 * @since   1.6.0
 */

if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly

class Guard_Dog_DB {

    /**
     * The database version key.
     * @var string
     */
    const DB_VERSION_OPTION = 'guard_dog_db_version';

    /**
     * The current database version.
     * Increment this when table structures change.
     * @var string
     */
    private $current_db_version = '1.7';

    /**
     * Base name for the Limit Login Attempts table.
     * @var string
     */
    private $lla_table_base_name = 'guard_dog_lla_lockouts';

         /**
     * Base name for the Activity Log table.
     * @var string
     */
    const ACTIVITY_LOG_TABLE_BASE_NAME = 'guard_dog_logs';

    /**
     * Base name for the Temporary Access table.
     * @var string
     */
    const TEMP_ACCESS_TABLE_BASE_NAME = 'guard_dog_temp_users';

    /**
     * Base name for the Email Verification table.
     * @var string
     */
    const EMAIL_VERIFICATION_TABLE_BASE_NAME = 'guard_dog_email_verifications';

    /**
     * Base name for the Password History table.
     * @var string
     */
    const PASSWORD_HISTORY_TABLE_BASE_NAME = 'guard_dog_password_history';

    /**
     * Base name for the Sessions table.
     * @var string
     */
    const SESSIONS_TABLE_BASE_NAME = 'guard_dog_sessions';

    /**
     * Base name for the Passkeys table.
     * @var string
     */
    const PASSKEYS_TABLE_BASE_NAME = 'guard_dog_passkeys';

    /**
     * Base name for the Social Accounts table.
     * @var string
     */
    const SOCIAL_ACCOUNTS_TABLE_BASE_NAME = 'guard_dog_social_accounts';

    /**
     * Constructor.
     *
     * @since 1.6.0
     */
    public function __construct() {
        // Add deactivation hook for cleanup
        register_deactivation_hook(GUARD_DOG_PLUGIN_FILE, [$this, 'cleanup_tables']);
    }

    /**
     * Get the full prefixed table name.
     *
     * @since 1.6.0
     * @param string $base_name The base table name (without prefix).
     * @return string Full table name with WordPress prefix.
     */
    public function get_table_name( $base_name ) {
        global $wpdb;
        // Ensure base name is valid (alphanumeric + underscore)
        $safe_base_name = preg_replace( '/[^a-zA-Z0-9_]/', '', $base_name );
        return $wpdb->prefix . $safe_base_name;
    }

    /**
     * Get the Limit Login Attempts table name.
     *
     * @since 1.6.0
     * @return string
     */
    public function get_lla_table_name() {
        return $this->get_table_name( $this->lla_table_base_name );
    }

    /**
     * Get the Activity Log table name.
     *
     * @since 1.6.0
     * @return string
     */
    public function get_activity_log_table_name() {
        return $this->get_table_name( self::ACTIVITY_LOG_TABLE_BASE_NAME );
    }

    /**
     * Get the Temporary Access table name.
     *
     * @since 1.6.0
     * @return string
     */
    public function get_temp_access_table_name() {
        return $this->get_table_name( self::TEMP_ACCESS_TABLE_BASE_NAME );
    }

    /**
     * Get the Email Verification table name.
     *
     * @since 1.9.0
     * @return string
     */
    public function get_email_verification_table_name() {
        return $this->get_table_name( self::EMAIL_VERIFICATION_TABLE_BASE_NAME );
    }

    /**
     * Get the Password History table name.
     *
     * @since 1.9.0
     * @return string
     */
    public function get_password_history_table_name() {
        return $this->get_table_name( self::PASSWORD_HISTORY_TABLE_BASE_NAME );
    }

    /**
     * Get the Sessions table name.
     *
     * @since 1.9.1
     * @return string
     */
    public function get_sessions_table_name() {
        return $this->get_table_name( self::SESSIONS_TABLE_BASE_NAME );
    }

    /**
     * Get the Passkeys table name.
     *
     * @since 1.9.1
     * @return string
     */
    public function get_passkeys_table_name() {
        return $this->get_table_name( self::PASSKEYS_TABLE_BASE_NAME );
    }

    /**
     * Get the Social Accounts table name.
     *
     * @since 2.0.0
     * @return string
     */
    public function get_social_accounts_table_name() {
        return $this->get_table_name( self::SOCIAL_ACCOUNTS_TABLE_BASE_NAME );
    }

    /**
     * Check if all required tables exist.
     *
     * @since 1.6.0
     * @return bool
     */
    public function tables_exist() {
        global $wpdb;
        
        $required_tables = [
            $this->get_lla_table_name(),
            $this->get_activity_log_table_name(),
            $this->get_temp_access_table_name(),
            $this->get_email_verification_table_name(),
            $this->get_password_history_table_name(),
            $this->get_sessions_table_name(),
            $this->get_passkeys_table_name(),
            $this->get_social_accounts_table_name()
        ];

        foreach ($required_tables as $table_name) {
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            // Table names are validated and sanitized, then escaped with esc_sql() for Plugin Check compliance
            // Direct database query is necessary for schema existence checks
            // Caching is not appropriate for schema checks as they need to be accurate and real-time
            $escaped_table_name = esc_sql( $table_name );
            $result = $wpdb->get_var("SHOW TABLES LIKE '$escaped_table_name'");
            // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            if ($result !== $table_name) {
                return false;
            }
        }
        
        return true;
    }

    /**
     * Cleanup tables on plugin deactivation
     */
    public function cleanup_tables() {
        global $wpdb;
        
        // Drop all Guard Dog tables
        $tables_to_drop = [
            $this->get_lla_table_name(),
            $this->get_activity_log_table_name(),
            $this->get_temp_access_table_name(),
            $this->get_email_verification_table_name(),
            $this->get_password_history_table_name(),
            $this->get_sessions_table_name(),
            $this->get_passkeys_table_name(),
            $this->get_social_accounts_table_name()
        ];

        foreach ($tables_to_drop as $table_name) {
            // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
            // Table names are validated and sanitized, then escaped with esc_sql() for Plugin Check compliance
            // Direct database query is necessary for schema changes during plugin deactivation
            // Schema changes cannot be cached and must be executed directly
            // This is a one-time cleanup operation on plugin deactivation
            $escaped_table_name = esc_sql( $table_name );
            $wpdb->query("DROP TABLE IF EXISTS $escaped_table_name");
            // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange
        }
        
        // Remove version option
        delete_option(self::DB_VERSION_OPTION);
        
        Guard_Dog()->debug->debug('Guard Dog: Database tables cleaned up on deactivation');
    }

    /**
     * Static flag to prevent recursion during table installation.
     *
     * @var bool
     */
    private static $installing = false;

    /**
     * Run database table creation/update routines.
     * Called on activation.
     *
     * @since 1.6.0
     */
    public function install_tables() {
        // Prevent recursion
        if (self::$installing) {
            return;
        }
        
        self::$installing = true;
        
        try {
            // Check current DB version against installed version
            $installed_ver = get_option( self::DB_VERSION_OPTION );

            if ( $installed_ver != $this->current_db_version ) {
                require_once ABSPATH . 'wp-admin/includes/upgrade.php';

                $this->create_lla_table();
                $this->create_activity_log_table();
                $this->create_temp_access_table();
                $this->create_email_verification_table();
                $this->create_password_history_table();
                $this->create_sessions_table();
                $this->create_passkeys_table();
                $this->create_social_accounts_table();

                update_option( self::DB_VERSION_OPTION, $this->current_db_version );
                
                Guard_Dog()->debug->debug('Guard Dog: Database tables installed/updated to version ' . $this->current_db_version);
            }
        } finally {
            self::$installing = false;
        }
    }

    /**
     * Create or update the Limit Login Attempts table.
     *
     * @since 1.6.0
     */
    private function create_lla_table() {
        global $wpdb;
        $table_name = $this->get_lla_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            lockout_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            ip_address VARCHAR(100) NOT NULL,
            attempt_count INT(11) UNSIGNED DEFAULT 0 NOT NULL,
            lockout_expiry DATETIME DEFAULT NULL,
            last_attempt DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
            PRIMARY KEY  (lockout_id),
            UNIQUE KEY ip_address (ip_address)
        ) $charset_collate;";

        $delta_result = dbDelta( $sql );
    }

    /**
     * Placeholder for creating the Activity Log table.
     *
     * @since 1.6.0
     */
    private function create_activity_log_table() {
        global $wpdb;
        $table_name = $this->get_activity_log_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            timestamp DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
            event_type VARCHAR(100) NOT NULL,
            username VARCHAR(255) DEFAULT NULL,
            user_id BIGINT(20) UNSIGNED DEFAULT NULL,
            ip_address VARCHAR(100) DEFAULT NULL,
            object_id BIGINT(20) UNSIGNED DEFAULT NULL,
            object_type VARCHAR(100) DEFAULT NULL,
            details LONGTEXT DEFAULT NULL,
            PRIMARY KEY  (id),
            KEY event_type (event_type),
            KEY timestamp (timestamp),
            KEY user_id (user_id),
            KEY ip_address (ip_address),
            KEY object_id (object_id),
            KEY object_type (object_type)
        ) $charset_collate;";

        dbDelta( $sql );
	    Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Create or update the Temporary Access table.
     *
     * @since 1.6.0
     */
    private function create_temp_access_table() {
        global $wpdb;
        $table_name = $this->get_temp_access_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            username VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            role VARCHAR(50) NOT NULL DEFAULT 'administrator',
            expires_at DATETIME NOT NULL,
            max_logins INT(11) UNSIGNED DEFAULT 10 NOT NULL,
            logins_used INT(11) UNSIGNED DEFAULT 0 NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
            last_login DATETIME DEFAULT NULL,
            is_active TINYINT(1) DEFAULT 1 NOT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY username (username),
            KEY email (email),
            KEY expires_at (expires_at),
            KEY is_active (is_active)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Insert a temporary access user into the audit table.
     *
     * @since 1.6.0
     * @param array $user_data User data array
     * @param int $wp_user_id WordPress user ID
     * @return bool Success status
     */
    public function insert_temp_access_user($user_data, $wp_user_id) {
        global $wpdb;
        $table_name = $this->get_temp_access_table_name();

        $insert_data = [
            'username' => sanitize_user($user_data['username']),
            'email' => sanitize_email($user_data['email']),
            'password_hash' => wp_hash_password($user_data['password'] ?? wp_generate_password()),
            'role' => sanitize_text_field($user_data['role']),
            'expires_at' => gmdate('Y-m-d H:i:s', $user_data['expires_at']),
            'max_logins' => absint($user_data['max_logins']),
            'logins_used' => 0,
            'created_at' => current_time('mysql'),
            'last_login' => null,
            'is_active' => 1,
        ];

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery
        // This is a necessary database insert operation for audit functionality
        $result = $wpdb->insert($table_name, $insert_data);
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery

        if ($result === false) {
            Guard_Dog()->debug->error('Failed to insert temporary access user into audit table', 'temp_access', [
                'user_data' => $user_data,
                'wp_user_id' => $wp_user_id,
                'db_error' => $wpdb->last_error
            ]);
            return false;
        }

        Guard_Dog()->debug->debug('Temporary access user inserted into audit table', 'temp_access', [
            'wp_user_id' => $wp_user_id,
            'audit_id' => $wpdb->insert_id
        ]);

        return true;
    }

    /**
     * Update temporary access user login count and last login.
     *
     * @since 1.6.0
     * @param string $username Username to update
     * @param int $login_count New login count
     * @return bool Success status
     */
    public function update_temp_access_login($username, $login_count) {
        global $wpdb;
        $table_name = $this->get_temp_access_table_name();

        $update_data = [
            'logins_used' => absint($login_count),
            'last_login' => current_time('mysql'),
        ];

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // This is a necessary database update operation for audit functionality
        // Caching is not appropriate for audit data as it needs to be real-time and accurate
        $result = $wpdb->update(
            $table_name,
            $update_data,
            ['username' => sanitize_user($username)],
            ['%d', '%s'],
            ['%s']
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        if ($result === false) {
            Guard_Dog()->debug->error('Failed to update temporary access login count', 'temp_access', [
                'username' => $username,
                'login_count' => $login_count,
                'db_error' => $wpdb->last_error
            ]);
            return false;
        }

        return true;
    }

    /**
     * Delete a temporary access user from the audit table.
     *
     * @since 1.6.0
     * @param string $username Username to delete
     * @return bool Success status
     */
    public function delete_temp_access_user($username) {
        global $wpdb;
        $table_name = $this->get_temp_access_table_name();

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        // This is a necessary database delete operation for audit functionality
        // Caching is not appropriate for audit data as it needs to be real-time and accurate
        $result = $wpdb->delete(
            $table_name,
            ['username' => sanitize_user($username)],
            ['%s']
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

        if ($result === false) {
            Guard_Dog()->debug->error('Failed to delete temporary access user from audit table', 'temp_access', [
                'username' => $username,
                'db_error' => $wpdb->last_error
            ]);
            return false;
        }

        Guard_Dog()->debug->debug('Temporary access user deleted from audit table', 'temp_access', [
            'username' => $username
        ]);

        return true;
    }

    /**
     * Get temporary access users from the audit table.
     *
     * @since 1.6.0
     * @return array Array of temporary access users
     */
    public function get_temp_access_users() {
        global $wpdb;
        $table_name = $this->get_temp_access_table_name();

        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        // This is a necessary database query for audit functionality
        // Table names are validated and sanitized, then escaped with esc_sql() for Plugin Check compliance
        // Caching is not appropriate for audit data as it needs to be real-time and accurate
        $escaped_table_name = esc_sql( $table_name );
        $results = $wpdb->get_results(
            $wpdb->prepare("SELECT * FROM $escaped_table_name WHERE is_active = %d ORDER BY created_at DESC", 1)
        );
        // phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared

        if ($results === null) {
            Guard_Dog()->debug->error('Failed to get temporary access users from audit table', 'temp_access', [
                'db_error' => $wpdb->last_error
            ]);
            return [];
        }

        return $results;
    }

    /**
     * Create or update the Email Verification table.
     *
     * @since 1.9.0
     */
    private function create_email_verification_table() {
        global $wpdb;
        $table_name = $this->get_email_verification_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            verification_token VARCHAR(64) NOT NULL,
            verification_key VARCHAR(64) NOT NULL,
            email VARCHAR(255) NOT NULL,
            created_at DATETIME NOT NULL,
            expires_at DATETIME NOT NULL,
            verified_at DATETIME NULL,
            resend_count INT(11) UNSIGNED DEFAULT 0,
            last_resend_at DATETIME NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY verification_token (verification_token),
            UNIQUE KEY verification_key (verification_key),
            KEY idx_user_id (user_id),
            KEY idx_expires_at (expires_at)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Create or update the Password History table.
     *
     * @since 1.9.0
     */
    private function create_password_history_table() {
        global $wpdb;
        $table_name = $this->get_password_history_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            password_hash VARCHAR(255) NOT NULL,
            created_at DATETIME NOT NULL,
            PRIMARY KEY  (id),
            KEY idx_user_id (user_id),
            KEY idx_created_at (created_at)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Create or update the Sessions table.
     *
     * Tracks active user sessions for remote logout and suspicious activity detection.
     * Sessions are identified by a hash of WordPress's session token, allowing
     * correlation with WordPress's native session management.
     *
     * @since 1.9.1
     */
    private function create_sessions_table() {
        global $wpdb;
        $table_name = $this->get_sessions_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            session_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            session_token_hash VARCHAR(64) NOT NULL,
            ip_address VARCHAR(45) NOT NULL,
            user_agent TEXT,
            device_fingerprint VARCHAR(64),
            geo_country VARCHAR(2),
            geo_city VARCHAR(100),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
            expires_at DATETIME NOT NULL,
            is_compromised TINYINT(1) DEFAULT 0,
            compromised_reason VARCHAR(255),
            PRIMARY KEY  (session_id),
            UNIQUE KEY session_token_hash (session_token_hash),
            KEY user_id (user_id),
            KEY ip_address (ip_address),
            KEY last_activity (last_activity),
            KEY expires_at (expires_at)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Create or update the Passkeys table.
     *
     * Stores WebAuthn credential data for passwordless authentication.
     * The credential_id is stored as VARBINARY to preserve binary data from WebAuthn.
     * The public_key_pem stores the public key in PEM format for verification.
     *
     * @since 1.9.1
     */
    private function create_passkeys_table() {
        global $wpdb;
        $table_name = $this->get_passkeys_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            passkey_id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            credential_id VARBINARY(1023) NOT NULL,
            public_key_pem TEXT NOT NULL,
            attestation_type VARCHAR(32),
            transports JSON,
            aaguid VARBINARY(16),
            sign_count INT(10) UNSIGNED DEFAULT 0,
            friendly_name VARCHAR(100),
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_used_at DATETIME,
            is_enabled TINYINT(1) DEFAULT 1,
            PRIMARY KEY  (passkey_id),
            UNIQUE KEY credential_id (credential_id(255)),
            KEY user_id (user_id)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

    /**
     * Create or update the Social Accounts table.
     *
     * Stores OAuth provider links for social login authentication.
     * Each row maps a WordPress user to an external provider identity.
     * The unique constraint on (provider, provider_user_id) prevents
     * duplicate links for the same external account.
     *
     * @since 2.0.0
     */
    private function create_social_accounts_table() {
        global $wpdb;
        $table_name = $this->get_social_accounts_table_name();
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id BIGINT(20) UNSIGNED NOT NULL,
            provider VARCHAR(50) NOT NULL,
            provider_user_id VARCHAR(255) NOT NULL,
            provider_email VARCHAR(255) DEFAULT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            last_login_at DATETIME DEFAULT NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY provider_user (provider, provider_user_id),
            KEY user_id (user_id),
            KEY provider (provider)
        ) $charset_collate;";

        dbDelta( $sql );
        Guard_Dog()->debug->debug('Guard Dog: dbDelta executed for table ' . $table_name);
    }

} // End class Guard_Dog_DB