<?php

namespace wpsolr\core\classes\engines\postgresql;

use PgSql\Connection;

class WPSOLR_PostgreSQL_Connection {
	protected Connection|false $_connection;

	public function __construct( string $host, string $port, string $dbname, string $user, string $password ) {
		$this->_connection = pg_connect(
			sprintf( 'host=%s port=%s dbname=%s user=%s password=%s',
				$host, $port, $dbname, $user, $password
			)
		);
	}

	private function _throw_last_error( string $error_pattern ) {
		throw new \Exception( esc_html( sprintf( $error_pattern, pg_last_error( $this->_connection ) ) ) );
	}

	function execute_statement_as_array( string $sql, array $params = [] ): array {
		$results = $this->execute_statement( $sql, $params );

		// Return an array
		$rows = [];
		while ( $row = pg_fetch_assoc( $results ) ) {
			$rows[] = $row;
		}
		pg_free_result( $results );

		return $rows;
	}

	function execute_statement( string $sql, array $params = [] ): \PgSql\Result {
		$this->_check_connection();

		set_error_handler( function ( $severity, $message, $file, $line ) {
			throw new \ErrorException( $message, 0, $severity, $file, $line );
		} );

		$results = pg_query_params( $this->_connection, $sql, $params );

		restore_error_handler();

		if ( ! $results ) {
			$this->_throw_last_error( 'Query failed: %s' );
		}

		return $results;
	}

	function _expand_query_for_log( string $sql, array $params ): string {
		foreach ( $params as $i => $param ) {
			$place_holder = '$' . ( $i + 1 );

			if ( $param === null ) {
				$value = 'NULL';
			} elseif ( is_numeric( $param ) ) {
				$value = $param;
			} elseif ( is_bool( $param ) ) {
				$value = $param ? 'TRUE' : 'FALSE';
			} else {
				$value = "'" . pg_escape_string( $this->_connection, $param ) . "'";
			}

			$sql = preg_replace( '/\\' . $place_holder . '\b/', $value, $sql );
		}

		return $sql;
	}

	public function get_create_database_sql( string $database_name ): string {
		return sprintf( "CREATE DATABASE %s", $this->escape_identifier( $database_name ) );
	}

	public function get_disconnect_database_sql( string $database_name ): string {
		return sprintf( "
        SELECT pg_terminate_backend(pid)
        FROM pg_stat_activity
        WHERE datname = %s AND pid <> pg_backend_pid()
        ", $this->escape_literal( $database_name ) );
	}

	public function get_drop_schema_sql( string $schema_name ): string {
		return sprintf( "DROP SCHEMA IF EXISTS %s CASCADE", $this->escape_identifier( $schema_name ) );
	}

	public function get_delete_vectorizer_sql( string $schema_name ): string {
		return sprintf( /** @lang PostgreSQL */ "
						SELECT ai.drop_vectorizer(id, drop_all => true)
						FROM ai.vectorizer
						WHERE source_table = '%s';
						", $schema_name );
	}

	public function get_create_schema_sql( string $schema_name ): string {
		return sprintf( "CREATE SCHEMA IF NOT EXISTS %s;", $this->escape_identifier( $schema_name ) );
	}


	public function escape_identifier( string $identifier ): string {
		$this->_check_connection();

		return pg_escape_identifier( $this->_connection, $identifier );
	}

	/**
	 * @return void
	 * @throws \Exception
	 */
	protected function _check_connection(): void {
		if ( ! $this->_connection ) {
			throw new \Exception( "Connection failed. Check host, user, password, or port." );
		}
	}

	public function escape_literal( string $literal ): string {
		return pg_escape_literal( $this->_connection, $literal );
	}

	public function escape_string( string $literal ): string {
		return pg_escape_string( $this->_connection, $literal );
	}

}
