<?php
/**
 * Custom table definition for the delivery log.
 *
 * @package TextTango
 */

declare( strict_types=1 );

namespace TextTango\Install;

use TextTango\Marketing\CampaignLog;

defined( 'ABSPATH' ) || exit;

/**
 * Owns the `texttango_messages` table: its DDL, creation and removal.
 *
 * The table is both the delivery log (spec §10) and the client-side idempotency
 * guard (spec §14) — the TextTango send endpoints accept no idempotency key, so a
 * unique `dedupe_key` is the only thing standing between a retried hook and a
 * second charge.
 */
final class Schema {

	/**
	 * Unqualified table name, without the site's table prefix.
	 */
	public const TABLE = 'texttango_messages';

	/**
	 * Table recording numbers that must not be messaged.
	 */
	public const SUPPRESSIONS_TABLE = 'texttango_suppressions';

	/**
	 * Table holding numbers that agreed, without an account behind them.
	 */
	public const CONSENTS_TABLE = 'texttango_consents';

	/**
	 * Table recording who changed what.
	 */
	public const AUDIT_TABLE = 'texttango_audit';

	/**
	 * Table remembering the campaigns this site has sent.
	 */
	public const CAMPAIGNS_TABLE = 'texttango_campaigns';

	/**
	 * Who a personal-code campaign is sending to, and what each one was given.
	 */
	public const CAMPAIGN_RECIPIENTS_TABLE = 'texttango_campaign_recipients';

	/**
	 * Fully-qualified, prefix-aware table name.
	 */
	public static function table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::TABLE;
	}

	/**
	 * Fully-qualified name of the suppression table.
	 */
	public static function consents_table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::CONSENTS_TABLE;
	}

	/**
	 * Fully-qualified name of the suppression table.
	 */
	public static function suppressions_table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::SUPPRESSIONS_TABLE;
	}

	/**
	 * Fully-qualified name of the audit table.
	 */
	public static function audit_table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::AUDIT_TABLE;
	}

	/**
	 * Create or upgrade the table.
	 *
	 * `dbDelta()` is intentionally fussy about formatting: two spaces after
	 * PRIMARY KEY, one field per line, uppercase types.
	 */
	public static function install(): void {
		global $wpdb;

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

		$table   = self::table_name();
		$collate = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			dedupe_key VARCHAR(191) NOT NULL,
			event VARCHAR(64) NOT NULL DEFAULT '',
			wp_user_id BIGINT UNSIGNED NULL DEFAULT NULL,
			order_id BIGINT UNSIGNED NULL DEFAULT NULL,
			recipient VARCHAR(20) NOT NULL DEFAULT '',
			sender_id VARCHAR(20) NOT NULL DEFAULT '',
			body TEXT NULL DEFAULT NULL,
			texttango_message_uuid VARCHAR(64) NULL DEFAULT NULL,
			status VARCHAR(32) NOT NULL DEFAULT 'queued',
			error_code VARCHAR(64) NULL DEFAULT NULL,
			error_reason TEXT NULL DEFAULT NULL,
			cost DECIMAL(12,4) NULL DEFAULT NULL,
			attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0,
			sandbox TINYINT(1) NOT NULL DEFAULT 0,
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			updated_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY dedupe_key (dedupe_key),
			KEY message_uuid (texttango_message_uuid),
			KEY order_id (order_id),
			KEY status_created (status, created_at),
			KEY recipient_created (recipient, created_at),
			KEY event_created (event, created_at),
			KEY created_at (created_at)
		) {$collate};";

		/*
		 * `recipient_created` is what makes the per-recipient cap cheap: counting
		 * what one number has already been sent today is otherwise a full scan of
		 * the log on every send, and it gets slower exactly as the log gets long
		 * enough for a runaway loop to matter.
		 *
		 * `created_at` alone serves two things that both scan without it: the
		 * per-day figures behind the analytics panel, and — found while measuring
		 * that — `Budget::usage()`, which counts a date range on **every send** to
		 * decide whether a spend cap has been reached. Each of the composite keys
		 * leads with another column, so none of them could answer a question that
		 * knows only a date.
		 *
		 * `event_created` is the same bargain for the Logs screen: filtering by
		 * event, and listing which events the log has ever held, both scan the
		 * whole table without it. Retention defaults to keeping everything, so
		 * that table has no ceiling on a busy shop.
		 *
		 * That explanation lives here rather than in the statement above because
		 * dbDelta does not understand SQL comments. It splits the definition by
		 * line and reads a `--` line as a column, which produces an ALTER TABLE
		 * ending mid-sentence and a database error on every upgrade check.
		 */
		dbDelta( $sql );

		/*
		 * Suppression is keyed by number, not by user. A customer who opts out may
		 * have checked out as a guest and have no account at all, and the same
		 * number can appear on orders from several accounts — so a usermeta flag
		 * would miss exactly the people it most needs to catch.
		 */
		$suppressions = self::suppressions_table_name();

		$sql = "CREATE TABLE {$suppressions} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			recipient VARCHAR(20) NOT NULL,
			reason VARCHAR(64) NOT NULL DEFAULT '',
			source VARCHAR(64) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY recipient (recipient)
		) {$collate};";

		dbDelta( $sql );

		/*
		 * The mirror image of the table above, and it exists for the same reason
		 * that one gives: a number can agree without an account behind it. Consent
		 * was recorded only on a user or an order, which covers everyone this
		 * plugin collects it from and leaves nowhere for somebody imported from a
		 * system that kept a plain subscriber list.
		 *
		 * There is no state column. Presence means consent, exactly as presence in
		 * the suppression table means opted out — a denial needs no row, because
		 * absence is already the answer.
		 */
		$consents = self::consents_table_name();

		$sql = "CREATE TABLE {$consents} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			recipient VARCHAR(20) NOT NULL,
			source VARCHAR(64) NOT NULL DEFAULT '',
			contact_uuid VARCHAR(64) NOT NULL DEFAULT '',
			synced_at DATETIME NULL DEFAULT NULL,
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY recipient (recipient)
		) {$collate};";

		dbDelta( $sql );

		/*
		 * The actor's name is stored alongside their id rather than looked up when
		 * the log is read. An audit trail whose entries turn into "user 47" the day
		 * somebody leaves the company is not an audit trail — the question it exists
		 * to answer is asked precisely when the person is gone.
		 */
		$audit = self::audit_table_name();

		$sql = "CREATE TABLE {$audit} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			actor_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			actor_name VARCHAR(191) NOT NULL DEFAULT '',
			action VARCHAR(64) NOT NULL DEFAULT '',
			subject VARCHAR(191) NOT NULL DEFAULT '',
			detail TEXT NULL DEFAULT NULL,
			ip VARCHAR(100) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			KEY action_created (action, created_at),
			KEY actor_created (actor_id, created_at)
		) {$collate};";

		dbDelta( $sql );

		/*
		 * Campaign history was a single option holding the newest fifty, with
		 * `array_slice( $history, 0, 50 )` on every write — so campaign fifty-one
		 * was not hidden behind a cap, it was deleted. What a shop sent, to how
		 * many people, and what it cost was being destroyed as it went.
		 *
		 * It also feeds the spend caps. A campaign puts no rows in the message
		 * log, because the platform fans it out, so `messages_since()` is the only
		 * thing that makes a large blast visible to a daily ceiling — and
		 * discarding rows silently narrowed the window that ceiling could see.
		 */
		$campaigns = self::campaigns_table_name();

		$sql = "CREATE TABLE {$campaigns} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			uuid VARCHAR(64) NOT NULL DEFAULT '',
			name VARCHAR(191) NOT NULL DEFAULT '',
			body TEXT NULL DEFAULT NULL,
			status VARCHAR(32) NOT NULL DEFAULT '',
			mode VARCHAR(32) NOT NULL DEFAULT '',
			delivery VARCHAR(32) NOT NULL DEFAULT '',
			segment VARCHAR(32) NOT NULL DEFAULT '',
			coupon VARCHAR(191) NOT NULL DEFAULT '',
			recipients INT UNSIGNED NOT NULL DEFAULT 0,
			supplied INT UNSIGNED NOT NULL DEFAULT 0,
			accepted INT UNSIGNED NOT NULL DEFAULT 0,
			delivered INT UNSIGNED NOT NULL DEFAULT 0,
			failed INT UNSIGNED NOT NULL DEFAULT 0,
			pending INT UNSIGNED NOT NULL DEFAULT 0,
			sent INT UNSIGNED NOT NULL DEFAULT 0,
			segments INT UNSIGNED NOT NULL DEFAULT 0,
			credits INT UNSIGNED NOT NULL DEFAULT 0,
			cost DECIMAL(12,4) NULL DEFAULT NULL,
			excluded_suppressed INT UNSIGNED NOT NULL DEFAULT 0,
			excluded_unusable INT UNSIGNED NOT NULL DEFAULT 0,
			sandbox TINYINT(1) NOT NULL DEFAULT 0,
			scheduled_at VARCHAR(32) NOT NULL DEFAULT '',
			dispatched_at VARCHAR(32) NOT NULL DEFAULT '',
			refreshed VARCHAR(32) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			PRIMARY KEY  (id),
			UNIQUE KEY uuid (uuid),
			KEY status_created (status, created_at),
			KEY created_at (created_at)
		) {$collate};";

		/*
		 * `status_created` serves in_flight(), which runs on every campaigns page
		 * load; `created_at` serves both the spend-cap window and paging. The
		 * unique key on `uuid` is what makes the migration below safe to run
		 * twice.
		 */
		dbDelta( $sql );

		/*
		 * Who a personal-code campaign is still sending to, and what each person
		 * was given.
		 *
		 * A campaign sending a different code to everybody cannot use the bulk
		 * endpoint, so it fans out locally in batches — and the list has to be
		 * frozen when the campaign starts rather than re-derived per batch. An
		 * audience re-read between batches shifts the moment somebody opts out,
		 * and an offset into a list that shrank skips whoever moved up.
		 *
		 * It is also the only place that can answer the question a merchant will
		 * certainly ask: which code did this person get, and did they use it.
		 */
		$recipients = self::campaign_recipients_table_name();

		$sql = "CREATE TABLE {$recipients} (
			id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
			campaign_uuid VARCHAR(64) NOT NULL DEFAULT '',
			recipient VARCHAR(32) NOT NULL DEFAULT '',
			coupon VARCHAR(191) NOT NULL DEFAULT '',
			message_id BIGINT UNSIGNED NOT NULL DEFAULT 0,
			status VARCHAR(32) NOT NULL DEFAULT '',
			error_reason VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
			sent_at DATETIME NULL DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY campaign_recipient (campaign_uuid, recipient),
			KEY campaign_status (campaign_uuid, status),
			KEY coupon (coupon)
		) {$collate};";

		/*
		 * `campaign_recipient` is unique because a batch that runs twice — Action
		 * Scheduler guarantees at least once, not exactly once — must not mint a
		 * second coupon for somebody who already has one.
		 *
		 * `campaign_status` answers "what is left to send" without a scan, which
		 * every batch asks. `coupon` answers "who got this code", which is what a
		 * merchant looking at a redemption needs.
		 */
		dbDelta( $sql );

		self::migrate_campaigns();

		update_option( 'texttango_db_version', TEXTTANGO_DB_VERSION, false );
	}

	/**
	 * Move campaign history out of the option it used to live in.
	 *
	 * Idempotent: `uuid` is unique, so a second run inserts nothing. The option
	 * is removed once its rows are in, and a row that fails to insert leaves the
	 * option alone — losing the history to a half-finished migration would be
	 * the same harm this change exists to stop.
	 */
	private static function migrate_campaigns(): void {
		$history = get_option( 'texttango_campaigns', null );

		if ( ! is_array( $history ) ) {
			return;
		}

		$moved = 0;

		foreach ( array_reverse( $history ) as $row ) {
			if ( ! is_array( $row ) || '' === (string) ( $row['uuid'] ?? '' ) ) {
				continue;
			}

			if ( CampaignLog::insert( $row ) ) {
				++$moved;
			}
		}

		$movable = count(
			array_filter(
				$history,
				static fn( $row ): bool => is_array( $row ) && '' !== (string) ( $row['uuid'] ?? '' )
			)
		);

		// Only once every row is in. A half-finished migration that dropped the
		// option would lose exactly the history this change exists to keep.
		if ( $movable === $moved ) {
			delete_option( 'texttango_campaigns' );
		}
	}

	/**
	 * Drop the table. Only ever called from uninstall.
	 */
	public static function drop(): void {
		global $wpdb;

		$tables = array(
			self::table_name(),
			self::suppressions_table_name(),
			self::consents_table_name(),
			self::audit_table_name(),
			self::campaigns_table_name(),
			self::campaign_recipients_table_name(),
		);

		foreach ( $tables as $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Table names cannot be parameterised; these are literals joined to $wpdb->prefix.
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
		}
	}

	/**
	 * Fully-qualified name of the campaign history table.
	 */
	public static function campaigns_table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::CAMPAIGNS_TABLE;
	}

	/**
	 * Fully-qualified name of the personal-campaign recipients table.
	 */
	public static function campaign_recipients_table_name(): string {
		global $wpdb;

		return $wpdb->prefix . self::CAMPAIGN_RECIPIENTS_TABLE;
	}

	/**
	 * Whether the installed table is older than the code expects.
	 */
	public static function needs_upgrade(): bool {
		return get_option( 'texttango_db_version' ) !== TEXTTANGO_DB_VERSION;
	}
}
