from datetime import date
from typing import Any

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession


LIST_BASE_SQL = """
FROM vwsiheader AS v
INNER JOIN trsiheader AS h ON h.kodesi = v.kodesi
WHERE h.DLT = '0'
    AND v.tanggalfaktur >= :period_start
    AND v.tanggalfaktur < :period_end
"""


class SalesRepository:
    def __init__(self, session: AsyncSession) -> None:
        self._session = session

    async def list_sales(
        self,
        *,
        month: int,
        year: int,
        sales_date: date | None = None,
        only_unlocked: bool,
        kode_sales: str | None = None,
        limit: int,
        offset: int,
    ) -> tuple[list[dict[str, Any]], int]:
        unlocked_filter = " AND h.tutup = '0'" if only_unlocked else ""
        sales_filter = " AND v.kodesales = :kode_sales" if kode_sales else ""
        if sales_date is not None:
            period_start = sales_date
            period_end = date.fromordinal(sales_date.toordinal() + 1)
        else:
            period_start = date(year, month, 1)
            period_end = (
                date(year + 1, 1, 1)
                if month == 12
                else date(year, month + 1, 1)
            )
        parameters = {
            "period_start": period_start,
            "period_end": period_end,
            "kode_sales": kode_sales,
        }

        count_result = await self._session.execute(
            text(f"SELECT COUNT(*) {LIST_BASE_SQL}{unlocked_filter}{sales_filter}"),
            parameters,
        )
        total = int(count_result.scalar_one())

        result = await self._session.execute(
            text(
                f"""
                SELECT
                    v.kodesi,
                    v.tanggalfaktur AS tanggal_faktur,
                    v.tanggaljatuhtempo AS tanggal_jatuh_tempo,
                    v.namapelanggan AS nama_pelanggan,
                    v.namasales AS nama_sales,
                    v.status,
                    v.biaya,
                    v.subtotal,
                    v.pajak,
                    v.total,
                    v.tanggaledit AS tanggal_edit,
                    v.kodedivisi AS kode_divisi,
                    v.ekspedisi,
                    v.keterangan,
                    v.kodepelanggan AS kode_pelanggan,
                    v.jumlahcoli AS jumlah_koli,
                    v.kodeperjalanan AS kode_perjalanan,
                    DATEDIFF(v.tanggalekspedisi, v.tanggalfaktur) AS lama_kirim,
                    h.tutup = '1' AS tutup
                {LIST_BASE_SQL}{unlocked_filter}{sales_filter}
                ORDER BY v.tanggalfaktur DESC, v.kodesi DESC
                LIMIT :limit OFFSET :offset
                """
            ),
            {**parameters, "limit": limit, "offset": offset},
        )
        return [dict(row) for row in result.mappings().all()], total

    async def get_sale(
        self, kodesi: str, *, kode_sales: str | None = None
    ) -> dict[str, Any] | None:
        sales_filter = " AND v.kodesales = :kode_sales" if kode_sales else ""
        header_result = await self._session.execute(
            text(
                f"""
                SELECT
                    v.kodesi,
                    v.tanggalfaktur AS tanggal_faktur,
                    v.tanggaljatuhtempo AS tanggal_jatuh_tempo,
                    v.namapelanggan AS nama_pelanggan,
                    v.namasales AS nama_sales,
                    v.status,
                    v.biaya,
                    v.subtotal,
                    v.pajak,
                    v.total,
                    v.tanggaledit AS tanggal_edit,
                    v.kodedivisi AS kode_divisi,
                    v.ekspedisi,
                    v.keterangan,
                    v.kodepelanggan AS kode_pelanggan,
                    v.kodesales AS kode_sales,
                    v.jumlahcoli AS jumlah_koli,
                    v.kodeperjalanan AS kode_perjalanan,
                    DATEDIFF(v.tanggalekspedisi, v.tanggalfaktur) AS lama_kirim,
                    h.tutup = '1' AS tutup
                FROM vwsiheader AS v
                INNER JOIN trsiheader AS h ON h.kodesi = v.kodesi
                WHERE v.kodesi = :kodesi AND h.DLT = '0'
                {sales_filter}
                """
            ),
            {"kodesi": kodesi, "kode_sales": kode_sales},
        )
        header = header_result.mappings().one_or_none()
        if header is None:
            return None

        detail_result = await self._session.execute(
            text(
                """
                SELECT
                    d.ID AS id,
                    d.urutan,
                    d.kodebarang AS kode_barang,
                    b.namabarang AS nama_barang,
                    d.qty,
                    d.satuan,
                    d.hargasatuan AS harga_satuan,
                    d.diskon1,
                    d.diskon2,
                    d.diskon3,
                    d.potongan,
                    d.hargatotal AS harga_total,
                    d.PPN AS ppn,
                    d.pajakdetail AS pajak_detail,
                    d.konversi,
                    d.hbkonversi AS hb_konversi,
                    d.kodelokasi AS kode_lokasi
                FROM trsidetail AS d
                LEFT JOIN msbarang AS b ON b.kodebarang = d.kodebarang
                WHERE d.kodesi = :kodesi AND COALESCE(d.DLT, '0') = '0'
                ORDER BY COALESCE(d.urutan, d.urut), d.ID
                """
            ),
            {"kodesi": kodesi},
        )
        return {
            **dict(header),
            "items": [dict(row) for row in detail_result.mappings().all()],
        }