<?php
namespace Careback;

defined( 'ABSPATH' ) || exit;

class Database {

	public static function table( $name ) {
		global $wpdb;
		return $wpdb->prefix . 'careback_' . $name;
	}

	/**
	 * Action: fires at the start of create_tables(), before the free part's own
	 * statements ( string $charset ). An add-on creates its tables here, and
	 * the columns it adds to a free table (create_statement()).
	 */
	const CREATE_ACTION = 'careback_create_tables';

	/**
	 * Filter: indexes replayed on an upgrade, as table suffix => index name =>
	 * definition ( array $indexes ). See INDEXES.
	 */
	const INDEXES_FILTER = 'careback_table_indexes';

	/**
	 * Filter: parts of the schema an add-on creates on the same trigger, part =>
	 * version ( array $parts ). A part not recorded at its version makes the next
	 * admin or cron load run the table setup again.
	 */
	const PARTS_FILTER = 'careback_schema_parts';

	/** The parts the last table setup ran with, part => version. */
	const PARTS_OPTION = 'careback_db_parts';

	/**
	 * Whether this site's tables are set up: the schema version is recorded
	 * (the table setup writes it last), or else the requests and events tables
	 * are there (a version deleted by hand).
	 *
	 * False on a site of a network that Careback's network activation did not
	 * reach, or that was added while its setup could not run, until its next
	 * admin or cron load sets it up (Plugin::maybe_upgrade()). Until then its
	 * returns form and tracking page say they are not available yet, their
	 * endpoints refuse, and the daily clean-up waits: nothing there reaches for
	 * a table that is not there.
	 *
	 * A site that is set up answers from the cached option, without a query.
	 *
	 * @return bool
	 */
	public static function ready() {
		if ( '' !== (string) get_option( 'careback_db_version', '' ) ) {
			return true;
		}

		global $wpdb;

		foreach ( array_keys( self::TABLES ) as $suffix ) {
			$table = self::table( $suffix );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- whether the table is there now; a cached answer would be a stale one.
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) !== $table ) {
				return false;
			}
		}

		return true;
	}

	/** Schema version that added the `user_agent` column of the requests table. */
	const USER_AGENT_SCHEMA = '0.18.0';

	/**
	 * Whether this site's tables were last set up at schema $version or later.
	 *
	 * An update raises the schema version at once, but the table setup runs on
	 * the next admin or cron load (Plugin::maybe_upgrade()). Until then a column
	 * the update adds is not there, and code that writes it asks here first, so
	 * a customer's request in that window is stored without it rather than
	 * refused. Reads the recorded version: no query.
	 *
	 * @param string $version Schema version.
	 * @return bool
	 */
	public static function schema_at_least( $version ) {
		$stored = (string) get_option( 'careback_db_version', '' );

		return '' !== $stored && version_compare( $stored, (string) $version, '>=' );
	}

	/**
	 * The free part's tables, one column or key per line, as dbDelta() reads
	 * them: a column is its name and definition, a key keeps the spacing
	 * dbDelta() expects (`PRIMARY KEY  (id)`).
	 *
	 * No SQL comment may go into a line: dbDelta() reads the statement line by
	 * line, takes a `-- …` line for a column named `--`, and on an existing table
	 * sends `ALTER TABLE … ADD COLUMN --`, which the database rejects on every
	 * upgrade. The notes are PHP comments.
	 */
	const TABLES = array(
		'requests' => array(
			'id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
			'order_id BIGINT UNSIGNED NOT NULL',
			"order_number VARCHAR(64) NOT NULL DEFAULT ''",
			"email_hash CHAR(64) NOT NULL DEFAULT ''",
			"customer_name VARCHAR(191) NOT NULL DEFAULT ''",
			'items LONGTEXT NULL',
			'reason VARCHAR(64) NULL',
			'comment TEXT NULL',
			"type VARCHAR(20) NOT NULL DEFAULT 'return'",
			"original_type VARCHAR(20) NOT NULL DEFAULT ''",
			"status VARCHAR(20) NOT NULL DEFAULT 'new'",
			"channel VARCHAR(20) NOT NULL DEFAULT 'portal'",
			'declaration_received_at DATETIME NULL',
			'declaration_sent_at DATETIME NULL',
			'goods_received_at DATETIME NULL',
			"goods_origin VARCHAR(20) NOT NULL DEFAULT ''",
			'dispatch_proof_at DATETIME NULL',
			"closure_ground VARCHAR(32) NOT NULL DEFAULT ''",
			'closure_ground_ref BIGINT UNSIGNED NOT NULL DEFAULT 0',
			"resolution VARCHAR(32) NOT NULL DEFAULT ''",
			'resolution_at DATETIME NULL',
			'resolution_note TEXT NULL',
			'consent_quote TEXT NULL',
			'consent_at DATETIME NULL',
			"flags VARCHAR(255) NOT NULL DEFAULT ''",
			"delivery_state VARCHAR(20) NOT NULL DEFAULT ''",
			'history LONGTEXT NULL',
			"token_jti CHAR(32) NOT NULL DEFAULT ''",
			"ip_hash CHAR(64) NOT NULL DEFAULT ''",
			// The browser (user agent) a request was sent from; empty for one recorded by hand.
			"user_agent VARCHAR(255) NOT NULL DEFAULT ''",
			'created_at DATETIME NOT NULL',
			'updated_at DATETIME NOT NULL',
			'closed_at DATETIME NULL',
			'PRIMARY KEY  (id)',
			'KEY order_id (order_id)',
			'KEY status (status)',
			'KEY created_at (created_at)',
			'KEY email_hash (email_hash)',
			'KEY type_status (type, status)',
			'UNIQUE KEY token_jti (token_jti)',
		),
		// `source`: which public form the refusal came from. Empty on every row
		// written before this column existed, and shown as unknown rather than
		// guessed at: those rows predate the second desk, but saying so on the
		// screen is the operator's call, not the schema's.
		'events'   => array(
			'id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT',
			'type VARCHAR(40) NOT NULL',
			"ip_hash CHAR(64) NOT NULL DEFAULT ''",
			"context VARCHAR(191) NOT NULL DEFAULT ''",
			"source VARCHAR(16) NOT NULL DEFAULT ''",
			'created_at DATETIME NOT NULL',
			'PRIMARY KEY  (id)',
			'KEY type_created (type, created_at)',
			'KEY created_at (created_at)',
			'KEY type_context (type, context)',
		),
	);

	/**
	 * Creates the tables or brings them up to date, replays the indexes dbDelta
	 * leaves out, and records the parts of the schema add-ons took part with.
	 *
	 * An add-on runs first (`careback_create_tables`): one that adds columns to a
	 * free table reads what the table carried before, and runs dbDelta() over
	 * the free statement with its own columns placed in it, so a table created
	 * fresh comes out in one piece. The free statements that follow find
	 * nothing to change on such a table; dbDelta() never drops a column it is
	 * not told about.
	 */
	public static function create_tables() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$charset = $wpdb->get_charset_collate();

		/**
		 * Fires before the free part creates or updates its tables. See
		 * api/data.md.
		 *
		 * @param string $charset The charset and collation clause of the site.
		 */
		do_action( self::CREATE_ACTION, $charset ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_create_tables, the final prefix.

		foreach ( array_keys( self::TABLES ) as $table ) {
			dbDelta( self::as_reported( self::create_statement( $table, $charset ) ) );
		}

		self::add_missing_indexes();

		update_option( self::PARTS_OPTION, self::parts() );
	}

	/**
	 * The CREATE TABLE statement of a free table, one column or key per line,
	 * with the lines an add-on places in it.
	 *
	 * @param string                 $table         Table suffix: `requests`, `events`.
	 * @param string                 $charset       Charset and collation clause.
	 * @param array<string,string[]> $columns_after Column name => column lines to place after it.
	 * @param array<string,string[]> $keys_after    Key name (`PRIMARY` for the primary key) => key lines to place after it.
	 * @return string Empty for a table the free part does not have.
	 */
	public static function create_statement( $table, $charset = '', array $columns_after = array(), array $keys_after = array() ) {
		if ( ! isset( self::TABLES[ $table ] ) ) {
			return '';
		}

		$columns = array();
		$keys    = array();

		foreach ( self::TABLES[ $table ] as $line ) {
			$is_key = (bool) preg_match( '/^(PRIMARY KEY|UNIQUE KEY|KEY)\s+(\w+)?/', $line, $match );
			$name   = $is_key ? ( 'PRIMARY KEY' === $match[1] ? 'PRIMARY' : $match[2] ) : strtok( $line, ' ' );
			$after  = $is_key ? $keys_after : $columns_after;
			$lines  = isset( $after[ $name ] ) ? array_values( (array) $after[ $name ] ) : array();

			if ( $is_key ) {
				$keys = array_merge( $keys, array( $line ), $lines );
				unset( $keys_after[ $name ] );
			} else {
				$columns = array_merge( $columns, array( $line ), $lines );
				unset( $columns_after[ $name ] );
			}
		}

		// Lines placed after a name the table does not carry go last of their kind.
		foreach ( $columns_after as $lines ) {
			$columns = array_merge( $columns, array_values( (array) $lines ) );
		}

		foreach ( $keys_after as $lines ) {
			$keys = array_merge( $keys, array_values( (array) $lines ) );
		}

		return 'CREATE TABLE ' . self::table( $table ) . " (\n\t" . implode( ",\n\t", array_merge( $columns, $keys ) ) . "\n) " . $charset . ';';
	}

	/**
	 * Names of the columns of a free table, in table order.
	 *
	 * @param string $table Table suffix: `requests`, `events`.
	 * @return string[] Empty for a table the free part does not have.
	 */
	public static function columns( $table ) {
		$out = array();

		foreach ( isset( self::TABLES[ $table ] ) ? self::TABLES[ $table ] : array() as $line ) {
			if ( ! preg_match( '/^(PRIMARY KEY|UNIQUE KEY|KEY)\b/', $line ) ) {
				$out[] = (string) strtok( $line, ' ' );
			}
		}

		return $out;
	}

	/**
	 * Parts of the schema the add-ons declare, part => version, sorted.
	 *
	 * @return array<string,string>
	 */
	public static function parts() {
		/**
		 * Filters the parts of the schema add-ons create on the table setup. See
		 * api/data.md.
		 *
		 * @param array $parts Part => version.
		 */
		$parts = apply_filters( self::PARTS_FILTER, array() ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_schema_parts, the final prefix.
		$out   = array();

		foreach ( is_array( $parts ) ? $parts : array() as $part => $version ) {
			if ( is_string( $part ) && '' !== $part && is_scalar( $version ) ) {
				$out[ $part ] = (string) $version;
			}
		}

		ksort( $out );

		return $out;
	}

	/**
	 * True when every part an add-on declares was set up at its version.
	 *
	 * An install whose table setup ran before the parts were recorded has no
	 * record at all: the plugin then created every table and column itself, so
	 * what is declared now is taken as present and recorded. A record that
	 * exists, even an empty one, is believed.
	 *
	 * A site whose tables were never set up (ready()) has no record either, and
	 * nothing to take as present: no part is current there and nothing is
	 * recorded, so an add-on that checks the record goes on waiting for its
	 * part, and the next admin or cron load sets the whole site up.
	 *
	 * @return bool
	 */
	public static function parts_current() {
		$declared = self::parts();

		if ( ! $declared ) {
			return true;
		}

		$stored = get_option( self::PARTS_OPTION, null );

		if ( null === $stored ) {
			if ( ! self::ready() ) {
				return false;
			}

			update_option( self::PARTS_OPTION, $declared );

			return true;
		}

		foreach ( $declared as $part => $version ) {
			if ( ! is_array( $stored ) || ! isset( $stored[ $part ] ) || (string) $stored[ $part ] !== $version ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * A table definition with each column type written the way MariaDB and MySQL
	 * report it back: in lower case, and an integer with its display width
	 * (`BIGINT UNSIGNED` reads back as `bigint(20) unsigned`). The types
	 * themselves are unchanged.
	 *
	 * WordPress's dbDelta() compares the reported type with the written one as
	 * text, so it took every such difference for a changed type and sent
	 * `ALTER TABLE … CHANGE COLUMN` on every upgrade: for each integer column,
	 * and before WordPress compared types case-insensitively (6.6 and earlier)
	 * for every column. MySQL 8.0.17+ reports integers without a width, and
	 * dbDelta() ignores that difference there.
	 *
	 * @param string $sql CREATE TABLE statement, one column or key per line.
	 * @return string
	 */
	public static function as_reported( $sql ) {
		// Integer type => display width signed, display width unsigned.
		$widths = array(
			'tinyint'   => array( 4, 3 ),
			'smallint'  => array( 6, 5 ),
			'mediumint' => array( 9, 8 ),
			'int'       => array( 11, 10 ),
			'bigint'    => array( 20, 20 ),
		);

		// A column line: its name, its type, and UNSIGNED when present. Key lines
		// and the CREATE TABLE line itself are left as they are.
		return preg_replace_callback(
			'/^(\s*)(?!(?:CREATE|PRIMARY|UNIQUE|KEY|INDEX|FULLTEXT|SPATIAL)\b)(\w+) ([A-Za-z]+(?:\([\d,]+\))?)(?![\w(])( UNSIGNED)?/m',
			static function ( $column ) use ( $widths ) {
				$type     = strtolower( $column[3] );
				$unsigned = ! empty( $column[4] );

				if ( isset( $widths[ $type ] ) ) {
					$type .= '(' . $widths[ $type ][ $unsigned ? 1 : 0 ] . ')';
				}

				return $column[1] . $column[2] . ' ' . $type . ( $unsigned ? ' unsigned' : '' );
			},
			$sql
		);
	}

	/**
	 * Indexes replayed on an upgrade, as table suffix => index name =>
	 * definition. `dbDelta` adds an index only while creating the table, so each
	 * entry here is added separately against a table that already exists. An
	 * add-on adds the indexes of its own columns and tables through
	 * `careback_table_indexes`.
	 */
	const INDEXES = array(
		'requests' => array(
			'type_status'      => 'KEY type_status (type, status)',
			'email_hash'       => 'KEY email_hash (email_hash)',
			// Replayed like the rest: the unique key is in the create statement,
			// and dbDelta adds an index only while creating the table — so a site
			// upgraded from a schema that predates it never received the guard
			// that stops one ticket filing twice.
			'token_jti'        => 'UNIQUE KEY token_jti (token_jti)',
		),
		'events'   => array(
			'type_created' => 'KEY type_created (type, created_at)',
			'type_context' => 'KEY type_context (type, context)',
		),
	);

	/**
	 * Every index replayed on an upgrade: the free part's, and those an add-on
	 * adds. A free entry keeps its definition.
	 *
	 * @return array<string,array<string,string>>
	 */
	public static function indexes() {
		/**
		 * Filters the indexes replayed on an upgrade. See api/data.md.
		 *
		 * @param array $indexes Table suffix => index name => definition.
		 */
		$indexes = apply_filters( self::INDEXES_FILTER, self::INDEXES ); // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.DynamicHooknameFound -- careback_table_indexes, the final prefix.
		$out     = self::INDEXES;

		foreach ( is_array( $indexes ) ? $indexes : array() as $suffix => $list ) {
			if ( ! is_string( $suffix ) || ! preg_match( '/^[a-z_]+$/', $suffix ) || ! is_array( $list ) ) {
				continue;
			}

			foreach ( $list as $name => $definition ) {
				if ( is_string( $name ) && is_string( $definition ) && ! isset( $out[ $suffix ][ $name ] ) ) {
					$out[ $suffix ][ $name ] = $definition;
				}
			}
		}

		return $out;
	}

	/** Indexes the last upgrade could not add, and why. */
	const GAPS_OPTION = 'careback_index_gaps';

	/**
	 * Adds any index of self::indexes() that the live table does not carry yet.
	 * Runs after dbDelta and skips a table that does not exist.
	 *
	 * A UNIQUE index is not added blind. On a site whose data already breaks the
	 * rule the ALTER fails, and until 0.66.0 the result was thrown away — the
	 * upgrade reported success, the version was stamped, and the site went on
	 * without the guard the release exists to add. Nothing said so. Anything
	 * that cannot be added is now recorded in GAPS_OPTION and surfaced on the
	 * plugin's screens, and a UNIQUE index is checked against the live rows
	 * first so the reason can be stated rather than guessed at.
	 */
	private static function add_missing_indexes() {
		global $wpdb;

		$gaps = array();

		foreach ( self::indexes() as $suffix => $indexes ) {
			$table = self::table( $suffix );

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the installer reads the schema as it is now.
			if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table ) ) !== $table ) {
				continue;
			}

			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the installer reads the schema as it is now.
			$existing = (array) $wpdb->get_col( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ), 2 );

			foreach ( $indexes as $name => $definition ) {
				if ( in_array( $name, $existing, true ) ) {
					continue;
				}

				$clash = self::unique_clash( $table, $definition );

				if ( $clash > 0 ) {
					$gaps[ $suffix . '.' . $name ] = sprintf(
						/* translators: 1: index name, 2: table name, 3: number of duplicate values. */
						_n( 'The unique index %1$s could not be added to %2$s: %3$d value already appears more than once. The rows have to be reconciled before the guard can be put in place.', 'The unique index %1$s could not be added to %2$s: %3$d values already appear more than once. The rows have to be reconciled before the guard can be put in place.', $clash, 'careback' ),
						$name,
						$table,
						$clash
					);
					continue;
				}

				// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- the installer: a table of this plugin (prefix + suffix), an index definition from self::indexes(), code and never input.
				$added = $wpdb->query( "ALTER TABLE {$table} ADD {$definition}" );

				if ( false === $added ) {
					$gaps[ $suffix . '.' . $name ] = sprintf(
						/* translators: 1: index name, 2: table name, 3: database error. */
						__( 'The index %1$s could not be added to %2$s. The database said: %3$s', 'careback' ),
						$name,
						$table,
						$wpdb->last_error ? $wpdb->last_error : __( 'nothing', 'careback' )
					);
				}
			}
		}

		if ( $gaps ) {
			update_option( self::GAPS_OPTION, $gaps, false );
		} else {
			delete_option( self::GAPS_OPTION );
		}
	}

	/**
	 * How many values would break a UNIQUE index that is about to be added.
	 *
	 * Returns 0 for an index that is not UNIQUE, for a definition this cannot
	 * read, and for a table whose rows are already fit for it. Empty values are
	 * counted with everything else: MySQL treats two empty strings as equal and
	 * refuses them, which is exactly the case a site upgrading from before the
	 * column existed runs into.
	 *
	 * @param string $table      Full table name.
	 * @param string $definition Index definition as written in self::INDEXES.
	 * @return int Number of values appearing more than once.
	 */
	private static function unique_clash( $table, $definition ) {
		global $wpdb;

		if ( 0 !== stripos( trim( $definition ), 'UNIQUE' ) ) {
			return 0;
		}

		if ( ! preg_match( '/\(([^)]+)\)/', $definition, $matches ) ) {
			return 0;
		}

		$columns = array();

		foreach ( explode( ',', $matches[1] ) as $column ) {
			$column = trim( $column, " `\t\n\r" );

			// Only plain column names, so nothing of the definition can reach the
			// query as anything but an identifier we recognise.
			if ( ! preg_match( '/^[A-Za-z0-9_]+$/', $column ) ) {
				return 0;
			}

			$columns[] = $column;
		}

		if ( ! $columns ) {
			return 0;
		}

		$list = implode( ', ', array_fill( 0, count( $columns ), '%i' ) );

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- the installer checks the live rows before adding the index.
		return (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM ( SELECT 1 FROM %i GROUP BY {$list} HAVING COUNT(*) > 1 ) AS d", // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- a list of %i placeholders.
				array_merge( array( $table ), $columns )
			)
		);
	}
}