<?php
/**
 * Reads table structure: tables, columns, the key used for paging, CREATE statements, triggers.
 *
 * @package AccessNow\Replica
 */

namespace AccessNow\Replica\Db;

defined( 'ABSPATH' ) || exit;

/**
 * Schema inspection.
 */
final class Schema_Reader {

	/**
	 * Connection.
	 *
	 * @var Connection
	 */
	private $db;

	/**
	 * Creates the reader.
	 *
	 * @param Connection $db Connection.
	 */
	public function __construct( Connection $db ) {
		$this->db = $db;
	}

	/**
	 * Table names and types (BASE TABLE / VIEW), optionally limited to a prefix.
	 *
	 * @param string $prefix Prefix ('' for all).
	 * @return array<string, string>
	 */
	public function tables( $prefix = '' ) {
		$all = array();
		foreach ( $this->db->rows( 'SHOW FULL TABLES' ) as $row ) {
			$row                    = array_values( $row );
			$all[ (string) $row[0] ] = strtoupper( (string) $row[1] );
		}
		$foreign = '' === $prefix ? array() : self::foreign_prefixes( array_keys( $all ), $prefix );
		$out     = array();
		foreach ( $all as $name => $type ) {
			if ( '' === $prefix || self::belongs_to( $name, $prefix, $foreign ) ) {
				$out[ $name ] = $type;
			}
		}
		ksort( $out, SORT_STRING );
		return $out;
	}

	/**
	 * Longer prefixes that belong to other WordPress installs in the same database.
	 *
	 * With the prefix "wp_", a second install using "wp_dev_" has tables that start with "wp_" too.
	 * A longer prefix counts as another install when it has both an options and a users table of
	 * its own. Multisite subsites ("wp_2_") share the network's users table, so they stay.
	 *
	 * @param string[] $names  Every table name in the database.
	 * @param string   $prefix This site's prefix.
	 * @return string[]
	 */
	public static function foreign_prefixes( array $names, $prefix ) {
		$set = array_flip( $names );
		$out = array();
		foreach ( $names as $name ) {
			if ( 0 !== strpos( $name, $prefix ) || 'options' !== substr( $name, -7 ) ) {
				continue;
			}
			$candidate = substr( $name, 0, -7 );
			if ( strlen( $candidate ) > strlen( $prefix ) && isset( $set[ $candidate . 'users' ] ) ) {
				$out[] = $candidate;
			}
		}
		return $out;
	}

	/**
	 * Whether a table is this site's: it has the prefix and no other install's longer one.
	 *
	 * @param string   $name    Table.
	 * @param string   $prefix  This site's prefix.
	 * @param string[] $foreign From foreign_prefixes().
	 */
	public static function belongs_to( $name, $prefix, array $foreign ) {
		if ( 0 !== strpos( $name, $prefix ) ) {
			return false;
		}
		foreach ( $foreign as $other ) {
			if ( 0 === strpos( $name, $other ) ) {
				return false;
			}
		}
		return true;
	}

	/**
	 * Table status (engine, rows estimate, sizes, collation) keyed by name.
	 *
	 * @return array<string, array>
	 */
	public function statuses() {
		$out = array();
		foreach ( $this->db->rows( 'SHOW TABLE STATUS' ) as $row ) {
			$out[ $row['Name'] ] = array(
				'engine'       => (string) $row['Engine'],
				'rows_est'     => (int) $row['Rows'],
				'avg_row'      => (int) $row['Avg_row_length'],
				'data_length'  => (int) $row['Data_length'],
				'index_length' => (int) $row['Index_length'],
				'collation'    => (string) $row['Collation'],
			);
		}
		return $out;
	}

	/**
	 * Columns with the flags the exporter needs.
	 *
	 * @param string $table Table.
	 * @return array[]
	 */
	public function columns( $table ) {
		$out = array();
		foreach ( $this->db->rows( 'SHOW FULL COLUMNS FROM %i', $table ) as $row ) {
			$type  = strtolower( (string) $row['Type'] );
			$extra = strtoupper( (string) $row['Extra'] );
			$out[] = array(
				'name'      => (string) $row['Field'],
				'type'      => $type,
				'null'      => 'YES' === $row['Null'],
				// Generated columns cannot be inserted. MySQL 8 reports "DEFAULT_GENERATED" for
				// expression defaults, which are ordinary columns, so match the keywords exactly.
				'generated' => (bool) preg_match( '/\b(VIRTUAL|STORED|PERSISTENT)\b/', $extra ),
				'binary'    => (bool) preg_match( '/^(binary|varbinary|tinyblob|blob|mediumblob|longblob|geometry|point|linestring|polygon|multipoint|multilinestring|multipolygon|geometrycollection|geomcollection)\b/', $type ),
				// BIT values arrive from mysqli as decimal text, so they are written as numbers.
				'numeric'   => (bool) preg_match( '/^(tinyint|smallint|mediumint|int|integer|bigint|decimal|numeric|float|double|real|year|bit)\b/', $type ),
				'float'     => (bool) preg_match( '/^(float|double|real)\b/', $type ),
				// Columns that can hold values too large to export inline.
				'large'     => (bool) preg_match( '/^(text|mediumtext|longtext|blob|mediumblob|longblob|json)\b/', $type ),
			);
		}
		return $out;
	}

	/**
	 * Picks the key used for paging: the primary key, else a unique key on NOT NULL columns.
	 *
	 * @param string $table   Table.
	 * @param array  $columns Columns from columns().
	 * @return array{kind: string, columns: string[]}
	 */
	public function paging_key( $table, array $columns ) {
		$nullable = array();
		foreach ( $columns as $column ) {
			$nullable[ $column['name'] ] = $column['null'];
		}
		$indexes = array();
		foreach ( $this->db->rows( 'SHOW INDEX FROM %i', $table ) as $row ) {
			if ( '0' !== (string) $row['Non_unique'] ) {
				continue;
			}
			$indexes[ $row['Key_name'] ][ (int) $row['Seq_in_index'] ] = (string) $row['Column_name'];
		}
		if ( isset( $indexes['PRIMARY'] ) ) {
			ksort( $indexes['PRIMARY'] );
			return array(
				'kind'    => 'primary',
				'columns' => array_values( $indexes['PRIMARY'] ),
			);
		}
		foreach ( $indexes as $cols ) {
			ksort( $cols );
			$ok = true;
			foreach ( $cols as $col ) {
				if ( ! empty( $nullable[ $col ] ) ) {
					$ok = false;
					break;
				}
			}
			if ( $ok ) {
				return array(
					'kind'    => 'unique',
					'columns' => array_values( $cols ),
				);
			}
		}
		return array(
			'kind'    => 'none',
			'columns' => array(),
		);
	}

	/**
	 * CREATE TABLE or CREATE VIEW statement.
	 *
	 * @param string $table Table.
	 * @param string $type  BASE TABLE or VIEW.
	 */
	public function create_sql( $table, $type ) {
		$is_view = 'VIEW' === $type;
		$rows    = $this->db->rows( $is_view ? 'SHOW CREATE VIEW %i' : 'SHOW CREATE TABLE %i', $table );
		$row     = isset( $rows[0] ) ? array_values( $rows[0] ) : array();
		$sql     = isset( $row[1] ) ? (string) $row[1] : '';
		if ( $is_view ) {
			// Definers name users that won't exist elsewhere.
			$sql = (string) preg_replace( '/\sDEFINER\s*=\s*(`[^`]*`|\S+)@(`[^`]*`|\S+)/i', '', $sql );
			$sql = (string) preg_replace( '/\sSQL SECURITY DEFINER/i', ' SQL SECURITY INVOKER', $sql );
		}
		return $sql;
	}

	/**
	 * Triggers on tables with a prefix.
	 *
	 * @param string $prefix Prefix.
	 * @return array[]
	 */
	public function triggers( $prefix ) {
		$out = array();
		try {
			$rows = $this->db->rows( 'SHOW TRIGGERS' );
		} catch ( Db_Exception $e ) {
			return $out;
		}
		$own = '' === $prefix ? array() : $this->tables( $prefix );
		foreach ( $rows as $row ) {
			if ( '' !== $prefix && ! isset( $own[ (string) $row['Table'] ] ) ) {
				continue;
			}
			$create = $this->db->rows( 'SHOW CREATE TRIGGER %i', $row['Trigger'] );
			$sql    = isset( $create[0]['SQL Original Statement'] ) ? (string) $create[0]['SQL Original Statement'] : '';
			$out[]  = array(
				'name'  => (string) $row['Trigger'],
				'table' => (string) $row['Table'],
				'sql'   => (string) preg_replace( '/\sDEFINER\s*=\s*(`[^`]*`|\S+)@(`[^`]*`|\S+)/i', '', $sql ),
			);
		}
		return $out;
	}
}