Postgresql, case, select in when
You need one row per product. So GROUP BY needed. And the query will look like

SELECT product_id, (expression for availability)
FROM sourcetable
GROUP BY product_id

You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact the amount into 3 values of -1, 0 or +1). For store_id != 7 we can get max value only and ignore all another. So source data can be prepared in CTE, and final query will be

WITH cte AS ( SELECT product_id, 
                     SIGN(MAX(CASE WHEN store_id != 7 
                                   THEN available_count 
                                   ELSE 0 END) -1) stock,
                     SIGN(MAX(CASE WHEN store_id = 7 
                              THEN available_count 
                              ELSE 0 END) -1) eshop 
              FROM sourcetable
              GROUP BY product_id )
SELECT product_id,
       CASE stock * 3 + eshop 
           WHEN -4 THEN ...
           WHEN -3 THEN ...
           ...
           WHEN 3 THEN ...
           WHEN 4 THEN ...
       END AS availability
FROM cte



Test this. Fill the variants in final CASE by yourself, please...