<?php
/**
 * Database schema.
 *
 * @package StockAlibi
 */

declare( strict_types=1 );

namespace StockAlibi\Database;

defined( 'ABSPATH' ) || exit;

/**
 * Names and definitions of the plugin's tables.
 *
 * Bump VERSION whenever a definition changes; `Installer::maybe_upgrade()`
 * then runs dbDelta on the next request.
 */
final class Schema {

	/**
	 * Schema version, stored in the `stal_db_version` option.
	 */
	public const VERSION = '3';

	/**
	 * Indexes of earlier versions to drop, by table name without prefix.
	 */
	public const OBSOLETE_INDEXES = array(
		'stock_alibi_events' => array( 'source_time', 'field', 'created_at' ),
		'stock_alibi_state'  => array( 'checked_at' ),
	);

	/**
	 * Name of the change log table, including the site prefix.
	 */
	public static function events_table(): string {
		global $wpdb;

		return $wpdb->prefix . 'stock_alibi_events';
	}

	/**
	 * Name of the last-known-value table used by reconciliation.
	 */
	public static function state_table(): string {
		global $wpdb;

		return $wpdb->prefix . 'stock_alibi_state';
	}

	/**
	 * All table names.
	 *
	 * @return string[]
	 */
	public static function tables(): array {
		return array( self::events_table(), self::state_table() );
	}

	/**
	 * CREATE TABLE statements in the format dbDelta expects: one column per
	 * line, `KEY` instead of `INDEX`, two spaces after `PRIMARY KEY`.
	 *
	 * Indexes:
	 * - product_time: timeline of one product (the most common query).
	 * - parent_id: all variations of a variable product.
	 * - source_time: report by source and period.
	 * - field: filter by field.
	 * - created_at: retention cleanup and store-wide date ranges.
	 * - request_id: group the changes of one request.
	 * - sku: search by SKU, including SKUs that changed later.
	 *
	 * State table (reconciliation): `last_value` is the value last known to be
	 * in the database, verified at `checked_at`. A difference no event explains
	 * is first kept as `seen_value` / `seen_at` and recorded only if it is still
	 * there after a grace period. Version 2 added the two `seen_` columns.
	 * Version 3 replaced the events indexes (see above), shortened `source_ref`
	 * to 100 characters, and removed the unused `checked_at` index of the
	 * state table, which every reconciliation pass rewrites.
	 *
	 * @return string[]
	 */
	public static function definitions(): array {
		global $wpdb;

		$collate = $wpdb->get_charset_collate();
		$events  = self::events_table();
		$state   = self::state_table();

		return array(
			"CREATE TABLE {$events} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  product_id bigint(20) unsigned NOT NULL,
  parent_id bigint(20) unsigned NOT NULL DEFAULT 0,
  sku varchar(100) NOT NULL DEFAULT '',
  field varchar(32) NOT NULL,
  old_value varchar(191) DEFAULT NULL,
  new_value varchar(191) DEFAULT NULL,
  delta decimal(20,4) DEFAULT NULL,
  source varchar(32) NOT NULL,
  source_ref varchar(100) NOT NULL DEFAULT '',
  user_id bigint(20) unsigned DEFAULT NULL,
  request_id char(36) NOT NULL,
  context longtext DEFAULT NULL,
  created_at datetime NOT NULL,
  PRIMARY KEY  (id),
  KEY product_time (product_id,created_at),
  KEY parent_id (parent_id),
  KEY time_cover (created_at,field,source,delta,product_id),
  KEY source_cover (source,created_at,field,product_id,source_ref),
  KEY source_ref (source_ref),
  KEY stock_value (field,new_value(16)),
  KEY request_id (request_id),
  KEY sku (sku),
  KEY user_id (user_id)
) {$collate};",
			"CREATE TABLE {$state} (
  product_id bigint(20) unsigned NOT NULL,
  field varchar(32) NOT NULL,
  last_value varchar(191) DEFAULT NULL,
  checked_at datetime NOT NULL,
  seen_value varchar(191) DEFAULT NULL,
  seen_at datetime DEFAULT NULL,
  PRIMARY KEY  (product_id,field),
  KEY seen_at (seen_at)
) {$collate};",
		);
	}
}