[Price Compare] Step 3: Update SQL query with unit_price and category

[frankieboxx]

Step 3 — Updated SQL Query (cijene-api side)

Update the price query to fetch unit_price, quantity, unit, and category from chain_products:

SELECT
    p.price,
    p.unit_price,
    p.date,
    cp.name,
    cp.code,
    cp.quantity,
    cp.unit,
    cp.category,
    c.name AS chain_name,
    s.name AS store_name,
    s.id AS store_id
FROM prices p
JOIN chain_products cp ON p.chain_product_id = cp.id
JOIN stores s ON p.store_id = s.id
JOIN chains c ON s.chain_id = c.id
WHERE s.id = ANY($1)                          -- Dubrovnik store IDs
  AND p.date >= CURRENT_DATE - INTERVAL '3 days'
  AND p.unit_price > 0
ORDER BY cp.code, p.unit_price ASC

File: scripts/price_compare.py
Depends on: #72 (Step 1)
Priority: P1