"""
SawitFlow ERP - ERP mini untuk pembelajaran logistik kelapa sawit.
Siklus: Master -> PO -> Penerimaan -> Stok -> BOM/Produksi -> SO -> Surat Jalan -> Invoice -> Pembayaran -> Laba Rugi.
Jalankan: python app.py
"""
import os, re, secrets, sqlite3, time
from datetime import date, datetime, timedelta
from functools import wraps

from flask import Flask, abort, flash, g, redirect, render_template, request, session, url_for
from jinja2 import DictLoader
from markupsafe import Markup, escape
from werkzeug.security import check_password_hash, generate_password_hash

import barcode as bc
import erp
import loader
from erp import Gagal, fq

BASE = os.path.dirname(os.path.abspath(__file__))
DB = os.path.join(BASE, "erp_sawit.db")
KEYFILE = os.path.join(BASE, ".secret_key")
if not os.path.exists(KEYFILE):
    with open(KEYFILE, "w") as f:
        f.write(secrets.token_hex(32))

app = Flask(__name__)
app.secret_key = open(KEYFILE).read().strip()
app.config.update(SESSION_COOKIE_HTTPONLY=True, SESSION_COOKIE_SAMESITE="Lax", PERMANENT_SESSION_LIFETIME=timedelta(hours=8))

ROLES = {"admin": "Administrator", "sales": "Sales", "purchasing": "Pembelian", "gudang": "Gudang & Logistik",
         "produksi": "Produksi", "keuangan": "Keuangan", "dosen": "Dosen (lihat saja)"}
JENIS_ITEM = ["Bahan Baku", "Bahan Pembantu", "Barang Jadi"]
TIPE_LOKASI = ["Ramp Bongkar", "Gudang", "Tangki", "Yard", "Karantina"]
KAT_BEBAN = ["Listrik & Air", "Perawatan Mesin", "Administrasi", "Transportasi", "Lainnya"]

MASTER = {
    "customer": dict(judul="Pelanggan", roles=("sales",), prefix="C", tampil=["kode", "nama", "kota", "termin_hari"],
                     kolom=[("kode", "Kode pelanggan", "text"), ("nama", "Nama pelanggan", "text"), ("pic", "Nama kontak (PIC)", "text"),
                            ("telp", "Nomor kontak / telepon", "text"), ("no_wa", "Nomor WhatsApp", "text"), ("email", "Alamat email", "text"),
                            ("alamat", "Alamat", "text"), ("kota", "Kota", "text"), ("negara", "Negara", "text"),
                            ("npwp", "NPWP / NIK (untuk faktur pajak)", "text"), ("termin_hari", "Termin bayar (hari)", "int"),
                            ("pkp", "Pembeli berstatus PKP", "bool")]),
    "supplier": dict(judul="Supplier", roles=("purchasing",), prefix="S", tampil=["kode", "nama", "kota", "termin_hari"],
                     kolom=[("kode", "Kode supplier", "text"), ("nama", "Nama supplier", "text"), ("pic", "Nama kontak (PIC)", "text"),
                            ("telp", "Nomor kontak / telepon", "text"), ("no_wa", "Nomor WhatsApp", "text"), ("email", "Alamat email", "text"),
                            ("alamat", "Alamat", "text"), ("kota", "Kota", "text"), ("negara", "Negara", "text"),
                            ("npwp", "NPWP / NIK", "text"), ("termin_hari", "Termin bayar (hari)", "int"),
                            ("lead_time_hari", "Lead time standar (hari)", "int"), ("pkp", "Supplier berstatus PKP", "bool")]),
    "item": dict(judul="Barang & Material", roles=("gudang", "purchasing"), prefix=None,
                 tampil=["kode", "nama", "jenis", "satuan", "metode", "masa_simpan_hari", "lead_time_hari", "safety_stock", "moq", "harga_std"],
                 kolom=[("kode", "Kode barang", "text"), ("nama", "Nama barang", "text"), ("jenis", "Jenis", "sel:" + ",".join(JENIS_ITEM)),
                        ("satuan", "Satuan", "text"), ("harga_std", "Harga standar / acuan (Rp)", "num"), ("stok_min", "Stok minimum", "num"),
                        ("metode", "Metode persediaan (pengeluaran & nilai)", "sel:FIFO,LIFO,AVG"), ("kemasan_id", "Kemasan default", "fk:kemasan"),
                        ("lokasi_id", "Lokasi simpan default", "fk:lokasi"), ("masa_simpan_hari", "Masa simpan (hari, 0 = tak terbatas)", "int"),
                        ("lead_time_hari", "Lead time pasok (hari)", "int"), ("safety_stock", "Safety stock", "num"),
                        ("pakai_harian", "Pemakaian harian (0 = hitung otomatis)", "num"), ("moq", "Minimum order (MOQ)", "num"),
                        ("biaya_pesan", "Biaya sekali pesan (Rp)", "num"), ("biaya_simpan_pct", "Biaya simpan (% harga / tahun)", "num")]),
    "lokasi": dict(judul="Lokasi Gudang", roles=("gudang",), prefix=None, tampil=["kode", "nama", "tipe", "kapasitas"],
                   kolom=[("kode", "Kode lokasi", "text"), ("nama", "Nama lokasi", "text"), ("tipe", "Tipe", "sel:" + ",".join(TIPE_LOKASI)),
                          ("kapasitas", "Kapasitas (satuan barang)", "num")]),
    "kemasan": dict(judul="Kemasan", roles=("gudang",), prefix=None, tampil=["kode", "nama", "isi", "satuan"],
                    kolom=[("kode", "Kode kemasan", "text"), ("nama", "Nama kemasan", "text"), ("isi", "Isi per kemasan (0 = curah)", "num"),
                           ("satuan", "Satuan isi", "text")]),
    "karyawan": dict(judul="Karyawan", roles=("keuangan",), prefix=None, tampil=["nama", "bagian", "tipe", "upah_dasar", "premi_per_kg"],
                     kolom=[("nama", "Nama", "text"), ("bagian", "Bagian", "text"), ("tipe", "Jenis upah", "sel:Harian,Mingguan,Bulanan"),
                            ("upah_dasar", "Upah dasar per hari / minggu / bulan (Rp)", "num"),
                            ("premi_per_kg", "Premi per kg bahan utama diolah (Rp)", "num")]),
}
LABEL = {k: l for m in MASTER.values() for k, l, _ in m["kolom"]}
LABEL.update(kode="Kode", termin_hari="Termin (hari)", pkp="PKP", stok_min="Stok min", harga_std="Harga acuan", metode="Metode",
             masa_simpan_hari="Masa simpan (hr)", lead_time_hari="Lead time (hr)", safety_stock="Safety stock", moq="MOQ",
             premi_per_kg="Premi/kg", upah_dasar="Upah dasar", isi="Isi", kapasitas="Kapasitas", npwp="NPWP", nama="Nama", tipe="Tipe",
             jenis="Jenis", satuan="Satuan", bagian="Bagian", kota="Kota")
DEFAULT_FORM = {"termin_hari": "30", "lead_time_hari": "7", "biaya_simpan_pct": "20", "negara": "Indonesia"}
TEL = re.compile(r"^[0-9+\-\s().]{6,24}$")
EMAIL = re.compile(r"^[^@\s]+@[^@\s]+\.[^@\s]+$")


# ------------------------------------------------------------------ database
def db():
    if "db" not in g:
        g.db = sqlite3.connect(DB)
        g.db.row_factory = sqlite3.Row
    return g.db


@app.teardown_appcontext
def close_db(_):
    con = g.pop("db", None)
    if con:
        con.close()


def q(sql, args=(), one=False):
    rows = db().execute(sql, args).fetchall()
    return (rows[0] if rows else None) if one else rows


def scalar(sql, args=()):
    return db().execute(sql, args).fetchone()[0] or 0


def setting():
    return {r["key"]: r["value"] for r in q("SELECT * FROM setting")}


# ------------------------------------------------------------- keamanan/auth
ATTEMPTS = {}


def csrf():
    if "_csrf" not in session:
        session["_csrf"] = secrets.token_hex(16)
    return session["_csrf"]


@app.before_request
def guard():
    if request.method == "POST" and not secrets.compare_digest(request.form.get("_csrf", ""), session.get("_csrf", "x")):
        abort(400, "Token keamanan tidak valid. Muat ulang halaman lalu coba lagi.")
    g.user = None
    if session.get("uid"):
        g.user = q("SELECT * FROM users WHERE id=? AND aktif=1", (session["uid"],), one=True)
        if not g.user:
            session.clear()
    if not g.user and request.endpoint not in ("login", "static"):
        return redirect(url_for("login"))


@app.after_request
def headers(r):
    r.headers.update({"X-Frame-Options": "DENY", "X-Content-Type-Options": "nosniff", "Cache-Control": "no-store"})
    return r


def roles(*allowed):
    def deco(fn):
        @wraps(fn)
        def wrap(*a, **k):
            if g.user["role"] != "admin" and g.user["role"] not in allowed:
                abort(403)
            return fn(*a, **k)
        return wrap
    return deco


def can(*allowed):
    return bool(g.user) and (g.user["role"] == "admin" or g.user["role"] in allowed)


def catat(aksi):
    db().execute("INSERT INTO log(waktu,user,aksi) VALUES(?,?,?)", (datetime.now().strftime("%Y-%m-%d %H:%M:%S"), g.user["username"], aksi))


def rp(v):
    v = v or 0
    s = "{:,.0f}".format(abs(v)).replace(",", ".")
    return ("-Rp " if v < 0 and round(abs(v)) else "Rp ") + s


def num(v):
    return "{:,.0f}".format(v or 0).replace(",", ".")


def tg(s):
    return "%s/%s/%s" % (s[8:10], s[5:7], s[0:4]) if s else "-"


def badge(s):
    return Markup('<span class="badge b-%s">%s</span>') % (str(s).replace(" ", "").replace(".", ""), s)


def stt(r):
    return "Jatuh Tempo" if (r["total"] - r["dibayar"] > 0.5 and r["jatuh_tempo"] < date.today().isoformat()) else r["status"]


@app.context_processor
def ctx():
    return dict(badge=badge, stt=stt, csrfi=lambda: Markup('<input type="hidden" name="_csrf" value="%s">') % csrf(), csrf=csrf, rp=rp, num=num, fq=fq, tg=tg, user=g.get("user"), ROLES=ROLES, can=can,
                S=setting() if g.get("user") else {}, today=date.today().isoformat())


@app.errorhandler(Gagal)
def err_gagal(e):
    db().rollback()
    flash(str(e), "err")
    return redirect(request.referrer or url_for("dashboard"))


@app.errorhandler(403)
def e403(_):
    return render_template("msg", title="Akses ditolak", msg="Peran Anda tidak punya izin untuk aksi ini."), 403


@app.errorhandler(404)
def e404(_):
    return render_template("msg", title="Tidak ditemukan", msg="Halaman atau data yang dicari tidak ada."), 404


@app.errorhandler(400)
def e400(e):
    return render_template("msg", title="Permintaan tidak valid", msg=e.description), 400


# ---------------------------------------------------------------- helper form
def fl(name, label, minimum=None, wajib=True):
    raw = (request.form.get(name) or "").strip().replace(",", ".")
    if raw == "":
        if wajib:
            raise Gagal("Isi %s." % label)
        return 0.0
    try:
        v = float(raw)
    except ValueError:
        raise Gagal("%s harus berupa angka." % label)
    if minimum is not None and v < minimum:
        raise Gagal("%s tidak boleh kurang dari %s." % (label, fq(minimum)))
    return v


def tx(name, label, wajib=True, maks=120):
    v = (request.form.get(name) or "").strip()[:maks]
    if wajib and not v:
        raise Gagal("Isi %s." % label)
    return v


def tgl_form(name="tanggal"):
    v = request.form.get(name) or date.today().isoformat()
    try:
        date.fromisoformat(v)
    except ValueError:
        raise Gagal("Format tanggal tidak valid.")
    return v


def ident(name, label):
    try:
        return int(request.form.get(name, ""))
    except ValueError:
        raise Gagal("Pilih %s." % label)


def baris(harga=True):
    out = []
    for i, (it, qt) in enumerate(zip(request.form.getlist("item_id"), request.form.getlist("qty"))):
        if not qt.strip():
            continue
        try:
            qv = float(qt.replace(",", "."))
            hg = float((request.form.getlist("harga")[i] or "0").replace(",", ".")) if harga else 0
            iid = int(it)
        except (ValueError, IndexError):
            raise Gagal("Baris %d: jumlah/harga harus angka." % (i + 1))
        if qv <= 0 or hg < 0:
            raise Gagal("Baris %d: jumlah harus lebih dari 0." % (i + 1))
        out.append((iid, qv, hg))
    return out


def qtys(prefix):
    out = {}
    for k, v in request.form.items():
        if k.startswith(prefix):
            try:
                out[int(k[len(prefix):])] = float((v or "0").replace(",", "."))
            except ValueError:
                raise Gagal("Jumlah harus berupa angka.")
    return out


def ppn_dari_form():
    return fl("ppn_persen", "PPN", 0, False) if request.form.get("ppn_on") else 0.0


def kembali(endpoint, **kw):
    return redirect(url_for(endpoint, **kw))


# ---------------------------------------------------------- login & akun
@app.route("/login", methods=["GET", "POST"])
def login():
    err = None
    if request.method == "POST":
        u = request.form.get("username", "").strip().lower()
        key = (request.remote_addr, u)
        n, t = ATTEMPTS.get(key, (0, 0))
        if n >= 5 and time.time() - t < 300:
            err = "Terlalu banyak percobaan. Coba lagi dalam 5 menit."
        else:
            row = q("SELECT * FROM users WHERE username=? AND aktif=1", (u,), one=True)
            if row and check_password_hash(row["pw_hash"], request.form.get("password", "")):
                ATTEMPTS.pop(key, None)
                session.clear()
                session.permanent = True
                session["uid"] = row["id"]
                g.user = row
                catat("login")
                db().commit()
                return kembali("dashboard")
            ATTEMPTS[key] = (n + 1 if time.time() - t < 300 else 1, time.time())
            err = "Username atau password salah."
    return render_template("login", err=err)


@app.route("/logout", methods=["POST"])
def logout():
    catat("logout")
    db().commit()
    session.clear()
    return kembali("login")


@app.route("/password", methods=["GET", "POST"])
def password():
    if request.method == "POST":
        if not check_password_hash(g.user["pw_hash"], request.form.get("lama", "")):
            raise Gagal("Password lama tidak cocok.")
        if len(request.form.get("baru", "")) < 8:
            raise Gagal("Password baru minimal 8 karakter.")
        db().execute("UPDATE users SET pw_hash=? WHERE id=?", (generate_password_hash(request.form["baru"]), g.user["id"]))
        catat("ganti password")
        db().commit()
        flash("Password berhasil diganti.", "ok")
        return kembali("dashboard")
    return render_template("password", active="")


# ------------------------------------------------------------------ dashboard
@app.route("/")
def dashboard():
    t = date.today().isoformat()
    lr = erp.lr_bulanan(db())
    now = next((m for m in lr if m["bln"] == t[:7]), dict(pen=0, hpp=0, beban=0, laba=0))
    ar = q("SELECT COALESCE(SUM(total-dibayar),0) v, COUNT(*) n FROM inv_h WHERE total-dibayar>0.5", one=True)
    ar_jt = q("SELECT COALESCE(SUM(total-dibayar),0) v, COUNT(*) n FROM inv_h WHERE total-dibayar>0.5 AND jatuh_tempo<?", (t,), one=True)
    ap = q("SELECT COALESCE(SUM(total-dibayar),0) v FROM ap WHERE total-dibayar>0.5", one=True)
    ap_jt = q("SELECT COALESCE(SUM(total-dibayar),0) v, COUNT(*) n FROM ap WHERE total-dibayar>0.5 AND jatuh_tempo<?", (t,), one=True)
    stok = lambda j: scalar("SELECT SUM(stok) FROM item WHERE jenis=?", (j,))
    chain = [("PO menunggu terima", scalar("SELECT COUNT(*) FROM po_h WHERE status IN ('Open','Sebagian Diterima')"), "", "Pembelian"),
             ("Stok bahan baku", stok("Bahan Baku"), "kg", "Gudang"),
             ("Produksi berjalan", scalar("SELECT COUNT(*) FROM wo_h WHERE status='Berjalan'"), "WO", "Produksi"),
             ("Stok barang jadi", stok("Barang Jadi"), "kg", "Gudang"),
             ("SO belum terkirim", scalar("SELECT COUNT(*) FROM so_h WHERE status IN ('Open','Sebagian Dikirim')"), "", "Sales"),
             ("Piutang beredar", ar["v"], "rp", "Keuangan")]
    low = q("SELECT * FROM item WHERE aktif=1 AND stok<stok_min ORDER BY nama")
    c = dict(so=scalar("SELECT COUNT(*) FROM so_h WHERE status IN ('Open','Sebagian Dikirim')"),
             po=scalar("SELECT COUNT(*) FROM po_h WHERE status IN ('Open','Sebagian Diterima')"),
             dterima=scalar("SELECT COUNT(*) FROM do_h WHERE status='Dikirim'"),
             dinv=scalar("SELECT COUNT(*) FROM do_h WHERE id NOT IN (SELECT do_id FROM inv_h)"),
             wo1=scalar("SELECT COUNT(*) FROM wo_h WHERE status='Direncanakan'"), wo2=scalar("SELECT COUNT(*) FROM wo_h WHERE status='Berjalan'"),
             pay=scalar("SELECT COUNT(*) FROM payroll_h WHERE status='Draft'"),
             kar=scalar("SELECT COUNT(*) FROM lot WHERE status='Karantina' AND qty_sisa>0"),
             telat=sum(1 for r in erp.inbound(db()) if r["status"] == "Terlambat"))
    tugas = [(r, l, n, u) for r, l, n, u in [
        ({"sales", "gudang"}, "Sales order menunggu pengiriman", c["so"], url_for("so_list")),
        ({"purchasing", "gudang"}, "PO menunggu penerimaan barang", c["po"], url_for("po_list")),
        ({"gudang"}, "Surat jalan belum dikonfirmasi diterima", c["dterima"], url_for("so_list")),
        ({"keuangan"}, "Surat jalan belum dibuatkan invoice", c["dinv"], url_for("inv_list")),
        ({"produksi"}, "Perintah produksi belum dimulai", c["wo1"], url_for("wo_list")),
        ({"produksi"}, "Produksi berjalan, menunggu hasil", c["wo2"], url_for("wo_list")),
        ({"keuangan"}, "Invoice pelanggan jatuh tempo", ar_jt["n"], url_for("inv_list")),
        ({"keuangan", "purchasing"}, "Tagihan supplier jatuh tempo", ap_jt["n"], url_for("hutang")),
        ({"keuangan"}, "Penggajian berstatus draft", c["pay"], url_for("payroll_list")),
        ({"gudang", "produksi"}, "Lot karantina menunggu keputusan QC", c["kar"], url_for("lot_list", status="Karantina")),
        ({"purchasing", "gudang"}, "Pesanan material terlambat datang", c["telat"], url_for("inbound_view"))] if n]
    peran = g.user["role"]
    tugas = [t for t in tugas if peran in t[0] or peran in ("admin", "dosen")]
    return render_template("dashboard", active="dashboard", now=now, ar=ar, ar_jt=ar_jt, ap=ap, ap_jt=ap_jt, chain=chain,
                           low=low, tugas=tugas, lr=lr[-6:], mx=max([1] + [max(m["pen"], m["hpp"] + m["beban"]) for m in lr[-6:]]),
                           nilai_stok=scalar("SELECT SUM(nilai) FROM item"))


# ---------------------------------------------------------------- master data
def next_kode(tabel, prefix):
    n = 0
    for r in q("SELECT kode FROM %s" % tabel):
        mm = re.match(r"^%s-(\d+)$" % re.escape(prefix), r["kode"] or "")
        if mm:
            n = max(n, int(mm.group(1)))
    return "%s-%03d" % (prefix, n + 1)


@app.route("/master/<jenis>", methods=["GET", "POST"])
@app.route("/master/<jenis>/<int:rid>", methods=["GET", "POST"])
def master(jenis, rid=None):
    m = MASTER.get(jenis) or abort(404)
    edit = q("SELECT * FROM %s WHERE id=?" % jenis, (rid,), one=True) if rid else None
    if rid and not edit:
        abort(404)
    if request.method == "POST":
        if not can(*m["roles"]):
            abort(403)
        v = {}
        for k, label, t in m["kolom"]:
            if t in ("num", "int"):
                v[k] = fl(k, label, 0, False)
                v[k] = int(v[k]) if t == "int" else v[k]
            elif t == "bool":
                v[k] = 1 if request.form.get(k) else 0
            elif t.startswith("fk:"):
                v[k] = int(request.form[k]) if (request.form.get(k) or "").isdigit() else None
            else:
                v[k] = tx(k, label, wajib=(k == "nama") or (k == "kode" and not m["prefix"]))
        for k, _, t in m["kolom"]:
            if t.startswith("sel:") and v[k] not in t[4:].split(","):
                raise Gagal("Pilihan %s tidak valid." % LABEL[k])
        if m["prefix"]:
            kode = (v.get("kode") or "").strip().upper() or (edit["kode"] if edit else next_kode(jenis, m["prefix"]))
            if not re.match(r"^[A-Z0-9][A-Z0-9._-]{0,19}$", kode):
                raise Gagal("Kode hanya boleh huruf, angka, titik, minus, atau garis bawah (maks. 20 karakter).")
            v["kode"] = kode
        for k in ("telp", "no_wa"):
            if v.get(k) and not TEL.match(v[k]):
                raise Gagal("Format %s tidak valid (gunakan angka, +, -, spasi, atau tanda kurung)." % LABEL[k].lower())
        if v.get("email") and not EMAIL.match(v["email"]):
            raise Gagal("Format alamat email tidak valid.")
        if "negara" in v and not v["negara"]:
            v["negara"] = "Indonesia"
        try:
            if rid:
                db().execute("UPDATE %s SET %s WHERE id=?" % (jenis, ",".join(k + "=?" for k in v)), (*v.values(), rid))
            else:
                db().execute("INSERT INTO %s(%s) VALUES(%s)" % (jenis, ",".join(v), ",".join("?" * len(v))), tuple(v.values()))
        except sqlite3.IntegrityError:
            raise Gagal("Kode %s sudah dipakai." % (v.get("kode") or ""))
        catat("%s %s %s" % ("ubah" if rid else "tambah", jenis, v.get("nama")))
        db().commit()
        flash("Data %s disimpan." % m["judul"].lower(), "ok")
        return kembali("master", jenis=jenis)
    if jenis == "customer":
        rows = q("SELECT c.*, COALESCE((SELECT SUM(total-dibayar) FROM inv_h WHERE customer_id=c.id),0) saldo FROM customer c ORDER BY c.aktif DESC, c.nama COLLATE NOCASE")
    elif jenis == "supplier":
        rows = q("SELECT c.*, COALESCE((SELECT SUM(total-dibayar) FROM ap WHERE supplier_id=c.id),0) saldo FROM supplier c ORDER BY c.aktif DESC, c.nama COLLATE NOCASE")
    else:
        rows = q("SELECT * FROM %s ORDER BY aktif DESC, id" % jenis)
    fk = {"kemasan": q("SELECT id, nama FROM kemasan WHERE aktif=1 ORDER BY nama"), "lokasi": q("SELECT id, nama FROM lokasi WHERE aktif=1 ORDER BY kode")}
    return render_template("master", active=jenis, jenis=jenis, m=m, rows=rows, edit=edit, fk=fk, LABEL=LABEL, DF=DEFAULT_FORM,
                           kolom=m["kolom"], saran=next_kode(jenis, m["prefix"]) if m["prefix"] else "")


@app.route("/master/<jenis>/<int:rid>/aktif", methods=["POST"])
def master_aktif(jenis, rid):
    m = MASTER.get(jenis) or abort(404)
    if not can(*m["roles"]):
        abort(403)
    db().execute("UPDATE %s SET aktif=1-aktif WHERE id=?" % jenis, (rid,))
    catat("aktif/nonaktif %s %s" % (jenis, rid))
    db().commit()
    return kembali("master", jenis=jenis)


# ----------------------------------------------------------------- stok
@app.route("/stok/opname", methods=["POST"])
@roles("gudang")
def stok_adjust():
    erp.sesuaikan_stok(db(), tgl_form(), ident("item_id", "barang"), fl("fisik", "Stok fisik", 0), tx("ket", "Keterangan", False))
    catat("opname stok")
    db().commit()
    flash("Stok disesuaikan, selisih tercatat di kartu stok dan laba rugi.", "ok")
    return kembali("stok")


@app.route("/stok")
def stok():
    return render_template("stok", active="stok", rows=q("SELECT * FROM item WHERE aktif=1 ORDER BY jenis, nama"))


@app.route("/stok/<int:iid>")
def kartu_stok(iid):
    it = q("SELECT * FROM item WHERE id=?", (iid,), one=True) or abort(404)
    return render_template("kartu", active="stok", it=it, rows=q("SELECT * FROM stok_gerak WHERE item_id=? ORDER BY id DESC LIMIT 100", (iid,)))


# ------------------------------------------------------------- penjualan (SO)
@app.route("/so")
def so_list():
    return render_template("so_list", active="so", rows=q("""SELECT h.*, c.nama cust, (SELECT SUM(qty*harga) FROM so_d WHERE so_id=h.id) dpp
        FROM so_h h JOIN customer c ON c.id=h.customer_id ORDER BY h.id DESC"""))


@app.route("/so/baru", methods=["GET", "POST"])
@roles("sales")
def so_baru():
    if request.method == "POST":
        cid = ident("customer_id", "pelanggan")
        sid = erp.buat_so(db(), tgl_form(), cid, tgl_form("tgl_kirim"), int(fl("termin", "Termin", 0)), ppn_dari_form(),
                          tx("catatan", "Catatan", False), baris(), g.user["username"])
        catat("buat SO id=%s" % sid)
        db().commit()
        flash("Sales order dibuat. Berikutnya: gudang membuat surat jalan.", "ok")
        return kembali("so_detail", sid=sid)
    return render_template("doc_form", active="so", tipe="so", parties=q("SELECT * FROM customer WHERE aktif=1 ORDER BY nama"),
                           items=q("SELECT * FROM item WHERE aktif=1 AND jenis='Barang Jadi' ORDER BY nama"))


@app.route("/so/<int:sid>")
def so_detail(sid):
    h = q("SELECT h.*, c.nama, c.npwp, c.alamat, c.kota FROM so_h h JOIN customer c ON c.id=h.customer_id WHERE h.id=?", (sid,), one=True) or abort(404)
    ls = q("SELECT d.*, i.nama, i.satuan, i.stok FROM so_d d JOIN item i ON i.id=d.item_id WHERE d.so_id=?", (sid,))
    dos = q("SELECT d.*, (SELECT kode FROM inv_h WHERE do_id=d.id) inv_kode, (SELECT id FROM inv_h WHERE do_id=d.id) inv_id FROM do_h d WHERE so_id=? ORDER BY id", (sid,))
    dpp = sum(l["qty"] * l["harga"] for l in ls)
    return render_template("so_detail", active="so", h=h, ls=ls, dos=dos, dpp=dpp, ppn=round(dpp * h["ppn_persen"] / 100))


@app.route("/so/<int:sid>/kirim", methods=["GET", "POST"])
@roles("gudang")
def do_baru(sid):
    h = q("SELECT h.*, c.nama, c.alamat, c.kota FROM so_h h JOIN customer c ON c.id=h.customer_id WHERE h.id=?", (sid,), one=True) or abort(404)
    if request.method == "POST":
        did = erp.buat_do(db(), sid, tgl_form(), tx("kendaraan", "Kendaraan", maks=60), tx("driver", "Driver", maks=60),
                          tx("tujuan", "Tujuan", maks=80), qtys("q_"))
        catat("buat surat jalan id=%s" % did)
        db().commit()
        flash("Surat jalan dibuat dan stok barang jadi berkurang. HPP dihitung dari rata-rata biaya stok.", "ok")
        return kembali("do_detail", did=did)
    ls = q("SELECT d.*, i.nama, i.satuan, i.stok FROM so_d d JOIN item i ON i.id=d.item_id WHERE d.so_id=? AND d.qty>d.qty_terkirim", (sid,))
    return render_template("do_form", active="so", h=h, ls=ls)


@app.route("/sj/<int:did>")
def do_detail(did):
    d = q("""SELECT d.*, h.kode so_kode, h.id so_id, c.nama, c.alamat, c.kota, (SELECT id FROM inv_h WHERE do_id=d.id) inv_id
        FROM do_h d JOIN so_h h ON h.id=d.so_id JOIN customer c ON c.id=h.customer_id WHERE d.id=?""", (did,), one=True) or abort(404)
    ls = q("SELECT dd.*, i.nama, i.satuan FROM do_d dd JOIN item i ON i.id=dd.item_id WHERE dd.do_id=?", (did,))
    return render_template("do_detail", active="so", d=d, ls=ls)


@app.route("/sj/<int:did>/terima", methods=["POST"])
@roles("gudang")
def do_terima(did):
    erp.terima_do(db(), did, tgl_form())
    catat("konfirmasi terima sj %s" % did)
    db().commit()
    flash("Barang dikonfirmasi diterima pelanggan.", "ok")
    return kembali("do_detail", did=did)


@app.route("/sj/<int:did>/invoice", methods=["POST"])
@roles("keuangan")
def inv_baru(did):
    iid = erp.buat_invoice(db(), did, tgl_form())
    catat("buat invoice id=%s" % iid)
    db().commit()
    flash("Invoice dibuat. Jatuh tempo dihitung dari termin pada SO.", "ok")
    return kembali("inv_detail", iid=iid)


@app.route("/invoice")
def inv_list():
    pending = q("""SELECT d.*, h.kode so_kode, c.nama FROM do_h d JOIN so_h h ON h.id=d.so_id JOIN customer c ON c.id=h.customer_id
        WHERE d.id NOT IN (SELECT do_id FROM inv_h) ORDER BY d.id""")
    rows = q("SELECT i.*, c.nama FROM inv_h i JOIN customer c ON c.id=i.customer_id ORDER BY i.id DESC")
    return render_template("inv_list", active="inv", rows=rows, pending=pending)


@app.route("/invoice/<int:iid>")
def inv_detail(iid):
    i = q("SELECT i.*, c.nama, c.npwp, c.alamat, c.kota, s.kode so_kode, s.ppn_persen, s.termin_hari, d.kode sj_kode FROM inv_h i JOIN customer c ON c.id=i.customer_id JOIN so_h s ON s.id=i.so_id JOIN do_h d ON d.id=i.do_id WHERE i.id=?", (iid,), one=True) or abort(404)
    return render_template("inv_detail", active="inv", i=i, ls=q("SELECT d.*, t.nama, t.satuan FROM inv_d d JOIN item t ON t.id=d.item_id WHERE inv_id=?", (iid,)),
                           pays=q("SELECT * FROM pembayaran WHERE tipe='AR' AND ref_id=? ORDER BY id", (iid,)))


@app.route("/bayar/<tipe>/<int:rid>", methods=["POST"])
@roles("keuangan")
def bayar(tipe, rid):
    if tipe not in ("AR", "AP"):
        abort(404)
    erp.bayar(db(), tipe, rid, tgl_form(), fl("jumlah", "Jumlah bayar", 0), request.form.get("metode", "Transfer")[:20], tx("ket", "Keterangan", False))
    catat("pembayaran %s %s" % (tipe, rid))
    db().commit()
    flash("Pembayaran dicatat.", "ok")
    return redirect(request.referrer or url_for("dashboard"))


# ------------------------------------------------------------ pembelian (PO)
@app.route("/po")
def po_list():
    return render_template("po_list", active="po", rows=q("""SELECT h.*, s.nama sup, (SELECT SUM(qty*harga) FROM po_d WHERE po_id=h.id) dpp
        FROM po_h h JOIN supplier s ON s.id=h.supplier_id ORDER BY h.id DESC"""))


@app.route("/po/baru", methods=["GET", "POST"])
@roles("purchasing")
def po_baru():
    if request.method == "POST":
        pid = erp.buat_po(db(), tgl_form(), ident("supplier_id", "supplier"), int(fl("termin", "Termin", 0)), ppn_dari_form(),
                          tx("catatan", "Catatan", False), baris(), g.user["username"], request.form.get("eta") or None)
        catat("buat PO id=%s" % pid)
        db().commit()
        flash("Purchase order dibuat. Berikutnya: gudang menerima barang.", "ok")
        return kembali("po_detail", pid=pid)
    return render_template("doc_form", active="po", tipe="po", parties=q("SELECT * FROM supplier WHERE aktif=1 ORDER BY nama"),
                           items=q("SELECT * FROM item WHERE aktif=1 AND jenis!='Barang Jadi' ORDER BY nama"))


@app.route("/po/<int:pid>")
def po_detail(pid):
    h = q("SELECT h.*, s.nama, s.npwp, s.alamat, s.kota FROM po_h h JOIN supplier s ON s.id=h.supplier_id WHERE h.id=?", (pid,), one=True) or abort(404)
    ls = q("SELECT d.*, i.nama, i.satuan FROM po_d d JOIN item i ON i.id=d.item_id WHERE d.po_id=?", (pid,))
    dpp = sum(l["qty"] * l["harga"] for l in ls)
    grs = q("SELECT g.*, a.kode ap_kode, a.total, a.jatuh_tempo, a.status ap_status FROM gr_h g LEFT JOIN ap a ON a.gr_id=g.id WHERE g.po_id=? ORDER BY g.id", (pid,))
    grl = q("SELECT d.*, i.nama, i.satuan, l.kode lot_kode FROM gr_d d JOIN item i ON i.id=d.item_id LEFT JOIN lot l ON l.id=d.lot_id WHERE d.gr_id IN (SELECT id FROM gr_h WHERE po_id=?)", (pid,))
    return render_template("po_detail", active="po", h=h, ls=ls, dpp=dpp, ppn=round(dpp * h["ppn_persen"] / 100), grs=grs, grl=grl)


@app.route("/po/<int:pid>/terima", methods=["GET", "POST"])
@roles("gudang")
def gr_baru(pid):
    h = q("SELECT h.*, s.nama FROM po_h h JOIN supplier s ON s.id=h.supplier_id WHERE h.id=?", (pid,), one=True) or abort(404)
    if request.method == "POST":
        lines = {}
        for k in request.form:
            if k.startswith("q_"):
                i = int(k[2:])
                lines[i] = (fl(k, "Jumlah diterima", 0, False), fl("t_%d" % i, "Jumlah ditolak", 0, False), request.form.get("qc_%d" % i, "Lulus"))
        lok = request.form.get("lokasi_id")
        gid = erp.terima_barang(db(), pid, tgl_form(), tx("sj", "No. surat jalan supplier", maks=40), int(lok) if lok and lok.isdigit() else None, lines)
        catat("terima barang PO %s" % h["kode"])
        db().commit()
        flash("Barang diterima: lot & label barcode dibuat, stok bertambah, dan tagihan supplier (hutang) otomatis dibuat.", "ok")
        return kembali("po_detail", pid=pid)
    return render_template("gr_form", active="po", h=h, lokasi=q("SELECT * FROM lokasi WHERE aktif=1 ORDER BY kode"),
                           ls=q("SELECT d.*, i.nama, i.satuan, i.masa_simpan_hari, k.nama kem FROM po_d d JOIN item i ON i.id=d.item_id LEFT JOIN kemasan k ON k.id=i.kemasan_id WHERE d.po_id=? AND d.qty>d.qty_diterima", (pid,)))


@app.route("/hutang")
def hutang():
    return render_template("hutang", active="hutang", rows=q("SELECT a.*, s.nama, p.kode po_kode FROM ap a JOIN supplier s ON s.id=a.supplier_id JOIN po_h p ON p.id=a.po_id ORDER BY a.id DESC"))


# ------------------------------------------------------------- BOM & produksi
@app.route("/bom", methods=["GET", "POST"])
def bom():
    if request.method == "POST":
        if not can("produksi"):
            abort(403)
        ls = []
        for a, it, qt in zip(request.form.getlist("arah"), request.form.getlist("item_id"), request.form.getlist("qty")):
            if qt.strip():
                if a not in ("Masuk", "Keluar") or float(qt) <= 0:
                    raise Gagal("Baris BOM tidak valid.")
                ls.append((a, int(it), float(qt)))
        erp.buat_bom(db(), tx("kode", "Kode BOM", maks=20), tx("nama", "Nama BOM"), ls)
        catat("buat BOM")
        db().commit()
        flash("BOM disimpan.", "ok")
        return kembali("bom")
    boms = q("SELECT * FROM bom_h ORDER BY id")
    det = {b["id"]: q("SELECT d.*, i.nama, i.satuan, i.harga_std FROM bom_d d JOIN item i ON i.id=d.item_id WHERE bom_id=? ORDER BY arah, d.id", (b["id"],)) for b in boms}
    return render_template("bom", active="bom", boms=boms, det=det, items=q("SELECT * FROM item WHERE aktif=1 ORDER BY jenis, nama"))


@app.route("/wo")
def wo_list():
    return render_template("wo_list", active="wo", rows=q("SELECT w.*, b.nama bom FROM wo_h w JOIN bom_h b ON b.id=w.bom_id ORDER BY w.id DESC"),
                           boms=q("SELECT * FROM bom_h WHERE aktif=1"))


@app.route("/wo/baru", methods=["POST"])
@roles("produksi")
def wo_baru():
    wid = erp.buat_wo(db(), tgl_form(), ident("bom_id", "BOM"), fl("batch", "Jumlah batch", 0.001), tx("catatan", "Catatan", False))
    catat("buat WO id=%s" % wid)
    db().commit()
    flash("Perintah produksi dibuat. Kebutuhan bahan dihitung dari BOM x jumlah batch.", "ok")
    return kembali("wo_detail", wid=wid)


@app.route("/wo/<int:wid>")
def wo_detail(wid):
    w = q("SELECT w.*, b.nama bom, b.kode bom_kode FROM wo_h w JOIN bom_h b ON b.id=w.bom_id WHERE w.id=?", (wid,), one=True) or abort(404)
    ls = q("SELECT d.*, i.nama, i.satuan, i.stok, i.harga_std FROM wo_d d JOIN item i ON i.id=d.item_id WHERE wo_id=? ORDER BY arah, d.id", (wid,))
    masuk = [l for l in ls if l["arah"] == "Masuk"]
    keluar = [l for l in ls if l["arah"] == "Keluar"]
    bahan_utama = next((l for l in masuk if scalar("SELECT 1 FROM item WHERE id=? AND jenis='Bahan Baku'", (l["item_id"],))), None)
    lot_pakai = q("""SELECT g.qty, g.nilai, l.id, l.kode, i.nama, i.satuan FROM lot_gerak g JOIN lot l ON l.id=g.lot_id JOIN item i ON i.id=l.item_id
        WHERE g.ref=? AND g.tipe='Produksi' ORDER BY g.id""", (w["kode"],))
    lot_hasil = q("SELECT l.*, i.nama, i.satuan FROM lot l JOIN item i ON i.id=l.item_id WHERE l.ref=? AND l.asal='Produksi'", (w["kode"],))
    return render_template("wo_detail", active="wo", w=w, masuk=masuk, keluar=keluar, bahan_utama=bahan_utama, lot_pakai=lot_pakai, lot_hasil=lot_hasil,
                           biaya_bahan=sum(l["nilai"] for l in masuk), cukup=all(l["stok"] >= l["qty_rencana"] for l in masuk))


@app.route("/wo/<int:wid>/mulai", methods=["POST"])
@roles("produksi")
def wo_mulai(wid):
    erp.mulai_wo(db(), wid, tgl_form())
    catat("mulai WO %s" % wid)
    db().commit()
    flash("Produksi dimulai: bahan dikeluarkan dari gudang dengan nilai rata-rata stok.", "ok")
    return kembali("wo_detail", wid=wid)


@app.route("/wo/<int:wid>/selesai", methods=["POST"])
@roles("produksi")
def wo_selesai(wid):
    erp.selesai_wo(db(), wid, tgl_form(), qtys("h_"), fl("biaya_lain", "Biaya lain", 0, False))
    catat("selesai WO %s" % wid)
    db().commit()
    flash("Produksi selesai: hasil masuk persediaan dengan biaya yang dialokasikan.", "ok")
    return kembali("wo_detail", wid=wid)


# ------------------------------------------------------------------- upah
@app.route("/payroll", methods=["GET", "POST"])
def payroll_list():
    if request.method == "POST":
        if not can("keuangan"):
            abort(403)
        tipe = request.form.get("tipe")
        if tipe not in ("Harian", "Mingguan", "Bulanan"):
            raise Gagal("Pilih jenis upah.")
        pid = erp.buat_payroll(db(), tipe, tgl_form("awal"), tgl_form("akhir"))
        catat("buat payroll id=%s" % pid)
        db().commit()
        return kembali("payroll_detail", pid=pid)
    return render_template("payroll_list", active="payroll", rows=q("SELECT * FROM payroll_h ORDER BY id DESC"))


@app.route("/payroll/<int:pid>")
def payroll_detail(pid):
    p = q("SELECT * FROM payroll_h WHERE id=?", (pid,), one=True) or abort(404)
    return render_template("payroll_detail", active="payroll", p=p,
                           rows=q("SELECT d.*, k.nama, k.bagian FROM payroll_d d JOIN karyawan k ON k.id=d.karyawan_id WHERE payroll_id=?", (pid,)),
                           wos=q("""SELECT w.kode, d.qty_aktual 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')""",
                                 (p["tgl_awal"], p["tgl_akhir"])))


@app.route("/payroll/<int:pid>/simpan", methods=["POST"])
@roles("keuangan")
def payroll_simpan(pid):
    p = q("SELECT * FROM payroll_h WHERE id=?", (pid,), one=True) or abort(404)
    if p["status"] != "Draft":
        raise Gagal("Penggajian yang sudah dibayar tidak bisa diubah.")
    for k, v in qtys("hadir_").items():
        if v < 0:
            raise Gagal("Jumlah hari/minggu/bulan tidak boleh negatif.")
        db().execute("UPDATE payroll_d SET hadir=? WHERE id=? AND payroll_id=?", (v, k, pid))
    erp.hitung_payroll(db(), pid)
    if request.form.get("final"):
        erp.final_payroll(db(), pid, tgl_form())
        flash("Upah dibayar dan dicatat sebagai beban Gaji & Upah.", "ok")
    else:
        flash("Kehadiran disimpan dan upah dihitung ulang.", "ok")
    catat("payroll %s" % pid)
    db().commit()
    return kembali("payroll_detail", pid=pid)


# ---------------------------------------------------------------- keuangan
def aging(tabel):
    out = {"Belum jatuh tempo": 0, "1-30 hari": 0, "31-60 hari": 0, "> 60 hari": 0}
    t = date.today()
    for r in q("SELECT total-dibayar sisa, jatuh_tempo FROM %s WHERE total-dibayar>0.5" % tabel):
        telat = (t - date.fromisoformat(r["jatuh_tempo"])).days
        k = "Belum jatuh tempo" if telat <= 0 else "1-30 hari" if telat <= 30 else "31-60 hari" if telat <= 60 else "> 60 hari"
        out[k] += r["sisa"]
    return out


@app.route("/keuangan", methods=["GET", "POST"])
def keuangan():
    if request.method == "POST":
        if not can("keuangan"):
            abort(403)
        kat = request.form.get("kategori")
        if kat not in KAT_BEBAN:
            raise Gagal("Pilih kategori beban.")
        erp.catat_beban(db(), tgl_form(), kat, fl("jumlah", "Jumlah", 0.01), tx("ket", "Keterangan", False))
        catat("beban manual %s" % kat)
        db().commit()
        flash("Beban dicatat.", "ok")
        return kembali("keuangan")
    lr = erp.lr_bulanan(db())
    tot = {k: sum(m[k] for m in lr) for k in ("pen", "hpp", "beban", "laba")}
    return render_template("keuangan", active="keuangan", lr=lr, tot=tot, ar=aging("inv_h"), apg=aging("ap"),
                           persediaan=scalar("SELECT SUM(nilai) FROM item"), KAT=KAT_BEBAN,
                           beban=q("SELECT * FROM beban ORDER BY tanggal DESC, id DESC LIMIT 40"))


@app.route("/beban/<int:bid>/hapus", methods=["POST"])
@roles("keuangan")
def beban_hapus(bid):
    db().execute("DELETE FROM beban WHERE id=? AND sumber='manual'", (bid,))
    db().commit()
    return kembali("keuangan")


# ------------------------------------------------------- lot, barcode, SCM
@app.route("/lot")
def lot_list():
    st, semua = request.args.get("status", ""), request.args.get("semua")
    sql = """SELECT l.*, i.nama item, i.satuan, i.metode, k.kode lok, kk.nama kem FROM lot l JOIN item i ON i.id=l.item_id
        LEFT JOIN lokasi k ON k.id=l.lokasi_id LEFT JOIN kemasan kk ON kk.id=l.kemasan_id WHERE 1=1"""
    args = []
    if st:
        sql += " AND l.status=?"
        args.append(st)
    elif not semua:
        sql += " AND l.qty_sisa>0.000001"
    return render_template("lot_list", active="lot", st=st, semua=semua, rows=q(sql + " ORDER BY l.item_id, l.tanggal, l.id", args),
                           today=date.today().isoformat())


@app.route("/lot/<int:lid>")
def lot_detail(lid):
    l = q("""SELECT l.*, i.nama item, i.satuan, i.metode, i.jenis, k.kode lok, k.nama lok_nama, kk.nama kem, kk.isi, s.nama sup FROM lot l
        JOIN item i ON i.id=l.item_id LEFT JOIN lokasi k ON k.id=l.lokasi_id LEFT JOIN kemasan kk ON kk.id=l.kemasan_id
        LEFT JOIN supplier s ON s.id=l.supplier_id WHERE l.id=?""", (lid,), one=True) or abort(404)
    trace = q("""SELECT g.*, c.nama pelanggan FROM lot_gerak g LEFT JOIN do_h d ON d.kode=g.ref LEFT JOIN so_h s ON s.id=d.so_id
        LEFT JOIN customer c ON c.id=s.customer_id WHERE g.lot_id=? ORDER BY g.id""", (lid,))
    turunan = []
    for wo in sorted({t["ref"] for t in trace if t["tipe"] == "Produksi"}):
        turunan += q("SELECT l2.*, i.nama, i.satuan FROM lot l2 JOIN item i ON i.id=l2.item_id WHERE l2.ref=? AND l2.asal='Produksi'", (wo,))
    return render_template("lot_detail", active="lot", l=l, trace=trace, turunan=turunan, labels=q("SELECT * FROM label WHERE lot_id=? ORDER BY id", (lid,)),
                           lokasi=q("SELECT * FROM lokasi WHERE aktif=1 ORDER BY kode"))


@app.route("/lot/<int:lid>/aksi", methods=["POST"])
@roles("gudang")
def lot_aksi(lid):
    a = request.form.get("aksi")
    if a == "lulus":
        erp.lepas_karantina(db(), lid, tgl_form())
        flash("Lot dinyatakan lulus QC dan siap dipakai.", "ok")
    elif a == "afkir":
        erp.afkir_lot(db(), lid, tgl_form())
        flash("Lot diafkir: nilai persediaan dikeluarkan dan dicatat sebagai beban selisih/afkir.", "ok")
    elif a == "pindah":
        erp.pindah_lot(db(), lid, ident("lokasi_id", "lokasi tujuan"), tgl_form())
        flash("Lot dipindahkan.", "ok")
    else:
        abort(400, "Aksi tidak dikenal.")
    catat("lot %s %s" % (lid, a))
    db().commit()
    return kembali("lot_detail", lid=lid)


@app.route("/lot/<int:lid>/label")
def lot_label(lid):
    l = q("SELECT l.*, i.nama item, i.satuan, k.kode lok FROM lot l JOIN item i ON i.id=l.item_id LEFT JOIN lokasi k ON k.id=l.lokasi_id WHERE l.id=?", (lid,), one=True) or abort(404)
    return render_template("lot_label", active="lot", l=l, labels=q("SELECT * FROM label WHERE lot_id=? ORDER BY id", (lid,)))


@app.route("/scan")
def scan():
    kode = (request.args.get("kode") or "").strip().upper()
    if kode:
        lb = q("SELECT * FROM label WHERE kode=?", (kode,), one=True)
        lot = q("SELECT * FROM lot WHERE kode=?", (kode.rsplit("-", 1)[0] if lb else kode,), one=True)
        if lot:
            l = q("""SELECT l.*, i.nama item, i.satuan, k.kode lok FROM lot l JOIN item i ON i.id=l.item_id
                LEFT JOIN lokasi k ON k.id=l.lokasi_id WHERE l.id=?""", (lot["id"],), one=True)
            return render_template("scan", active="scan", kode=kode, label=lb, l=l)
        flash("Kode %s tidak ditemukan." % kode, "err")
    return render_template("scan", active="scan", kode=kode, label=None, l=None)


@app.route("/inbound")
def inbound_view():
    rows = erp.inbound(db())
    per = {}
    for r in rows:
        per.setdefault(r["eta"], []).append(r)
    t = date.today().isoformat()
    mx = max([len(v) for v in per.values()] + [1])
    ch = [dict(label=tg(e)[:5], val=len(v), max=mx, text="%d baris" % len(v), color="var(--red)" if e < t else "var(--leaf)") for e, v in sorted(per.items())]
    return render_template("inbound", active="inbound", rows=rows, per=per, ch=ch, kinerja=erp.kinerja_supplier(db()))


WARNA = ["#2f9e6b", "#f0a81c", "#3d6fb6", "#c8453b", "#7b4fb3", "#12868b"]


def svg_tren(seri, std, satuan):
    """Grafik garis harga beli per supplier (SVG, tanpa pustaka luar)."""
    pts = [(date.fromisoformat(t["tanggal"]).toordinal(), t["harga"]) for s in seri for t in s["titik"]]
    if not pts:
        return Markup("")
    W, H, ml, mr, mt, mb = 780, 320, 74, 20, 20, 44
    x0, x1 = min(p[0] for p in pts), max(max(p[0] for p in pts), date.today().toordinal())
    ys = [p[1] for p in pts] + ([std] if std else [])
    y0, y1 = min(ys), max(ys)
    pad = (y1 - y0) * 0.15 or y1 * 0.05 or 1
    y0, y1 = y0 - pad, y1 + pad
    X = lambda v: ml + (v - x0) / ((x1 - x0) or 1) * (W - ml - mr)
    Y = lambda v: mt + (1 - (v - y0) / (y1 - y0)) * (H - mt - mb)
    o = ['<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 %d %d" style="width:100%%;height:auto" role="img" aria-label="Tren harga beli">' % (W, H)]
    for i in range(5):
        v = y0 + (y1 - y0) * i / 4
        o.append('<line x1="%d" x2="%d" y1="%.1f" y2="%.1f" stroke="#e3eae5"/><text x="%d" y="%.1f" font-size="11" text-anchor="end" fill="#66766c">%s</text>' % (ml, W - mr, Y(v), Y(v), ml - 8, Y(v) + 4, num(v)))
    for i in range(5):
        d = x0 + (x1 - x0) * i / 4
        lbl = date.fromordinal(int(round(d))).strftime("%d/%m")
        o.append('<text x="%.1f" y="%d" font-size="11" text-anchor="middle" fill="#66766c">%s</text>' % (X(d), H - 18, lbl))
    if std:
        o.append('<line x1="%d" x2="%d" y1="%.1f" y2="%.1f" stroke="#13231b" stroke-dasharray="5 4"/><text x="%d" y="%.1f" font-size="11" text-anchor="end" fill="#13231b">harga acuan</text>' % (ml, W - mr, Y(std), Y(std), W - mr, Y(std) - 5))
    for i, sr in enumerate(seri):
        c = WARNA[i % len(WARNA)]
        p = [(X(date.fromisoformat(t["tanggal"]).toordinal()), Y(t["harga"]), t) for t in sr["titik"]]
        if len(p) > 1:
            o.append('<polyline fill="none" stroke="%s" stroke-width="2.5" points="%s"/>' % (c, " ".join("%.1f,%.1f" % (a, b) for a, b, _ in p)))
        for a, b, t in p:
            o.append('<circle cx="%.1f" cy="%.1f" r="5" fill="%s" stroke="#fff" stroke-width="2"><title>%s - %s: %s per %s (%s)</title></circle>' % (a, b, c, escape(sr["nama"]), tg(t["tanggal"]), rp(t["harga"]), satuan, t["po"]))
    o.append("</svg>")
    return Markup("".join(o))


@app.route("/harga")
def harga():
    items = q("SELECT * FROM item WHERE aktif=1 AND jenis!='Barang Jadi' AND id IN (SELECT item_id FROM po_d) ORDER BY jenis, nama")
    if not items:
        return render_template("harga", active="harga", items=[], it=None, seri=[], svg="", rank=[])
    iid = request.args.get("item", type=int) or items[0]["id"]
    it = q("SELECT * FROM item WHERE id=?", (iid,), one=True) or abort(404)
    seri = erp.tren_harga(db(), iid)
    legend = [dict(s, warna=WARNA[i % len(WARNA)]) for i, s in enumerate(seri)]
    rank = sorted(legend, key=lambda s: s["akhir"])
    mx = max([s["max"] for s in seri] + [1])
    ch = [dict(label=s["kode"], val=s["rata"], max=mx, text="%s (tertimbang)" % rp(s["rata"]), cls="", mark=None, marktext="") for s in legend]
    riwayat = sorted([dict(p, nama=sr["nama"]) for sr in legend for p in sr["titik"]], key=lambda p: (p["tanggal"], p["po"]))
    return render_template("harga", active="harga", items=items, it=it, seri=legend, rank=rank, ch=ch, riwayat=riwayat,
                           svg=svg_tren(seri, it["harga_std"], it["satuan"]))


@app.route("/scm")
def scm():
    t = date.today().isoformat()
    kin = erp.kinerja_supplier(db())
    rn = erp.rencana_material(db())
    hpp90 = scalar("SELECT SUM(d.hpp_total) FROM inv_h i JOIN do_d d ON d.do_id=i.do_id WHERE i.tanggal>=?", ((date.today() - timedelta(days=90)).isoformat(),))
    nilai = scalar("SELECT SUM(nilai) FROM item")
    avg = lambda k: sum(x[k] for x in kin) / len(kin) if kin else 0
    items = q("SELECT * FROM item WHERE aktif=1 AND nilai>0 ORDER BY nilai DESC")
    ch_stok = [hrow(r["item"]["nama"], r["tersedia"], max(r["tersedia"], r["rop"], r["posisi"], 1) * 1.15,
                    "%s / ROP %s %s" % (fq(round(r["tersedia"])), fq(round(r["rop"])), r["item"]["satuan"]),
                    {"Pesan Sekarang": "red", "Waspada": "amber"}.get(r["status"], ""), r["rop"], "ROP") for r in rn]
    ch_sup = [hrow("%s %s" % (k["supplier"]["kode"], nm), k[f], 100, "%d%%" % round(k[f]), pct_cls(k[f])) for k in kin
              for nm, f in (("tepat waktu", "otd"), ("fill rate", "fill"), ("mutu", "mutu"))]
    nmax = max([x["nilai"] for x in items] + [1])
    ch_nilai = [hrow(i["nama"], i["nilai"], nmax, rp(i["nilai"])) for i in items]
    lokasi = erp.util_lokasi(db())
    ch_lok = [hrow(l["kode"] + " " + l["nama"], l["isi"], max(l["kapasitas"], l["isi"], 1), "%s / %s" % (num(l["isi"]), num(l["kapasitas"])),
                   "red" if l["kapasitas"] and l["isi"] > l["kapasitas"] else "amber" if l["kapasitas"] and l["isi"] > 0.85 * l["kapasitas"] else "") for l in lokasi]
    umur = erp.umur_lot(db())
    umx = max(list(umur.values()) + [1])
    ch_umur = [dict(label=k, val=v, max=umx, text=rp(v), color=c) for (k, v), c in zip(umur.items(), ("var(--leaf)", "#8ccf6b", "var(--amber)", "var(--red)"))]
    return render_template("scm", active="scm", ch_stok=ch_stok, ch_sup=ch_sup, ch_nilai=ch_nilai, ch_lok=ch_lok, ch_umur=ch_umur, kin=kin, rn=rn, mrp=erp.mrp_dari_so(db()), otif=erp.otif(db()), umur=erp.umur_lot(db()),
                           items=items, nilai=nilai, inbound=erp.inbound(db()),
                           otd=avg("otd"), fill=avg("fill"), reject=100 - avg("mutu") if kin else 0, lt=avg("lt"),
                           dio=(nilai / (hpp90 / 90) if hpp90 else 0), turun=(hpp90 * 365 / 90 / nilai if nilai else 0),
                           kar=scalar("SELECT COUNT(*) FROM lot WHERE status='Karantina' AND qty_sisa>0"),
                           kad=q("SELECT l.*, i.nama FROM lot l JOIN item i ON i.id=l.item_id WHERE l.kadaluarsa IS NOT NULL AND l.qty_sisa>0.000001 AND l.kadaluarsa<=? ORDER BY l.kadaluarsa", (tambah3(t),)))


def hrow(label, val, mx, text, cls="", mark=None, marktext=""):
    return dict(label=label, val=val, max=mx, text=text, cls=cls, mark=mark, marktext=marktext)


def pct_cls(v, bagus=90, cukup=70):
    return "" if v >= bagus else "amber" if v >= cukup else "red"


def tambah3(t):
    return erp.tambah_hari(t, 3)


@app.route("/persediaan")
def persediaan():
    val = erp.valuasi_metode(db())
    ch = []
    for v in val:
        akh = [v["hasil"][m]["akhir"] for m in ("FIFO", "LIFO", "AVG")]
        if max(akh) - min(akh) > 1:
            ch += [hrow("%s %s" % (v["item"]["kode"], m), v["hasil"][m]["akhir"], max(akh), rp(v["hasil"][m]["akhir"]), "amber" if m == v["item"]["metode"] else "") for m in ("FIFO", "LIFO", "AVG")]
    return render_template("persediaan", active="persediaan", ch=ch, val=val, cogs=erp.cogs_per_item(db()), rekon=erp.rekonsiliasi(db()),
                           total=scalar("SELECT SUM(nilai) FROM item"))


@app.route("/jurnal")
def jurnal_view():
    tipe = request.args.get("tipe", "")
    sql = "SELECT j.*, c.nama akun_nama FROM jurnal j JOIN coa c ON c.kode=j.akun" + (" WHERE j.tipe=?" if tipe else "") + " ORDER BY j.tanggal DESC, j.id DESC LIMIT 200"
    ns = erp.neraca_saldo(db())
    return render_template("jurnal", active="jurnal", rows=q(sql, (tipe,) if tipe else ()), ns=ns, tipe=tipe,
                           tipes=[r[0] for r in db().execute("SELECT DISTINCT tipe FROM jurnal ORDER BY tipe")],
                           td=sum(r["d"] for r in ns), tk=sum(r["k"] for r in ns))


# ---------------------------------------------------------------- pengguna
@app.route("/pengguna", methods=["GET", "POST"])
@roles()
def pengguna():
    if request.method == "POST":
        u, n, r, pw = (request.form.get(x, "").strip() for x in ("username", "nama", "role", "password"))
        if not u.isalnum() or not n or r not in ROLES or len(pw) < 8:
            raise Gagal("Username huruf/angka, nama wajib, peran valid, password minimal 8 karakter.")
        if q("SELECT 1 FROM users WHERE username=?", (u.lower(),), one=True):
            raise Gagal("Username sudah dipakai.")
        db().execute("INSERT INTO users(username,nama,role,pw_hash) VALUES(?,?,?,?)", (u.lower(), n, r, generate_password_hash(pw)))
        catat("tambah user %s" % u)
        db().commit()
        flash("Pengguna ditambahkan.", "ok")
        return kembali("pengguna")
    return render_template("pengguna", active="pengguna", rows=q("SELECT * FROM users ORDER BY id"), log=q("SELECT * FROM log ORDER BY id DESC LIMIT 20"))


@app.route("/pengguna/<int:uid>/<aksi>", methods=["POST"])
@roles()
def pengguna_aksi(uid, aksi):
    u = q("SELECT * FROM users WHERE id=?", (uid,), one=True) or abort(404)
    if aksi == "toggle" and u["id"] != g.user["id"]:
        db().execute("UPDATE users SET aktif=1-aktif WHERE id=?", (uid,))
    elif aksi == "reset":
        if len(request.form.get("password", "")) < 8:
            raise Gagal("Password baru minimal 8 karakter.")
        db().execute("UPDATE users SET pw_hash=? WHERE id=?", (generate_password_hash(request.form["password"]), uid))
        flash("Password %s direset." % u["username"], "ok")
    catat("%s user %s" % (aksi, u["username"]))
    db().commit()
    return kembali("pengguna")


@app.route("/pengaturan", methods=["POST"])
@roles()
def pengaturan():
    for k in ("nama", "npwp", "alamat"):
        db().execute("INSERT OR REPLACE INTO setting(key,value) VALUES(?,?)", ("perusahaan_" + k, tx(k, k, False, 150)))
    db().execute("INSERT OR REPLACE INTO setting(key,value) VALUES('ppn',?)", (str(fl("ppn", "Tarif PPN", 0)),))
    catat("ubah pengaturan")
    db().commit()
    flash("Pengaturan disimpan.", "ok")
    return kembali("pengguna")


# -------------------------------------------------------------- data contoh
def init_db(paksa=False):
    ada = os.path.exists(DB)
    if ada:
        con = sqlite3.connect(DB)
        ver = con.execute("PRAGMA user_version").fetchone()[0]
        con.close()
        if paksa or ver != erp.SCHEMA_VERSION:
            os.replace(DB, DB + ".bak")
    con = sqlite3.connect(DB)
    con.row_factory = sqlite3.Row
    con.executescript(erp.SCHEMA)
    erp.migrasi(con)
    con.execute("PRAGMA user_version=%d" % erp.SCHEMA_VERSION)
    if con.execute("SELECT COUNT(*) FROM users").fetchone()[0] == 0:
        for u, n, r in [("admin", "Administrator", "admin"), ("sales", "Rina Sales", "sales"), ("purchasing", "Hendra Pembelian", "purchasing"),
                        ("gudang", "Dedi Gudang", "gudang"), ("produksi", "Budi Produksi", "produksi"),
                        ("keuangan", "Sari Keuangan", "keuangan"), ("dosen", "Dosen Pengampu", "dosen")]:
            con.execute("INSERT INTO users(username,nama,role,pw_hash) VALUES(?,?,?,?)", (u, n, r, generate_password_hash(u + "#2026")))
        loader.muat_master(con)
        if os.environ.get("TANPA_TRANSAKSI") != "1":
            loader.jalankan_skenario(con)
    con.commit()
    con.close()


from templates_html import TEMPLATES  # noqa: E402
from templates_logistik import T as _T  # noqa: E402

TEMPLATES.update(_T)

app.jinja_loader = DictLoader(TEMPLATES)
app.jinja_env.globals["barcode"] = bc.code39_svg
init_db()

if __name__ == "__main__":
    HOST = os.environ.get("HOST", "0.0.0.0")   # 0.0.0.0 = bisa diakses dari komputer lain; ganti 127.0.0.1 untuk localhost saja
    PORT = int(os.environ.get("PORT", 5011))
    print("SawitFlow ERP berjalan di http://localhost:%d  (komputer lain: http://<IP-komputer>:%d)" % (PORT, PORT))
    try:
        from waitress import serve
        serve(app, host=HOST, port=PORT)
    except ImportError:
        app.run(host=HOST, port=PORT)
