<?php
/**
 * Database operations - simplified to store only JWT
 *
 * @package Scanshow
 */

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

/**
 * Database handler for STW plugin custom tables.
 */
class Scan_Show_Database {

	/**
	 * Create plugin database tables.
	 */
	public static function create_table() {
		global $wpdb;
		$charset_collate = $wpdb->get_charset_collate();
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';

		$connection_table = $wpdb->prefix . 'stw_connection';
		$sql_connection   = "CREATE TABLE $connection_table (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			jwt_token text NOT NULL,
			shop_id varchar(255) DEFAULT NULL,
			connected_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id)
		) $charset_collate;";
		dbDelta( $sql_connection );

		// Create page-to-item mapping table (supports both items and rooms).
		$page_item_table = $wpdb->prefix . 'stw_linked_objects';
		$sql_page_items  = "CREATE TABLE $page_item_table (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			page_id bigint(10) NOT NULL,
			entity_id varchar(255) NOT NULL,
			entity_type varchar(20) DEFAULT 'item',
			created_at datetime DEFAULT CURRENT_TIMESTAMP,
			updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
			PRIMARY KEY  (id),
			UNIQUE KEY page_id (page_id)
		) $charset_collate;";
		dbDelta( $sql_page_items );
	}

	/**
	 * Save JWT token and shop ID to database.
	 *
	 * @param string $jwt_token JWT token from STW platform.
	 * @param string $shop_id   Shop ID from STW platform.
	 * @return int|false Insert/update ID or false on failure.
	 */
	public static function save_jwt( $jwt_token, $shop_id ) {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_connection';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe (prefix + hardcoded).
		$existing = $wpdb->get_row( "SELECT id FROM $table_name LIMIT 1" );

		$data = array(
			'jwt_token'  => $jwt_token,
			'updated_at' => current_time( 'mysql' ),
			'shop_id'    => $shop_id,
		);

		if ( $existing ) {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update.
			$wpdb->update(
				$table_name,
				$data,
				array( 'id' => $existing->id )
			);
			return $existing->id;
		} else {
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table insert.
			$wpdb->insert(
				$table_name,
				$data
			);
			return $wpdb->insert_id;
		}
	}

	/**
	 * Get stored JWT token.
	 *
	 * @return string|null JWT token or null if not found.
	 */
	public static function get_jwt() {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_connection';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe.
		return $wpdb->get_var( "SELECT jwt_token FROM $table_name LIMIT 1" );
	}

	/**
	 * Get stored shop ID.
	 *
	 * @return string|null Shop ID or null if not found.
	 */
	public static function get_shop_id() {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_connection';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe.
		return $wpdb->get_var( "SELECT shop_id FROM $table_name LIMIT 1" );
	}

	/**
	 * Link a page to an STW item or room.
	 *
	 * @param int    $page_id     WordPress page/post ID.
	 * @param string $entity_id   STW item or room ID.
	 * @param string $entity_type Type: 'item' or 'room' (default 'item').
	 * @return int|false Insert/update ID or false on failure.
	 */
	public static function link_page_to_item( $page_id, $entity_id, $entity_type = 'item' ) {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_linked_objects';

		// Check if link already exists.
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe.
		$existing = $wpdb->get_row(
			$wpdb->prepare( "SELECT id FROM $table_name WHERE page_id = %d", $page_id )
		);

		$data = array(
			'page_id'     => $page_id,
			'entity_id'   => $entity_id,
			'entity_type' => $entity_type,
			'updated_at'  => current_time( 'mysql' ),
		);

		if ( $existing ) {
			// Update existing link.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table update.
			$wpdb->update(
				$table_name,
				$data,
				array( 'id' => $existing->id )
			);
			return $existing->id;
		} else {
			// Insert new link.
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom plugin table insert.
			$wpdb->insert( $table_name, $data );
			return $wpdb->insert_id;
		}
	}

	/**
	 * Get the item ID linked to a page.
	 *
	 * @param int $page_id WordPress page/post ID.
	 * @return string|null Item ID or null if not linked.
	 */
	public static function get_page_item_id( $page_id ) {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_linked_objects';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe.
		return $wpdb->get_var(
			$wpdb->prepare( "SELECT entity_id FROM $table_name WHERE page_id = %d", $page_id )
		);
	}

	/**
	 * Get complete link data for a page (ID and type).
	 *
	 * @param int $page_id WordPress page/post ID.
	 * @return array|null Array with entity_id and entity_type, or null if not linked.
	 */
	public static function get_page_link_data( $page_id ) {
		global $wpdb;
		// Table name safely constructed from trusted $wpdb->prefix + hardcoded string.
		$table_name = $wpdb->prefix . 'stw_linked_objects';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Custom plugin table. Table name is safe.
		$row = $wpdb->get_row(
			$wpdb->prepare( "SELECT entity_id, entity_type FROM $table_name WHERE page_id = %d", $page_id ),
			ARRAY_A
		);

		return $row;
	}

	/**
	 * Unlink a page from its STW item.
	 *
	 * @param int $page_id WordPress page/post ID.
	 * @return bool True on success, false on failure.
	 */
	public static function unlink_page_from_item( $page_id ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'stw_linked_objects';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom plugin table delete.
		return $wpdb->delete( $table_name, array( 'page_id' => $page_id ) ) !== false;
	}

	/**
	 * Drop the database tables when plugin is uninstalled.
	 */
	public static function drop_table() {
		global $wpdb;
		// Table names safely constructed from trusted $wpdb->prefix + hardcoded string.
		$connection_table = $wpdb->prefix . 'stw_connection';
		$page_items_table = $wpdb->prefix . 'stw_linked_objects';

		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Intentional table drop. Table names are safe.
		$wpdb->query( "DROP TABLE IF EXISTS $connection_table" );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Intentional table drop. Table names are safe.
		$wpdb->query( "DROP TABLE IF EXISTS $page_items_table" );
	}
}