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 GUDANG
# ==========================================================
def cek_akses_gudang():

    if "login" not in session:
        return False

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

    return True


# ==========================================================
# BUAT KODE TRANSAKSI MASUK
# ==========================================================
def buat_kode_transaksi_masuk():

    tanggal = datetime.now().strftime("%Y%m%d")

    prefix = f"BM-{tanggal}-"

    cursor = mysql.connection.cursor()

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

    terakhir = cursor.fetchone()

    cursor.close()

    nomor = 1

    if terakhir:

        try:
            nomor = int(
                terakhir[0].split("-")[-1]
            ) + 1

        except (ValueError, IndexError):
            nomor = 1

    return f"{prefix}{nomor:04d}"


# ==========================================================
# BARANG MASUK
# ==========================================================
@app.route("/gudang/barang-masuk", methods=["GET", "POST"])
def barang_masuk():

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


    # ======================================================
    # PROSES PENYIMPANAN
    # ======================================================
    if request.method == "POST":

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

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

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


        # --------------------------------------------------
        # MULTI-ITEM DARI FORM
        # --------------------------------------------------
        barang_ids = request.form.getlist(
            "barang_id[]"
        )

        jumlah_besars = request.form.getlist(
            "jumlah_besar[]"
        )


        # --------------------------------------------------
        # VALIDASI HEADER
        # --------------------------------------------------
        if not tanggal:

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

            return redirect("/gudang/barang-masuk")


        if not barang_ids:

            flash(
                "Minimal harus ada satu barang dalam transaksi.",
                "danger"
            )

            return redirect("/gudang/barang-masuk")


        if len(barang_ids) != len(jumlah_besars):

            flash(
                "Data barang masuk tidak lengkap.",
                "danger"
            )

            return redirect("/gudang/barang-masuk")


        # --------------------------------------------------
        # CEK BARANG DUPLIKAT DALAM SATU TRANSAKSI
        # --------------------------------------------------
        barang_valid = [
            str(item).strip()
            for item in barang_ids
            if str(item).strip()
        ]

        if len(barang_valid) != len(set(barang_valid)):

            flash(
                "Barang yang sama tidak boleh dimasukkan dua kali "
                "dalam satu transaksi.",
                "danger"
            )

            return redirect("/gudang/barang-masuk")


        cursor = mysql.connection.cursor()


        try:

            # ==================================================
            # VALIDASI SEMUA ITEM DULU
            # ==================================================
            detail_simpan = []


            for index, barang_id in enumerate(barang_ids):

                barang_id = str(
                    barang_id
                ).strip()

                jumlah_input = str(
                    jumlah_besars[index]
                ).strip()


                if not barang_id:

                    raise ValueError(
                        f"Barang pada baris {index + 1} belum dipilih."
                    )


                try:

                    barang_id = int(
                        barang_id
                    )

                    jumlah_besar = int(
                        jumlah_input
                    )

                except (ValueError, TypeError):

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


                if jumlah_besar <= 0:

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


                # ==============================================
                # AMBIL MASTER BARANG
                # ==============================================
                cursor.execute(
                    """
                    SELECT
                        id,
                        kode_barang,
                        nama_barang,
                        satuan_besar,
                        satuan_kecil,
                        isi_per_satuan,
                        stok_kecil
                    FROM barang
                    WHERE id = %s
                    LIMIT 1
                    """,
                    (barang_id,)
                )

                barang = cursor.fetchone()


                if barang is None:

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


                isi_per_satuan = int(
                    barang[5]
                )

                stok_sebelum = int(
                    barang[6]
                )


                if isi_per_satuan <= 0:

                    raise ValueError(
                        f"Konversi satuan barang "
                        f"{barang[2]} tidak valid."
                    )


                # ==============================================
                # KONVERSI BESAR → KECIL
                # ==============================================
                jumlah_kecil = (
                    jumlah_besar
                    * isi_per_satuan
                )


                stok_sesudah = (
                    stok_sebelum
                    + jumlah_kecil
                )


                detail_simpan.append({
                    "barang_id": barang[0],
                    "kode_barang": barang[1],
                    "nama_barang": barang[2],
                    "satuan_besar": barang[3],
                    "satuan_kecil": barang[4],
                    "isi_per_satuan": isi_per_satuan,
                    "jumlah_besar": jumlah_besar,
                    "jumlah_kecil": jumlah_kecil,
                    "stok_sebelum": stok_sebelum,
                    "stok_sesudah": stok_sesudah
                })


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


            # ==================================================
            # INSERT HEADER TRANSAKSI
            # ==================================================
            cursor.execute(
                """
                INSERT INTO transaksi_masuk
                (
                    kode_transaksi,
                    tanggal,
                    nomor_faktur,
                    keterangan,
                    user_id
                )
                VALUES
                (
                    %s,
                    %s,
                    %s,
                    %s,
                    %s
                )
                """,
                (
                    kode_transaksi,
                    tanggal,
                    nomor_faktur if nomor_faktur else None,
                    keterangan if keterangan else None,
                    session.get("user_id")
                )
            )


            transaksi_masuk_id = (
                cursor.lastrowid
            )


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

                # ----------------------------------------------
                # INSERT DETAIL
                # ----------------------------------------------
                cursor.execute(
                    """
                    INSERT INTO detail_transaksi_masuk
                    (
                        transaksi_masuk_id,
                        barang_id,
                        jumlah_besar,
                        jumlah_kecil,
                        stok_sebelum,
                        stok_sesudah
                    )
                    VALUES
                    (
                        %s, %s, %s, %s, %s, %s
                    )
                    """,
                    (
                        transaksi_masuk_id,
                        item["barang_id"],
                        item["jumlah_besar"],
                        item["jumlah_kecil"],
                        item["stok_sebelum"],
                        item["stok_sesudah"]
                    )
                )


                # ----------------------------------------------
                # UPDATE MASTER STOK
                # ----------------------------------------------
                cursor.execute(
                    """
                    UPDATE barang
                    SET stok_kecil = stok_kecil + %s
                    WHERE id = %s
                    """,
                    (
                        item["jumlah_kecil"],
                        item["barang_id"]
                    )
                )


            # ==================================================
            # SEMUA BERHASIL
            # ==================================================
            mysql.connection.commit()

            cursor.close()


            flash(
                f"Transaksi {kode_transaksi} berhasil disimpan. "
                f"{len(detail_simpan)} jenis barang telah ditambahkan "
                f"ke stok.",
                "success"
            )

            return redirect(
                "/gudang/barang-masuk"
            )


        # ======================================================
        # ERROR VALIDASI
        # ======================================================
        except ValueError as e:

            mysql.connection.rollback()

            cursor.close()

            flash(
                str(e),
                "danger"
            )

            return redirect(
                "/gudang/barang-masuk"
            )


        # ======================================================
        # ERROR DATABASE
        # ======================================================
        except Exception as e:

            mysql.connection.rollback()

            cursor.close()

            print(
                "ERROR BARANG MASUK:",
                e
            )

            flash(
                "Terjadi kesalahan saat menyimpan transaksi "
                "Barang Masuk. Seluruh transaksi dibatalkan.",
                "danger"
            )

            return redirect(
                "/gudang/barang-masuk"
            )


    # ======================================================
    # GET - TAMPILKAN FORM
    # ======================================================

    cursor = mysql.connection.cursor()

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

    data = cursor.fetchall()

    cursor.close()


    barang = []

    for item in data:

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


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


    return render_template(
        "gudang/barang_masuk.html",
        barang=barang,
        tanggal_hari_ini=tanggal_hari_ini
    )

# ==========================================================
# HISTORI BARANG MASUK
# ==========================================================
@app.route("/gudang/histori")
def histori_barang_masuk():

    if not cek_akses_gudang():
        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 HISTORI
    # ======================================================
    query = """
        SELECT
            tm.id,
            tm.kode_transaksi,
            tm.tanggal,
            tm.nomor_faktur,
            tm.keterangan,
            u.nama,
            COUNT(dtm.id) AS jumlah_jenis_barang,
            COALESCE(
                SUM(dtm.jumlah_kecil),
                0
            ) AS total_jumlah_kecil
        FROM transaksi_masuk tm

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

        LEFT JOIN detail_transaksi_masuk dtm
            ON tm.id = dtm.transaksi_masuk_id

        WHERE 1 = 1
    """


    parameter = []


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

        query += """
            AND (
                tm.kode_transaksi LIKE %s
                OR tm.nomor_faktur LIKE %s
                OR tm.keterangan LIKE %s
            )
        """

        parameter.extend([
            f"%{cari}%",
            f"%{cari}%",
            f"%{cari}%"
        ])


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

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

        parameter.append(
            tanggal_awal
        )


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

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

        parameter.append(
            tanggal_akhir
        )


    # ======================================================
    # GROUPING
    # ======================================================
    query += """
        GROUP BY
            tm.id,
            tm.kode_transaksi,
            tm.tanggal,
            tm.nomor_faktur,
            tm.keterangan,
            u.nama

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


    cursor.execute(
        query,
        tuple(parameter)
    )

    data = cursor.fetchall()


    # ======================================================
    # SUSUN DATA HISTORI
    # ======================================================
    histori = []


    for item in data:

        histori.append({
            "id": item[0],
            "kode_transaksi": item[1],
            "tanggal": item[2],
            "nomor_faktur": item[3],
            "keterangan": item[4],
            "nama_petugas": item[5],
            "jumlah_jenis_barang": item[6],
            "total_jumlah_kecil": item[7]
        })


    cursor.close()


    return render_template(
        "gudang/histori_barang_masuk.html",
        histori=histori,
        cari=cari,
        tanggal_awal=tanggal_awal,
        tanggal_akhir=tanggal_akhir
    )

# ==========================================================
# DETAIL TRANSAKSI BARANG MASUK
# ==========================================================
@app.route("/gudang/histori/<int:id>")
def detail_barang_masuk(id):

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

    cursor = mysql.connection.cursor()

    # ======================================================
    # AMBIL HEADER TRANSAKSI
    # ======================================================
    cursor.execute(
        """
        SELECT
            tm.id,
            tm.kode_transaksi,
            tm.tanggal,
            tm.nomor_faktur,
            tm.keterangan,
            u.nama
        FROM transaksi_masuk tm

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

        WHERE tm.id = %s

        LIMIT 1
        """,
        (id,)
    )

    data_transaksi = cursor.fetchone()


    # ======================================================
    # TRANSAKSI TIDAK DITEMUKAN
    # ======================================================
    if data_transaksi is None:

        cursor.close()

        flash(
            "Transaksi Barang Masuk tidak ditemukan.",
            "danger"
        )

        return redirect("/gudang/histori")


    transaksi = {
        "id": data_transaksi[0],
        "kode_transaksi": data_transaksi[1],
        "tanggal": data_transaksi[2],
        "nomor_faktur": data_transaksi[3],
        "keterangan": data_transaksi[4],
        "nama_petugas": data_transaksi[5]
    }


    # ======================================================
    # AMBIL DETAIL BARANG
    # ======================================================
    cursor.execute(
        """
        SELECT
            dtm.id,
            b.kode_barang,
            b.nama_barang,
            b.satuan_besar,
            b.satuan_kecil,
            b.isi_per_satuan,
            dtm.jumlah_besar,
            dtm.jumlah_kecil,
            dtm.stok_sebelum,
            dtm.stok_sesudah
        FROM detail_transaksi_masuk dtm

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

        WHERE dtm.transaksi_masuk_id = %s

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

    data_detail = cursor.fetchall()

    cursor.close()


    # ======================================================
    # SUSUN DETAIL
    # ======================================================
    detail = []

    total_jumlah_kecil = 0


    for item in data_detail:

        detail.append({
            "id": item[0],
            "kode_barang": item[1],
            "nama_barang": item[2],
            "satuan_besar": item[3],
            "satuan_kecil": item[4],
            "isi_per_satuan": item[5],
            "jumlah_besar": item[6],
            "jumlah_kecil": item[7],
            "stok_sebelum": item[8],
            "stok_sesudah": item[9]
        })

        total_jumlah_kecil += int(
            item[7]
        )


    return render_template(
        "gudang/detail_barang_masuk.html",
        transaksi=transaksi,
        detail=detail,
        total_jumlah_kecil=total_jumlah_kecil
    )

# ==========================================================
# IMPORT EXCEL BARANG MASUK
# TAHAP 1: UPLOAD + VALIDASI + PREVIEW
# ==========================================================
@app.route("/gudang/barang-masuk/import", methods=["GET", "POST"])
def import_barang_masuk():

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


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

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

        return render_template(
            "gudang/import_barang_masuk.html",
            preview=None,
            nama_file=None,
            ada_error=False,
            tanggal_hari_ini=tanggal_hari_ini,
            tanggal="",
            nomor_faktur="",
            keterangan=""
        )


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

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

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


    if not tanggal:

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


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

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


    file = request.files[
        "file_excel"
    ]


    if file.filename == "":

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # VALIDASI FORMAT
    # ======================================================
    nama_asli = secure_filename(
        file.filename
    )


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

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

        return redirect(
            "/gudang/barang-masuk/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:

        # Header template berada pada baris Excel ke-4.
        # Karena pandas mulai menghitung dari 0,
        # maka header=3.
        df = pd.read_excel(
            lokasi_file,
            sheet_name="Barang Masuk",
            header=3,
            engine="openpyxl"
        )

    except Exception as e:

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

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

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


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


    kolom_wajib = [
        "kode_barang",
        "jumlah_besar"
    ]


    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(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # AMBIL KOLOM YANG DIPERLUKAN
    # ======================================================
    df = df[
        kolom_wajib
    ]


    # Hapus baris yang seluruhnya kosong
    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 Masuk.",
            "danger"
        )

        return redirect(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # VALIDASI DATA
    # ======================================================
    preview = []

    ada_error = False

    kode_dalam_file = set()

    cursor = mysql.connection.cursor()


    for index, row in df.iterrows():

        # Karena header berada di baris 4,
        # data pertama berada di baris Excel ke-5.
        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"
            )


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

            nilai_jumlah = row[
                "jumlah_besar"
            ]

            # Menolak nilai kosong / NaN
            if pd.isna(
                nilai_jumlah
            ):
                raise ValueError()


            # Menolak angka pecahan seperti 2.5
            nilai_float = float(
                nilai_jumlah
            )

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


            jumlah_besar = int(
                nilai_float
            )


            if jumlah_besar <= 0:
                raise ValueError()


        except Exception:

            jumlah_besar = 0

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


        # --------------------------------------------------
        # CEK DUPLIKAT DALAM FILE
        # --------------------------------------------------
        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
                )


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

        if kode_barang:

            cursor.execute(
                """
                SELECT
                    id,
                    kode_barang,
                    nama_barang,
                    satuan_besar,
                    satuan_kecil,
                    isi_per_satuan,
                    stok_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"
                )


        # --------------------------------------------------
        # DATA DEFAULT PREVIEW
        # --------------------------------------------------
        nama_barang = "-"
        satuan_besar = "-"
        satuan_kecil = "-"
        isi_per_satuan = 0
        stok_kecil = 0
        jumlah_kecil = 0
        stok_sesudah = 0


        # --------------------------------------------------
        # JIKA MASTER BARANG DITEMUKAN
        # --------------------------------------------------
        if barang is not None:

            nama_barang = barang[2]

            satuan_besar = barang[3]

            satuan_kecil = barang[4]

            isi_per_satuan = int(
                barang[5]
            )

            stok_kecil = int(
                barang[6]
            )


            if isi_per_satuan <= 0:

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


            # ----------------------------------------------
            # HITUNG KONVERSI
            # ----------------------------------------------
            if (
                jumlah_besar > 0
                and isi_per_satuan > 0
            ):

                jumlah_kecil = (
                    jumlah_besar
                    * isi_per_satuan
                )

                stok_sesudah = (
                    stok_kecil
                    + jumlah_kecil
                )


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

            status = "Error"

            ada_error = True

        else:

            status = "Valid"


        # --------------------------------------------------
        # MASUKKAN KE PREVIEW
        # --------------------------------------------------
        preview.append({
            "nomor_baris": nomor_baris,
            "kode_barang": kode_barang,
            "nama_barang": nama_barang,
            "satuan_besar": satuan_besar,
            "satuan_kecil": satuan_kecil,
            "isi_per_satuan": isi_per_satuan,
            "jumlah_besar": jumlah_besar,
            "jumlah_kecil": jumlah_kecil,
            "stok_sebelum": stok_kecil,
            "stok_sesudah": stok_sesudah,
            "status": status,
            "error": ", ".join(
                error_baris
            )
        })


    cursor.close()


    # ======================================================
    # TAMPILKAN PREVIEW
    # ======================================================
    return render_template(
        "gudang/import_barang_masuk.html",
        preview=preview,
        nama_file=nama_file,
        ada_error=ada_error,
        tanggal_hari_ini=tanggal,
        tanggal=tanggal,
        nomor_faktur=nomor_faktur,
        keterangan=keterangan
    )

# ==========================================================
# SIMPAN IMPORT EXCEL BARANG MASUK
# ==========================================================
@app.route(
    "/gudang/barang-masuk/import/simpan",
    methods=["POST"]
)
def simpan_import_barang_masuk():

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


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

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

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

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


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

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


    if not tanggal:

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # KEAMANAN NAMA 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(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # BACA ULANG FILE
    # Jangan percaya data preview dari browser.
    # ======================================================
    try:

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

    except Exception as e:

        print(
            "ERROR BACA FILE SAAT SIMPAN:",
            e
        )

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


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


    kolom_wajib = [
        "kode_barang",
        "jumlah_besar"
    ]


    for kolom in kolom_wajib:

        if kolom not in df.columns:

            flash(
                "Format file Excel tidak sesuai.",
                "danger"
            )

            return redirect(
                "/gudang/barang-masuk/import"
            )


    df = df[
        kolom_wajib
    ]


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


    if df.empty:

        flash(
            "Tidak ada data Barang Masuk "
            "yang dapat disimpan.",
            "danger"
        )

        return redirect(
            "/gudang/barang-masuk/import"
        )


    # ======================================================
    # VALIDASI SEMUA DATA TERLEBIH DAHULU
    # ======================================================
    cursor = mysql.connection.cursor()

    detail_transaksi = []

    kode_dalam_file = set()


    try:

        for index, row in df.iterrows():

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


            if not kode_barang:

                raise ValueError(
                    f"Kode barang kosong pada baris {index + 5}."
                )


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

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


            kode_dalam_file.add(
                kode_barang
            )


            # ----------------------------------------------
            # JUMLAH BESAR
            # ----------------------------------------------
            nilai_jumlah = row[
                "jumlah_besar"
            ]


            if pd.isna(
                nilai_jumlah
            ):

                raise ValueError(
                    f"Jumlah barang kosong untuk "
                    f"{kode_barang}."
                )


            nilai_float = float(
                nilai_jumlah
            )


            if not nilai_float.is_integer():

                raise ValueError(
                    f"Jumlah {kode_barang} harus "
                    f"bilangan bulat."
                )


            jumlah_besar = int(
                nilai_float
            )


            if jumlah_besar <= 0:

                raise ValueError(
                    f"Jumlah {kode_barang} harus "
                    f"lebih dari 0."
                )


            # ----------------------------------------------
            # AMBIL MASTER BARANG
            #
            # FOR UPDATE mengunci baris barang selama
            # transaksi database berlangsung.
            # ----------------------------------------------
            cursor.execute(
                """
                SELECT
                    id,
                    kode_barang,
                    nama_barang,
                    satuan_besar,
                    satuan_kecil,
                    isi_per_satuan,
                    stok_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 di Master Barang."
                )


            barang_id = int(
                barang[0]
            )

            nama_barang = barang[2]

            isi_per_satuan = int(
                barang[5]
            )

            stok_sebelum = int(
                barang[6]
            )


            if isi_per_satuan <= 0:

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


            # ----------------------------------------------
            # HITUNG JUMLAH SATUAN KECIL
            # ----------------------------------------------
            jumlah_kecil = (
                jumlah_besar
                * isi_per_satuan
            )


            stok_sesudah = (
                stok_sebelum
                + jumlah_kecil
            )


            detail_transaksi.append({
                "barang_id": barang_id,
                "kode_barang": kode_barang,
                "nama_barang": nama_barang,
                "jumlah_besar": jumlah_besar,
                "jumlah_kecil": jumlah_kecil,
                "stok_sebelum": stok_sebelum,
                "stok_sesudah": stok_sesudah
            })


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


        # ==================================================
        # SIMPAN HEADER TRANSAKSI
        # ==================================================
        cursor.execute(
            """
            INSERT INTO transaksi_masuk
            (
                kode_transaksi,
                tanggal,
                nomor_faktur,
                keterangan,
                user_id
            )
            VALUES (%s, %s, %s, %s, %s)
            """,
            (
                kode_transaksi,
                tanggal,
                nomor_faktur if nomor_faktur else None,
                keterangan if keterangan else None,
                session["user_id"]
            )
        )


        transaksi_masuk_id = (
            cursor.lastrowid
        )


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

            cursor.execute(
                """
                INSERT INTO detail_transaksi_masuk
                (
                    transaksi_masuk_id,
                    barang_id,
                    jumlah_besar,
                    jumlah_kecil,
                    stok_sebelum,
                    stok_sesudah
                )
                VALUES (%s, %s, %s, %s, %s, %s)
                """,
                (
                    transaksi_masuk_id,
                    item["barang_id"],
                    item["jumlah_besar"],
                    item["jumlah_kecil"],
                    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:

        # ==================================================
        # ROLLBACK
        # ==================================================
        mysql.connection.rollback()

        cursor.close()

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

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

        return redirect(
            "/gudang/barang-masuk/import"
        )


    cursor.close()


    # ======================================================
    # HAPUS FILE SEMENTARA
    # ======================================================
    try:

        if os.path.exists(
            lokasi_file
        ):

            os.remove(
                lokasi_file
            )

    except Exception as e:

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


    # ======================================================
    # BERHASIL
    # ======================================================
    flash(
        f"Import Barang Masuk berhasil disimpan "
        f"dengan kode transaksi {kode_transaksi}.",
        "success"
    )


    return redirect(
        f"/gudang/histori/{transaksi_masuk_id}"
    )