from datetime import date
from typing import Any

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


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

    async def list_customers(
        self,
        *,
        search: str | None,
        kode_sales: str | None = None,
        limit: int,
        offset: int,
    ) -> tuple[list[dict[str, Any]], int]:
        search_filter = """
            AND (:search IS NULL
                OR p.kodepelanggan LIKE :search_pattern
                OR p.namapelanggan LIKE :search_pattern)
        """
        sales_filter = "AND p.salescreated = :kode_sales" if kode_sales else ""
        parameters = {
            "search": search,
            "search_pattern": f"%{search}%" if search else None,
            "kode_sales": kode_sales,
        }
        count_result = await self._session.execute(
            text(
                f"""
                SELECT COUNT(*)
                FROM mspelanggan AS p
                WHERE p.aktif = 'AKTIF' AND COALESCE(p.DLT, '0') = '0'
                {sales_filter}
                {search_filter}
                """
            ),
            parameters,
        )
        result = await self._session.execute(
            text(
                f"""
                SELECT
                    p.kodepelanggan AS kode_pelanggan,
                    p.namapelanggan AS nama_pelanggan,
                    p.alamat,
                    p.telp AS telepon,
                    p.kodetingkat AS kode_tingkat,
                    COALESCE(p.limitpiutang, 0) AS limit_piutang,
                    COALESCE(p.piutang, 0) AS piutang,
                    p.limithari AS limit_hari
                FROM mspelanggan AS p
                WHERE p.aktif = 'AKTIF' AND COALESCE(p.DLT, '0') = '0'
                {sales_filter}
                {search_filter}
                ORDER BY p.namapelanggan, p.kodepelanggan
                LIMIT :limit OFFSET :offset
                """
            ),
            {**parameters, "limit": limit, "offset": offset},
        )
        return [dict(row) for row in result.mappings().all()], int(
            count_result.scalar_one()
        )

    async def list_salespeople(
        self, *, search: str | None, limit: int, kode_sales: str | None = None
    ) -> list[dict[str, Any]]:
        sales_filter = "AND kodesales = :kode_sales" if kode_sales else ""
        result = await self._session.execute(
            text(
                f"""
                SELECT kodesales AS kode_sales, namasales AS nama_sales
                FROM mssales
                WHERE aktif = 'AKTIF' AND COALESCE(DLT, '0') = '0'
                  {sales_filter}
                  AND (:search IS NULL
                    OR kodesales LIKE :search_pattern
                    OR namasales LIKE :search_pattern)
                ORDER BY namasales, kodesales
                LIMIT :limit
                """
            ),
            {
                "search": search,
                "search_pattern": f"%{search}%" if search else None,
                "kode_sales": kode_sales,
                "limit": limit,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    async def list_locations(
        self,
        *,
        search: str | None,
        kode_divisi: str | None,
        limit: int,
    ) -> list[dict[str, Any]]:
        result = await self._session.execute(
            text(
                """
                SELECT
                    kodelokasi AS kode_lokasi,
                    kodedivisi AS kode_divisi,
                    lokasi AS nama_lokasi
                FROM mslokasi
                WHERE aktif = 'AKTIF' AND COALESCE(DLT, '0') = '0'
                  AND (:kode_divisi IS NULL OR kodedivisi = :kode_divisi)
                  AND (:search IS NULL
                    OR kodelokasi LIKE :search_pattern
                    OR lokasi LIKE :search_pattern)
                ORDER BY lokasi, kodelokasi
                LIMIT :limit
                """
            ),
            {
                "search": search,
                "search_pattern": f"%{search}%" if search else None,
                "kode_divisi": kode_divisi,
                "limit": limit,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    async def list_products(
        self,
        *,
        search: str | None,
        kode_lokasi: str | None,
        limit: int,
        offset: int,
    ) -> tuple[list[dict[str, Any]], int]:
        words = (search or "").lower().split()
        parameters: dict[str, Any] = {"kode_lokasi": kode_lokasi}
        conditions = []
        for index, word in enumerate(words):
            key = f"search_word_{index}"
            conditions.append(
                f"AND (LOWER(b.kodebarang) LIKE :{key} "
                f"OR LOWER(b.namabarang) LIKE :{key})"
            )
            parameters[key] = f"%{word}%"
        search_filter = "\n".join(conditions)
        count_result = await self._session.execute(
            text(
                f"""
                SELECT COUNT(*)
                FROM msbarang AS b
                WHERE b.aktif = 'AKTIF' AND COALESCE(b.DLT, '0') = '0'
                {search_filter}
                """
            ),
            parameters,
        )
        result = await self._session.execute(
            text(
                f"""
                SELECT
                    b.kodebarang AS kode_barang,
                    b.namabarang AS nama_barang,
                    COALESCE(b.satuandefault, b.satuan) AS satuan_default,
                    b.HB AS harga_beli,
                    b.HJ AS harga_jual,
                    b.ppn,
                    COALESCE(s.stok_tersedia, 0) AS stok_tersedia,
                    b.kodeakunpenjualan AS kode_akun_penjualan,
                    b.kodeakunpersediaan AS kode_akun_persediaan,
                    b.kodeakunhpp AS kode_akun_hpp
                FROM msbarang AS b
                LEFT JOIN (
                    SELECT kodebarang, SUM(qty_saldo) AS stok_tersedia
                    FROM msstok_saldo_fifo
                    WHERE :kode_lokasi IS NULL OR kodelokasi = :kode_lokasi
                    GROUP BY kodebarang
                ) AS s ON s.kodebarang = b.kodebarang
                WHERE b.aktif = 'AKTIF' AND COALESCE(b.DLT, '0') = '0'
                {search_filter}
                ORDER BY b.namabarang, b.kodebarang
                LIMIT :limit OFFSET :offset
                """
            ),
            {**parameters, "limit": limit, "offset": offset},
        )
        return [dict(row) for row in result.mappings().all()], int(
            count_result.scalar_one()
        )

    async def list_product_units(
        self, *, kode_barang: str, kode_tingkat: str | None
    ) -> list[dict[str, Any]]:
        result = await self._session.execute(
            text(
                """
                SELECT
                    satuan,
                    konversi,
                    hbkonversi AS harga_beli,
                    hjkonversi AS harga_jual,
                    kodetingkat AS kode_tingkat,
                    NULLIF(barcode, '') AS barcode,
                    diskon1,
                    diskon2,
                    diskon3,
                    potongan
                FROM msbarangkonversi
                WHERE kodebarang = :kode_barang AND DLT = '0'
                  AND (:kode_tingkat IS NULL OR kodetingkat = :kode_tingkat)
                ORDER BY konversi, satuan, kodetingkat
                """
            ),
            {"kode_barang": kode_barang, "kode_tingkat": kode_tingkat},
        )
        return [dict(row) for row in result.mappings().all()]

    async def find_barcode(
        self,
        *,
        barcode: str,
        kode_tingkat: str | None,
        kode_lokasi: str | None,
    ) -> list[dict[str, Any]]:
        result = await self._session.execute(
            text(
                """
                SELECT
                    k.kodebarang AS kode_barang,
                    b.namabarang AS nama_barang,
                    k.satuan,
                    k.konversi,
                    k.hbkonversi AS harga_beli,
                    k.hjkonversi AS harga_jual,
                    k.kodetingkat AS kode_tingkat,
                    k.barcode,
                    k.diskon1,
                    k.diskon2,
                    k.diskon3,
                    k.potongan,
                    b.ppn,
                    COALESCE(s.stok_tersedia, 0) AS stok_tersedia,
                    b.kodeakunpenjualan AS kode_akun_penjualan,
                    b.kodeakunpersediaan AS kode_akun_persediaan,
                    b.kodeakunhpp AS kode_akun_hpp
                FROM msbarangkonversi AS k
                INNER JOIN msbarang AS b ON b.kodebarang = k.kodebarang
                LEFT JOIN (
                    SELECT kodebarang, SUM(qty_saldo) AS stok_tersedia
                    FROM msstok_saldo_fifo
                    WHERE :kode_lokasi IS NULL OR kodelokasi = :kode_lokasi
                    GROUP BY kodebarang
                ) AS s ON s.kodebarang = k.kodebarang
                WHERE k.barcode = :barcode
                  AND k.DLT = '0'
                  AND b.aktif = 'AKTIF'
                  AND COALESCE(b.DLT, '0') = '0'
                  AND k.kodetingkat = COALESCE(:kode_tingkat, 'T00001')
                ORDER BY k.kodebarang, k.konversi
                """
            ),
            {
                "barcode": barcode,
                "kode_tingkat": kode_tingkat,
                "kode_lokasi": kode_lokasi,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    async def get_credit_info(
        self, kode_pelanggan: str, *, kode_sales: str | None = None
    ) -> dict[str, Any] | None:
        sales_filter = "AND p.salescreated = :kode_sales" if kode_sales else ""
        result = await self._session.execute(
            text(
                f"""
                SELECT
                    p.kodepelanggan AS kode_pelanggan,
                    p.namapelanggan AS nama_pelanggan,
                    COALESCE(p.limitpiutang, 0) AS limit_piutang,
                    COALESCE(p.piutang, 0) AS piutang,
                    COALESCE(p.limitpiutang, 0) - COALESCE(p.piutang, 0)
                        AS sisa_limit,
                    COALESCE(p.piutang, 0) > COALESCE(p.limitpiutang, 0)
                        AS melewati_limit,
                    p.limithari AS limit_hari,
                    p.kodetingkat AS kode_tingkat,
                    t.tingkatharga AS tingkat_harga
                FROM mspelanggan AS p
                LEFT JOIN mstingkatharga AS t
                    ON t.kodetingkat = p.kodetingkat AND t.DLT = '0'
                WHERE p.kodepelanggan = :kode_pelanggan
                  AND p.aktif = 'AKTIF'
                  AND COALESCE(p.DLT, '0') = '0'
                  {sales_filter}
                """
            ),
            {"kode_pelanggan": kode_pelanggan, "kode_sales": kode_sales},
        )
        row = result.mappings().one_or_none()
        return dict(row) if row else None

    async def get_price_history(
        self,
        *,
        kode_pelanggan: str,
        kode_barang: str,
        limit: int,
        kode_sales: str | None = None,
    ) -> list[dict[str, Any]]:
        sales_filter = "AND h.kodesales = :kode_sales" if kode_sales else ""
        result = await self._session.execute(
            text(
                f"""
                SELECT
                    h.kodesi,
                    h.tanggalfaktur AS tanggal_faktur,
                    d.qty,
                    d.satuan,
                    d.hargasatuan AS harga_satuan,
                    d.diskon1,
                    d.diskon2,
                    d.diskon3,
                    d.potongan,
                    d.hargatotal AS harga_total
                FROM trsiheader AS h
                INNER JOIN trsidetail AS d ON d.kodesi = h.kodesi
                WHERE h.kodepelanggan = :kode_pelanggan
                  AND d.kodebarang = :kode_barang
                  AND h.DLT = '0'
                  AND COALESCE(d.DLT, '0') = '0'
                  {sales_filter}
                ORDER BY h.tanggalfaktur DESC, h.kodesi DESC,
                    COALESCE(d.urutan, d.urut) DESC
                LIMIT :limit
                """
            ),
            {
                "kode_pelanggan": kode_pelanggan,
                "kode_barang": kode_barang,
                "kode_sales": kode_sales,
                "limit": limit,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    async def get_receivable_summary(
        self,
        *,
        kode_pelanggan: str,
        tanggal_mulai: date,
        tanggal_selesai: date,
        kode_sales: str | None = None,
    ) -> tuple[dict[str, Any], list[dict[str, Any]]]:
        sales_filter = "AND h.kodesales = :kode_sales" if kode_sales else ""
        parameters = {
            "kode_pelanggan": kode_pelanggan,
            "tanggal_mulai": tanggal_mulai,
            "tanggal_selesai": tanggal_selesai,
            "kode_sales": kode_sales,
        }
        base_sql = f"""
            FROM vwpiutang2 AS p
            INNER JOIN trsiheader AS h ON h.kodesi = p.kodesi
            WHERE h.kodepelanggan = :kode_pelanggan
              AND h.DLT = '0'
              AND h.tanggalfaktur BETWEEN :tanggal_mulai AND :tanggal_selesai
              {sales_filter}
        """
        summary_result = await self._session.execute(
            text(
                f"""
                SELECT
                    COUNT(*) AS jumlah_faktur,
                    COALESCE(SUM(p.total), 0) AS total_faktur,
                    COALESCE(SUM(p.returan), 0) AS total_retur,
                    COALESCE(SUM(p.bayar), 0) AS total_bayar,
                    COALESCE(SUM(
                        COALESCE(p.total, 0)
                        - COALESCE(p.returan, 0)
                        - COALESCE(p.bayar, 0)
                    ), 0) AS sisa_piutang
                {base_sql}
                """
            ),
            parameters,
        )
        detail_result = await self._session.execute(
            text(
                f"""
                SELECT
                    p.kodesi,
                    h.tanggalfaktur AS tanggal_faktur,
                    COALESCE(p.total, 0) AS total_faktur,
                    COALESCE(p.returan, 0) AS total_retur,
                    COALESCE(p.bayar, 0) AS total_bayar,
                    COALESCE(p.total, 0)
                        - COALESCE(p.returan, 0)
                        - COALESCE(p.bayar, 0) AS sisa_piutang
                {base_sql}
                ORDER BY h.tanggalfaktur DESC, p.kodesi DESC
                """
            ),
            parameters,
        )
        return (
            dict(summary_result.mappings().one()),
            [dict(row) for row in detail_result.mappings().all()],
        )