<?php
/**
 * File Path: includes/data/class-mbs-pos-inventory-dao.php
 *
 * MBS_POS_Inventory_DAO -- Per-location, per-format stock management
 *
 * Wraps mbs_pos_inventory. set_stock() is an INSERT ... ON DUPLICATE KEY UPDATE
 * upsert keyed on (location_id, format_row_id). The UNIQUE KEY on that pair
 * (set in migration 3.5) enforces exactly one stock row per location+format.
 *
 * get_by_location() returns a JOIN against mbs_format_rows so callers get
 * format/isbn13 context alongside stock_qty without a second query.
 *
 * @package    My_Book_Showroom
 * @subpackage Includes/Data
 * @since      1.2.0
 */

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

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- DAO/migration: table names via $wpdb->prefix are trusted server-controlled values, not user input
// phpcs:disable PluginCheck.Security.DirectDB.UnescapedDBParameter -- same reason: $wpdb->prefix table names are safe; $where_sql is built from sanitized server-controlled inputs
class MBS_POS_Inventory_DAO { // phpcs:ignore WordPress.NamingConventions.PrefixAllGlobals.NonPrefixedClassFound -- mbs_/MBS_ prefix is correct; scanner not configured with plugin prefix

	/**
	 * Return all inventory rows for a location, joined with format_row fields.
	 *
	 * Each returned object has:
	 *   format_row_id, format, format_label, isbn13, price, currency_code,
	 *   square_item_id, stock_qty, updated_at, post_id.
	 *
	 * @param int $location_id Location ID.
	 * @return object[]
	 */
	public static function get_by_location( int $location_id ): array {
		global $wpdb;
		$inv  = $wpdb->prefix . 'mbs_pos_inventory';
		$rows = $wpdb->prefix . 'mbs_format_rows';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$results = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT
				i.id              AS inventory_id,
				i.location_id,
				i.format_row_id,
				i.stock_qty,
				i.updated_at,
				r.post_id,
				r.format,
				r.format_label,
				r.isbn13,
				r.price,
				r.currency_code,
				r.square_item_id
			 FROM {$inv} i
			 INNER JOIN {$rows} r ON r.id = i.format_row_id
			 WHERE i.location_id = %d
			 ORDER BY r.post_id ASC, r.sort_order ASC",
			$location_id
		) );
		return $results ?: array();
	}

	/**
	 * Return the stock quantity for a specific location + format row pair.
	 *
	 * @param int $location_id   Location ID.
	 * @param int $format_row_id Format row ID.
	 * @return int 0 if no row exists.
	 */
	public static function get_stock( int $location_id, int $format_row_id ): int {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_pos_inventory';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$val = $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"SELECT stock_qty FROM {$table} WHERE location_id = %d AND format_row_id = %d",
			$location_id,
			$format_row_id
		) );
		return null !== $val ? (int) $val : 0;
	}

	/**
	 * Upsert stock quantity for a location + format row pair.
	 *
	 * Uses INSERT ... ON DUPLICATE KEY UPDATE so callers do not need to know
	 * whether a row already exists.
	 *
	 * @param int $location_id   Location ID.
	 * @param int $format_row_id Format row ID.
	 * @param int $qty           New stock quantity (0 or greater).
	 * @return bool
	 */
	public static function set_stock( int $location_id, int $format_row_id, int $qty ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_pos_inventory';
		$qty   = max( 0, $qty );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$result = $wpdb->query( $wpdb->prepare( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			"INSERT INTO {$table} (location_id, format_row_id, stock_qty)
			 VALUES (%d, %d, %d)
			 ON DUPLICATE KEY UPDATE stock_qty = VALUES(stock_qty), updated_at = NOW()",
			$location_id,
			$format_row_id,
			$qty
		) );
		return false !== $result;
	}

	/**
	 * Delete all inventory rows for a location (called before location delete).
	 *
	 * @param int $location_id Location ID.
	 * @return bool
	 */
	public static function delete_by_location( int $location_id ): bool {
		global $wpdb;
		$table = $wpdb->prefix . 'mbs_pos_inventory';
		return false !== $wpdb->delete( $table, array( 'location_id' => $location_id ), array( '%d' ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
	}
	/**
	 * Delete all inventory rows whose format row belongs to a book.
	 *
	 * The mbs_pos_inventory table is keyed by format_row_id, not post_id.
	 * This method uses an INNER JOIN to find all inventory rows whose parent
	 * format row belongs to $post_id and deletes them. Must be called BEFORE
	 * MBS_Format_Row_DAO::hard_delete_for_post() so the JOIN can still resolve.
	 *
	 * @param int $post_id Book post ID.
	 * @return void
	 */
	public static function delete_for_post( int $post_id ): void {
		global $wpdb;
		$inv  = $wpdb->prefix . 'mbs_pos_inventory';
		$rows = $wpdb->prefix . 'mbs_format_rows';
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names from $wpdb->prefix
		$wpdb->query( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- DAO layer -- direct DB access is intentional per MBS architecture
			$wpdb->prepare(
				"DELETE i FROM {$inv} i
				 INNER JOIN {$rows} r ON r.id = i.format_row_id
				 WHERE r.post_id = %d",
				$post_id
			)
		);
	}

}
