from datetime import date, datetime
from typing import Any

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

from app.sales.schemas import SalesShortageOrderRequest


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

    @property
    def session(self) -> AsyncSession:
        return self._session

    async def non_transactional_tables(self) -> list[str]:
        result = await self._session.execute(
            text(
                """
                SELECT table_name
                FROM information_schema.tables
                WHERE table_schema = DATABASE()
                  AND table_name IN (
                    'trsoheader', 'trsodetail', 'trfifo_layer', 'xyz'
                  )
                  AND engine <> 'InnoDB'
                ORDER BY table_name
                """
            )
        )
        return [str(value) for value in result.scalars().all()]

    async def invalid_references(
        self, request: SalesShortageOrderRequest
    ) -> list[str]:
        invalid: list[str] = []
        customer = await self._session.scalar(
            text(
                """
                SELECT COUNT(*) FROM mspelanggan
                WHERE kodepelanggan = :code
                  AND aktif = 'AKTIF' AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"code": request.kode_pelanggan},
        )
        if not customer:
            invalid.append(f"pelanggan:{request.kode_pelanggan}")
        for item in request.items:
            product_unit = await self._session.scalar(
                text(
                    """
                    SELECT COUNT(*)
                    FROM msbarang AS b
                    INNER JOIN msbarangkonversi AS k
                        ON k.kodebarang = b.kodebarang
                    WHERE b.kodebarang = :kode_barang
                      AND k.satuan = :satuan
                      AND k.konversi = :konversi
                      AND b.aktif = 'AKTIF'
                      AND COALESCE(b.DLT, '0') = '0'
                      AND k.DLT = '0'
                    """
                ),
                {
                    "kode_barang": item.kode_barang,
                    "satuan": item.satuan,
                    "konversi": item.konversi,
                },
            )
            if not product_unit:
                invalid.append(
                    f"barang-satuan:{item.kode_barang}/{item.satuan}"
                )
            if item.kode_lokasi:
                location = await self._session.scalar(
                    text(
                        """
                        SELECT COUNT(*) FROM mslokasi
                        WHERE kodelokasi = :code
                          AND aktif = 'AKTIF'
                          AND COALESCE(DLT, '0') = '0'
                        """
                    ),
                    {"code": item.kode_lokasi},
                )
                if not location:
                    invalid.append(f"lokasi:{item.kode_lokasi}")
        return sorted(set(invalid))

    async def lock_and_get_fifo_balances(
        self, pairs: list[dict[str, str]]
    ) -> dict[tuple[str, str], Any]:
        balances: dict[tuple[str, str], Any] = {}
        for pair in pairs:
            await self._session.execute(
                text(
                    """
                    SELECT layer_id
                    FROM trfifo_layer
                    WHERE kodebarang = :kodebarang
                      AND kodelokasi = :kodelokasi
                      AND status_layer = 'OPEN' AND qty_sisa > 0
                    ORDER BY tanggal_masuk, layer_id
                    FOR UPDATE
                    """
                ),
                pair,
            )
            balance = await self._session.scalar(
                text(
                    """
                    SELECT COALESCE(SUM(qty_sisa), 0)
                    FROM trfifo_layer
                    WHERE kodebarang = :kodebarang
                      AND kodelokasi = :kodelokasi
                      AND status_layer = 'OPEN' AND qty_sisa > 0
                    """
                ),
                pair,
            )
            balances[(pair["kodebarang"], pair["kodelokasi"])] = balance
        return balances

    async def generate_kodeso(self, *, period: str, division: str) -> str:
        prefix = f"SO{period}{division.zfill(2)}"
        result = await self._session.execute(
            text(
                """
                SELECT kodeso
                FROM trsoheader
                WHERE kodeso LIKE :pattern
                ORDER BY CAST(RIGHT(kodeso, 6) AS UNSIGNED) DESC
                LIMIT 1
                FOR UPDATE
                """
            ),
            {"pattern": f"{prefix}%"},
        )
        previous = result.scalar_one_or_none()
        sequence = int(previous[-6:]) + 1 if previous else 1
        if sequence > 999999:
            raise ValueError("Nomor SO untuk periode/divisi ini sudah habis")
        return f"{prefix}{sequence:06d}"

    async def insert_shortage_order(
        self,
        *,
        header: dict[str, Any],
        details: list[dict[str, Any]],
    ) -> None:
        await self._session.execute(
            text(
                """
                INSERT INTO trsoheader (
                    kodeso, tanggalcreated, kodepelanggan, status,
                    biaya, subtotal, pajak, metodeppn, persenppn, total,
                    tanggaledit, kodesi, DLT, catatan, keterangan, tutup
                ) VALUES (
                    :kodeso, :now, :kode_pelanggan, 'ORDER',
                    :biaya, :subtotal, :pajak, :metode_ppn, :persen_ppn,
                    :total, :now, NULL, '0', :catatan, :keterangan, 0
                )
                """
            ),
            header,
        )
        for detail in details:
            await self._session.execute(
                text(
                    """
                    INSERT INTO trsodetail (
                        kodeso, kodebarang, qty, satuan, hargasatuan,
                        diskon1, diskon2, diskon3, potongan, hargatotal,
                        PPN, pajakdetail, kodeakunpenjualan, kodeakunhpp,
                        kodeakunpersediaan, konversi, hbkonversi,
                        kodelokasi, ID, urutan, DLT, status, kodesi
                    ) VALUES (
                        :kodeso, :kode_barang, :qty, :satuan, :harga_satuan,
                        :diskon1, :diskon2, :diskon3, :potongan,
                        :harga_total, :ppn, :pajak_detail,
                        :kode_akun_penjualan, :kode_akun_hpp,
                        :kode_akun_persediaan, :konversi,
                        :harga_beli_konversi, :kode_lokasi, :id,
                        :urutan, '0', '0', NULL
                    )
                    """
                ),
                detail,
            )

    async def add_audit(
        self, *, kodeso: str, user_code: str, user_name: str
    ) -> None:
        await self._session.execute(
            text(
                """
                INSERT INTO xyz (
                    kodepengguna, namapengguna, kodetransaksi,
                    status, jumlahedit, tanggal
                ) VALUES (
                    :user_code, :user_name, :kodeso,
                    'AUTO ORDER SHORTAGE', 0, NOW()
                )
                """
            ),
            {
                "user_code": user_code,
                "user_name": user_name,
                "kodeso": kodeso,
            },
        )

    async def get_for_sales(
        self, kodeso: str, *, kode_sales: str | None = None
    ) -> dict[str, Any] | None:
        sales_filter = "AND p.salescreated = :kode_sales" if kode_sales else ""
        header_result = await self._session.execute(
            text(
                f"""
                SELECT
                    h.kodeso,
                    h.tanggalcreated AS tanggal_created,
                    h.tanggalcreated AS tanggal_order,
                    h.tanggaledit AS tanggal_edit,
                    h.kodepelanggan AS kode_pelanggan,
                    p.namapelanggan AS nama_pelanggan,
                    h.status,
                    COALESCE(h.biaya, 0) AS biaya,
                    COALESCE(h.subtotal, 0) AS subtotal,
                    COALESCE(h.pajak, 0) AS pajak,
                    COALESCE(h.total, 0) AS total,
                    h.metodeppn AS metode_ppn,
                    h.persenppn AS persen_ppn,
                    h.catatan,
                    h.keterangan,
                    h.kodesi,
                    h.kodesi IS NOT NULL AND h.kodesi <> '' AS sudah_diproses,
                    h.tutup = 1 AS tutup
                FROM trsoheader AS h
                LEFT JOIN mspelanggan AS p
                    ON p.kodepelanggan = h.kodepelanggan
                WHERE h.kodeso = :kodeso AND COALESCE(h.DLT, '0') = '0'
                  {sales_filter}
                """
            ),
            {"kodeso": kodeso, "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.kodeakunpenjualan AS kode_akun_penjualan,
                    d.kodeakunhpp AS kode_akun_hpp,
                    d.kodeakunpersediaan AS kode_akun_persediaan,
                    d.konversi,
                    d.hbkonversi AS harga_beli_konversi,
                    d.kodelokasi AS kode_lokasi
                FROM trsodetail AS d
                LEFT JOIN msbarang AS b ON b.kodebarang = d.kodebarang
                WHERE d.kodeso = :kodeso AND COALESCE(d.DLT, '0') = '0'
                ORDER BY COALESCE(d.urutan, d.urut), d.ID
                """
            ),
            {"kodeso": kodeso},
        )
        return {
            **dict(header),
            "items": [dict(row) for row in detail_result.mappings().all()],
        }

    async def get_order(
        self, kodeso: str, *, kode_sales: str | None = None
    ) -> dict[str, Any] | None:
        return await self.get_for_sales(kodeso, kode_sales=kode_sales)

    async def list_orders(
        self,
        *,
        month: int,
        year: int,
        search: str | None = None,
        only_unlocked: bool = False,
        kode_sales: str | None = None,
        limit: int = 25,
        offset: int = 0,
    ) -> tuple[list[dict[str, Any]], int]:
        period_start = date(year, month, 1)
        period_end = (
            date(year + 1, 1, 1)
            if month == 12
            else date(year, month + 1, 1)
        )
        parameters: dict[str, Any] = {
            "period_start": period_start,
            "period_end": period_end,
            "kode_sales": kode_sales,
        }
        where_clauses = [
            "COALESCE(h.DLT, '0') = '0'",
            "h.tanggalcreated >= :period_start",
            "h.tanggalcreated < :period_end",
        ]
        if only_unlocked:
            where_clauses.append("h.tutup = 0")
        if kode_sales:
            where_clauses.append("p.salescreated = :kode_sales")
        if search:
            where_clauses.append(
                "(h.kodeso LIKE :search OR p.namapelanggan LIKE :search)"
            )
            parameters["search"] = f"%{search}%"

        where_sql = " AND ".join(where_clauses)
        base_from = """
        FROM trsoheader AS h
        LEFT JOIN mspelanggan AS p ON p.kodepelanggan = h.kodepelanggan
        """

        count_result = await self._session.execute(
            text(f"SELECT COUNT(*) {base_from} WHERE {where_sql}"),
            parameters,
        )
        total = int(count_result.scalar_one())

        result = await self._session.execute(
            text(
                f"""
                SELECT
                    h.kodeso,
                    h.tanggalcreated AS tanggal_created,
                    p.namapelanggan AS nama_pelanggan,
                    h.status,
                    COALESCE(h.biaya, 0) AS biaya,
                    COALESCE(h.subtotal, 0) AS subtotal,
                    COALESCE(h.pajak, 0) AS pajak,
                    COALESCE(h.total, 0) AS total,
                    h.tanggaledit AS tanggal_edit,
                    h.kodesi,
                    h.catatan,
                    h.keterangan,
                    h.tutup = 1 AS tutup
                {base_from}
                WHERE {where_sql}
                ORDER BY h.tanggalcreated DESC, h.kodeso DESC
                LIMIT :limit OFFSET :offset
                """
            ),
            {**parameters, "limit": limit, "offset": offset},
        )
        return [dict(row) for row in result.mappings().all()], total

    async def is_period_closed(self, *, month: int, year: int) -> bool:
        count = await self._session.scalar(
            text(
                """
                SELECT COUNT(*) FROM mstutupbuku
                WHERE bulan = :month AND tahun = :year
                  AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"month": month, "year": year},
        )
        return bool(count)

    async def get_order_state(self, kodeso: str) -> dict[str, Any] | None:
        result = await self._session.execute(
            text(
                """
                SELECT
                    kodeso,
                    tanggaledit AS tanggal_edit,
                    tutup = 1 AS tutup,
                    kodesi,
                    COALESCE(total, 0) AS total,
                    tanggalcreated AS tanggal_created
                FROM trsoheader
                WHERE kodeso = :kodeso AND COALESCE(DLT, '0') = '0'
                FOR UPDATE
                """
            ),
            {"kodeso": kodeso},
        )
        row = result.mappings().one_or_none()
        return dict(row) if row else None

    async def get_customer_credit(self, kode_pelanggan: str) -> dict[str, Any] | None:
        result = await self._session.execute(
            text(
                """
                SELECT
                    COALESCE(limitpiutang, 0) AS limit_piutang,
                    COALESCE(piutang, 0) AS piutang
                FROM mspelanggan
                WHERE kodepelanggan = :code
                  AND aktif = 'AKTIF'
                  AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"code": kode_pelanggan},
        )
        row = result.mappings().one_or_none()
        return dict(row) if row else None

    async def invalid_command_references(
        self,
        *,
        kode_pelanggan: str,
        items: list[Any],
    ) -> list[str]:
        invalid: list[str] = []
        customer = await self._session.scalar(
            text(
                """
                SELECT COUNT(*) FROM mspelanggan
                WHERE kodepelanggan = :code
                  AND aktif = 'AKTIF' AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"code": kode_pelanggan},
        )
        if not customer:
            invalid.append(f"pelanggan:{kode_pelanggan}")
        for item in items:
            product_unit = await self._session.scalar(
                text(
                    """
                    SELECT COUNT(*)
                    FROM msbarang AS b
                    INNER JOIN msbarangkonversi AS k
                        ON k.kodebarang = b.kodebarang
                    WHERE b.kodebarang = :kode_barang
                      AND k.satuan = :satuan
                      AND k.konversi = :konversi
                      AND b.aktif = 'AKTIF'
                      AND COALESCE(b.DLT, '0') = '0'
                      AND k.DLT = '0'
                    """
                ),
                {
                    "kode_barang": item.kode_barang,
                    "satuan": item.satuan,
                    "konversi": item.konversi,
                },
            )
            if not product_unit:
                invalid.append(
                    f"barang-satuan:{item.kode_barang}/{item.satuan}"
                )
            if item.kode_lokasi:
                location = await self._session.scalar(
                    text(
                        """
                        SELECT COUNT(*) FROM mslokasi
                        WHERE kodelokasi = :code
                          AND aktif = 'AKTIF'
                          AND COALESCE(DLT, '0') = '0'
                        """
                    ),
                    {"code": item.kode_lokasi},
                )
                if not location:
                    invalid.append(f"lokasi:{item.kode_lokasi}")
        return sorted(set(invalid))

    async def create_order(
        self,
        *,
        header: dict[str, Any],
        details: list[dict[str, Any]],
        user_code: str,
        user_name: str,
    ) -> None:
        await self._session.execute(
            text(
                """
                INSERT INTO trsoheader (
                    kodeso, tanggalcreated, kodepelanggan, status,
                    biaya, subtotal, pajak, metodeppn, persenppn, total,
                    tanggaledit, kodesi, DLT, catatan, keterangan, tutup
                ) VALUES (
                    :kodeso, :now, :kode_pelanggan, 'ORDER',
                    :biaya, :subtotal, :pajak, :metode_ppn, :persen_ppn,
                    :total, :now, NULL, '0', :catatan, :keterangan, 0
                )
                """
            ),
            header,
        )
        for detail in details:
            await self._session.execute(
                text(
                    """
                    INSERT INTO trsodetail (
                        kodeso, kodebarang, qty, satuan, hargasatuan,
                        diskon1, diskon2, diskon3, potongan, hargatotal,
                        PPN, pajakdetail, kodeakunpenjualan, kodeakunhpp,
                        kodeakunpersediaan, konversi, hbkonversi,
                        kodelokasi, ID, urutan, DLT, status, kodesi
                    ) VALUES (
                        :kodeso, :kode_barang, :qty, :satuan, :harga_satuan,
                        :diskon1, :diskon2, :diskon3, :potongan,
                        :harga_total, :ppn, :pajak_detail,
                        :kode_akun_penjualan, :kode_akun_hpp,
                        :kode_akun_persediaan, :konversi,
                        :harga_beli_konversi, :kode_lokasi, :id,
                        :urutan, '0', '0', NULL
                    )
                    """
                ),
                detail,
            )
        await self._session.execute(
            text(
                """
                INSERT INTO xyz (
                    kodepengguna, namapengguna, kodetransaksi,
                    status, jumlahedit, tanggal
                ) VALUES (
                    :user_code, :user_name, :kodeso,
                    'SIMPAN ORDER PENJUALAN', 0, :now
                )
                """
            ),
            {
                "user_code": user_code,
                "user_name": user_name,
                "kodeso": header["kodeso"],
                "now": header["now"],
            },
        )

    async def update_order(
        self,
        *,
        kodeso: str,
        header: dict[str, Any],
        details: list[dict[str, Any]],
        user_code: str,
        user_name: str,
        edit_count: int,
    ) -> None:
        await self._session.execute(
            text(
                """
                UPDATE trsoheader
                SET kodepelanggan = :kode_pelanggan,
                    biaya = :biaya,
                    subtotal = :subtotal,
                    pajak = :pajak,
                    metodeppn = :metode_ppn,
                    persenppn = :persen_ppn,
                    total = :total,
                    tanggaledit = :now,
                    catatan = :catatan,
                    keterangan = :keterangan
                WHERE kodeso = :kodeso AND COALESCE(DLT, '0') = '0'
                """
            ),
            {**header, "kodeso": kodeso},
        )
        await self._session.execute(
            text(
                """
                DELETE FROM trsodetail
                WHERE kodeso = :kodeso
                """
            ),
            {"kodeso": kodeso},
        )
        for detail in details:
            await self._session.execute(
                text(
                    """
                    INSERT INTO trsodetail (
                        kodeso, kodebarang, qty, satuan, hargasatuan,
                        diskon1, diskon2, diskon3, potongan, hargatotal,
                        PPN, pajakdetail, kodeakunpenjualan, kodeakunhpp,
                        kodeakunpersediaan, konversi, hbkonversi,
                        kodelokasi, ID, urutan, DLT, status, kodesi
                    ) VALUES (
                        :kodeso, :kode_barang, :qty, :satuan, :harga_satuan,
                        :diskon1, :diskon2, :diskon3, :potongan,
                        :harga_total, :ppn, :pajak_detail,
                        :kode_akun_penjualan, :kode_akun_hpp,
                        :kode_akun_persediaan, :konversi,
                        :harga_beli_konversi, :kode_lokasi, :id,
                        :urutan, '0', '0', NULL
                    )
                    """
                ),
                detail,
            )
        await self._session.execute(
            text(
                """
                INSERT INTO xyz (
                    kodepengguna, namapengguna, kodetransaksi,
                    status, jumlahedit, tanggal
                ) VALUES (
                    :user_code, :user_name, :kodeso,
                    'EDIT ORDER PENJUALAN', :edit_count, :now
                )
                """
            ),
            {
                "user_code": user_code,
                "user_name": user_name,
                "kodeso": kodeso,
                "edit_count": edit_count + 1,
                "now": header["now"],
            },
        )

    async def delete_order(
        self,
        *,
        kodeso: str,
        user_code: str,
        user_name: str,
    ) -> None:
        await self._session.execute(
            text(
                """
                DELETE FROM trsodetail
                WHERE kodeso = :kodeso
                """
            ),
            {"kodeso": kodeso},
        )
        await self._session.execute(
            text(
                """
                DELETE FROM trsoheader
                WHERE kodeso = :kodeso
                """
            ),
            {"kodeso": kodeso},
        )
        await self._session.execute(
            text(
                """
                INSERT INTO xyz (
                    kodepengguna, namapengguna, kodetransaksi,
                    status, jumlahedit, tanggal
                ) VALUES (
                    :user_code, :user_name, :kodeso,
                    'HAPUS ORDER PENJUALAN', 0, NOW()
                )
                """
            ),
            {
                "user_code": user_code,
                "user_name": user_name,
                "kodeso": kodeso,
            },
        )

    async def get_order_history(self, kodeso: str) -> list[dict[str, Any]]:
        result = await self._session.execute(
            text(
                """
                SELECT
                    kodepengguna,
                    namapengguna,
                    kodetransaksi,
                    status,
                    jumlahedit,
                    tanggal
                FROM xyz
                WHERE kodetransaksi = :kodeso
                ORDER BY tanggal DESC
                """
            ),
            {"kodeso": kodeso},
        )
        return [dict(row) for row in result.mappings().all()]

    async def get_order_audit_count(self, kodeso: str) -> int:
        result = await self._session.scalar(
            text(
                """
                SELECT COALESCE(MAX(jumlahedit), 0)
                FROM xyz
                WHERE kodetransaksi = :kodeso
                """
            ),
            {"kodeso": kodeso},
        )
        return int(result or 0)