<?php
/** Personal data lookup and checked erasure across related plugin records. */
namespace Abientot\Repositories;

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

final class PrivacyRepository {
	/** Only current canonical email grants customer-profile authority. */
	public function customer_ids( string $email, int $limit = 0 ): array {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( ! is_email( $email ) ) {
			return array();
		}
		if ( $limit > 0 ) {
			$ids = array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}abientot_customers WHERE LOWER(email) = %s ORDER BY id LIMIT %d", $email, $limit ) ) );
		} else {
			$ids = array_map( 'intval', $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}abientot_customers WHERE LOWER(email) = %s ORDER BY id", $email ) ) );
		}
		$this->check_read();
		return $ids;
	}

	/** Snapshot-only matches require independently verified manual review. */
	public function historical_review_needed( string $email ): bool {
		global $wpdb;
		$email = strtolower( trim( $email ) );
		if ( ! is_email( $email ) ) {
			return false;
		}
		$after = 0;
		$like  = '%' . $wpdb->esc_like( trim( wp_json_encode( $email ), '"' ) ) . '%';
		do {
			$rows = $wpdb->get_results( $wpdb->prepare( "SELECT a.id,a.customer_fields_json FROM {$wpdb->prefix}abientot_appointments a INNER JOIN {$wpdb->prefix}abientot_customers c ON c.id=a.customer_id WHERE a.id > %d AND LOWER(COALESCE(c.email,'')) <> %s AND LOWER(a.customer_fields_json) LIKE %s ORDER BY a.id LIMIT 250", $after, $email, $like ), ARRAY_A );
			$this->check_read();
			foreach ( $rows as $row ) {
				$after  = (int) $row['id'];
				$fields = json_decode( (string) $row['customer_fields_json'], true );
				foreach ( is_array( $fields ) ? $fields : array() as $field ) {
					if ( is_array( $field ) && 'email' === ( $field['key'] ?? '' ) && is_string( $field['value'] ?? null ) && strtolower( trim( $field['value'] ) ) === $email ) {
						return true;
					}
				}
			}
		} while ( count( $rows ) === 250 );
		return false;
	}

	/** Stable, bounded pages; exports never include tokens or other users' actor IDs. */
	public function export_rows( string $kind, array $ids, int $page ): array {
		global $wpdb;
		if ( ! $ids ) {
			return array();
		}
		$prefix = $wpdb->prefix . 'abientot_';
		$offset = max( 0, $page - 1 ) * 100;
		switch ( $kind ) {
			case 'selection_answers':
				if ( ! ( new BookingSelectionsRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.appointment_id AS id,r.snapshot_json FROM {$wpdb->prefix}abientot_booking_selections r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'dependants':
				if ( ! ( new PartyAttendeesRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.display_name,r.notes,r.relation_confirmed,r.state,r.created_at FROM {$wpdb->prefix}abientot_dependants r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'attendees':
				if ( ! ( new PartyAttendeesRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.appointment_id,r.ordinal,r.display_name,r.notes,r.checked_in_at FROM {$wpdb->prefix}abientot_booking_attendees r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'credit_grants':
				if ( ! ( new CreditRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.package_id,r.units,r.eligibility_json,r.expires_at,r.policy_json,r.acquisition_cents,r.currency,r.state,r.created_at FROM {$wpdb->prefix}abientot_credit_grants r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'credit_applications':
				if ( ! ( new CreditRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.appointment_id AS id,r.grant_id,r.units,r.covered_cents,r.currency,r.state,r.policy_json,r.created_at FROM {$wpdb->prefix}abientot_credit_applications r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'booking_instructions':
				if ( ! ( new BookingInstructionsRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.appointment_id AS id,r.revision,r.snapshot_json FROM {$wpdb->prefix}abientot_booking_instructions r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'customers':
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.* FROM {$wpdb->prefix}abientot_customers r WHERE r.id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'appointments':
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.* FROM {$wpdb->prefix}abientot_appointments r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'payment_records':
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.* FROM {$wpdb->prefix}abientot_payment_records r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id = r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'groups':
				if ( ! ( new GroupBookingRepository() )->present() ) {
					return array();
				}
				if ( get_option( 'abientot_groups_schema_version' ) === '2' ) {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT a.id, m.seat_count,m.unit_price_cents,m.session_id, g.service_id,g.staff_id,g.location_id,g.start_utc,g.end_utc,g.timezone,g.capacity,g.price_cents,g.currency,g.status AS session_status FROM {$wpdb->prefix}abientot_group_memberships m INNER JOIN {$wpdb->prefix}abientot_group_sessions g ON g.id=m.session_id INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=m.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY a.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				} else {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT a.id, m.session_id, g.service_id,g.staff_id,g.location_id,g.start_utc,g.end_utc,g.timezone,g.capacity,g.price_cents,g.currency,g.status AS session_status FROM {$wpdb->prefix}abientot_group_memberships m INNER JOIN {$wpdb->prefix}abientot_group_sessions g ON g.id=m.session_id INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=m.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY a.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				}
				break;
			case 'attendance':
				if ( ! ( new AttendanceRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.appointment_id AS id,r.state,r.arrived_at,r.updated_at FROM {$wpdb->prefix}abientot_attendance r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'notification_attempts':
				if ( ! ( new NotificationAttemptsRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.attempt_uuid,r.template_key,r.recipient_role,r.content_revision,r.result,r.occurred_at FROM {$wpdb->prefix}abientot_notification_attempts r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'public_waitlist':
				if ( ! ( new PublicWaitlistRepository() )->present() ) {
					return array();
				}
				if ( get_option( 'abientot_public_waitlist_schema_version' ) === '2' ) {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.service_id,r.location_id,r.session_id,r.staff_id,r.date_from,r.date_to,r.time_from,r.time_to,r.timezone,r.requested_seats,r.consent_version,r.consented_at,r.state,r.created_at,r.intent_json FROM {$wpdb->prefix}abientot_waitlist_preferences r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				} else {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.service_id,r.location_id,r.session_id,r.staff_id,r.date_from,r.date_to,r.time_from,r.time_to,r.timezone,r.requested_seats,r.consent_version,r.consented_at,r.state,r.created_at FROM {$wpdb->prefix}abientot_waitlist_preferences r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				}
				break;
			case 'payment_plans':
				if ( ! ( new PaymentPlanRepository() )->present() ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.appointment_id AS id,r.gross_cents,r.currency,r.deposit_percent,r.deposit_cents,r.collection_frozen,r.created_at,r.updated_at FROM {$wpdb->prefix}abientot_payment_plans r INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=r.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'payment_refunds':
				if ( ! ( new ResourceCapacityRepository() )->exists( 'payment_refunds' ) ) {
					return array();
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.payment_record_id,r.amount_cents,r.currency,r.state,r.created_at,r.updated_at FROM {$wpdb->prefix}abientot_payment_refunds r INNER JOIN {$wpdb->prefix}abientot_payment_records p ON p.id=r.payment_record_id INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=p.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'waitlist':
				if ( ! ( new ResourceCapacityRepository() )->exists( 'waitlist_entries' ) ) {
					return array();
				}
				$entry_columns = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $prefix . 'waitlist_entries' ), 0 );
				$this->check_read();
				if ( in_array( 'intent_json', $entry_columns, true ) ) {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.service_id,r.location_id,r.session_id,r.date_from,r.date_to,r.state,r.created_at,r.updated_at,r.intent_json FROM {$wpdb->prefix}abientot_waitlist_entries r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				} else {
					$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.id,r.service_id,r.location_id,r.session_id,r.date_from,r.date_to,r.state,r.created_at,r.updated_at FROM {$wpdb->prefix}abientot_waitlist_entries r WHERE r.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				}
				break;
			case 'recurrence':
				foreach ( array( 'recurrence_series', 'recurrence_occurrences' ) as $suffix ) {
					$table  = $prefix . $suffix;
					$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
					$this->check_read();
					if ( $exists !== $table ) {
						return array();
					}
				}
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT m.appointment_id AS id, r.id AS series_id, m.ordinal, r.payload_version, r.timezone, r.first_local, r.interval_weeks, r.occurrence_count, r.state, r.created_at FROM {$wpdb->prefix}abientot_recurrence_series r INNER JOIN {$wpdb->prefix}abientot_recurrence_occurrences m ON m.series_id=r.id INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=m.appointment_id WHERE a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY m.appointment_id ASC LIMIT 100 OFFSET %d", array_merge( $ids, array( $offset ) ) ), ARRAY_A );
				break;
			case 'activity_log':
				$rows = $wpdb->get_results( $wpdb->prepare( "SELECT r.* FROM {$wpdb->prefix}abientot_activity_log r LEFT JOIN {$wpdb->prefix}abientot_appointments a ON r.object_type = 'appointment' AND a.id = r.object_id WHERE (r.object_type = 'customer' AND r.object_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ")) OR a.customer_id IN (" . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ") ORDER BY r.id ASC LIMIT 100 OFFSET %d", array_merge( $ids, $ids, array( $offset ) ) ), ARRAY_A );
				break;
			default:
				return array();
		}
		$this->check_read();
		foreach ( $rows as &$row ) {
			unset( $row['actor_id'], $row['provider_object_id'], $row['provider_intent_id'], $row['provider_config_digest'], $row['provider_object_digest'], $row['provider_intent_digest'], $row['provider_charge_digest'], $row['operation_id'] );
		}
		unset( $row );
		if ( $kind === 'appointments' ) {
			$retained = new ResourceBookingRepository();
			foreach ( $rows as &$row ) {
				$snapshot = $retained->snapshot( (int) $row['id'] );
				if ( $snapshot ) {
					$row['retained_booking'] = $snapshot;
				}
			}
			unset( $row );
		}
		return $rows;
	}

	/** Erase unverified subject requests even when no customer profile exists. */
	public function erase_pending_waitlist( string $email ): array {
		$owned = ! BookingLock::is_held();
		if ( ! BookingLock::acquire() ) {
			throw new \RuntimeException( 'Privacy lock unavailable.' );
		}
		global $wpdb;
		$started = false;
		try {
			\Abientot\Support\TransactionGuard::prepare_write();
			$this->write( $wpdb->query( 'START TRANSACTION' ) );
			$started = true;
			$repo    = new PublicWaitlistRepository();
			$rows    = $repo->pending( $email, 21 );
			$repo->erase_ids( array_column( array_slice( $rows, 0, 20 ), 'id' ) );
			( new ImportHistoryRepository() )->revoke_previews();
			$this->write( $wpdb->query( 'COMMIT' ) );
			$started = false;
			return array(
				'removed' => min( 20, count( $rows ) ),
				'done'    => count( $rows ) <= 20
			);
		} catch ( \Throwable $error ) {
			if ( $started ) {
				$wpdb->query( 'ROLLBACK' );
			}
			throw $error;
		} finally {
			ImportHistoryRepository::clear_epoch_cache();
			if ( $owned ) {
				BookingLock::release();
			}
		}
	}

	/** Atomic customer-level erasure; preserve appointment IDs and financial totals. */
	public function erase_customer( int $id ): bool {
		global $wpdb;
		$owns_lock = ! BookingLock::is_held();
		if ( $id <= 0 || ! BookingLock::acquire() ) {
			return false;
		}
		$prefix  = $wpdb->prefix . 'abientot_';
		$started = false;
		try {
			\Abientot\Support\TransactionGuard::prepare_write();
			$this->write( $wpdb->query( 'START TRANSACTION' ) );
			$started  = true;
			$customer = ( new CustomersRepository() )->find( $id );
			$this->check_read();
			if ( ! $customer ) {
				throw new \RuntimeException( 'Customer not found.' );
			}
			$now = gmdate( 'Y-m-d H:i:s' );
			( new RetainedSmsPrivacyRepository() )->erase_customer( $id );
			( new PublicWaitlistRepository() )->erase_customer( $id );
			( new CustomerSeriesRepository() )->erase_customer( $id );
			( new BookingSelectionsRepository() )->erase_customer( $id );
			( new CreditRepository() )->erase_customer( $id );
			( new BookingInstructionsRepository() )->erase_customer( $id );
			( new PartyAttendeesRepository() )->erase_customer(
				$id,
				static function () use ( &$started ): bool {
					return $started && BookingLock::is_held();
				}
			);
			( new ImportHistoryRepository() )->erase_customer( $id );
			$this->write(
				$wpdb->update(
					$prefix . 'customers',
					array(
						'first_name'       => '',
						'last_name'        => '',
						'display_name'     => 'Customer #' . $id,
						'email'            => '',
						'phone'            => '',
						'notes'            => '',
						'marketing_opt_in' => 0,
						'updated_at'       => $now,
					),
					array( 'id' => $id )
				)
			);
			$this->write(
				$wpdb->update(
					$prefix . 'appointments',
					array(
						'customer_fields_json' => null,
						'customer_note'        => '',
						'internal_note'        => '',
						'updated_at'           => $now,
					),
					array( 'customer_id' => $id )
				)
			);
			$payment_columns = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $prefix . 'payment_records' ), 0 );
			$this->check_read();
			$this->write(
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id
				SET p.transaction_reference = NULL, p.updated_at = %s WHERE a.customer_id = %d",
						$prefix . 'payment_records',
						$prefix . 'appointments',
						$now,
						$id
					)
				)
			);
			if ( in_array( 'provider_object_id', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.provider_object_id = NULL, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'provider_intent_id', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.provider_intent_id = NULL, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'collection_frozen', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.collection_frozen = 1, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'privacy_erased', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.privacy_erased = 1, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'reconciliation_state', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.reconciliation_state = 'stopped', p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'next_attempt_at', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.next_attempt_at = NULL, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			if ( in_array( 'last_error_code', $payment_columns, true ) ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id = p.appointment_id SET p.last_error_code = NULL, p.updated_at = %s WHERE a.customer_id = %d", $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
			}
			$this->write(
				$wpdb->query(
					$wpdb->prepare(
						"UPDATE %i l LEFT JOIN %i a ON l.object_type = 'appointment' AND a.id = l.object_id
				SET l.message = NULL, l.context_json = NULL, l.actor_id = CASE WHEN l.actor_type = 'customer' THEN NULL ELSE l.actor_id END
				WHERE (l.object_type = 'customer' AND l.object_id = %d) OR a.customer_id = %d",
						$prefix . 'activity_log',
						$prefix . 'appointments',
						$id,
						$id
					)
				)
			);
			$legacy = $prefix . 'public_tokens';
			$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $legacy ) ) );
			$this->check_read();
			if ( $legacy === $exists ) {
				$this->write(
					$wpdb->query(
						$wpdb->prepare(
							"DELETE t FROM %i t INNER JOIN %i a ON a.id = t.appointment_id WHERE a.customer_id = %d",
							$legacy,
							$prefix . 'appointments',
							$id
						)
					)
				);
			}
			if ( ( new NotificationAttemptsRepository() )->present() ) {
				$this->write( $wpdb->query( $wpdb->prepare( "DELETE r FROM %i r INNER JOIN %i a ON a.id=r.appointment_id WHERE a.customer_id=%d", $prefix . 'notification_attempts', $prefix . 'appointments', $id ) ) );
			}
			if ( ( new AttendanceRepository() )->present() ) {
				$this->write( $wpdb->query( $wpdb->prepare( "DELETE r FROM %i r INNER JOIN %i a ON a.id=r.appointment_id WHERE a.customer_id=%d", $prefix . 'attendance', $prefix . 'appointments', $id ) ) );
			}
			$reminders = $prefix . 'reminder_queue';
			$exists    = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $reminders ) ) );
			$this->check_read();
			if ( $reminders === $exists ) {
				$this->write( $wpdb->query( $wpdb->prepare( "DELETE r FROM %i r INNER JOIN %i a ON a.id = r.appointment_id WHERE a.customer_id = %d", $reminders, $prefix . 'appointments', $id ) ) );
			}
			foreach ( array( 'payment_plans', 'payment_refunds' ) as $suffix ) {
				$table  = $prefix . $suffix;
				$exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) );
				$this->check_read();
				if ( $table !== $exists ) {
					continue;
				}
				if ( $suffix === 'payment_plans' ) {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i p INNER JOIN %i a ON a.id=p.appointment_id SET p.privacy_erased=1, p.collection_frozen=1, p.updated_at=%s WHERE a.customer_id=%d", $table, $prefix . 'appointments', $now, $id ) ) );
				} else {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i r INNER JOIN %i p ON p.id=r.payment_record_id INNER JOIN %i a ON a.id=p.appointment_id SET r.privacy_erased=1,r.provider_refund_id=NULL,r.next_attempt_at=NULL,r.last_error_code=NULL,r.updated_at=%s WHERE a.customer_id=%d", $table, $prefix . 'payment_records', $prefix . 'appointments', $now, $id ) ) );
				}
			}
			$entries     = $prefix . 'waitlist_entries';
			$offers      = $prefix . 'waitlist_offers';
			$has_entries = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $entries ) ) );
			$this->check_read();
			$has_offers = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $offers ) ) );
			$this->check_read();
			if ( $has_entries === $entries ) {
				if ( $has_offers === $offers ) {
					$offer_columns = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $offers ), 0 );
					$this->check_read();
					if ( in_array( 'resource_json', $offer_columns, true ) ) {
						$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i o LEFT JOIN %i e ON e.id=o.entry_id LEFT JOIN %i a ON a.id=o.appointment_id SET o.state='erased',o.bearer_hash=NULL,o.entry_id=NULL,o.appointment_id=NULL,o.service_id=NULL,o.staff_id=NULL,o.location_id=NULL,o.session_id=NULL,o.start_utc=NULL,o.end_utc=NULL,o.timezone=NULL,o.duration_minutes=NULL,o.price_cents=NULL,o.currency=NULL,o.resource_json=NULL,o.updated_at=%s WHERE e.customer_id=%d OR a.customer_id=%d", $offers, $entries, $prefix . 'appointments', $now, $id, $id ) ) );
					} else {
						$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i o LEFT JOIN %i e ON e.id=o.entry_id LEFT JOIN %i a ON a.id=o.appointment_id SET o.state='erased',o.bearer_hash=NULL,o.entry_id=NULL,o.appointment_id=NULL,o.service_id=NULL,o.staff_id=NULL,o.location_id=NULL,o.session_id=NULL,o.start_utc=NULL,o.end_utc=NULL,o.timezone=NULL,o.duration_minutes=NULL,o.price_cents=NULL,o.currency=NULL,o.updated_at=%s WHERE e.customer_id=%d OR a.customer_id=%d", $offers, $entries, $prefix . 'appointments', $now, $id, $id ) ) );
					}
				}
				$entry_columns = $wpdb->get_col( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $entries ), 0 );
				$this->check_read();
				if ( in_array( 'intent_json', $entry_columns, true ) ) {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i SET state='erased',customer_id=NULL,service_id=NULL,location_id=NULL,session_id=NULL,date_from=NULL,date_to=NULL,intent_json=NULL,updated_at=%s WHERE customer_id=%d", $entries, $now, $id ) ) );
				} else {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i SET state='erased',customer_id=NULL,service_id=NULL,location_id=NULL,session_id=NULL,date_from=NULL,date_to=NULL,updated_at=%s WHERE customer_id=%d", $entries, $now, $id ) ) );
				}
			}
			// Keep anonymized membership occupancy, but erase durable enrollment lookup.
			$commands = $prefix . 'group_commands';
			$exists   = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $commands ) ) );
			$this->check_read();
			if ( $commands === $exists ) {
				$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i c INNER JOIN %i a ON a.id=c.appointment_id SET c.state='erased', c.appointment_id=NULL, c.session_id=NULL WHERE a.customer_id=%d", $commands, $prefix . 'appointments', $id ) ) );
			}
			$series     = $prefix . 'recurrence_series';
			$members    = $prefix . 'recurrence_occurrences';
			$has_series = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $series ) ) );
			$this->check_read();
			$has_members = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $members ) ) );
			$this->check_read();
			if ( $has_series === $series ) {
				// Template ownership also invalidates replay even if memberships were edited.
				if ( $has_members === $members ) {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i r SET r.state='erased',r.template_id=NULL,r.timezone=NULL,r.first_local=NULL,r.interval_weeks=NULL,r.occurrence_count=NULL WHERE EXISTS (SELECT 1 FROM {$wpdb->prefix}abientot_appointments a WHERE a.id=r.template_id AND a.customer_id=%d) OR EXISTS (SELECT 1 FROM %i m INNER JOIN {$wpdb->prefix}abientot_appointments a ON a.id=m.appointment_id WHERE m.series_id=r.id AND a.customer_id=%d)", $series, $id, $members, $id ) ) );
				} else {
					$this->write( $wpdb->query( $wpdb->prepare( "UPDATE %i r SET r.state='erased',r.template_id=NULL,r.timezone=NULL,r.first_local=NULL,r.interval_weeks=NULL,r.occurrence_count=NULL WHERE EXISTS (SELECT 1 FROM {$wpdb->prefix}abientot_appointments a WHERE a.id=r.template_id AND a.customer_id=%d)", $series, $id ) ) );
				}
			}
			if ( $has_members === $members ) {
				$this->write( $wpdb->query( $wpdb->prepare( "DELETE m FROM %i m INNER JOIN %i a ON a.id=m.appointment_id WHERE a.customer_id=%d", $members, $prefix . 'appointments', $id ) ) );
			}
			$this->write( $wpdb->query( 'COMMIT' ) );
			$started = false;
			return true;
		} catch ( \Throwable $exception ) {
			if ( $started ) {
				$wpdb->query( 'ROLLBACK' );
			}
			return false;
		} finally {
			ImportHistoryRepository::clear_epoch_cache();
			if ( $owns_lock ) {
				BookingLock::release();
			}
		}
	}

	private function write( $result ): void {
		if ( false === $result ) {
			throw new \RuntimeException( 'Privacy write failed.' );
		}
	}

	private function check_read(): void {
		global $wpdb;
		if ( $wpdb->last_error ) {
			throw new \RuntimeException( 'Privacy lookup failed.' );
		}
	}
}