from datetime import datetime
from typing import Any

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

from app.sales.schemas import SalesCommandRequest


REQUIRED_TRANSACTIONAL_TABLES = (
    "trsiheader",
    "trsidetail",
    "trsoheader",
    "trsodetail",
)


class SalesCommandRepository:
    def __init__(self, session: AsyncSession) -> None:
        self.session = 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 (
                    'trsiheader', 'trsidetail', 'trsoheader', 'trsodetail'
                  )
                  AND engine <> 'InnoDB'
                ORDER BY table_name
                """
            )
        )
        return [str(value) for value in result.scalars().all()]

    async def invalid_references(
        self, request: SalesCommandRequest
    ) -> 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}")

        salesperson = await self.session.scalar(
            text(
                """
                SELECT COUNT(*) FROM mssales
                WHERE kodesales = :code
                  AND aktif = 'AKTIF' AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"code": request.kode_sales},
        )
        if not salesperson:
            invalid.append(f"sales:{request.kode_sales}")

        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 generate_kodesi(self, *, period: str, division: str) -> str:
        prefix = f"SI{period}{division.zfill(2)}"
        result = await self.session.execute(
            text(
                """
                SELECT kodesi
                FROM trsiheader
                WHERE kodesi LIKE :period_pattern
                ORDER BY CAST(RIGHT(kodesi, 6) AS UNSIGNED) DESC
                LIMIT 1
                FOR UPDATE
                """
            ),
            {"period_pattern": f"{prefix}%"},
        )
        previous = result.scalar_one_or_none()
        sequence = int(previous[-6:]) + 1 if previous else 1
        if sequence > 999999:
            raise ValueError("Nomor SI untuk periode ini sudah habis")
        return f"{prefix}{sequence:06d}"

    async def lock_header(self, kodesi: str) -> dict[str, Any] | None:
        result = await self.session.execute(
            text(
                """
                SELECT kodesi, tanggaledit AS tanggal_edit, tutup
                FROM trsiheader
                WHERE kodesi = :kodesi AND DLT = '0'
                FOR UPDATE
                """
            ),
            {"kodesi": kodesi},
        )
        row = result.mappings().one_or_none()
        return dict(row) if row else None

    async def insert_header(self, values: dict[str, Any]) -> None:
        await self.session.execute(
            text(
                """
                INSERT INTO trsiheader (
                    kodesi, tanggalcreated, kodepelanggan, kodesales, status,
                    biaya, tanggaledit, kodeakunbiaya, kodeakunpajak,
                    metodeppn, persenppn, tanggalfaktur, tanggaljatuhtempo,
                    kodeakuntotal, kodedivisi, DLT, keterangan, koderute, tutup
                ) VALUES (
                    :kodesi, :now, :kode_pelanggan, :kode_sales, 'KREDIT',
                    :biaya, :now, :kode_akun_biaya, :kode_akun_pajak,
                    :metode_ppn, :persen_ppn, :tanggal_faktur,
                    :tanggal_jatuh_tempo, :kode_akun_total, :kode_divisi,
                    '0', :keterangan, :kode_rute, '0'
                )
                """
            ),
            values,
        )

    async def update_header(self, values: dict[str, Any]) -> bool:
        result = await self.session.execute(
            text(
                """
                UPDATE trsiheader
                SET kodepelanggan = :kode_pelanggan,
                    kodesales = :kode_sales,
                    biaya = :biaya,
                    tanggaledit = :now,
                    kodeakunbiaya = :kode_akun_biaya,
                    kodeakunpajak = :kode_akun_pajak,
                    metodeppn = :metode_ppn,
                    persenppn = :persen_ppn,
                    tanggalfaktur = :tanggal_faktur,
                    tanggaljatuhtempo = :tanggal_jatuh_tempo,
                    kodeakuntotal = :kode_akun_total,
                    kodedivisi = :kode_divisi,
                    keterangan = :keterangan,
                    koderute = :kode_rute
                WHERE kodesi = :kodesi
                  AND DLT = '0'
                  AND tutup = '0'
                  AND tanggaledit = :expected_tanggal_edit
                """
            ),
            values,
        )
        return result.rowcount == 1

    async def replace_details(
        self, *, kodesi: str, details: list[dict[str, Any]]
    ) -> None:
        await self.session.execute(
            text("DELETE FROM trsidetail WHERE kodesi = :kodesi"),
            {"kodesi": kodesi},
        )
        await self.session.execute(
            text(
                """
                INSERT INTO trsidetail (
                    kodesi, kodebarang, qty, satuan, hargasatuan, konversi,
                    diskon1, diskon2, diskon3, potongan, hargatotal, PPN,
                    pajakdetail, kodeakunpersediaan, kodeakunpotongan,
                    kodeakunpenjualan, kodeakunhpp, hbkonversi, kodelokasi,
                    ID, urutan, DLT, tutuphpp
                ) VALUES (
                    :kodesi, :kode_barang, :qty, :satuan, :harga_satuan,
                    :konversi, :diskon1, :diskon2, :diskon3, :potongan,
                    :harga_total, :ppn, :pajak_detail,
                    :kode_akun_persediaan, :kode_akun_potongan,
                    :kode_akun_penjualan, :kode_akun_hpp,
                    :harga_beli_konversi, :kode_lokasi, :id, :urutan, '0', '0'
                )
                """
            ),
            [{**detail, "kodesi": kodesi} for detail in details],
        )

    async def link_sales_order(self, *, kodeso: str, kodesi: str) -> bool:
        result = await self.session.execute(
            text(
                """
                UPDATE trsoheader
                SET kodesi = :kodesi, tanggaledit = NOW()
                WHERE kodeso = :kodeso
                  AND COALESCE(DLT, '0') = '0'
                  AND tutup = 0
                  AND (kodesi IS NULL OR kodesi = '' OR kodesi = :kodesi)
                """
            ),
            {"kodeso": kodeso, "kodesi": kodesi},
        )
        if result.rowcount != 1:
            return False
        await self.session.execute(
            text(
                """
                UPDATE trsodetail
                SET kodesi = :kodesi
                WHERE kodeso = :kodeso AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"kodeso": kodeso, "kodesi": kodesi},
        )
        return True

    async def unlink_sales_orders(self, kodesi: str) -> None:
        await self.session.execute(
            text(
                """
                UPDATE trsoheader
                SET kodesi = '', tanggaledit = NOW()
                WHERE kodesi = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )
        await self.unlink_sales_order_details(kodesi)

    async def unlink_sales_order_details(self, kodesi: str) -> None:
        await self.session.execute(
            text(
                """
                UPDATE trsodetail
                SET kodesi = ''
                WHERE kodesi = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )

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

    async def next_edit_count(self, kodesi: str) -> int:
        result = await self.session.execute(
            text(
                """
                SELECT COALESCE(MAX(jumlahedit), 0) + 1
                FROM xyz
                WHERE kodetransaksi = :kodesi
                  AND status = 'EDIT PENJUALAN'
                """
            ),
            {"kodesi": kodesi},
        )
        return int(result.scalar_one())

    async def delete_via_procedure(self, *, kodesi: str, user_code: str) -> None:
        await self.session.execute(
            text("CALL sp_fifo_delete_penjualan(:kodesi, :user_code)"),
            {"kodesi": kodesi, "user_code": user_code},
        )