<?php
/**
 * Supplier Model
 *
 * @category   WordPress_Plugin
 * @package    Common
 * @subpackage Models
 * @author     miniOrange <info@miniorange.com>
 * @license    MIT/Expat
 * @link       https://plugins.miniorange.com/woocommerce-api-product-sync-with-woocommerce-rest-apis
 */

namespace MOWCPS\Common\Models;

use WP_Error;
use MOWCPS\Common\Interfaces\Model_Interface;
use MOWCPS\Common\Constants\Constants;
use MOWCPS\Common\Utils\Model_Utils;
use MOWCPS\Common\Utils\Sync_Utils;
use MOWCPS\Common\Services\Preview\Preview_Filter_Service;

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

/**
 * Class Supplier_Model
 *
 * Handles supplier data operations
 */
class Supplier_Model implements Model_Interface {
	/**
	 * Database table name
	 *
	 * @var string
	 */
	private static $table_name = Model_Utils::TABLE_SUPPLIERS;

	/**
	 * Valid columns that can be selected
	 *
	 * @var array
	 */
	private static $valid_columns = array(
		'id',
		'uuid',
		'name',
		'is_active',
		'app_type',
		'sync_type',
		'debug_mode',
		'audit_mode',
		'unique_product_identifier',
		'unique_product_variation_identifier',
		'details',
	);

	/**
	 * Get all suppliers with optional filtering and column selection
	 *
	 * @param array $args Optional. Arguments to filter and customize the query.
	 * @return array|WP_Error List of suppliers or error if invalid parameters
	 */
	public static function get_all( $args = array() ) {
		global $wpdb;

		$table = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		// Handle column selection.
		$columns = isset( $args['columns'] ) ? $args['columns'] : null;
		if ( null !== $columns ) {
			// Validate columns.
			$columns = self::validate_columns( $columns );
			if ( is_wp_error( $columns ) ) {
				return $columns;
			}
			// Always include uuid and name.
			$columns        = array_unique( array_merge( $columns, array( 'uuid', 'name' ) ) );
			$select_columns = implode( ', ', array_map( 'esc_sql', $columns ) );
		} else {
			$select_columns = '*';
		}

		$suppliers = $wpdb->get_results(
			"SELECT {$select_columns} FROM {$table} ORDER BY id ASC", //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name and columns are safe
			ARRAY_A
		); //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.

		// Decode JSON fields only if details column is selected.
		if ( '*' === $select_columns || strpos( $select_columns, 'details' ) !== false ) {
			$suppliers = Model_Utils::decode_json_fields( $suppliers, array( 'details' ) );
		}

		return $suppliers ? $suppliers : array();
	}

	/**
	 * Get supplier by UUID with optional column selection
	 *
	 * @param string $uuid The supplier UUID.
	 * @return array|null|WP_Error Supplier data or null if not found, WP_Error if invalid columns
	 */
	public static function get_by_uuid( $uuid ) {
		global $wpdb;

		$table_name = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		$supplier = $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT * FROM {$table_name} WHERE uuid = %s",
				$uuid
			),
			ARRAY_A
		);

		// Decode JSON fields.
		$supplier = Model_Utils::decode_json_fields( $supplier, array( 'details' ) );

		return $supplier;
	}

	/**
	 * Get supplier by ID
	 *
	 * @param int    $id      The supplier ID.
	 * @param string $columns Columns to select.
	 * @return array|null|WP_Error Array of supplier data, null if not found, or WP_Error on error.
	 */
	public static function get_by_id( $id, $columns = '*' ) {
		global $wpdb;

		// Validate and sanitize columns.
		if ( '*' !== $columns ) {
			$columns = self::validate_columns( $columns );
			if ( is_wp_error( $columns ) ) {
				return $columns;
			}
			$select_columns = implode( ', ', array_map( 'esc_sql', $columns ) );
		} else {
			$select_columns = '*';
		}

		$table = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		return $wpdb->get_row( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				'SELECT %1s FROM %1s WHERE id = %d',
				$select_columns,
				$table,
				$id
			),
			ARRAY_A
		);
	}

	/**
	 * Create new supplier
	 *
	 * @param array $data The supplier data.
	 * @return array|WP_Error Created supplier data or error
	 */
	public static function create( $data ) {
		global $wpdb;

		$table = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		// Check for duplicate name.
		$existing = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$table} WHERE name = %s", //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safe
				$data['name']
			)
		);

		if ( $existing ) {
			return new WP_Error(
				Constants::ERROR_DUPLICATE_ENTRY,
				Constants::MSG_DUPLICATE_ENTRY,
				array( 'status' => Constants::HTTP_CONFLICT )
			);
		}

		// Create a copy of data to avoid modifying the original parameter.
		$insert_data = $data;

		// Ensure sync_type is properly formatted for ENUM.
		if ( isset( $insert_data['sync_type'] ) ) {
			$insert_data['sync_type'] = strtoupper( $insert_data['sync_type'] );
		}

		// Add UUID.
		$insert_data['uuid'] = wp_generate_uuid4();

		// Convert details to JSON if present.
		if ( isset( $insert_data['details'] ) ) {
			$insert_data['details'] = wp_json_encode( $insert_data['details'] );
		}

		// Use $wpdb->insert() with proper format specification.
		$formats = array();
		foreach ( $insert_data as $column => $value ) {
			if ( 'is_active' === $column || 'debug_mode' === $column || 'audit_mode' === $column ) {
				$formats[ $column ] = '%d';
			} else {
				$formats[ $column ] = '%s';
			}
		}

		$result = $wpdb->insert( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Custom query.
			$table,
			$insert_data,
			$formats
		);

		if ( false === $result ) {
			// Get the actual database error.
			$db_error = $wpdb->last_error;
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $db_error ),
				array(
					'status'   => Constants::HTTP_INTERNAL_SERVER_ERROR,
					'db_error' => $db_error,
					'supplier' => $insert_data,
				)
			);
		}

		$supplier = self::get_by_uuid( $insert_data['uuid'] );
		if ( ! $supplier ) {
			return new WP_Error(
				Constants::ERROR_SUPPLIER_NOT_FOUND,
				Constants::MSG_SUPPLIER_NOT_FOUND,
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		return $supplier;
	}

	/**
	 * Update supplier
	 *
	 * @param string $uuid The supplier UUID.
	 * @param array  $data The supplier data.
	 * @return array|WP_Error Updated supplier data or error
	 */
	public static function update( $uuid, $data ) {
		global $wpdb;

		$table = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		$supplier = self::get_by_uuid( $uuid );
		if ( ! $supplier ) {
			return new WP_Error(
				Constants::ERROR_SUPPLIER_NOT_FOUND,
				Constants::MSG_SUPPLIER_NOT_FOUND,
				array( 'status' => Constants::HTTP_NOT_FOUND )
			);
		}

		// Check for restricted fields that cannot be updated.
		$restricted_fields = array( 'app_type', 'sync_type' );
		foreach ( $restricted_fields as $field ) {
			if ( isset( $data[ $field ] ) ) {
				return new WP_Error(
					Constants::ERROR_NOT_SUPPORTED,
					sprintf( Constants::MSG_NOT_SUPPORTED, "Updating '{$field}' is not allowed after supplier creation" ),
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}
		}

		// Only allow updating name, details, is_active, and debug_mode.
		$update_data = array();
		$formats     = array();

		// Update name if provided.
		if ( isset( $data['name'] ) ) {
			// Check for duplicate name if name is being updated.
			if ( $data['name'] !== $supplier['name'] ) {
				$existing = $wpdb->get_var( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
					$wpdb->prepare(
						"SELECT COUNT(*) FROM {$table} WHERE name = %s AND uuid != %s", //phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name is safe
						$data['name'],
						$uuid
					)
				);

				if ( $existing ) {
					return new WP_Error(
						Constants::ERROR_DUPLICATE_ENTRY,
						Constants::MSG_DUPLICATE_ENTRY,
						array( 'status' => Constants::HTTP_CONFLICT )
					);
				}
			}
			$update_data['name'] = $data['name'];
			$formats[]           = '%s';
		}

		// Update is_active if provided.
		if ( isset( $data['is_active'] ) ) {
			$update_data['is_active'] = intval( (bool) $data['is_active'] );
			$formats[]                = '%d';
		}

		// Update debug_mode if provided.
		if ( isset( $data['debug_mode'] ) ) {
			$update_data['debug_mode'] = intval( (bool) $data['debug_mode'] );
			$formats[]                 = '%d';
		}

		// Update audit_mode if provided.
		if ( isset( $data['audit_mode'] ) ) {
			$update_data['audit_mode'] = intval( (bool) $data['audit_mode'] );
			$formats[]                 = '%d';
		}

		// Update unique_product_identifier if provided.
		if ( isset( $data['unique_product_identifier'] ) ) {
			$update_data['unique_product_identifier'] = $data['unique_product_identifier'];
			$formats[]                                = '%s';
		}

		// Update unique_product_variation_identifier if provided.
		if ( isset( $data['unique_product_variation_identifier'] ) ) {
			$update_data['unique_product_variation_identifier'] = $data['unique_product_variation_identifier'];
			$formats[] = '%s';
		}

		// Update details if provided.
		if ( isset( $data['details'] ) ) {
			if ( ! is_array( $data['details'] ) ) {
				return new WP_Error(
					Constants::ERROR_INVALID_DATA_TYPE,
					sprintf( Constants::MSG_INVALID_DATA_TYPE, 'details' ),
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}
			$update_data['details'] = wp_json_encode( $data['details'] );
			$formats[]              = '%s';
		}

		// If no valid fields to update.
		if ( empty( $update_data ) ) {
			return new WP_Error(
				Constants::ERROR_NO_DATA,
				Constants::MSG_NO_DATA,
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		$result = $wpdb->update( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$table,
			$update_data,
			array( 'uuid' => $uuid ),
			$formats,
			array( '%s' )
		);

		if ( false === $result ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $wpdb->last_error ),
				array(
					'status'   => Constants::HTTP_INTERNAL_SERVER_ERROR,
					'db_error' => $wpdb->last_error,
					'uuid'     => $uuid,
				)
			);
		}

		$supplier = self::get_by_uuid( $uuid );
		if ( ! $supplier ) {
			return new WP_Error(
				Constants::ERROR_SUPPLIER_NOT_FOUND,
				Constants::MSG_SUPPLIER_NOT_FOUND,
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		// Update default supplier connections and authorization if details were updated.
		if ( isset( $data['details'] ) && Constants::SUPPLIER_APP_TYPE_CUSTOM !== $supplier['app_type'] ) {
			Model_Utils::update_default_supplier_configurations( $supplier );
		}

		return $supplier;
	}

	/**
	 * Get suppliers that have synced products
	 *
	 * @return array List of suppliers that have products with mowcps_connection_name_product_created meta.
	 */
	public static function get_suppliers_with_synced_products() {
		global $wpdb;

		// Get unique supplier IDs and names from products with the meta key.
		$results = $wpdb->get_results(
			"SELECT DISTINCT 
				pm.meta_value as supplier_id,
				pm2.meta_value as supplier_name
			FROM {$wpdb->postmeta} pm
			LEFT JOIN {$wpdb->postmeta} pm2 ON pm.post_id = pm2.post_id 
				AND pm2.meta_key = 'mowcps_supplier_name_product_created'
			INNER JOIN {$wpdb->posts} p ON pm.post_id = p.ID
			WHERE pm.meta_key = 'mowcps_connection_name_product_created'
			AND p.post_status NOT IN ('trash', 'auto-draft')
			ORDER BY pm2.meta_value ASC",
			ARRAY_A
		);

		$suppliers = array();
		foreach ( $results as $result ) {
			$supplier_id   = intval( $result['supplier_id'] ); // Always numeric ID.
			$supplier_name = $result['supplier_name'] ?: 'Unknown Supplier';

			// Try to get supplier details from suppliers table using numeric ID.
			$supplier_details = self::get_by_id( $supplier_id );

			if ( $supplier_details ) {
				// Current supplier exists in table - use fresh data.
				$suppliers[] = array(
					'uuid' => (string) $supplier_id, // Use ID as identifier for frontend.
					'name' => $supplier_details['name'],
					'type' => 'current', // Current supplier.
				);
			} else {
				// Legacy supplier that no longer exists in suppliers table.
				$suppliers[] = array(
					'uuid' => (string) $supplier_id, // Use ID as identifier for frontend.
					'name' => $supplier_name,
					'type' => 'legacy', // Legacy supplier.
				);
			}
		}

		return $suppliers;
	}

	/**
	 * Delete supplier and all its dependent records
	 *
	 * @param string $uuid The supplier UUID.
	 * @return bool|WP_Error True on success, WP_Error on failure
	 */
	public static function delete( $uuid ) {
		global $wpdb;

		$supplier = self::get_by_uuid( $uuid );
		if ( ! $supplier ) {
			return new WP_Error(
				Constants::ERROR_SUPPLIER_NOT_FOUND,
				Constants::MSG_SUPPLIER_NOT_FOUND,
				array( 'status' => Constants::HTTP_NOT_FOUND )
			);
		}

		$supplier_id = $supplier['id'];

		// Clean up scheduled cron jobs for this supplier.
		Sync_Utils::clear_supplier_scheduled_events( $supplier_id );

		// Delete preview filter file before deleting supplier.
		Preview_Filter_Service::delete_filter_file( $supplier_id );

		$table = esc_sql( Model_Utils::get_table_name( self::$table_name ) );

		// Delete the supplier - all related records will be deleted automatically via ON DELETE CASCADE.
		$result = $wpdb->delete( //phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query.
			$table,
			array( 'uuid' => $uuid ),
			array( '%s' )
		);

		if ( false === $result ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				sprintf( Constants::MSG_DB_ERROR, $wpdb->last_error ),
				array(
					'status'   => Constants::HTTP_INTERNAL_SERVER_ERROR,
					'db_error' => $wpdb->last_error,
					'uuid'     => $uuid,
				)
			);
		}

		return true;
	}

	/**
	 * Validate requested columns against valid columns
	 *
	 * @param string|array $columns Columns to validate.
	 * @return array|WP_Error Array of valid columns or WP_Error if invalid
	 */
	private static function validate_columns( $columns ) {
		// Convert string to array if single column.
		if ( is_string( $columns ) ) {
			$columns = array( $columns );
		}

		// Validate each column.
		$invalid_columns = array_diff( $columns, self::$valid_columns );
		if ( ! empty( $invalid_columns ) ) {
			return new WP_Error(
				Constants::ERROR_INVALID_FIELD_VALUE,
				sprintf( Constants::MSG_INVALID_FIELD_VALUE, 'columns' ),
				array(
					'status'          => Constants::HTTP_BAD_REQUEST,
					'invalid_columns' => $invalid_columns,
				)
			);
		}

		return $columns;
	}
}
