CREATE DEFINER=CURRENT_USER PROCEDURE `sales_byproduct`(IN `start_date` DATE, IN `end_date` DATE, IN `user_filter` INT)
BEGIN
-- Pharmacy sales detailed: one row per pharmacy order (POS / invoice) that the
-- customer has paid something on. Cost = what the products cost us (the
-- line's recorded cost, else the latest batch cost per selling unit, else the
-- product's cost price); Paid = money received on the order (at the till and
-- later payments on the invoice, other currency converted to $); Profit = Paid - Cost.
SELECT
    DATE_FORMAT(o.pos_date, '%d %b, %Y')                    AS `Date`,
    CONCAT('#', o.pos_id)                                   AS `Invoice`,
    COALESCE(o.customer, 'Walk-in')                         AS `Customer`,
    o.items                                                 AS `Items`,
    CONCAT('$ ', FORMAT(o.cost, 2))                         AS `Cost`,
    CONCAT('$ ', FORMAT(o.sale, 2))                         AS `Sale amount`,
    CONCAT('$ ', FORMAT(o.discount, 2))                     AS `Discount`,
    CONCAT('$ ', FORMAT(o.paid, 2))                         AS `Paid`,
    CONCAT('$ ', FORMAT(GREATEST(o.sale - o.discount - o.paid, 0), 2)) AS `Balance`,
    CONCAT('$ ', FORMAT(o.paid - o.cost, 2))                AS `Profit`,
    CASE WHEN o.sale - o.discount - o.paid <= 0.009 THEN 'Paid' ELSE 'Partial' END AS `Status`
FROM (
    SELECT
        pos.id, pos.pos_id, pos.pos_date,
        pat.name AS customer,
        (SELECT GROUP_CONCAT(CONCAT(p.name, ' × ', FORMAT(l.quantity, 0)) ORDER BY l.id SEPARATOR ', ')
           FROM phar_pos_products l JOIN scm_products p ON p.id = l.product_id
          WHERE l.pos_id = pos.id)                                                 AS items,
        (SELECT IFNULL(SUM(l.price * l.quantity), 0)
           FROM phar_pos_products l WHERE l.pos_id = pos.id)                        AS sale,
        (SELECT IFNULL(SUM(l.quantity * COALESCE(
                    NULLIF(l.cost, 0),
                    (SELECT si.cost / NULLIF(si.conversion, 0)
                       FROM store_inventories si
                      WHERE si.product_id = l.product_id AND DATE(si.created_at) <= pos.pos_date
                      ORDER BY si.created_at DESC, si.id DESC LIMIT 1),
                    NULLIF(p.cost_price, 0),
                    0)), 0)
           FROM phar_pos_products l JOIN scm_products p ON p.id = l.product_id
          WHERE l.pos_id = pos.id)                                                  AS cost,
        (SELECT IFNULL(SUM(IF(pay.currency = 2 AND pay.exchange_rate > 0, pay.amount / pay.exchange_rate, pay.amount)), 0) FROM phar_pos_payments pay WHERE pay.pos_id = pos.id)
          + (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.payment / pm.exchange_rate, pm.payment)), 0) FROM payments pm
              WHERE BINARY pm.reference = 'pharmacy' AND pm.reference_id = pos.id AND pm.status <> 'Refund') AS paid,
        (SELECT IFNULL(SUM(IF(pay.currency = 2 AND pay.exchange_rate > 0, pay.discount / pay.exchange_rate, pay.discount)), 0) FROM phar_pos_payments pay WHERE pay.pos_id = pos.id)
          + (SELECT IFNULL(SUM(IF(pm.currency = 2 AND pm.exchange_rate > 0, pm.discount / pm.exchange_rate, pm.discount)), 0) FROM payments pm
              WHERE BINARY pm.reference = 'pharmacy' AND pm.reference_id = pos.id AND pm.status <> 'Refund') AS discount
    FROM phar_pos pos
    LEFT JOIN patients pat ON pat.id = pos.customer_id
    WHERE pos.pos_date BETWEEN start_date AND end_date
      AND (user_filter = 0 OR pos.created_by = user_filter)
      AND (pos.status IS NULL OR pos.status <> 'Void')
      AND IFNULL(pos.is_deleted, 0) = 0
) o
WHERE o.paid > 0
ORDER BY o.pos_date, o.pos_id;
END
