includes/class-db.php

<?php
/**
 * Database tables and CRUD.
 *
 * @package WC_Email_Discount_Manager
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Handles custom tables and all CRUD operations.
 */
class WC_EDM_DB {

	/**
	 * Get a fully prefixed table name.
	 *
	 * @param string $name Short table name (groups, group_categories, emails, shipping_rules).
	 * @return string
	 */
	public static function table( $name ) {
		global $wpdb;
		return $wpdb->prefix . 'wc_edm_' . $name;
	}

	/**
	 * Create/upgrade the custom tables via dbDelta(), then add foreign keys.
	 */
	public static function create_tables() {
		global $wpdb;

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset_collate = $wpdb->get_charset_collate();

		$groups           = self::table( 'groups' );
		$group_categories = self::table( 'group_categories' );
		$emails           = self::table( 'emails' );
		$shipping_rules   = self::table( 'shipping_rules' );
		$audit_log        = self::table( 'audit_log' );

		$sql = "CREATE TABLE {$groups} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			name varchar(191) NOT NULL,
			discount_percent decimal(5,2) NOT NULL DEFAULT 0.00,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id)
		) {$charset_collate};
		CREATE TABLE {$group_categories} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			group_id bigint(20) unsigned NOT NULL,
			category_id bigint(20) unsigned NOT NULL,
			sort_order int(11) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			UNIQUE KEY group_category (group_id,category_id),
			KEY group_id (group_id)
		) {$charset_collate};
		CREATE TABLE {$emails} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			email_pattern varchar(191) NOT NULL,
			assignment_type enum('group','coupon','custom') NOT NULL DEFAULT 'group',
			group_id bigint(20) unsigned DEFAULT NULL,
			coupon_code varchar(191) DEFAULT NULL,
			custom_discount decimal(5,2) DEFAULT NULL,
			is_active tinyint(1) NOT NULL DEFAULT 1,
			free_shipping tinyint(1) NOT NULL DEFAULT 0,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			PRIMARY KEY  (id),
			KEY email_pattern (email_pattern),
			KEY group_id (group_id)
		) {$charset_collate};
		CREATE TABLE {$shipping_rules} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			group_id bigint(20) unsigned NOT NULL,
			shipping_method_id varchar(191) NOT NULL,
			action enum('show','hide','only') NOT NULL DEFAULT 'show',
			sort_order int(11) NOT NULL DEFAULT 0,
			PRIMARY KEY  (id),
			KEY group_id (group_id)
		) {$charset_collate};
		CREATE TABLE {$audit_log} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			created_at datetime NOT NULL,
			user_id bigint(20) unsigned NOT NULL DEFAULT 0,
			user_login varchar(191) DEFAULT NULL,
			ip varchar(45) DEFAULT NULL,
			method varchar(10) NOT NULL DEFAULT '',
			route varchar(191) NOT NULL DEFAULT '',
			status smallint(5) unsigned NOT NULL DEFAULT 0,
			outcome enum('allowed','denied','rate_limited') NOT NULL DEFAULT 'allowed',
			details text DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY created_at (created_at),
			KEY user_id (user_id),
			KEY outcome (outcome)
		) {$charset_collate};";

		dbDelta( $sql );

		self::add_foreign_keys();
		self::migrate_show_to_only();

		update_option( 'wc_edm_db_version', WC_EDM_VERSION );
	}

	/**
	 * Once, when "only" was introduced: every existing "show" rule was reserved
	 * for its group, so it becomes "only" and the shop's checkout looks exactly
	 * as it did. Without this the update would silently put reserved free
	 * delivery back in front of every stranger.
	 */
	private static function migrate_show_to_only() {
		global $wpdb;

		if ( get_option( 'wc_edm_show_to_only_done' ) ) {
			return;
		}

		$table  = self::table( 'shipping_rules' );
		$column = $wpdb->get_row( "SHOW COLUMNS FROM {$table} LIKE 'action'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		// dbDelta did not widen the enum: converting now would store '' rather
		// than 'only'. Leave the flag unset so the next load tries again.
		if ( ! $column || false === strpos( (string) $column->Type, "'only'" ) ) {
			return;
		}

		$wpdb->query( "UPDATE {$table} SET action = 'only' WHERE action = 'show'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		update_option( 'wc_edm_show_to_only_done', 1 );
	}

	/**
	 * Run create_tables() again when the plugin was updated without
	 * re-activation (dbDelta adds any missing columns).
	 */
	public static function maybe_upgrade() {
		if ( get_option( 'wc_edm_db_version' ) !== WC_EDM_VERSION ) {
			self::create_tables();
		}
	}

	/**
	 * Add foreign key constraints (dbDelta cannot handle these).
	 */
	private static function add_foreign_keys() {
		$groups = self::table( 'groups' );

		self::maybe_add_foreign_key(
			self::table( 'group_categories' ),
			'fk_wc_edm_group_categories_group',
			"FOREIGN KEY (group_id) REFERENCES {$groups} (id) ON DELETE CASCADE"
		);

		self::maybe_add_foreign_key(
			self::table( 'emails' ),
			'fk_wc_edm_emails_group',
			"FOREIGN KEY (group_id) REFERENCES {$groups} (id) ON DELETE SET NULL"
		);

		self::maybe_add_foreign_key(
			self::table( 'shipping_rules' ),
			'fk_wc_edm_shipping_rules_group',
			"FOREIGN KEY (group_id) REFERENCES {$groups} (id) ON DELETE CASCADE"
		);
	}

	/**
	 * Add a foreign key if it does not already exist.
	 *
	 * @param string $table      Table name.
	 * @param string $constraint Constraint name.
	 * @param string $definition FOREIGN KEY definition SQL.
	 */
	private static function maybe_add_foreign_key( $table, $constraint, $definition ) {
		global $wpdb;

		$exists = $wpdb->get_var(
			$wpdb->prepare(
				'SELECT CONSTRAINT_NAME FROM information_schema.TABLE_CONSTRAINTS
				 WHERE CONSTRAINT_SCHEMA = %s AND TABLE_NAME = %s AND CONSTRAINT_NAME = %s AND CONSTRAINT_TYPE = %s',
				DB_NAME,
				$table,
				$constraint,
				'FOREIGN KEY'
			)
		);

		if ( ! $exists ) {
			// Table/constraint names are internal, not user input.
			$wpdb->query( "ALTER TABLE {$table} ADD CONSTRAINT {$constraint} {$definition}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}
	}

	/*
	|--------------------------------------------------------------------------
	| Discount groups
	|--------------------------------------------------------------------------
	*/

	/**
	 * Get all discount groups.
	 *
	 * @return array Array of row objects.
	 */
	public static function get_groups() {
		global $wpdb;
		$table = self::table( 'groups' );
		return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY name ASC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Count discount groups.
	 *
	 * @return int
	 */
	public static function count_groups() {
		global $wpdb;
		$table = self::table( 'groups' );
		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get the most recently created groups.
	 *
	 * @param int $limit Number of groups.
	 * @return array
	 */
	public static function get_recent_groups( $limit = 5 ) {
		global $wpdb;
		$table = self::table( 'groups' );
		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} ORDER BY created_at DESC LIMIT %d", $limit ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Get a single group.
	 *
	 * @param int $id Group ID.
	 * @return object|null
	 */
	public static function get_group( $id ) {
		global $wpdb;
		$table = self::table( 'groups' );
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Insert a group.
	 *
	 * @param array $data { name, discount_percent }.
	 * @return int New group ID (0 on failure).
	 */
	public static function insert_group( $data ) {
		global $wpdb;
		$now = current_time( 'mysql' );

		$wpdb->insert(
			self::table( 'groups' ),
			array(
				'name'             => $data['name'],
				'discount_percent' => $data['discount_percent'],
				'created_at'       => $now,
				'updated_at'       => $now,
			),
			array( '%s', '%f', '%s', '%s' )
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Update a group.
	 *
	 * @param int   $id   Group ID.
	 * @param array $data { name, discount_percent }.
	 * @return bool
	 */
	public static function update_group( $id, $data ) {
		global $wpdb;

		return false !== $wpdb->update(
			self::table( 'groups' ),
			array(
				'name'             => $data['name'],
				'discount_percent' => $data['discount_percent'],
				'updated_at'       => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%s', '%f', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Delete a group and its related rows.
	 *
	 * @param int $id Group ID.
	 * @return bool
	 */
	public static function delete_group( $id ) {
		global $wpdb;

		// Manual cleanup in case foreign keys are unsupported (e.g. MyISAM).
		$wpdb->delete( self::table( 'group_categories' ), array( 'group_id' => $id ), array( '%d' ) );
		$wpdb->delete( self::table( 'shipping_rules' ), array( 'group_id' => $id ), array( '%d' ) );
		$wpdb->update( self::table( 'emails' ), array( 'group_id' => null ), array( 'group_id' => $id ), array( '%d' ), array( '%d' ) );

		return false !== $wpdb->delete( self::table( 'groups' ), array( 'id' => $id ), array( '%d' ) );
	}

	/*
	|--------------------------------------------------------------------------
	| Group categories (M2M)
	|--------------------------------------------------------------------------
	*/

	/**
	 * Get category IDs for a group, ordered by sort_order.
	 *
	 * @param int $group_id Group ID.
	 * @return int[]
	 */
	public static function get_group_category_ids( $group_id ) {
		global $wpdb;
		$table = self::table( 'group_categories' );

		return array_map(
			'intval',
			$wpdb->get_col(
				$wpdb->prepare( "SELECT category_id FROM {$table} WHERE group_id = %d ORDER BY sort_order ASC, id ASC", $group_id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			)
		);
	}

	/**
	 * Replace the categories for a group. Sort order follows array order.
	 *
	 * @param int   $group_id     Group ID.
	 * @param int[] $category_ids Ordered category IDs.
	 */
	public static function set_group_categories( $group_id, $category_ids ) {
		global $wpdb;

		$wpdb->delete( self::table( 'group_categories' ), array( 'group_id' => $group_id ), array( '%d' ) );

		$category_ids = array_values( array_unique( array_map( 'intval', $category_ids ) ) );

		foreach ( $category_ids as $index => $category_id ) {
			if ( $category_id <= 0 ) {
				continue;
			}
			$wpdb->insert(
				self::table( 'group_categories' ),
				array(
					'group_id'    => $group_id,
					'category_id' => $category_id,
					'sort_order'  => $index,
				),
				array( '%d', '%d', '%d' )
			);
		}
	}

	/*
	|--------------------------------------------------------------------------
	| Email assignments
	|--------------------------------------------------------------------------
	*/

	/**
	 * Get all email assignments.
	 *
	 * @return array
	 */
	public static function get_emails() {
		global $wpdb;
		$table = self::table( 'emails' );
		return $wpdb->get_results( "SELECT * FROM {$table} ORDER BY email_pattern ASC" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get all active email assignments.
	 *
	 * @return array
	 */
	public static function get_active_emails() {
		global $wpdb;
		$table = self::table( 'emails' );
		return $wpdb->get_results( "SELECT * FROM {$table} WHERE is_active = 1" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Count email assignments.
	 *
	 * @param bool $active_only Count only active assignments.
	 * @return int
	 */
	public static function count_emails( $active_only = false ) {
		global $wpdb;
		$table = self::table( 'emails' );
		$sql   = "SELECT COUNT(*) FROM {$table}";
		if ( $active_only ) {
			$sql .= ' WHERE is_active = 1';
		}
		return (int) $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Get a single email assignment.
	 *
	 * @param int $id Assignment ID.
	 * @return object|null
	 */
	public static function get_email( $id ) {
		global $wpdb;
		$table = self::table( 'emails' );
		return $wpdb->get_row(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Insert an email assignment.
	 *
	 * @param array $data Assignment data.
	 * @return int New ID (0 on failure).
	 */
	public static function insert_email( $data ) {
		global $wpdb;
		$now = current_time( 'mysql' );

		$wpdb->insert(
			self::table( 'emails' ),
			array(
				'email_pattern'   => $data['email_pattern'],
				'assignment_type' => $data['assignment_type'],
				'group_id'        => $data['group_id'],
				'coupon_code'     => $data['coupon_code'],
				'custom_discount' => $data['custom_discount'],
				'is_active'       => $data['is_active'],
				'free_shipping'   => $data['free_shipping'],
				'created_at'      => $now,
				'updated_at'      => $now,
			)
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Update an email assignment.
	 *
	 * @param int   $id   Assignment ID.
	 * @param array $data Assignment data.
	 * @return bool
	 */
	public static function update_email( $id, $data ) {
		global $wpdb;

		return false !== $wpdb->update(
			self::table( 'emails' ),
			array(
				'email_pattern'   => $data['email_pattern'],
				'assignment_type' => $data['assignment_type'],
				'group_id'        => $data['group_id'],
				'coupon_code'     => $data['coupon_code'],
				'custom_discount' => $data['custom_discount'],
				'is_active'       => $data['is_active'],
				'free_shipping'   => $data['free_shipping'],
				'updated_at'      => current_time( 'mysql' ),
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Toggle an email assignment's active flag.
	 *
	 * @param int $id        Assignment ID.
	 * @param int $is_active 1 or 0.
	 * @return bool
	 */
	public static function set_email_active( $id, $is_active ) {
		global $wpdb;

		return false !== $wpdb->update(
			self::table( 'emails' ),
			array(
				'is_active'  => $is_active ? 1 : 0,
				'updated_at' => current_time( 'mysql' ),
			),
			array( 'id' => $id ),
			array( '%d', '%s' ),
			array( '%d' )
		);
	}

	/**
	 * Delete an email assignment.
	 *
	 * @param int $id Assignment ID.
	 * @return bool
	 */
	public static function delete_email( $id ) {
		global $wpdb;
		return false !== $wpdb->delete( self::table( 'emails' ), array( 'id' => $id ), array( '%d' ) );
	}

	/*
	|--------------------------------------------------------------------------
	| Shipping rules
	|--------------------------------------------------------------------------
	*/

	/**
	 * Get shipping rules for a group, ordered by sort_order.
	 *
	 * @param int $group_id Group ID.
	 * @return array
	 */
	public static function get_shipping_rules( $group_id ) {
		global $wpdb;
		$table = self::table( 'shipping_rules' );

		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} WHERE group_id = %d ORDER BY sort_order ASC, id ASC", $group_id ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Every shipping method any group reserves for itself.
	 *
	 * An "only" rule means the group sees that method AND nobody else does —
	 * otherwise reserving a free-delivery method for an approved group leaves
	 * it on the checkout for every other customer, which is what a shop
	 * notices as lost shipping revenue rather than as a bug. A plain "show"
	 * rule is a whitelist for its own group and reserves nothing: 1.6.0 made
	 * "show" do both, and a group that needed to show ordinary methods next to
	 * a reserved one took those ordinary methods away from everybody else.
	 *
	 * @return string[] Rate ids, e.g. "free_shipping:15".
	 */
	public static function restricted_shipping_methods() {
		global $wpdb;
		$table = self::table( 'shipping_rules' );

		$ids = $wpdb->get_col( "SELECT DISTINCT shipping_method_id FROM {$table} WHERE action = 'only'" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		return is_array( $ids ) ? $ids : array();
	}

	/**
	 * Replace all shipping rules for a group.
	 *
	 * @param int   $group_id Group ID.
	 * @param array $rules    Array of { shipping_method_id, action, sort_order }.
	 */
	public static function replace_shipping_rules( $group_id, $rules ) {
		global $wpdb;

		$wpdb->delete( self::table( 'shipping_rules' ), array( 'group_id' => $group_id ), array( '%d' ) );

		foreach ( $rules as $rule ) {
			if ( empty( $rule['shipping_method_id'] ) || ! in_array( $rule['action'], array( 'show', 'hide', 'only' ), true ) ) {
				continue;
			}
			$wpdb->insert(
				self::table( 'shipping_rules' ),
				array(
					'group_id'           => $group_id,
					'shipping_method_id' => $rule['shipping_method_id'],
					'action'             => $rule['action'],
					'sort_order'         => (int) $rule['sort_order'],
				),
				array( '%d', '%s', '%s', '%d' )
			);
		}
	}

	/*
	|--------------------------------------------------------------------------
	| Audit log
	|--------------------------------------------------------------------------
	*/

	/**
	 * Insert an audit log entry.
	 *
	 * @param array $data Entry data.
	 * @return int New entry ID (0 on failure).
	 */
	public static function insert_audit_entry( $data ) {
		global $wpdb;

		$wpdb->insert(
			self::table( 'audit_log' ),
			array(
				'created_at' => current_time( 'mysql' ),
				'user_id'    => (int) $data['user_id'],
				'user_login' => $data['user_login'],
				'ip'         => $data['ip'],
				'method'     => $data['method'],
				'route'      => $data['route'],
				'status'     => (int) $data['status'],
				'outcome'    => $data['outcome'],
				'details'    => $data['details'],
			),
			array( '%s', '%d', '%s', '%s', '%s', '%s', '%d', '%s', '%s' )
		);

		return (int) $wpdb->insert_id;
	}

	/**
	 * Get audit log entries, newest first.
	 *
	 * @param int    $limit   Max entries.
	 * @param int    $offset  Offset.
	 * @param string $outcome Optional outcome filter (allowed/denied/rate_limited).
	 * @return array
	 */
	public static function get_audit_entries( $limit = 50, $offset = 0, $outcome = '' ) {
		global $wpdb;
		$table = self::table( 'audit_log' );

		if ( in_array( $outcome, array( 'allowed', 'denied', 'rate_limited' ), true ) ) {
			return $wpdb->get_results(
				$wpdb->prepare(
					"SELECT * FROM {$table} WHERE outcome = %s ORDER BY id DESC LIMIT %d OFFSET %d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
					$outcome,
					$limit,
					$offset
				)
			);
		}

		return $wpdb->get_results(
			$wpdb->prepare( "SELECT * FROM {$table} ORDER BY id DESC LIMIT %d OFFSET %d", $limit, $offset ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Count audit log entries.
	 *
	 * @param string $outcome Optional outcome filter.
	 * @return int
	 */
	public static function count_audit_entries( $outcome = '' ) {
		global $wpdb;
		$table = self::table( 'audit_log' );

		if ( in_array( $outcome, array( 'allowed', 'denied', 'rate_limited' ), true ) ) {
			return (int) $wpdb->get_var(
				$wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE outcome = %s", $outcome ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			);
		}

		return (int) $wpdb->get_var( "SELECT COUNT(*) FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}

	/**
	 * Delete audit entries older than the retention period.
	 *
	 * @param int $days Retention in days.
	 * @return int Rows deleted.
	 */
	public static function prune_audit_log( $days ) {
		global $wpdb;
		$table = self::table( 'audit_log' );

		$cutoff = gmdate( 'Y-m-d H:i:s', strtotime( current_time( 'mysql' ) . ' -' . absint( $days ) . ' days' ) );

		return (int) $wpdb->query(
			$wpdb->prepare( "DELETE FROM {$table} WHERE created_at < %s", $cutoff ) // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		);
	}

	/**
	 * Delete every audit log entry.
	 *
	 * @return int Rows deleted.
	 */
	public static function clear_audit_log() {
		global $wpdb;
		$table = self::table( 'audit_log' );

		return (int) $wpdb->query( "DELETE FROM {$table}" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	}
}