"""Memuat data contoh dari folder data_contoh/ (CSV): master data lalu skenario transaksi berurutan waktu."""
import csv
import os
from datetime import date, timedelta

import erp
from erp import Gagal

FOLDER = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data_contoh")


def baca(nama):
    with open(os.path.join(FOLDER, nama), encoding="utf-8-sig", newline="") as f:
        return [{k.strip(): (v or "").strip() for k, v in r.items()} for r in csv.DictReader(f)]


def n(x, default=0.0):
    x = (x or "").strip().replace(",", ".")
    return float(x) if x else default


def muat_master(con):
    for k, v in [("ppn", "11"), ("perusahaan_nama", "PKS Edukasi Sawit Mandiri"), ("perusahaan_npwp", "00.000.000.0-000.000"),
                 ("perusahaan_alamat", "Jl. Industri Sawit No. 1, Riau")]:
        con.execute("INSERT OR REPLACE INTO setting(key,value) VALUES(?,?)", (k, v))
    for kode, nama, tipe in erp.COA:
        con.execute("INSERT OR REPLACE INTO coa(kode,nama,tipe) VALUES(?,?,?)", (kode, nama, tipe))
    for r in baca("pelanggan.csv"):
        con.execute("INSERT INTO customer(kode,nama,npwp,alamat,kota,telp,termin_hari,pkp,email,no_wa,negara,pic) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)",
                    (r["kode"], r["nama"], r["npwp"], r["alamat"], r["kota"], r["telp"], int(n(r["termin_hari"], 30)), int(n(r["pkp"])),
                     r.get("email", ""), r.get("no_wa", ""), r.get("negara") or "Indonesia", r.get("pic", "")))
    for r in baca("supplier.csv"):
        con.execute("INSERT INTO supplier(kode,nama,npwp,alamat,kota,telp,termin_hari,pkp,lead_time_hari,email,no_wa,negara,pic) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    (r["kode"], r["nama"], r["npwp"], r["alamat"], r["kota"], r["telp"], int(n(r["termin_hari"], 30)), int(n(r["pkp"])), int(n(r["lead_time_hari"], 7)),
                     r.get("email", ""), r.get("no_wa", ""), r.get("negara") or "Indonesia", r.get("pic", "")))
    for r in baca("lokasi.csv"):
        con.execute("INSERT INTO lokasi(kode,nama,tipe,kapasitas) VALUES(?,?,?,?)", (r["kode"], r["nama"], r["tipe"], n(r["kapasitas"])))
    for r in baca("kemasan.csv"):
        con.execute("INSERT INTO kemasan(kode,nama,isi,satuan) VALUES(?,?,?,?)", (r["kode"], r["nama"], n(r["isi"]), r["satuan"]))
    kem = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM kemasan")}
    lok = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM lokasi")}
    for r in baca("barang.csv"):
        con.execute("""INSERT INTO item(kode,nama,jenis,satuan,harga_std,stok_min,metode,kemasan_id,lokasi_id,masa_simpan_hari,lead_time_hari,
            safety_stock,pakai_harian,moq,biaya_pesan,biaya_simpan_pct) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                    (r["kode"], r["nama"], r["jenis"], r["satuan"], n(r["harga_std"]), n(r["stok_min"]), r["metode"] or "FIFO",
                     kem.get(r["kemasan"]), lok.get(r["lokasi"]), int(n(r["masa_simpan_hari"])), int(n(r["lead_time_hari"])),
                     n(r["safety_stock"]), n(r["pakai_harian"]), n(r["moq"]), n(r["biaya_pesan"]), n(r["biaya_simpan_pct"], 20)))
    item = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM item")}
    boms = {}
    for r in baca("bom.csv"):
        boms.setdefault((r["kode_bom"], r["nama_bom"]), []).append((r["arah"], item[r["barang"]], n(r["qty_per_batch"])))
    for (kode, nama), ls in boms.items():
        erp.buat_bom(con, kode, nama, ls)
    for r in baca("karyawan.csv"):
        con.execute("INSERT INTO karyawan(nama,bagian,tipe,upah_dasar,premi_per_kg) VALUES(?,?,?,?,?)",
                    (r["nama"], r["bagian"], r["tipe"], n(r["upah_dasar"]), n(r["premi_per_kg"])))


def baris(s):
    return [x.split(":") for x in (s or "").split(";") if x.strip()]


def jalankan_skenario(con):
    hari_ini = date.today()
    item = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM item")}
    lok = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM lokasi")}
    sup = {r["kode"]: r for r in con.execute("SELECT * FROM supplier")}
    cus = {r["kode"]: r for r in con.execute("SELECT * FROM customer")}
    bom = {r["kode"]: r["id"] for r in con.execute("SELECT id,kode FROM bom_h")}
    ref = {}
    ev = baca("skenario_transaksi.csv")
    for i, r in enumerate(ev):
        r["_i"] = i
    ev.sort(key=lambda r: (-int(r["hari_lalu"]), r["_i"]))
    for r in ev:
        tgl = (hari_ini - timedelta(days=int(r["hari_lalu"]))).isoformat()
        a, rf, p1, p2, p3, p4, p5 = r["aksi"].upper(), r["ref"], r["p1"], r["p2"], r["p3"], r["p4"], r["p5"]
        try:
            if a == "MODAL":
                erp.modal_awal(con, tgl, n(p1))
            elif a == "PO":
                s = sup[p1]
                eta = (date.fromisoformat(tgl) + timedelta(days=int(n(p4)))).isoformat() if p4 else None
                ref[rf] = erp.buat_po(con, tgl, s["id"], int(n(p5, s["termin_hari"])), n(p3, 11), "", [(item[k], n(q), n(h)) for k, q, h in baris(p2)], "seed", eta)
            elif a == "GR":
                pid = ref[p1]
                pd = {x["kode"]: x["id"] for x in con.execute("SELECT d.id, i.kode FROM po_d d JOIN item i ON i.id=d.item_id WHERE d.po_id=?", (pid,))}
                ref[rf] = erp.terima_barang(con, pid, tgl, p4, lok.get(p3), {pd[k]: (n(q), n(t), qc) for k, q, t, qc in baris(p2)})
            elif a in ("RELEASE", "PINDAH"):
                kode_gr = con.execute("SELECT kode FROM gr_h WHERE id=?", (ref[p1],)).fetchone()[0]
                for l in con.execute("SELECT id FROM lot WHERE ref=? AND item_id=? AND qty_sisa>0", (kode_gr, item[p2])).fetchall():
                    erp.lepas_karantina(con, l["id"], tgl) if a == "RELEASE" else erp.pindah_lot(con, l["id"], lok[p3], tgl)
            elif a == "WO_MULAI":
                ref[rf] = erp.buat_wo(con, tgl, bom[p1], n(p2))
                erp.mulai_wo(con, ref[rf], tgl)
            elif a == "WO_SELESAI":
                wid = ref[p1]
                wd = {x["kode"]: x["id"] for x in con.execute("SELECT d.id, i.kode FROM wo_d d JOIN item i ON i.id=d.item_id WHERE d.wo_id=? AND d.arah='Keluar'", (wid,))}
                erp.selesai_wo(con, wid, tgl, {wd[k]: n(q) for k, q in baris(p2)}, n(p3))
            elif a == "SO":
                c = cus[p1]
                kirim = (date.fromisoformat(tgl) + timedelta(days=int(n(p4)))).isoformat()
                ref[rf] = erp.buat_so(con, tgl, c["id"], kirim, int(n(p3, c["termin_hari"])), n(p5, 11), "", [(item[k], n(q), n(h)) for k, q, h in baris(p2)], "seed")
            elif a == "SJ":
                sid = ref[p1]
                sd = {x["kode"]: x["id"] for x in con.execute("SELECT d.id, i.kode FROM so_d d JOIN item i ON i.id=d.item_id WHERE d.so_id=?", (sid,))}
                kota = con.execute("SELECT c.kota FROM so_h h JOIN customer c ON c.id=h.customer_id WHERE h.id=?", (sid,)).fetchone()[0]
                ref[rf] = erp.buat_do(con, sid, tgl, p3, p4, kota, {sd[k]: n(q) for k, q in baris(p2)})
            elif a == "TERIMA_SJ":
                erp.terima_do(con, ref[p1], tgl)
            elif a == "INV":
                ref[rf] = erp.buat_invoice(con, ref[p1], tgl)
            elif a == "BAYAR_AR":
                tot = con.execute("SELECT total-dibayar FROM inv_h WHERE id=?", (ref[p1],)).fetchone()[0]
                erp.bayar(con, "AR", ref[p1], tgl, tot if p2.upper() == "ALL" else n(p2), "Transfer", "")
            elif a == "BAYAR_AP":
                ap = con.execute("SELECT id, total-dibayar sisa FROM ap WHERE gr_id=?", (ref[p1],)).fetchone()
                erp.bayar(con, "AP", ap["id"], tgl, ap["sisa"] if p2.upper() == "ALL" else n(p2), "Transfer", "")
            elif a == "PAYROLL":
                awal = (hari_ini - timedelta(days=int(p2))).isoformat()
                akhir = (hari_ini - timedelta(days=int(p3))).isoformat()
                pid = erp.buat_payroll(con, p1, awal, akhir)
                if p4.upper() == "BAYAR":
                    erp.final_payroll(con, pid, akhir)
            elif a == "BEBAN":
                erp.catat_beban(con, tgl, p1, n(p2), p3)
            elif a == "OPNAME":
                erp.sesuaikan_stok(con, tgl, item[p1], n(p2), p3)
            else:
                raise Gagal("Aksi tidak dikenal: %s" % a)
        except (Gagal, KeyError) as e:
            raise Gagal("Skenario baris %d (%s %s, hari_lalu=%s): %s" % (r["_i"] + 2, a, rf or p1, r["hari_lalu"], e))
    return len(ev)
