<?php
class ProductMain {

  protected PDO $pdo;
  protected ?string $language = null;

  protected ?int $pricingCountry = null;
  protected int  $pricingQuantity = 1;
  protected ?int $fallbackCountry = null;
  
  
  protected string $currencyCode = 'EUR';
  protected string $currencyLocale = 'de';
  protected int $currencyDecimals = 2;
  
  protected ?bool $hasReservationTable = null;

  public function __construct(PDO $pdo, ?string $language = null) {
    $this->pdo = $pdo;
    $this->language = $language;
  }

  public function setLanguage(?string $language): void {
    $this->language = $language;
  }

	public function setPricingContext(?int $id_country, int $quantity = 1): void {
    $this->pricingCountry  = $id_country;
    $this->pricingQuantity = max(1, (int)$quantity);
  }
  public function setPricingFallbackCountry(?int $id_country): void {
    $this->fallbackCountry = $id_country;
  }
  public function getPricing(): array {
    return [$this->pricingCountry, $this->pricingQuantity, $this->fallbackCountry];
  }
  
  
	public function setCurrency(string $code = 'EUR', string $locale = 'de', int $decimals = 2): void {
    $this->currencyCode = strtoupper($code);
    $this->currencyLocale = $locale;
    $this->currencyDecimals = $decimals;
  }

  public function formatPrice(?float $value): ?string {
    if ($value === null) return null;
	  if (class_exists(\NumberFormatter::class)) {
	    $format = new \NumberFormatter($this->currencyLocale, \NumberFormatter::CURRENCY);
	    $format->setAttribute(\NumberFormatter::FRACTION_DIGITS, $this->currencyDecimals);
	    return $format->formatCurrency($value, $this->currencyCode);
	  }
	  $number = number_format($value, $this->currencyDecimals, ',', '.');
	  return $number.' '.$this->currencyCode;
  }

  public function getParentId(int $id): int {
	  $sql = "SELECT id_module FROM products WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  $id_parent = (int)($result->fetchColumn() ?: 0);
	  return $id_parent > 0 ? $id_parent : $id;
	}
	
  public function isVariant(int $id): bool {
    return $this->getParentId($id) !== $id;
  }
  
	public function resolveModuleIdWithFallback(string $table, int $id): int {
	  $sql = "SELECT 1 FROM ".$table." WHERE id_module = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  if ($result->fetchColumn()) return $id;
	  return $this->getParentId($id);
	}
	
	public function isNonEmpty(mixed $value): bool {
	  if (is_array($value)) return !empty($value);
	  if (is_string($value)) return trim($value) !== '';
	  return $value !== null && $value !== false;
	}
	
	public function getMergedContent(int $id_variant, ?int $id_parent): array {
	  $variant_data = $this->getContent($id_variant, $this->language, true) ?? [];
	  if (!$id_parent || $id_parent === $id_variant) return $variant_data;
	
	  $parent_data  = $this->getContent($id_parent, $this->language, true) ?? [];
	
	  $keys = array_unique(array_merge(array_keys($parent_data), array_keys($variant_data)));
	  $return = [];
	  foreach ($keys AS $key) {
	    $return[$key] = $this->isNonEmpty($variant_data[$key] ?? null) ? $variant_data[$key] : ($parent_data[$key] ?? null);
	  }
	  return $return;
	}
	
  public function getContent(int $id, ?string $language = null, bool $fallback = true): array {
    $lang = $language ?? $this->language;
    $id_target = $fallback ? $this->resolveModuleIdWithFallback('products_content', $id) : $id;
    
    $sql = "SELECT * FROM products_content WHERE id_module = :id AND language = :language LIMIT 1";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id', $id_target, PDO::PARAM_INT);
    $result->bindValue(':language', $lang, PDO::PARAM_STR);
    $result->execute();
    return $result->fetch() ?: [];
  }
  
  public function getImages(int $id, bool $fallback = true): array {
    $id_target = $fallback ? $this->resolveModuleIdWithFallback('products_images', $id) : $id;
    $images = [];
    $sql = "SELECT images.file, module_images.r_position_outside, module_images.r_position_inside, module_images.c_enlarge,
						IF(module_images.title != '', module_images.title, images.title) AS title,
						IF(module_images.alt != '', module_images.alt, images.alt) AS alt,
						IF(module_images.text != '', module_images.text, images.text) AS text,
						IF(module_images.source != '', module_images.source, images.source) AS source,
						IF(module_images.copyright != '', module_images.copyright, images.copyright) AS copyright,
						IF(module_images.photographer != '', module_images.photographer, images.photographer) AS photographer
						FROM images AS images
						LEFT JOIN products_images AS module_images ON module_images.id_media = images.id
						WHERE module_images.id_module = :id_module
						GROUP BY module_images.id_media ORDER BY module_images.sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id_module', $id_target, PDO::PARAM_INT);
    $result->execute();
    while ($arr = $result->fetch()) $images[] = $arr;
    return $images;
  }
  
  public function getFiles(int $id, bool $fallback = true): array {
    $id_target = $fallback ? $this->resolveModuleIdWithFallback('products_files', $id) : $id;
    $files = [];
    $sql = "SELECT files.file,
    				IF(module_files.text != '', module_files.text, files.text) AS text
            FROM files AS files
            LEFT JOIN products_files AS module_files ON module_files.id_media = files.id
            WHERE module_files.id_module = :id_module
            GROUP BY module_files.id_media ORDER BY module_files.sortorder ASC";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id_module', $id_target, PDO::PARAM_INT);
    $result->execute();
    while ($arr = $result->fetch()) $files[] = $arr;
    return $files;
  }
  
	public function getSku(int $id): ?string {
	  $sql = "SELECT sku FROM products WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  $sku = $result->fetchColumn();
	  if ($sku && trim($sku) !== '') return trim($sku);
	
	  $id_parent = $this->getParentId($id);
	  if ($id_parent !== $id) {
	    $result = $this->pdo->prepare($sql);
	    $result->bindValue(':id', $id_parent, PDO::PARAM_INT);
	    $result->execute();
	    $sku = $result->fetchColumn();
	    if ($sku && trim($sku) !== '') return trim($sku);
	  }
	  return null;
	}
	
	public function getWeight(int $id): float {
	  $sql = "SELECT weight FROM products WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  $weight = $result->fetchColumn();
	  if ($weight > 0) return $weight;
	
	  $id_parent = $this->getParentId($id);
	  if ($id_parent !== $id) {
	    $result = $this->pdo->prepare($sql);
	    $result->bindValue(':id', $id_parent, PDO::PARAM_INT);
	    $result->execute();
	    $weight = $result->fetchColumn();
	  	if ($weight > 0) return $weight;
	  }
	  return 0.0;
	}
	
  
  public function getPriceSingle(int $id, ?int $id_country = null, int $quantity = 1, ?int $id_country_fallback = null): array {
    $id_target = $this->resolveModuleIdWithFallback('products_prices', $id);
  	$sql = "SELECT products_prices.price_gross, tax_rate.tax AS tax, tax_rate.title AS tax_title
	          FROM products_prices AS products_prices
	          LEFT JOIN tax_rate AS tax_rate ON tax_rate.id = products_prices.id_tax_rate
	          WHERE products_prices.id_module = :id_module
	            AND products_prices.price_role = 'base'
	            AND (products_prices.id_country IS NULL 
	                 OR products_prices.id_country = :id_country 
	                 OR products_prices.id_country = :id_country_fallback)
	            AND (products_prices.quantity IS NULL OR products_prices.quantity <= :quantity)
	          ORDER BY
	            (products_prices.id_country = :id_country) DESC,
	            (products_prices.id_country = :id_country_fallback) DESC,
	            (products_prices.id_country IS NULL) ASC,
	            COALESCE(products_prices.quantity, 0) DESC
	          LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_target, PDO::PARAM_INT);
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
	  $result->execute();
	  $price_value = $result->fetch();

	  if (!$price_value || $price_value['price_gross'] === null) {
	    return [
	      'unavailable'  => true,
	      'value'        => null,
	      'uvp'          => null,
	      'base_price'   => null,
	      'has_tiers'    => false,
	    ];
	  }
	
	  $price = (float)$price_value['price_gross'];

	  $sql = "SELECT products_prices.price_gross
	          FROM products_prices AS products_prices
	          WHERE products_prices.id_module = :id_module
	            AND products_prices.price_role = 'uvp'
	            AND (products_prices.id_country IS NULL 
	                 OR products_prices.id_country = :id_country 
	                 OR products_prices.id_country = :id_country_fallback)
	          ORDER BY
	            (products_prices.id_country = :id_country) DESC,
	            (products_prices.id_country = :id_country_fallback) DESC,
	            (products_prices.id_country IS NULL) ASC
	          LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_target, PDO::PARAM_INT);
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  $result->execute();
	  $uvp_value = $result->fetchColumn();
	  $uvp = ($uvp_value !== false && $uvp_value !== null) ? (float)$uvp_value : null;
	
	  $sql = "SELECT 1
	          FROM products_prices
	          WHERE id_module = :id_module
	            AND price_role = 'base'
	            AND quantity IS NOT NULL AND quantity > 1
	            AND (id_country IS NULL OR id_country = :id_country OR id_country = :id_country_fallback)
	          LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_module', $id_target, PDO::PARAM_INT);
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  $result->execute();
	  $has_tiers = (bool)$result->fetchColumn();
	
	  $base_price = $this->computeBasePriceFromUnits($id_target, $price);
	
	  return [
	    'unavailable'			=> false,
	    'value'        		=> $price,
  		'value_fmt'    		=> $this->formatPrice($price),
	    'uvp'          		=> $uvp,
	    'uvp_fmt'      		=> $this->formatPrice($uvp),
	    'base_price'   		=> $base_price,
	    'base_price_fmt' 	=> $base_price && isset($base_price['value']) ? $this->formatPrice($base_price['value']).' / '.($base_price['unit'] ?? '') : null,
	    'has_tiers'    		=> $has_tiers,
	    'id_priced'    		=> $id_target,
	    'vat_title'		 		=> $price_value['tax_title'],
	    'vat_rate'		 		=> $price_value['tax'],
	  ];
	}
  
  public function getPriceSummary(int $id, ?int $id_country = null, int $quantity = 1, ?int $id_country_fallback = null): array {
  	$sql = "SELECT id FROM products WHERE (id = :id OR id_module = :id) AND c_active = '1'";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id', $id, PDO::PARAM_INT);
    $result->execute();
    
    $ids = array_map('intval', $result->fetchAll(PDO::FETCH_COLUMN));
    if (empty($ids)) {
      return [
        'unavailable' 				=> true,
        'min' 								=> null,
        'max' 								=> null,
        'uvp' 								=> null,
        'has_range' 					=> false, 
	      'has_variants'				=> false,
	      'has_tiers' 					=> false,
	      'has_distinct_prices' => false,
        'id_cheapest'  				=> null,
        'base_price'          => null,
      ];
    }

	  $inPlaceholders = [];
	  foreach ($ids as $idx => $pid) {
	    $inPlaceholders[] = ":id{$idx}";
	  }
	  $in = implode(',', $inPlaceholders);
    
    $sql_price = "SELECT products.id, (
			             SELECT products_prices.price_gross
			             FROM products_prices AS products_prices
			             WHERE products_prices.id_module = products.id
			               AND products_prices.price_role = 'base'
			               AND (products_prices.id_country IS NULL OR products_prices.id_country = :id_country OR products_prices.id_country = :id_country_fallback)
			               AND (products_prices.quantity IS NULL OR products_prices.quantity <= :quantity)
			             ORDER BY
			               (products_prices.id_country = :id_country) DESC,
			               (products_prices.id_country = :id_country_fallback) DESC,
			               (products_prices.id_country IS NULL) ASC,
			               COALESCE(products_prices.quantity, 0) DESC
			             LIMIT 1
				          ) AS effective_price
						      FROM products AS products
						      WHERE products.c_active = '1' AND products.id IN ($in)";
    
    $sql = "SELECT MIN(price_data.effective_price) AS price_min, MAX(price_data.effective_price) AS price_max
    				FROM ($sql_price) AS price_data
			      WHERE price_data.effective_price IS NOT NULL";
		      
    $result = $this->pdo->prepare($sql);
	  foreach ($ids as $idx => $pid) {
	    $result->bindValue(":id{$idx}", $pid, PDO::PARAM_INT);
	  }
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
    $result->execute();
    $arr = $result->fetch(PDO::FETCH_ASSOC) ?: [];
    $min = isset($arr['price_min']) ? (float)$arr['price_min'] : null;
    $max = isset($arr['price_max']) ? (float)$arr['price_max'] : null;

    if ($min === null) {
      return [
        'unavailable' 				=> true,
        'min' 								=> null,
        'max' 								=> null,
        'uvp' 								=> null,
        'has_range' 					=> false, 
	      'has_variants'				=> false,
	      'has_tiers' 					=> false,
	      'has_distinct_prices' => false,
        'id_cheapest'  				=> null,
        'base_price'          => null,
      ];
    }

    $sql = "SELECT COUNT(DISTINCT price_data.effective_price) AS cnt FROM ($sql_price) AS price_data WHERE price_data.effective_price IS NOT NULL";
    $result = $this->pdo->prepare($sql);
	  foreach ($ids as $idx => $pid) {
	    $result->bindValue(":id{$idx}", $pid, PDO::PARAM_INT);
	  }
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
    $result->execute();
    
    $count_distinct = (int)($result->fetchColumn() ?: 0);

    $has_range = ($max !== null && $max > $min);
    $has_variants = (count($ids) > 1);
    $has_distinct = ($count_distinct > 1);
    
    
    $sql = "SELECT 1 FROM products_prices WHERE id_module IN ($in) AND price_role = 'base' AND quantity IS NOT NULL AND quantity > 1 AND (id_country IS NULL OR id_country = :id_country OR id_country = :id_country_fallback) LIMIT 1";
    $result = $this->pdo->prepare($sql);
	  foreach ($ids as $idx => $pid) {
	    $result->bindValue(":id{$idx}", $pid, PDO::PARAM_INT);
	  }
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->execute();
    $has_tiers = (bool)$result->fetchColumn();

    $sql = "SELECT price_data.id FROM ($sql_price) AS price_data WHERE price_data.effective_price IS NOT NULL ORDER BY price_data.effective_price ASC, price_data.id ASC LIMIT 1";
    $result = $this->pdo->prepare($sql);
	  foreach ($ids as $idx => $pid) {
	    $result->bindValue(":id{$idx}", $pid, PDO::PARAM_INT);
	  }
	  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
    $result->execute();
    
    $id_cheapest = $result->fetchColumn();
    $id_cheapest = $id_cheapest ? (int)$id_cheapest : null;

		$vat_title = NULL;
		$vat_rate = NULL;
    $uvp = NULL;
    if ($id_cheapest !== null) {
    	$sql = "SELECT products_prices.id_tax_rate, tax_rate.tax AS tax, tax_rate.title AS tax_title
		          FROM products_prices AS products_prices
		          LEFT JOIN tax_rate AS tax_rate ON tax_rate.id = products_prices.id_tax_rate
		          WHERE products_prices.id_module = :id_module
		            AND products_prices.price_role = 'base'
		            AND (products_prices.id_country IS NULL OR products_prices.id_country = :id_country OR products_prices.id_country = :id_country_fallback) 
		            AND (products_prices.quantity IS NULL OR products_prices.quantity <= :quantity)
		          ORDER BY
		            (products_prices.id_country = :id_country) DESC,
		            (products_prices.id_country = :id_country_fallback) DESC,
		            (products_prices.id_country IS NULL) ASC,
		            COALESCE(products_prices.quantity, 0) DESC
		          LIMIT 1";
      $result = $this->pdo->prepare($sql);
      $result->bindValue(':id_module', $id_cheapest, PDO::PARAM_INT);
		  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
		  $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
		  $result->execute();
		  $tax_value = $result->fetch(PDO::FETCH_ASSOC) ?: null;
		
		  if ($tax_value) {
		    $vat_title = $tax_value['tax_title'] ?? NULL;
		    $vat_rate  = $tax_value['tax'] ?? NULL;
		  }
		  
      $sql = "SELECT products_prices.price_gross
			        FROM products_prices AS products_prices
			        WHERE products_prices.id_module = :id_module
				        AND products_prices.price_role = 'uvp'
				        AND (products_prices.id_country IS NULL OR products_prices.id_country = :id_country OR products_prices.id_country = :id_country_fallback)
			        ORDER BY
			          (products_prices.id_country = :id_country) DESC,
			          (products_prices.id_country = :id_country_fallback) DESC,
			          (products_prices.id_country IS NULL) ASC 
			        LIMIT 1";
      $result = $this->pdo->prepare($sql);
      $result->bindValue(':id_module', $id_cheapest, PDO::PARAM_INT);
		  $result->bindValue(':id_country', $id_country, $id_country === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
	  	$result->bindValue(':id_country_fallback', $id_country_fallback, $id_country_fallback === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
      $result->execute();
      $uvp_value = $result->fetchColumn();
      if ($uvp_value !== false && $uvp_value !== null) {
        $uvp = (float)$uvp_value;
      }
    }
    
    $base_price = null;
		if ($id_cheapest !== null && $min !== null) {
		  $base_price = $this->computeBasePriceFromUnits($id_cheapest, (float)$min);
		}

    return [
      'unavailable' 				=> false,
      'min'        					=> $min,
  		'min_fmt'             => $this->formatPrice($min),
      'max'        					=> $max,
  		'max_fmt'             => $this->formatPrice($max),
      'has_range'  					=> $has_range,
      'has_variants'				=> $has_variants,
      'has_tiers' 					=> $has_tiers,
      'has_distinct_prices' => $has_distinct,
      'uvp'        					=> $uvp,
  		'uvp_fmt'    					=> $this->formatPrice($uvp),
      'id_cheapest' 				=> $id_cheapest,
      'base_price'          => $base_price,
  		'base_price_fmt'      => $base_price && isset($base_price['value']) ? $this->formatPrice($base_price['value']).' / '.($base_price['unit'] ?? '') : null,
  		'vat_title'         	=> $vat_title,
  		'vat_rate'						=> $vat_rate,
    ];
  }
  
  
  
  public function baseUnitForType(string $type): array {
	  switch ($type) {
	    case 'mass':   return ['title' => 'kg'];
	    case 'volume': return ['title' => 'l'];
	    case 'length': return ['title' => 'm'];
	    case 'area':   return ['title' => 'm2'];
	    default:       return ['title' => ''];
	  }
	}

	public function fetchUnit(string $title): ?array {
	  $sql = "SELECT title, value, type FROM products_units WHERE c_active = '1' AND title = :title LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result ->bindValue(':title', $title, PDO::PARAM_STR);
	  $result ->execute();
	  $arr = $result ->fetch(PDO::FETCH_ASSOC);
	  return $arr ?: null;
	}
	
	public function factorToBase(string $type, string $unit_title): ?float {
	  $unit = strtolower($unit_title);
	  switch ($type) {
	    case 'mass':
	      if ($unit === 'g')  return 1/1000;
	      if ($unit === 'kg') return 1.0;
	      return null;
	    case 'volume':
	      if ($unit === 'ml') return 1/1000;
	      if ($unit === 'l')  return 1.0;
	      return null;
	    case 'length':
	      if ($unit === 'mm') return 1/1000;
	      if ($unit === 'cm') return 1/100;
	      if ($unit === 'm')  return 1.0;
	      return null;
	    case 'area':
	      if ($unit === 'm2') return 1.0;
	      // for maybe later cm2/dm2 : cm2=>1/10000, dm2=>1/100
	      return null;
	    default:
	      return null;
	  }
	}

	public function computeBasePriceFromUnits(int $id, float $price_gross): ?array {
		$sql = "SELECT amount, unit, packaging_unit FROM products WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  $arr = $result->fetch(PDO::FETCH_ASSOC);
	  if (!$arr) return null;
	
	  $amount = (float)($arr['amount'] ?? 0);
	  $unit_title = trim((string)($arr['unit'] ?? ''));
	  if ($amount <= 0 || $unit_title === '') return null;
	
	  $unit = $this->fetchUnit($unit_title);
	  if (!$unit) return null;
	  if ($unit['type'] === 'count') return null;
	
	  $base_meta  = $this->baseUnitForType($unit['type']);
	  if ($base_meta['title'] === '') return null;
	
	  $factor    = $this->factorToBase($unit['type'], $unit['title']);
	  if ($factor === null) return null;
	
	  $quantity_in_base = $amount * $factor;
	  if ($quantity_in_base <= 0) return null;
	
	  $base_unit_row = $this->fetchUnit($base_meta['title']);
	  $display_unit = $base_unit_row['value'] ?? $base_meta['title'];
	
	  $base_value = $price_gross / $quantity_in_base;
	
	  return [
	    'value' 		=> $base_value,
	    'unit'  		=> $display_unit,
	    'quantity'  => $quantity_in_base,
	    'raw'   		=> [
	      'amount'          => $amount,
	      'unit_title'      => $unit['title'],
	      'unit_type'       => $unit['type'],
	      'packaging_unit'  => $arr['packaging_unit'] ?? null,
	      'base_unit_title' => $base_meta['title'],
	    ],
	  ];
	}
	
	private function fetchProductRow(int $id): ?array {
	  $sql = "SELECT id, c_active, c_digital, c_coupon, c_stock, c_oversell, stock, stock_min, sku FROM products WHERE id = :id LIMIT 1";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->execute();
	  return $result->fetch(PDO::FETCH_ASSOC) ?: null;
	}

	public function getAvailable(int $id): int {
		if (!$this->hasReservationTable()) {
	    $sql = "SELECT stock FROM products WHERE id = :id";
	    $result = $this->pdo->prepare($sql);
	    $result->bindValue(':id', $id, PDO::PARAM_INT);
	    $result->execute();
	    return (int)($result->fetchColumn() ?? 0);
	  }

	  $sql = "SELECT products.stock
            - COALESCE((
                SELECT SUM(stock_reservation.quantity)
                FROM stock_reservation AS stock_reservation
                WHERE stock_reservation.id_product = products.id
                  AND stock_reservation.status = 'active'
                  AND stock_reservation.reserved_until >= :reserved_until
              ), 0) AS available
	          FROM products AS products
	          WHERE products.id = :id";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id, PDO::PARAM_INT);
	  $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
	  $result->execute();
	  return (int)($result->fetchColumn() ?? 0);
	}

	private function effectiveFlags(array $product): array {
	  $is_unlimited  = ((int)$product['c_digital'] ===  1) || ((int)$product['c_coupon'] === 1);
	  $stock_enabled = $is_unlimited ? 0 : ((int)$product['c_stock'] === 1 ? 1 : 0);
	  $oversell = ((int)$product['c_oversell'] === 1 ? 1 : 0);
	  $stock_min  = isset($product['stock_min']) ? (int)$product['stock_min'] : 0;
	
	  return [
	    'is_unlimited'  => $is_unlimited,
	    'stock_enabled' => $stock_enabled,
	    'oversell'     	=> $oversell,
	    'stock_min'     => $stock_min,
	  ];
	}

	public function getStockStatus(int $id): array {
	  $product = $this->fetchProductRow($id);
	  if (!$product || (int)$product['c_active'] !== 1) {
	    return ['status' => 'unavailable', 'can_buy' => false, 'available' => 0, 'backorder' => false];
	  }
	
	  $flags = $this->effectiveFlags($product);
	  if ($flags['is_unlimited'] || !$flags['stock_enabled']) {
	    return ['status' => 'in_stock', 'can_buy' => true, 'available' => null, 'backorder' => false];
	  }
	
	  $available = $this->getAvailable($id);
	  if ($available > $flags['stock_min']) return ['status' => 'in_stock', 'can_buy' => true, 'available' => $available, 'backorder' => false];
	  if ($available > 0) return ['status' => 'low_stock', 'can_buy' => true, 'available' => $available, 'backorder' => false];
	  if ($flags['oversell'] === 1) return ['status' => 'backorderable', 'can_buy' => true, 'available' => 0, 'backorder' => true];
	  return ['status' => 'out_of_stock', 'can_buy' => false, 'available' => 0, 'backorder' => false];
	}

	public function canAddToCart(int $id, int $quantity): array {
	  $product = $this->fetchProductRow($id);
	  if (!$product || (int)$product['c_active'] !== 1) return ['allowed' => false, 'reason' => 'unavailable'];
	
	  $flags = $this->effectiveFlags($product);
	  if ($flags['is_unlimited'] || !$flags['stock_enabled']) {
	    return ['allowed' => true, 'reason' => 'unlimited'];
	  }
	
	  $available = $this->getAvailable($id);
	  if ($available >= $quantity) return ['allowed' => true, 'reason' => 'ok'];
	  if ($available > 0) return ['allowed' => false, 'reason' => 'insufficient', 'available' => $available];
	
	  if ($flags['oversell'] === 1) return ['allowed' => true, 'reason' => 'backorder'];
	  return ['allowed' => false, 'reason' => 'out_of_stock'];
	}

	public function reserve(int $id, int $quantity, ?int $id_cart, ?int $id_customer, int $ttl = 900): bool {
		if ($quantity <= 0) return false;
		
	  $product = $this->fetchProductRow($id);
	  if (!$product) return false;
	
	  $flags = $this->effectiveFlags($product);
	  if ($flags['is_unlimited'] || !$flags['stock_enabled']) {
	    return true;
	  }
	  
	  if (!$this->hasReservationTable()) {
	    $available = $this->getAvailable($id);
	    if ($available < $quantity && $flags['oversell'] !== 1) return false;
	    return true;
	  }
    	  	
    $now = time();	
  	
  	$sql = "UPDATE stock_reservation SET status = 'released' WHERE id_product = :id_product AND status = 'active' AND reserved_until < :reserved_until";
  	$result = $this->pdo->prepare($sql);
  	$result->bindValue(':id_product', $id, PDO::PARAM_INT);
  	$result->bindValue(':reserved_until', $now, PDO::PARAM_INT);
  	$result->execute();

    $available = $this->getAvailable($id);
    if ($available < $quantity && $flags['oversell'] !== 1) {
      return false;
    }

    
    $sql = "INSERT INTO stock_reservation SET 
    				datetime = :datetime,
    				reserved_until = :reserved_until,
    				id_product = :id_product,
    				id_customer = :id_customer,
    				id_cart = :id_cart,
    				quantity = :quantity,
    				status = 'active',
    				sku = :sku ";
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':datetime', $now, PDO::PARAM_INT);
    $result->bindValue(':reserved_until', $now + $ttl, PDO::PARAM_INT);
    $result->bindValue(':id_product', $id, PDO::PARAM_INT);
    $result->bindValue(':id_customer', $id_customer, $id_customer === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':id_cart', $id_cart, $id_cart === null ? PDO::PARAM_NULL : PDO::PARAM_INT);
    $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
    $result->bindValue(':sku', $product['sku'] ?? null, PDO::PARAM_STR);
    $result->execute();
    return true;
	}
	
	public function consume(int $id, int $quantity, ?int $id_cart, ?int $id_customer = null): bool {
		if ($quantity <= 0) return false;
	  
	   if (!$this->hasReservationTable()) {
      $sql = "UPDATE products SET stock = stock - :quantity WHERE id = :id";
      $result = $this->pdo->prepare($sql);
      $result->bindValue(':quantity', $quantity, PDO::PARAM_INT);
      $result->bindValue(':id', $id, PDO::PARAM_INT);
      $result->execute();
	    return true;
	  }
	  
	  $product = $this->fetchProductRow($id);
	  if (!$product) return false;
	  $flags = $this->effectiveFlags($product);
	  
	  
	  $sql = "SELECT COALESCE(SUM(quantity),0) FROM stock_reservation
          WHERE id_product = :id_product AND status = 'active' AND reserved_until >= :reserved_until ";
	  if ($id_cart !== null) $sql .= "AND id_cart = :id_cart ";
	  else if ($id_customer !== null) $sql .= "AND id_customer = :id_customer";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_product', $id, PDO::PARAM_INT);
	  $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
	  if ($id_cart !== null) $result->bindValue(':id_cart', $id_cart, PDO::PARAM_INT);
	  else if ($id_customer !== null) $result->bindValue(':id_customer', $id_customer, PDO::PARAM_INT);
	  $result->execute();
	  $reserved_quantity = (int)$result->fetchColumn();
	
	  if ($reserved_quantity < $quantity && $flags['oversell'] !== 1) {
	    return false;
	  }
	  
  	
  	$sql = "SELECT id, quantity FROM stock_reservation WHERE id_product = :id_product AND status = 'active' AND reserved_until >= :reserved_until";
    if ($id_cart !== null) $sql .= " AND id_cart = :id_cart";
    else if ($id_customer !== null) $sql .= " AND id_customer = :id_customer";
    $sql .= " ORDER BY datetime ASC ";
    
    $result = $this->pdo->prepare($sql);
    $result->bindValue(':id_product', $id, PDO::PARAM_INT);
    $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
    if ($id_cart !== null) $result->bindValue(':id_cart', $id_cart, PDO::PARAM_INT);
    else if ($id_customer !== null) $result->bindValue(':id_customer', $id_customer, PDO::PARAM_INT);
    $result->execute();
    
    $consume_ids = [];
		$left = $quantity;
		while ($arr = $result->fetch(PDO::FETCH_ASSOC)) {
	    $rid = (int)$arr['id'];
	    $rq  = (int)$arr['quantity'];
	    if ($rq <= 0) continue;
	
	    if ($rq <= $left) {
        $consume_ids[] = $rid;
        $left -= $rq;
        if ($left === 0) break;
	    } else {
	    	$upd = "UPDATE stock_reservation SET quantity = quantity - :quantity WHERE id = :id";
        $upd = $this->pdo->prepare($upd);
        $upd->bindValue(':quantity', $left, PDO::PARAM_INT);
        $upd->bindValue(':id', $rid, PDO::PARAM_INT);
        $upd->execute();
        $left = 0;
        break;
	    }
		}
		
		$to_decrement = ($flags['oversell'] === 1) ? $quantity : ($quantity - $left);
		if ($to_decrement > 0) {
	  	$sql = "UPDATE products SET stock = stock - :quantity WHERE id = :id";
	  	$result = $this->pdo->prepare($sql);
	  	$result->bindValue(':quantity', $to_decrement, PDO::PARAM_INT);
	  	$result->bindValue(':id', $id, PDO::PARAM_INT);
	  	$result->execute();
	  }
  	
    if (!empty($consume_ids)) {
      $in = implode(',', array_fill(0, count($consume_ids), '?'));
      $sql = "UPDATE stock_reservation SET status = 'consumed' WHERE id IN ($in)";
      $result = $this->pdo->prepare($sql);
      $result->execute(array_map('intval', $consume_ids));
    }
    
    if ($flags['oversell'] !== 1 && $to_decrement < $quantity) {
    	return false;
    }

    return true;
	}
	
	public function releaseByCart(int $id_cart): void {
		if (!$this->hasReservationTable()) return;
		$sql = "UPDATE stock_reservation SET status = 'released' WHERE id_cart = :id_cart AND status = 'active'";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_cart', $id_cart, PDO::PARAM_INT);
	  $result->execute();
	}
	
	public function releasePartial(int $id_product, int $quantity, int $id_cart): void {
  	if ($quantity <= 0 || !$this->hasReservationTable()) return;
	
	  $sql = "SELECT id, quantity
	          FROM stock_reservation
	          WHERE id_product = :id_product
            AND id_cart = :id_cart
            AND status = 'active'
            AND reserved_until >= :reserved_until
	          ORDER BY datetime ASC";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_product', $id_product, PDO::PARAM_INT);
	  $result->bindValue(':id_cart', $id_cart, PDO::PARAM_INT);
	  $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
	  $result->execute();
	
	  $left = $quantity;
	  while ($left > 0 && ($arr = $result->fetch(PDO::FETCH_ASSOC))) {
	    if ($arr['quantity'] <= 0) continue;
	
	    if ($arr['quantity'] <= $left) {
	      $sql_upd = "UPDATE stock_reservation SET status = 'released' WHERE id = :id";
	      $result_upd = $this->pdo->prepare($sql_upd);
	      $result_upd->bindValue(':id', $arr['id'], PDO::PARAM_INT);
	      $result_upd->execute();
	      $left -= $arr['quantity'];
	    } else {
	      $sql_upd = "UPDATE stock_reservation SET quantity = quantity - :quantity WHERE id = :id";
	      $result_upd = $this->pdo->prepare($sql_upd);
	      $result_upd->bindValue(':quantity', $left, PDO::PARAM_INT);
	      $result_upd->bindValue(':id', $arr['id'], PDO::PARAM_INT);
	      $result_upd->execute();
	      $left = 0;
	    }
	  }
	}
	
	public function cleanupExpiredReservations(): int {
		if (!$this->hasReservationTable()) return 0;
		$sql = "UPDATE stock_reservation SET status = 'released' WHERE status = 'active' AND reserved_until < :reserved_until ";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
	  $result->execute();
	  return $result->rowCount();
	}

	public function hasReservationTable(): bool {
	  if ($this->hasReservationTable !== null) return $this->hasReservationTable;
	  try {
	    $sql = "SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'stock_reservation' LIMIT 1";
	    $result = $this->pdo->query($sql);
	    $this->hasReservationTable = (bool)$result->fetchColumn();
	  } catch (\Throwable $e) {
	    try {
	    	$sql = "SELECT 1 FROM stock_reservation LIMIT 1";
	      $result = $this->pdo->query($sql);
	      $this->hasReservationTable = true;
	    } catch (\Throwable $e2) {
	      $this->hasReservationTable = false;
	    }
	  }
	  return $this->hasReservationTable;
	}
	
	public function deliveryHintFromStock(array $stock): string {
		$map = [
	    'in_stock'      => defined('LABEL_IN_STOCK') ? LABEL_IN_STOCK : 'LABEL_IN_STOCK',
	    'low_stock'     => defined('LABEL_LOW_STOCK') ? LABEL_LOW_STOCK : 'LABEL_LOW_STOCK',
	    'backorderable' => defined('LABEL_BACKORDERABLE_STOCK') ? LABEL_BACKORDERABLE_STOCK : 'LABEL_BACKORDERABLE_STOCK',
	    'out_of_stock'  => defined('LABEL_OUT_OF_STOCK') ? LABEL_OUT_OF_STOCK : 'LABEL_OUT_OF_STOCK',
	    'unavailable'   => defined('LABEL_NOT_AVAILABLE') ? LABEL_NOT_AVAILABLE : 'LABEL_NOT_AVAILABLE',
	  ];
	  $key = $stock['status'] ?? 'unavailable';
	  return $map[$key] ?? $map['unavailable'];
	}
	
	public function getBestVariantStockStatus(int $id_parent): array {
	  $sql = "SELECT id FROM products WHERE (id = :id OR id_module = :id) AND c_active = '1'";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id', $id_parent, PDO::PARAM_INT);
	  $result->execute();
	  $ids = array_map('intval', $result->fetchAll(PDO::FETCH_COLUMN));
	
	  if (empty($ids)) {
	    return ['status' => 'unavailable', 'can_buy' => false, 'available' => 0, 'backorder' => false];
	  }
	
	  $rank = [
	    'in_stock'      => 1,
	    'low_stock'     => 2,
	    'backorderable' => 3,
	    'out_of_stock'  => 4,
	    'unavailable'   => 5,
	  ];
	
	  $best = ['status' => 'unavailable', 'can_buy' => false, 'available' => 0, 'backorder' => false];
	  $bestScore = PHP_INT_MAX;
	
	  foreach ($ids as $pid) {
	    $s = $this->getStockStatus($pid);
	    $score = $rank[$s['status']] ?? 999;
	    $tieBreaker = - (int)($s['available'] ?? 0);
	
	    $cmpBestScore = $bestScore;
	    $cmpBestAvail = - (int)($best['available'] ?? 0);
	
	    if ($score < $cmpBestScore || ($score === $cmpBestScore && $tieBreaker < $cmpBestAvail)) {
	      $best = $s;
	      $bestScore = $score;
	    }
	  }
	
	  return $best;
	}
	
	public function getReservedForCart(int $id_product, int $id_cart): int {
	  if (!$this->hasReservationTable()) return 0;
	  $sql = "SELECT COALESCE(SUM(quantity),0) FROM stock_reservation 
	  				WHERE id_product = :id_product
	  				AND id_cart = :id_cart
	  				AND status = 'active'
	  				AND reserved_until >= :reserved_until";
	  $result = $this->pdo->prepare($sql);
	  $result->bindValue(':id_product', $id_product, PDO::PARAM_INT);
	  $result->bindValue(':id_cart', $id_cart, PDO::PARAM_INT);
	  $result->bindValue(':reserved_until', time(), PDO::PARAM_INT);
	  $result->execute();
	  return (int)$result->fetchColumn();
	}
	
	public function getMaxEditableQuantity(int $id_product, ?int $id_cart): ?int {
	  $product = $this->fetchProductRow($id_product);
	  if (!$product || (int)$product['c_active'] !== 1) return 0;
	
	  $flags = $this->effectiveFlags($product);
	  if ($flags['is_unlimited'] || !$flags['stock_enabled']) return null;
	  if ($flags['oversell'] === 1) return null;
	
	  $availableGlobal = $this->getAvailable($id_product);	
	  $reservedByCart = ($id_cart !== null) ? $this->getReservedForCart($id_product, $id_cart) : 0;
	  $maxTotal = max(0, $reservedByCart + $availableGlobal);
	
	  return $maxTotal;
	}
  
}