from typing import Any

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


class SalesFifoRepository:
    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', 'trfifo_layer',
                    'trfifo_consumption', 'trstock_movement',
                    'msstok_saldo_fifo', 'xyz'
                  )
                  AND engine <> 'InnoDB'
                ORDER BY table_name
                """
            )
        )
        return [str(value) for value in result.scalars().all()]

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

    async def lock_document_layers(self, kodesi: str) -> None:
        await self.session.execute(
            text(
                """
                SELECT l.layer_id
                FROM trfifo_layer AS l
                INNER JOIN (
                    SELECT DISTINCT
                        kodebarang, COALESCE(kodelokasi, '') AS kodelokasi
                    FROM trsidetail
                    WHERE kodesi = :kodesi AND COALESCE(DLT, '0') = '0'
                ) AS d
                    ON d.kodebarang = l.kodebarang
                   AND d.kodelokasi = l.kodelokasi
                WHERE l.status_layer = 'OPEN' AND l.qty_sisa > 0
                ORDER BY l.kodebarang, l.kodelokasi,
                         l.tanggal_masuk, l.layer_id
                FOR UPDATE
                """
            ),
            {"kodesi": kodesi},
        )

    async def get_document_shortages(
        self, kodesi: str
    ) -> list[dict[str, Any]]:
        result = await self.session.execute(
            text(
                """
                SELECT
                    required.kodebarang,
                    required.kodelokasi,
                    required.qty_required,
                    GREATEST(
                        required.qty_required
                        - COALESCE(available.qty_available, 0)
                        - COALESCE(own.qty_consumed, 0),
                        0
                    ) AS qty_shortage,
                    COALESCE(
                        NULLIF(own.hpp_unit, 0),
                        NULLIF(available.hpp_unit, 0)
                    ) AS hpp_unit
                FROM (
                    SELECT
                        kodebarang,
                        COALESCE(kodelokasi, '') AS kodelokasi,
                        SUM(COALESCE(qty, 0)
                            * COALESCE(NULLIF(konversi, 0), 1)) AS qty_required
                    FROM trsidetail
                    WHERE kodesi = :kodesi AND COALESCE(DLT, '0') = '0'
                    GROUP BY kodebarang, COALESCE(kodelokasi, '')
                ) AS required
                LEFT JOIN (
                    SELECT
                        kodebarang,
                        kodelokasi,
                        SUM(qty_sisa) AS qty_available,
                        SUBSTRING_INDEX(
                            GROUP_CONCAT(
                                hpp_unit ORDER BY tanggal_masuk DESC, layer_id DESC
                            ), ',', 1
                        ) + 0 AS hpp_unit
                    FROM trfifo_layer
                    WHERE status_layer = 'OPEN' AND qty_sisa > 0
                    GROUP BY kodebarang, kodelokasi
                ) AS available
                    ON available.kodebarang = required.kodebarang
                   AND available.kodelokasi = required.kodelokasi
                LEFT JOIN (
                    SELECT
                        kodebarang,
                        kodelokasi,
                        SUM(qty_pakai) AS qty_consumed,
                        SUM(nilai_hpp) / NULLIF(SUM(qty_pakai), 0) AS hpp_unit
                    FROM trfifo_consumption
                    WHERE out_type = 'PENJUALAN' AND out_no = :kodesi
                    GROUP BY kodebarang, kodelokasi
                ) AS own
                    ON own.kodebarang = required.kodebarang
                   AND own.kodelokasi = required.kodelokasi
                HAVING qty_shortage > 0
                ORDER BY required.kodebarang, required.kodelokasi
                """
            ),
            {"kodesi": kodesi},
        )
        return [dict(row) for row in result.mappings().all()]

    async def insert_developer_layers(
        self,
        *,
        kodesi: str,
        shortages: list[dict[str, Any]],
        user_code: str,
    ) -> None:
        for shortage in shortages:
            await self.session.execute(
                text(
                    """
                    INSERT INTO trfifo_layer (
                        tanggal_masuk, kodebarang, kodelokasi,
                        source_type, source_no, source_detail_id,
                        qty_awal, qty_sisa, hpp_unit, status_layer,
                        created_at, created_by
                    )
                    SELECT
                        h.tanggalfaktur,
                        :kodebarang,
                        :kodelokasi,
                        'PENJUALAN-DEV',
                        h.kodesi,
                        NULL,
                        :qty_shortage,
                        :qty_shortage,
                        :hpp_unit,
                        'OPEN',
                        NOW(),
                        :user_code
                    FROM trsiheader AS h
                    WHERE h.kodesi = :kodesi AND COALESCE(h.DLT, '0') = '0'
                    """
                ),
                {**shortage, "kodesi": kodesi, "user_code": user_code},
            )

    async def sync_detail_hpp(self, kodesi: str) -> None:
        await self.session.execute(
            text(
                """
                UPDATE trsidetail AS d
                INNER JOIN (
                    SELECT out_detail_id, SUM(nilai_hpp) AS nilai_hpp
                    FROM trfifo_consumption
                    WHERE out_type = 'PENJUALAN' AND out_no = :kodesi
                    GROUP BY out_detail_id
                ) AS c ON c.out_detail_id = d.ID
                SET d.hbkonversi = c.nilai_hpp / NULLIF(d.qty, 0)
                WHERE d.kodesi = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )

    async def sync_balances(self, *, kodesi: str, user_code: str) -> None:
        pairs = await self.get_document_pairs(kodesi)
        await self.sync_balance_pairs(pairs=pairs, user_code=user_code)

    async def get_document_pairs(self, kodesi: str) -> list[dict[str, str]]:
        result = await self.session.execute(
            text(
                """
                SELECT DISTINCT
                    kodebarang,
                    COALESCE(kodelokasi, '') AS kodelokasi
                FROM trsidetail
                WHERE kodesi = :kodesi AND COALESCE(DLT, '0') = '0'
                """
            ),
            {"kodesi": kodesi},
        )
        return [dict(row) for row in result.mappings().all()]

    async def sync_balance_pairs(
        self, *, pairs: list[dict[str, str]], user_code: str
    ) -> None:
        for pair in pairs:
            await self.session.execute(
                text(
                    """
                    INSERT INTO msstok_saldo_fifo (
                        kodebarang, kodelokasi, qty_saldo, nilai_saldo,
                        hpp_rata, updated_at, updated_by
                    )
                    SELECT
                        :kodebarang,
                        :kodelokasi,
                        COALESCE(SUM(CASE WHEN qty_sisa > 0
                            THEN qty_sisa ELSE 0 END), 0),
                        COALESCE(SUM(CASE WHEN qty_sisa > 0
                            THEN qty_sisa * hpp_unit ELSE 0 END), 0),
                        CASE
                            WHEN COALESCE(SUM(CASE WHEN qty_sisa > 0
                                THEN qty_sisa ELSE 0 END), 0) > 0
                            THEN SUM(CASE WHEN qty_sisa > 0
                                THEN qty_sisa * hpp_unit ELSE 0 END)
                                / SUM(CASE WHEN qty_sisa > 0
                                    THEN qty_sisa ELSE 0 END)
                            ELSE 0
                        END,
                        NOW(),
                        :user_code
                    FROM trfifo_layer
                    WHERE kodebarang = :kodebarang
                      AND kodelokasi = :kodelokasi
                    ON DUPLICATE KEY UPDATE
                        qty_saldo = VALUES(qty_saldo),
                        nilai_saldo = VALUES(nilai_saldo),
                        hpp_rata = VALUES(hpp_rata),
                        updated_at = VALUES(updated_at),
                        updated_by = VALUES(updated_by)
                    """
                ),
                {**pair, "user_code": user_code},
            )

    async def rollback(self, *, kodesi: str, user_code: str) -> None:
        await self.session.execute(
            text(
                """
                UPDATE trfifo_layer AS l
                INNER JOIN (
                    SELECT layer_id, SUM(qty_pakai) AS qty_pakai
                    FROM trfifo_consumption
                    WHERE out_type = 'PENJUALAN' AND out_no = :kodesi
                    GROUP BY layer_id
                ) AS c ON c.layer_id = l.layer_id
                SET l.qty_sisa = l.qty_sisa + c.qty_pakai,
                    l.status_layer = CASE
                        WHEN l.qty_sisa + c.qty_pakai > 0 THEN 'OPEN'
                        ELSE 'CLOSED'
                    END,
                    l.updated_at = NOW(),
                    l.updated_by = :user_code
                """
            ),
            {"kodesi": kodesi, "user_code": user_code},
        )
        await self.session.execute(
            text(
                """
                DELETE FROM trfifo_consumption
                WHERE out_type = 'PENJUALAN' AND out_no = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )
        await self.session.execute(
            text(
                """
                DELETE FROM trstock_movement
                WHERE transaksi_type = 'PENJUALAN'
                  AND transaksi_no = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )
        await self.session.execute(
            text(
                """
                UPDATE trsidetail
                SET hbkonversi = 0, tutuphpp = '0'
                WHERE kodesi = :kodesi
                """
            ),
            {"kodesi": kodesi},
        )

    async def get_header_for_update(
        self, kodesi: str
    ) -> dict[str, Any] | None:
        result = await self.session.execute(
            text(
                """
                SELECT kodesi, 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 set_locked(self, *, kodesi: str, locked: bool) -> None:
        value = "1" if locked else "0"
        await self.session.execute(
            text("UPDATE trsiheader SET tutup=:value WHERE kodesi=:kodesi"),
            {"value": value, "kodesi": kodesi},
        )
        await self.session.execute(
            text(
                """
                UPDATE trsidetail SET tutuphpp=:value WHERE kodesi=:kodesi
                """
            ),
            {"value": value, "kodesi": kodesi},
        )

    async def get_batch_for_update(
        self,
        *,
        tanggal_mulai: Any,
        tanggal_selesai: Any,
        kode_divisi: str | None,
    ) -> list[str]:
        result = await self.session.execute(
            text(
                """
                SELECT kodesi
                FROM trsiheader
                WHERE DLT = '0'
                  AND tutup = '0'
                  AND tanggalfaktur BETWEEN :tanggal_mulai AND :tanggal_selesai
                  AND (:kode_divisi IS NULL OR kodedivisi = :kode_divisi)
                ORDER BY tanggalfaktur, kodesi
                FOR UPDATE
                """
            ),
            {
                "tanggal_mulai": tanggal_mulai,
                "tanggal_selesai": tanggal_selesai,
                "kode_divisi": kode_divisi,
            },
        )
        return [str(value) for value in result.scalars().all()]

    async def add_audit(
        self,
        *,
        user_code: str,
        user_name: str,
        kodesi: 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,
            },
        )