from datetime import date
from typing import Any

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


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

    async def latest_sequence(self, prefix: str) -> int:
        value = await self.session.scalar(
            text(
                """
                SELECT COALESCE(MAX(CAST(RIGHT(kodesi, 6) AS UNSIGNED)), 0)
                FROM trsiheader
                WHERE kodesi LIKE :pattern
                """
            ),
            {"pattern": f"{prefix}%"},
        )
        return int(value or 0)

    async def sale_exists(self, kodesi: str) -> bool:
        value = await self.session.scalar(
            text(
                """
                SELECT COUNT(*) FROM trsiheader
                WHERE kodesi=:kodesi AND COALESCE(DLT, '0')='0'
                """
            ),
            {"kodesi": kodesi},
        )
        return bool(value)

    async def reusable_numbers(self, period_prefix: str) -> list[dict[str, Any]]:
        result = await self.session.execute(
            text(
                """
                SELECT
                    x.kodetransaksi AS kodesi,
                    MAX(x.tanggal) AS deleted_at,
                    MAX(x.namapengguna) AS deleted_by
                FROM xyz AS x
                LEFT JOIN trsiheader AS h
                    ON h.kodesi = x.kodetransaksi
                   AND COALESCE(h.DLT, '0') = '0'
                WHERE x.status LIKE 'HAPUS PENJUALAN%'
                  AND x.kodetransaksi LIKE :pattern
                  AND h.kodesi IS NULL
                GROUP BY x.kodetransaksi
                ORDER BY x.kodetransaksi
                """
            ),
            {"pattern": f"SI{period_prefix}%"},
        )
        return [dict(row) for row in result.mappings().all()]

    async def used_numbers(
        self, *, period_prefix: str, division: str | None
    ) -> list[str]:
        pattern = (
            f"SI{period_prefix}{division}%"
            if division is not None
            else f"SI{period_prefix}%"
        )
        result = await self.session.execute(
            text(
                """
                SELECT kodesi
                FROM trsiheader
                WHERE kodesi LIKE :pattern
                ORDER BY kodesi
                """
            ),
            {"pattern": pattern},
        )
        return [str(value) for value in result.scalars().all()]

    async def fifo_shortages(
        self, *, tanggal_mulai: date, tanggal_selesai: date
    ) -> list[dict[str, Any]]:
        result = await self.session.execute(
            text(
                """
                SELECT
                    h.kodesi,
                    h.tanggalfaktur AS tanggal_faktur,
                    d.kodebarang AS kode_barang,
                    b.namabarang AS nama_barang,
                    COALESCE(d.kodelokasi, '') AS kode_lokasi,
                    d.qty * COALESCE(NULLIF(d.konversi, 0), 1)
                        AS qty_required,
                    COALESCE(SUM(c.qty_pakai), 0) AS qty_posted,
                    GREATEST(
                        d.qty * COALESCE(NULLIF(d.konversi, 0), 1)
                        - COALESCE(SUM(c.qty_pakai), 0),
                        0
                    ) AS qty_shortage,
                    h.tutup = '1' AS tutup
                FROM trsiheader AS h
                INNER JOIN trsidetail AS d
                    ON d.kodesi = h.kodesi AND COALESCE(d.DLT, '0') = '0'
                LEFT JOIN msbarang AS b ON b.kodebarang = d.kodebarang
                LEFT JOIN trfifo_consumption AS c
                    ON c.out_type = 'PENJUALAN'
                   AND c.out_no = h.kodesi
                   AND c.out_detail_id = d.ID
                WHERE COALESCE(h.DLT, '0') = '0'
                  AND h.tanggalfaktur BETWEEN :tanggal_mulai AND :tanggal_selesai
                GROUP BY
                    h.kodesi, h.tanggalfaktur, d.ID, d.kodebarang,
                    b.namabarang, d.kodelokasi, d.qty, d.konversi, h.tutup
                HAVING qty_shortage > 0
                ORDER BY h.tanggalfaktur, h.kodesi, d.urutan, d.ID
                """
            ),
            {
                "tanggal_mulai": tanggal_mulai,
                "tanggal_selesai": tanggal_selesai,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    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 ('trsiheader', 'xyz')
                  AND engine <> 'InnoDB'
                ORDER BY table_name
                """
            )
        )
        return [str(value) for value in result.scalars().all()]

    async def reconcile_candidates(
        self, *, tanggal_mulai: date, tanggal_selesai: date
    ) -> list[dict[str, Any]]:
        result = await self.session.execute(
            text(
                """
                SELECT
                    h.kodesi,
                    h.status AS status_sekarang,
                    CASE
                        WHEN v.total - COALESCE(p.returan, 0)
                             - COALESCE(p.bayar, 0) <= 0
                        THEN 'LUNAS' ELSE 'KREDIT'
                    END AS status_seharusnya,
                    v.total AS total_faktur,
                    COALESCE(p.returan, 0) AS total_retur,
                    COALESCE(p.bayar, 0) AS total_bayar,
                    v.total - COALESCE(p.returan, 0)
                        - COALESCE(p.bayar, 0) AS sisa
                FROM trsiheader AS h
                INNER JOIN vwsiheader AS v ON v.kodesi = h.kodesi
                LEFT JOIN vwpiutang2 AS p ON p.kodesi = h.kodesi
                WHERE COALESCE(h.DLT, '0') = '0'
                  AND h.tanggalfaktur BETWEEN :tanggal_mulai AND :tanggal_selesai
                HAVING status_sekarang <> status_seharusnya
                ORDER BY h.tanggalfaktur, h.kodesi
                """
            ),
            {
                "tanggal_mulai": tanggal_mulai,
                "tanggal_selesai": tanggal_selesai,
            },
        )
        return [dict(row) for row in result.mappings().all()]

    async def apply_reconcile(
        self,
        *,
        rows: list[dict[str, Any]],
        user_code: str,
        user_name: str,
    ) -> int:
        updated = 0
        for row in rows:
            result = await self.session.execute(
                text(
                    """
                    UPDATE trsiheader
                    SET status = :status
                    WHERE kodesi = :kodesi AND COALESCE(DLT, '0') = '0'
                    """
                ),
                {
                    "status": row["status_seharusnya"],
                    "kodesi": row["kodesi"],
                },
            )
            if result.rowcount:
                updated += 1
                await self.add_audit(
                    kodesi=row["kodesi"],
                    user_code=user_code,
                    user_name=user_name,
                    audit_status=(
                        f"RECONCILE STATUS {row['status_sekarang']}"
                        f"->{row['status_seharusnya']}"
                    ),
                )
        return updated

    async def update_expedition(
        self, *, kodesi: str, ekspedisi: str, tanggal_ekspedisi: date
    ) -> bool:
        result = await self.session.execute(
            text(
                """
                UPDATE trsiheader
                SET EKSPEDISI=:ekspedisi, tanggalekspedisi=:tanggal
                WHERE kodesi=:kodesi AND COALESCE(DLT, '0')='0'
                """
            ),
            {
                "kodesi": kodesi,
                "ekspedisi": ekspedisi,
                "tanggal": tanggal_ekspedisi,
            },
        )
        return bool(result.rowcount)

    async def update_packages(
        self, *, kodesi: str, jumlah_koli: int, tanggal_kemas: date
    ) -> bool:
        result = await self.session.execute(
            text(
                """
                UPDATE trsiheader
                SET jumlahcoli=:jumlah, tanggalkemas=:tanggal
                WHERE kodesi=:kodesi AND COALESCE(DLT, '0')='0'
                """
            ),
            {
                "kodesi": kodesi,
                "jumlah": jumlah_koli,
                "tanggal": tanggal_kemas,
            },
        )
        return bool(result.rowcount)

    async def add_audit(
        self,
        *,
        kodesi: str,
        user_code: str,
        user_name: str,
        audit_status: str,
    ) -> None:
        await self.session.execute(
            text(
                """
                INSERT INTO xyz (
                    kodepengguna, namapengguna, kodetransaksi,
                    status, jumlahedit, tanggal
                ) VALUES (
                    :user_code, :user_name, :kodesi,
                    :audit_status, 0, NOW()
                )
                """
            ),
            {
                "user_code": user_code,
                "user_name": user_name,
                "kodesi": kodesi,
                "audit_status": audit_status,
            },
        )

    async def history(self, kodesi: str) -> dict[str, list[dict[str, Any]]]:
        audit = await self.session.execute(
            text(
                """
                SELECT kodepengguna AS kode_pengguna,
                       namapengguna AS nama_pengguna,
                       status, jumlahedit AS jumlah_edit, tanggal
                FROM xyz WHERE kodetransaksi=:kodesi
                ORDER BY tanggal, jumlahedit
                """
            ),
            {"kodesi": kodesi},
        )
        payments = await self.session.execute(
            text(
                """
                SELECT d.kodesh, h.tanggal, d.totalfaktur AS total_faktur,
                       d.returan, d.potongan, d.bayar,
                       d.kodeakunbayar AS kode_akun_bayar,
                       d.remarknamaakunbayar AS nama_akun_bayar,
                       h.collector, h.keterangan
                FROM trshdetail AS d
                INNER JOIN trshheader AS h ON h.kodesh=d.kodesh
                WHERE d.kodesi=:kodesi
                  AND COALESCE(d.DLT, '0')='0'
                  AND COALESCE(h.DLT, '0')='0'
                ORDER BY h.tanggal, d.ID
                """
            ),
            {"kodesi": kodesi},
        )
        edits = await self.session.execute(
            text(
                """
                SELECT h.kelompok, h.kodebarang AS kode_barang,
                       b.namabarang AS nama_barang, h.qty, h.satuan,
                       h.hargasatuan AS harga_satuan, h.diskon1, h.diskon2,
                       h.diskon3, h.potongan, h.hargatotal AS harga_total,
                       h.PPN AS ppn, h.pajakdetail AS pajak_detail,
                       h.konversi, h.hbkonversi AS hb_konversi,
                       h.kodelokasi AS kode_lokasi, h.ID AS id, h.urut
                FROM trsidetail_history AS h
                LEFT JOIN msbarang AS b ON b.kodebarang=h.kodebarang
                WHERE h.kodesi=:kodesi
                ORDER BY h.kelompok, h.urut, h.key
                """
            ),
            {"kodesi": kodesi},
        )
        final_details = await self.session.execute(
            text(
                """
                SELECT 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, d.ID AS id, d.urutan
                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 {
            "audit": [dict(row) for row in audit.mappings().all()],
            "payments": [dict(row) for row in payments.mappings().all()],
            "edits": [dict(row) for row in edits.mappings().all()],
            "final_details": [
                dict(row) for row in final_details.mappings().all()
            ],
        }

    async def print_data(
        self, *, kodesi: str, document_type: str
    ) -> dict[str, Any] | None:
        view = "vwinvoices2" if document_type == "invoice" else "vwinvoices"
        result = await self.session.execute(
            text(
                f"""
                SELECT * FROM {view}
                WHERE KODESI=:kodesi
                ORDER BY urut, id
                """
            ),
            {"kodesi": kodesi},
        )
        rows = [dict(row) for row in result.mappings().all()]
        if not rows:
            return None
        company_result = await self.session.execute(
            text(
                """
                SELECT namaperusahaan AS nama, alamatperusahaan AS alamat,
                       telpperusahaan AS telepon, emailperusahaan AS email,
                       npwpperusahaan AS npwp, footnote
                FROM msperusahaan ORDER BY id LIMIT 1
                """
            )
        )
        company = company_result.mappings().one_or_none()
        first = rows[0]
        header_keys = {
            "KODESI", "tanggalfaktur", "tanggaljatuhtempo",
            "kodepelanggan", "kodesales", "biaya", "subtotal", "pajak",
            "total", "namapelanggan", "alamat", "telp", "namasales",
            "jalursales", "kb", "keterangan", "footnote", "status",
        }
        item_keys = {
            "id", "ID", "kodebarang", "namabarang", "qty", "satuan",
            "hargasatuan", "diskon1", "diskon2", "diskon3", "potongan",
            "potonganc", "hargatotal", "ppn", "kodelokasi", "urut",
            "pajakdetail", "dppdetail", "hpp", "hpptotal",
        }
        return {
            "company": dict(company) if company else {},
            "header": {key.lower(): first.get(key) for key in header_keys
                       if key in first},
            "items": [
                {key.lower(): row.get(key) for key in item_keys if key in row}
                for row in rows
            ],
        }

    async def whatsapp_settings(self, kodesi: str) -> dict[str, Any]:
        result = await self.session.execute(
            text(
                """
                SELECT p.wa, s.headerwa, s.footerwa
                FROM trsiheader AS h
                INNER JOIN mspelanggan AS p
                    ON p.kodepelanggan=h.kodepelanggan
                LEFT JOIN mssetupwa AS s ON 1=1
                WHERE h.kodesi=:kodesi AND COALESCE(h.DLT, '0')='0'
                LIMIT 1
                """
            ),
            {"kodesi": kodesi},
        )
        row = result.mappings().one_or_none()
        return dict(row) if row else {}