<?php
/**
 * Thin, fast database layer on top of the wpdb connection.
 *
 * Uses the underlying mysqli handle directly for speed (no result caching,
 * no query logging) and converts every MySQL error to plain English.
 *
 * @package Spinix_Migration
 */

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class Spinix_DB {

	/**
	 * mysqli handle or null when a custom db driver is in use.
	 *
	 * @return mysqli|null
	 */
	public static function dbh() {
		global $wpdb;
		if ( isset( $wpdb->dbh ) && $wpdb->dbh instanceof mysqli ) {
			return $wpdb->dbh;
		}
		return null;
	}

	/**
	 * Make sure the connection is alive.
	 */
	public static function ping() {
		global $wpdb;
		$dbh = self::dbh();
		if ( $dbh ) {
			try {
				if ( @$dbh->ping() ) { // phpcs:ignore
					return;
				}
			} catch ( Throwable $e ) { // phpcs:ignore
				// Fall through to reconnect.
			}
		}
		if ( method_exists( $wpdb, 'check_connection' ) ) {
			$wpdb->check_connection( false );
		}
	}

	/**
	 * Run a statement.
	 *
	 * @param string $sql SQL.
	 * @return int Affected rows.
	 * @throws Spinix_Error On error.
	 */
	public static function exec( $sql ) {
		global $wpdb;
		$dbh = self::dbh();
		if ( $dbh ) {
			$res = self::raw( $dbh, $sql );
			if ( false === $res ) {
				$errno = $dbh->errno;
				if ( 2006 === $errno || 2013 === $errno ) {
					self::ping();
					$dbh = self::dbh();
					$res = self::raw( $dbh, $sql );
					if ( false !== $res ) {
						return (int) $dbh->affected_rows;
					}
				}
				throw Spinix_Error::from_mysql( esc_html( $dbh->errno ), esc_html( $dbh->error ), esc_html( $sql ) );
			}
			if ( $res instanceof mysqli_result ) {
				$res->free();
			}
			return (int) $dbh->affected_rows;
		}
		$suppress = $wpdb->suppress_errors( true );
		$r        = $wpdb->query( $sql ); // phpcs:ignore
		$wpdb->suppress_errors( $suppress );
		if ( false === $r ) {
			throw Spinix_Error::from_mysql( 0, esc_html( $wpdb->last_error ), esc_html( $sql ) );
		}
		return (int) $r;
	}

	/**
	 * mysqli_query that never throws (PHP 8.1+ defaults to exceptions).
	 *
	 * @param mysqli $dbh Handle.
	 * @param string $sql SQL.
	 * @return mysqli_result|bool
	 */
	private static function raw( $dbh, $sql ) {
		try {
			return $dbh->query( $sql );
		} catch ( mysqli_sql_exception $e ) {
			return false;
		}
	}

	/**
	 * Fetch rows as numeric arrays.
	 *
	 * @param string $sql SQL.
	 * @return array
	 * @throws Spinix_Error On error.
	 */
	public static function rows( $sql ) {
		return self::fetch( $sql, false );
	}

	/**
	 * Fetch rows as associative arrays.
	 *
	 * @param string $sql SQL.
	 * @return array
	 * @throws Spinix_Error On error.
	 */
	public static function assoc( $sql ) {
		return self::fetch( $sql, true );
	}

	/**
	 * Fetch helper.
	 *
	 * @param string $sql   SQL.
	 * @param bool   $assoc Assoc.
	 * @return array
	 * @throws Spinix_Error On error.
	 */
	private static function fetch( $sql, $assoc ) {
		global $wpdb;
		$dbh = self::dbh();
		if ( $dbh ) {
			$res = self::raw( $dbh, $sql );
			if ( false === $res && ( 2006 === $dbh->errno || 2013 === $dbh->errno ) ) {
				self::ping();
				$dbh = self::dbh();
				$res = self::raw( $dbh, $sql );
			}
			if ( false === $res ) {
				throw Spinix_Error::from_mysql( esc_html( $dbh->errno ), esc_html( $dbh->error ), esc_html( $sql ) );
			}
			if ( true === $res ) {
				return array();
			}
			$out = array();
			if ( $assoc ) {
				while ( $row = $res->fetch_assoc() ) { // phpcs:ignore
					$out[] = $row;
				}
			} else {
				while ( $row = $res->fetch_row() ) { // phpcs:ignore
					$out[] = $row;
				}
			}
			$res->free();
			return $out;
		}
		$suppress = $wpdb->suppress_errors( true );
		$r        = $wpdb->get_results( $sql, $assoc ? ARRAY_A : ARRAY_N ); // phpcs:ignore
		$wpdb->suppress_errors( $suppress );
		if ( $wpdb->last_error ) {
			throw Spinix_Error::from_mysql( 0, esc_html( $wpdb->last_error ), esc_html( $sql ) );
		}
		return is_array( $r ) ? $r : array();
	}

	/**
	 * Single value.
	 *
	 * @param string $sql SQL.
	 * @return string|null
	 */
	public static function value( $sql ) {
		$rows = self::rows( $sql );
		return isset( $rows[0][0] ) ? $rows[0][0] : null;
	}

	/**
	 * Escape a string for use inside single quotes.
	 *
	 * @param string $s String.
	 * @return string
	 */
	public static function esc( $s ) {
		global $wpdb;
		$dbh = self::dbh();
		if ( $dbh ) {
			return $dbh->real_escape_string( (string) $s );
		}
		return $wpdb->_real_escape( (string) $s );
	}

	/**
	 * Quote an identifier.
	 *
	 * @param string $name Name.
	 * @return string
	 */
	public static function ident( $name ) {
		return '`' . str_replace( '`', '``', $name ) . '`';
	}

	/**
	 * Escape a value for LIKE '%...%'.
	 *
	 * @param string $s String.
	 * @return string
	 */
	public static function like( $s ) {
		return self::esc( addcslashes( $s, '_%\\' ) );
	}

	/**
	 * Server version string.
	 *
	 * @return string
	 */
	public static function server_version() {
		try {
			return (string) self::value( 'SELECT VERSION()' );
		} catch ( Spinix_Error $e ) {
			return '';
		}
	}

	/**
	 * Base tables that belong to this WordPress install (by prefix).
	 *
	 * @param string $prefix Prefix.
	 * @return array
	 */
	public static function tables_with_prefix( $prefix ) {
		$rows = self::rows( "SHOW FULL TABLES LIKE '" . self::like( $prefix ) . "%'" );
		$out  = array();
		foreach ( $rows as $r ) {
			if ( isset( $r[1] ) && 'BASE TABLE' !== strtoupper( $r[1] ) ) {
				continue;
			}
			$out[] = $r[0];
		}
		return $out;
	}

	/**
	 * Column info.
	 *
	 * @param string $table Table.
	 * @return array [ [name, type, key, extra], ... ]
	 */
	public static function columns( $table ) {
		$rows = self::assoc( 'SHOW COLUMNS FROM ' . self::ident( $table ) );
		$out  = array();
		foreach ( $rows as $r ) {
			$out[] = array(
				'name'  => $r['Field'],
				'type'  => strtolower( $r['Type'] ),
				'key'   => $r['Key'],
				'extra' => strtolower( (string) $r['Extra'] ),
			);
		}
		return $out;
	}

	/**
	 * Primary key columns of a table.
	 *
	 * @param string $table Table.
	 * @return array
	 */
	public static function primary_key( $table ) {
		$rows = self::assoc( 'SHOW KEYS FROM ' . self::ident( $table ) . " WHERE Key_name = 'PRIMARY'" );
		usort(
			$rows,
			function ( $a, $b ) {
				return (int) $a['Seq_in_index'] - (int) $b['Seq_in_index'];
			}
		);
		$out = array();
		foreach ( $rows as $r ) {
			$out[] = $r['Column_name'];
		}
		return $out;
	}

	/**
	 * Use relaxed SQL mode for import (zero dates etc. from older servers).
	 */
	public static function relaxed_session() {
		try {
			self::exec( "SET SESSION sql_mode = 'NO_AUTO_VALUE_ON_ZERO'" );
		} catch ( Spinix_Error $e ) { // phpcs:ignore
			// Not fatal.
		}
		try {
			self::exec( 'SET SESSION foreign_key_checks = 0' );
			self::exec( 'SET SESSION unique_checks = 0' );
		} catch ( Spinix_Error $e ) { // phpcs:ignore
			// Not fatal.
		}
	}

	/**
	 * max_allowed_packet in bytes.
	 *
	 * @return int
	 */
	public static function max_packet() {
		try {
			$v = (int) self::value( 'SELECT @@max_allowed_packet' );
			return $v > 0 ? $v : 1048576;
		} catch ( Spinix_Error $e ) {
			return 1048576;
		}
	}
}