captaincore-manager/app/DB.php

<?php

namespace CaptainCore;

class DB {

    private static function _table() {
        global $wpdb;
        $tablename = explode( '\\', get_called_class(), 2 );
        $tablename[0] = strtolower ( $tablename[0] );
        // Add '_' before each capitalized letter and trim the first
        $tablename[1] = strtolower ( trim ( preg_replace( '/([A-Z])/', '_$1', $tablename[1] ), "_" ) );
        $tablename = implode ( '_', $tablename);
        return $wpdb->prefix . $tablename;
    }

    private static function _fetch_sql( $value ) {
        global $wpdb;
        $sql = sprintf( 'SELECT * FROM %s WHERE %s = %%s', self::_table(), static::$primary_key );
        return $wpdb->prepare( $sql, $value );
    }

    static function valid_check( $data ) {
        global $wpdb;

        $sql_where       = '';
        $sql_where_count = count( $data );
        $i               = 1;
        foreach ( $data as $key => $row ) {
            if ( $i < $sql_where_count ) {
                $sql_where .= "`$key` = '$row' and ";
            } else {
                $sql_where .= "`$key` = '$row'";
            }
            $i++;
        }
        $sql     = 'SELECT * FROM ' . self::_table() . " WHERE $sql_where";
        $results = $wpdb->get_results( $sql );
        if ( count( $results ) != 0 ) {
            return false;
        } else {
            return true;
        }
    }

    static function get( $value ) {
        global $wpdb;
        return $wpdb->get_row( self::_fetch_sql( $value ) );
    }

    static function insert( $data ) {
        global $wpdb;
        $wpdb->insert( self::_table(), $data );
        return $wpdb->insert_id;
    }

    static function update( $data, $where ) {
        global $wpdb;
        return $wpdb->update( self::_table(), $data, $where );
    }

    static function delete( $value ) {
        global $wpdb;
        $sql = sprintf( 'DELETE FROM %s WHERE %s = %%s', self::_table(), static::$primary_key );
        return $wpdb->query( $wpdb->prepare( $sql, $value ) );
    }

    static function where( $conditions ) {
        global $wpdb;
        $where_clauses = [];
        $values = [];

        foreach ( $conditions as $column => $value ) {
            $column = preg_replace('/[^a-zA-Z0-9_]/', '', $column);
            
            if ( is_array( $value ) ) {
                $placeholders = implode( ',', array_fill( 0, count( $value ), '%s' ) );
                $where_clauses[] = "`$column` IN ($placeholders)";
                $values = array_merge( $values, $value );
            } else {
                $where_clauses[] = "`$column` = %s";
                $values[] = $value;
            }
        }

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

        $sql = "SELECT * FROM " . self::_table() . " WHERE " . implode( " AND ", $where_clauses ) . " ORDER BY `created_at` DESC";

        return $wpdb->get_results( $wpdb->prepare( $sql, $values ) );
    }

    static function where_compare( $conditions ) {
        global $wpdb;
        $where_statements = [];
        foreach ( $conditions as $condition ) {
            $where_statements[] =  "`{$condition["key"]}` {$condition["compare"]} '{$condition["value"]}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = 'SELECT * FROM ' . self::_table() . " WHERE $where_statements order by `created_at` DESC";
        return $wpdb->get_results( $sql );
    }

    static function fetch( $value ) {
        global $wpdb;
        $value = intval( $value );
        $sql   = 'SELECT * FROM ' . self::_table() . " WHERE `site_id` = '$value' order by `created_at` DESC";
        return $wpdb->get_results( $sql );
    }

    static function fetch_environment( $value, $environment_id ) {
        global $wpdb;
        $value          = intval( $value );
        $environment_id = intval( $environment_id );
        $sql            = 'SELECT * FROM ' . self::_table() . " WHERE `site_id` = '$value' and `environment_id` = '$environment_id' order by `created_at` DESC";
        return $wpdb->get_results( $sql );
    }

    static function all( $sort = "created_at", $sort_order = "DESC" ) {
        global $wpdb;
        $sql = 'SELECT * FROM ' . self::_table() . ' order by `' . $sort . '` '. $sort_order;
        return $wpdb->get_results( $sql );
    }

    static function mine( $sort = "created_at", $sort_order = "DESC" ) {
        global $wpdb;
        $user_id = get_current_user_id();
        $sql = 'SELECT * FROM ' . self::_table() . " WHERE user_id = '{$user_id}' order by `{$sort}` {$sort_order}";
        return $wpdb->get_results( $sql );
    }

    static function select( $field = "site_id", $where = "status", $value = "active", $sort = "created_at", $sort_order = "DESC" ) {
        global $wpdb;
        $sql = "SELECT $field FROM " . self::_table() . " WHERE $where = '{$value}' order by `{$sort}` {$sort_order}";
        $results = array_column( $wpdb->get_results( $sql ), $field );
        return $results;
    }

    static function select_by_conditions( $field = "environment_id", $conditions = [] ) {
        global $wpdb;
        $table = self::_table();
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            $where_statements[] =  "{$table}.{$row} = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = "SELECT {$table}.{$field} FROM {$table} WHERE $where_statements";
        $results = array_column( $wpdb->get_results( $sql ), $field );
        return $results;
    }

    static function select_domains( $field = "domain_id", $sort = "name", $sort_order = "ASC" ) {
        global $wpdb;
        $sql = "SELECT $field FROM " . self::_table() . " order by `{$sort}` {$sort_order}";
        $results = array_column( $wpdb->get_results( $sql ), $field );
        return $results;
    }
    
    static function select_all( $field = "site_id" ) {
        global $wpdb;
        $sql = "SELECT $field FROM " . self::_table() . " order by `created_at` DESC";
        $results = array_column( $wpdb->get_results( $sql ), $field );
        return $results;
    }

    static function select_active_sites( $field = "site_id", $conditions = [] ) {
        global $wpdb;
        $table = self::_table();
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            $where_statements[] =  "{$table}.{$row} = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = "SELECT {$table}.{$field} FROM {$table} INNER JOIN wp_captaincore_sites ON {$table}.site_id = wp_captaincore_sites.site_id WHERE $where_statements AND wp_captaincore_sites.status = 'active'";
        $results = array_column( $wpdb->get_results( $sql ), $field );
        return $results;
    }

    static function select_domains_for_account( $account_ids = [] ) {
        global $wpdb;
        $table   = self::_table();
        $ids     = implode( ",", $account_ids );
        $sql     = "SELECT {$table}.domain_id FROM {$table} INNER JOIN wp_captaincore_domains ON {$table}.domain_id = wp_captaincore_domains.domain_id WHERE {$table}.account_id in ({$ids})";
        $results = array_column( $wpdb->get_results( $sql ), 'domain_id' );
        return $results;
    }

    static function renewals( $user_id ) {
        global $wpdb;
        $table = self::_table();
        $sql   = "SELECT *
                FROM {$table}
                WHERE {$table}.plan like '%,\"billing_user_id\":\"$user_id\"%'
                order by {$table}.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function with_renewals() {
        global $wpdb;
        $table = self::_table();
        $sql   = "SELECT *
                FROM {$table}
                WHERE {$table}.plan like '%\"next_renewal\":\"%'
                order by {$table}.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_domains( $conditions = [] ) {
        global $wpdb;
        $table = self::_table();
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            if ( is_array( $value ) ) {
                $values = implode( ", ", $value );
                $where_statements[] =  "{$table}.{$row} IN ($values)";
                continue;
            }
            $where_statements[] =  "{$table}.{$row} = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = "SELECT {$table}.domain_id, {$wpdb->prefix}captaincore_domains.name
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_domains ON {$table}.domain_id = {$wpdb->prefix}captaincore_domains.domain_id
                WHERE $where_statements 
                order by {$wpdb->prefix}captaincore_domains.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_filters_for_admins( $environment = "all" ) {
        global $wpdb;
        $table = self::_table();
        $environment_conditions = "";
        if ( $environment != "all" ) {
            $environment_conditions = "AND {$table}.`environment` = '$environment'";
        }
        $sql = "SELECT {$table}.themes, {$table}.plugins, {$table}.core, {$table}.details
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_sites ON {$table}.site_id = {$wpdb->prefix}captaincore_sites.site_id
                WHERE {$wpdb->prefix}captaincore_sites.`status` = 'active' $environment_conditions";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_filters_for_account( $account_id = "", $environment = "all" ) {
        global $wpdb;
        $table = self::_table();
        $environment_conditions = "";
        if ( $environment != "all" ) {
            $environment_conditions = "AND {$table}.`environment` = '$environment'";
        }
        $sql = "SELECT {$table}.themes, {$table}.plugins, {$table}.core
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_sites ON {$table}.site_id = {$wpdb->prefix}captaincore_sites.site_id
                WHERE {$wpdb->prefix}captaincore_sites.account_id = $account_id AND {$wpdb->prefix}captaincore_sites.`status` = 'active' $environment_conditions";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_filters_for_shared_accounts( $account_id = "", $environment = "all" ) {
        global $wpdb;
        $table = self::_table();
        $environment_conditions = "";
        if ( $environment != "all" ) {
            $environment_conditions = "AND {$table}.`environment` = '$environment'";
        }
        $sql = "SELECT {$table}.themes, {$table}.plugins
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_sites ON {$table}.site_id = {$wpdb->prefix}captaincore_sites.site_id
                INNER JOIN {$wpdb->prefix}captaincore_account_site ON {$table}.site_id = {$wpdb->prefix}captaincore_account_site.site_id
                INNER JOIN {$wpdb->prefix}captaincore_accounts ON {$wpdb->prefix}captaincore_account_site.account_id = {$wpdb->prefix}captaincore_accounts.account_id
                WHERE {$wpdb->prefix}captaincore_accounts.account_id = $account_id AND {$wpdb->prefix}captaincore_sites.`status` = 'active' $environment_conditions";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_process_logs( $conditions = [] ) {
        global $wpdb;
        $table = self::_table();
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            if ( is_array($value) ) {
                $values = implode( ", ", $value );
                $where_statements[] =  "{$table}.{$row} IN ($values)";
                continue;
            }
            $where_statements[] =  "{$table}.{$row} = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = "SELECT {$table}.process_log_id, {$wpdb->prefix}captaincore_processes.name
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_process_logs ON {$table}.process_log_id = {$wpdb->prefix}captaincore_process_logs.process_log_id
                LEFT JOIN {$wpdb->prefix}captaincore_processes ON {$wpdb->prefix}captaincore_process_logs.process_id = {$wpdb->prefix}captaincore_processes.process_id
                WHERE $where_statements 
                order by {$wpdb->prefix}captaincore_process_logs.`created_at` DESC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_sites_for_account( $account_id = "" ) {
        global $wpdb;
        $table = self::_table();
        $sql = "SELECT *
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_account_site ON {$wpdb->prefix}captaincore_account_site.account_id = {$table}.account_id
                INNER JOIN {$wpdb->prefix}captaincore_accounts ON {$wpdb->prefix}captaincore_accounts.account_id = {$table}.account_id
                WHERE {$table}.account_id = $account_id OR {$wpdb->prefix}captaincore_account_site.account_id = $account_id
                order by {$wpdb->prefix}captaincore_sites.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_sites_matching( $arguments = [] ) {
        global $wpdb;
        $arguments = (object) $arguments;
        $filter    = empty( $arguments->filter ) ? "" : (object) $arguments->filter;
        $table     = self::_table();
        $provider_conditions    = "";
        $environment_conditions = "";
        $target_conditions      = [];
        $field_selection        = "";
        $environment_columns    = [ "address", "username", "password", "protocol", "port", "home_directory", "database_username", "database_password", "storage", "visits", "core", "fathom", "home_url", "themes", "plugins", "updates_enabled", "updates_exclude_themes", "updates_exclude_plugins", "screenshot", "capture_pages" ];
        if ( ! empty( $arguments->provider ) ) {
            $provider_conditions = "AND {$table}.provider = '{$arguments->provider}'";
        }
        if ( $arguments->environment != "all" ) {
            $environment_conditions = "AND {$wpdb->prefix}captaincore_environments.environment = '{$arguments->environment}'";
        }

        if ( ! empty( $arguments->field ) ) {
            $field_selection = ", {$table}.{$arguments->field}";
        }

        if ( ! empty( $arguments->targets ) && count( $arguments->targets ) > 0 ) {
            if ( in_array("updates-on", $arguments->targets ) ) {
                $target_conditions[] = "AND {$wpdb->prefix}captaincore_environments.updates_enabled = '1'";
            }
            if ( in_array("updates-off", $arguments->targets ) ) {
                $target_conditions[] = "AND {$wpdb->prefix}captaincore_environments.updates_enabled = '0'";
            }
            if ( in_array("offload-on", $arguments->targets ) ) {
                $target_conditions[] = "AND {$wpdb->prefix}captaincore_environments.offload_enabled = '1'";
            }
            if ( in_array("offload-off", $arguments->targets ) ) {
                $target_conditions[] = "AND {$wpdb->prefix}captaincore_environments.offload_enabled = '0'";
            }
            if ( in_array("monitor-on", $arguments->targets ) ) {
                $target_conditions[] = "AND {$wpdb->prefix}captaincore_environments.monitor_enabled = '1'";
            }
        }

        $target_conditions = implode( " ", $target_conditions );

        if ( ! empty( $arguments->field ) && in_array( $arguments->field, $environment_columns ) ) {
            $field_selection = ", {$wpdb->prefix}captaincore_environments.{$arguments->field}";
        }
        
        if ( ! empty( $arguments->field ) && strpos( $arguments->field, ',' ) !== false ) {
            $fields = explode( ",", $arguments->field );
            $field_selection = "";
            foreach ( $fields as $field ) {
                if ( in_array( $field, $environment_columns ) ) {
                    $field_selection = "{$field_selection}, {$wpdb->prefix}captaincore_environments.{$field}";
                    continue;
                }
                $field_selection = "{$field_selection}, {$table}.{$field}";
            }
        }

        if ( empty( $filter->type ) ) {
            $sql = "SELECT {$table}.site, {$wpdb->prefix}captaincore_environments.environment $field_selection
                    FROM {$table}
                    INNER JOIN {$wpdb->prefix}captaincore_environments ON {$table}.site_id = {$wpdb->prefix}captaincore_environments.site_id
                    WHERE {$table}.status = 'active' $provider_conditions $environment_conditions $target_conditions
                    order by {$wpdb->prefix}captaincore_sites.`name` ASC";
            $results = $wpdb->get_results( $sql );
            return $results;
        }

        if ( $filter->type == "core" ) {
            $sql = "SELECT {$table}.site, {$wpdb->prefix}captaincore_environments.environment $field_selection
                    FROM {$table}
                    INNER JOIN {$wpdb->prefix}captaincore_environments ON {$table}.site_id = {$wpdb->prefix}captaincore_environments.site_id
                    WHERE {$wpdb->prefix}captaincore_environments.core = '{$filter->version}' $provider_conditions $environment_conditions $target_conditions
                    AND {$table}.status = 'active'
                    order by {$wpdb->prefix}captaincore_sites.`name` ASC";
            $results = $wpdb->get_results( $sql );
            return $results;
        }

        if ( empty( $filter->name ) ) {
            $filter->name = '[^"]*';
        }
        if ( empty( $filter->status ) ) {
            $filter->status = '[^"]*';
        }
        if ( empty( $filter->version ) ) {
            $filter->version = '[^"]*';
        }

        // WordPress thinks {} in SQL is a syntax error. To workaround we can wrap them in brackets likes so [{] and [}].
        $pattern = '{"name":"'.$filter->name.'","title":"[^"]*","status":"'.$filter->status.'","version":"'.$filter->version.'"}';
        $pattern = str_replace ( "{", "[{]", $pattern );
        $pattern = str_replace ( "}", "[}]", $pattern );

        $sql = "SELECT {$table}.site, {$wpdb->prefix}captaincore_environments.environment $field_selection
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_environments ON {$table}.site_id = {$wpdb->prefix}captaincore_environments.site_id
                WHERE {$wpdb->prefix}captaincore_environments.{$filter->type} REGEXP '{$pattern}' $provider_conditions $environment_conditions $target_conditions
                AND {$table}.status = 'active'
                order by {$wpdb->prefix}captaincore_sites.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function fetch_sites_filtered( $filters = [], $allowed_site_ids = [] ) {
        global $wpdb;
        $table              = self::_table();
        $environments_table = "{$wpdb->prefix}captaincore_environments";
        
        // Base conditions
        $base_conditions = [
            "{$table}.status = 'active'",
        ];

        // Add permission check
        if ( ! empty( $allowed_site_ids ) ) {
            $allowed_ids_str = implode( ',', array_map( 'intval', $allowed_site_ids ) );
            $base_conditions[] = "{$table}.site_id IN ($allowed_ids_str)";
        }
        
        $filters = (object) $filters;

        if ( ! empty( $filters->backup_mode ) ) {
            $mode = esc_sql( $filters->backup_mode );
            // Search the JSON details column for the specific mode
            $base_conditions[] = "{$table}.details LIKE '%\"mode\":\"{$mode}\"%'";
        }

        // Retrieve logic operators
        $primary_logic   = ( ! empty( $filters->logic ) && strtolower( $filters->logic ) === 'or' ) ? 'OR' : 'AND';
        $version_logic   = ( ! empty( $filters->version_logic ) && strtolower( $filters->version_logic ) === 'or' ) ? 'OR' : 'AND';
        $status_logic    = ( ! empty( $filters->status_logic ) && strtolower( $filters->status_logic ) === 'or' ) ? 'OR' : 'AND';
        $version_mode    = ( ! empty( $filters->version_mode ) && $filters->version_mode === 'exclude' ) ? 'NOT REGEXP' : 'REGEXP';
        $status_mode     = ( ! empty( $filters->status_mode ) && $filters->status_mode === 'exclude' ) ? 'NOT REGEXP' : 'REGEXP';

        // Filters
        $theme_filters   = $filters->themes ?? [];
        $plugin_filters  = $filters->plugins ?? [];
        $version_filters = $filters->versions ?? [];
        $status_filters  = $filters->statuses ?? [];
        $core_filters    = $filters->core ?? [];
        
        $all_filter_clauses = [];

        // Helper: build a REGEXP that matches a JSON object containing the given key-value pairs (order-agnostic)
        $create_pattern = function( $name, $version = null, $status = null ) {
            // Always match by name
            $conditions = [ '"name":"' . $name . '"' ];
            if ( $version !== null ) {
                $conditions[] = '"version":"' . $version . '"';
            }
            if ( $status !== null ) {
                $conditions[] = '"status":"' . $status . '"';
            }
            // Match a single JSON object containing all specified key-value pairs in any order
            // Each condition checks that within a {...} block the key-value pair exists
            $parts = [];
            foreach ( $conditions as $cond ) {
                $escaped = str_replace( "{", "[{]", $cond );
                $escaped = str_replace( "}", "[}]", $escaped );
                $parts[] = '[{][^}]*' . $escaped . '[^}]*[}]';
            }
            return $parts;
        };

        // Themes/Plugins
        $primary_clauses = [];
        foreach ( $theme_filters as $theme ) {
            $parts = $create_pattern( $theme['name'] );
            $sub = [];
            foreach ( $parts as $p ) {
                $sub[] = "{$environments_table}.themes REGEXP '{$p}'";
            }
            $primary_clauses[] = "( " . implode( " AND ", $sub ) . " )";
        }
        foreach ( $plugin_filters as $plugin ) {
            $parts = $create_pattern( $plugin['name'] );
            $sub = [];
            foreach ( $parts as $p ) {
                $sub[] = "{$environments_table}.plugins REGEXP '{$p}'";
            }
            $primary_clauses[] = "( " . implode( " AND ", $sub ) . " )";
        }

        // Versions/Statuses
        $secondary_clauses = [];
        if ( ! empty( $version_filters ) ) {
            $version_sub_clauses = [];
            foreach ( $version_filters as $version ) {
                if ( empty( $version['slug'] ) ) { continue; }
                $parts = $create_pattern( $version['slug'], $version['name'] );
                $sub = [];
                foreach ( $parts as $p ) {
                    $version_sub_clauses[] = "{$environments_table}.{$version['type']} {$version_mode} '{$p}'";
                }
            }
            if ( ! empty( $version_sub_clauses ) ) {
                $secondary_clauses[] = "( " . implode( " {$version_logic} ", $version_sub_clauses ) . " )";
            }
        }
        if ( ! empty( $status_filters ) ) {
            $status_sub_clauses = [];
            foreach ( $status_filters as $status ) {
                if ( empty( $status['slug'] ) ) { continue; }
                $parts = $create_pattern( $status['slug'], null, $status['name'] );
                $sub = [];
                foreach ( $parts as $p ) {
                    $status_sub_clauses[] = "{$environments_table}.{$status['type']} {$status_mode} '{$p}'";
                }
            }
            if ( ! empty( $status_sub_clauses ) ) {
                $secondary_clauses[] = "( " . implode( " {$status_logic} ", $status_sub_clauses ) . " )";
            }
        }
        
        $all_filter_clauses = array_merge( $primary_clauses, $secondary_clauses );

        $where_clause = implode( " AND ", $base_conditions );
        if ( ! empty( $all_filter_clauses ) ) {
            $where_clause .= " AND ( " . implode( " {$primary_logic} ", $all_filter_clauses ) . " )";
        }

        // Core Filter Logic
        if ( ! empty( $core_filters ) ) {
            $core_clauses = [];
            foreach ( $core_filters as $version ) {
                $safe_version = esc_sql( $version );
                $core_clauses[] = "{$environments_table}.core = '{$safe_version}'";
            }
            $where_clause .= " AND ( " . implode( " OR ", $core_clauses ) . " )";
        }

        // Return site_id and environment_id
        $sql = "SELECT {$table}.site_id, {$environments_table}.environment_id
                FROM {$table}
                INNER JOIN {$environments_table} ON {$table}.site_id = {$environments_table}.site_id
                WHERE {$where_clause}
                ORDER BY {$table}.name ASC";
        
        return $wpdb->get_results( $sql );
    }

    static function fetch_sites_matching_versions_statuses( $arguments = [] ) {
        global $wpdb;
        $arguments  = (object) $arguments;
        $table      = self::_table();
        $patterns   = [];
        $conditions = "{$wpdb->prefix}captaincore_environments.environment = 'production'";
        if ( $arguments->filter ) {
            $arguments->filter = explode( "+", $arguments->filter );
            // WordPress thinks {} in SQL is a syntax error. To workaround we can wrap them in brackets likes so [{] and [}].
            $pattern = '{"name":"'.$arguments->filter[0].'","title":"[^"]*","status":"[^"]*","version":"[^"]*"}';
            $pattern = str_replace ( "{", "[{]", $pattern );
            $pattern = str_replace ( "}", "[}]", $pattern );
            $conditions = "$conditions AND {$wpdb->prefix}captaincore_environments.{$arguments->filter[1]} REGEXP '{$pattern}'";
        }

        foreach( $arguments->versions as $version ) {
            $version = (object) $version;
            if ( empty( $version->slug ) ) {
                continue;
            }
            // WordPress thinks {} in SQL is a syntax error. To workaround we can wrap them in brackets likes so [{] and [}].
            $pattern = '{"name":"'.$version->slug.'","title":"[^"]*","status":"[^"]*","version":"'.$version->name.'"}';
            $pattern = str_replace ( "{", "[{]", $pattern );
            $pattern = str_replace ( "}", "[}]", $pattern );
            if ( empty( $version_conditions ) ) {
                $version_conditions = "{$wpdb->prefix}captaincore_environments.{$version->type} REGEXP '{$pattern}'";
            } else {
                $version_conditions = "$version_conditions OR {$wpdb->prefix}captaincore_environments.{$version->type} REGEXP '{$pattern}'";
            }
        }
        if ( ! empty( $version_conditions ) ) {
            $conditions = "$conditions AND ( $version_conditions )";
        }

        foreach( $arguments->statuses as $status ) {
            $status = (object) $status;
            if ( empty( $status->slug ) ) {
                continue;
            }
            // WordPress thinks {} in SQL is a syntax error. To workaround we can wrap them in brackets likes so [{] and [}].
            $pattern = '{"name":"'.$status->slug.'","title":"[^"]*","status":"'.$status->name.'","version":"[^"]*"}';
            $pattern = str_replace ( "{", "[{]", $pattern );
            $pattern = str_replace ( "}", "[}]", $pattern );
            $conditions = "$conditions AND {$wpdb->prefix}captaincore_environments.{$status->type} REGEXP '{$pattern}'";
        }

        $sql = "SELECT {$table}.site
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_environments ON {$table}.site_id = {$wpdb->prefix}captaincore_environments.site_id
                WHERE $conditions
                AND {$table}.status = 'active'
                order by {$wpdb->prefix}captaincore_sites.`name` ASC";

        $results = array_column( $wpdb->get_results( $sql ), 'site' );
        return $results;
    }

    static function fetch_sites_for_process_log( $conditions = [] ) {
        global $wpdb;
        $table = self::_table();
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            $where_statements[] =  "{$table}.{$row} = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = "SELECT {$table}.site_id, {$wpdb->prefix}captaincore_sites.name
                FROM {$table}
                INNER JOIN {$wpdb->prefix}captaincore_sites ON {$table}.site_id = {$wpdb->prefix}captaincore_sites.site_id
                WHERE $where_statements 
                order by {$wpdb->prefix}captaincore_sites.`name` ASC";
        $results = $wpdb->get_results( $sql );
        return $results;
    }

    static function latest_capture( $conditions ) {
        $sort = "created_at";
        $sort_order = "DESC";
        global $wpdb;
        $where_statements = [];
        foreach ( $conditions as $row => $value ) {
            if ( is_array( $value ) ) {
                $values = implode( ", ", $value );
                $where_statements[] =  "`{$row}` IN ($values)";
                continue;
            }
            $where_statements[] =  "`{$row}` = '{$value}'";
        }
        $where_statements = implode( " AND ", $where_statements );
        $sql = 'SELECT * FROM ' . self::_table() . " WHERE $where_statements order by `created_at` DESC limit 1";
        $results = $wpdb->get_results( $sql );
        if ( is_array( $results ) && ! empty( $results ) ) {
            $results = $results[0];
        }
        return $results;
    }

    static function fetch_recipes( $sort = "created_at", $sort_order = "DESC" ) {
        global $wpdb;
        $user_id = get_current_user_id();
        $sql = 'SELECT * FROM ' . self::_table() . " WHERE user_id = '{$user_id}' or `public` = '1' order by `{$sort}` {$sort_order}";
        return $wpdb->get_results( $sql );
    }

    static function fetch_environments( $value ) {
        global $wpdb;
        $value = intval( $value );
        $sql   = 'SELECT * FROM ' . self::_table() . " WHERE `site_id` = '$value' order by `environment` ASC";
        return $wpdb->get_results( $sql );
    }

    static function fetch_field( $value, $environment, $field ) {
        global $wpdb;
        $value = intval( $value );
        $sql   = "SELECT $field FROM " . self::_table() . " WHERE `site_id` = '$value' and `environment` = '$environment' order by `created_at` DESC";
        return $wpdb->get_results( $sql );
    }

     // Perform CaptainCore database upgrades by running `CaptainCore\DB::upgrade();`
     public static function upgrade( $force = false ) {
        $required_version = (int) "46";
        $version          = (int) get_site_option( 'captaincore_db_version' );
    
        if ( $version >= $required_version and $force != true ) {
            return;
        }
    
        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_captures` (
            capture_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            site_id bigint(20) UNSIGNED NOT NULL,
            environment_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            git_commit varchar(100),
            pages longtext,
        PRIMARY KEY  (capture_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_snapshots` (
            snapshot_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            site_id bigint(20) UNSIGNED NOT NULL,
            environment_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            snapshot_name varchar(255),
            storage varchar(20),
            email varchar(100),
            notes longtext,
            expires_at datetime NOT NULL,
            token varchar(32),
        PRIMARY KEY  (snapshot_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_environments` (
            environment_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            site_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            environment varchar(255),
            address varchar(255),
            username varchar(255),
            password varchar(255),
            protocol varchar(255),
            port varchar(255),
            fathom varchar(255),
            home_directory varchar(255),
            database_name varchar(255),
            database_username varchar(255),
            database_password varchar(255),
            offload_enabled boolean,
            offload_provider varchar(255),
            offload_access_key varchar(255),
            offload_secret_key varchar(255),
            offload_bucket varchar(255),
            offload_path varchar(255),
            token varchar(255),
            php_memory varchar(255),
            storage varchar(20),
            visits varchar(20),
            core varchar(10),
            core_verify_checksums boolean default 1,
            subsite_count varchar(10),
            home_url varchar(255),
            capture_pages longtext,
            themes longtext,
            plugins longtext,
            users longtext,
            details longtext,
            screenshot boolean,
            monitor_enabled boolean,
            updates_enabled boolean,
            updates_exclude_themes longtext,
            updates_exclude_plugins longtext,
        PRIMARY KEY  (environment_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_processes` (
            process_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            name varchar(255),
            description longtext,
            time_estimate varchar(100),
            repeat_interval varchar(100),
            repeat_quantity varchar(100),
            roles longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (process_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_process_logs` (
            process_log_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            process_id bigint(20) UNSIGNED NOT NULL,
            user_id bigint(20) UNSIGNED NOT NULL,
            description longtext,
            public boolean,
            status varchar(50),
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            completed_at datetime NOT NULL,
        PRIMARY KEY  (process_log_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_process_log_site` (
            process_log_site_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            process_log_id bigint(20) UNSIGNED NOT NULL,
            site_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (process_log_site_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_process_log_file` (
            process_log_file_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            process_log_id bigint(20) UNSIGNED NOT NULL,
            site_id bigint(20) UNSIGNED,
            file_path varchar(512) NOT NULL,
            change_type varchar(20) NOT NULL,
            hunks longtext,
            lines_added int UNSIGNED NOT NULL DEFAULT 0,
            lines_removed int UNSIGNED NOT NULL DEFAULT 0,
            created_at datetime NOT NULL,
        PRIMARY KEY  (process_log_file_id),
        KEY process_log_id (process_log_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_recipes` (
            recipe_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            title varchar(255),
            content longtext,
            public boolean,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (recipe_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_scripts` (
            script_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            environment_id bigint(20) UNSIGNED NOT NULL,
            code longtext,
            details longtext,
            status varchar(255),
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (script_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_keys` (
            key_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            title varchar(255),
            fingerprint varchar(47),
            main boolean,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (key_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_invites` (
            invite_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            email varchar(255),
            token varchar(255),
            level varchar(255),
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            accepted_at datetime NOT NULL,
        PRIMARY KEY  (invite_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        // Permission/relationships data structure for CaptainCore: https://dbdiagram.io/d/5d7d409283427516dc0ba8b3
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_accounts` (
            account_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            billing_user_id bigint(20) UNSIGNED NOT NULL,
            name varchar(255),
            defaults longtext,
            plan longtext,
            metrics varchar(255),
            status varchar(255),
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (account_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_account_portals` (
            account_portal_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            domain varchar(255),
            configurations longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (account_portal_id)
        ) $charset_collate;";
        
        dbDelta($sql);
        
        // account_id determines which account is responsible for billing, customer_id determines customer ownership
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_sites` (
            site_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            customer_id bigint(20) UNSIGNED NOT NULL,
            name varchar(255),
            site varchar(255),
            provider_id varchar(255),
            provider_site_id varchar(255),
            provider varchar(255),
            token varchar(255),
            status varchar(255),
            details longtext,
            screenshot boolean,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (site_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_providers` (
            provider_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            name varchar(255),
            provider varchar(255),
            status varchar(255),
            details longtext,
            credentials longtext,
            configurations longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (provider_id)
        ) $charset_collate;";
        
        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_provider_actions` (
            provider_action_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            provider_id bigint(20) UNSIGNED NOT NULL,
            provider_key varchar(255),
            user_id bigint(20) UNSIGNED NOT NULL,
            status varchar(255),
            action longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (provider_action_id)
        ) $charset_collate;";
        
        dbDelta($sql);

    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_domains` (
            domain_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            remote_id varchar(255),
            provider_id varchar(255),
            provider_domain_id varchar(255),
            status varchar(255),
            price varchar(255),
            name varchar(255),
            details longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (domain_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_account_user` (
            account_user_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            user_id bigint(20) UNSIGNED NOT NULL,
            level varchar(255),
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (account_user_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_account_domain` (
            account_domain_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            domain_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (account_domain_id)
        ) $charset_collate;";
        
        dbDelta($sql);
    
        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_account_site` (
            account_site_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            account_id bigint(20) UNSIGNED NOT NULL,
            site_id bigint(20) UNSIGNED NOT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (account_site_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_scheduled_reports` (
            scheduled_report_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            site_ids longtext NOT NULL,
            account_id bigint(20) UNSIGNED DEFAULT NULL,
            `interval` varchar(20) NOT NULL,
            recipient varchar(255) NOT NULL,
            user_id bigint(20) UNSIGNED NOT NULL,
            next_run datetime NOT NULL,
            last_run datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (scheduled_report_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_web_risk_logs` (
            web_risk_log_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            total_sites int(11) UNSIGNED NOT NULL,
            threats_found int(11) UNSIGNED NOT NULL,
            errors_count int(11) UNSIGNED NOT NULL,
            details longtext,
            created_at datetime NOT NULL,
        PRIMARY KEY  (web_risk_log_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_job_tokens` (
            job_token_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            token varchar(64) NOT NULL,
            task_id bigint(20) UNSIGNED NOT NULL,
            user_id bigint(20) UNSIGNED NOT NULL,
            site_id bigint(20) UNSIGNED DEFAULT NULL,
            command varchar(255) DEFAULT NULL,
            created_at datetime NOT NULL,
        PRIMARY KEY  (job_token_id),
        UNIQUE KEY token (token),
        KEY user_id (user_id)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_security_threat_tracking` (
            security_threat_tracking_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            slug varchar(255) NOT NULL,
            version varchar(50) NOT NULL,
            type varchar(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'new',
            notes longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            resolved_at datetime DEFAULT NULL,
        PRIMARY KEY  (security_threat_tracking_id),
        UNIQUE KEY slug_version_type (slug, version, type),
        KEY status (status)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_activity_logs` (
            activity_log_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            user_id bigint(20) UNSIGNED NOT NULL,
            account_id bigint(20) UNSIGNED DEFAULT NULL,
            action varchar(100) NOT NULL,
            entity_type varchar(50) NOT NULL,
            entity_id bigint(20) UNSIGNED DEFAULT NULL,
            entity_name varchar(255) DEFAULT NULL,
            description text,
            context longtext,
            ip_address varchar(45) DEFAULT NULL,
            created_at datetime NOT NULL,
        PRIMARY KEY  (activity_log_id),
        KEY account_id (account_id),
        KEY entity_type (entity_type),
        KEY created_at (created_at)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_security_patch` (
            security_patch_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            slug varchar(255) NOT NULL,
            version varchar(50) NOT NULL,
            type varchar(20) NOT NULL DEFAULT 'plugin',
            title varchar(255) NOT NULL DEFAULT '',
            patched_version varchar(50) NOT NULL,
            download_url varchar(500) NOT NULL,
            description longtext,
            severity varchar(20) NOT NULL DEFAULT '',
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (security_patch_id),
        UNIQUE KEY slug_version_type (slug, version, type),
        KEY type (type)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_site_audits` (
            site_audit_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            site_id bigint(20) UNSIGNED NOT NULL,
            environment_id bigint(20) UNSIGNED NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'in_progress',
            filesystem_status varchar(20) DEFAULT NULL,
            wp_version varchar(20) DEFAULT NULL,
            php_version varchar(20) DEFAULT NULL,
            issues_count int(11) DEFAULT 0,
            plugins_count int(11) DEFAULT 0,
            scan_checks longtext,
            site_config longtext,
            admin_accounts longtext,
            timeline_events longtext,
            user_id bigint(20) UNSIGNED DEFAULT NULL,
            notes longtext,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
            completed_at datetime DEFAULT NULL,
            report_path varchar(255) DEFAULT NULL,
            report_type varchar(50) NOT NULL DEFAULT 'security_audit',
            dashboard_metrics longtext,
            summary longtext,
            sections longtext,
            report_title varchar(255) DEFAULT NULL,
            section_order longtext,
        PRIMARY KEY  (site_audit_id),
        KEY site_id (site_id),
        KEY environment_id (environment_id),
        KEY status (status),
        KEY created_at (created_at)
        ) $charset_collate;";

        dbDelta($sql);

        $sql = "CREATE TABLE `{$wpdb->base_prefix}captaincore_site_audit_findings` (
            site_audit_finding_id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
            site_audit_id bigint(20) UNSIGNED NOT NULL,
            severity varchar(20) NOT NULL,
            status varchar(20) NOT NULL DEFAULT 'open',
            title varchar(255) NOT NULL,
            description longtext,
            evidence longtext,
            recommendation longtext,
            resolution varchar(512) DEFAULT NULL,
            resolved_at datetime DEFAULT NULL,
            created_at datetime NOT NULL,
            updated_at datetime NOT NULL,
        PRIMARY KEY  (site_audit_finding_id),
        KEY site_audit_id (site_audit_id),
        KEY severity (severity),
        KEY status (status)
        ) $charset_collate;";

        dbDelta($sql);

        if ( ! empty( $wpdb->last_error ) ) {
            return $wpdb->last_error;
        }

        // Backfill account_user levels for v39
        if ( $version < 39 ) {
            $accounts = $wpdb->get_results( "SELECT account_id, plan FROM {$wpdb->base_prefix}captaincore_accounts" );
            foreach ( $accounts as $account ) {
                $plan = json_decode( $account->plan );
                if ( ! empty( $plan->billing_user_id ) ) {
                    $wpdb->query( $wpdb->prepare(
                        "UPDATE {$wpdb->base_prefix}captaincore_account_user SET level = 'full-billing' WHERE account_id = %d AND user_id = %d",
                        $account->account_id, $plan->billing_user_id
                    ) );
                }
            }
            $wpdb->query( "UPDATE {$wpdb->base_prefix}captaincore_account_user SET level = 'full' WHERE level IS NULL OR level = ''" );
        }

        update_site_option( 'captaincore_db_version', $required_version );
        echo "Updated `captaincore_db_version` to v$required_version";
    }

}