from flask import (
    render_template,
    redirect,
    session,
    request,
    flash
)

from extensions import app, mysql
from datetime import datetime

import os
import uuid
import pandas as pd

from werkzeug.utils import secure_filename


# ==========================================================
# CEK AKSES KASIR
# ==========================================================
def cek_akses_kasir():

    if "login" not in session:
        return False

    if session.get("role") != "kasir":
        return False

    return True


# ==========================================================
# BUAT KODE TRANSAKSI KELUAR
# ==========================================================
def buat_kode_transaksi_keluar():

    prefix = datetime.now().strftime(
        "BK-%Y%m%d-"
    )

    cursor = mysql.connection.cursor()

    cursor.execute(
        """
        SELECT kode_transaksi
        FROM transaksi_keluar
        WHERE kode_transaksi LIKE %s
        ORDER BY id DESC
        LIMIT 1
        """,
        (prefix + "%",)
    )

    data = cursor.fetchone()

    cursor.close()


    if data:

        try:

            nomor_terakhir = int(
                data[0].split("-")[-1]
            )

            nomor_baru = (
                nomor_terakhir + 1
            )

        except Exception:

            nomor_baru = 1

    else:

        nomor_baru = 1


    return (
        f"{prefix}"
        f"{nomor_baru:04d}"
    )

# ==========================================================
# BARANG KELUAR - FORM MANUAL
# ==========================================================
@app.route("/kasir/barang-keluar", methods=["GET"])
def barang_keluar():

    if not cek_akses_kasir():
        return redirect("/login")

    cursor = mysql.connection.cursor()

    cursor.execute(
        """
        SELECT
            id,
            kode_barang,
            nama_barang,
            satuan_besar,
            satuan_kecil,
            isi_per_satuan,
            stok_kecil,
            harga_jual_besar,
            harga_jual_kecil
        FROM barang
        ORDER BY nama_barang ASC
        """
    )

    data_barang = cursor.fetchall()

    cursor.close()


    barang = []

    for item in data_barang:

        barang.append({
            "id": item[0],
            "kode_barang": item[1],
            "nama_barang": item[2],
            "satuan_besar": item[3],
            "satuan_kecil": item[4],
            "isi_per_satuan": int(item[5]),
            "stok_kecil": int(item[6]),
            "harga_jual_besar": float(item[7]),
            "harga_jual_kecil": float(item[8])
        })


    tanggal_hari_ini = datetime.now().strftime(
        "%Y-%m-%dT%H:%M"
    )


    return render_template(
        "kasir/barang_keluar.html",
        barang=barang,
        tanggal_hari_ini=tanggal_hari_ini
    )

# ==========================================================
# SIMPAN TRANSAKSI BARANG KELUAR
# ==========================================================
@app.route(
    "/kasir/barang-keluar/simpan",
    methods=["POST"]
)
def simpan_barang_keluar():

    if not cek_akses_kasir():
        return redirect("/login")


    # ======================================================
    # AMBIL HEADER TRANSAKSI
    # ======================================================
    tanggal = request.form.get(
        "tanggal",
        ""
    ).strip()

    nama_pelanggan = request.form.get(
        "nama_pelanggan",
        ""
    ).strip()

    keterangan = request.form.get(
        "keterangan",
        ""
    ).strip()


    # ======================================================
    # AMBIL DETAIL TRANSAKSI
    # ======================================================
    barang_ids = request.form.getlist(
        "barang_id[]"
    )

    jenis_satuans = request.form.getlist(
        "jenis_satuan[]"
    )

    jumlahs = request.form.getlist(
        "jumlah[]"
    )


    # ======================================================
    # BAYAR
    # ======================================================
    bayar_input = request.form.get(
        "bayar",
        "0"
    ).strip()


    # ======================================================
    # VALIDASI HEADER
    # ======================================================
    if not tanggal:

        flash(
            "Tanggal transaksi wajib diisi.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar"
        )


    # ======================================================
    # VALIDASI DETAIL
    # ======================================================
    if (
        not barang_ids
        or not jenis_satuans
        or not jumlahs
    ):

        flash(
            "Minimal satu barang harus dipilih.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar"
        )


    if not (
        len(barang_ids)
        == len(jenis_satuans)
        == len(jumlahs)
    ):

        flash(
            "Data transaksi tidak lengkap.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar"
        )


    # ======================================================
    # VALIDASI BAYAR
    # ======================================================
    try:

        bayar = float(
            bayar_input
        )

        if bayar < 0:
            raise ValueError()

    except Exception:

        flash(
            "Nominal pembayaran tidak valid.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar"
        )


    # ======================================================
    # MULAI PROSES DATABASE
    # ======================================================
    cursor = mysql.connection.cursor()

    detail_transaksi = []

    barang_dalam_transaksi = set()

    total = 0


    try:

        # ==================================================
        # VALIDASI SEMUA BARANG
        # ==================================================
        for index in range(
            len(barang_ids)
        ):

            # ----------------------------------------------
            # BARANG ID
            # ----------------------------------------------
            try:

                barang_id = int(
                    barang_ids[index]
                )

            except Exception:

                raise ValueError(
                    f"Barang pada baris "
                    f"{index + 1} tidak valid."
                )


            # ----------------------------------------------
            # CEK DUPLIKAT BARANG
            # ----------------------------------------------
            if barang_id in barang_dalam_transaksi:

                raise ValueError(
                    "Barang yang sama tidak boleh "
                    "dipilih lebih dari satu kali."
                )


            barang_dalam_transaksi.add(
                barang_id
            )


            # ----------------------------------------------
            # JENIS SATUAN
            # ----------------------------------------------
            jenis_satuan = (
                jenis_satuans[index]
                .strip()
                .lower()
            )


            if jenis_satuan not in [
                "besar",
                "kecil"
            ]:

                raise ValueError(
                    f"Jenis satuan pada baris "
                    f"{index + 1} tidak valid."
                )


            # ----------------------------------------------
            # JUMLAH
            # ----------------------------------------------
            try:

                jumlah = int(
                    jumlahs[index]
                )

                if jumlah <= 0:
                    raise ValueError()

            except Exception:

                raise ValueError(
                    f"Jumlah pada baris "
                    f"{index + 1} harus "
                    f"bilangan bulat lebih dari 0."
                )


            # ----------------------------------------------
            # AMBIL MASTER BARANG
            #
            # FOR UPDATE:
            # mengunci stok selama transaksi berlangsung
            # ----------------------------------------------
            cursor.execute(
                """
                SELECT
                    id,
                    kode_barang,
                    nama_barang,
                    satuan_besar,
                    satuan_kecil,
                    isi_per_satuan,
                    stok_kecil,
                    harga_jual_besar,
                    harga_jual_kecil
                FROM barang
                WHERE id = %s
                LIMIT 1
                FOR UPDATE
                """,
                (barang_id,)
            )


            barang = cursor.fetchone()


            if barang is None:

                raise ValueError(
                    f"Barang pada baris "
                    f"{index + 1} tidak ditemukan."
                )


            kode_barang = barang[1]

            nama_barang = barang[2]

            satuan_besar = barang[3]

            satuan_kecil = barang[4]

            isi_per_satuan = int(
                barang[5]
            )

            stok_sebelum = int(
                barang[6]
            )

            harga_jual_besar = float(
                barang[7]
            )

            harga_jual_kecil = float(
                barang[8]
            )


            # ----------------------------------------------
            # VALIDASI KONVERSI
            # ----------------------------------------------
            if isi_per_satuan <= 0:

                raise ValueError(
                    f"Konversi satuan "
                    f"{nama_barang} tidak valid."
                )


            # ----------------------------------------------
            # HITUNG BERDASARKAN SATUAN
            # ----------------------------------------------
            if jenis_satuan == "besar":

                jumlah_kecil = (
                    jumlah
                    * isi_per_satuan
                )

                harga = (
                    harga_jual_besar
                )

                nama_satuan = (
                    satuan_besar
                )

            else:

                jumlah_kecil = (
                    jumlah
                )

                harga = (
                    harga_jual_kecil
                )

                nama_satuan = (
                    satuan_kecil
                )


            # ----------------------------------------------
            # VALIDASI STOK
            # ----------------------------------------------
            if jumlah_kecil > stok_sebelum:

                raise ValueError(
                    f"Stok {nama_barang} tidak mencukupi. "
                    f"Stok tersedia {stok_sebelum} "
                    f"{satuan_kecil}."
                )


            # ----------------------------------------------
            # HITUNG STOK SESUDAH
            # ----------------------------------------------
            stok_sesudah = (
                stok_sebelum
                - jumlah_kecil
            )


            # ----------------------------------------------
            # HITUNG SUBTOTAL
            # ----------------------------------------------
            subtotal = (
                harga
                * jumlah
            )


            total += subtotal


            # ----------------------------------------------
            # SIMPAN SEMENTARA
            # ----------------------------------------------
            detail_transaksi.append({
                "barang_id": barang_id,
                "kode_barang": kode_barang,
                "nama_barang": nama_barang,
                "jenis_satuan": jenis_satuan,
                "nama_satuan": nama_satuan,
                "jumlah": jumlah,
                "jumlah_kecil": jumlah_kecil,
                "harga": harga,
                "subtotal": subtotal,
                "stok_sebelum": stok_sebelum,
                "stok_sesudah": stok_sesudah
            })


        # ==================================================
        # VALIDASI PEMBAYARAN
        # ==================================================
        if bayar < total:

            raise ValueError(
                "Uang pembayaran kurang. "
                f"Total transaksi Rp "
                f"{total:,.0f}."
            )


        kembalian = (
            bayar
            - total
        )


        # ==================================================
        # BUAT KODE TRANSAKSI
        # ==================================================
        kode_transaksi = (
            buat_kode_transaksi_keluar()
        )


        # ==================================================
        # SIMPAN HEADER TRANSAKSI
        # ==================================================
        cursor.execute(
            """
            INSERT INTO transaksi_keluar
            (
                kode_transaksi,
                tanggal,
                nama_pelanggan,
                total,
                bayar,
                kembalian,
                keterangan,
                user_id
            )
            VALUES
            (%s, %s, %s, %s, %s, %s, %s, %s)
            """,
            (
                kode_transaksi,
                tanggal,
                (
                    nama_pelanggan
                    if nama_pelanggan
                    else None
                ),
                total,
                bayar,
                kembalian,
                (
                    keterangan
                    if keterangan
                    else None
                ),
                session["user_id"]
            )
        )


        transaksi_keluar_id = (
            cursor.lastrowid
        )


        # ==================================================
        # SIMPAN DETAIL DAN UPDATE STOK
        # ==================================================
        for item in detail_transaksi:

            cursor.execute(
                """
                INSERT INTO detail_transaksi_keluar
                (
                    transaksi_keluar_id,
                    barang_id,
                    jenis_satuan,
                    jumlah,
                    jumlah_kecil,
                    harga,
                    subtotal,
                    stok_sebelum,
                    stok_sesudah
                )
                VALUES
                (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """,
                (
                    transaksi_keluar_id,
                    item["barang_id"],
                    item["jenis_satuan"],
                    item["jumlah"],
                    item["jumlah_kecil"],
                    item["harga"],
                    item["subtotal"],
                    item["stok_sebelum"],
                    item["stok_sesudah"]
                )
            )


            cursor.execute(
                """
                UPDATE barang
                SET stok_kecil = %s
                WHERE id = %s
                """,
                (
                    item["stok_sesudah"],
                    item["barang_id"]
                )
            )


        # ==================================================
        # COMMIT
        # ==================================================
        mysql.connection.commit()


    except Exception as e:

        mysql.connection.rollback()

        cursor.close()


        print(
            "ERROR SIMPAN BARANG KELUAR:",
            e
        )


        flash(
            f"Transaksi gagal disimpan: {e}",
            "danger"
        )


        return redirect(
            "/kasir/barang-keluar"
        )


    cursor.close()


    # ======================================================
    # BERHASIL
    # ======================================================
    flash(
        f"Transaksi {kode_transaksi} "
        f"berhasil disimpan. "
        f"Total Rp {total:,.0f} - "
        f"Kembalian Rp {kembalian:,.0f}.",
        "success"
    )


    return redirect(
        "/kasir/barang-keluar"
    )

# ==========================================================
# HISTORI PENJUALAN
# ==========================================================
@app.route("/kasir/histori")
def histori_penjualan():

    if not cek_akses_kasir():
        return redirect("/login")


    # ======================================================
    # FILTER
    # ======================================================
    cari = request.args.get(
        "cari",
        ""
    ).strip()

    tanggal_awal = request.args.get(
        "tanggal_awal",
        ""
    ).strip()

    tanggal_akhir = request.args.get(
        "tanggal_akhir",
        ""
    ).strip()


    cursor = mysql.connection.cursor()


    query = """
        SELECT
            tk.id,
            tk.kode_transaksi,
            tk.tanggal,
            tk.nama_pelanggan,
            tk.total,
            tk.bayar,
            tk.kembalian,
            tk.keterangan,
            u.nama,
            COUNT(dtk.id) AS jumlah_jenis_barang,
            COALESCE(
                SUM(dtk.jumlah_kecil),
                0
            ) AS total_jumlah_kecil

        FROM transaksi_keluar tk

        INNER JOIN users u
            ON tk.user_id = u.id

        LEFT JOIN detail_transaksi_keluar dtk
            ON tk.id = dtk.transaksi_keluar_id

        WHERE 1 = 1
    """


    parameter = []


    # ======================================================
    # PENCARIAN
    # ======================================================
    if cari:

        query += """
            AND
            (
                tk.kode_transaksi LIKE %s
                OR tk.nama_pelanggan LIKE %s
                OR tk.keterangan LIKE %s
            )
        """

        keyword = (
            "%" + cari + "%"
        )

        parameter.extend([
            keyword,
            keyword,
            keyword
        ])


    # ======================================================
    # TANGGAL AWAL
    # ======================================================
    if tanggal_awal:

        query += """
            AND DATE(tk.tanggal) >= %s
        """

        parameter.append(
            tanggal_awal
        )


    # ======================================================
    # TANGGAL AKHIR
    # ======================================================
    if tanggal_akhir:

        query += """
            AND DATE(tk.tanggal) <= %s
        """

        parameter.append(
            tanggal_akhir
        )


    # ======================================================
    # GROUP + ORDER
    # ======================================================
    query += """
        GROUP BY
            tk.id,
            tk.kode_transaksi,
            tk.tanggal,
            tk.nama_pelanggan,
            tk.total,
            tk.bayar,
            tk.kembalian,
            tk.keterangan,
            u.nama

        ORDER BY
            tk.tanggal DESC,
            tk.id DESC
    """


    cursor.execute(
        query,
        tuple(parameter)
    )


    data = cursor.fetchall()

    cursor.close()


    # ======================================================
    # RAPIIKAN DATA
    # ======================================================
    histori = []


    for item in data:

        histori.append({
            "id": item[0],
            "kode_transaksi": item[1],
            "tanggal": item[2],
            "nama_pelanggan": item[3],
            "total": item[4],
            "bayar": item[5],
            "kembalian": item[6],
            "keterangan": item[7],
            "nama_petugas": item[8],
            "jumlah_jenis_barang": item[9],
            "total_jumlah_kecil": item[10]
        })


    return render_template(
        "kasir/histori_penjualan.html",
        histori=histori,
        cari=cari,
        tanggal_awal=tanggal_awal,
        tanggal_akhir=tanggal_akhir
    )

# ==========================================================
# DETAIL TRANSAKSI PENJUALAN
# ==========================================================
@app.route("/kasir/histori/<int:id>")
def detail_penjualan(id):

    if not cek_akses_kasir():
        return redirect("/login")


    cursor = mysql.connection.cursor()


    # ======================================================
    # AMBIL HEADER TRANSAKSI
    # ======================================================
    cursor.execute(
        """
        SELECT
            tk.id,
            tk.kode_transaksi,
            tk.tanggal,
            tk.nama_pelanggan,
            tk.total,
            tk.bayar,
            tk.kembalian,
            tk.keterangan,
            u.nama
        FROM transaksi_keluar tk

        INNER JOIN users u
            ON tk.user_id = u.id

        WHERE tk.id = %s

        LIMIT 1
        """,
        (id,)
    )


    data_transaksi = cursor.fetchone()


    if data_transaksi is None:

        cursor.close()

        flash(
            "Transaksi penjualan tidak ditemukan.",
            "danger"
        )

        return redirect(
            "/kasir/histori"
        )


    transaksi = {
        "id": data_transaksi[0],
        "kode_transaksi": data_transaksi[1],
        "tanggal": data_transaksi[2],
        "nama_pelanggan": data_transaksi[3],
        "total": data_transaksi[4],
        "bayar": data_transaksi[5],
        "kembalian": data_transaksi[6],
        "keterangan": data_transaksi[7],
        "nama_petugas": data_transaksi[8]
    }


    # ======================================================
    # AMBIL DETAIL TRANSAKSI
    # ======================================================
    cursor.execute(
        """
        SELECT
            dtk.id,
            b.kode_barang,
            b.nama_barang,
            b.satuan_besar,
            b.satuan_kecil,
            b.isi_per_satuan,
            dtk.jenis_satuan,
            dtk.jumlah,
            dtk.jumlah_kecil,
            dtk.harga,
            dtk.subtotal,
            dtk.stok_sebelum,
            dtk.stok_sesudah
        FROM detail_transaksi_keluar dtk

        INNER JOIN barang b
            ON dtk.barang_id = b.id

        WHERE dtk.transaksi_keluar_id = %s

        ORDER BY dtk.id ASC
        """,
        (id,)
    )


    data_detail = cursor.fetchall()

    cursor.close()


    detail = []


    for item in data_detail:

        jenis_satuan = item[6]

        if jenis_satuan == "besar":

            nama_satuan = item[3]

        else:

            nama_satuan = item[4]


        detail.append({
            "id": item[0],
            "kode_barang": item[1],
            "nama_barang": item[2],
            "satuan_besar": item[3],
            "satuan_kecil": item[4],
            "isi_per_satuan": int(item[5]),
            "jenis_satuan": item[6],
            "jumlah": item[7],
            "jumlah_kecil": item[8],
            "harga": item[9],
            "subtotal": item[10],
            "stok_sebelum": item[11],
            "stok_sesudah": item[12],
            "nama_satuan": nama_satuan
        })


    return render_template(
        "kasir/detail_penjualan.html",
        transaksi=transaksi,
        detail=detail
    )

# ==========================================================
# IMPORT EXCEL BARANG KELUAR
# TAHAP 1: UPLOAD + VALIDASI + PREVIEW
# ==========================================================
@app.route(
    "/kasir/barang-keluar/import",
    methods=["GET", "POST"]
)
def import_barang_keluar():

    if not cek_akses_kasir():
        return redirect("/login")


    # ======================================================
    # GET
    # ======================================================
    if request.method == "GET":

        tanggal_hari_ini = datetime.now().strftime(
            "%Y-%m-%dT%H:%M"
        )

        return render_template(
            "kasir/import_barang_keluar.html",
            preview=None,
            nama_file=None,
            ada_error=False,
            total=0,
            tanggal_hari_ini=tanggal_hari_ini,
            tanggal="",
            nama_pelanggan="",
            bayar="0",
            keterangan=""
        )


    # ======================================================
    # HEADER TRANSAKSI
    # ======================================================
    tanggal = request.form.get(
        "tanggal",
        ""
    ).strip()

    nama_pelanggan = request.form.get(
        "nama_pelanggan",
        ""
    ).strip()

    bayar = request.form.get(
        "bayar",
        "0"
    ).strip()

    keterangan = request.form.get(
        "keterangan",
        ""
    ).strip()


    if not tanggal:

        flash(
            "Tanggal transaksi wajib diisi.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # VALIDASI BAYAR
    # ======================================================
    try:

        nilai_bayar = float(
            bayar
        )

        if nilai_bayar < 0:
            raise ValueError()

    except Exception:

        flash(
            "Nominal pembayaran tidak valid.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # CEK FILE
    # ======================================================
    if "file_excel" not in request.files:

        flash(
            "Silakan pilih file Excel terlebih dahulu.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    file = request.files[
        "file_excel"
    ]


    if file.filename == "":

        flash(
            "File Excel belum dipilih.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    nama_asli = secure_filename(
        file.filename
    )


    if not nama_asli.lower().endswith(
        ".xlsx"
    ):

        flash(
            "Format file harus .xlsx",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # SIMPAN FILE SEMENTARA
    # ======================================================
    base_dir = os.path.dirname(
        os.path.dirname(
            os.path.abspath(__file__)
        )
    )

    upload_folder = os.path.join(
        base_dir,
        "uploads"
    )

    os.makedirs(
        upload_folder,
        exist_ok=True
    )


    nama_file = (
        f"{uuid.uuid4().hex}_"
        f"{nama_asli}"
    )


    lokasi_file = os.path.join(
        upload_folder,
        nama_file
    )


    file.save(
        lokasi_file
    )


    # ======================================================
    # BACA EXCEL
    # ======================================================
    try:

        df = pd.read_excel(
            lokasi_file,
            sheet_name="Barang Keluar",
            header=3,
            engine="openpyxl"
        )

    except Exception as e:

        if os.path.exists(
            lokasi_file
        ):
            os.remove(
                lokasi_file
            )

        print(
            "ERROR BACA EXCEL BARANG KELUAR:",
            e
        )

        flash(
            "File Excel tidak dapat dibaca. "
            "Pastikan menggunakan template Barang Keluar Kasir.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # KOLOM WAJIB
    # ======================================================
    df.columns = [
        str(kolom).strip()
        for kolom in df.columns
    ]


    kolom_wajib = [
        "kode_barang",
        "jenis_satuan",
        "jumlah"
    ]


    kolom_tidak_ada = [
        kolom
        for kolom in kolom_wajib
        if kolom not in df.columns
    ]


    if kolom_tidak_ada:

        if os.path.exists(
            lokasi_file
        ):
            os.remove(
                lokasi_file
            )

        flash(
            "Format Excel tidak sesuai. "
            "Kolom berikut tidak ditemukan: "
            + ", ".join(kolom_tidak_ada),
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    df = df[
        kolom_wajib
    ]


    df = df.dropna(
        how="all"
    )


    if df.empty:

        if os.path.exists(
            lokasi_file
        ):
            os.remove(
                lokasi_file
            )

        flash(
            "File Excel tidak memiliki data Barang Keluar.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # VALIDASI + PREVIEW
    # ======================================================
    cursor = mysql.connection.cursor()

    preview = []

    ada_error = False

    kode_dalam_file = set()

    total = 0


    for index, row in df.iterrows():

        nomor_baris = index + 5

        error_baris = []


        # --------------------------------------------------
        # KODE BARANG
        # --------------------------------------------------
        kode_barang = (
            ""
            if pd.isna(
                row["kode_barang"]
            )
            else str(
                row["kode_barang"]
            ).strip().upper()
        )


        if not kode_barang:

            error_baris.append(
                "Kode barang kosong"
            )


        # --------------------------------------------------
        # JENIS SATUAN
        # --------------------------------------------------
        jenis_satuan = (
            ""
            if pd.isna(
                row["jenis_satuan"]
            )
            else str(
                row["jenis_satuan"]
            ).strip().lower()
        )


        if jenis_satuan not in [
            "besar",
            "kecil"
        ]:

            error_baris.append(
                "Jenis satuan harus besar atau kecil"
            )


        # --------------------------------------------------
        # JUMLAH
        # --------------------------------------------------
        try:

            nilai_jumlah = row[
                "jumlah"
            ]


            if pd.isna(
                nilai_jumlah
            ):
                raise ValueError()


            nilai_float = float(
                nilai_jumlah
            )


            if not nilai_float.is_integer():
                raise ValueError()


            jumlah = int(
                nilai_float
            )


            if jumlah <= 0:
                raise ValueError()


        except Exception:

            jumlah = 0

            error_baris.append(
                "Jumlah harus bilangan bulat lebih dari 0"
            )


        # --------------------------------------------------
        # DUPLIKAT
        # --------------------------------------------------
        if kode_barang:

            if kode_barang in kode_dalam_file:

                error_baris.append(
                    "Kode barang duplikat di file Excel"
                )

            else:

                kode_dalam_file.add(
                    kode_barang
                )


        # --------------------------------------------------
        # MASTER BARANG
        # --------------------------------------------------
        barang = None


        if kode_barang:

            cursor.execute(
                """
                SELECT
                    id,
                    kode_barang,
                    nama_barang,
                    satuan_besar,
                    satuan_kecil,
                    isi_per_satuan,
                    stok_kecil,
                    harga_jual_besar,
                    harga_jual_kecil
                FROM barang
                WHERE kode_barang = %s
                LIMIT 1
                """,
                (kode_barang,)
            )

            barang = cursor.fetchone()


            if barang is None:

                error_baris.append(
                    "Kode barang tidak ditemukan di Master Barang"
                )


        # --------------------------------------------------
        # DEFAULT PREVIEW
        # --------------------------------------------------
        nama_barang = "-"
        satuan_besar = "-"
        satuan_kecil = "-"
        nama_satuan = "-"
        isi_per_satuan = 0

        stok_sebelum = 0
        stok_sesudah = 0

        jumlah_kecil = 0

        harga = 0
        subtotal = 0


        # --------------------------------------------------
        # JIKA BARANG ADA
        # --------------------------------------------------
        if barang is not None:

            nama_barang = barang[2]

            satuan_besar = barang[3]

            satuan_kecil = barang[4]

            isi_per_satuan = int(
                barang[5]
            )

            stok_sebelum = int(
                barang[6]
            )

            harga_besar = float(
                barang[7]
            )

            harga_kecil = float(
                barang[8]
            )


            if isi_per_satuan <= 0:

                error_baris.append(
                    "Konversi satuan pada Master Barang tidak valid"
                )


            # ----------------------------------------------
            # HITUNG BERDASARKAN SATUAN
            # ----------------------------------------------
            if (
                jumlah > 0
                and isi_per_satuan > 0
                and jenis_satuan in [
                    "besar",
                    "kecil"
                ]
            ):

                if jenis_satuan == "besar":

                    nama_satuan = (
                        satuan_besar
                    )

                    jumlah_kecil = (
                        jumlah
                        * isi_per_satuan
                    )

                    harga = (
                        harga_besar
                    )

                else:

                    nama_satuan = (
                        satuan_kecil
                    )

                    jumlah_kecil = (
                        jumlah
                    )

                    harga = (
                        harga_kecil
                    )


                subtotal = (
                    harga
                    * jumlah
                )


                # ------------------------------------------
                # CEK STOK
                # ------------------------------------------
                if jumlah_kecil > stok_sebelum:

                    error_baris.append(
                        "Stok tidak mencukupi"
                    )

                else:

                    stok_sesudah = (
                        stok_sebelum
                        - jumlah_kecil
                    )


        # --------------------------------------------------
        # STATUS
        # --------------------------------------------------
        if error_baris:

            status = "Error"

            ada_error = True

        else:

            status = "Valid"

            total += subtotal


        # --------------------------------------------------
        # PREVIEW
        # --------------------------------------------------
        preview.append({
            "nomor_baris": nomor_baris,
            "kode_barang": kode_barang,
            "nama_barang": nama_barang,
            "jenis_satuan": jenis_satuan,
            "nama_satuan": nama_satuan,
            "satuan_besar": satuan_besar,
            "satuan_kecil": satuan_kecil,
            "isi_per_satuan": isi_per_satuan,
            "jumlah": jumlah,
            "jumlah_kecil": jumlah_kecil,
            "harga": harga,
            "subtotal": subtotal,
            "stok_sebelum": stok_sebelum,
            "stok_sesudah": stok_sesudah,
            "status": status,
            "error": ", ".join(
                error_baris
            )
        })


    cursor.close()


    # ======================================================
    # VALIDASI PEMBAYARAN
    # ======================================================
    if not ada_error:

        if nilai_bayar < total:

            ada_error = True

            flash(
                "Uang pembayaran kurang dari total transaksi.",
                "danger"
            )


    kembalian = 0


    if (
        not ada_error
        and nilai_bayar >= total
    ):

        kembalian = (
            nilai_bayar - total
        )


    # ======================================================
    # TAMPILKAN PREVIEW
    # ======================================================
    return render_template(
        "kasir/import_barang_keluar.html",
        preview=preview,
        nama_file=nama_file,
        ada_error=ada_error,
        total=total,
        kembalian=kembalian,
        tanggal_hari_ini=tanggal,
        tanggal=tanggal,
        nama_pelanggan=nama_pelanggan,
        bayar=bayar,
        keterangan=keterangan
    )

# ==========================================================
# SIMPAN IMPORT EXCEL BARANG KELUAR
# ==========================================================
@app.route(
    "/kasir/barang-keluar/import/simpan",
    methods=["POST"]
)
def simpan_import_barang_keluar():

    if not cek_akses_kasir():
        return redirect("/login")


    # ======================================================
    # AMBIL DATA HEADER
    # ======================================================
    nama_file = request.form.get(
        "nama_file",
        ""
    ).strip()

    tanggal = request.form.get(
        "tanggal",
        ""
    ).strip()

    nama_pelanggan = request.form.get(
        "nama_pelanggan",
        ""
    ).strip()

    bayar_input = request.form.get(
        "bayar",
        "0"
    ).strip()

    keterangan = request.form.get(
        "keterangan",
        ""
    ).strip()


    # ======================================================
    # VALIDASI HEADER
    # ======================================================
    if not nama_file:

        flash(
            "File import tidak ditemukan.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    if not tanggal:

        flash(
            "Tanggal transaksi wajib diisi.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    try:

        bayar = float(
            bayar_input
        )

        if bayar < 0:
            raise ValueError()

    except Exception:

        flash(
            "Nominal pembayaran tidak valid.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # LOKASI FILE
    # ======================================================
    nama_file = os.path.basename(
        nama_file
    )


    base_dir = os.path.dirname(
        os.path.dirname(
            os.path.abspath(__file__)
        )
    )


    upload_folder = os.path.join(
        base_dir,
        "uploads"
    )


    lokasi_file = os.path.join(
        upload_folder,
        nama_file
    )


    if not os.path.exists(
        lokasi_file
    ):

        flash(
            "File import sudah tidak tersedia. "
            "Silakan upload ulang.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # BACA ULANG FILE EXCEL
    # ======================================================
    try:

        df = pd.read_excel(
            lokasi_file,
            sheet_name="Barang Keluar",
            header=3,
            engine="openpyxl"
        )

    except Exception as e:

        print(
            "ERROR BACA ULANG IMPORT KASIR:",
            e
        )

        flash(
            "File Excel tidak dapat dibaca kembali.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # VALIDASI KOLOM
    # ======================================================
    df.columns = [
        str(kolom).strip()
        for kolom in df.columns
    ]


    kolom_wajib = [
        "kode_barang",
        "jenis_satuan",
        "jumlah"
    ]


    for kolom in kolom_wajib:

        if kolom not in df.columns:

            flash(
                f"Kolom {kolom} tidak ditemukan.",
                "danger"
            )

            return redirect(
                "/kasir/barang-keluar/import"
            )


    df = df[
        kolom_wajib
    ]


    df = df.dropna(
        how="all"
    )


    if df.empty:

        flash(
            "File Excel tidak memiliki data transaksi.",
            "danger"
        )

        return redirect(
            "/kasir/barang-keluar/import"
        )


    # ======================================================
    # MULAI TRANSAKSI DATABASE
    # ======================================================
    cursor = mysql.connection.cursor()

    detail_transaksi = []

    kode_dalam_file = set()

    total = 0


    try:

        # ==================================================
        # VALIDASI ULANG SETIAP BARANG
        # ==================================================
        for index, row in df.iterrows():

            nomor_baris = index + 5


            # ----------------------------------------------
            # KODE BARANG
            # ----------------------------------------------
            if pd.isna(
                row["kode_barang"]
            ):

                raise ValueError(
                    f"Kode barang baris {nomor_baris} kosong."
                )


            kode_barang = str(
                row["kode_barang"]
            ).strip().upper()


            if not kode_barang:

                raise ValueError(
                    f"Kode barang baris {nomor_baris} kosong."
                )


            # ----------------------------------------------
            # CEK DUPLIKAT
            # ----------------------------------------------
            if kode_barang in kode_dalam_file:

                raise ValueError(
                    f"Kode {kode_barang} duplikat "
                    f"di file Excel."
                )


            kode_dalam_file.add(
                kode_barang
            )


            # ----------------------------------------------
            # JENIS SATUAN
            # ----------------------------------------------
            if pd.isna(
                row["jenis_satuan"]
            ):

                raise ValueError(
                    f"Jenis satuan baris {nomor_baris} kosong."
                )


            jenis_satuan = str(
                row["jenis_satuan"]
            ).strip().lower()


            if jenis_satuan not in [
                "besar",
                "kecil"
            ]:

                raise ValueError(
                    f"Jenis satuan baris {nomor_baris} "
                    f"harus besar atau kecil."
                )


            # ----------------------------------------------
            # JUMLAH
            # ----------------------------------------------
            try:

                nilai_jumlah = float(
                    row["jumlah"]
                )


                if not nilai_jumlah.is_integer():

                    raise ValueError()


                jumlah = int(
                    nilai_jumlah
                )


                if jumlah <= 0:

                    raise ValueError()


            except Exception:

                raise ValueError(
                    f"Jumlah baris {nomor_baris} "
                    f"harus bilangan bulat lebih dari 0."
                )


            # ----------------------------------------------
            # AMBIL MASTER BARANG + LOCK
            # ----------------------------------------------
            cursor.execute(
                """
                SELECT
                    id,
                    kode_barang,
                    nama_barang,
                    satuan_besar,
                    satuan_kecil,
                    isi_per_satuan,
                    stok_kecil,
                    harga_jual_besar,
                    harga_jual_kecil

                FROM barang

                WHERE kode_barang = %s

                LIMIT 1

                FOR UPDATE
                """,
                (kode_barang,)
            )


            barang = cursor.fetchone()


            if barang is None:

                raise ValueError(
                    f"Kode barang {kode_barang} "
                    f"tidak ditemukan."
                )


            barang_id = barang[0]

            nama_barang = barang[2]

            satuan_besar = barang[3]

            satuan_kecil = barang[4]

            isi_per_satuan = int(
                barang[5]
            )

            stok_sebelum = int(
                barang[6]
            )

            harga_jual_besar = float(
                barang[7]
            )

            harga_jual_kecil = float(
                barang[8]
            )


            if isi_per_satuan <= 0:

                raise ValueError(
                    f"Konversi satuan {nama_barang} "
                    f"tidak valid."
                )


            # ----------------------------------------------
            # KONVERSI + HARGA
            # ----------------------------------------------
            if jenis_satuan == "besar":

                jumlah_kecil = (
                    jumlah
                    * isi_per_satuan
                )

                harga = (
                    harga_jual_besar
                )

                nama_satuan = (
                    satuan_besar
                )


            else:

                jumlah_kecil = (
                    jumlah
                )

                harga = (
                    harga_jual_kecil
                )

                nama_satuan = (
                    satuan_kecil
                )


            # ----------------------------------------------
            # VALIDASI STOK
            # ----------------------------------------------
            if jumlah_kecil > stok_sebelum:

                raise ValueError(
                    f"Stok {nama_barang} tidak mencukupi. "
                    f"Tersedia {stok_sebelum} "
                    f"{satuan_kecil}."
                )


            stok_sesudah = (
                stok_sebelum
                - jumlah_kecil
            )


            subtotal = (
                harga
                * jumlah
            )


            total += (
                subtotal
            )


            detail_transaksi.append({

                "barang_id":
                    barang_id,

                "kode_barang":
                    kode_barang,

                "nama_barang":
                    nama_barang,

                "jenis_satuan":
                    jenis_satuan,

                "nama_satuan":
                    nama_satuan,

                "jumlah":
                    jumlah,

                "jumlah_kecil":
                    jumlah_kecil,

                "harga":
                    harga,

                "subtotal":
                    subtotal,

                "stok_sebelum":
                    stok_sebelum,

                "stok_sesudah":
                    stok_sesudah

            })


        # ==================================================
        # VALIDASI PEMBAYARAN
        # ==================================================
        if bayar < total:

            raise ValueError(
                "Uang pembayaran kurang. "
                f"Total transaksi Rp {total:,.0f}."
            )


        kembalian = (
            bayar - total
        )


        # ==================================================
        # KODE TRANSAKSI
        # ==================================================
        kode_transaksi = (
            buat_kode_transaksi_keluar()
        )


        # ==================================================
        # SIMPAN HEADER
        # ==================================================
        cursor.execute(
            """
            INSERT INTO transaksi_keluar
            (
                kode_transaksi,
                tanggal,
                nama_pelanggan,
                total,
                bayar,
                kembalian,
                keterangan,
                user_id
            )

            VALUES
            (
                %s, %s, %s, %s,
                %s, %s, %s, %s
            )
            """,
            (
                kode_transaksi,
                tanggal,

                nama_pelanggan
                if nama_pelanggan
                else None,

                total,
                bayar,
                kembalian,

                keterangan
                if keterangan
                else None,

                session["user_id"]
            )
        )


        transaksi_keluar_id = (
            cursor.lastrowid
        )


        # ==================================================
        # SIMPAN DETAIL + UPDATE STOK
        # ==================================================
        for item in detail_transaksi:

            cursor.execute(
                """
                INSERT INTO detail_transaksi_keluar
                (
                    transaksi_keluar_id,
                    barang_id,
                    jenis_satuan,
                    jumlah,
                    jumlah_kecil,
                    harga,
                    subtotal,
                    stok_sebelum,
                    stok_sesudah
                )

                VALUES
                (
                    %s, %s, %s, %s, %s,
                    %s, %s, %s, %s
                )
                """,
                (
                    transaksi_keluar_id,
                    item["barang_id"],
                    item["jenis_satuan"],
                    item["jumlah"],
                    item["jumlah_kecil"],
                    item["harga"],
                    item["subtotal"],
                    item["stok_sebelum"],
                    item["stok_sesudah"]
                )
            )


            cursor.execute(
                """
                UPDATE barang

                SET stok_kecil = %s

                WHERE id = %s
                """,
                (
                    item["stok_sesudah"],
                    item["barang_id"]
                )
            )


        # ==================================================
        # COMMIT
        # ==================================================
        mysql.connection.commit()


    except Exception as e:

        mysql.connection.rollback()

        cursor.close()


        print(
            "ERROR SIMPAN IMPORT BARANG KELUAR:",
            e
        )


        flash(
            f"Transaksi gagal disimpan: {e}",
            "danger"
        )


        return redirect(
            "/kasir/barang-keluar/import"
        )


    cursor.close()


    # ======================================================
    # HAPUS FILE SEMENTARA SETELAH BERHASIL
    # ======================================================
    try:

        if os.path.exists(
            lokasi_file
        ):

            os.remove(
                lokasi_file
            )

    except Exception as e:

        print(
            "GAGAL HAPUS FILE SEMENTARA:",
            e
        )


    # ======================================================
    # PESAN BERHASIL
    # ======================================================
    flash(
        f"Transaksi {kode_transaksi} berhasil disimpan. "
        f"Total Rp {total:,.0f} - "
        f"Kembalian Rp {kembalian:,.0f}.",
        "success"
    )


    # Langsung ke halaman detail transaksi
    return redirect(
        f"/kasir/histori/{transaksi_keluar_id}"
    )