<?php
/**
 * Product Tag Repository.
 *
 * @package Ugcify\Database
 */

namespace Ugcify\Database;

use WP_Error;

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

/**
 * Class ProductTagRepository

 * phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery,WordPress.DB.DirectDatabaseQuery.NoCaching,WordPress.DB.PreparedSQL.NotPrepared,WordPress.DB.PreparedSQL.InterpolatedNotPrepared,PluginCheck.Security.DirectDB.UnescapedDBParameter,WordPress.DB.SlowDBQuery.slow_db_query_meta_key,WordPress.DB.SlowDBQuery.slow_db_query_meta_value
 */
class ProductTagRepository {
	/**
	 * Table name.
	 *
	 * @var string
	 */
	private $table;

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

	/**
	 * Get tags by media ID.
	 *
	 * @param int $media_id Media ID.
	 * @return array<int, array<string, mixed>>
	 */
	public function get_by_media_id( int $media_id ): array {
		global $wpdb;

		$rows = $wpdb->get_results(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE media_id = %d ORDER BY id ASC",
				$media_id
			),
			ARRAY_A
		);

		if ( ! is_array( $rows ) ) {
			return array();
		}

		$tags = array();
		foreach ( $rows as $row ) {
			$tags[] = $this->normalize_row( $row );
		}

		return $tags;
	}

	/**
	 * Find one tag by ID.
	 *
	 * @param int $tag_id Tag ID.
	 * @return array<string, mixed>|null
	 */
	public function find( int $tag_id ): ?array {
		global $wpdb;

		$row = $wpdb->get_row(
			$wpdb->prepare(
				"SELECT * FROM {$this->table} WHERE id = %d",
				$tag_id
			),
			ARRAY_A
		);

		if ( ! is_array( $row ) ) {
			return null;
		}

		return $this->normalize_row( $row );
	}

	/**
	 * Create a new tag.
	 *
	 * @param int         $media_id Media ID.
	 * @param int         $product_id Product ID.
	 * @param float       $x_coordinate X coordinate in percentage.
	 * @param float       $y_coordinate Y coordinate in percentage.
	 * @param string|null $custom_label Optional custom label.
	 * @param string      $source Source.
	 * @param int|null    $ai_job_id AI job ID.
	 * @param float|null  $confidence AI confidence.
	 * @return array<string, mixed>|WP_Error
	 */
	public function create( int $media_id, int $product_id, float $x_coordinate, float $y_coordinate, ?string $custom_label = '', string $source = 'manual', ?int $ai_job_id = null, ?float $confidence = null ) {
		global $wpdb;

		$result = $wpdb->insert(
			$this->table,
			array(
				'media_id'      => $media_id,
				'product_id'    => $product_id,
				'x_coordinate'  => $x_coordinate,
				'y_coordinate'  => $y_coordinate,
				'custom_label'  => (string) $custom_label,
				'source'        => sanitize_key( $source ) ?: 'manual',
				'ai_job_id'     => $ai_job_id,
				'confidence'    => $confidence,
				'created_at'    => current_time( 'mysql' ),
				'updated_at'    => current_time( 'mysql' ),
			),
			array( '%d', '%d', '%f', '%f', '%s', '%s', '%d', '%f', '%s', '%s' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Failed to create product hotspot.', 'ugcify' )
			);
		}

		$tag = $this->find( (int) $wpdb->insert_id );
		if ( ! $tag ) {
			return new WP_Error(
				'db_insert_error',
				__( 'Failed to read created product hotspot.', 'ugcify' )
			);
		}

		return $tag;
	}

	/**
	 * Delete a tag.
	 *
	 * @param int $tag_id Tag ID.
	 * @return bool|WP_Error
	 */
	public function delete( int $tag_id ) {
		global $wpdb;

		$result = $wpdb->delete(
			$this->table,
			array( 'id' => $tag_id ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_delete_error',
				__( 'Failed to delete product hotspot.', 'ugcify' )
			);
		}

		return true;
	}

	/**
	 * Delete all tags for a media identifier.
	 *
	 * @param int $media_id Media ID.
	 * @return bool|WP_Error
	 */
	public function delete_by_media_id( int $media_id ) {
		global $wpdb;

		$result = $wpdb->delete(
			$this->table,
			array( 'media_id' => $media_id ),
			array( '%d' )
		);

		if ( false === $result ) {
			return new WP_Error(
				'db_delete_error',
				__( 'Failed to delete product hotspots for this media item.', 'ugcify' )
			);
		}

		return true;
	}

	/**
	 * Normalize DB row values.
	 *
	 * @param array<string, mixed> $row Raw row.
	 * @return array<string, mixed>
	 */
	private function normalize_row( array $row ): array {
		return array(
			'id'           => isset( $row['id'] ) ? (int) $row['id'] : 0,
			'media_id'     => isset( $row['media_id'] ) ? (int) $row['media_id'] : 0,
			'product_id'   => isset( $row['product_id'] ) ? (int) $row['product_id'] : 0,
			'x_coordinate' => isset( $row['x_coordinate'] ) ? (float) $row['x_coordinate'] : 0.0,
			'y_coordinate' => isset( $row['y_coordinate'] ) ? (float) $row['y_coordinate'] : 0.0,
			'custom_label' => isset( $row['custom_label'] ) ? (string) $row['custom_label'] : '',
			'source'       => isset( $row['source'] ) ? (string) $row['source'] : 'manual',
			'ai_job_id'    => isset( $row['ai_job_id'] ) ? (int) $row['ai_job_id'] : 0,
			'confidence'   => isset( $row['confidence'] ) ? (float) $row['confidence'] : null,
			'created_at'   => isset( $row['created_at'] ) ? (string) $row['created_at'] : '',
			'updated_at'   => isset( $row['updated_at'] ) ? (string) $row['updated_at'] : '',
		);
	}
}
