<?php
/**
 * @license GPL-2.0-or-later
 * @link    https://wordpress.org/plugins/pressio-crm
 */
namespace PressioCRM\Database\Migrations;

defined( 'ABSPATH' ) || exit;

use PressioCRM\Database\Migration;

class Migration003 extends Migration {

	public function run(): void {
		global $wpdb;

		$p = $wpdb->prefix;

		// Add (contact_id, type, created_at) on activities.
		// Speeds up the batch last_contacted query which filters by type after contact_id.
		// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema migrations on plugin-owned tables; table names are built from $wpdb->prefix only.
		$has = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(1) FROM information_schema.STATISTICS WHERE table_schema = DATABASE() AND table_name = %s AND index_name = %s',
				"{$p}pcrm_activities",
				'idx_contact_type_date'
			)
		);
		if ( ! $has ) {
			$wpdb->query( "ALTER TABLE `{$p}pcrm_activities` ADD INDEX idx_contact_type_date (contact_id, type(50), created_at)" );
		}

		// Add (deleted_at, created_at) on contacts.
		// Speeds up the default list query: WHERE deleted_at IS NULL ORDER BY created_at DESC.
		$has = (int) $wpdb->get_var(
			$wpdb->prepare(
				'SELECT COUNT(1) FROM information_schema.STATISTICS WHERE table_schema = DATABASE() AND table_name = %s AND index_name = %s',
				"{$p}pcrm_contacts",
				'idx_deleted_created'
			)
		);
		if ( ! $has ) {
			$wpdb->query( "ALTER TABLE `{$p}pcrm_contacts` ADD INDEX idx_deleted_created (deleted_at, created_at)" );
		}
		// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange, PluginCheck.Security.DirectDB.UnescapedDBParameter
	}
}
