# edited by glg
from typing import Dict, List


class SettlementFinanceCalculationService:
    @staticmethod
    def build_refund_subquery() -> str:
        return """
            SELECT r.transaksi_id,
                   SUM(COALESCE(td.produk_ord_hrg, dr.harga, 0) * COALESCE(dr.jumlah, 0)) AS total_refund
            FROM return_transaksi_penjualan r
            JOIN detail_return_transaksi_penjualan dr ON dr.return_id = r.id
            LEFT JOIN transaksi_data td
                ON td.transaksi_id = r.transaksi_id AND td.produk_id = dr.produk_id
            WHERE COALESCE(r.refund_method, 'cash') = 'cash'
            GROUP BY r.transaksi_id
        """

    def calculate_totals(self, cursor, *, normalized_ids: List[int], voucher_subquery: str) -> Dict[str, float]:
        placeholders = ",".join(["?"] * len(normalized_ids))
        refund_subquery = self.build_refund_subquery()

        cursor.execute(
            f"""
            SELECT COALESCE(
                       SUM(
                           MAX(
                               0,
                               COALESCE(t.transaksi_nilai, 0)
                               - COALESCE(r.total_refund, 0)
                               - COALESCE(v.total_voucher, 0)
                           )
                       ),
                       0
                   ) AS total_harus
            FROM transaksi t
            LEFT JOIN ({refund_subquery}) r ON r.transaksi_id = t.id
            LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id
            WHERE t.id IN ({placeholders})
            """,
            normalized_ids,
        )
        total_harus = float(cursor.fetchone()["total_harus"] or 0)

        cursor.execute(
            f"""
            SELECT COALESCE(SUM(COALESCE(r.total_refund, 0)), 0) AS total_refund
            FROM ({refund_subquery}) r
            WHERE r.transaksi_id IN ({placeholders})
            """,
            normalized_ids,
        )
        total_refund_cash = float(cursor.fetchone()["total_refund"] or 0)

        cursor.execute(
            f"""
            SELECT COALESCE(
                       SUM(
                           CASE
                               WHEN COALESCE(t.bank_nama, '') = '' THEN 0
                               WHEN t.bank_nama = 'Tunai' THEN 0
                               ELSE MAX(
                                   0,
                                   COALESCE(t.pembayaran_non_tunai, t.transaksi_dibayar, t.transaksi_nilai, 0)
                                   - COALESCE(v.total_voucher, 0)
                               )
                           END
                       ),
                       0
                   ) AS total_non_tunai
            FROM transaksi t
            LEFT JOIN ({voucher_subquery}) v ON v.transaksi_id = t.id
            WHERE t.id IN ({placeholders})
            """,
            normalized_ids,
        )
        total_non_tunai = float(cursor.fetchone()["total_non_tunai"] or 0)

        return {
            "total_harus": total_harus,
            "total_refund_cash": total_refund_cash,
            "total_non_tunai": total_non_tunai,
            "refund_subquery": refund_subquery,
        }