<?php

if ( ! defined( 'ABSPATH' ) ) exit; // Exit if accessed directly

use Adventurebook\Item;
use Adventurebook\Item_Definition;

function create_tables(): void {
	create_characters_table();
	create_item_definitions_table();
	create_item_spawns_table();
	create_items_table();
	create_enemy_definitions_table();
	create_enemy_spawns_table();
	create_enemies_table();
	create_scripts_table();
}

function create_scripts_table(): void
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'adventurebook_scripts'; // Tabelle prefix + name

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
		id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		code VARCHAR(255) NOT NULL,
		name VARCHAR(255) NOT NULL,
		commands JSON
	) $charset_collate;";

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

function create_enemy_definitions_table(): void
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'adventurebook_enemy_definitions'; // Tabelle prefix + name

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
		id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		code VARCHAR(255) NOT NULL,
		name VARCHAR(255) NOT NULL,
    	is_hostile TINYINT(1) NOT NULL DEFAULT 1,
		can_be_attacked TINYINT(1) NOT NULL DEFAULT 1,
    	xp_value INT(11) NOT NULL DEFAULT 0,
		description TEXT,
		stats JSON,
    	inventory JSON,
		image_url VARCHAR(255)
	) $charset_collate;";

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

function create_enemy_spawns_table(): void
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'adventurebook_enemy_spawns'; // Tabelle prefix + name

	$charset_collate = $wpdb->get_charset_collate();

	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
		id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		code VARCHAR(255) NOT NULL,
		page_id INT(11) NOT NULL DEFAULT 0,
    	respawn_time INT(11) NOT NULL DEFAULT 0,
    	last_defeated TIMESTAMP NULL DEFAULT NULL,
    	character_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
        parent_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
		alternate_page_ids VARCHAR(255) DEFAULT NULL
	) $charset_collate;";

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

function create_enemies_table(): void
{
	global $wpdb;
	$table_name = $wpdb->prefix . 'adventurebook_enemies'; // Tabelle prefix + name
	$charset_collate = $wpdb->get_charset_collate();
	$sql = "CREATE TABLE IF NOT EXISTS $table_name (
		id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
		code VARCHAR(255) NOT NULL,
		location_id INT(11) NOT NULL DEFAULT 0,
		stats JSON,
    	is_hostile TINYINT(1) NOT NULL DEFAULT 1,
    	next_action VARCHAR(255) DEFAULT NULL,
		spawn_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0
	) $charset_collate;";
	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
	dbDelta($sql);

}

/**
 * in the item definitions table, we store all items that are available in the game
 * stats are stored in a json column
 *
 * @return void
 */
function create_item_definitions_table(): void
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'adventurebook_item_definitions'; // Tabelle prefix + name

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(255) NOT NULL,
        name VARCHAR(255) NOT NULL,
        description TEXT,
    	stats JSON,
        image_url VARCHAR(255)
    ) $charset_collate;";

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


function create_item_spawns_table(): void
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'adventurebook_item_spawns'; // Tabelle prefix + name

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(255) NOT NULL,
        page_id INT(11) NOT NULL DEFAULT 0,
        respawn_time INT(11) NOT NULL DEFAULT 0,
		last_spawned TIMESTAMP NULL DEFAULT NULL,
		data VARCHAR(255) DEFAULT NULL,
		character_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
		parent_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0
    ) $charset_collate;";

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

/**
 * Create the items table
 * This function creates the table for the items
 *
 * The table has the following columns:
 * - id: The ID of the item
 * - parent_item_id: The ID of the parent item
 * - character_id: The ID of the character who owns the item
 * - location_id: The ID of the location where the item is at the moment
 *
 * @return void
 */
function create_items_table(): void
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'adventurebook_items'; // Tabelle prefix + name

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(255) NOT NULL,
        character_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
        location_id INT(11) NOT NULL DEFAULT 0,
		stats JSON,
		spawn_id INT(11) NOT NULL DEFAULT 0
    ) $charset_collate;";

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

/**
 * Create the character table
 * This function creates the table for the characters
 *
 * The table has the following columns:
 * - id: The ID of the character
 * - user_id: The ID of the user who owns the character
 * - name: The name of the character
 * - zone: The ID of the zone the character is in
 * - created_at: The timestamp when the character was created
 *
 * The table also has a foreign key constraint on the user_id column
 * to ensure that the user exists in the users table.
 *
 * Character stats are stored in a json column
 *
 * @return void
 */
function create_characters_table(): void
{
    global $wpdb;
    $table_name = $wpdb->prefix . 'adventurebook_characters';

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE IF NOT EXISTS $table_name (
        id BIGINT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        user_id BIGINT(20) UNSIGNED NOT NULL,
        adventurebook_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
        name VARCHAR(255) NOT NULL,
        location_id BIGINT(20) NOT NULL DEFAULT 0,
    	stats JSON,
    	inventory JSON,
    	next_action VARCHAR(255) DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES {$wpdb->prefix}users(ID) ON DELETE CASCADE
    ) $charset_collate;";

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

function adventurebook_drop_all_tables(): void
{
    drop_table_characters();
    drop_table_items();
    drop_table_item_spawns();
    drop_table_item_definitions();
	drop_table_enemies();
	drop_table_enemy_spawns();
	drop_table_enemy_definitions();
	drop_table_scripts();
}

function drop_table_scripts(): void {
	global $wpdb;
	$wpdb->query( "DROP TABLE IF EXISTS " . $wpdb->prefix . "adventurebook_scripts" );// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

function drop_table_enemy_definitions(): void
{
	global $wpdb;
	$wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_enemy_definitions");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}
function drop_table_enemy_spawns(): void
{
	global $wpdb;
	$wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_enemy_spawns");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

function drop_table_enemies(): void
{
	global $wpdb;
	$wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_enemies");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

function drop_table_characters(): void
{
    global $wpdb;
	$wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_characters");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

function drop_table_item_definitions(): void
{
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_item_definitions");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

function drop_table_item_spawns(): void
{
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_item_spawns");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}


function drop_table_items(): void
{
    global $wpdb;
    $wpdb->query("DROP TABLE IF EXISTS ".$wpdb->prefix."adventurebook_items");// phpcs:ignore WordPress.DB.DirectDatabaseQuery
}

/**
 * Get all items for a specific page and character.
 *
 * @param int $page_id The ID of the page.
 * @param int $character_id The ID of the character (for character-specific items).
 *
 * @return Item[] An array of Item entries
 */
function get_items_for_page(int $page_id, int $character_id): array
{
	global $wpdb;
	$results = $wpdb->get_results($wpdb->prepare("SELECT * FROM ".$wpdb->prefix."adventurebook_items WHERE character_id = %d AND location_id=%d", $character_id, $page_id), ARRAY_A);// phpcs:ignore WordPress.DB.DirectDatabaseQuery
	$items = array();
	foreach ($results as $result) {
		$items[] = new Item($result['id'], $result['character_id'], $result['code'], $result['location_id'], $result['stats']);
	}
	return $items;
}

function adventurebook_update_database_schema( $old_version ) {
	global $wpdb;

	if($old_version < '1.0.14') {
		// set the options adventurebook_enable_keyboard_navigation initially to true
		//  adventurebook_show_direction_icons needs to be set to true as well
		update_option( 'adventurebook_enable_keyboard_navigation', 1 );
		update_option( 'adventurebook_show_direction_icons', 1 );
		update_option( 'adventurebook_enable_inline_editing', 0  );
	}

	//  rename the column 'zone' to 'location_id' in the adventurebook_characters table
	$table_name = $wpdb->prefix . 'adventurebook_characters';
	if(adventurebook_column_exists($table_name, 'zone')) {
		$success = adventurebook_rename_column(
			$table_name,   // Table name with prefix
			'zone',                       // Old column name
			'location_id',                // New column name
			'BIGINT(20) NOT NULL DEFAULT 0' // Column definition
		);
	}

	// we need to add the column 'parent_id' to the adventurebook_enemy_spawns table
	$table_name = $wpdb->prefix . 'adventurebook_enemy_spawns';
	// check if the column already exists
	if(!adventurebook_column_exists($table_name, 'parent_id')) {
		adventurebook_add_column($table_name, 'parent_id', 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0');
	}

	//  we need to add the varchar column "alternate_page_ids"
	if(!adventurebook_column_exists($table_name, 'alternate_page_ids')) {
		adventurebook_add_column($table_name, 'alternate_page_ids', 'VARCHAR(255) DEFAULT NULL');
	}

	//  we need to add the JSON column 'Stats' to the adventurebook_items table
	$table_name = $wpdb->prefix . 'adventurebook_items';
	if(!adventurebook_column_exists($table_name, 'stats')) {
		adventurebook_add_column($table_name, 'stats', 'JSON');
	}
	//  add column for spawn_id (int)
	if(!adventurebook_column_exists($table_name, 'spawn_id')) {
		adventurebook_add_column($table_name, 'spawn_id', 'int(11) NOT NULL DEFAULT 0');
	}

	//  add respawn_time, last_spawned and data (string) to adventurebook_item_spawns table
	$table_name = $wpdb->prefix . 'adventurebook_item_spawns';
	if(!adventurebook_column_exists($table_name, 'respawn_time')) {
		adventurebook_add_column($table_name, 'respawn_time', 'INT(11) NOT NULL DEFAULT 0');
	}
	if(!adventurebook_column_exists($table_name, 'last_spawned')) {
		adventurebook_add_column($table_name, 'last_spawned', 'TIMESTAMP NULL DEFAULT NULL');
	}
	if(!adventurebook_column_exists($table_name, 'data')) {
		adventurebook_add_column($table_name, 'data', 'VARCHAR(255) DEFAULT NULL');
	}
	//  add character_id and parent_id to adventurebook_item_spawns table
	if(!adventurebook_column_exists($table_name, 'character_id')) {
		adventurebook_add_column($table_name, 'character_id', 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0');
	}
	if(!adventurebook_column_exists($table_name, 'parent_id')) {
		adventurebook_add_column($table_name, 'parent_id', 'BIGINT(20) UNSIGNED NOT NULL DEFAULT 0');
	}

	//  look in the characters table and see if the inventory JSON consists of "code:count" pairs
	$table_name = $wpdb->prefix . 'adventurebook_characters';
	$results = $wpdb->get_results( "SELECT id, inventory FROM $table_name WHERE inventory IS NOT NULL AND inventory != ''" , ARRAY_A );// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
	foreach ( $results as $result ) {
		$inventory = json_decode( $result['inventory'], true );
		if ( is_array( $inventory ) ) {
			//  check if the first element is a string with a colon
			if ( isset( $inventory[0] ) && is_string( $inventory[0] ) && strpos( $inventory[0], ':' ) !== false ) {
				//  we need to convert the inventory
				$new_inventory = array();
				foreach ( $inventory as $item ) {
					$parts = explode( ':', $item );
					if ( count( $parts ) == 2 ) {
						$code  = $parts[0];
						$count = intval( $parts[1] );
						for ( $i = 0; $i < $count; $i ++ ) {
							// create a new item for this code
							$item = Item::create_item( $result['id'], $code, 0 );
							//  add this item to the new inventory
							if ( $item ) {
								$new_inventory[] = $item->id;
							}
						}
					}
				}
				//  update the character's inventory
				$wpdb->update(// phpcs:ignore WordPress.DB.DirectDatabaseQuery
					$table_name,
					array( 'inventory' => wp_json_encode( $new_inventory ) ),
					array( 'id' => $result['id'] )
				);
			}
		}
	}

}

/**
 * checks if a column exists in a table
 *
 * @param string $table_name table name without prefix
 * @param string $column_name column name to check
 * @return bool true if column exists, false otherwise
 */
function adventurebook_column_exists( string $table_name, string $column_name ): bool {
	global $wpdb;

	$full_table_name = esc_sql( $table_name );
	$query = $wpdb->prepare(
		"SHOW COLUMNS FROM `$full_table_name` LIKE %s",// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$column_name
	);

	$result = $wpdb->get_results( $query ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery

	return ! empty( $result );
}

/**
 * Rename a column in a custom database table (WordPress-compliant and safe).
 *
 * @param string $table_name   Table name (without $wpdb->prefix).
 * @param string $old_column   Old column name.
 * @param string $new_column   New column name.
 * @param string $column_type  Full SQL column definition, e.g. 'BIGINT(20) NOT NULL DEFAULT 0'.
 * @return bool True on success, false on failure.
 */
function adventurebook_rename_column( string $table_name, string $old_column, string $new_column, string $column_type ): bool {
	global $wpdb;

	// Sanitize all dynamic parts
	$table_safe   = esc_sql( $table_name );
	$old_col_safe = esc_sql( $old_column );
	$new_col_safe = esc_sql( $new_column );

	// Build the SQL query (no placeholders here — structural query)
	$sql = "ALTER TABLE `$table_safe` CHANGE `$old_col_safe` `$new_col_safe` $column_type";

	// Execute and return success state
	$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery

	return ( $result !== false );
}

/**
 * Add a new column to a custom database table (WordPress-compliant and safe).
 *
 * @param string $table_name   Table name (without $wpdb->prefix).
 * @param string $column_name  Column name to add.
 * @param string $column_type  Full SQL column definition, e.g. 'VARCHAR(255) NOT NULL DEFAULT '''.
 * @return bool True on success, false on failure.
 */
function adventurebook_add_column( string $table_name, string $column_name, string $column_type ): bool {
	global $wpdb;

	// Sanitize dynamic parts
	$table_safe  = esc_sql( $table_name );
	$column_safe = esc_sql( $column_name );

	// Build SQL query
	$sql = "ALTER TABLE `$table_safe` ADD `$column_safe` $column_type";

	// Execute query (no caching applicable here)
	$result = $wpdb->query( $sql ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery

	return ( $result !== false );
}