<?php
/*
 * Jetpack CRM
 * https://jetpackcrm.com
 * V1.1.18
 *
 * Copyright 2020 Automattic
 *
 * Date: 30/08/16
 */

defined( 'ZEROBSCRM_PATH' ) || exit( 0 );

/*
======================================================
	MIGRATION FUNCS
	====================================================== */

global $zeroBSCRM_migrations; $zeroBSCRM_migrations = array(
	'288', // build client portal page (moved to shortcodes) if using
	'2963', // 2.96.3 - installs page templates
	'29999', // Flush permalinks
	'411', // 4.11.0 - Ensure upload folders are secure
	'50', // 5.0 - Alter external sources table for existing users (added origin)
	'53', // 5.3 - Migrate all encrypted data to new encryption endpoints
	'54', // 5.4 - Support pinned logs, migrate all log meta stored in old dehydrated fashion
	'543', // 5.4.3 - Deletes unwanted .htaccess files
	'544', // 5.4.2 Forces re-install of default fonts
	'55', // 5.5 Deletes orphaned rows linked to invoices in the objlinks table
	'55a', // 5.5a Recompiles segments after wp_loaded
	'551', // 5.5.1 Deletes orphaned aka rows linked to contacts since deleted
	'560', // 5.6.0 Moves old folder structure (zbscrm-store) to new (jpcrm-storage)
	'task_offset_fix', // removes task timezone offsets from database
	'refresh_user_roles', // Refresh user roles
	'regenerate_tag_slugs', // Regenerate tag slugs
	'create_workflows_table', // Create "workflows" table.
	'invoice_language_fixes', // Store invoice statuses and mappings consistently
	'gh3465_increase_city_field_size',  // from gh issue 3465, increases the city field size to 200
	'tax_rate_precision_fix', // increase tax rate precision from 2 to 10 decimal places
	'ensure_notifications_table', // Ensure notifications table exists
);

global $zeroBSCRM_migrations_requirements; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
$zeroBSCRM_migrations_requirements = array( // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	'288'  => array( 'postsettings' ),
	'53'   => array( 'postsettings' ),
	'5402' => array( 'postsettings' ),
	'55a'  => array( 'wp_loaded' ),
);

// mark's a migration complete
function zeroBSCRM_migrations_markComplete( $migrationKey = -1, $logObj = false ) {

	global $zeroBSCRM_migrations;

	if ( ! empty( $migrationKey ) && in_array( $migrationKey, $zeroBSCRM_migrations ) ) {

		$completedMigrations   = zeroBSCRM_migrations_getCompleted();
		$completedMigrations[] = $migrationKey;

		// we're using wp options because they're reliable OUTSIDE of the scope of our settings model
		// ... which has changed through versions
		// the separation here is key, at 2.88 WH discovered much re-running + pain due to this.
		// stick to a separate migration system (away from zbssettings)
		update_option( 'zbsmigrations', $completedMigrations, false );

		// log opt?
		update_option(
			'zbsmigration' . $migrationKey,
			array(
				'completed' => time(),
				'meta'      => $logObj,
			),
			false
		);

	}
}

// gets the list of completed migrations
function zeroBSCRM_migrations_getCompleted() {

	// we're using wp options because they're reliable OUTSIDE of the scope of our settings model
	// ... which has changed through versions
	// the separation here is key, at 2.88 WH discovered much re-running + pain due to this.
	// stick to a separate migration system (away from zbssettings)

	// BUT WAIT! hilariously, for those who already have finished migrations, this'll re-run them
	// ... so here we 'MIGRATE' the migrations :o ffs
	global $zbs;
	$migrations = $zbs->settings->get( 'migrations' ); if ( isset( $migrations ) && is_array( $migrations ) && count( $migrations ) > 0 ) {

		$existingMigrationsMigration = get_option( 'zbsmigrationsdal', -1 );

		if ( $existingMigrationsMigration == -1 ) {
			// copy over +
			// to stop this ever rerunning + confusing things, we set an option to say migrated the migrations, LOL
			update_option( 'zbsmigrations', $migrations, false );
			update_option( 'zbsmigrationsdal', 2, false );
		}
	}

	// normal return
	return get_option( 'zbsmigrations', array() );
}

// gets details on a migration
function jpcrm_migrations_get_migration( $migrationKey = '' ) {

	// we're using wp options because they're reliable OUTSIDE of the scope of our settings model
	// ... which has changed through versions
	// the separation here is key, at 2.88 WH discovered much re-running + pain due to this.
	// stick to a separate migration system (away from zbssettings)
	$finished   = false;
	$migrations = zeroBSCRM_migrations_getCompleted();
	if ( in_array( $migrationKey, $migrations ) ) {
		$finished = true;
	}

	return array( $finished, get_option( 'zbsmigration' . $migrationKey, false ) );
}

function zeroBSCRM_migrations_run( $settingsArr = false, $run_at = 'init' ) {

	global $zeroBSCRM_migrations, $zeroBSCRM_migrations_requirements;

		// catch migration block removal (can be run from system status):
	if ( jpcrm_perms_manage_options() && isset( $_GET['resetmigrationblock'] ) && wp_verify_nonce( sanitize_text_field( wp_unslash( $_GET['_wpnonce'] ?? '' ) ), 'resetmigrationblock' ) ) {

		// unblock migration blocks
		delete_option( 'zbsmigrationpreloadcatch' );
		delete_option( 'zbsmigrationblockerrors' );

		// flag
		$migrationBlocksRemoved = true;
	}

	#} Check if we've been stumped by blocking errs, and STOP migrating if so
	$blockingErrs = get_option( 'zbsmigrationblockerrors', false );
	if ( $blockingErrs !== false && ! empty( $blockingErrs ) ) {
		return false;
	}

	#} load migrated list if not loaded
	$migratedAlreadyArr = zeroBSCRM_migrations_getCompleted();

	#} Run count
	$migrationRunCount = 0;

	#} cycle through any migrations + fire if not fired.
	if ( count( $zeroBSCRM_migrations ) > 0 ) {
		foreach ( $zeroBSCRM_migrations as $migration ) {

			if ( ! in_array( $migration, $migratedAlreadyArr ) && function_exists( 'zeroBSCRM_migration_' . $migration ) ) {

				$run = true;

				// check reached state
				if ( isset( $zeroBSCRM_migrations_requirements[ $migration ] ) ) {

					// 'preload' requirement means this migration needs to run AFTER a reload AFTER the previous migration
					// ... so if preload here, we kill this loop, if prev migrations have run
					if ( in_array( 'preload', $zeroBSCRM_migrations_requirements[ $migration ] ) && $migrationRunCount > 0 ) {

						// ... as a catch to stop infinite reloads, we check whether more than 3 of these have run in a row, and we stop that.
						$previousAttempts = get_option( 'zbsmigrationpreloadcatch', array() );
						if ( ! is_array( $previousAttempts ) ) {
							$previousAttempts = array();
						}
						if ( ! isset( $previousAttempts[ $migration ] ) ) {
							$previousAttempts[ $migration ] = 1;
						}
						if ( $previousAttempts[ $migration ] < 5 ) {

							// update count
							++$previousAttempts[ $migration ];
							update_option( 'zbsmigrationpreloadcatch', $previousAttempts, false );

							// stop running migrations, reload the page
							header( 'Refresh:0' );
							exit( 0 );

						} else {

							// set a global which'll show up on systemstatus if this state occurs.
							update_option( 'zbsmigrationblockerrors', $migration, false );

							// expose an error that the world's about to rupture
							add_action( 'after-zerobscrm-admin-init', 'zeroBSCRM_adminNotices_majorMigrationError' );
							add_action( 'admin_notices', 'zeroBSCRM_adminNotices_majorMigrationError' );

						}
					}

					// assume func
					foreach ( $zeroBSCRM_migrations_requirements[ $migration ] as $check ) {

						// skip 'preload', dealt with above
						// skip 'wp_loaded', dealt with in second run
						if ( $check !== 'preload' && $check !== 'wp_loaded' ) {

							$checkFuncName = 'zeroBSCRM_migrations_checks_' . $check;
							if ( ! call_user_func( $checkFuncName ) ) {
								$run = false;
							}
						}
					}

					// wp_loaded
					if ( in_array( 'wp_loaded', $zeroBSCRM_migrations_requirements[ $migration ] ) ) {

						$run = false;

						if ( $run_at == 'wp_loaded' ) {
							$run = true;
						}
					}
				}

				// go
				if ( $run ) {

					// run migration
					call_user_func( 'zeroBSCRM_migration_' . $migration );

					// update count
					++$migrationRunCount;

				}
			}
		}
	}
}

function zeroBSCRM_migrations_checks_postsettings() {

	global $zbs;
	/*
	didn't work:
	if (isset($zbs->settings) && method_exists($zbs->settings,'get')){
		$possiblyInstalled = $zbs->settings->get('settingsinstalled',true);
		if (isset($possiblyInstalled) && $possiblyInstalled > 0) return true;
	} */
	// HARD DB settings check
	try {
		$potentialDBSetting = $zbs->DAL->getSetting(
			array(
				'key'         => 'settingsinstalled',
				'fullDetails' => false,
			)
		);

		if ( isset( $potentialDBSetting ) && $potentialDBSetting > 0 ) {

			return true;

		}
	} catch ( Exception $e ) {

	}

	return false;
}

// general migration mechanism error
function zeroBSCRM_adminNotices_majorMigrationError() {

	// pop in a Notify Me Notification here instead....?
	if ( get_current_user_id() > 0 ) {

		// already sent?
		$msgSent = get_transient( 'zbs-migration-general-errors' );
		if ( ! $msgSent ) {

			zeroBSCRM_notifyme_insert_notification( get_current_user_id(), -999, -1, 'migration.blocked.errors', 'migration.blocked.errors' );
			set_transient( 'zbs-migration-general-errors', 20, 24 * 7 * HOUR_IN_SECONDS );

		}
	}
}

/*
======================================================
	/ MIGRATION FUNCS
	====================================================== */

/*
======================================================
	MIGRATIONS
	====================================================== */

	/*
	* Migration 2.88 - build client portal page (moved to shortcodes) if using
	*/
function zeroBSCRM_migration_288() {

	global $zbs;

	zeroBSCRM_portal_checkCreatePage();

	zeroBSCRM_migrations_markComplete( '288', array( 'updated' => '1' ) );
}

	/*
	* Migration 2.4 - Refresh user roles
	*  Previously this was a number of template related migrations
	*  for v5 we combined these, though in time the need for this method of install should be done away with
	*  Previously, migrations: 2.96.3, 2.96.4, 2.96.6, 2.97.4, 4.0.7, 4.0.8
	*/
function zeroBSCRM_migration_2963() {

	global $zbs, $wpdb, $ZBSCRM_t;

	#} Check + create
	zeroBSCRM_checkTablesExist();

	#} Make the DB emails...
	zeroBSCRM_populateEmailTemplateList();

	// ===== Previously: Migration 2.96.3 - adds new template for 'client portal pw reset'

	#} default is admin email and CRM name
	// now all done via zeroBSCRM_mailDelivery_defaultFromname
	$from_name = zeroBSCRM_mailDelivery_defaultFromname();

	/*
		This wasn't used in end, switched to default mail delivery opt
	$from_address = zeroBSCRM_mailDelivery_defaultEmail();; //default WordPress admin email ?
	$reply_to = '';
	$cc = ''; */
	$deliveryMethod = zeroBSCRM_getMailDeliveryDefault();

	$ID       = 6;
	$reply_to = '';
	$cc       = '';
	$bcc      = '';

	#} The email stuff...
	$subject = __( 'Your Client Portal Password', 'zero-bs-crm' );
	$content = zeroBSCRM_mail_retrieveDefaultBodyTemplate( 'clientportalpwreset' );
	$active  = 1; // 1 = true..
	if ( zeroBSCRM_mailTemplate_exists( $ID ) == 0 ) {
		$content = zeroBSCRM_mailTemplate_processEmailHTML( $content );
		// zeroBSCRM_insertEmailTemplate($ID,$from_name,$from_address,$reply_to,$cc,$bcc,$subject,$content,$active);
		zeroBSCRM_insertEmailTemplate( $ID, $deliveryMethod, $bcc, $subject, $content, $active );
	}

	// ===== / Previously: Migration 2.96.3

	// ===== Previously: last one hadn't got the html file, this ADDS file proper :)

	#} default is admin email and CRM name
	// now all done via zeroBSCRM_mailDelivery_defaultFromname
	$from_name = zeroBSCRM_mailDelivery_defaultFromname();

	/*
		This wasn't used in end, switched to default mail delivery opt
	$from_address = zeroBSCRM_mailDelivery_defaultEmail();; //default WordPress admin email ?
	$reply_to = '';
	$cc = ''; */
	$deliveryMethod = zeroBSCRM_getMailDeliveryDefault();

	$ID       = 6;
	$reply_to = '';
	$cc       = '';
	$bcc      = '';

	// BRUTAL DELETE old one
	$wpdb->delete( $ZBSCRM_t['system_mail_templates'], array( 'zbsmail_id' => $ID ) );

	#} The email stuff...
	$subject = __( 'Your Client Portal Password', 'zero-bs-crm' );
	$content = zeroBSCRM_mail_retrieveDefaultBodyTemplate( 'clientportalpwreset' );

	$active = 1; // 1 = true..
	if ( zeroBSCRM_mailTemplate_exists( $ID ) == 0 ) {
		$content = zeroBSCRM_mailTemplate_processEmailHTML( $content );
		// zeroBSCRM_insertEmailTemplate($ID,$from_name,$from_address,$reply_to,$cc,$bcc,$subject,$content,$active);
		zeroBSCRM_insertEmailTemplate( $ID, $deliveryMethod, $bcc, $subject, $content, $active );
	}

	// ===== / Previously: last one hadn't got the html file, this ADDS file proper :)

	// ===== Previously: adds template for 'invoice summary statement sent'

	#} default is admin email and CRM name
	// now all done via zeroBSCRM_mailDelivery_defaultFromname
	$from_name = zeroBSCRM_mailDelivery_defaultFromname();

	/*
		This wasn't used in end, switched to default mail delivery opt
	$from_address = zeroBSCRM_mailDelivery_defaultEmail();; //default WordPress admin email ?
	$reply_to = '';
	$cc = ''; */
	$deliveryMethod = zeroBSCRM_getMailDeliveryDefault();

	$ID       = 7;
	$reply_to = '';
	$cc       = '';
	$bcc      = '';

	#} The email stuff...
	$subject = __( 'Your Statement', 'zero-bs-crm' );
	$content = zeroBSCRM_mail_retrieveDefaultBodyTemplate( 'invoicestatementsent' );

	// BRUTAL DELETE old one
	$wpdb->delete( $ZBSCRM_t['system_mail_templates'], array( 'zbsmail_id' => $ID ) );

	$active = 1; // 1 = true..
	if ( zeroBSCRM_mailTemplate_exists( $ID ) == 0 ) {
		$content = zeroBSCRM_mailTemplate_processEmailHTML( $content );
		// zeroBSCRM_insertEmailTemplate($ID,$from_name,$from_address,$reply_to,$cc,$bcc,$subject,$content,$active);
		zeroBSCRM_insertEmailTemplate( $ID, $deliveryMethod, $bcc, $subject, $content, $active );
	}

	// ===== / Previously: adds template for 'invoice summary statement sent'

	// ===== Previously: 2.97.4 - fixes duplicated email templates (found on 2 installs so far)

	// 7 template emails up to here :)
	for ( $i = 0; $i <= 7; $i++ ) {

		// count em
		$sql = $wpdb->prepare( 'SELECT ID FROM ' . $ZBSCRM_t['system_mail_templates'] . ' WHERE zbsmail_id = %d GROUP BY ID ORDER BY zbsmail_id DESC, zbsmail_lastupdated DESC', $i );
		$r   = $wpdb->get_results( $sql, ARRAY_A );

			// if too many, delete oldest (few?)
		if ( is_array( $r ) && count( $r ) > 1 ) {

			$count = 0;

			// first stays, as the above selects in order by last updated
			foreach ( $r as $x ) {

				// if already got one, delete this (extra)
				if ( $count > 0 ) {

					// BRUTAL DELETE old one
					$wpdb->delete( $ZBSCRM_t['system_mail_templates'], array( 'ID' => $x['ID'] ) );

				}

				++$count;

			}
		}
	}

	// ===== / Previously: 2.97.4 - fixes duplicated email templates (found on 2 installs so far)

	// ===== Previously: 4.0.7 - corrects outdated task notification template

	// retrieve existing template - hardtyped
	$existingTemplate = $wpdb->get_var( 'SELECT zbsmail_body FROM ' . $ZBSCRM_t['system_mail_templates'] . ' WHERE ID = 6' );

	// load new
	$newTemplate = zeroBSCRM_mail_retrieveDefaultBodyTemplate( 'tasknotification' ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	// back it up into a WP option if was different
	if ( $existingTemplate !== $newTemplate ) { // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		update_option( 'jpcrm_tasknotificationtemplate', $existingTemplate, false ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	}

	// overwrite
	$sql = 'UPDATE ' . $ZBSCRM_t['system_mail_templates'] . ' SET zbsmail_body = %s WHERE ID = 6';
	$q   = $wpdb->prepare( $sql, array( $newTemplate ) );
	$wpdb->query( $q );

	// ===== / Previously: 4.0.7 - corrects outdated task notification template

	// ===== Previously: 4.0.8 - Set the default reference type for invoices & Update the existing template for email notifications (had old label)

	if ( $zbs->DAL->invoices->getFullCount() > 0 ) {
		// The user has used the invoice module. Default reference type = manual
		$zbs->settings->update( 'reftype', 'manual' );
	}

	// Update the existing template for email notifications (had old label)
	global $ZBSCRM_t, $wpdb;

	// retrieve existing template - hardtyped
	$existingTemplate = $wpdb->get_var( 'SELECT zbsmail_body FROM ' . $ZBSCRM_t['system_mail_templates'] . ' WHERE ID = 4' );

	// load new
	$newTemplate = zeroBSCRM_mail_retrieveDefaultBodyTemplate( 'invoicesent' );

	// back it up into a WP option if was different
	if ( $existingTemplate !== $newTemplate ) {
		update_option( 'jpcrm_invnotificationtemplate', $existingTemplate, false );
	}

	// overwrite
	$sql = 'UPDATE ' . $ZBSCRM_t['system_mail_templates'] . ' SET zbsmail_body = %s WHERE ID = 4';
	$q   = $wpdb->prepare( $sql, array( $newTemplate ) );
	$wpdb->query( $q );

	// ===== / Previously: 4.0.8 - Set the default reference type for invoices & Update the existing template for email notifications (had old label)

	zeroBSCRM_migrations_markComplete( '2963', array( 'updated' => '1' ) );
}

	/*
	* Migration 2.99.99 - set permalinks to flush (was used with v3.0 migration, left in tact as portal may be dependent)
	*/
function zeroBSCRM_migration_29999() {

	// set permalinks to flush, this'll cause them to be refreshed on 3000 migration
	// ... as that has preload setting
	jpcrm_flag_for_flush_rewrite();

	// fini
	zeroBSCRM_migrations_markComplete( '29999', array( 'updated' => 1 ) );
}

	/*
	* Migration 4.11.0 - secure upload folders
	*  previously:
	*  4.5.0 - Adds indexing protection to directories with potentially sensitive .html files
	*  4.11.0 - secure upload folders
	*/
function zeroBSCRM_migration_411() {

	$wp_uploads_dir = wp_upload_dir();

	// directories to secure
	// if these ever expand beyond this we should move the list to core & manage periodic checks
	$directories = array(

		ZEROBSCRM_PATH . 'templates/',
		ZEROBSCRM_PATH . 'templates/emails/',
		ZEROBSCRM_PATH . 'templates/invoices/',
		ZEROBSCRM_PATH . 'templates/quotes/',

		$wp_uploads_dir['basedir'] . '/' . 'zbscrm-store/_wip/',

	);

	// secure them!
	foreach ( $directories as $directory ) {
		jpcrm_create_and_secure_dir_from_external_access( $directory, true );
	}

	jpcrm_create_and_secure_dir_from_external_access( $wp_uploads_dir['basedir'] . '/' . 'zbscrm-store/', false );

	// mark complete
	zeroBSCRM_migrations_markComplete( '411', array( 'updated' => 1 ) );
}

	/*
	* Migration 5.0 - Alter external sources table for existing users (added origin)
	*/
function zeroBSCRM_migration_50() {

	global $zbs, $wpdb, $ZBSCRM_t;

	// external source tweak
	if ( ! zeroBSCRM_migration_tableHasColumn( $ZBSCRM_t['externalsources'], 'zbss_origin' ) ) {

		$sql = 'ALTER TABLE ' . $ZBSCRM_t['externalsources'] . ' ADD COLUMN `zbss_origin` VARCHAR(400) NULL DEFAULT NULL AFTER `zbss_uid`, ADD INDEX (zbss_origin);';
		$wpdb->query( $sql );

	}

	// add transaction status

	// build string
	$transaction_statuses = zeroBSCRM_getTransactionsStatuses( true );
	$deleted_string       = __( 'Deleted', 'zero-bs-crm' );
	if ( ! in_array( $deleted_string, $transaction_statuses ) ) {
		$transaction_statuses[] = $deleted_string;
	}
	$transaction_statuses_str = implode( ',', $transaction_statuses );

	// update
	$customisedFields                              = $zbs->settings->get( 'customisedfields' );
	$customisedFields['transactions']['status'][1] = $transaction_statuses_str;
	$zbs->settings->update( 'customisedfields', $customisedFields );

	// mark complete
	zeroBSCRM_migrations_markComplete( '50', array( 'updated' => 1 ) );
}

	/*
	* 5.3 - Migrate all encrypted data to new encryption endpoints
	*/
function zeroBSCRM_migration_53() {

	global $zbs;

	// load libs

	// ~5.3
	if ( ! function_exists( 'zeroBSCRM_encrypt' ) ) {
		require ZEROBSCRM_INCLUDE_PATH . 'ZeroBSCRM.Encryption.php';
	}

	// 5.3~
	$zbs->load_encryption();

	// count
	$successful_recryptions = 0;

	// Mail Delivery methods (if any):

	// previous decrypt key
	$decryption_key = hex2bin( zeroBSCRM_getSetting( 'smtpkey' ) );

	// retrieve existing
	$existing_mail_delivery_methods = zeroBSCRM_getSetting( 'smtpaccs' );
	if ( ! is_array( $existing_mail_delivery_methods ) ) {
		$existing_mail_delivery_methods = array();
	}

	// cycle through them and re-encrypt
	$replacement_delivery_methods = array();
	foreach ( $existing_mail_delivery_methods as $method_key => $method_array ) {

			$updated_method_array = $method_array;

		if ( isset( $method_array['pass'] ) ) {

			// decrypt (hiding deprecation notices via param)
			$password = zeroBSCRM_encryption_unsafe_process( 'decrypt', $method_array['pass'], $decryption_key, zeroBSCRM_get_iv( true ), true );

			// This is used as a fallback because some users can still have passwords
			// that were encrypted using the wrong IV.
			if ( ! $password ) {
				$password = zeroBSCRM_encryption_unsafe_process( 'decrypt', $method_array['pass'], $decryption_key, $decryption_key, true );
			}

			if ( $password ) {

				// encrypt password:
				$updated_method_array['pass'] = $zbs->encryption->encrypt( $password, 'smtp' );

			} else {

				// keep existing ciphertext; likely already updated but otherwise corrupt
				$updated_method_array['pass'] = $method_array['pass'];

			}

			++$successful_recryptions;

		}

			$replacement_delivery_methods[ $method_key ] = $updated_method_array;

	}

	// update em
	$zbs->settings->update( 'smtpaccs', $replacement_delivery_methods );

	// There was some old usage of pwmanager on companys with CPTs, for now we're skipping support.
	// $pws = get_post_meta($id,$zbsPasswordManager['dbkey'],true);

	// hash secret if not already hashed
	$api_secret = $zbs->DAL->setting( 'api_secret' ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
	if ( str_starts_with( $api_secret, 'zbscrm_' ) ) {
		$hashed_api_secret = $zbs->encryption->hash( $api_secret );
		$zbs->DAL->updateSetting( 'api_secret', $hashed_api_secret ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
	}

	global $wpdb, $ZBSCRM_t;
	// add indexes for performance
	if ( jpcrm_database_server_has_ability( 'fulltext_index' ) && ! jpcrm_migration_table_has_index( $ZBSCRM_t['customfields'], 'search' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['customfields'] . ' ADD FULLTEXT INDEX `search` (`zbscf_objval`);';
		$wpdb->query( $sql );
	}
	if ( ! jpcrm_migration_table_has_index( $ZBSCRM_t['taglinks'], 'zbstl_tagid+zbstl_objtype' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['taglinks'] . ' ADD INDEX `zbstl_tagid+zbstl_objtype` (`zbstl_tagid`,`zbstl_objtype`) USING BTREE;';
		$wpdb->query( $sql );
	}
	if ( ! jpcrm_migration_table_has_index( $ZBSCRM_t['externalsources'], 'zbss_uid+zbss_source+zbss_objtype' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['externalsources'] . ' ADD INDEX `zbss_uid+zbss_source+zbss_objtype` (`zbss_uid`,`zbss_source`,`zbss_objtype`) USING BTREE;';
		$wpdb->query( $sql );
	}
	if ( ! jpcrm_migration_table_has_index( $ZBSCRM_t['meta'], 'zbsm_objid+zbsm_key+zbsm_objtype' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['meta'] . ' ADD INDEX `zbsm_objid+zbsm_key+zbsm_objtype` (`zbsm_objid`,`zbsm_key`,`zbsm_objtype`) USING BTREE;';
		$wpdb->query( $sql );
	}
	if ( ! jpcrm_migration_table_has_index( $ZBSCRM_t['logs'], 'zbsl_created' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['logs'] . ' ADD INDEX `zbsl_created` (`zbsl_created`) USING BTREE;';
		$wpdb->query( $sql );
	}
	if ( ! jpcrm_migration_table_has_index( $ZBSCRM_t['contacts'], 'zbsc_status' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['contacts'] . ' ADD INDEX `zbsc_status` (`zbsc_status`) USING BTREE;';
		$wpdb->query( $sql );
	}

	// remove errant .htaccess file
	$wp_uploads_dir  = wp_upload_dir();
	$errant_htaccess = $wp_uploads_dir['basedir'] . '/' . 'zbscrm-store/.htaccess';
	if ( file_exists( $errant_htaccess ) ) {
		unlink( $errant_htaccess );
	}
	// mark complete
	zeroBSCRM_migrations_markComplete( '53', array( 'updated' => $successful_recryptions ) );
}

	/*
	* Migration 5.4
 * - Support pinned logs.
 * - Migrate all log meta stored in old dehydrated fashion. (Will do in 1k chunks until finished.)
	*/
function zeroBSCRM_migration_54() {

	global $zbs, $wpdb, $ZBSCRM_t;

	// add zbsl_pinned to log table if not existing
	if ( ! zeroBSCRM_migration_tableHasColumn( $ZBSCRM_t['logs'], 'zbsl_pinned' ) ) {
		$sql = 'ALTER TABLE ' . $ZBSCRM_t['logs'] . ' ADD `zbsl_pinned` int(1) NULL AFTER `zbsl_longdesc`;';
		$wpdb->query( $sql );
	}

	// get outdated log meta count
	$outdated_log_meta_count = (int) $wpdb->get_var( 'SELECT COUNT(ID) FROM ' . $ZBSCRM_t['meta'] . ' WHERE zbsm_objtype = 8 AND zbsm_key = "logmeta"' );

	if ( $outdated_log_meta_count > 0 ) {

		// get outdated meta records
		$outdated_log_meta_records = $wpdb->get_results( 'SELECT * FROM ' . $ZBSCRM_t['meta'] . ' WHERE zbsm_objtype = 8 AND zbsm_key = "logmeta" ORDER BY ID DESC LIMIT 5000' );

		foreach ( $outdated_log_meta_records as $log_record ) {

			// hydrate - Note that `[]` doesn't hydrate into array with this
			$log_meta = $zbs->DAL->decodeIfJSON( $zbs->DAL->stripSlashes( $log_record->zbsm_val ) );

			// insert new line foreach meta
			if ( is_array( $log_meta ) ) {

				foreach ( $log_meta as $key => $value ) {

					$zbs->DAL->updateMeta( ZBS_TYPE_LOG, $log_record->zbsm_objid, $zbs->DAL->makeSlug( $key ), $value );

				}
			}

			// delete old 'dehydrated whole' line
			zeroBSCRM_db2_deleteGeneric( $log_record->ID, 'meta' );

		}

		// any left?
		$outdated_log_meta_count = (int) $wpdb->get_var( 'SELECT COUNT(ID) FROM ' . $ZBSCRM_t['meta'] . ' WHERE zbsm_objtype = 8 AND zbsm_key = "logmeta"' );

	}

	if ( $outdated_log_meta_count == 0 ) {

		// mark complete
		zeroBSCRM_migrations_markComplete( '54', array( 'updated' => 1 ) );

	}
}

	/*
	* Migration 5.4.3
	* - Removes unwanted .htaccess files
	*/
function zeroBSCRM_migration_543() {
	// recursively deletes all .htaccess files starting from the root storage folder
	$root_storage = zeroBSCRM_privatisedDirCheck();
	if ( $root_storage !== false ) {
		$recursive_file_iterator = new RecursiveIteratorIterator( new RecursiveDirectoryIterator( $root_storage['path'] ) );
		$htaccess_files          = array();

		foreach ( $recursive_file_iterator as $file ) {
			if ( $file->isDir() || $file->getBasename() != '.htaccess' ) {
				continue;
			}
			$htaccess_files[] = $file->getPathname();
		}

		foreach ( $htaccess_files as $errant_htaccess ) {
			if ( is_file( $errant_htaccess ) ) {
					unlink( $errant_htaccess );
			}
		}
	}

	// mark this migration as complete
	zeroBSCRM_migrations_markComplete( '543', array( 'updated' => 1 ) );
}

	/*
	* Migration 5.4.4
	* - Forces re-install of default fonts (moved to new JPCRM storage folder)
	*/
function zeroBSCRM_migration_544() {

	global $zbs;

	// font reinstall
	$shouldBeInstalled = zeroBSCRM_getSetting( 'feat_pdfinv' );
	if ( $shouldBeInstalled == '1' ) {

		// force reinstall of fonts
		$fonts = $zbs->get_fonts();
		if ( ! $fonts->extract_and_install_default_fonts() ) {
			return false;
		}
	}

	// mark complete
	zeroBSCRM_migrations_markComplete( '544', array( 'updated' => 1 ) );
}

	/*
	* Migration 5.5
	* - Deletes orphaned rows linked to invoices in the objlinks table
	*/
function zeroBSCRM_migration_55() {

	global $zbs, $wpdb, $ZBSCRM_t;

	// Deletes links when missing invoices are the 'to' object
	$wpdb->query(
		' DELETE FROM ' . $ZBSCRM_t['objlinks']
		. ' WHERE '
		. ' zbsol_objtype_to = ' . ZBS_TYPE_INVOICE
		. ' AND zbsol_objid_to NOT IN ( SELECT ID from ' . $ZBSCRM_t['invoices'] . ' ) '
	);

	// Deletes links when missing invoices are the 'from' object
	$wpdb->query(
		' DELETE FROM ' . $ZBSCRM_t['objlinks']
		. ' WHERE '
		. ' zbsol_objtype_from = ' . ZBS_TYPE_INVOICE
		. ' AND zbsol_objid_from NOT IN ( SELECT ID from ' . $ZBSCRM_t['invoices'] . ' ) '
	);

	// Deletes orphaned line items
	$wpdb->query(
		' DELETE FROM ' . $ZBSCRM_t['lineitems'] . ' WHERE ID NOT IN'
		. ' ('
		. '   SELECT zbsol_objid_from FROM ' . $ZBSCRM_t['objlinks']
		. '   WHERE '
		. '    zbsol_objtype_from = ' . ZBS_TYPE_LINEITEM
		. ' )'
	);

	// mark complete
	zeroBSCRM_migrations_markComplete( '55', array( 'updated' => 1 ) );
}

	/*
	* Migration 5.5a
	* Recompiles segments, runs on later schedule (wp_loaded)
	*/
function zeroBSCRM_migration_55a() {

	global $zbs;

	// recompile segments with new condition names
	$zbs->DAL->segments->compile_all_segments();

	// mark complete
	zeroBSCRM_migrations_markComplete( '55a', array( 'updated' => 1 ) );
}

	/*
	* Migration 5.5.1
	* - Deletes orphaned aka rows linked to contacts since deleted
	*/
function zeroBSCRM_migration_551() {

	global $zbs, $wpdb, $ZBSCRM_t;

	// Deletes orphaned aka rows
	$wpdb->query(
		'DELETE FROM ' . $ZBSCRM_t['aka']
			. ' WHERE aka_type = ' . ZBS_TYPE_CONTACT . ' AND aka_id NOT IN'
			. ' (SELECT id FROM ' . $ZBSCRM_t['contacts'] . ')'
	);

	// mark complete
	zeroBSCRM_migrations_markComplete( '551', array( 'updated' => 1 ) );
}

/**
 * Migration 5.6.0
 * Moves files from the old file structure (zbscrm-store) to the new
 * one (jpcrm-storage) for rows from the meta table that have their object type
 * equals to ZBS_TYPE_CONTACT and a file in the zbsm_val column.
 *
 * @param object $meta_row Row from the meta table that needs to be updated.
 *
 * @return void
 */
function zeroBSCRM_migration_560_move_custom_file_upload_box( $meta_row ) { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.FunctionNameInvalid
	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	jpcrm_migration_load_wp_filesystem_direct();

	// Skip if this is not a custom file for a contact
	// (the only type that should exist, but we are being extra careful here).
	if ( $meta_row->zbsm_objtype !== ZBS_TYPE_CONTACT ) {
		return;
	}
	$file_path = $meta_row->zbsm_val;
	// Skip if this file doesn't exist (user may have deleted using the filesystem).
	if ( ! file_exists( $file_path ) ) {
		error_log( sprintf( 'JPCRM migration error while searching for upload box file %s', $file_path ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
		return;
	}
	$new_dir = jpcrm_storage_dir_info_for_contact( $meta_row->zbsm_objid );
	// Skip if there is no information for the files subfolder.
	if ( $new_dir === false || ! isset( $new_dir['files'] ) ) {
		error_log( sprintf( 'JPCRM migration error missing subfolder files for contact ID %s', $meta_row->zbsm_objid ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
		return;
	}
	$new_dir_info         = $new_dir['files'];
	$upload_folder_exists = jpcrm_create_and_secure_dir_from_external_access( $new_dir_info['path'], false );
	if ( $upload_folder_exists === false ) {
		// We shouldn't have any errors here, but if we do we log it and skip this one.
		error_log( sprintf( 'JPCRM migration error while creating upload box folder %s ', $new_dir_info['path'] ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
		return;
	}
	$file_name            = basename( $file_path );
	$new_file_path        = $new_dir_info['path'] . '/' . $file_name;
	$wp_filesystem_direct = new WP_Filesystem_Direct( false );
	// Moving the file.
	if ( ! $wp_filesystem_direct->move( $file_path, $new_file_path, true ) ) {
		// We shouldn't have any errors here, but if we do we log it and skip this one.
		error_log( sprintf( 'JPCRM migration error while moving upload box %s to %s', $file_path, $new_file_path ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
		return;
	}

	// Updates the database.
	$update_result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$ZBSCRM_t['meta'], // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		array(
			'zbsm_val'         => $new_file_path,
			'zbsm_lastupdated' => time(),
		),
		array( 'ID' => $meta_row->ID ),
		array( // Field data types.
			'%s',
			'%d',
		),
		array( // Where data types.
			'%d',
		)
	);

	if ( $update_result === false ) {
		error_log( sprintf( 'JPCRM migration error while updating upload box meta %s to %s', $meta_row->ID ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
	}
}

/**
 * Migration 5.6.0
 * Moves files from the old file structure (zbscrm-store) to the new
 * one (jpcrm-storage) for rows from the meta table that have the key 'files'.
 *
 * @param object $meta_row Row from the meta table that needs to be updated.
 *
 * @return void
 */
function zeroBSCRM_migration_560_move_file_array( $meta_row ) { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.FunctionNameInvalid
	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	jpcrm_migration_load_wp_filesystem_direct();

	// Before we move the files from the array we must discover its type and
	// update its dir_info information (contains information for several
	// subfolders, we will use the 'files' subfolder).
	$new_dir = false;
	switch ( $meta_row->zbsm_objtype ) {
		case ZBS_TYPE_CONTACT:
			$new_dir = jpcrm_storage_dir_info_for_contact( $meta_row->zbsm_objid );
			break;

		case ZBS_TYPE_COMPANY:
			$new_dir = jpcrm_storage_dir_info_for_company( $meta_row->zbsm_objid );
			break;

		case ZBS_TYPE_QUOTE:
			$new_dir = jpcrm_storage_dir_info_for_quotes( $meta_row->zbsm_objid );
			break;

		case ZBS_TYPE_INVOICE:
			$new_dir = jpcrm_storage_dir_info_for_invoices( $meta_row->zbsm_objid );
			break;
	}

	// Skip if any other type (we are only moving these four types).
	if ( $new_dir === false || ! isset( $new_dir['files'] ) ) {
		return;
	}

	$new_dir_info        = $new_dir['files'];
	$outdated_file_array = json_decode( $meta_row->zbsm_val, true );
	// If we can't decode it neither the CRM can when it shows files, so
	// we can skip it.
	if ( $outdated_file_array === null ) {
		return;
	}

	// This was the hard-coded value in JPCRM < 5.4.x.
	$previous_folder = 'zbscrm-store';
	$new_file_array  = array();
	foreach ( $outdated_file_array as $outdated_file_meta ) {
		// Skip if this has an unknown format.
		// Skip if this isn't an outdate file.
		// Skip if this file doesn`t exist (user may have deleted using the filesystem).
		if (
			! isset( $outdated_file_meta['file'] )
			|| ! str_contains( $outdated_file_meta['file'], "/$previous_folder/" )
			|| ! file_exists( $outdated_file_meta['file'] )
		) {
			$new_file_array[] = $outdated_file_meta;
			continue;
		}

		$upload_folder_exists = jpcrm_create_and_secure_dir_from_external_access( $new_dir_info['path'], false );
		if ( $upload_folder_exists === false ) {
			// We shouldn't have any errors here, but if we do we log it and skip this one.
			error_log( sprintf( 'JPCRM migration error while creating folder %s ', $new_dir_info['path'] ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
			$new_file_array[] = $outdated_file_meta;
			return;
		}

		$file_name            = basename( $outdated_file_meta['file'] );
		$new_file_path        = $new_dir_info['path'] . '/' . $file_name;
		$wp_filesystem_direct = new WP_Filesystem_Direct( false );
		// Moving the file.
		if ( ! $wp_filesystem_direct->move( $outdated_file_meta['file'], $new_file_path, true ) ) {
			// We shouldn't have any errors here, but if we do we log it and skip this one.
			error_log( sprintf( 'JPCRM migration error while moving %s to %s', $outdated_file_meta['file'], $new_file_path ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
			$new_file_array[] = $outdated_file_meta;
			continue;
		}

		// Updating references to save in the database.
		$outdated_file_meta['file'] = $new_file_path;
		$outdated_file_meta['url']  = $new_dir_info['url'] . '/' . $file_name;
		$new_file_array[]           = $outdated_file_meta;
	}
	// Updates the database.
	$update_result = $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$ZBSCRM_t['meta'], // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		array(
			'zbsm_val'         => wp_json_encode( $new_file_array, JSON_UNESCAPED_SLASHES ),
			'zbsm_lastupdated' => time(),
		),
		array( 'ID' => $meta_row->ID ),
		array( // Field data types.
			'%s',
			'%d',
		),
		array( // Where data types.
			'%d',
		)
	);

	if ( $update_result === false ) {
		error_log( sprintf( 'JPCRM migration error while updating file array meta %s to %s', $meta_row->ID ) ); // phpcs:ignore WordPress.PHP.DevelopmentFunctions.error_log_error_log
	}
}

/**
 * Migration 5.6.0
 * Moves the old folder structure (zbscrm-store) to the new one (jpcrm-storage).
 *
 * @return void
 */
function zeroBSCRM_migration_560() { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.FunctionNameInvalid
	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	// This was the hard-coded value in JPCRM < 5.4.x.
	$previous_folder = 'zbscrm-store';
	// We only store files in the meta table.
	$query         = sprintf( "SELECT * FROM `%s` WHERE `zbsm_val` LIKE '%s'", $ZBSCRM_t['meta'], '%%%s%%' ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	$outdated_rows = $wpdb->get_results( $wpdb->prepare( $query, $previous_folder ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching

	if ( is_array( $outdated_rows ) ) {
		foreach ( $outdated_rows as $outdated_row ) {
			// The first type of row we have to migrate has they key 'files' and
			// has an array of files attached to an object of type `zbsm_objtype`.
			if ( $outdated_row->zbsm_key === 'files' ) {
				zeroBSCRM_migration_560_move_file_array( $outdated_row );
			} else {
				zeroBSCRM_migration_560_move_custom_file_upload_box( $outdated_row );
			}
		}
	}

	// Mark as complete.
	zeroBSCRM_migrations_markComplete( '560', array( 'updated' => 1 ) );
}

/**
 * Migration create_workflows_table
 *
 * This migration will:
 * - Make sure all tables are up-to-date. Practically speaking, then we're creating a new "workflows" table.
 *
 * @return void
 */
function zeroBSCRM_migration_create_workflows_table() { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.FunctionNameInvalid
	// Check tables if exist and create if not.
	zeroBSCRM_checkTablesExist();

	// Mark migration as complete.
	zeroBSCRM_migrations_markComplete( 'create_workflows_table', array( 'updated' => 1 ) );
}

/**
 * Removes errant task timezone offsets from database
 */
function zeroBSCRM_migration_task_offset_fix() { // phpcs:ignore WordPress.NamingConventions.ValidFunctionName.FunctionNameInvalid
	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	$timezone_offset_in_secs = jpcrm_get_wp_timezone_offset_in_seconds();

	if ( ! empty( $timezone_offset_in_secs ) ) {
		// remove offset from stored task dates
		$sql = sprintf( 'UPDATE %s SET zbse_start = zbse_start - %d;', $ZBSCRM_t['events'], $timezone_offset_in_secs ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
		$sql = sprintf( 'UPDATE %s SET zbse_end = zbse_end - %d;', $ZBSCRM_t['events'], $timezone_offset_in_secs ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	zeroBSCRM_migrations_markComplete( 'task_offset_fix', array( 'updated' => 1 ) );
}

/**
 * Refresh user roles after tightening restrictions
 */
function zeroBSCRM_migration_refresh_user_roles() {
	// remove roles
	zeroBSCRM_clearUserRoles();

	// add roles anew
	zeroBSCRM_addUserRoles();

	zeroBSCRM_migrations_markComplete( 'refresh_user_roles', array( 'updated' => 1 ) );
}

/**
 * Regenerate tag slugs
 */
function zeroBSCRM_migration_regenerate_tag_slugs() {
	$obj_ids = array( ZBS_TYPE_CONTACT, ZBS_TYPE_COMPANY, ZBS_TYPE_QUOTE, ZBS_TYPE_INVOICE, ZBS_TYPE_TRANSACTION, ZBS_TYPE_TASK, ZBS_TYPE_FORM );
	foreach ( $obj_ids as $obj_id ) {
		jpcrm_migration_regenerate_tag_slugs_for_obj_type( $obj_id );
	}
	zeroBSCRM_migrations_markComplete( 'regenerate_tag_slugs', array( 'updated' => 1 ) );
}

/**
 * Convert invoice statuses and mappings to English
 */
function zeroBSCRM_migration_invoice_language_fixes() {

	// if already English, can't auto-migrate and probably no need
	$cur_locale = get_locale();
	if ( $cur_locale === 'en_US' ) {
		zeroBSCRM_migrations_markComplete( 'invoice_language_fixes', array( 'updated' => 1 ) );
		return;
	}

	global $zbs, $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	$invoice_statuses = array(
		'Draft'   => __( 'Draft', 'zero-bs-crm' ),
		'Unpaid'  => __( 'Unpaid', 'zero-bs-crm' ),
		'Paid'    => __( 'Paid', 'zero-bs-crm' ),
		'Overdue' => __( 'Overdue', 'zero-bs-crm' ),
		'Deleted' => __( 'Deleted', 'zero-bs-crm' ),
	);

	// get WooSync settings
	$woosync_settings = $zbs->settings->get( 'zbscrm_dmz_ext_woosync' );

	$settings_to_update = array();

	foreach ( $invoice_statuses as $invoice_status => $translated_status ) {

		// if the "translation" is the same as English, continue
		if ( $translated_status === $invoice_status ) {
			continue;
		}

		// if there are settings, we may need to update mappings too
		if ( $woosync_settings ) {

			foreach ( $woosync_settings as $setting => $value ) {
				// if not a setting we care about, continue
				if ( ! str_starts_with( $setting, 'order_invoice_map_' ) ) {
					continue;
				}

				// if no translated status matches, continue
				if ( $value !== $translated_status ) {
					continue;
				}

				// flag setting for update
				$settings_to_update[ $setting ] = $invoice_status;
			}
		}

		// see if there are any matches on translated status
		$query = $wpdb->prepare( 'SELECT COUNT(ID) FROM ' . $ZBSCRM_t['invoices'] . ' WHERE zbsi_status=%s', $translated_status ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		$count = $wpdb->get_var( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

		// if no matches, nothing to update
		if ( $count === 0 ) {
			continue;
		}

		// update status to English
		$query = $wpdb->prepare( 'UPDATE ' . $ZBSCRM_t['invoices'] . ' SET zbsi_status=%s WHERE zbsi_status=%s', $invoice_status, $translated_status ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
		$wpdb->query( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared
	}

	// if there are mapping settings to update, do it
	if ( $woosync_settings && ! empty( $settings_to_update ) ) {

		// make a backup of settings
		$zbs->settings->update( 'zbscrm_dmz_ext_woosync.bak', $woosync_settings );

		// update WooSync settings
		$updated_woosync_settings = array_merge( $woosync_settings, $settings_to_update );
		$zbs->settings->update( 'zbscrm_dmz_ext_woosync', $updated_woosync_settings );
	}

	zeroBSCRM_migrations_markComplete( 'invoice_language_fixes', array( 'updated' => 1 ) );
}

/**
 * From Gh Issue 3465, this migration increases the city field size to 200 chars
 */
function zeroBSCRM_migration_gh3465_increase_city_field_size() {

	// SQLite doesn't support column modification.
	$db_engine = jpcrm_database_engine();
	if ( $db_engine === 'sqlite' ) {
		return;
	}

	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	$sql = 'ALTER TABLE ' . $ZBSCRM_t['contacts'] . ' MODIFY COLUMN `zbsc_city` VARCHAR(200);'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

	$sql = 'ALTER TABLE ' . $ZBSCRM_t['contacts'] . ' MODIFY COLUMN `zbsc_seccity` VARCHAR(200);'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

	$sql = 'ALTER TABLE ' . $ZBSCRM_t['companies'] . ' MODIFY COLUMN `zbsco_city` VARCHAR(200);'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

	$sql = 'ALTER TABLE ' . $ZBSCRM_t['companies'] . ' MODIFY COLUMN `zbsco_seccity` VARCHAR(200);'; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	$wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared

	zeroBSCRM_migrations_markComplete( 'gh3465_increase_city_field_size', array( 'updated' => 1 ) );
}

/**
 * Increase tax rate precision from 2 to 10 decimal places for WooCommerce sync compatibility
 */
function zeroBSCRM_migration_tax_rate_precision_fix() {

	global $wpdb, $ZBSCRM_t; // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase

	$db_engine = jpcrm_database_engine();

	if ( $db_engine === 'sqlite' ) {
		// SQLite has dynamic typing - column types are flexible and can store high precision decimals
		// No schema modification needed, but we can mark the migration as complete
		zeroBSCRM_migrations_markComplete(
			'tax_rate_precision_fix',
			array(
				'updated' => 0,
				'note'    => 'SQLite dynamic typing - no schema change needed',
			)
		);
		return;
	}

	// For MySQL/MariaDB - check if the column exists and get its current data type.
	$column_type = zeraBSCRM_migration_get_column_data_type( $ZBSCRM_t['tax'], 'zbsc_rate' ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase
	if ( $column_type && strpos( $column_type, 'decimal(20,10)' ) === false ) {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.NamingConventions.ValidVariableName.VariableNotSnakeCase, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$wpdb->query( 'ALTER TABLE `' . esc_sql( $ZBSCRM_t['tax'] ) . '` MODIFY zbsc_rate DECIMAL(20,10) NOT NULL DEFAULT 0.0000000000' );
	}

	zeroBSCRM_migrations_markComplete( 'tax_rate_precision_fix', array( 'updated' => 1 ) );
}

/**
 * Ensure the notifications table exists
 *
 * This migration ensures the notifications table is created using the dedicated
 * table creation function in ZeroBSCRM.Database.php
 */
function zeroBSCRM_migration_ensure_notifications_table() {
	global $wpdb;

	// Include the Database file if needed
	if ( ! function_exists( 'jpcrm_create_notifications_table' ) ) {
		require_once ZEROBSCRM_INCLUDE_PATH . 'ZeroBSCRM.Database.php';
	}

	// Check if table exists first
	$table_name = $wpdb->prefix . 'zbs_notifications';

	// phpcs:disable
	$table_exists = $wpdb->get_var( "SHOW TABLES LIKE '$table_name'" ) === $table_name;
	// phpcs:enable 

	if ( ! $table_exists ) {
		// Table doesn't exist, create just the notifications table
		$result = jpcrm_create_notifications_table();

		if ( $result ) {
			zeroBSCRM_migrations_markComplete(
				'ensure_notifications_table',
				array(
					'updated' => 1,
					'note'    => 'Successfully created notifications table',
				)
			);
			return true;
		} else {
			// Log error if table creation failed
			zeroBSCRM_migrations_markComplete(
				'ensure_notifications_table',
				array(
					'updated' => 0,
					'note'    => 'Failed to create notifications table',
					'error'   => true,
				)
			);
			return false;
		}
	}

	// Table already exists
	zeroBSCRM_migrations_markComplete(
		'ensure_notifications_table',
		array(
			'updated' => 0,
			'note'    => 'Notifications table already exists',
		)
	);
	return true;
}

/*
======================================================
	/ MIGRATIONS
	====================================================== */

/*
======================================================
	MIGRATION Helpers
	====================================================== */

	// simplistic arr manager
function zeroBSCRM_migration_addErrToStack( $err = array(), $errKey = '' ) {

	if ( $errKey !== '' ) {

			$existing = get_option( $errKey, array() );

			// catch err in err stack.
		if ( ! is_array( $existing ) ) {
			$existing = array();
		}

			// add + update
			$existing[] = $err;
			update_option( $errKey, $existing, false );

			return true;

	}

	return false;
}

	// checks if a column already exists
	// note $tableName is used unchecked
function zeroBSCRM_migration_tableHasColumn( $table_name, $column_name ) {

	global $wpdb;

	if ( ! empty( $table_name ) && ! empty( $column_name ) ) {

			$query = $wpdb->prepare( 'SHOW COLUMNS FROM ' . $table_name . ' LIKE %s', $column_name );

			$row = $wpdb->get_results( $query );

		if ( is_array( $row ) && count( $row ) > 0 ) {

			return true;

		}
	}

	return false;
}

	/*
	* Verifies if a mysql table has an index named X
	*/
function jpcrm_migration_table_has_index( $table_name, $index_name ) {

	global $wpdb;

	$query = $wpdb->prepare( 'SHOW INDEX FROM ' . $table_name . ' WHERE Key_name = %s', $index_name );
	$row   = $wpdb->get_results( $query );

	if ( is_array( $row ) && count( $row ) > 0 ) {

			return true;

	}

	return false;
}

	/**
	 * Retrieves the data typo of the given colemn name in the given table name.
	 * It's worth noting that it will have the size of the field too, so `int(10)`
	 * rather than just `int`.
	 *
	 * @param $table_name string The table name to query.
	 * @param $column_name string The column name to query.
	 *
	 * @return string|false The column type as a string, or `false` on failure.
	 */
function zeraBSCRM_migration_get_column_data_type( $table_name, $column_name ) {
	global $wpdb;

	$column = $wpdb->get_row(
		$wpdb->prepare(
			"SHOW COLUMNS FROM $table_name LIKE %s",
			$column_name
		)
	);
	return empty( $column ) ? false : $column->Type;
}

/**
 * Loads everything needed to use the WP_Filesystem_Direct class.
 *
 * @return void
 */
function jpcrm_migration_load_wp_filesystem_direct() {
	require_once ABSPATH . 'wp-admin/includes/class-wp-filesystem-base.php';
	require_once ABSPATH . 'wp-admin/includes/class-wp-filesystem-direct.php';
}

/**
 * Regenerates tag slugs for a given object type.
 *
 * @param int $obj_type_id Object type ID.
 */
function jpcrm_migration_regenerate_tag_slugs_for_obj_type( int $obj_type_id ) {
	global $zbs;

	// get tags for object type
	$tags = $zbs->DAL->getTagsForObjType( // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
		array(
			'objtypeid'    => $obj_type_id,
			'excludeEmpty' => false,
		)
	);

	// store slugs we've used so we prevent duplicates
	$used_slugs = array();

	foreach ( $tags as $tag ) {

		// generate a potential slug
		$potential_slug = sanitize_key( $tag['name'] );

		// this will be empty if Chinese or Cyrillic or symbols, so use `tag` fallback
		if ( empty( $potential_slug ) ) {
			if ( preg_match( '/^tag-\d+$/', $tag['slug'] ) && ! in_array( $tag['slug'], $used_slugs, true ) ) {
				// if we had a fallback slug before and it hasn't been claimed by another tag, use it
				$potential_slug = $tag['slug'];
			} else {
				// get a new fallback slug
				$potential_slug = $zbs->DAL->get_new_tag_slug( $obj_type_id, 'tag', true ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
			}
		} elseif ( in_array( $potential_slug, $used_slugs, true ) ) {
			// this needs an iteration
			if ( preg_match( '/^' . $potential_slug . '-\d+$/', $tag['slug'] ) && ! in_array( $tag['slug'], $used_slugs, true ) ) {
				// use old slug iteration
				$potential_slug = $tag['slug'];
			} else {
				// generate a new slug iteration
				$potential_slug = $zbs->DAL->get_new_tag_slug( $obj_type_id, $potential_slug ); // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
			}
		}

		// if the new slug is different than the old one, update the database
		if ( $potential_slug !== $tag['slug'] ) {
			$zbs->DAL->addUpdateTag( // phpcs:ignore WordPress.NamingConventions.ValidVariableName.UsedPropertyNotSnakeCase
				array(
					'id'   => $tag['id'],
					'data' => array(
						'objtype' => $obj_type_id,
						'name'    => $tag['name'],
						'slug'    => $potential_slug,
					),
				)
			);
		}

		// store in index of used slugs
		$used_slugs[] = $potential_slug;
	}
}

/*
======================================================
	/ MIGRATION Helpers
	====================================================== */
