<?php
/**
 * Database tables for report runs and per-recipient delivery history.
 *
 * @package TurtleReport
 */

namespace TurtleReport\Delivery;

defined( 'ABSPATH' ) || exit;

/**
 * Initializes the plugin's two tables with dbDelta().
 *
 * - {prefix}turtlereport_runs: one row per report (a site and a period):
 *   its status, readiness checks, insights, notes and the numbers it was
 *   built from, so a reviewed or resent report uses the same figures.
 * - {prefix}turtlereport_deliveries: one row per recipient of a run, with
 *   the result of every attempt.
 *
 * Installation is idempotent. Tables use the site's own prefix, so each
 * Multisite subsite keeps its own history.
 */
final class Schema {

	/** Bump when the table definitions change. */
	const VERSION = '1';

	const OPTION = 'turtlereport_db_version';

	/** Set only after columns and indexes were verified; the version option alone proves nothing. */
	const VERIFIED = 'turtlereport_db_verified';

	/** Blog-local back-off after a failed repair, so requests don't rerun DDL. */
	const RETRY = 'turtlereport_schema_retry';

	/**
	 * Runs table name.
	 *
	 * @return string
	 */
	public static function runs() {
		global $wpdb;
		return $wpdb->prefix . 'turtlereport_runs';
	}

	/**
	 * Deliveries table name.
	 *
	 * @return string
	 */
	public static function deliveries() {
		global $wpdb;
		return $wpdb->prefix . 'turtlereport_deliveries';
	}

	/**
	 * Initializes the tables when their schema has not been recorded.
	 *
	 * @return bool Whether the tables are ready.
	 */
	public static function ensure() {
		if ( self::ready() ) {
			return true;
		}
		if ( get_transient( self::RETRY ) ) {
			return false;
		}
		return self::install();
	}

	/**
	 * Whether setup recorded verified tables, without querying their structure.
	 *
	 * @return bool
	 */
	public static function ready() {
		return self::VERSION === get_option( self::OPTION ) && self::VERSION === get_option( self::VERIFIED );
	}

	/**
	 * Initializes the tables.
	 *
	 * @return bool Whether required columns and indexes are ready afterwards.
	 */
	public static function install() {
		global $wpdb;
		// A failed repair must never leave an earlier success receipt behind.
		delete_option( self::VERIFIED );
		if ( ! function_exists( 'dbDelta' ) && defined( 'ABSPATH' ) && is_readable( ABSPATH . 'wp-admin/includes/upgrade.php' ) ) {
			require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		}
		if ( ! function_exists( 'dbDelta' ) ) {
			return false;
		}
		$charset = method_exists( $wpdb, 'get_charset_collate' ) ? $wpdb->get_charset_collate() : '';
		$runs    = self::runs();
		$sends   = self::deliveries();

		dbDelta(
			"CREATE TABLE {$runs} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  run_key varchar(80) NOT NULL DEFAULT '',
  site_id varchar(20) NOT NULL DEFAULT '',
  kind varchar(12) NOT NULL DEFAULT 'scheduled',
  slot bigint(20) NOT NULL DEFAULT 0,
  period_start char(10) NOT NULL DEFAULT '',
  period_end char(10) NOT NULL DEFAULT '',
  compare_start char(10) NOT NULL DEFAULT '',
  compare_end char(10) NOT NULL DEFAULT '',
  status varchar(20) NOT NULL DEFAULT 'preparing',
  readiness varchar(10) NOT NULL DEFAULT '',
  checks longtext NULL,
  insights longtext NULL,
  notes text NULL,
  snapshot longtext NULL,
  meta longtext NULL,
  attempts smallint(5) unsigned NOT NULL DEFAULT 0,
  next_check bigint(20) NOT NULL DEFAULT 0,
  reviewed_by bigint(20) unsigned NOT NULL DEFAULT 0,
  reviewed_at bigint(20) NOT NULL DEFAULT 0,
  sent_at bigint(20) NOT NULL DEFAULT 0,
  created bigint(20) NOT NULL DEFAULT 0,
  updated bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  UNIQUE KEY run_key (run_key),
  KEY site_created (site_id,created),
  KEY status_check (status,next_check)
) {$charset};"
		);
		dbDelta(
			"CREATE TABLE {$sends} (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  run_id bigint(20) unsigned NOT NULL DEFAULT 0,
  site_id varchar(20) NOT NULL DEFAULT '',
  recipient varchar(190) NOT NULL DEFAULT '',
  status varchar(12) NOT NULL DEFAULT 'pending',
  attempts smallint(5) unsigned NOT NULL DEFAULT 0,
  last_attempt bigint(20) NOT NULL DEFAULT 0,
  next_attempt bigint(20) NOT NULL DEFAULT 0,
  error text NULL,
  created bigint(20) NOT NULL DEFAULT 0,
  updated bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY  (id),
  UNIQUE KEY run_recipient (run_id,recipient),
  KEY due (status,next_attempt),
  KEY site_id (site_id)
) {$charset};"
		);

		$ok = self::exists();
		if ( $ok ) {
			update_option( self::OPTION, self::VERSION, false );
			update_option( self::VERIFIED, self::VERSION, false );
			$ok = self::ready();
		}
		if ( $ok ) {
			delete_transient( self::RETRY );
		} else {
			set_transient( self::RETRY, 1, HOUR_IN_SECONDS );
		}
		return $ok;
	}

	/**
	 * Checks usable columns and index semantics, not just table/index names.
	 *
	 * @return bool
	 */
	public static function exists() {
		$runs       = array(
			'id'            => 'bigint unsigned',
			'run_key'       => 'varchar(80)',
			'site_id'       => 'varchar(20)',
			'kind'          => 'varchar(12)',
			'slot'          => 'bigint',
			'period_start'  => 'char(10)',
			'period_end'    => 'char(10)',
			'compare_start' => 'char(10)',
			'compare_end'   => 'char(10)',
			'status'        => 'varchar(20)',
			'readiness'     => 'varchar(10)',
			'checks'        => 'longtext',
			'insights'      => 'longtext',
			'notes'         => 'text',
			'snapshot'      => 'longtext',
			'meta'          => 'longtext',
			'attempts'      => 'smallint unsigned',
			'next_check'    => 'bigint',
			'reviewed_by'   => 'bigint unsigned',
			'reviewed_at'   => 'bigint',
			'sent_at'       => 'bigint',
			'created'       => 'bigint',
			'updated'       => 'bigint',
		);
		$deliveries = array(
			'id'           => 'bigint unsigned',
			'run_id'       => 'bigint unsigned',
			'site_id'      => 'varchar(20)',
			'recipient'    => 'varchar(190)',
			'status'       => 'varchar(12)',
			'attempts'     => 'smallint unsigned',
			'last_attempt' => 'bigint',
			'next_attempt' => 'bigint',
			'error'        => 'text',
			'created'      => 'bigint',
			'updated'      => 'bigint',
		);
		return self::valid_table( self::runs(), $runs, array( array( array( 'run_key' ), true ), array( array( 'site_id', 'created' ), false ), array( array( 'status', 'next_check' ), false ) ) )
			&& self::valid_table( self::deliveries(), $deliveries, array( array( array( 'run_id', 'recipient' ), true ), array( array( 'status', 'next_attempt' ), false ), array( array( 'site_id' ), false ) ) );
	}

	/**
	 * Verifies a trusted table definition without depending on index names.
	 *
	 * @param string $table   Table from this class.
	 * @param array  $columns Required storage types.
	 * @param array  $indexes Required ordered columns and uniqueness.
	 * @return bool
	 */
	private static function valid_table( $table, array $columns, array $indexes ) {
		global $wpdb;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Avoid querying columns of an absent table during lifecycle checks.
		if ( $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $wpdb->esc_like( $table ) ) ) !== $table ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Setup postcondition; a cached result cannot verify DDL success.
		$found = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i', $table ), ARRAY_A );
		$found = is_array( $found ) ? array_column( $found, null, 'Field' ) : array();
		foreach ( $columns as $name => $type ) {
			$actual = isset( $found[ $name ]['Type'] ) ? strtolower( $found[ $name ]['Type'] ) : '';
			$actual = preg_replace( '/\b(bigint|smallint)\(\d+\)/', '$1', $actual );
			if ( $actual !== $type ) {
				return false;
			}
		}
		if ( false === strpos( strtolower( $found['id']['Extra'] ), 'auto_increment' ) ) {
			return false;
		}
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Verify full-column uniqueness and lookup indexes after setup.
		$rows    = $wpdb->get_results( $wpdb->prepare( 'SHOW INDEX FROM %i', $table ), ARRAY_A );
		$keys    = array();
		$partial = array();
		foreach ( is_array( $rows ) ? $rows : array() as $row ) {
			$name = $row['Key_name'];
			if ( null !== $row['Sub_part'] || null === $row['Column_name'] ) {
				$partial[ $name ] = true;
				continue;
			}
			$keys[ $name ]['columns'][ (int) $row['Seq_in_index'] ] = $row['Column_name'];
			$keys[ $name ]['unique']                                = 0 === (int) $row['Non_unique'];
		}
		$keys = array_diff_key( $keys, $partial );
		foreach ( $keys as &$key ) {
			ksort( $key['columns'] );
			$key['columns'] = array_values( $key['columns'] );
		}
		unset( $key );
		if ( ! isset( $keys['PRIMARY'] ) || array( 'id' ) !== $keys['PRIMARY']['columns'] || ! $keys['PRIMARY']['unique'] ) {
			return false;
		}
		foreach ( $indexes as list( $names, $unique ) ) {
			$matched = false;
			foreach ( $keys as $key ) {
				if ( $unique ? $key['unique'] && $key['columns'] === $names : array_slice( $key['columns'], 0, count( $names ) ) === $names ) {
					$matched = true;
					break;
				}
			}
			if ( ! $matched ) {
				return false;
			}
		}
		return true;
	}

	/**
	 * Drops both tables (uninstall).
	 *
	 * @return void
	 */
	public static function drop() {
		global $wpdb;
		foreach ( array( self::runs(), self::deliveries() ) as $table ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Uninstall; table name from $wpdb->prefix.
			$wpdb->query( "DROP TABLE IF EXISTS {$table}" );
		}
		delete_option( self::OPTION );
		delete_option( self::VERIFIED );
	}
}