<?php
/**
 * Database schema.
 *
 * @package Rahkar\WP
 */

declare( strict_types = 1 );

namespace Rahkar\WP\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Owns every table the plugin creates.
 *
 * Tables are declared in one place rather than each module creating its own on
 * activation, so a fresh install produces the same schema regardless of which
 * modules happen to be switched on, and enabling a module later never has to run
 * DDL on a live site.
 */
final class Schema {

	public const VERSION_OPTION = 'rahkar_wp_db_version';

	/**
	 * Bare table names shipped by the free plugin, without the site prefix.
	 *
	 * Read through {@see self::tables()} rather than directly, so an add-on's
	 * tables are visible to the health screen, the uninstaller and anything else
	 * that walks the list.
	 */
	public const TABLES = [
		'jobs'         => 'rahkar_jobs',
		'logs'         => 'rahkar_logs',
		'otp'          => 'rahkar_otp',
		'rate'         => 'rahkar_rate_limits',
		'identity'     => 'rahkar_identities',
		'otp_log'      => 'rahkar_otp_log',
		'hub_outbox'   => 'rahkar_hub_outbox',
		'flow_entries' => 'rahkar_flow_entries',
		'optouts'      => 'rahkar_optouts',
		'links'        => 'rahkar_links',
		'cust_orders'  => 'rahkar_customer_orders',
		'cust_stats'   => 'rahkar_customer_stats',
	];

	/**
	 * Every table this install owns, add-ons included.
	 *
	 * @return array<string, string>
	 */
	public static function tables(): array {
		/**
		 * Filters the table map.
		 *
		 * Keys are short identifiers used with {@see self::table()}; values are bare
		 * table names without the site prefix. An add-on that adds a key must also
		 * add its `CREATE TABLE` through `rahkar_wp_table_definitions`, or the name
		 * will resolve to a table that was never created.
		 *
		 * @param array<string, string> $tables Bare table names, keyed by identifier.
		 */
		$tables = apply_filters( 'rahkar_wp_tables', self::TABLES );

		return is_array( $tables ) ? $tables : self::TABLES;
	}

	/**
	 * Fully-qualified table name.
	 *
	 * An unknown key returns the prefix plus the key itself, unchanged. That is
	 * deliberate: it keeps a typo producing an obviously wrong table name in an
	 * error message rather than a silent query against the wrong table.
	 *
	 * @param string $key One of the {@see self::tables()} keys.
	 */
	public static function table( string $key ): string {
		global $wpdb;

		return $wpdb->prefix . ( self::tables()[ $key ] ?? $key );
	}

	/**
	 * Create or upgrade every table.
	 *
	 * Safe to call repeatedly — `dbDelta` diffs against the live schema.
	 */
	public static function install(): void {
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		global $wpdb;

		$collate = $wpdb->get_charset_collate();

		/**
		 * Filters the `CREATE TABLE` statements run on install and upgrade.
		 *
		 * An add-on appends its own here. `dbDelta` diffs against the live schema,
		 * so a statement that is already satisfied costs nothing — which is why an
		 * add-on can simply call `Schema::install()` from its activation hook
		 * rather than shipping DDL of its own.
		 *
		 * @param list<string> $definitions Statements in `dbDelta` dialect.
		 * @param string       $collate     Charset/collation clause for this site.
		 */
		$definitions = apply_filters( 'rahkar_wp_table_definitions', self::definitions( $collate ), $collate );

		foreach ( (array) $definitions as $sql ) {
			if ( is_string( $sql ) && '' !== $sql ) {
				dbDelta( $sql );
			}
		}

		update_option( self::VERSION_OPTION, RAHKAR_WP_DB_VERSION, false );
	}

	/**
	 * Is the stored schema behind the code?
	 */
	public static function needsUpgrade(): bool {
		return (int) get_option( self::VERSION_OPTION, 0 ) < RAHKAR_WP_DB_VERSION;
	}

	/**
	 * Does a table actually exist?
	 *
	 * Used by the health screen to catch a half-finished activation.
	 *
	 * @param string $key One of the {@see self::tables()} keys.
	 */
	public static function exists( string $key ): bool {
		global $wpdb;

		$table = self::table( $key );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		return (string) $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) === $table;
	}

	/**
	 * Drop every table. Uninstall only.
	 */
	public static function drop(): void {
		global $wpdb;

		foreach ( array_keys( self::tables() ) as $key ) {
			$table = self::table( $key );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange
			$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $table ) );
		}

		delete_option( self::VERSION_OPTION );
	}

	/**
	 * Table definitions, in `dbDelta` dialect.
	 *
	 * `dbDelta` is fussy: two spaces after PRIMARY KEY, one field per line, and
	 * KEY names must match exactly or it will try to re-add them on every call.
	 *
	 * @param string $collate Charset/collation clause.
	 *
	 * @return list<string>
	 */
	private static function definitions( string $collate ): array {
		$jobs     = self::table( 'jobs' );
		$logs     = self::table( 'logs' );
		$otp      = self::table( 'otp' );
		$rate     = self::table( 'rate' );
		$identity = self::table( 'identity' );
		$otpLog   = self::table( 'otp_log' );
		$outbox   = self::table( 'hub_outbox' );
		$flows    = self::table( 'flow_entries' );
		$optouts  = self::table( 'optouts' );
		$links    = self::table( 'links' );
		$cOrders  = self::table( 'cust_orders' );
		$cStats   = self::table( 'cust_stats' );

		return [
			// Background work. `due` carries the exact column order the worker's
			// claim query filters and sorts on, so claiming a batch is an index
			// range scan rather than a filesort over the whole table.
			"CREATE TABLE {$jobs} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				hook varchar(64) NOT NULL,
				payload longtext NOT NULL,
				job_group varchar(64) NOT NULL DEFAULT '',
				priority tinyint(3) unsigned NOT NULL DEFAULT 10,
				status tinyint(1) unsigned NOT NULL DEFAULT 0,
				attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
				available_at datetime NOT NULL,
				reserved_at datetime DEFAULT NULL,
				claim_token varchar(32) NOT NULL DEFAULT '',
				created_at datetime NOT NULL,
				last_error text DEFAULT NULL,
				PRIMARY KEY  (id),
				KEY due (status,available_at,priority),
				KEY reserved (status,reserved_at),
				KEY claim (claim_token),
				KEY dedupe (hook,job_group)
			) {$collate};",

			// Audit trail. Written as one buffered multi-row insert per request.
			"CREATE TABLE {$logs} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				level tinyint(1) unsigned NOT NULL DEFAULT 3,
				channel varchar(32) NOT NULL DEFAULT '',
				message varchar(500) NOT NULL DEFAULT '',
				context text DEFAULT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY channel_time (channel,created_at),
				KEY level_time (level,created_at)
			) {$collate};",

			// One-time codes. The code itself is never stored — only an HMAC — so a
			// database leak cannot be replayed against the login form.
			"CREATE TABLE {$otp} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				identifier varchar(191) NOT NULL,
				channel varchar(16) NOT NULL DEFAULT 'sms',
				code_hash char(64) NOT NULL,
				token char(64) NOT NULL,
				attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
				verified tinyint(1) unsigned NOT NULL DEFAULT 0,
				ip varchar(45) NOT NULL DEFAULT '',
				expires_at datetime NOT NULL,
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY token (token),
				KEY identifier_time (identifier,created_at),
				KEY expiry (expires_at)
			) {$collate};",

			// Throttling. Deliberately a table rather than transients: on a site
			// without a persistent object cache, transients land in wp_options and
			// bloat the autoload payload every request pays for.
			"CREATE TABLE {$rate} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				bucket varchar(64) NOT NULL,
				subject varchar(191) NOT NULL,
				hits smallint(5) unsigned NOT NULL DEFAULT 0,
				expires_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY bucket_subject (bucket,subject),
				KEY expiry (expires_at)
			) {$collate};",

			// Verified phone numbers, stored in E.164. Replaces wp_rs_phone_numbers.
			"CREATE TABLE {$identity} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				user_id bigint(20) unsigned NOT NULL,
				type varchar(16) NOT NULL DEFAULT 'phone',
				value varchar(191) NOT NULL,
				country char(2) NOT NULL DEFAULT 'IR',
				verified tinyint(1) unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				updated_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY type_value (type,value),
				KEY user_type (user_id,type)
			) {$collate};",

			// What happened to each sign-in attempt: code sends, checks, sign-ins,
			// throttles and the administrators' alerts. The monitoring screen, the
			// alert rules and the failsafe all read from here. Never the code.
			"CREATE TABLE {$otpLog} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				event varchar(16) NOT NULL DEFAULT '',
				result varchar(16) NOT NULL DEFAULT '',
				phone varchar(32) NOT NULL DEFAULT '',
				user_id bigint(20) unsigned NOT NULL DEFAULT 0,
				gateway varchar(32) NOT NULL DEFAULT '',
				via varchar(16) NOT NULL DEFAULT '',
				message varchar(250) NOT NULL DEFAULT '',
				ip varchar(45) NOT NULL DEFAULT '',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY event_time (event,created_at),
				KEY gateway_time (gateway,created_at),
				KEY phone (phone)
			) {$collate};",

			// Events waiting for the Rahkar Foroosh hub. The id is the contract's
			// `seq`, so it only grows; delivered rows are deleted, so the table
			// stays small. `pending` matches the flush query exactly.
			"CREATE TABLE {$outbox} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				event_id char(36) NOT NULL,
				type varchar(64) NOT NULL,
				occurred_at datetime NOT NULL,
				origin tinyint(1) unsigned NOT NULL DEFAULT 0,
				payload longtext NOT NULL,
				status tinyint(1) unsigned NOT NULL DEFAULT 0,
				attempts tinyint(3) unsigned NOT NULL DEFAULT 0,
				last_error varchar(250) NOT NULL DEFAULT '',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY pending (status,id)
			) {$collate};",

			// One row per entry into a promotional flow, both groups. The weekly
			// cap counts these by number over seven days; `phone_hash` is a blind
			// index (Notifications\SiteKey), never the number.
			"CREATE TABLE {$flows} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				flow varchar(64) NOT NULL,
				unit varchar(16) NOT NULL,
				unit_id varchar(64) NOT NULL DEFAULT '',
				phone_hash char(64) NOT NULL,
				grp tinyint(1) unsigned NOT NULL,
				entered_at datetime NOT NULL,
				PRIMARY KEY  (id),
				KEY phone_time (phone_hash,entered_at),
				KEY entered_at (entered_at),
				KEY flow_time (flow,entered_at)
			) {$collate};",

			// The opt-out list. A blind index and a masked number, no number.
			"CREATE TABLE {$optouts} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				phone_hash char(64) NOT NULL,
				phone_masked varchar(20) NOT NULL DEFAULT '',
				source varchar(16) NOT NULL DEFAULT '',
				created_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY phone_hash (phone_hash)
			) {$collate};",

			// Short links (Notifications\TrackedLinks). The long URL lives only
			// here; `phone_hash` is a blind index, never the number.
			"CREATE TABLE {$links} (
				id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
				token varchar(16) NOT NULL,
				module varchar(64) NOT NULL DEFAULT '',
				flow varchar(64) NOT NULL DEFAULT '',
				step varchar(64) NOT NULL DEFAULT '',
				target varchar(16) NOT NULL DEFAULT 'page',
				destination text NOT NULL,
				product_id bigint(20) unsigned NOT NULL DEFAULT 0,
				coupon_code varchar(64) NOT NULL DEFAULT '',
				phone_hash char(64) NOT NULL DEFAULT '',
				message_id char(36) NOT NULL DEFAULT '',
				clicks int(10) unsigned NOT NULL DEFAULT 0,
				bot_clicks int(10) unsigned NOT NULL DEFAULT 0,
				first_clicked_at datetime DEFAULT NULL,
				created_at datetime NOT NULL,
				expires_at datetime NOT NULL,
				PRIMARY KEY  (id),
				UNIQUE KEY token (token),
				KEY expires_at (expires_at)
			) {$collate};",

			// Customer stats (Customers\Stats): one row per order with a mobile, the
			// source each customer's row is rebuilt from. `phone_hash` is a blind
			// index (Notifications\SiteKey); `net_sales` is in the store currency.
			"CREATE TABLE {$cOrders} (
				order_id bigint(20) unsigned NOT NULL,
				phone_hash char(64) NOT NULL,
				is_sale tinyint(1) unsigned NOT NULL DEFAULT 0,
				created_at datetime NOT NULL,
				local_date date NOT NULL,
				net_sales decimal(20,4) NOT NULL DEFAULT 0,
				PRIMARY KEY  (order_id),
				KEY phone_hash (phone_hash)
			) {$collate};",

			// One row per mobile: the numbers of DATA-MODEL.md §4-6 and §4-10 in
			// rahkar-foroosh. `computed_at` lets a flow's sweep read only the
			// customers that changed since its last run.
			"CREATE TABLE {$cStats} (
				phone_hash char(64) NOT NULL,
				orders_count int(10) unsigned NOT NULL DEFAULT 0,
				purchase_days int(10) unsigned NOT NULL DEFAULT 0,
				first_order_at datetime DEFAULT NULL,
				last_order_at datetime DEFAULT NULL,
				first_date date DEFAULT NULL,
				last_date date DEFAULT NULL,
				avg_interval_days decimal(8,2) DEFAULT NULL,
				cycle_days decimal(8,2) DEFAULT NULL,
				net_spent decimal(20,4) NOT NULL DEFAULT 0,
				last_order_id bigint(20) unsigned NOT NULL DEFAULT 0,
				computed_at datetime NOT NULL,
				PRIMARY KEY  (phone_hash),
				KEY computed_at (computed_at)
			) {$collate};",
		];
	}
}