"""Logika bisnis SawitFlow ERP v2: lot/FIFO-LIFO, kemasan & label, jurnal, SCM, penjualan, pembelian, produksi, upah."""
import math
import sqlite3
from datetime import date, timedelta


class Gagal(Exception):
    """Kesalahan proses bisnis yang ditampilkan ke pengguna."""


SCHEMA_VERSION = 2
SCHEMA = """
CREATE TABLE IF NOT EXISTS setting(key TEXT PRIMARY KEY, value TEXT);
CREATE TABLE IF NOT EXISTS users(id INTEGER PRIMARY KEY, username TEXT UNIQUE NOT NULL, nama TEXT NOT NULL,
  role TEXT NOT NULL, pw_hash TEXT NOT NULL, aktif INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS log(id INTEGER PRIMARY KEY, waktu TEXT, user TEXT, aksi TEXT);
CREATE TABLE IF NOT EXISTS customer(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, npwp TEXT, alamat TEXT,
  kota TEXT, telp TEXT, termin_hari INTEGER DEFAULT 30, pkp INTEGER DEFAULT 0, aktif INTEGER DEFAULT 1,
  email TEXT, no_wa TEXT, negara TEXT DEFAULT 'Indonesia', pic TEXT);
CREATE TABLE IF NOT EXISTS supplier(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, npwp TEXT, alamat TEXT,
  kota TEXT, telp TEXT, termin_hari INTEGER DEFAULT 30, pkp INTEGER DEFAULT 0, lead_time_hari INTEGER DEFAULT 7, aktif INTEGER DEFAULT 1,
  email TEXT, no_wa TEXT, negara TEXT DEFAULT 'Indonesia', pic TEXT);
CREATE TABLE IF NOT EXISTS lokasi(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, tipe TEXT, kapasitas REAL DEFAULT 0, aktif INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS kemasan(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, isi REAL DEFAULT 0, satuan TEXT, aktif INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS item(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, jenis TEXT, satuan TEXT,
  harga_std REAL DEFAULT 0, stok_min REAL DEFAULT 0, stok REAL DEFAULT 0, nilai REAL DEFAULT 0, aktif INTEGER DEFAULT 1,
  metode TEXT DEFAULT 'FIFO', kemasan_id INTEGER, lokasi_id INTEGER, masa_simpan_hari INTEGER DEFAULT 0,
  lead_time_hari INTEGER DEFAULT 0, safety_stock REAL DEFAULT 0, pakai_harian REAL DEFAULT 0, moq REAL DEFAULT 0,
  biaya_pesan REAL DEFAULT 0, biaya_simpan_pct REAL DEFAULT 20);
CREATE TABLE IF NOT EXISTS lot(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, item_id INTEGER, tanggal TEXT, kadaluarsa TEXT,
  qty_awal REAL, qty_sisa REAL, biaya_unit REAL, lokasi_id INTEGER, kemasan_id INTEGER, jml_kemasan INTEGER DEFAULT 0,
  status TEXT, asal TEXT, ref TEXT, supplier_id INTEGER);
CREATE TABLE IF NOT EXISTS lot_gerak(id INTEGER PRIMARY KEY, tanggal TEXT, lot_id INTEGER, qty REAL, nilai REAL, tipe TEXT, ref TEXT);
CREATE TABLE IF NOT EXISTS label(id INTEGER PRIMARY KEY, lot_id INTEGER, kode TEXT UNIQUE, qty_awal REAL, qty REAL, status TEXT, ref_pakai TEXT);
CREATE TABLE IF NOT EXISTS stok_gerak(id INTEGER PRIMARY KEY, tanggal TEXT, item_id INTEGER, qty REAL, nilai REAL, tipe TEXT, ref TEXT, ket TEXT);
CREATE TABLE IF NOT EXISTS so_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, customer_id INTEGER, tgl_kirim TEXT,
  termin_hari INTEGER, ppn_persen REAL, status TEXT, catatan TEXT, dibuat_oleh TEXT);
CREATE TABLE IF NOT EXISTS so_d(id INTEGER PRIMARY KEY, so_id INTEGER, item_id INTEGER, qty REAL, harga REAL, qty_terkirim REAL DEFAULT 0);
CREATE TABLE IF NOT EXISTS do_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, so_id INTEGER, kendaraan TEXT,
  driver TEXT, tujuan TEXT, status TEXT, tgl_terima TEXT);
CREATE TABLE IF NOT EXISTS do_d(id INTEGER PRIMARY KEY, do_id INTEGER, so_d_id INTEGER, item_id INTEGER, qty REAL, hpp_total REAL);
CREATE TABLE IF NOT EXISTS inv_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, jatuh_tempo TEXT, customer_id INTEGER,
  so_id INTEGER, do_id INTEGER UNIQUE, dpp REAL, ppn REAL, total REAL, dibayar REAL DEFAULT 0, status TEXT);
CREATE TABLE IF NOT EXISTS inv_d(id INTEGER PRIMARY KEY, inv_id INTEGER, item_id INTEGER, qty REAL, harga REAL, jumlah REAL);
CREATE TABLE IF NOT EXISTS po_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, supplier_id INTEGER, termin_hari INTEGER,
  ppn_persen REAL, status TEXT, catatan TEXT, dibuat_oleh TEXT, eta TEXT);
CREATE TABLE IF NOT EXISTS po_d(id INTEGER PRIMARY KEY, po_id INTEGER, item_id INTEGER, qty REAL, harga REAL, qty_diterima REAL DEFAULT 0);
CREATE TABLE IF NOT EXISTS gr_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, po_id INTEGER, no_sj_supplier TEXT, lokasi_id INTEGER);
CREATE TABLE IF NOT EXISTS gr_d(id INTEGER PRIMARY KEY, gr_id INTEGER, po_d_id INTEGER, item_id INTEGER, qty REAL, nilai REAL,
  qty_tolak REAL DEFAULT 0, qc TEXT, lot_id INTEGER);
CREATE TABLE IF NOT EXISTS ap(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, jatuh_tempo TEXT, supplier_id INTEGER,
  po_id INTEGER, gr_id INTEGER UNIQUE, dpp REAL, ppn REAL, total REAL, dibayar REAL DEFAULT 0, status TEXT);
CREATE TABLE IF NOT EXISTS pembayaran(id INTEGER PRIMARY KEY, tanggal TEXT, tipe TEXT, ref_id INTEGER, jumlah REAL, metode TEXT, ket TEXT);
CREATE TABLE IF NOT EXISTS bom_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, nama TEXT, aktif INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS bom_d(id INTEGER PRIMARY KEY, bom_id INTEGER, arah TEXT, item_id INTEGER, qty REAL);
CREATE TABLE IF NOT EXISTS wo_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tanggal TEXT, bom_id INTEGER, batch REAL, status TEXT,
  tgl_mulai TEXT, tgl_selesai TEXT, biaya_lain REAL DEFAULT 0, catatan TEXT);
CREATE TABLE IF NOT EXISTS wo_d(id INTEGER PRIMARY KEY, wo_id INTEGER, arah TEXT, item_id INTEGER, qty_rencana REAL,
  qty_aktual REAL DEFAULT 0, nilai REAL DEFAULT 0);
CREATE TABLE IF NOT EXISTS karyawan(id INTEGER PRIMARY KEY, nama TEXT, bagian TEXT, tipe TEXT, upah_dasar REAL DEFAULT 0,
  premi_per_kg REAL DEFAULT 0, aktif INTEGER DEFAULT 1);
CREATE TABLE IF NOT EXISTS payroll_h(id INTEGER PRIMARY KEY, kode TEXT UNIQUE, tipe TEXT, tgl_awal TEXT, tgl_akhir TEXT,
  kg_bahan REAL, total REAL DEFAULT 0, status TEXT, tgl_bayar TEXT);
CREATE TABLE IF NOT EXISTS payroll_d(id INTEGER PRIMARY KEY, payroll_id INTEGER, karyawan_id INTEGER, hadir REAL, tarif REAL,
  premi_kg REAL, upah_pokok REAL, premi REAL, total REAL);
CREATE TABLE IF NOT EXISTS beban(id INTEGER PRIMARY KEY, tanggal TEXT, kategori TEXT, jumlah REAL, ket TEXT, sumber TEXT, ref TEXT);
CREATE TABLE IF NOT EXISTS coa(kode TEXT PRIMARY KEY, nama TEXT, tipe TEXT);
CREATE TABLE IF NOT EXISTS jurnal(id INTEGER PRIMARY KEY, tanggal TEXT, tipe TEXT, ref TEXT, akun TEXT, debit REAL DEFAULT 0, kredit REAL DEFAULT 0, ket TEXT);
"""

COA = [("1101", "Kas & Bank", "Aset"), ("1201", "Piutang Usaha", "Aset"), ("1301", "Persediaan Bahan Baku", "Aset"),
       ("1302", "Persediaan Bahan Pembantu", "Aset"), ("1303", "Barang Dalam Proses (WIP)", "Aset"),
       ("1304", "Persediaan Barang Jadi", "Aset"), ("1305", "Persediaan dalam Pengiriman", "Aset"), ("1401", "PPN Masukan", "Aset"),
       ("2101", "Hutang Usaha", "Kewajiban"), ("2201", "PPN Keluaran", "Kewajiban"), ("3101", "Modal", "Ekuitas"),
       ("4101", "Penjualan", "Pendapatan"), ("5101", "Harga Pokok Penjualan (HPP/COGS)", "Beban"),
       ("6101", "Beban Gaji & Upah", "Beban"), ("6102", "Beban Operasional", "Beban"), ("6103", "Selisih & Afkir Persediaan", "Beban")]
AKUN_ITEM = {"Bahan Baku": "1301", "Bahan Pembantu": "1302", "Barang Jadi": "1304"}


def tambah_hari(tgl, n):
    return (date.fromisoformat(tgl) + timedelta(days=int(n or 0))).isoformat()


def fq(v):
    return ("%.2f" % v).rstrip("0").rstrip(".") if v is not None else "0"


def nomor(con, prefix, tabel, tgl):
    ym = tgl[2:4] + tgl[5:7]
    n = con.execute("SELECT COUNT(*) FROM %s WHERE kode LIKE ?" % tabel, ("%s/%s/%%" % (prefix, ym),)).fetchone()[0] + 1
    return "%s/%s/%04d" % (prefix, ym, n)


def one(con, sql, args=()):
    r = con.execute(sql, args).fetchone()
    if r is None:
        raise Gagal("Data tidak ditemukan.")
    return r


def dr(akun, v):
    return (akun, v, 0) if v >= 0 else (akun, 0, -v)


def cr(akun, v):
    return (akun, 0, v) if v >= 0 else (akun, -v, 0)


def jurnal(con, tgl, tipe, ref, lines, ket=""):
    """Jurnal berpasangan (debit = kredit). Selisih pembulatan kecil diserap ke baris terbesar."""
    ls = [[a, round(d, 2), round(k, 2)] for a, d, k in lines if abs(d) > 0.004 or abs(k) > 0.004]
    if not ls:
        return
    diff = round(sum(l[1] for l in ls) - sum(l[2] for l in ls), 2)
    if abs(diff) > 5:
        raise Gagal("Jurnal %s tidak seimbang (selisih %s)." % (ref, fq(diff)))
    if diff:
        idx = max(range(len(ls)), key=lambda i: ls[i][2] if diff > 0 else ls[i][1])
        ls[idx][2 if diff > 0 else 1] = round(ls[idx][2 if diff > 0 else 1] + (diff if diff > 0 else -diff), 2)
    for a, d, k in ls:
        con.execute("INSERT INTO jurnal(tanggal,tipe,ref,akun,debit,kredit,ket) VALUES(?,?,?,?,?,?,?)", (tgl, tipe, ref, a, d, k, ket))


def akun_item(con, item_id):
    return AKUN_ITEM[one(con, "SELECT jenis FROM item WHERE id=?", (item_id,))["jenis"]]


def modal_awal(con, tgl, jumlah):
    jurnal(con, tgl, "Modal", "MODAL", [dr("1101", jumlah), cr("3101", jumlah)], "Setoran modal awal")


# ------------------------------------------------------------- lot & persediaan
def _equalize(con, item_id):
    it = con.execute("SELECT * FROM item WHERE id=?", (item_id,)).fetchone()
    if it["metode"] == "AVG" and it["stok"] > 1e-9:
        con.execute("UPDATE lot SET biaya_unit=? WHERE item_id=? AND qty_sisa>0.000001", (it["nilai"] / it["stok"], item_id))


def _pakai_label(con, lot_id, qty, ref):
    for lb in con.execute("SELECT * FROM label WHERE lot_id=? AND status='Gudang' ORDER BY id", (lot_id,)).fetchall():
        if qty <= 1e-9:
            break
        t = min(lb["qty"], qty)
        sisa = lb["qty"] - t
        con.execute("UPDATE label SET qty=?, status=?, ref_pakai=? WHERE id=?", (sisa, "Terpakai" if sisa < 1e-9 else "Gudang", ref, lb["id"]))
        qty -= t


def masuk_lot(con, tgl, item_id, qty, tipe, ref, ket, nilai=None, lokasi_id=None, status="Tersedia",
              asal="Penerimaan", supplier_id=None, kemasan_id=None):
    """Barang masuk: membuat lot (lapisan persediaan) lengkap kemasan/label. Mengembalikan (lot_id, nilai)."""
    it = one(con, "SELECT * FROM item WHERE id=?", (item_id,))
    if qty <= 0:
        raise Gagal("Jumlah masuk harus lebih dari 0.")
    if nilai is None:
        nilai = qty * ((it["nilai"] / it["stok"]) if it["stok"] > 1e-9 else (it["harga_std"] or 0))
    lokasi_id = lokasi_id or it["lokasi_id"]
    kemasan_id = kemasan_id or it["kemasan_id"]
    ym = tgl[2:4] + tgl[5:7]
    kode = "LOT-%s-%04d" % (ym, con.execute("SELECT COUNT(*) FROM lot WHERE kode LIKE ?", ("LOT-%s-%%" % ym,)).fetchone()[0] + 1)
    kad = tambah_hari(tgl, it["masa_simpan_hari"]) if it["masa_simpan_hari"] else None
    isi, jml = 0, 0
    if kemasan_id:
        isi = one(con, "SELECT isi FROM kemasan WHERE id=?", (kemasan_id,))["isi"] or 0
        jml = math.ceil(qty / isi - 1e-9) if isi > 0 else 0
    lid = con.execute("INSERT INTO lot(kode,item_id,tanggal,kadaluarsa,qty_awal,qty_sisa,biaya_unit,lokasi_id,kemasan_id,jml_kemasan,status,asal,ref,supplier_id)"
                      " VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                      (kode, item_id, tgl, kad, qty, qty, nilai / qty, lokasi_id, kemasan_id, jml, status, asal, ref, supplier_id)).lastrowid
    if 0 < jml <= 400:
        sisa = qty
        for i in range(jml):
            q = min(isi, sisa)
            sisa -= q
            con.execute("INSERT INTO label(lot_id,kode,qty_awal,qty,status) VALUES(?,?,?,?,'Gudang')", (lid, "%s-%03d" % (kode, i + 1), q, q))
    con.execute("INSERT INTO lot_gerak(tanggal,lot_id,qty,nilai,tipe,ref) VALUES(?,?,?,?,?,?)", (tgl, lid, qty, nilai, tipe, ref))
    con.execute("UPDATE item SET stok=stok+?, nilai=nilai+? WHERE id=?", (qty, nilai, item_id))
    _equalize(con, item_id)
    con.execute("INSERT INTO stok_gerak(tanggal,item_id,qty,nilai,tipe,ref,ket) VALUES(?,?,?,?,?,?,?)", (tgl, item_id, qty, nilai, tipe, ref, ket))
    return lid, nilai


def keluar(con, tgl, item_id, qty, tipe, ref, ket):
    """Barang keluar mengikuti metode barang (FIFO/LIFO/AVG). Hanya lot berstatus Tersedia. Mengembalikan nilai (positif)."""
    it = one(con, "SELECT * FROM item WHERE id=?", (item_id,))
    if qty <= 0:
        raise Gagal("Jumlah keluar harus lebih dari 0.")
    ada = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE item_id=? AND status='Tersedia'", (item_id,)).fetchone()[0]
    if ada + 1e-6 < qty:
        kar = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE item_id=? AND status='Karantina'", (item_id,)).fetchone()[0]
        raise Gagal("Stok %s tidak cukup: tersedia %s %s, dibutuhkan %s %s.%s" % (
            it["nama"], fq(ada), it["satuan"], fq(qty), it["satuan"],
            " (%s %s masih karantina QC)" % (fq(kar), it["satuan"]) if kar > 0 else ""))
    avg = it["nilai"] / it["stok"] if it["stok"] > 1e-9 else 0
    urut = "DESC" if it["metode"] == "LIFO" else "ASC"
    sisa, total = qty, 0.0
    for lot in con.execute("SELECT * FROM lot WHERE item_id=? AND status='Tersedia' AND qty_sisa>0.000001 ORDER BY tanggal %s, id %s" % (urut, urut), (item_id,)).fetchall():
        if sisa <= 1e-9:
            break
        ambil = min(lot["qty_sisa"], sisa)
        c = ambil * (avg if it["metode"] == "AVG" else lot["biaya_unit"])
        baru = lot["qty_sisa"] - ambil
        con.execute("UPDATE lot SET qty_sisa=?, status=? WHERE id=?", (baru, "Habis" if baru < 1e-6 else "Tersedia", lot["id"]))
        con.execute("INSERT INTO lot_gerak(tanggal,lot_id,qty,nilai,tipe,ref) VALUES(?,?,?,?,?,?)", (tgl, lot["id"], -ambil, -c, tipe, ref))
        _pakai_label(con, lot["id"], ambil, ref)
        total += c
        sisa -= ambil
    if abs(it["stok"] - qty) < 1e-6:
        total = it["nilai"]
    con.execute("UPDATE item SET stok=stok-?, nilai=nilai-? WHERE id=?", (qty, total, item_id))
    con.execute("UPDATE item SET stok=0, nilai=0 WHERE id=? AND abs(stok)<0.000001", (item_id,))
    _equalize(con, item_id)
    con.execute("INSERT INTO stok_gerak(tanggal,item_id,qty,nilai,tipe,ref,ket) VALUES(?,?,?,?,?,?,?)", (tgl, item_id, -qty, -total, tipe, ref, ket))
    return total


def sesuaikan_stok(con, tgl, item_id, fisik, ket):
    it = one(con, "SELECT * FROM item WHERE id=?", (item_id,))
    selisih = fisik - it["stok"]
    if abs(selisih) < 1e-9:
        raise Gagal("Stok fisik sama dengan stok sistem, tidak ada yang disesuaikan.")
    ak = akun_item(con, item_id)
    if selisih < 0:
        n = keluar(con, tgl, item_id, -selisih, "Penyesuaian", "OPNAME", ket or "Stok opname")
        jurnal(con, tgl, "Opname", "OPNAME", [dr("6103", n), cr(ak, n)], "Susut hasil opname " + it["nama"])
        con.execute("INSERT INTO beban(tanggal,kategori,jumlah,ket,sumber,ref) VALUES(?,?,?,?,'otomatis','OPNAME')", (tgl, "Selisih Persediaan", n, "Opname " + it["nama"]))
    else:
        _, n = masuk_lot(con, tgl, item_id, selisih, "Penyesuaian", "OPNAME", ket or "Stok opname", asal="Opname")
        jurnal(con, tgl, "Opname", "OPNAME", [dr(ak, n), cr("6103", n)], "Kelebihan hasil opname " + it["nama"])
        con.execute("INSERT INTO beban(tanggal,kategori,jumlah,ket,sumber,ref) VALUES(?,?,?,?,'otomatis','OPNAME')", (tgl, "Selisih Persediaan", -n, "Opname " + it["nama"]))


def lepas_karantina(con, lot_id, tgl):
    lot = one(con, "SELECT * FROM lot WHERE id=?", (lot_id,))
    if lot["status"] != "Karantina":
        raise Gagal("Lot ini tidak sedang karantina.")
    con.execute("UPDATE lot SET status='Tersedia' WHERE id=?", (lot_id,))
    con.execute("INSERT INTO lot_gerak(tanggal,lot_id,qty,nilai,tipe,ref) VALUES(?,?,0,0,'QC lulus','Lepas karantina')", (tgl, lot_id))


def afkir_lot(con, lot_id, tgl):
    lot = one(con, "SELECT l.*, i.nama, i.jenis FROM lot l JOIN item i ON i.id=l.item_id WHERE l.id=?", (lot_id,))
    if lot["qty_sisa"] <= 1e-9 or lot["status"] not in ("Tersedia", "Karantina"):
        raise Gagal("Lot ini tidak punya sisa stok untuk diafkir.")
    q, n = lot["qty_sisa"], lot["qty_sisa"] * lot["biaya_unit"]
    con.execute("UPDATE lot SET qty_sisa=0, status='Afkir' WHERE id=?", (lot_id,))
    con.execute("INSERT INTO lot_gerak(tanggal,lot_id,qty,nilai,tipe,ref) VALUES(?,?,?,?,'Afkir',?)", (tgl, lot_id, -q, -n, lot["kode"]))
    _pakai_label(con, lot_id, q, "AFKIR")
    con.execute("UPDATE item SET stok=stok-?, nilai=nilai-? WHERE id=?", (q, n, lot["item_id"]))
    con.execute("UPDATE item SET stok=0, nilai=0 WHERE id=? AND abs(stok)<0.000001", (lot["item_id"],))
    _equalize(con, lot["item_id"])
    con.execute("INSERT INTO stok_gerak(tanggal,item_id,qty,nilai,tipe,ref,ket) VALUES(?,?,?,?,'Afkir',?,?)", (tgl, lot["item_id"], -q, -n, lot["kode"], "Barang afkir / kedaluwarsa"))
    jurnal(con, tgl, "Afkir", lot["kode"], [dr("6103", n), cr(AKUN_ITEM[lot["jenis"]], n)], "Afkir " + lot["nama"])
    con.execute("INSERT INTO beban(tanggal,kategori,jumlah,ket,sumber,ref) VALUES(?,?,?,?,'otomatis',?)", (tgl, "Selisih Persediaan", n, "Afkir lot " + lot["kode"], lot["kode"]))


def pindah_lot(con, lot_id, lokasi_id, tgl):
    lot = one(con, "SELECT * FROM lot WHERE id=?", (lot_id,))
    lk = one(con, "SELECT * FROM lokasi WHERE id=?", (lokasi_id,))
    if lot["lokasi_id"] == lokasi_id:
        raise Gagal("Lot sudah berada di lokasi tersebut.")
    isi = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE lokasi_id=? AND qty_sisa>0", (lokasi_id,)).fetchone()[0]
    if lk["kapasitas"] and isi + lot["qty_sisa"] > lk["kapasitas"] + 1e-6:
        raise Gagal("Kapasitas %s tidak cukup (isi %s dari %s)." % (lk["nama"], fq(isi), fq(lk["kapasitas"])))
    con.execute("UPDATE lot SET lokasi_id=? WHERE id=?", (lokasi_id, lot_id))
    con.execute("INSERT INTO lot_gerak(tanggal,lot_id,qty,nilai,tipe,ref) VALUES(?,?,0,0,'Pindah lokasi',?)", (tgl, lot_id, "ke " + lk["kode"]))


# ------------------------------------------------------------- penjualan
def buat_so(con, tgl, cid, tgl_kirim, termin, ppn, cat, lines, user):
    if not lines:
        raise Gagal("Tambahkan minimal satu baris barang.")
    kode = nomor(con, "SO", "so_h", tgl)
    sid = con.execute("INSERT INTO so_h(kode,tanggal,customer_id,tgl_kirim,termin_hari,ppn_persen,status,catatan,dibuat_oleh)"
                      " VALUES(?,?,?,?,?,?,'Open',?,?)", (kode, tgl, cid, tgl_kirim, termin, ppn, cat, user)).lastrowid
    for it, qt, hg in lines:
        con.execute("INSERT INTO so_d(so_id,item_id,qty,harga) VALUES(?,?,?,?)", (sid, it, qt, hg))
    return sid


def refresh_so(con, sid):
    d = con.execute("SELECT SUM(qty) q, SUM(qty_terkirim) k FROM so_d WHERE so_id=?", (sid,)).fetchone()
    if d["k"] <= 0:
        st = "Open"
    elif d["k"] < d["q"] - 1e-6:
        st = "Sebagian Dikirim"
    else:
        belum = con.execute("SELECT COUNT(*) FROM do_h WHERE so_id=? AND id NOT IN (SELECT do_id FROM inv_h)", (sid,)).fetchone()[0]
        open_inv = con.execute("SELECT COUNT(*) FROM inv_h WHERE so_id=? AND status!='Lunas'", (sid,)).fetchone()[0]
        st = "Selesai" if (belum == 0 and open_inv == 0) else "Terkirim"
    con.execute("UPDATE so_h SET status=? WHERE id=?", (st, sid))


def buat_do(con, sid, tgl, kendaraan, driver, tujuan, qtys):
    kirim = {k: v for k, v in qtys.items() if v > 0}
    if not kirim:
        raise Gagal("Isi jumlah kirim minimal pada satu baris.")
    kode = nomor(con, "SJ", "do_h", tgl)
    did = con.execute("INSERT INTO do_h(kode,tanggal,so_id,kendaraan,driver,tujuan,status) VALUES(?,?,?,?,?,?,'Dikirim')",
                      (kode, tgl, sid, kendaraan, driver, tujuan)).lastrowid
    per_akun = {}
    for sdid, qt in kirim.items():
        d = one(con, "SELECT * FROM so_d WHERE id=? AND so_id=?", (sdid, sid))
        if qt > d["qty"] - d["qty_terkirim"] + 1e-6:
            raise Gagal("Jumlah kirim melebihi sisa pesanan.")
        n = keluar(con, tgl, d["item_id"], qt, "Surat Jalan", kode, "Pengiriman ke pelanggan")
        con.execute("INSERT INTO do_d(do_id,so_d_id,item_id,qty,hpp_total) VALUES(?,?,?,?,?)", (did, sdid, d["item_id"], qt, n))
        con.execute("UPDATE so_d SET qty_terkirim=qty_terkirim+? WHERE id=?", (qt, sdid))
        ak = akun_item(con, d["item_id"])
        per_akun[ak] = per_akun.get(ak, 0) + n
    jurnal(con, tgl, "Surat Jalan", kode, [dr("1305", sum(per_akun.values()))] + [cr(a, v) for a, v in per_akun.items()], "Barang keluar ke pelanggan")
    refresh_so(con, sid)
    return did


def terima_do(con, did, tgl):
    d = one(con, "SELECT * FROM do_h WHERE id=?", (did,))
    if d["status"] != "Dikirim":
        raise Gagal("Surat jalan ini sudah dikonfirmasi.")
    con.execute("UPDATE do_h SET status='Diterima', tgl_terima=? WHERE id=?", (tgl, did))


def buat_invoice(con, did, tgl):
    d = one(con, "SELECT * FROM do_h WHERE id=?", (did,))
    if con.execute("SELECT 1 FROM inv_h WHERE do_id=?", (did,)).fetchone():
        raise Gagal("Surat jalan ini sudah punya invoice.")
    so = one(con, "SELECT * FROM so_h WHERE id=?", (d["so_id"],))
    ls = con.execute("SELECT dd.item_id, dd.qty, dd.hpp_total, sd.harga FROM do_d dd JOIN so_d sd ON sd.id=dd.so_d_id WHERE dd.do_id=?", (did,)).fetchall()
    dpp = sum(l["qty"] * l["harga"] for l in ls)
    ppn = round(dpp * so["ppn_persen"] / 100)
    kode = nomor(con, "INV", "inv_h", tgl)
    iid = con.execute("INSERT INTO inv_h(kode,tanggal,jatuh_tempo,customer_id,so_id,do_id,dpp,ppn,total,status) VALUES(?,?,?,?,?,?,?,?,?,'Belum Bayar')",
                      (kode, tgl, tambah_hari(tgl, so["termin_hari"]), so["customer_id"], so["id"], did, dpp, ppn, dpp + ppn)).lastrowid
    for l in ls:
        con.execute("INSERT INTO inv_d(inv_id,item_id,qty,harga,jumlah) VALUES(?,?,?,?,?)", (iid, l["item_id"], l["qty"], l["harga"], l["qty"] * l["harga"]))
    hpp = sum(l["hpp_total"] for l in ls)
    jurnal(con, tgl, "Invoice", kode, [dr("1201", dpp + ppn), cr("4101", dpp), cr("2201", ppn)], "Penjualan")
    jurnal(con, tgl, "Invoice", kode, [dr("5101", hpp), cr("1305", hpp)], "HPP penjualan (COGS)")
    refresh_so(con, so["id"])
    return iid


def bayar(con, tipe, rid, tgl, jumlah, metode, ket):
    tabel = {"AR": "inv_h", "AP": "ap"}[tipe]
    r = one(con, "SELECT * FROM %s WHERE id=?" % tabel, (rid,))
    sisa = r["total"] - r["dibayar"]
    if jumlah <= 0 or jumlah > sisa + 0.5:
        raise Gagal("Jumlah pembayaran harus lebih dari 0 dan tidak melebihi sisa tagihan (%s)." % fq(sisa))
    baru = r["dibayar"] + jumlah
    st = "Lunas" if baru >= r["total"] - 0.5 else "Sebagian"
    con.execute("UPDATE %s SET dibayar=?, status=? WHERE id=?" % tabel, (baru, st, rid))
    con.execute("INSERT INTO pembayaran(tanggal,tipe,ref_id,jumlah,metode,ket) VALUES(?,?,?,?,?,?)", (tgl, tipe, rid, jumlah, metode, ket))
    if tipe == "AR":
        jurnal(con, tgl, "Pembayaran", r["kode"], [dr("1101", jumlah), cr("1201", jumlah)], "Penerimaan dari pelanggan")
        refresh_so(con, r["so_id"])
    else:
        jurnal(con, tgl, "Pembayaran", r["kode"], [dr("2101", jumlah), cr("1101", jumlah)], "Pembayaran ke supplier")
        refresh_po(con, r["po_id"])


# ------------------------------------------------------------- pembelian & penerimaan
def buat_po(con, tgl, sid, termin, ppn, cat, lines, user, eta=None):
    if not lines:
        raise Gagal("Tambahkan minimal satu baris material.")
    sp = one(con, "SELECT * FROM supplier WHERE id=?", (sid,))
    eta = eta or tambah_hari(tgl, sp["lead_time_hari"])
    kode = nomor(con, "PO", "po_h", tgl)
    pid = con.execute("INSERT INTO po_h(kode,tanggal,supplier_id,termin_hari,ppn_persen,status,catatan,dibuat_oleh,eta) VALUES(?,?,?,?,?,'Open',?,?,?)",
                      (kode, tgl, sid, termin, ppn, cat, user, eta)).lastrowid
    for it, qt, hg in lines:
        con.execute("INSERT INTO po_d(po_id,item_id,qty,harga) VALUES(?,?,?,?)", (pid, it, qt, hg))
    return pid


def refresh_po(con, pid):
    d = con.execute("SELECT SUM(qty) q, SUM(qty_diterima) k FROM po_d WHERE po_id=?", (pid,)).fetchone()
    if d["k"] <= 0:
        st = "Open"
    elif d["k"] < d["q"] - 1e-6:
        st = "Sebagian Diterima"
    else:
        open_ap = con.execute("SELECT COUNT(*) FROM ap WHERE po_id=? AND status!='Lunas'", (pid,)).fetchone()[0]
        st = "Selesai" if open_ap == 0 else "Diterima"
    con.execute("UPDATE po_h SET status=? WHERE id=?", (st, pid))


def terima_barang(con, pid, tgl, sj, lokasi_id, lines):
    """lines: {po_d_id: (qty_terima, qty_tolak, qc)}. qc = 'Lulus' atau 'Karantina'. Hanya qty diterima yang masuk stok & tagihan."""
    po = one(con, "SELECT * FROM po_h WHERE id=?", (pid,))
    lines = {k: v for k, v in lines.items() if v[0] > 0 or v[1] > 0}
    if not lines:
        raise Gagal("Isi jumlah diterima atau ditolak minimal pada satu baris.")
    kode = nomor(con, "GR", "gr_h", tgl)
    gid = con.execute("INSERT INTO gr_h(kode,tanggal,po_id,no_sj_supplier,lokasi_id) VALUES(?,?,?,?,?)", (kode, tgl, pid, sj, lokasi_id)).lastrowid
    dpp, per_akun = 0.0, {}
    for pdid, (qt, tolak, qc) in lines.items():
        d = one(con, "SELECT * FROM po_d WHERE id=? AND po_id=?", (pdid, pid))
        if qt > d["qty"] - d["qty_diterima"] + 1e-6:
            raise Gagal("Jumlah diterima melebihi sisa PO.")
        if qc not in ("Lulus", "Karantina"):
            raise Gagal("Status QC tidak valid.")
        nilai, lid = qt * d["harga"], None
        if qt > 0:
            lid, _ = masuk_lot(con, tgl, d["item_id"], qt, "Penerimaan", kode, "Barang masuk dari supplier (PO %s)" % po["kode"], nilai,
                               lokasi_id, "Tersedia" if qc == "Lulus" else "Karantina", "Penerimaan", po["supplier_id"])
            con.execute("UPDATE po_d SET qty_diterima=qty_diterima+? WHERE id=?", (qt, pdid))
            ak = akun_item(con, d["item_id"])
            per_akun[ak] = per_akun.get(ak, 0) + nilai
        con.execute("INSERT INTO gr_d(gr_id,po_d_id,item_id,qty,nilai,qty_tolak,qc,lot_id) VALUES(?,?,?,?,?,?,?,?)", (gid, pdid, d["item_id"], qt, nilai, tolak, qc, lid))
        dpp += nilai
    if dpp > 0:
        ppn = round(dpp * po["ppn_persen"] / 100)
        con.execute("INSERT INTO ap(kode,tanggal,jatuh_tempo,supplier_id,po_id,gr_id,dpp,ppn,total,status) VALUES(?,?,?,?,?,?,?,?,?,'Belum Bayar')",
                    (nomor(con, "AP", "ap", tgl), tgl, tambah_hari(tgl, po["termin_hari"]), po["supplier_id"], pid, gid, dpp, ppn, dpp + ppn))
        jurnal(con, tgl, "Penerimaan", kode, [dr(a, v) for a, v in per_akun.items()] + [dr("1401", ppn), cr("2101", dpp + ppn)], "Penerimaan material PO " + po["kode"])
    refresh_po(con, pid)
    return gid


# ------------------------------------------------------------- BOM & produksi
def buat_bom(con, kode, nama, lines):
    if not any(l[0] == "Masuk" for l in lines) or not any(l[0] == "Keluar" for l in lines):
        raise Gagal("BOM harus punya minimal satu bahan masuk dan satu hasil keluar.")
    try:
        bid = con.execute("INSERT INTO bom_h(kode,nama) VALUES(?,?)", (kode, nama)).lastrowid
    except sqlite3.IntegrityError:
        raise Gagal("Kode BOM sudah dipakai.")
    for arah, it, qt in lines:
        con.execute("INSERT INTO bom_d(bom_id,arah,item_id,qty) VALUES(?,?,?,?)", (bid, arah, it, qt))
    return bid


def buat_wo(con, tgl, bom_id, batch, cat=""):
    one(con, "SELECT 1 FROM bom_h WHERE id=?", (bom_id,))
    wid = con.execute("INSERT INTO wo_h(kode,tanggal,bom_id,batch,status,catatan) VALUES(?,?,?,?,'Direncanakan',?)",
                      (nomor(con, "WO", "wo_h", tgl), tgl, bom_id, batch, cat)).lastrowid
    for d in con.execute("SELECT * FROM bom_d WHERE bom_id=?", (bom_id,)).fetchall():
        con.execute("INSERT INTO wo_d(wo_id,arah,item_id,qty_rencana) VALUES(?,?,?,?)", (wid, d["arah"], d["item_id"], d["qty"] * batch))
    return wid


def mulai_wo(con, wid, tgl):
    wo = one(con, "SELECT * FROM wo_h WHERE id=?", (wid,))
    if wo["status"] != "Direncanakan":
        raise Gagal("Perintah produksi ini sudah dimulai.")
    per_akun = {}
    for d in con.execute("SELECT * FROM wo_d WHERE wo_id=? AND arah='Masuk'", (wid,)).fetchall():
        n = keluar(con, tgl, d["item_id"], d["qty_rencana"], "Produksi", wo["kode"], "Pemakaian bahan untuk produksi")
        con.execute("UPDATE wo_d SET qty_aktual=?, nilai=? WHERE id=?", (d["qty_rencana"], n, d["id"]))
        ak = akun_item(con, d["item_id"])
        per_akun[ak] = per_akun.get(ak, 0) + n
    jurnal(con, tgl, "Produksi", wo["kode"], [dr("1303", sum(per_akun.values()))] + [cr(a, v) for a, v in per_akun.items()], "Bahan ke produksi (WIP)")
    con.execute("UPDATE wo_h SET status='Berjalan', tgl_mulai=? WHERE id=?", (tgl, wid))


def selesai_wo(con, wid, tgl, hasil, biaya_lain):
    """Hasil jadi masuk persediaan (lot baru). Biaya (bahan + biaya lain) dialokasikan menurut nilai jual relatif."""
    wo = one(con, "SELECT * FROM wo_h WHERE id=?", (wid,))
    if wo["status"] != "Berjalan":
        raise Gagal("Produksi harus dalam status Berjalan.")
    total_bahan = sum(r["nilai"] for r in con.execute("SELECT nilai FROM wo_d WHERE wo_id=? AND arah='Masuk'", (wid,)))
    total = total_bahan + biaya_lain
    outs = con.execute("SELECT d.*, i.harga_std FROM wo_d d JOIN item i ON i.id=d.item_id WHERE d.wo_id=? AND d.arah='Keluar'", (wid,)).fetchall()
    qa = {o["id"]: hasil.get(o["id"], 0.0) for o in outs}
    bobot = {o["id"]: qa[o["id"]] * (o["harga_std"] or 1) for o in outs}
    sb = sum(bobot.values())
    if sb <= 0:
        raise Gagal("Isi hasil produksi aktual minimal pada satu produk.")
    per_akun = {}
    for o in outs:
        share = total * bobot[o["id"]] / sb
        if qa[o["id"]] > 0:
            masuk_lot(con, tgl, o["item_id"], qa[o["id"]], "Hasil Produksi", wo["kode"], "Hasil pengolahan masuk gudang", share, asal="Produksi")
            ak = akun_item(con, o["item_id"])
            per_akun[ak] = per_akun.get(ak, 0) + share
        con.execute("UPDATE wo_d SET qty_aktual=?, nilai=? WHERE id=?", (qa[o["id"]], share, o["id"]))
    lines = [dr(a, v) for a, v in per_akun.items()] + [cr("1303", total_bahan)]
    if biaya_lain > 0:
        lines += [cr("1101", biaya_lain)]
    jurnal(con, tgl, "Produksi", wo["kode"], lines, "Hasil produksi masuk persediaan (biaya lain dikapitalisasi)")
    con.execute("UPDATE wo_h SET status='Selesai', tgl_selesai=?, biaya_lain=? WHERE id=?", (tgl, biaya_lain, wid))


# ------------------------------------------------------------- upah & beban
def kg_bahan(con, awal, akhir):
    return con.execute("""SELECT COALESCE(SUM(d.qty_aktual),0) FROM wo_d d JOIN wo_h w ON w.id=d.wo_id JOIN item i ON i.id=d.item_id
        WHERE d.arah='Masuk' AND i.jenis='Bahan Baku' AND w.tgl_mulai BETWEEN ? AND ? AND w.status IN ('Berjalan','Selesai')""", (awal, akhir)).fetchone()[0]


def hitung_payroll(con, pid):
    h = con.execute("SELECT kg_bahan FROM payroll_h WHERE id=?", (pid,)).fetchone()[0]
    for d in con.execute("SELECT * FROM payroll_d WHERE payroll_id=?", (pid,)).fetchall():
        pokok, premi = d["hadir"] * d["tarif"], d["premi_kg"] * h
        con.execute("UPDATE payroll_d SET upah_pokok=?, premi=?, total=? WHERE id=?", (pokok, premi, pokok + premi, d["id"]))
    con.execute("UPDATE payroll_h SET total=(SELECT COALESCE(SUM(total),0) FROM payroll_d WHERE payroll_id=?) WHERE id=?", (pid, pid))


def buat_payroll(con, tipe, awal, akhir):
    a, b = date.fromisoformat(awal), date.fromisoformat(akhir)
    if b < a:
        raise Gagal("Tanggal akhir tidak boleh sebelum tanggal awal.")
    peg = con.execute("SELECT * FROM karyawan WHERE aktif=1 AND tipe=?", (tipe,)).fetchall()
    if not peg:
        raise Gagal("Belum ada karyawan %s yang aktif." % tipe.lower())
    hari = (b - a).days + 1
    if tipe == "Harian":
        unit = sum(1 for i in range(hari) if (a + timedelta(i)).weekday() != 6)
    elif tipe == "Mingguan":
        unit = max(1, round(hari / 7))
    else:
        unit = max(1, round(hari / 30))
    pid = con.execute("INSERT INTO payroll_h(kode,tipe,tgl_awal,tgl_akhir,kg_bahan,status) VALUES(?,?,?,?,?,'Draft')",
                      (nomor(con, "PAY", "payroll_h", akhir), tipe, awal, akhir, kg_bahan(con, awal, akhir))).lastrowid
    for k in peg:
        con.execute("INSERT INTO payroll_d(payroll_id,karyawan_id,hadir,tarif,premi_kg) VALUES(?,?,?,?,?)", (pid, k["id"], unit, k["upah_dasar"], k["premi_per_kg"]))
    hitung_payroll(con, pid)
    return pid


def final_payroll(con, pid, tgl):
    p = one(con, "SELECT * FROM payroll_h WHERE id=?", (pid,))
    if p["status"] != "Draft":
        raise Gagal("Penggajian ini sudah dibayar.")
    con.execute("UPDATE payroll_h SET status='Dibayar', tgl_bayar=? WHERE id=?", (tgl, pid))
    con.execute("INSERT INTO beban(tanggal,kategori,jumlah,ket,sumber,ref) VALUES(?,?,?,?,'otomatis',?)",
                (tgl, "Gaji & Upah", p["total"], "Upah %s %s s/d %s" % (p["tipe"].lower(), p["tgl_awal"], p["tgl_akhir"]), p["kode"]))
    jurnal(con, tgl, "Penggajian", p["kode"], [dr("6101", p["total"]), cr("1101", p["total"])], "Pembayaran upah " + p["tipe"].lower())


def catat_beban(con, tgl, kategori, jumlah, ket):
    con.execute("INSERT INTO beban(tanggal,kategori,jumlah,ket,sumber) VALUES(?,?,?,?,'manual')", (tgl, kategori, jumlah, ket))
    jurnal(con, tgl, "Beban", kategori, [dr("6102", jumlah), cr("1101", jumlah)], ket or kategori)


# ------------------------------------------------------------- laporan keuangan & valuasi
def lr_bulanan(con):
    m = {}

    def put(sql, key):
        for r in con.execute(sql):
            m.setdefault(r["b"], dict(bln=r["b"], pen=0, hpp=0, beban=0))[key] += r["v"] or 0
    put("SELECT substr(tanggal,1,7) b, SUM(dpp) v FROM inv_h GROUP BY b", "pen")
    put("SELECT substr(i.tanggal,1,7) b, SUM(d.hpp_total) v FROM inv_h i JOIN do_d d ON d.do_id=i.do_id GROUP BY b", "hpp")
    put("SELECT substr(tanggal,1,7) b, SUM(jumlah) v FROM beban GROUP BY b", "beban")
    out = [m[k] for k in sorted(m)]
    for r in out:
        r["laba"] = r["pen"] - r["hpp"] - r["beban"]
    return out


def neraca_saldo(con):
    rows = con.execute("""SELECT c.kode, c.nama, c.tipe, COALESCE(SUM(j.debit),0) d, COALESCE(SUM(j.kredit),0) k
        FROM coa c LEFT JOIN jurnal j ON j.akun=c.kode GROUP BY c.kode ORDER BY c.kode""").fetchall()
    return [dict(kode=r["kode"], nama=r["nama"], tipe=r["tipe"], d=r["d"], k=r["k"], saldo=r["d"] - r["k"]) for r in rows]


def rekonsiliasi(con):
    """Bandingkan buku besar pembantu (nilai lot/item) dengan saldo akun jurnal."""
    gl = {r["kode"]: r["saldo"] for r in neraca_saldo(con)}
    sub = {j: con.execute("SELECT COALESCE(SUM(nilai),0) FROM item WHERE jenis=?", (j,)).fetchone()[0] for j in AKUN_ITEM}
    wip = con.execute("SELECT COALESCE(SUM(d.nilai),0) FROM wo_d d JOIN wo_h w ON w.id=d.wo_id WHERE d.arah='Masuk' AND w.status='Berjalan'").fetchone()[0]
    transit = con.execute("SELECT COALESCE(SUM(d.hpp_total),0) FROM do_d d WHERE d.do_id NOT IN (SELECT do_id FROM inv_h)").fetchone()[0]
    rows = [("1301", "Persediaan Bahan Baku", sub["Bahan Baku"]), ("1302", "Persediaan Bahan Pembantu", sub["Bahan Pembantu"]),
            ("1304", "Persediaan Barang Jadi", sub["Barang Jadi"]), ("1303", "Barang Dalam Proses", wip), ("1305", "Persediaan dalam Pengiriman", transit)]
    return [dict(akun=a, nama=n, sub=s, gl=gl.get(a, 0), selisih=s - gl.get(a, 0)) for a, n, s in rows]


def valuasi_metode(con):
    """Simulasi nilai keluar (pemakaian/penjualan) dan nilai persediaan akhir menurut FIFO, LIFO, dan rata-rata dari riwayat mutasi."""
    out = []
    for it in con.execute("SELECT * FROM item WHERE aktif=1 ORDER BY jenis, nama").fetchall():
        mv = con.execute("SELECT qty, nilai FROM stok_gerak WHERE item_id=? ORDER BY id", (it["id"],)).fetchall()
        res = {}
        for m in ("FIFO", "LIFO", "AVG"):
            lay, stok, nilai, kel = [], 0.0, 0.0, 0.0
            for r in mv:
                if r["qty"] > 0:
                    lay.append([r["qty"], r["nilai"] / r["qty"]])
                    stok += r["qty"]
                    nilai += r["nilai"]
                else:
                    need = -r["qty"]
                    if m == "AVG":
                        c = need * (nilai / stok if stok > 1e-9 else 0)
                        nilai -= c
                        stok -= need
                        kel += c
                        continue
                    while need > 1e-9 and lay:
                        L = lay[0] if m == "FIFO" else lay[-1]
                        t = min(L[0], need)
                        kel += t * L[1]
                        L[0] -= t
                        need -= t
                        if L[0] <= 1e-9:
                            lay.pop(0 if m == "FIFO" else -1)
            akhir = nilai if m == "AVG" else sum(a * b for a, b in lay)
            res[m] = dict(keluar=kel, akhir=akhir)
        out.append(dict(item=it, hasil=res))
    return out


def cogs_per_item(con):
    return con.execute("""SELECT substr(i.tanggal,1,7) bln, t.kode, t.nama, SUM(d.qty) qty, SUM(d.hpp_total) hpp,
        SUM(d.qty*sd.harga) penjualan FROM inv_h i JOIN do_d d ON d.do_id=i.do_id JOIN so_d sd ON sd.id=d.so_d_id
        JOIN item t ON t.id=d.item_id GROUP BY bln, t.id ORDER BY bln, t.nama""").fetchall()


# ------------------------------------------------------------- SCM
def lead_time_aktual(con, item_id):
    r = con.execute("""SELECT AVG(julianday(g.tanggal)-julianday(p.tanggal)) FROM gr_d d JOIN gr_h g ON g.id=d.gr_id
        JOIN po_h p ON p.id=g.po_id WHERE d.item_id=? AND d.qty>0""", (item_id,)).fetchone()[0]
    return r


def rencana_material(con, hari=90):
    t = date.today()
    awal = (t - timedelta(days=hari)).isoformat()
    out = []
    for it in con.execute("SELECT * FROM item WHERE aktif=1 AND jenis!='Barang Jadi' ORDER BY jenis, nama").fetchall():
        tersedia = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE item_id=? AND status='Tersedia'", (it["id"],)).fetchone()[0]
        kar = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE item_id=? AND status='Karantina'", (it["id"],)).fetchone()[0]
        po_open = con.execute("""SELECT COALESCE(SUM(d.qty-d.qty_diterima),0) FROM po_d d JOIN po_h h ON h.id=d.po_id
            WHERE d.item_id=? AND h.status IN ('Open','Sebagian Diterima')""", (it["id"],)).fetchone()[0]
        pakai = it["pakai_harian"] or (con.execute("SELECT COALESCE(SUM(-qty),0) FROM stok_gerak WHERE item_id=? AND tipe='Produksi' AND tanggal>=?", (it["id"], awal)).fetchone()[0] / hari)
        lt_akt = lead_time_aktual(con, it["id"])
        lt = it["lead_time_hari"] or lt_akt or 0
        rop = pakai * lt + it["safety_stock"]
        posisi = tersedia + po_open
        D, S, H = pakai * 365, it["biaya_pesan"], (it["harga_std"] or 0) * it["biaya_simpan_pct"] / 100
        eoq = math.sqrt(2 * D * S / H) if D > 0 and S > 0 and H > 0 else 0
        st = "Pesan Sekarang" if posisi <= rop else "Waspada" if posisi <= rop * 1.25 else "Aman"
        rek = 0.0
        if st != "Aman":
            rek = max(it["moq"] or 0, eoq, rop - posisi)
            isi = con.execute("SELECT isi FROM kemasan WHERE id=?", (it["kemasan_id"],)).fetchone() if it["kemasan_id"] else None
            unit = (isi["isi"] if isi and isi["isi"] else 100)
            rek = math.ceil(rek / unit) * unit
        out.append(dict(item=it, tersedia=tersedia, karantina=kar, po_open=po_open, pakai=pakai, lt=lt, lt_akt=lt_akt, rop=rop, posisi=posisi,
                        hari=(tersedia / pakai if pakai > 0 else None), eoq=eoq, status=st, rekomendasi=rek))
    return out


def mrp_dari_so(con):
    """Kebutuhan material dari sales order yang belum terkirim, dihitung mundur lewat BOM dan dibandingkan stok + PO berjalan."""
    need = {}
    for r in con.execute("""SELECT d.item_id, SUM(d.qty-d.qty_terkirim) sisa FROM so_d d JOIN so_h h ON h.id=d.so_id
            WHERE h.status IN ('Open','Sebagian Dikirim') AND d.qty>d.qty_terkirim GROUP BY d.item_id""").fetchall():
        stok = con.execute("SELECT stok FROM item WHERE id=?", (r["item_id"],)).fetchone()[0]
        net = r["sisa"] - stok
        if net <= 0:
            continue
        bom = con.execute("SELECT bom_id, qty FROM bom_d WHERE item_id=? AND arah='Keluar' ORDER BY id LIMIT 1", (r["item_id"],)).fetchone()
        if not bom:
            continue
        batch = net / bom["qty"]
        for m in con.execute("SELECT item_id, qty FROM bom_d WHERE bom_id=? AND arah='Masuk'", (bom["bom_id"],)).fetchall():
            need.setdefault(m["item_id"], [0.0, 0.0])[0] += batch * m["qty"]
            need[m["item_id"]][1] += batch
    out = []
    for iid, (qty, batch) in need.items():
        it = con.execute("SELECT * FROM item WHERE id=?", (iid,)).fetchone()
        tersedia = con.execute("SELECT COALESCE(SUM(qty_sisa),0) FROM lot WHERE item_id=? AND status='Tersedia'", (iid,)).fetchone()[0]
        po = con.execute("SELECT COALESCE(SUM(d.qty-d.qty_diterima),0) FROM po_d d JOIN po_h h ON h.id=d.po_id WHERE d.item_id=? AND h.status IN ('Open','Sebagian Diterima')", (iid,)).fetchone()[0]
        out.append(dict(item=it, butuh=qty, tersedia=tersedia, po=po, kurang=max(0, qty - tersedia - po), batch=batch))
    return out


def kinerja_supplier(con):
    out = []
    for s in con.execute("SELECT * FROM supplier WHERE aktif=1 ORDER BY nama").fetchall():
        gr = con.execute("""SELECT g.id, g.tanggal, p.eta, p.tanggal ptgl FROM gr_h g JOIN po_h p ON p.id=g.po_id
            WHERE p.supplier_id=? AND EXISTS (SELECT 1 FROM gr_d WHERE gr_id=g.id AND qty>0)""", (s["id"],)).fetchall()
        if not gr:
            continue
        tepat = sum(1 for g in gr if g["tanggal"] <= g["eta"])
        lt = sum((date.fromisoformat(g["tanggal"]) - date.fromisoformat(g["ptgl"])).days for g in gr) / len(gr)
        po = con.execute("SELECT COALESCE(SUM(d.qty),0) q, COALESCE(SUM(d.qty_diterima),0) k FROM po_d d JOIN po_h h ON h.id=d.po_id WHERE h.supplier_id=? AND h.status!='Open'", (s["id"],)).fetchone()
        rj = con.execute("SELECT COALESCE(SUM(d.qty),0) a, COALESCE(SUM(d.qty_tolak),0) t FROM gr_d d JOIN gr_h g ON g.id=d.gr_id JOIN po_h p ON p.id=g.po_id WHERE p.supplier_id=?", (s["id"],)).fetchone()
        otd = tepat / len(gr) * 100
        fill = po["k"] / po["q"] * 100 if po["q"] else 100
        mutu = 100 - (rj["t"] / (rj["a"] + rj["t"]) * 100 if rj["a"] + rj["t"] else 0)
        skor = 0.4 * otd + 0.3 * fill + 0.3 * mutu
        out.append(dict(supplier=s, n=len(gr), otd=otd, fill=fill, mutu=mutu, lt=lt, lt_std=s["lead_time_hari"], skor=skor,
                        grade="A" if skor >= 90 else "B" if skor >= 75 else "C"))
    return out


def inbound(con):
    t = date.today()
    out = []
    for r in con.execute("""SELECT h.kode, h.tanggal, h.eta, s.nama sup, i.nama item, i.satuan, d.qty, d.qty_diterima FROM po_d d
        JOIN po_h h ON h.id=d.po_id JOIN supplier s ON s.id=h.supplier_id JOIN item i ON i.id=d.item_id
        WHERE h.status IN ('Open','Sebagian Diterima') AND d.qty>d.qty_diterima ORDER BY h.eta""").fetchall():
        sel = (date.fromisoformat(r["eta"]) - t).days
        st = "Terlambat" if sel < 0 else "Hari ini" if sel == 0 else "Segera" if sel <= 3 else "Terjadwal"
        out.append(dict(r, sisa=r["qty"] - r["qty_diterima"], selisih=sel, status=st))
    return out


def otif(con):
    t = date.today().isoformat()
    selesai = con.execute("""SELECT h.id, h.tgl_kirim, (SELECT MAX(tanggal) FROM do_h WHERE so_id=h.id) last FROM so_h h
        WHERE h.status IN ('Terkirim','Selesai')""").fetchall()
    ok = sum(1 for r in selesai if r["last"] <= r["tgl_kirim"])
    telat_open = con.execute("SELECT COUNT(*) FROM so_h WHERE status IN ('Open','Sebagian Dikirim') AND tgl_kirim<?", (t,)).fetchone()[0]
    n = len(selesai) + telat_open
    return dict(persen=(ok / n * 100 if n else 100), tepat=ok, selesai=len(selesai), telat_open=telat_open)


def umur_lot(con):
    t = date.today()
    b = {"0-7 hari": 0.0, "8-30 hari": 0.0, "31-60 hari": 0.0, "> 60 hari": 0.0}
    for r in con.execute("SELECT tanggal, qty_sisa*biaya_unit v FROM lot WHERE qty_sisa>0.000001").fetchall():
        d = (t - date.fromisoformat(r["tanggal"])).days
        b["0-7 hari" if d <= 7 else "8-30 hari" if d <= 30 else "31-60 hari" if d <= 60 else "> 60 hari"] += r["v"]
    return b


def util_lokasi(con):
    return con.execute("""SELECT l.*, COALESCE((SELECT SUM(qty_sisa) FROM lot WHERE lokasi_id=l.id AND qty_sisa>0.000001),0) isi
        FROM lokasi l WHERE aktif=1 ORDER BY kode""").fetchall()


def tren_harga(con, item_id):
    """Riwayat harga beli per supplier (dari PO) untuk satu material, lengkap dengan statistik."""
    rows = con.execute("""SELECT s.id sid, s.kode, s.nama, h.tanggal, h.kode po, d.harga, d.qty FROM po_d d JOIN po_h h ON h.id=d.po_id
        JOIN supplier s ON s.id=h.supplier_id WHERE d.item_id=? ORDER BY h.tanggal, h.id""", (item_id,)).fetchall()
    seri = {}
    for r in rows:
        seri.setdefault(r["sid"], dict(kode=r["kode"], nama=r["nama"], titik=[]))["titik"].append(
            dict(tanggal=r["tanggal"], harga=r["harga"], qty=r["qty"], po=r["po"]))
    out = []
    for s in seri.values():
        t = s["titik"]
        hs = [x["harga"] for x in t]
        qty = sum(x["qty"] for x in t)
        s.update(n=len(t), qty=qty, rata=sum(x["harga"] * x["qty"] for x in t) / qty if qty else 0, min=min(hs), max=max(hs),
                 awal=hs[0], akhir=hs[-1], ubah=((hs[-1] - hs[0]) / hs[0] * 100 if hs[0] and len(hs) > 1 else 0))
        out.append(s)
    return out


def migrasi(con):
    """Menambah kolom baru pada database lama tanpa menghapus data."""
    for tabel in ("customer", "supplier"):
        ada = {r[1] for r in con.execute("PRAGMA table_info(%s)" % tabel)}
        for kol, ddl in (("email", "TEXT"), ("no_wa", "TEXT"), ("negara", "TEXT DEFAULT 'Indonesia'"), ("pic", "TEXT")):
            if kol not in ada:
                con.execute("ALTER TABLE %s ADD COLUMN %s %s" % (tabel, kol, ddl))
