<?php
/**
 * Custom database manager.
 *
 * @package AISupportAssistant\Database
 */

// phpcs:disable WordPress.DB.DirectDatabaseQuery

namespace AISupportAssistant\Database;

use AISupportAssistant\Helpers\Logger;

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

/**
 * Class DatabaseManager
 */
class DatabaseManager {

	/**
	 * Get the document table name.
	 *
	 * @return string
	 */
	public static function get_documents_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'ai_support_assistant_documents';
	}

	/**
	 * Get the chunks table name.
	 *
	 * @return string
	 */
	public static function get_chunks_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'ai_support_assistant_chunks';
	}

	/**
	 * Get the chat history table name.
	 *
	 * @return string
	 */
	public static function get_chat_history_table(): string {
		global $wpdb;
		return $wpdb->prefix . 'ai_support_assistant_chat_history';
	}

	/**
	 * Creation of tables during activation hook.
	 *
	 * @return bool Whether the database schema is ready for document indexing.
	 */
	public static function activate(): bool {
		global $wpdb;

		$charset_collate = $wpdb->get_charset_collate();

		$doc_table   = self::get_documents_table();
		$chunk_table = self::get_chunks_table();
		$chat_table  = self::get_chat_history_table();

		$sql = "CREATE TABLE $doc_table (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			title TEXT NOT NULL,
			filename VARCHAR(255) NOT NULL,
			mime_type VARCHAR(100) NOT NULL,
			file_path VARCHAR(255) NOT NULL,
			status VARCHAR(50) NOT NULL DEFAULT 'pending',
			error_message TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NOT NULL,
			PRIMARY KEY (id),
			KEY status_idx (status)
		) $charset_collate;

		CREATE TABLE $chunk_table (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			document_id BIGINT(20) UNSIGNED NOT NULL,
			chunk_number INT(11) NOT NULL,
			page_number INT(11) NULL,
			chunk_text LONGTEXT NOT NULL,
			embedding LONGTEXT NOT NULL,
			embedding_model VARCHAR(191) NOT NULL DEFAULT '',
			token_count INT(11) NOT NULL,
			created_at DATETIME NOT NULL,
			PRIMARY KEY (id),
			KEY document_id_idx (document_id),
			KEY embedding_model_idx (embedding_model, id)
		) ENGINE=InnoDB $charset_collate;

		CREATE TABLE $chat_table (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			session_id VARCHAR(100) NOT NULL,
			question TEXT NOT NULL,
			answer TEXT NOT NULL,
			messages LONGTEXT NULL,
			provider VARCHAR(50) NOT NULL,
			model VARCHAR(50) NOT NULL,
			status VARCHAR(20) NOT NULL DEFAULT 'completed',
			error_message TEXT NULL,
			created_at DATETIME NOT NULL,
			updated_at DATETIME NULL,
			PRIMARY KEY (id),
			KEY session_id_idx (session_id)
		) $charset_collate;";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );

		self::maybe_upgrade_chat_table();
		self::ensure_chunk_uniqueness();
		return self::ensure_chunks_table_engine();
	}

	/** Ensure concurrent indexing cannot create duplicate chunk rows. */
	private static function ensure_chunk_uniqueness(): void {
		global $wpdb;
		$table = self::get_chunks_table();
		// Remove only duplicate rows, retaining the oldest row for each chunk identity.
		$wpdb->query( $wpdb->prepare( 'DELETE c1 FROM %i c1 INNER JOIN %i c2 ON c1.document_id = c2.document_id AND c1.chunk_number = c2.chunk_number AND c1.id > c2.id', $table, $table ) );
		$has_index = $wpdb->get_var( $wpdb->prepare( 'SHOW INDEX FROM %i WHERE Key_name = %s', $table, 'document_chunk_unique' ) );
		if ( ! $has_index ) {
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD UNIQUE KEY document_chunk_unique (document_id, chunk_number)', $table ) );
		}
		if ( ! empty( $wpdb->last_error ) && false === strpos( strtolower( $wpdb->last_error ), 'duplicate key name' ) ) {
			Logger::warning( 'Unable to add the document chunk uniqueness constraint.', array( 'error' => $wpdb->last_error ) );
		}
	}

	/**
	 * Ensure the chunks table uses InnoDB for atomic re-indexing.
	 *
	 * @return bool Whether the chunks table is ready for transactions.
	 */
	public static function ensure_chunks_table_engine(): bool {
		global $wpdb;

		$chunks_table = self::get_chunks_table();
		$table_status = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS WHERE Name = %s', $chunks_table ) );
		if ( ! $table_status || empty( $table_status->Engine ) ) {
			Logger::error( 'Unable to verify the chunks table storage engine.', array( 'table' => $chunks_table ) );
			return false;
		}

		if ( 'innodb' === strtolower( (string) $table_status->Engine ) ) {
			return true;
		}

		$result = $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ENGINE=InnoDB', $chunks_table ) );
		if ( false === $result ) {
			Logger::error(
				'Unable to convert the chunks table to InnoDB.',
				array(
					'table' => $chunks_table,
					'error' => $wpdb->last_error,
				)
			);
			return false;
		}

		$table_status = $wpdb->get_row( $wpdb->prepare( 'SHOW TABLE STATUS WHERE Name = %s', $chunks_table ) );
		if ( ! $table_status || 'innodb' !== strtolower( (string) $table_status->Engine ) ) {
			Logger::error( 'The chunks table did not switch to InnoDB after conversion.', array( 'table' => $chunks_table ) );
			return false;
		}

		Logger::info( 'Converted the chunks table to InnoDB for safe document re-indexing.', array( 'table' => $chunks_table ) );
		return true;
	}

	/**
	 * Ensure chat table columns are present on runtime.
	 *
	 * @return void
	 */
	public static function maybe_upgrade_chat_table(): void {
		global $wpdb;
		$chat_table   = self::get_chat_history_table();
		$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $chat_table ) );
		if ( ! $table_exists ) {
			return;
		}

		$row = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $chat_table, 'messages' ) );
		if ( empty( $row ) ) {
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN messages LONGTEXT NULL AFTER answer', $chat_table ) );
		}

		$row_updated = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $chat_table, 'updated_at' ) );
		if ( empty( $row_updated ) ) {
			$wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN updated_at DATETIME NULL AFTER created_at', $chat_table ) );
		}

		$row_status = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $chat_table, 'status' ) );
		if ( empty( $row_status ) && false === $wpdb->query( $wpdb->prepare( "ALTER TABLE %i ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'completed' AFTER model", $chat_table ) ) ) {
			Logger::error(
				'Unable to add chat history status column.',
				array(
					'table' => $chat_table,
					'error' => $wpdb->last_error,
				)
			);
		}

		$row_error = $wpdb->get_results( $wpdb->prepare( 'SHOW COLUMNS FROM %i LIKE %s', $chat_table, 'error_message' ) );
		if ( empty( $row_error ) && false === $wpdb->query( $wpdb->prepare( 'ALTER TABLE %i ADD COLUMN error_message TEXT NULL AFTER status', $chat_table ) ) ) {
			Logger::error(
				'Unable to add chat history error details column.',
				array(
					'table' => $chat_table,
					'error' => $wpdb->last_error,
				)
			);
		}
	}

	/**
	 * Runs on deactivation.
	 *
	 * @return void
	 */
	public static function deactivate(): void {
		delete_transient( 'ai_support_assistant_statistics' );
	}

	/**
	 * Drop all custom tables on uninstall.
	 *
	 * @return void
	 */
	public static function drop_tables(): void {
		global $wpdb;

		$doc_table   = self::get_documents_table();
		$chunk_table = self::get_chunks_table();
		$chat_table  = self::get_chat_history_table();

		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $chunk_table ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $doc_table ) );
		$wpdb->query( $wpdb->prepare( 'DROP TABLE IF EXISTS %i', $chat_table ) );
	}
}