<?php
/**
 * Multichannels Model
 *
 * @package    MOMCS_Sync
 * @subpackage MOMCS_Sync/includes/common/models
 * @author     miniOrange
 */

declare(strict_types=1);

namespace MOMCS_Sync\Includes\Common\Models;

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

/**
 * Class for handling multichannels data.
 */
class Multichannels_Model {

	/**
	 * DB table name with prefix.
	 *
	 * @var string
	 */
	private string $table_name;



	/**
	 * Constructor.
	 */
	public function __construct() {
		global $wpdb;
		$this->table_name = $wpdb->prefix . 'momcs_multichannels';
	}

	/**
	 * Create the multichannels table if it doesn't exist.
	 *
	 * @return void
	 */
	public static function create_table(): void {
		global $wpdb;

		$wpdb->query( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Custom table creation.
			"CREATE TABLE IF NOT EXISTS {$wpdb->prefix}momcs_multichannels (
			id mediumint(9) NOT NULL AUTO_INCREMENT,
			uuid varchar(36) NOT NULL,
			connection_name varchar(100) NOT NULL,
			marketplace varchar(50) NOT NULL,
			region varchar(50) NOT NULL,
			seller_id varchar(50) DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'active',
			current_step tinyint(1) NOT NULL DEFAULT 1,
			last_sync datetime DEFAULT NULL,
			created_at datetime DEFAULT NULL,
			updated_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			UNIQUE KEY uuid (uuid)
			);"
		);
	}

	/**
	 * Get all channels.
	 *
	 * @return array Array of channels.
	 */
	/**
	 * Get all channels.
	 *
	 * @return array<int, array<string, mixed>>
	 */
	public function get_all_channels(): array {
		global $wpdb;

		return $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			"SELECT * FROM {$wpdb->prefix}momcs_multichannels ORDER BY id DESC",
			ARRAY_A
		);
	}

	/**
	 * Get a channel by ID.
	 *
	 * @param int $id Channel ID.
	 * @return array|null Channel data or null if not found.
	 */
	public function get_channel_by_id( int $id ): ?array {
		global $wpdb;

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_multichannels WHERE id = %d",
				$id
			),
			ARRAY_A
		);
	}

	/**
	 * Get a channel by UUID.
	 *
	 * @param string $uuid Channel UUID.
	 * @return array|null Channel data or null if not found.
	 */
	public function get_channel_by_uuid( string $uuid ): ?array {
		global $wpdb;

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_multichannels WHERE uuid = %s LIMIT 1",
				$uuid
			),
			ARRAY_A
		);
	}

	/**
	 * Add a new channel.
	 *
	 * @param array $data Channel data.
	 * @return int|false The number of rows inserted, or false on error.
	 */
	public function add_channel( $data ) {
		global $wpdb;

		return $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			$data
		);
	}

	/**
	 * Update a channel.
	 *
	 * @param int   $id   Channel ID.
	 * @param array $data Channel data.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_channel( $id, $data ) {
		global $wpdb;

		return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			$data,
			array( 'id' => $id )
		);
	}

	/**
	 * Delete a channel.
	 *
	 * @param int $id Channel ID.
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public function delete_channel( $id ) {
		global $wpdb;

		return $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array( 'id' => $id )
		);
	}

	/**
	 * Update seller_id for the most recent channel row matching marketplace and connection name.
	 *
	 * This mirrors a "UPDATE ... ORDER BY id DESC LIMIT 1" by first resolving the latest row id
	 * and then issuing a targeted update against that id.
	 *
	 * @param string $marketplace     Marketplace slug (e.g. 'amazon').
	 * @param string $connection_name Connection name provided by user.
	 * @param string $seller_id       Amazon Selling Partner ID.
	 * @return int|false Number of rows updated, or false on error.
	 */
	public function update_latest_seller_id( string $marketplace, string $connection_name, string $seller_id ) {
		global $wpdb;

		$latest_id = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT id FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s AND connection_name = %s ORDER BY id DESC LIMIT 1",
				$marketplace,
				$connection_name
			)
		);

		if ( empty( $latest_id ) ) {
			return false;
		}

		return $this->update_channel(
			(int) $latest_id,
			array(
				'seller_id'  => $seller_id,
				'updated_at' => current_time( 'mysql' ),
			)
		);
	}

	/**
	 * Update the last sync time for a channel.
	 *
	 * @param int $id Channel ID.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_last_sync( $id ) {
		global $wpdb;

		return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array(
				'last_sync' => current_time( 'mysql' ),
			),
			array( 'id' => $id )
		);
	}

	/**
	 * Get channels by marketplace.
	 *
	 * @param string $marketplace Marketplace name (e.g., 'amazon', 'ebay').
	 * @return array Array of channels for the specified marketplace.
	 */
	public function get_channels_by_marketplace( $marketplace ) {
		global $wpdb;

		$results = $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s AND status = %s ORDER BY id DESC",
				$marketplace,
				'active'
			),
			ARRAY_A
		);

		return $results;
	}

	/**
	 * Get the current step for a channel.
	 *
	 * @param string $uuid Channel UUID.
	 * @return int The current step, defaults to 1 if not found.
	 */
	public function get_current_step( $uuid ) {
		global $wpdb;

		$result = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT current_step FROM {$wpdb->prefix}momcs_multichannels WHERE uuid = %s",
				$uuid
			)
		);

		return $result ? (int) $result : 1;
	}

	/**
	 * Update the current step for a channel.
	 *
	 * @param string $uuid Channel UUID.
	 * @param int    $step The step to set.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_current_step( $uuid, $step ) {
		global $wpdb;

		return $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array(
				'current_step' => (int) $step,
				'updated_at'   => current_time( 'mysql' ),
			),
			array( 'uuid' => $uuid )
		);
	}

	/**
	 * Get the current step for a marketplace.
	 * Returns the step for the most recently created channel of the specified marketplace.
	 *
	 * @param string $marketplace Marketplace name.
	 * @return int The current step, defaults to 1 if no channel found.
	 */
	public function get_marketplace_current_step( $marketplace ) {
		global $wpdb;

		$result = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT current_step FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s ORDER BY id DESC LIMIT 1",
				$marketplace
			)
		);

		return $result ? (int) $result : 1;
	}

	/**
	 * Update the current step for a marketplace.
	 * Updates the step for the most recently created channel of the specified marketplace.
	 *
	 * @param string $marketplace Marketplace name.
	 * @param int    $step        The step to set.
	 * @return int|false The number of rows updated, or false on error.
	 */
	public function update_marketplace_current_step( $marketplace, $step ) {
		global $wpdb;

		// Get the most recent channel for this marketplace.
		$uuid = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT uuid FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s ORDER BY id DESC LIMIT 1",
				$marketplace
			)
		);

		if ( ! $uuid ) {
			return false;
		}

		return $this->update_current_step( $uuid, $step );
	}

	/**
	 * Get all channels for a marketplace (including inactive ones).
	 *
	 * @param string $marketplace Marketplace name (e.g., 'amazon', 'ebay').
	 * @return array Array of all channels for the specified marketplace.
	 */
	public function get_all_channels_by_marketplace( string $marketplace ): array {
		global $wpdb;

		return $wpdb->get_results( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT * FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s ORDER BY id DESC",
				$marketplace
			),
			ARRAY_A
		);
	}

	/**
	 * Delete all channels for a specific marketplace.
	 *
	 * @param string $marketplace Marketplace name (e.g., 'amazon', 'ebay').
	 * @return int|false The number of rows deleted, or false on error.
	 */
	public function delete_channels_by_marketplace( string $marketplace ) {
		global $wpdb;

		return $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$this->table_name,
			array( 'marketplace' => $marketplace )
		);
	}

	/**
	 * Get all UUIDs for channels of a specific marketplace.
	 *
	 * @param string $marketplace Marketplace name (e.g., 'amazon', 'ebay').
	 * @return array Array of UUIDs.
	 */
	public function get_uuids_by_marketplace( string $marketplace ): array {
		global $wpdb;

		$results = $wpdb->get_col( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT uuid FROM {$wpdb->prefix}momcs_multichannels WHERE marketplace = %s",
				$marketplace
			)
		);

		return $results ?? array();
	}

	/**
	 * Get latest channel details (region and uuid) by most recent id.
	 *
	 * @return array<string,string>|null
	 */
	public function get_latest_channel_details(): ?array {
		global $wpdb;
		$row = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.SchemaChange -- Always the latest data is required from database.
			$wpdb->prepare(
				"SELECT region, uuid, seller_id FROM {$wpdb->prefix}momcs_multichannels ORDER BY id DESC LIMIT %d",
				1
			),
			ARRAY_A
		);

		return $row ?? null;
	}

	/**
	 * Get channel configuration by marketplace region (Amazon marketplace ID).
	 *
	 * Looks for an active channel configured for the given marketplace region.
	 * Returns the channel with uuid, seller_id, and region for API calls.
	 *
	 * @param string $region Amazon marketplace ID (e.g., 'ATVPDKIKX0DER' for US).
	 *
	 * @return array<string,string>|null Channel data with uuid, seller_id, region, or null if not found.
	 */
	public function get_channel_by_region( string $region ): ?array {
		global $wpdb;

		$row = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.DirectDatabaseQuery.DirectQuery -- Always need fresh data.
			$wpdb->prepare(
				"SELECT uuid, seller_id, region FROM {$wpdb->prefix}momcs_multichannels 
				WHERE region = %s AND status = %s 
				ORDER BY id DESC LIMIT 1",
				$region,
				'active'
			),
			ARRAY_A
		);

		return $row ?? null;
	}
}
