<?php
/**
 * MainWP Database Client
 *
 * This file handles all interactions with the Client DB.
 *
 * @package MainWP/Dashboard
 */

namespace MainWP\Dashboard;

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

/**
 * Class MainWP_DB_Client
 *
 * @package MainWP\Dashboard
 */
class MainWP_DB_Client extends MainWP_DB { // phpcs:ignore Generic.Classes.OpeningBraceSameLine.ContentAfterBrace -- NOSONAR.

    // phpcs:disable WordPress.DB.RestrictedFunctions, WordPress.DB.PreparedSQL.NotPrepared, Generic.Metrics.CyclomaticComplexity -- unprepared SQL ok, accessing the database directly to custom database functions.

    /**
     * Private static variable to hold the single instance of the class.
     *
     * @static
     *
     * @var mixed Default null
     */
    private static $instance = null;

    /**
     * Method instance()
     *
     * Create public static instance.
     *
     * @static
     * @return MainWP_DB_Client
     */
    public static function instance() {
        if ( null === static::$instance ) {
            static::$instance = new self();
        }
        return static::$instance;
    }

    /**
     * Constructor.
     *
     * Run each time the class is called.
     */
    public function __construct() {
        parent::__construct();
        add_filter( 'mainwp_db_install_tables', array( $this, 'hook_db_install_tables' ), 10, 3 );
    }

    /**
     * Method hook_db_install_tables()
     *
     * Get the query to install db tables.
     *
     * @param array  $sql input filter.
     * @param string $currentVersion Current db Version.
     * @param string $charset_collate charset collate.
     *
     * @return array $sql queries.
     */
    public function hook_db_install_tables( $sql, $currentVersion, $charset_collate ) {

        $tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients' ) . " (
    client_id int(11) NOT NULL auto_increment,
    image varchar(255) NOT NULL default '',
    name varchar(255) NOT NULL,
    address_1 varchar(255) NOT NULL default '',
    address_2 varchar(255) NOT NULL default '',
    city varchar(100) NOT NULL default '',
    zip varchar(32) NOT NULL default '',
    state varchar(200) NOT NULL default '',
    country varchar(200) NOT NULL default '',
    note longtext NOT NULL default '',
    selected_icon_info varchar(64) NOT NULL default '',
    client_email varchar(191) NOT NULL,
    client_phone varchar(100) NOT NULL default '',
    client_facebook varchar(255) NOT NULL default '',
    client_twitter varchar(255) NOT NULL default '',
    client_instagram varchar(255) NOT NULL default '',
    client_linkedin varchar(255) NOT NULL default '',
    created int(11) NOT NULL DEFAULT 0,
    `suspended` tinyint(1) NOT NULL DEFAULT 0,
    primary_contact_id int(11) NOT NULL default 0";

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '<=' ) ) {
            $tbl .= ',
        PRIMARY KEY (client_id)';
        }
        $tbl  .= ') ' . $charset_collate;
        $sql[] = $tbl;

        $tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_fields' ) . ' (
    field_id int(11) NOT NULL auto_increment,
    field_name varchar(191) NOT NULL DEFAULT "",
    field_desc varchar(255) NOT NULL DEFAULT "",
    client_id int(11) NOT NULL,
    UNIQUE KEY client_id_field_name (client_id,field_name)';

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '<=' ) ) {
            $tbl .= ',
        PRIMARY KEY  (`field_id`)  ';
        }

        $tbl  .= ') ' . $charset_collate;
        $sql[] = $tbl;

        $tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_field_values' ) . ' (
    value_id int(11) NOT NULL auto_increment,
    field_id int(11) NOT NULL,
    field_value longtext NOT NULL DEFAULT "",
    value_client_id int(11) NOT NULL,
    UNIQUE KEY value_client_id_field_id (value_client_id, field_id)';

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '<=' ) ) {
            $tbl .= ',
        PRIMARY KEY  (`value_id`) ';
        }

        $tbl  .= ') ' . $charset_collate;
        $sql[] = $tbl;

        $tbl = 'CREATE TABLE ' . $this->table_name( 'wp_clients_contacts' ) . ' (
    contact_id int(11) NOT NULL auto_increment,
    contact_client_id int(11) NOT NULL,
    contact_email varchar(191) NOT NULL,
    contact_image varchar(255) NOT NULL default "",
    contact_icon_info varchar(64) NOT NULL default "",
    contact_name varchar(255) NOT NULL,
    contact_phone varchar(100) NOT NULL default "",
    contact_role varchar(255) NOT NULL default "",
    facebook varchar(255) NOT NULL default "",
    twitter varchar(255) NOT NULL default "",
    instagram varchar(255) NOT NULL default "",
    linkedin varchar(255) NOT NULL default "",
    UNIQUE KEY contact_email (contact_email)';

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.69', '<=' ) ) {
            $tbl .= ',
        PRIMARY KEY (contact_id)';
        }
        $tbl  .= ') ' . $charset_collate;
        $sql[] = $tbl;

        return $sql;
    }

    /**
     * Method check_to_updates_reports_data_861()
     *
     * Check version less than 8.61 to import reports data.
     *
     * @param string $currentVersion Current db Version.
     *
     * @return void.
     */
    public function check_to_updates_reports_data_861( $currentVersion ) {

        $convert_pro_reports_dt    = false;
        $convert_client_reports_dt = false;
        if ( is_plugin_active( 'mainwp-pro-reports-extension/mainwp-pro-reports-extension.php' ) ) {
            $convert_pro_reports_dt = true;
        } elseif ( is_plugin_active( 'mainwp-client-reports-extension/mainwp-client-reports-extension.php' ) ) {
            $convert_client_reports_dt = true;
        }

        if ( ! $convert_pro_reports_dt && ! $convert_client_reports_dt ) {
            $table_token = esc_sql( $this->table_name( 'pro_reports_token' ) );
            $rslt        = $this->query( 'SHOW TABLES LIKE ' . $this->wpdb->prepare( '%s', $table_token ) );
            if ( static::num_rows( $rslt ) ) {
                $convert_pro_reports_dt = true;
            }

            $table_report_token = esc_sql( $this->table_name( 'client_report_token' ) );
            $rslt               = $this->query( 'SHOW TABLES LIKE ' . $this->wpdb->prepare( '%s', $table_report_token ) );
            if ( static::num_rows( $rslt ) ) {
                $convert_client_reports_dt = true;
            }
        }

        $this->insert_client_fields_861( $currentVersion );

        if ( $convert_pro_reports_dt ) {
            $this->pro_reports_check_updates_861( $currentVersion );
        } elseif ( $convert_client_reports_dt ) {
            $this->client_reports_check_updates_861( $currentVersion );
        }
    }

    /**
     * Method insert_client_fields_861()
     *
     * Check version less than 8.61 to import client reports data.
     *
     * @param string $currentVersion Current db Version.
     *
     * @return null.
     */
    public function insert_client_fields_861( $currentVersion ) {

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '>' ) ) {
            return;
        }

        $default_tokens = array(
            'client.name'              => 'Displays the Client Name',
            'client.contact.name'      => 'Displays the Client Contact Name',
            'client.contact.address.1' => 'Displays the Client Contact Address 1',
            'client.contact.address.2' => 'Displays the Client Contact Address 2',
            'client.city'              => 'Displays the Client City',
            'client.state'             => 'Displays the Client State',
            'client.zip'               => 'Displays the Client Zip',
            'client.phone'             => 'Displays the Client Phone',
            'client.email'             => 'Displays the Client Email',
            'client.note'              => 'Displays the Client Note',
        );

        // create or update default token.
        foreach ( $default_tokens as $field_name => $field_desc ) {
            $current = $this->get_client_fields_by( 'field_name', $field_name );
            if ( $current ) {
                $this->update_client_field(
                    $current->field_id,
                    array(
                        'field_name' => $field_name,
                        'field_desc' => $field_desc,
                    )
                );
            } else {
                $this->add_client_field(
                    array(
                        'field_name' => $field_name,
                        'field_desc' => $field_desc,
                        'client_id'  => 0,
                    )
                );

            }
        }
    }

    /**
     * Method client_reports_check_updates_861()
     *
     * Check version less than 8.61 to import client reports data.
     *
     * @param string $currentVersion Current db Version.
     *
     * @return null.
     */
    public function client_reports_check_updates_861( $currentVersion ) {

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '>' ) ) {
            return;
        }

        $tokens              = $this->client_reports_get_tokens();
        $sites_tokens_values = $this->client_reports_get_site_token_values();

        $this->reports_check_updates_861( $tokens, $sites_tokens_values );
    }

    /**
     * Method pro_reports_check_updates_861()
     *
     * Check version less than 8.61 to import pro reports data.
     *
     * @param string $currentVersion Current db Version.
     *
     * @return null.
     */
    public function pro_reports_check_updates_861( $currentVersion ) {

        if ( empty( $currentVersion ) || version_compare( $currentVersion, '8.61', '>' ) ) {
            return;
        }

        $tokens              = $this->pro_reports_get_tokens();
        $sites_tokens_values = $this->pro_reports_get_site_token_values();

        $this->reports_check_updates_861( $tokens, $sites_tokens_values );
    }


    /**
     * Method reports_check_updates_861()
     *
     * Check version less than 8.61 to import reports data.
     *
     * @param array $tokens Tokens.
     * @param array $sites_tokens_values Tokens values.
     *
     * @return void.
     */
    public function reports_check_updates_861( $tokens, $sites_tokens_values ) { // phpcs:ignore -- NOSONAR complexex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.

        $default_client_fields  = MainWP_Client_Handler::get_default_client_fields();
        $default_contact_fields = MainWP_Client_Handler::get_default_contact_fields();

        $compatible_tokens = MainWP_Client_Handler::get_compatible_tokens();

        $ids_to_tokens = array();

        if ( $tokens ) {
            foreach ( $tokens as $token ) {
                $tok_name                    = $token->token_name;
                $ids_to_tokens[ $token->id ] = array(
                    'token_name' => $tok_name,
                    'token_desc' => $token->token_description,
                );
            }
        }

        $ids_to_tokens_values = array();

        if ( $sites_tokens_values ) {
            foreach ( $sites_tokens_values as $token ) {
                if ( ! isset( $ids_to_tokens[ $token->token_id ] ) ) {
                    continue;
                }
                if ( empty( $token->token_value ) ) {
                    continue;
                }
                $tok_name = $ids_to_tokens[ $token->token_id ]['token_name'];

                $ids_to_tokens_values[ $token->site_id ][ $tok_name ] = array(
                    'token_value' => $token->token_value,
                    'token_desc'  => $ids_to_tokens[ $token->token_id ]['token_desc'],
                );
            }
        }

        $clients_to_add          = array();
        $primary_contacts_to_add = array();
        $custom_tokens_to_add    = array();

        foreach ( $ids_to_tokens_values as $site_id => $tokens ) {

            if ( empty( $tokens['client.email'] ) ) {
                continue;
            }

            $email = $tokens['client.email']['token_value'];

            if ( ! isset( $clients_to_add[ $email ] ) ) {
                $clients_to_add[ $email ] = array();
            }

            if ( ! isset( $clients_to_add[ $email ]['site_ids'] ) ) {
                $clients_to_add[ $email ]['site_ids'] = array();
            }

            $clients_to_add[ $email ]['site_ids'][] = $site_id;

            // prepare clients to add.
            foreach ( $default_client_fields as $field_name => $field ) {
                if ( ! isset( $tokens[ $field_name ] ) ) {
                    continue;
                }
                $clients_to_add[ $email ][ $field['db_field'] ] = $tokens[ $field_name ]['token_value'];
            }

            $flip_compatible_tokens = array_flip( $compatible_tokens ); // flip: new_token_name => old_token_name.
            // prepare primary contacts to add.
            foreach ( $default_contact_fields as $field_name => $field ) {

                // $field_name - new_token_name.
                // $tok_name - old_token_name.
                $tok_name = isset( $flip_compatible_tokens[ $field_name ] ) ? $flip_compatible_tokens[ $field_name ] : $field_name;
                if ( ! isset( $tokens[ $tok_name ] ) ) {
                    continue;
                }

                $primary_contacts_to_add[ $email ][ $field['db_field'] ] = $tokens[ $tok_name ]['token_value'];
            }

            foreach ( $tokens as $tok_name => $tok_value_desc ) {
                if ( isset( $default_client_fields[ $tok_name ] ) || isset( $default_contact_fields[ $tok_name ] ) ) {
                    continue;
                }
                if ( 'client.site.url' === $tok_name || 'client.site.name' === $tok_name ) { // avoid those tokens.
                    continue;
                }
                if ( isset( $compatible_tokens[ $tok_name ] ) ) {
                    continue;
                }
                $custom_tokens_to_add[ $email ][ $tok_name ] = $tok_value_desc;
            }
        }

        $emails_to_ids = array();
        foreach ( $clients_to_add as $email => $client_add ) {
            $selected_sites = array();
            if ( isset( $client_add['site_ids'] ) ) {
                $selected_sites = $client_add['site_ids'];
                unset( $client_add['site_ids'] );
            }

            $inserted = $this->update_client( $client_add ); // add new client.

            if ( $inserted ) {
                $client_id = $inserted->client_id;
                $this->update_selected_sites_for_client( $client_id, $selected_sites );
                $emails_to_ids[ $email ] = $client_id;
            }
        }

        foreach ( $emails_to_ids as $email => $client_id ) {

            if ( isset( $primary_contacts_to_add[ $email ] ) ) {
                $contact_add                      = $primary_contacts_to_add[ $email ];
                $contact_add['contact_client_id'] = $client_id;
                $inserted                         = $this->update_client_contact( $contact_add );
                if ( $inserted ) {
                    $params = array(
                        'client_id'          => $client_id, // update the client.
                        'primary_contact_id' => $inserted->contact_id,
                    );
                    $this->update_client( $params );
                }
            }

            if ( isset( $custom_tokens_to_add[ $email ] ) ) {
                $custom_tokens = $custom_tokens_to_add[ $email ];
                foreach ( $custom_tokens as $tok_name => $tok_value_desc ) {
                    $field_id = 0;
                    $current  = $this->get_client_fields_by( 'field_name', $tok_name ); // get general field by name.
                    if ( $current ) {
                        $field_id = $current->field_id;
                    } else {
                        $field     = array(
                            'field_name' => $tok_name,
                            'field_desc' => $tok_value_desc['token_desc'],
                            'client_id'  => 0, // it is general tokens.
                        );
                        $new_field = $this->add_client_field( $field );
                        if ( $new_field ) {
                            $field_id = $new_field->field_id;
                        }
                    }

                    if ( $field_id ) {
                        $this->update_client_field_value( $field_id, $tok_value_desc['token_value'], $client_id );
                    }
                }
            }
        }
    }


    /**
     * Method update_client.
     *
     * Create or update client.
     *
     * @param array $data Client data.
     * @param bool  $throw_out Throw or return error.
     *
     * @throws MainWP_Exception On errors.
     *
     * @return bool.
     */
    public function update_client( $data, $throw_out = false ) {
        if ( empty( $data ) || ! is_array( $data ) ) {
            return false;
        }

        $client_id = isset( $data['client_id'] ) ? (int) $data['client_id'] : 0;

        if ( isset( $data['client_email'] ) && ! empty( $data['client_email'] ) ) {
            $client_existed = $this->get_wp_client_by( 'client_email', $data['client_email'], ARRAY_A );
            if ( is_array( $client_existed ) && isset( $client_existed['client_id'] ) && ( empty( $client_id ) || $client_id !== (int) $client_existed['client_id'] ) ) {
                if ( $throw_out ) {
                    throw new MainWP_Exception( esc_html__( 'Client email exists. Please try again.', 'mainwp' ) );
                } else {
                    return false;
                }
            }
        }

        if ( ! empty( $client_id ) ) {
            $this->wpdb->update( $this->table_name( 'wp_clients' ), $data, array( 'client_id' => intval( $client_id ) ) );
            return $this->get_wp_client_by( 'client_id', $client_id );
        } elseif ( $this->wpdb->insert( $this->table_name( 'wp_clients' ), $data ) ) {
                return $this->get_wp_client_by( 'client_id', $this->wpdb->insert_id );
        }
        return false;
    }


    /**
     * Method update_client.
     *
     * Create or update client contact.
     *
     * @param array $data Contact data.
     *
     * @return bool.
     */
    public function update_client_contact( $data ) { // phpcs:ignore -- NOSONAR - complex.

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

        $contact_id = isset( $data['contact_id'] ) ? (int) $data['contact_id'] : 0;

        $contact_email = isset( $data['contact_email'] ) ? $data['contact_email'] : '';
        if ( ! empty( $contact_email ) ) {
            $existed_email = false;
            // check to valid email.
            $curret_contact = $this->get_wp_client_contact_by( 'contact_email', $contact_email );
            if ( $curret_contact && empty( $contact_id ) ) { // add new contact with existed email => failed.
                $existed_email = true;
            }

            if ( $curret_contact && ! empty( $contact_id ) && (int) $curret_contact->contact_id !== $contact_id ) { // update contact with existed email => failed.
                $existed_email = true;
            }

            if ( $existed_email ) {
                MainWP_Utility::update_flash_message( 'contact_existed_emails', $contact_email );
                return false;
            }
        }

        if ( ! empty( $contact_id ) ) {
            $this->wpdb->update( $this->table_name( 'wp_clients_contacts' ), $data, array( 'contact_id' => intval( $contact_id ) ) );
            return $this->get_wp_client_contact_by( 'contact_id', $contact_id );
        } elseif ( $this->wpdb->insert( $this->table_name( 'wp_clients_contacts' ), $data ) ) {
                return $this->get_wp_client_contact_by( 'contact_id', $this->wpdb->insert_id );
        }
        return false;
    }

    /**
     * Method get_wp_client_contact_by.
     *
     * Get client by.
     *
     * @param string $by by.
     * @param mixed  $value by value.
     * @param mixed  $obj Format data.
     * @param array  $params other params.
     *
     * @return mixed $result results.
     */
    public function get_wp_client_contact_by( $by = 'contact_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complex.

        $sql            = '';
        $table_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );

        if ( 'client_id' === $by && ! empty( $value ) ) {
            $sql = $this->wpdb->prepare( 'SELECT wc.*  FROM ' . $table_contacts . ' wc WHERE wc.contact_client_id=%d ', $value );
        }

        if ( ! empty( $sql ) ) {
            if ( OBJECT === $obj ) {
                $result = $this->wpdb->get_results( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            } else {
                $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            }
            return $result;
        }

        if ( 'contact_id' === $by && is_numeric( $value ) ) {
            $sql = $this->wpdb->prepare( 'SELECT wc.* FROM ' . $table_contacts . ' wc WHERE wc.contact_id=%d ', $value );
        } elseif ( 'contact_email' === $by && ! empty( $value ) ) {
            $sql = $this->wpdb->prepare( 'SELECT wc.*  FROM ' . $table_contacts . ' wc WHERE wc.contact_email=%s ', $value );
        }

        $result = null;
        if ( ! empty( $sql ) ) {
            if ( OBJECT === $obj ) {
                $result = $this->wpdb->get_row( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            } else {
                $result = $this->wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            }
        }
        return $result;
    }


    /**
     * Method delete_client.
     *
     * Delete client.
     *
     * @param int $client_id Client id of the contact.
     * @param int $contact_id Contact id to delete.
     *
     * @return bool Results.
     */
    public function delete_client_contact( $client_id, $contact_id ) {
        $current        = $this->get_wp_client_contact_by( 'contact_id', $contact_id );
        $table_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );
        if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_contacts . ' WHERE contact_client_id=%d AND contact_id = %d', $client_id, $contact_id ) ) ) {
            if ( ! empty( $current->contact_image ) ) {
                $dirs     = MainWP_System_Utility::get_mainwp_dir( 'client-images', true );
                $base_dir = $dirs[0];
                $old_file = $base_dir . '/' . $current->contact_image;
                MainWP_Utility::delete_file( $old_file );
            }
            return true;
        }
        return false;
    }


    /**
     * Method delete_client_contacts_by_client_id.
     *
     * Delete client contacts by client id.
     *
     * @param int $client_id Client id.
     *
     * @return bool Results.
     */
    public function delete_client_contacts_by_client_id( $client_id ) {

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

        $client_contacts = $this->get_wp_client_contact_by( 'client_id', $client_id, ARRAY_A );
        if ( $client_contacts ) {
            foreach ( $client_contacts as $contact ) {
                $this->delete_client_contact( $client_id, $contact['contact_id'] );
            }
        }
        return true;
    }


    /**
     * Method get_wp_client_by.
     *
     * Get client by.
     *
     * @param string $by by.
     * @param mixed  $value by value.
     * @param mixed  $obj Format data.
     * @param array  $params Others params. Supports:
     *               - 'with_selected_sites' (bool) Include selected sites.
     *               - 'with_tags' (bool) Include tags info.
     *               - 'by_tags' (array) Filter by tag IDs.
     *               - 'offset' (int) SQL offset for pagination.
     *               - 'limit' (int) SQL limit for pagination.
     *               - 'count_only' (bool) Return only count instead of results.
     *
     * @return mixed $result results.
     */
    public function get_wp_client_by( $by = 'client_id', $value = null, $obj = OBJECT, $params = array() ) { // phpcs:ignore -- NOSONAR - complexex function. Current complexity is the only way to achieve desired results, pull request solutions appreciated.

        $with_selected_sites = isset( $params['with_selected_sites'] ) && $params['with_selected_sites'] ? true : false;
        $with_tags           = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
        $group_ids           = isset( $params['by_tags'] ) ? $params['by_tags'] : '';
        $count_only          = isset( $params['count_only'] ) && $params['count_only'] ? true : false;
        $offset              = isset( $params['offset'] ) ? intval( $params['offset'] ) : null;
        $limit               = isset( $params['limit'] ) ? intval( $params['limit'] ) : null;

        // valid group ids.
        if ( is_array( $group_ids ) ) {
            $group_ids = array_filter(
                $group_ids,
                function ( $e ) {
                    if ( 'nogroups' === $e ) {
                        return true;
                    }
                    return ( is_numeric( $e ) && 0 < $e ) ? true : false;
                }
            );
        } else {
            $group_ids = '';
        }

        $select_sites  = '';
        $select_tags   = '';
        $where_clients = '';

        $sql = '';

        $table_wp               = esc_sql( $this->table_name( 'wp' ) );
        $table_wp_group         = esc_sql( $this->table_name( 'wp_group' ) );
        $table_group            = esc_sql( $this->table_name( 'group' ) );
        $table_clients          = esc_sql( $this->table_name( 'wp_clients' ) );
        $table_clients_contacts = esc_sql( $this->table_name( 'wp_clients_contacts' ) );

        if ( $with_selected_sites ) {
            $select_sites .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $table_wp . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ';
        }

        if ( ! empty( $group_ids ) ) {

            $join_group  = '';
            $where_group = '';

            if ( in_array( 'nogroups', $group_ids ) ) {
                $join_group = ' LEFT JOIN ' . $table_wp_group . ' wpgroup ON wp.id = wpgroup.wpid ';
                $group_ids  = array_filter(
                    $group_ids,
                    function ( $e ) {
                        return 'nogroups' !== $e;
                    }
                );
                if ( ! empty( $group_ids ) ) {
                    $groups      = implode( ',', array_map( 'intval', $group_ids ) );
                    $where_group = ' AND ( wpgroup.groupid IS NULL OR wpgroup.groupid IN (' . $groups . ') ) ';
                } else {
                    $where_group = ' AND wpgroup.groupid IS NULL ';
                }
            } else {
                $groups      = implode( ',', array_map( 'intval', $group_ids ) );
                $join_group  = ' JOIN ' . $table_wp_group . ' wpgroup ON wp.id = wpgroup.wpid ';
                $where_group = ' AND wpgroup.groupid IN (' . $groups . ') ';
            }

            $sql_tags    = ' SELECT wp.client_id FROM ' . $table_wp . ' wp ';
            $sql_tags   .= $join_group;
            $sql_tags   .= ' WHERE 1 ' . $where_group;
            $result_tags = $this->wpdb->get_results( $sql_tags ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql_tags built with escaped table names and validated numeric IDs, safe to use directly.

            if ( empty( $result_tags ) ) {
                return $count_only ? 0 : array();
            } else {
                $cli_ids = array();
                foreach ( $result_tags as $item ) {
                    $cli_ids[] = (int) $item->client_id;
                }
                $cli_ids       = array_values( array_unique( $cli_ids ) );
                $where_clients = ' AND wc.client_id IN (' . implode( ',', $cli_ids ) . ') ';
            }
        }

        if ( $with_tags ) {
            $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $table_wp . ' wp ';
            $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
            $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
            $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';

            $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $table_wp . ' wp ';
            $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
            $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
            $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
        }

        $join_contact = ' LEFT JOIN ' . $table_clients_contacts . ' cc ON wc.primary_contact_id = cc.contact_id ';

        if ( 'all' === $by ) {
            // Handle count-only mode for efficient pagination total.
            if ( $count_only ) {
                $sql  = ' SELECT COUNT(*) ';
                $sql .= ' FROM ' . esc_sql( $this->table_name( 'wp_clients' ) ) . ' wc ';
                $sql .= ' WHERE 1 ' . $where_clients;
                return (int) $this->wpdb->get_var( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            }

            $sql  = ' SELECT wc.*, cc.* ' . $select_sites . $select_tags;
            $sql .= ' FROM ' . esc_sql( $this->table_name( 'wp_clients' ) ) . ' wc ';
            $sql .= $join_contact;
            $sql .= ' WHERE 1 ' . $where_clients;
            $sql .= ' ORDER BY wc.name';

            // Add pagination if offset and limit are provided.
            if ( null !== $offset && null !== $limit && $limit > 0 ) {
                $sql .= $this->wpdb->prepare( ' LIMIT %d, %d', $offset, $limit );
            }
        }

        if ( ! empty( $sql ) ) {
            if ( OBJECT === $obj ) {
                $result = $this->wpdb->get_results( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            } else {
                $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            }
            return $result;
        }

        if ( 'client_id' === $by && is_numeric( $value ) ) {
            $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $table_clients . ' wc ' . $join_contact . ' WHERE wc.client_id=%d ', $value );
        } elseif ( 'client_email' === $by && ! empty( $value ) ) {
            $sql = $this->wpdb->prepare( 'SELECT wc.*, cc.* ' . $select_sites . $select_tags . ' FROM ' . $table_clients . ' wc ' . $join_contact . ' WHERE wc.client_email=%s ', $value );
        }

        $result = null;
        if ( ! empty( $sql ) ) {
            if ( OBJECT === $obj ) {
                $result = $this->wpdb->get_row( $sql, OBJECT ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            } else {
                $result = $this->wpdb->get_row( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() or escaped table name concatenation, safe to use directly.
            }
        }
        return $result;
    }

    /**
     * Method get_wp_clients.
     *
     * Get clients.
     *
     * @param array $params params.
     *
     * @return mixed result.
     */
    public function get_wp_clients( $params = array() ) { // phpcs:ignore -- NOSONAR - complex.

        $custom_field  = false;
        $with_tags     = false;
        $with_contacts = false;
        $count_only    = false;

        $s       = '';
        $exclude = array();
        $include = array();
        $limit   = '';

        $where       = '';
        $select_tags = '';

        $table_wp       = esc_sql( $this->table_name( 'wp' ) );
        $table_wp_group = esc_sql( $this->table_name( 'wp_group' ) );
        $table_group    = esc_sql( $this->table_name( 'group' ) );
        $table_clients  = esc_sql( $this->table_name( 'wp_clients' ) );

        if ( $params && is_array( $params ) ) {
            $client = isset( $params['client'] ) ? sanitize_text_field( wp_unslash( $params['client'] ) ) : '';
            if ( '' !== $client ) {
                $clients = preg_split( '/[;,]/', $client );
                $clients = array_filter( array_map( 'trim', $clients ) );

                $clientWhere = '';
                foreach ( $clients as $clt ) {
                    if ( is_numeric( $clt ) ) {
                        $clientWhere .= intval( $clt ) . ', ';
                    }
                }
                $clientWhere = rtrim( $clientWhere, ', ' );

                $where .= ' AND wc.client_id IN (' . $clientWhere . ') ';
            }
            $custom_field = isset( $params['custom_fields'] ) && $params['custom_fields'] ? true : false;

            $with_tags     = isset( $params['with_tags'] ) && $params['with_tags'] ? true : false;
            $with_contacts = isset( $params['with_contacts'] ) && $params['with_contacts'] ? true : false;
            $count_only    = isset( $params['count_only'] ) && $params['count_only'] ? true : false;

            $s        = isset( $params['s'] ) ? $params['s'] : '';
            $exclude  = isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ? wp_parse_id_list( $params['exclude'] ) : array();
            $include  = isset( $params['include'] ) && ! empty( $params['include'] ) ? wp_parse_id_list( $params['include'] ) : array();
            $status   = isset( $params['status'] ) && ! empty( $params['status'] ) ? wp_parse_list( $params['status'] ) : array();
            $page     = isset( $params['page'] ) ? intval( $params['page'] ) : false;
            $per_page = isset( $params['per_page'] ) ? intval( $params['per_page'] ) : false;

            if ( ! empty( $s ) ) {
                $s = trim( $s );
                // Use esc_like() to escape LIKE wildcards (%, _) then prepare() for SQL safety.
                $like   = '%' . $this->wpdb->esc_like( $s ) . '%';
                $where .= $this->wpdb->prepare(
                    ' AND ( wc.client_id LIKE %s OR wc.name LIKE %s OR wc.client_email LIKE %s ) ',
                    $like,
                    $like,
                    $like
                );
            }

            if ( ! empty( $exclude ) ) {
                $where .= ' AND  wc.client_id NOT IN (' . implode( ',', $exclude ) . ') ';
            }

            if ( ! empty( $include ) ) {
                $where .= ' AND  wc.client_id IN (' . implode( ',', $include ) . ') ';
            }

            if ( ! empty( $status ) ) {
                $suspended_status = array_map(
                    function ( $value ) {
                        $map_status_db = array(
                            'active'    => 0,
                            'suspended' => 1,
                            'lead'      => 2,
                            'lost'      => 3,
                        );
                        return isset( $map_status_db[ $value ] ) ? $map_status_db[ $value ] : '';
                    },
                    $status
                );
                $suspended_status = array_filter(
                    $suspended_status,
                    function ( $value ) {
                        return '' !== $value;
                    }
                );
                if ( ! empty( $suspended_status ) ) {
                    $where .= ' AND  wc.suspended IN (' . implode( ',', $suspended_status ) . ') ';
                }
            }

            if ( ! empty( $page ) && ! empty( $per_page ) ) {
                $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ',' . $per_page;
            }
        }

        if ( $with_tags ) {
            $select_tags .= ", ( SELECT GROUP_CONCAT(gr.name ORDER BY gr.name SEPARATOR ',') FROM " . $table_wp . ' wp ';
            $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
            $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
            $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroups ';

            $select_tags .= ", ( SELECT GROUP_CONCAT(gr.id ORDER BY gr.id SEPARATOR ',') FROM " . $table_wp . ' wp ';
            $select_tags .= ' LEFT JOIN ' . $table_wp_group . ' wpgr ON wp.id = wpgr.wpid ';
            $select_tags .= ' LEFT JOIN ' . $table_group . ' gr ON wpgr.groupid = gr.id ';
            $select_tags .= ' WHERE wp.client_id = wc.client_id ) as wpgroupids ';
        }

        if ( $count_only ) {
            $sql  = ' SELECT count(*) ';
            $sql .= ' FROM ' . $table_clients . ' wc ';
            $sql .= ' WHERE 1 ' . $where;
            return $this->wpdb->get_var( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names, safe to use directly.
        } else {
            $sql  = ' SELECT wc.* ';
            $sql .= ',( SELECT GROUP_CONCAT(wp.id SEPARATOR ",") FROM ' . $table_wp . ' wp WHERE wp.client_id = wc.client_id ) as selected_sites ' . $select_tags;
            $sql .= ' FROM ' . $table_clients . ' wc ';
            $sql .= ' WHERE 1 ' . $where . ' ORDER BY wc.name ' . $limit;
        }

        $result = $this->wpdb->get_results( $sql, ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names, safe to use directly.

        $contact_fields = array(
            'contact_id',
            'contact_client_id',
            'contact_email',
            'contact_name',
            'contact_phone',
            'contact_role',
            'contact_image',
            'facebook',
            'twitter',
            'instagram',
            'linkedin',
        );

        $output = array();

        foreach ( $result as $rst ) {

            if ( $custom_field ) {
                $custom_fields = $this->get_client_fields( true, $rst['client_id'] );
                if ( $custom_fields ) {
                    foreach ( $custom_fields as $field ) {
                        $rst[ $field->field_name ] = $field->field_value;
                    }
                }
            }
            if ( $with_tags && ! empty( $rst['wpgroupids'] ) && ! empty( $rst['wpgroups'] ) ) {
                $wpgroupids = explode( ',', $rst['wpgroupids'] );
                $wpgroups   = explode( ',', $rst['wpgroups'] );

                $tags = array();
                if ( is_array( $wpgroupids ) ) {
                    foreach ( $wpgroupids as $gidx => $groupid ) {
                        if ( $groupid && ! isset( $tags[ $groupid ] ) ) {
                            $tags[ $groupid ] = isset( $wpgroups[ $gidx ] ) ? $wpgroups[ $gidx ] : '';
                        }
                    }
                }

                $rst['tags'] = $tags;
            }

            if ( $with_contacts ) {
                $client_contacts = $this->get_wp_client_contact_by( 'client_id', $rst['client_id'], ARRAY_A );

                $contacts = array();

                if ( is_array( $client_contacts ) ) {
                    foreach ( $client_contacts as $gidx => $contact ) {
                        $contacts[ $contact['contact_id'] ] = MainWP_Utility::map_fields( $contact, $contact_fields, false );
                    }
                }
                $rst['contacts'] = $contacts;
            }

            $output[] = $rst;
        }
        return $output;
    }

    /**
     * Method update_selected_sites_for_client.
     *
     * Update client.
     *
     * @param int   $client_id client id.
     * @param array $site_ids site ids.
     *
     * @return bool true|false.
     */
    public function update_selected_sites_for_client( $client_id, $site_ids ) {
        if ( empty( $client_id ) ) {
            return false;
        }

        $site_ids = array_filter(
            $site_ids,
            function ( $e ) {
                return ( is_numeric( $e ) && 0 < $e ) ? true : false;
            }
        );

        $table_wp = esc_sql( $this->table_name( 'wp' ) );

        if ( is_array( $site_ids ) && ! empty( $site_ids ) ) {
            $safe_site_ids = implode( ',', array_map( 'intval', $site_ids ) );
            $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d AND id NOT IN (' . $safe_site_ids . ')', $client_id ) );
            $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=%d WHERE id IN (' . $safe_site_ids . ')', $client_id ) );
            return true;
        } else {
            $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
            return true;
        }
    }

    /**
     * Method delete_client.
     *
     * Delete client.
     *
     * @param int $client_id Client id to delete.
     * @return bool Results.
     */
    public function delete_client( $client_id ) {

        $current            = $this->get_wp_client_by( 'client_id', $client_id );
        $table_clients      = esc_sql( $this->table_name( 'wp_clients' ) );
        $table_wp           = esc_sql( $this->table_name( 'wp' ) );
        $table_fields       = esc_sql( $this->table_name( 'wp_clients_fields' ) );
        $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );

        if ( $current && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_clients . ' WHERE client_id=%d ', $client_id ) ) ) {
            $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET client_id=0 WHERE client_id=%d ', $client_id ) );
            $fields = $this->get_client_fields_by( 'client_id', $client_id );
            if ( $fields ) {
                foreach ( $fields as $field ) {
                    $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_fields . ' WHERE field_id=%d ', $field->field_id ) );
                    $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d AND value_client_id=%d ', $field->field_id, $client_id ) );
                }
            }

            if ( ! empty( $current->image ) ) {
                $dirs     = MainWP_System_Utility::get_mainwp_dir( 'client-images', true );
                $base_dir = $dirs[0];
                $old_file = $base_dir . '/' . $current->image;
                MainWP_Utility::delete_file( $old_file );
            }

            $this->delete_client_contacts_by_client_id( $client_id );

            $snapshot_info = $this->get_clients_snapshot_info();

            /**
             * Delete client
             *
             * Fires after delete a client.
             *
             * @param object $current client deleted.
             *
             * @since 4.5.1.1
             *
             * @since 6.0 Added $snapshot_info parameter.
             */
            do_action( 'mainwp_client_deleted', $current, $snapshot_info );

            return true;
        }
        return false;
    }


    /**
     * Get clients snapshot info.
     *
     * @return array
     */
    public function get_clients_snapshot_info() {

        $total_clients        = $this->get_wp_clients( array( 'count_only' => true ) );
        $total_active_clients = $this->get_wp_clients(
            array(
                'status'     => '0',
                'count_only' => true,
            )
        );
        $total_suspended      = $this->get_wp_clients(
            array(
                'status'     => '1',
                'count_only' => true,
            )
        );
        $count_sites          = MainWP_DB::instance()->get_websites_count();
        $avg_sites_per_client = $total_clients && $count_sites ? round( $count_sites / $total_clients, 2 ) : 0;

        return array(
            'clients_active_count'   => $total_active_clients,
            'clients_inactive_count' => $total_suspended,
            'avg_sites_per_client'   => $avg_sites_per_client,
        );
    }


    /**
     * Method get_websites_by_client_ids.
     *
     * Get websites by client.
     *
     * @param array $client_ids Client ids.
     * @param array $params other params.
     *
     * @return mixed $result Results.
     */
    public function get_websites_by_client_ids( $client_ids, $params = array() ) {

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

        if ( ! is_array( $params ) ) {
            $params = array();
        }

        $client = '';
        if ( is_array( $client_ids ) ) {
            // valid client ids.
            $client_ids = array_filter(
                $client_ids,
                function ( $e ) {
                    return is_numeric( $e ) && ! empty( $e ) ? true : false; // to valid client ids.
                }
            );
            $client     = implode( ';', $client_ids );
        } else {
            $client = $client_ids;
        }

        $params['client'] = $client;

        return MainWP_DB::instance()->get_websites_for_current_user( $params );
    }

    /**
     * Method get_number_sites_of_client.
     *
     * Get client by.
     *
     * @param int $client_id Client id.
     *
     * @return int Number of sites.
     */
    public function get_number_sites_of_client( $client_id ) {
        if ( empty( $client_id ) ) {
            return false;
        }
        $table_wp = esc_sql( $this->table_name( 'wp' ) );
        return $this->wpdb->get_var( $this->wpdb->prepare( 'SELECT COUNT(wp.id) FROM ' . $table_wp . ' wp WHERE wp.client_id =%d', $client_id ) );
    }

    /**
     * Method count_total_clients.
     *
     * Count total of Clients.
     *
     * @return int Total number.
     */
    public function count_total_clients() {
        $table_clients = esc_sql( $this->table_name( 'wp_clients' ) );
        return $this->wpdb->get_var( 'SELECT COUNT(client_id) FROM ' . $table_clients );
    }


    /**
     * Method add_client_field.
     *
     * Add client field.
     *
     * @param mixed $field Client fields data.
     *
     * @return mixed bool|results.
     */
    public function add_client_field( $field ) {
        if ( ! empty( $field['field_name'] ) && isset( $field['client_id'] ) ) {
            if ( $this->get_client_fields_by( 'field_name', $field['field_name'], $field['client_id'] ) ) { // field name existed for the client can not create.
                return false;
            }
            if ( $this->wpdb->insert( $this->table_name( 'wp_clients_fields' ), $field ) ) {
                return $this->get_client_fields_by( 'field_id', $this->wpdb->insert_id );
            }
        }
        return false;
    }

    /**
     * Method update_client_field.
     *
     * Update client field.
     *
     * @param int   $field_id field id.
     * @param array $field field data.
     *
     * @return mixed|false field.
     */
    public function update_client_field( $field_id, $field ) {
        if ( $field_id && ! empty( $field['field_name'] ) ) {
            $current = $this->get_client_fields_by( 'field_id', $field_id );
            if ( $current && $this->wpdb->update( $this->table_name( 'wp_clients_fields' ), $field, array( 'field_id' => $field_id ) ) ) {
                return $this->get_client_fields_by( 'field_id', $current->field_id );
            }
        }
        return false;
    }

    /**
     * Method update_client_field_value.
     *
     * Update client field value.
     *
     * @param int   $field_id field id.
     * @param mixed $value field value.
     * @param int   $client_id client id.
     *
     * @return mixed|false field data.
     */
    public function update_client_field_value( $field_id, $value, $client_id ) {
        if ( ! empty( $field_id ) ) {
            $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );
            $row                = $this->wpdb->get_row( $this->wpdb->prepare( 'SELECT * FROM ' . $table_field_values . ' WHERE field_id = %d AND value_client_id=%d ', $field_id, $client_id ) );
            if ( $row ) {
                if ( $row->field_value != $value ) { //phpcs:ignore -- to valid.
                    $this->wpdb->update( $table_field_values, array( 'field_value' => $value ), array( 'value_id' => $row->value_id ) );
                }
                return true;
            } else {
                return $this->wpdb->insert(
                    $table_field_values,
                    array(
                        'field_id'        => $field_id,
                        'field_value'     => $value,
                        'value_client_id' => $client_id,
                    )
                );
            }
        }
        return false;
    }

    /**
     * Method get_client_fields_by.
     *
     * Get client field.
     *
     * @param string $by by.
     * @param mixed  $value field value.
     * @param int    $client_id client id.
     *
     * @return mixed|null $field field data.
     */
    public function get_client_fields_by( $by = 'field_id', $value = null, $client_id = 0 ) {

        if ( empty( $by ) || empty( $value ) ) {
            return null;
        }

        $sql          = '';
        $table_fields = esc_sql( $this->table_name( 'wp_clients_fields' ) );

        if ( 'client_id' === $by ) {
            $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `client_id`=%d ', $value );
            return $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
        }

        if ( 'field_name' === $by ) {
            $value = str_replace( array( '[', ']' ), '', $value );
        }

        if ( 'field_id' === $by ) {
            $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_id`=%d', $value );
        } elseif ( 'field_name' === $by ) {
            $sql = $this->wpdb->prepare( 'SELECT * FROM ' . $table_fields . ' WHERE `field_name` = %s AND `client_id`=%d ', $value, $client_id );
        }

        $field = null;

        if ( ! empty( $sql ) ) {
            $field = $this->wpdb->get_row( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql uses wpdb->prepare() with escaped table name, safe to use directly.
        }

        return $field;
    }

    /**
     * Method get_client_fields_by_params.
     *
     * Get client fields by params.
     *
     * @param array  $params params.
     * @param string $obj OBJECT|ARRAY_A.
     *
     * @return mixed|null $fields fields data.
     */
    public function get_client_fields_by_params( $params = array(), $obj = OBJECT ) {  // phpcs:ignore -- NOSONAR - complex.

        if ( ! is_array( $params ) ) {
            $params = array();
        }

        // Initialize custom_where if it does not exist.
        $where   = '';
        $limit   = '';
        $orderby = 'clients_fields.field_id DESC';

        // Parse exclude and include lists.
        $parse_lists = static function ( $lists ) {
            // Convert string to array.
            if ( is_string( $lists ) ) {
                $lists = array_map( 'trim', explode( ',', $lists ) );
            }

            // Out default.
            $out = array(
                'ids'   => array(),
                'names' => array(),
            );

            foreach ( (array) $lists as $v ) {
                if ( '' === $v || null === $v ) {
                    continue;
                }

                if ( is_int( $v ) || ( is_string( $v ) && ctype_digit( $v ) ) ) {
                    $out['ids'][] = (int) $v;
                } else {
                    $out['names'][] = (string) $v;
                }
            }
            return $out;
        };

        // Handle exclude.
        if ( isset( $params['exclude'] ) && ! empty( $params['exclude'] ) ) {
            ['ids' => $ids, 'names' => $names] = $parse_lists( $params['exclude'] );
            $sub_conditions                    = array();
            $sub_conditions[]                  = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id NOT IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
            $sub_conditions[]                  = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name NOT IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
            $sub_conditions                    = array_filter( $sub_conditions );
            $where                            .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
        }

        // Handle include.
        if ( isset( $params['include'] ) && ! empty( $params['include'] ) ) {
            ['ids' => $ids, 'names' => $names] = $parse_lists( $params['include'] );
            $sub_conditions                    = array();
            $sub_conditions[]                  = empty( $ids ) ? '' : $this->wpdb->prepare( 'clients_fields.field_id IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')', ...$ids );
            $sub_conditions[]                  = empty( $names ) ? '' : $this->wpdb->prepare( 'clients_fields.field_name IN (' . implode( ',', array_fill( 0, count( $names ), '%s' ) ) . ')', ...$names );
            $sub_conditions                    = array_filter( $sub_conditions );
            $where                            .= empty( $sub_conditions ) ? '' : ' AND (' . implode( ' AND ', $sub_conditions ) . ')';
        }

        // Handle search.
        if ( isset( $params['search'] ) && ! empty( $params['search'] ) ) {
            $search_term = $this->escape( htmlspecialchars( $params['search'] ) );
            $where      .= ' AND clients_fields.field_name LIKE "%' . $search_term . '%"';
        }

        // Handle client id.
        if ( isset( $params['client_id'] ) && ! empty( $params['client_id'] ) ) {
            $client_ids = wp_parse_id_list( $params['client_id'] );
            $where     .= empty( $client_ids ) ? '' : ' AND clients_fields.client_id IN (' . implode( ',', $client_ids ) . ')';
        }

        // Handel page and per_page.
        $page     = '';
        $per_page = '';
        if ( isset( $params['page'] ) && ! empty( $params['page'] ) ) {
            $page = (int) $params['page'];
        }

        if ( isset( $params['per_page'] ) && ! empty( $params['per_page'] ) ) {
            $per_page = (int) $params['per_page'];
        }

        if ( ! empty( $page ) && ! empty( $per_page ) ) {
            $limit = ' LIMIT ' . ( $page - 1 ) * $per_page . ', ' . $per_page;
        }

        // Build SQL.
        $sql = 'SELECT * FROM ' . $this->table_name( 'wp_clients_fields' ) . ' clients_fields WHERE 1=1 ' . $where . ' ORDER BY ' . $orderby . $limit;

        return $this->wpdb->get_results( $sql, $obj ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with wpdb->prepare() and escaped table name, safe to use directly.
    }

    /**
     * Method get_client_fields()
     *
     * Get client fields.
     *
     * @param bool $general Get general fields OR not.
     * @param int  $client_id Client id.
     * @param bool $field_name_index index result with field name.
     *
     * @return mixed results.
     */
    public function get_client_fields( $general = true, $client_id = 0, $field_name_index = false ) { // phpcs:ignore -- NOSONAR - complex.
        $where = '';

        if ( $client_id ) {
            if ( true === $general ) {
                $where = ' f.client_id = ' . intval( $client_id ) . ' OR ( f.client_id = 0 AND ( v.value_client_id IS NULL OR v.value_client_id = ' . intval( $client_id ) . ') ) ';
            } else {
                $where = ' f.client_id = ' . intval( $client_id );
            }
        } elseif ( true === $general ) {
            $where = ' f.client_id = 0';
        }

        if ( '' === $where ) {
            return false;
        }

        $gene_results       = array();
        $table_fields       = esc_sql( $this->table_name( 'wp_clients_fields' ) );
        $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );

        if ( $general ) {
            $gene_sql     = 'SELECT f.* FROM ' . $table_fields . ' f WHERE f.client_id = 0 ORDER BY f.field_name ';
            $gene_results = $this->wpdb->get_results( $gene_sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $gene_sql built with escaped table names and hardcoded WHERE condition, safe to use directly.
        }

        if ( $client_id ) { // get fields values of client.
            $sql = 'SELECT f.*, v.field_value as field_value FROM ' . $table_fields . ' f LEFT JOIN ' .
            $table_field_values . ' v ON f.field_id = v.field_id WHERE ' . $where . ' ORDER BY f.field_name ';
        } else {
            $sql = 'SELECT f.* FROM ' . $table_fields . ' f WHERE ' . $where . ' ORDER BY f.field_name '; // value_client_id to compatible result.
        }

        $results = $this->wpdb->get_results( $sql ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter -- $sql built with escaped table names and validated numeric values in $where clause, safe to use directly.

        $fields_list = array();
        if ( $field_name_index ) {
            if ( $results ) {
                foreach ( $results as $item ) {
                    $fields_list[ $item->field_name ] = $item;
                }
            }
            if ( $gene_results ) {
                foreach ( $gene_results as $gene_item ) {
                    if ( ! isset( $fields_list[ $gene_item->field_name ] ) ) {
                        $gene_item->field_value                = '';
                        $fields_list[ $gene_item->field_name ] = $gene_item;
                    }
                }
            }
        } else {
            $check_list = array();
            if ( $results ) {
                foreach ( $results as $item ) {
                    $check_list[ $item->field_name ] = true;
                    $fields_list[]                   = $item;
                }
            }
            if ( $gene_results ) {
                foreach ( $gene_results as $gene_item ) {
                    if ( ! isset( $check_list[ $gene_item->field_name ] ) ) {
                        $check_list[ $gene_item->field_name ] = true;
                        $gene_item->field_value               = '';
                        $fields_list[]                        = $gene_item;
                    }
                }
            }
        }
        return $fields_list;
    }

    /**
     * Suspend - Unsuspended websites.
     *
     * @param int $client_id Client id.
     * @param int $suspend_val Suspend value: 0 or 1.
     *
     * @return int|boolean The result.
     */
    public function suspend_unsuspend_websites_by_client_id( $client_id, $suspend_val ) {
        if ( empty( $client_id ) ) {
            return false;
        }
        $table_wp = esc_sql( $this->table_name( 'wp' ) );
        return $this->wpdb->query( $this->wpdb->prepare( 'UPDATE ' . $table_wp . ' SET suspended=%d WHERE client_id=%d ', $suspend_val, $client_id ) );
    }

    /**
     * Method delete_client_field_by.
     *
     * Delete client field by.
     *
     * @param string $by by.
     * @param mixed  $value field value.
     * @param int    $client_id client id.
     *
     * @return bool Deleted or not.
     */
    public function delete_client_field_by( $by = 'field_id', $value = false, $client_id = false ) {

        $table_fields       = esc_sql( $this->table_name( 'wp_clients_fields' ) );
        $table_field_values = esc_sql( $this->table_name( 'wp_clients_field_values' ) );

        if ( 'field_id' === $by && $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_fields . ' WHERE field_id=%d AND client_id=%d', $value, $client_id ) ) ) { // delete individual or general client field.
            if ( $client_id > 0 ) { // individual field value.
                $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d AND value_client_id=%d', $value, $client_id ) ); // delete individual tokens values, for one client.
            } else {
                $this->wpdb->query( $this->wpdb->prepare( 'DELETE FROM ' . $table_field_values . ' WHERE field_id=%d', $value ) ); // delete general tokens values, multi clients (value_client_id).
            }
            return true;
        }

        return false;
    }

    /**
     * Method pro_reports_get_tokens()
     *
     * Get pro reports get tokens data.
     *
     * @return mixed results.
     */
    public function pro_reports_get_tokens() {
        $table_token = esc_sql( $this->table_name( 'pro_reports_token' ) );
        return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY type DESC, token_name ASC' );
    }

    /**
     * Method pro_reports_get_site_token_values()
     *
     * Get pro reports get tokens values data.
     *
     * @return mixed results.
     */
    public function pro_reports_get_site_token_values() {
        $table_site_token = esc_sql( $this->table_name( 'pro_reports_site_token' ) );
        return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
    }

    /**
     * Method client_reports_get_tokens()
     *
     * Get client reports get tokens data.
     *
     * @return mixed results.
     */
    public function client_reports_get_tokens() {
        $table_token = esc_sql( $this->table_name( 'client_report_token' ) );
        return $this->wpdb->get_results( 'SELECT * FROM ' . $table_token . ' WHERE 1 = 1 ORDER BY token_name ASC' );
    }

    /**
     * Method client_reports_get_site_token_values()
     *
     * Get client reports get tokens values data.
     *
     * @return mixed results.
     */
    public function client_reports_get_site_token_values() {
        $table_site_token = esc_sql( $this->table_name( 'client_report_site_token' ) );
        return $this->wpdb->get_results( ' SELECT * FROM ' . $table_site_token . ' WHERE 1 ' );
    }
}
