<?php
/**
 * Product Sync Audit 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\Constants\Constants;
use MOWCPS\Common\Utils\Model_Utils;

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

/**
 * Class Product_Sync_Audit_Model
 *
 * Handles product sync audit data storage and retrieval
 */
class Product_Sync_Audit_Model {
	/**
	 * Product sync audit table name
	 *
	 * @var string
	 */
	private static $table_name = Model_Utils::TABLE_PRODUCT_SYNC_AUDIT;

	/**
	 * Supplier table name
	 *
	 * @var string
	 */
	private static $suppliers_table = Model_Utils::TABLE_SUPPLIERS;

	/**
	 * Settings table name
	 *
	 * @var string
	 */
	private static $settings_table = Model_Utils::TABLE_SYNC_OPERATIONS;

	/**
	 * Connection table name
	 *
	 * @var string
	 */
	private static $connections_table = Model_Utils::TABLE_CONNECTIONS;

	/**
	 * Get product sync audit data with connection details by supplier UUID
	 *
	 * @param string $supplier_id Supplier ID.
	 * @param string $connection_id Connection ID.
	 * @return array|WP_Error Audit data or error
	 */
	public static function get_product_sync_audit_by_supplier( $supplier_id, $connection_id = null ) {
		$table_name        = Model_Utils::get_table_name( self::$table_name );
		$suppliers_table   = Model_Utils::get_table_name( self::$suppliers_table );
		$connections_table = Model_Utils::get_table_name( self::$connections_table );

		// Build the base query with connection details filtered by supplier UUID.
		$query = "SELECT 
			psa.connection_id,
			psa.status,
			psa.status_message,
			psa.parsed_success,
			psa.variation_success_parsed,
			c.name as connection_name
		FROM `{$table_name}` psa
		LEFT JOIN `{$suppliers_table}` s ON psa.supplier_id = s.id
		LEFT JOIN `{$connections_table}` c ON psa.connection_id = c.id
		WHERE psa.supplier_id = %d
		AND psa.connection_id IS NOT NULL
		AND psa.schedule_id IS NULL
		AND psa.batch_number IS NULL
		ORDER BY psa.start_time DESC";

		return self::get_results( $query, $supplier_id, $connection_id );
	}

	/**
	 * Get results from the database.
	 *
	 * @param string $query Query to execute.
	 * @param string $id ID.
	 * @param string $connection_id Connection ID.
	 * @return array|WP_Error Results or error.
	 */
	protected static function get_results( $query, $id = null, $connection_id = null ) {
		global $wpdb;

		// Execute query.
		$results = $wpdb->get_results( $wpdb->prepare( $query, $id, $connection_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom query with table names.

		if ( false === $results ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				'Failed to retrieve product sync audit data',
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		return $results;
	}

	/**
	 * Create a new product sync audit entry
	 *
	 * @param array $data Audit data.
	 * @return int|WP_Error Insert ID on success, WP_Error on failure
	 */
	public static function create( $data ) {
		global $wpdb;

		// Validate required fields.
		$required_fields = array( 'supplier_id' );
		foreach ( $required_fields as $field ) {
			if ( ! isset( $data[ $field ] ) ) {
				return new WP_Error(
					Constants::ERROR_MISSING_REQUIRED_FIELD,
					"Missing required field: {$field}",
					array( 'status' => Constants::HTTP_BAD_REQUEST )
				);
			}
		}

		// Prepare insert data with all columns.
		$insert_data = array(
			'supplier_id'                 => intval( $data['supplier_id'] ),
			'schedule_id'                 => isset( $data['schedule_id'] ) ? intval( sanitize_text_field( wp_unslash( $data['schedule_id'] ) ) ) : null,
			'connection_id'               => isset( $data['connection_id'] ) ? intval( sanitize_text_field( wp_unslash( $data['connection_id'] ) ) ) : null,
			'batch_number'                => isset( $data['batch_number'] ) ? intval( sanitize_text_field( wp_unslash( $data['batch_number'] ) ) ) : null,
			'initiate_sync'               => isset( $data['initiate_sync'] ) ? (bool) sanitize_text_field( wp_unslash( $data['initiate_sync'] ) ) : true,
			'sync_type'                   => isset( $data['sync_type'] ) ? sanitize_text_field( wp_unslash( $data['sync_type'] ) ) : 'vendor-available',
			'status'                      => isset( $data['status'] ) ? sanitize_text_field( wp_unslash( $data['status'] ) ) : 'initiate sync',
			'status_message'              => isset( $data['status_message'] ) ? sanitize_textarea_field( wp_unslash( $data['status_message'] ) ) : 'initiate sync',
			'start_time'                  => isset( $data['start_time'] ) ? sanitize_text_field( wp_unslash( $data['start_time'] ) ) : current_time( 'mysql' ),
			'end_time'                    => isset( $data['end_time'] ) ? sanitize_text_field( $data['end_time'] ) : null,
			'total_parsed'                => isset( $data['total_parsed'] ) ? intval( $data['total_parsed'] ) : null,
			'parsed_success'              => isset( $data['parsed_success'] ) ? intval( $data['parsed_success'] ) : null,
			'parsed_failed'               => isset( $data['parsed_failed'] ) ? intval( $data['parsed_failed'] ) : null,
			'variation_success_parsed'    => isset( $data['variation_success_parsed'] ) ? intval( $data['variation_success_parsed'] ) : null,
			'current_child_product_count' => isset( $data['current_child_product_count'] ) ? intval( $data['current_child_product_count'] ) : null,
			'additional_data'             => isset( $data['additional_data'] ) ? wp_json_encode( $data['additional_data'] ) : null,
		);

		$format = array( '%d', '%d', '%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%d', '%d', '%d', '%d', '%d', '%s' );

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

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

		if ( false === $result ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				'Failed to create product sync audit entry',
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		return $wpdb->insert_id;
	}

	/**
	 * Update a product sync audit entry
	 *
	 * @param Sync_Analytics $analytics_object Object containing analytics data.
	 *
	 * @return bool|WP_Error True on success, WP_Error on failure
	 */
	public static function update( $analytics_object ) {
		global $wpdb;

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

		// Map incoming object properties → DB schema fields.
		$property_map = array(
			'sync_status_code'      => 'status',
			'sync_status_message'   => 'status_message',
			'sync_end_time'         => 'end_time',
			'total_product_parsed'  => 'total_parsed',
			'product_saved_success' => 'parsed_success',
			'product_saved_fail'    => 'parsed_failed',
			'additional_sync_data'  => 'additional_data',
		);

		foreach ( $property_map as $from => $to ) {
			if ( isset( $analytics_object->$from ) ) {
				$analytics_object->$to = $analytics_object->$from;
			}
		}

		// Allowed fields and formats (DB schema fields).
		$allowed_fields = array(
			'initiate_sync'               => '%d',
			'status'                      => '%s',
			'status_message'              => '%s',
			'end_time'                    => '%s',
			'total_parsed'                => '%d',
			'parsed_success'              => '%d',
			'parsed_failed'               => '%d',
			'variation_success_parsed'    => '%d',
			'current_child_product_count' => '%d',
			'additional_data'             => '%s',
		);

		$update_data = array();
		$format      = array();

		foreach ( $allowed_fields as $key => $field_format ) {
			if ( isset( $analytics_object->$key ) && null !== $analytics_object->$key && '' !== $analytics_object->$key ) {
				$value = $analytics_object->$key;

				if ( 'initiate_sync' === $key ) {
					$update_data[ $key ] = (bool) $value;
				} elseif ( 'additional_data' === $key ) {
					$update_data[ $key ] = ! is_null( $value ) ? wp_json_encode( $value ) : null;
				} elseif ( 'status_message' === $key ) {
					$update_data[ $key ] = ! is_null( $value ) ? sanitize_textarea_field( $value ) : null;
				} elseif ( '%d' === $field_format ) {
					$update_data[ $key ] = ! is_null( $value ) ? intval( $value ) : null;
				} elseif ( 'end_time' === $key ) {
					$update_data[ $key ] = ! is_null( $value ) ? wp_date( 'Y-m-d H:i:s', intval( $value ) ) : null;
				} else {
					$update_data[ $key ] = ! is_null( $value ) ? sanitize_text_field( $value ) : null;
				}

				$format[] = $field_format;
			}
		}

		if ( empty( $update_data ) ) {
			return new WP_Error(
				Constants::ERROR_NO_DATA,
				'No valid data provided for update',
				array( 'status' => Constants::HTTP_BAD_REQUEST )
			);
		}

		$where_clauses = array( 'supplier_id = %d' );
		$where_params  = array( intval( $analytics_object->supplier_id ) );

		if ( is_null( $analytics_object->schedule_id ) ) {
			$where_clauses[] = 'schedule_id IS NULL';
		} else {
			$where_clauses[] = 'schedule_id = %d';
			$where_params[]  = intval( $analytics_object->schedule_id );
		}

		if ( is_null( $analytics_object->connection_id ) ) {
			$where_clauses[] = 'connection_id IS NULL';
		} else {
			$where_clauses[] = 'connection_id = %d';
			$where_params[]  = intval( $analytics_object->connection_id );
		}

		if ( is_null( $analytics_object->batch_number ) ) {
			$where_clauses[] = 'batch_number IS NULL';
		} else {
			$where_clauses[] = 'batch_number = %d';
			$where_params[]  = intval( $analytics_object->batch_number );
		}

		$where_sql = implode( ' AND ', $where_clauses );

		// --- Final update query ---
		$sql = $wpdb->prepare(
			"UPDATE {$table_name} SET " .
			implode( ', ', array_map( fn( $col ) => "$col = {$allowed_fields[$col]}", array_keys( $update_data ) ) ) . // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.Security.EscapeOutput.NotEscaped -- $allowed_fields is already validated and escaped.
			" WHERE {$where_sql}",
			array_merge( array_values( $update_data ), $where_params )
		);

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

		if ( false === $result ) {
			return new WP_Error(
				Constants::ERROR_DB_ERROR,
				'Failed to update product sync audit entry',
				array( 'status' => Constants::HTTP_INTERNAL_SERVER_ERROR )
			);
		}

		return true;
	}


	/**
	 * Delete a product sync audit entry
	 *
	 * @param int $supplier_id Supplier ID.
	 * @param int $schedule_id Schedule ID.
	 * @param int $connection_id Connection ID.
	 * @return int Number of rows deleted.
	 */
	/**
	 * Delete a product sync audit entry
	 *
	 * @param int $supplier_id Supplier ID.
	 * @param int $schedule_id Schedule ID.
	 * @param int $connection_id Connection ID.
	 * @return int Number of rows deleted.
	 */
	public static function delete( $supplier_id, $schedule_id = null, $connection_id = null ): int {
		global $wpdb;

		$table  = Model_Utils::get_table_name( self::$table_name );
		$where  = array( 'supplier_id = %d' );
		$params = array( $supplier_id );

		if ( null === $schedule_id ) {
			$where[] = 'schedule_id IS NULL';
		} else {
			$where[]  = 'schedule_id = %d';
			$params[] = $schedule_id;
		}

		if ( null !== $connection_id ) {
			$where[]  = 'connection_id = %d';
			$params[] = $connection_id;
		}

		$where_sql = implode( ' AND ', $where );

		$query = $wpdb->prepare(
			"DELETE FROM {$table} WHERE {$where_sql}",
			$params
		);

		$wpdb->query( $query ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom query with dynamic WHERE.

		return (int) $wpdb->rows_affected;
	}

	/**
	 * Whether any sync run (any schedule) for this supplier is currently marked as running.
	 *
	 * Used to avoid offering "finalize audit PDF" while a sync is still in progress, which would
	 * delete the session JSONL after PDF generation.
	 *
	 * @param int $supplier_id Supplier ID.
	 * @return bool True if at least one `mowcps_{id}_*_running_status` option is `on`.
	 */
	public static function is_supplier_sync_in_progress( $supplier_id ) {
		global $wpdb;
		$supplier_id = (int) $supplier_id;
		if ( $supplier_id <= 0 ) {
			return false;
		}
		$pattern = 'mowcps_' . $supplier_id . '_%_running_status';
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Single lightweight check for UI.
		$count = (int) $wpdb->get_var(
			$wpdb->prepare(
				"SELECT COUNT(*) FROM {$wpdb->options} WHERE option_name LIKE %s AND option_value = %s",
				$pattern,
				Constants::SYNC_RUNNING_STATUS_ON
			)
		);
		return $count > 0;
	}

	/**
	 * Get sync running status
	 *
	 * @param int $supplier_id Supplier ID.
	 * @param int $schedule_id Schedule ID.
	 * @return string Sync running status.
	 */
	public static function get_sync_running_status( $supplier_id, $schedule_id ) {
		global $wpdb;
		// Clear the object cache.
		Model_Utils::clear_option_cache( 'mowcps_' . $supplier_id . '_' . $schedule_id . '_running_status' );
		wp_cache_delete( 'mowcps_' . $supplier_id . '_' . $schedule_id . '_running_status', 'options' );

		$option = $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->options} WHERE option_name LIKE %s",
				'mowcps_' . $supplier_id . '_' . $schedule_id . '_running_status'
			)
		);

		if ( is_array( $option ) && ! empty( $option ) ) {
			$option = $option[0]->option_value;
		} else {
			$option = Constants::SYNC_RUNNING_STATUS_OFF;
		}

		return $option;
	}

	/**
	 * Update sync running status in database.
	 *
	 * @param string $supplier_id supplier_id name of the analytics.
	 * @param int    $schedule_id Schedule ID.
	 * @param mixed  $flag        Sync status flag.
	 *
	 * @return void
	 */
	public static function update_sync_running_status( $supplier_id, $schedule_id, $flag ) {
		Model_Utils::update_option( 'mowcps_' . $supplier_id . '_' . $schedule_id . '_running_status', $flag );
	}


	/**
	 * Get sync audit entries.
	 *
	 * @param int      $supplier_id     Supplier ID.
	 * @param int|null $schedule_id     Schedule ID.
	 * @param int|null $connection_id   Connection ID.
	 * @param int|null $batch_number    Batch number.
	 * @param bool     $batch_analytics Whether to fetch batch analytics.
	 *
	 * @return array|object|null Sync audit entries.
	 */
	public static function get_sync_audit( $supplier_id, $schedule_id = null, $connection_id = null, $batch_number = null, $batch_analytics = false ) {
		global $wpdb;
		$table_name = $wpdb->prefix . 'mowcps_product_sync_audit';

		$conditions = array( 'supplier_id = %d' );
		$params     = array( $supplier_id );

		// Handle schedule_id (can be NULL).
		if ( is_null( $schedule_id ) ) {
			$conditions[] = 'schedule_id IS NULL';
		} else {
			$conditions[] = 'schedule_id = %d';
			$params[]     = $schedule_id;
		}

		// Handle connection_id (can be NULL).
		if ( is_null( $connection_id ) ) {
			$conditions[] = 'connection_id IS NULL';
		} else {
			$conditions[] = 'connection_id = %d';
			$params[]     = $connection_id;
		}

		// If batch analytics required → fetch all rows with batch_number not null.
		if ( $batch_analytics && ! is_null( $batch_number ) ) {
			$conditions[] = 'batch_number IS NOT NULL';
			$sql          = "SELECT * FROM {$table_name} WHERE " . implode( ' AND ', $conditions ) . ' ORDER BY id ASC';
			return $wpdb->get_results( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom query.
		} elseif ( ! is_null( $batch_number ) ) {
				$conditions[] = 'batch_number = %d';
				$params[]     = $batch_number;
		} elseif ( is_null( $batch_number ) ) {
			$conditions[] = 'batch_number IS NULL';
		}

		// Else → get latest row (batch or non-batch).
		$sql = "SELECT * FROM {$table_name} WHERE " . implode( ' AND ', $conditions ) . ' ORDER BY id DESC LIMIT 1';
		return $wpdb->get_row( $wpdb->prepare( $sql, $params ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared -- Custom query.
	}
}
