# edited by glg
from typing import Any, Dict, List

from pypos.core.utils.sql_query_builder import (
    build_sql_with_identifier_in_clause,
    render_sql_template,
)


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, Any]:
        refund_subquery = self.build_refund_subquery()

        total_harus_prefix = render_sql_template(
            """
            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
            """,
            refund_subquery=refund_subquery,
            voucher_subquery=voucher_subquery,
        )
        total_harus_query, total_harus_params = build_sql_with_identifier_in_clause(
            total_harus_prefix,
            "t.id",
            normalized_ids,
            cast_int=True,
            positive_only=True,
            unique=True,
        )
        cursor.execute(total_harus_query, total_harus_params)
        total_harus = float(cursor.fetchone()["total_harus"] or 0)

        total_refund_prefix = render_sql_template(
            """
            SELECT COALESCE(SUM(COALESCE(r.total_refund, 0)), 0) AS total_refund
            FROM ({refund_subquery}) r
            WHERE
            """,
            refund_subquery=refund_subquery,
        )
        total_refund_query, total_refund_params = build_sql_with_identifier_in_clause(
            total_refund_prefix,
            "r.transaksi_id",
            normalized_ids,
            cast_int=True,
            positive_only=True,
            unique=True,
        )
        cursor.execute(total_refund_query, total_refund_params)
        total_refund_cash = float(cursor.fetchone()["total_refund"] or 0)

        total_non_tunai_prefix = render_sql_template(
            """
            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
            """,
            voucher_subquery=voucher_subquery,
        )
        total_non_tunai_query, total_non_tunai_params = build_sql_with_identifier_in_clause(
            total_non_tunai_prefix,
            "t.id",
            normalized_ids,
            cast_int=True,
            positive_only=True,
            unique=True,
        )
        cursor.execute(total_non_tunai_query, total_non_tunai_params)
        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,
        }