<?php
/**
 * Data access for supplier <-> product cost/terms links.
 *
 * @package ProfitEngine
 */

declare( strict_types=1 );

namespace ProfitEngine\Purchasing;

use wpdb;

defined( 'ABSPATH' ) || exit;

/**
 * Repository for wp_pe_supplier_products.
 *
 * A product (purchasable unit) can be linked to multiple suppliers; exactly one
 * link may be flagged is_primary = 1.
 */
class SupplierProductRepository {

	private wpdb $db;

	private string $table;

	private string $suppliers_table;

	/**
	 * Constructor.
	 *
	 * @param wpdb $db WordPress database.
	 */
	public function __construct( wpdb $db ) {
		$this->db              = $db;
		$this->table           = $db->prefix . 'profit_engine_supplier_products';
		$this->suppliers_table = $db->prefix . 'profit_engine_suppliers';
	}

	/**
	 * All supplier links for a purchasable unit, with supplier names.
	 *
	 * @param int $product_id Purchasable-unit ID.
	 * @return array<int,array<string,mixed>>
	 */
	public function for_product( int $product_id ): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT sp.*, s.name AS supplier_name
				 FROM {$this->table} sp
				 LEFT JOIN {$this->suppliers_table} s ON s.id = sp.supplier_id
				 WHERE sp.product_id = %d
				 ORDER BY sp.is_primary DESC, s.name ASC",
				$product_id
			),
			ARRAY_A
		);

		return $rows ?: array();
	}

	/**
	 * The primary supplier link for a purchasable unit, if any.
	 *
	 * @param int $product_id Purchasable-unit ID.
	 * @return array<string,mixed>|null
	 */
	public function primary_for_product( int $product_id ): ?array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$row = $this->db->get_row(
			$this->db->prepare(
				"SELECT sp.*, s.name AS supplier_name
				 FROM {$this->table} sp
				 LEFT JOIN {$this->suppliers_table} s ON s.id = sp.supplier_id
				 WHERE sp.product_id = %d AND sp.is_primary = 1
				 LIMIT 1",
				$product_id
			),
			ARRAY_A
		);

		return $row ?: null;
	}

	/**
	 * Primary supplier links for many units at once, keyed by product_id.
	 *
	 * Batch equivalent of primary_for_product() — one query for a whole set of
	 * units, so callers (e.g. the product picker) avoid an N+1 query pattern.
	 *
	 * @param int[] $product_ids Purchasable-unit IDs.
	 * @return array<int,array<string,mixed>> product_id => primary link row.
	 */
	public function primary_for_products( array $product_ids ): array {
		$product_ids = array_values( array_unique( array_map( 'intval', $product_ids ) ) );
		if ( empty( $product_ids ) ) {
			return array();
		}

		$placeholders = implode( ',', array_fill( 0, count( $product_ids ), '%d' ) );
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT sp.*, s.name AS supplier_name
				 FROM {$this->table} sp
				 LEFT JOIN {$this->suppliers_table} s ON s.id = sp.supplier_id
				 WHERE sp.product_id IN ({$placeholders}) AND sp.is_primary = 1",
				$product_ids
			),
			ARRAY_A
		);

		$out = array();
		foreach ( $rows ?: array() as $row ) {
			// First primary per product wins (there should only be one).
			$pid = (int) $row['product_id'];
			if ( ! isset( $out[ $pid ] ) ) {
				$out[ $pid ] = $row;
			}
		}

		return $out;
	}

	/**
	 * Insert or update a supplier-product link (keyed on supplier+product).
	 *
	 * @param int                 $supplier_id Supplier ID.
	 * @param int                 $product_id  Purchasable-unit ID.
	 * @param array<string,mixed> $data        Column data (without keys/timestamps).
	 */
	public function upsert( int $supplier_id, int $product_id, array $data ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$existing_id = (int) $this->db->get_var(
			$this->db->prepare(
				"SELECT id FROM {$this->table} WHERE supplier_id = %d AND product_id = %d",
				$supplier_id,
				$product_id
			)
		);

		$now = gmdate( 'Y-m-d H:i:s' );

		if ( $existing_id > 0 ) {
			$data['updated_at'] = $now;
			// phpcs:ignore WordPress.DB.DirectDatabaseQuery
			$this->db->update( $this->table, $data, array( 'id' => $existing_id ) );
			return;
		}

		$data['supplier_id'] = $supplier_id;
		$data['product_id']  = $product_id;
		$data['created_at']  = $now;
		$data['updated_at']  = $now;
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->insert( $this->table, $data );
	}

	/**
	 * Clear the is_primary flag on every link for a product except one supplier.
	 *
	 * @param int $product_id  Purchasable-unit ID.
	 * @param int $keep_supplier Supplier to keep primary (0 to clear all).
	 */
	public function clear_primary_except( int $product_id, int $keep_supplier ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL.NotPrepared
		$this->db->query(
			$this->db->prepare(
				"UPDATE {$this->table} SET is_primary = 0 WHERE product_id = %d AND supplier_id <> %d",
				$product_id,
				$keep_supplier
			)
		);
	}

	/**
	 * Remove a supplier-product link.
	 *
	 * @param int $supplier_id Supplier ID.
	 * @param int $product_id  Purchasable-unit ID.
	 */
	public function delete_link( int $supplier_id, int $product_id ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->delete(
			$this->table,
			array(
				'supplier_id' => $supplier_id,
				'product_id'  => $product_id,
			)
		);
	}

	/**
	 * Remove all supplier links for a product (e.g. when the product is deleted).
	 *
	 * @param int $product_id Purchasable-unit ID.
	 */
	public function delete_by_product( int $product_id ): void {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$this->db->delete( $this->table, array( 'product_id' => $product_id ) );
	}

	/**
	 * Product links for a supplier (for the supplier detail screen).
	 *
	 * @param int $supplier_id Supplier ID.
	 * @return array<int,array<string,mixed>>
	 */
	public function for_supplier( int $supplier_id ): array {
		// phpcs:ignore WordPress.DB.DirectDatabaseQuery
		$rows = $this->db->get_results(
			$this->db->prepare(
				"SELECT * FROM {$this->table} WHERE supplier_id = %d ORDER BY updated_at DESC",
				$supplier_id
			),
			ARRAY_A
		);

		return $rows ?: array();
	}
}