<?php

namespace RoosterLMS\Data;

use RoosterLMS\Core\Schema;

defined( 'ABSPATH' ) || exit;

final class Db {

	private const UNKNOWN_VARIABLE = 1193;

	private static ?string $delta_prefix = null;

	public static function table( string $short ): string {
		return Schema::table( $short );
	}

	public static function query( string|SqlPart $query, ...$args ): int {
		global $wpdb;
		self::run( '', $query, $args );
		return (int) $wpdb->rows_affected;
	}

	public static function delta( string|SqlPart $query, ...$args ): int {
		global $wpdb;
		$prefix = self::delta_prefix();
		if ( '' === $prefix ) {
			return self::query( $query, ...$args );
		}
		try {
			self::run( $prefix, $query, $args );
			return (int) $wpdb->rows_affected;
		} catch ( DbError $e ) {
			if ( self::UNKNOWN_VARIABLE !== (int) $e->errno ) {
				throw $e;
			}
			self::$delta_prefix = '';
			return self::query( $query, ...$args );
		}
	}

	public static function delta_rows( string|SqlPart $query, ...$args ): array {
		$prefix = self::delta_prefix();
		if ( '' === $prefix ) {
			return self::rows( $query, ...$args );
		}
		try {
			self::run( $prefix, $query, $args );
			return self::last_rows();
		} catch ( DbError $e ) {
			if ( self::UNKNOWN_VARIABLE !== (int) $e->errno ) {
				throw $e;
			}
			self::$delta_prefix = '';
			return self::rows( $query, ...$args );
		}
	}

	public static function delta_prefix(): string {
		if ( null === self::$delta_prefix ) {
			global $wpdb;
			$info               = is_object( $wpdb ) && method_exists( $wpdb, 'db_server_info' ) ? (string) $wpdb->db_server_info() : '';
			self::$delta_prefix = self::has_snapshot_isolation( $info ) ? 'SET STATEMENT innodb_snapshot_isolation=OFF FOR ' : '';
		}
		return self::$delta_prefix;
	}

	public static function has_snapshot_isolation( string $info ): bool {
		if ( 1 !== preg_match( '/(\d+)\.(\d+)\.(\d+)-MariaDB/i', $info, $m ) ) {
			return false;
		}
		$version = (int) $m[1] . '.' . (int) $m[2] . '.' . (int) $m[3];
		$series  = (int) $m[1] . '.' . (int) $m[2];
		$first   = array(
			'10.6'  => '10.6.18',
			'10.11' => '10.11.8',
			'11.0'  => '11.0.6',
			'11.1'  => '11.1.5',
			'11.2'  => '11.2.4',
			'11.4'  => '11.4.2',
		);
		if ( isset( $first[ $series ] ) ) {
			return version_compare( $version, $first[ $series ], '>=' );
		}
		return version_compare( $series, '11.5', '>=' );
	}

	public static function insert_id(): int {
		global $wpdb;
		return (int) $wpdb->insert_id;
	}

	public static function rows( string|SqlPart $query, ...$args ): array {
		self::run( '', $query, $args );
		return self::last_rows();
	}

	public static function row( string|SqlPart $query, ...$args ): ?array {
		$rows = self::rows( $query, ...$args );
		return $rows[0] ?? null;
	}

	public static function col( string|SqlPart $query, ...$args ): array {
		$out = array();
		foreach ( self::rows( $query, ...$args ) as $row ) {
			$out[] = null === reset( $row ) ? null : (string) reset( $row );
		}
		return $out;
	}

	public static function value( string|SqlPart $query, ...$args ): ?string {
		$row = self::row( $query, ...$args );
		if ( null === $row ) {
			return null;
		}
		$first = reset( $row );
		return null === $first ? null : (string) $first;
	}

	private static function last_rows(): array {
		global $wpdb;
		$rows = array();
		foreach ( (array) $wpdb->last_result as $row ) {
			$rows[] = get_object_vars( $row );
		}
		return $rows;
	}

	public static function where_in( string $column, array $ids ): string {
		global $wpdb;
		$ids = array_values( array_map( 'intval', $ids ) );
		if ( array() === $ids ) {
			return '';
		}
		$parts = explode( '.', $column, 2 );
		$ident = 2 === count( $parts ) ? '%i.%i' : '%i';
		$marks = 1 === count( $ids ) ? ' = %d' : ' IN (' . implode( ',', array_fill( 0, count( $ids ), '%d' ) ) . ')';
		return $wpdb->prepare( ' AND ' . $ident . $marks, array_merge( $parts, $ids ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- fixed placeholders: %i per name part, one %d per value.
	}

	public static function prepared( SqlPart $part ): string {
		global $wpdb;
		if ( array() === $part->args() ) {
			return $part->text();
		}
		return (string) $wpdb->prepare( $part->text(), ...$part->args() ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared -- prepared here; the text is a literal of the caller's code (SourceRulesTest).
	}

	private static function run( string $prefix, string|SqlPart $query, array $args ): void {
		global $wpdb;
		if ( $query instanceof SqlPart ) {
			if ( array() !== $args && array() !== $query->args() ) {
				throw new \LogicException( 'Db: values beside a part that has values of its own.' );
			}
			$args  = array() === $args ? $query->args() : $args;
			$query = $query->text();
		}
		$query = $prefix . $query;
		if ( Transaction::is_doomed() ) {
			$error = new DbError( 'doomed', 1213, self::short_table( $query . ' ' . implode( ' ', array_filter( $args, 'is_string' ) ) ), self::verb( $query ) );
			throw $error;
		}
		Transaction::assert_connection();
		Transaction::note_statement();
		$suppress = $wpdb->suppress_errors( true );
		try {
			if ( array() === $args ) {
				$result = $wpdb->query( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery, PluginCheck.Security.DirectDB.UnescapedDBParameter -- a statement without values; SourceRulesTest checks the call sites.
			} else {
				$result = $wpdb->query( $wpdb->prepare( $query, ...$args ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery -- prepared here; the text is a literal of the caller's code (SourceRulesTest).
			}
		} finally {
			$wpdb->suppress_errors( $suppress );
		}
		Transaction::assert_connection();
		if ( false === $result ) {
			$errno = self::errno();
			Transaction::note_error( $errno );
			$sent  = (string) $wpdb->last_query;
			$error = new DbError( DbError::sanitize_detail( (string) $wpdb->last_error ), $errno, self::short_table( $sent ), self::verb( $sent ) );
			throw $error;
		}
	}

	public static function errno(): int {
		global $wpdb;
		return DbError::errno_of( (string) $wpdb->last_error );
	}

	private static function short_table( string $sql ): string {
		global $wpdb;
		$prefix = preg_quote( $wpdb->prefix . 'rooster_lms_', '/' );
		return 1 === preg_match( '/' . $prefix . '([a-z_]+)/', $sql, $m ) ? $m[1] : '';
	}

	private static function verb( string $sql ): string {
		$sql = (string) preg_replace( '/^\s*SET\s+STATEMENT\s+.*?\s+FOR\s+\(?/is', '', $sql );
		return 1 === preg_match( '/^\s*(?:\/\*.*?\*\/\s*)?([a-zA-Z]+)/s', $sql, $m ) ? strtoupper( $m[1] ) : '';
	}
}