#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""Fish HPP — Aplikasi Pembelian Ikan / 鱼类采购系统
Login + Master Ikan + Master Supplier + Transaksi Pembelian (HPP final per item,
update moving average ke master ikan).
"""
import json
import os
import secrets
import sqlite3
from datetime import datetime
from functools import wraps

from flask import (Flask, g, render_template, request, redirect, url_for,
                   session, flash)
from werkzeug.security import generate_password_hash, check_password_hash

BASE = os.path.dirname(os.path.abspath(__file__))
DB_PATH = os.path.join(BASE, "fish.db")
SECRET_DIR = os.path.join(BASE, "data")

app = Flask(__name__)


# --- Host publik (opsional, untuk deploy di belakang proxy) ---
# Jika FORCE_HOST diset (misal psj.dataonlive.com), semua tautan memakai nama
# domain itu, bukan alamat internal server.
class _ForceHost:
    def __init__(self, wsgi_app, host):
        self.wsgi_app = wsgi_app
        self.host = host

    def __call__(self, environ, start_response):
        environ["HTTP_HOST"] = self.host
        environ["SERVER_NAME"] = self.host.split(":")[0]
        if environ.get("HTTP_X_FORWARDED_PROTO") == "https":
            environ["SERVER_PORT"] = "443"
            environ["wsgi.url_scheme"] = "https"
        else:
            environ["SERVER_PORT"] = "80"
        return self.wsgi_app(environ, start_response)


_FORCE_HOST = os.getenv("FORCE_HOST", "").strip()
if _FORCE_HOST:
    app.wsgi_app = _ForceHost(app.wsgi_app, _FORCE_HOST)

# --- secret key persisted ---
os.makedirs(SECRET_DIR, exist_ok=True)
_sk = os.path.join(SECRET_DIR, "secret_key")
if os.path.exists(_sk):
    with open(_sk, encoding="utf-8") as f:
        app.secret_key = f.read().strip()
else:
    app.secret_key = secrets.token_hex(32)
    with open(_sk, "w", encoding="utf-8") as f:
        f.write(app.secret_key)

# --- Label dua bahasa side by side: Indonesia / 简体中文 ---
L = {
    "app_title": "Pembelian Ikan / 鱼类采购系统",
    "dashboard": "Dashboard / 首页",
    "master_ikan": "Master Ikan / 鱼种管理",
    "master_supplier": "Master Supplier / 供应商管理",
    "pembelian": "Pembelian / 采购",
    "logout": "Keluar / 退出",
    "login_title": "Masuk / 登录",
    "username": "Nama Pengguna / 用户名",
    "password": "Kata Sandi / 密码",
    "btn_login": "Masuk / 登录",
    "welcome": "Selamat Datang / 欢迎",
    "stat_ikan": "Jumlah Ikan / 鱼种数量",
    "stat_supplier": "Jumlah Supplier / 供应商数量",
    "stat_hpp": "Rata-rata HPP / 平均成本价",
    "stat_stok": "Total Stok / 总库存",
    "stat_transaksi": "Total Transaksi / 交易总数",
    "table_ikan": "Daftar Ikan / 鱼种列表",
    "table_supplier": "Daftar Supplier / 供应商列表",
    "col_kode": "Kode / 代码",
    "col_nama": "Nama / 名称",
    "col_satuan": "Satuan / 单位",
    "col_stok": "Stok / 库存",
    "col_hpp": "HPP / 成本价",
    "col_aksi": "Aksi / 操作",
    "col_id": "ID",
    "col_nama_pt": "Nama PT / 公司名称",
    "col_kontak": "Kontak Person / 联系人",
    "col_telp": "Telp / 电话",
    "col_alamat": "Alamat / 地址",
    "add_ikan": "Tambah Ikan / 添加鱼种",
    "edit_ikan": "Edit Ikan / 编辑鱼种",
    "add_supplier": "Tambah Supplier / 添加供应商",
    "edit_supplier": "Edit Supplier / 编辑供应商",
    "btn_save": "Simpan / 保存",
    "btn_update": "Perbarui / 更新",
    "btn_cancel": "Batal / 取消",
    "btn_edit": "Edit / 编辑",
    "btn_delete": "Hapus / 删除",
    "kode_auto": "Kode otomatis / 自动编号",
    "next_code": "Kode berikutnya / 下一个编号",
    "hpp_label": "Harga HPP / 成本价",
    "hpp_hint": "HPP diperbarui otomatis dari transaksi pembelian (rata-rata bergerak) / 成本价由采购交易自动更新（移动平均）",
    "empty_ikan": "Belum ada data ikan. Tambahkan lewat form di atas / 暂无鱼种数据，请使用上方表单添加",
    "empty_supplier": "Belum ada data supplier. Tambahkan lewat form di atas / 暂无供应商数据，请使用上方表单添加",
    "satuan_label": "Satuan / 单位",
    "delete_confirm": "Yakin hapus data ini? / 确定要删除这条数据吗？",
    # --- Pembelian ---
    "list_pembelian": "Daftar Transaksi Pembelian / 采购交易列表",
    "btn_baru": "Transaksi Baru / 新建采购",
    "col_no_invoice": "No Invoice / 发票号",
    "col_supplier": "Supplier / 供应商",
    "col_tgl": "Tgl Beli / 采购日期",
    "col_gross": "Gross Amount / 采购总额",
    "col_selisih": "Selisih Packing / 包装差异",
    "btn_view": "Lihat / 查看",
    "empty_pembelian": "Belum ada transaksi. Klik 'Transaksi Baru' untuk mulai / 暂无交易，请点击“新建采购”开始",
    "header_transaksi": "Header Transaksi / 交易信息",
    "detail_item": "Detail Barang / 商品明细",
    "col_no": "No / 序号",
    "col_ikan": "Nama Ikan / 鱼名",
    "col_berat": "Berat (kg) / 重量(公斤)",
    "col_harga": "Harga Beli / 采购价",
    "col_jumlah": "Jumlah Harga Beli / 采购金额",
    "col_jpen": "Jumlah Penyesuaian / 调整后金额",
    "col_hpp_final": "HPP Final / 最终成本价",
    "add_row": "+ Tambah Baris / 添加行",
    "total_kg": "Total Kg / 总重量",
    "harga_nota": "Harga Nota sblm Pot PPH / 扣PPH前发票金额",
    "btn_hitung": "Hitung / 计算",
    "penyesuaian_per_kg": "Penyesuaian Per Kg / 每公斤调整",
    "btn_simpan_trans": "Simpan Transaksi / 保存交易",
    "nota_hint": "Isi harga nota, lalu klik Hitung untuk melihat HPP Final per item / 填写发票金额后点击“计算”查看每项最终成本价",
    "tgl_hint": "Format: YYYY-MM-DD / 格式：YYYY-MM-DD",
    "invoice_preview": "No Invoice (otomatis) / 发票号（自动）",
    "total_hpp": "Total HPP / 最终成本总额",
    "detail_transaksi": "Detail Transaksi / 交易详情",
    "kembali": "← Kembali / 返回",
    "confirm_hapus_trans": "Yakin hapus transaksi ini? Stok akan dikembalikan / 确定删除此交易？库存将被退回",
    "pilih_supplier": "-- Pilih Supplier / 选择供应商 --",
    "pilih_ikan": "-- Pilih Ikan / 选择鱼种 --",
    "search_ikan": "Cari Ikan (ketik kode / nama) / 搜索鱼种（输入代码或名称）",
    "btn_pilih": "Pilih / 选择",
}

# --- Database ---
def get_db():
    if "db" not in g:
        g.db = sqlite3.connect(DB_PATH)
        g.db.row_factory = sqlite3.Row
    return g.db


@app.teardown_appcontext
def close_db(exc):
    db = g.pop("db", None)
    if db is not None:
        db.close()


def init_db():
    db = sqlite3.connect(DB_PATH)
    db.row_factory = sqlite3.Row
    db.executescript("""
    CREATE TABLE IF NOT EXISTS users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT UNIQUE NOT NULL,
        password TEXT NOT NULL,
        nama TEXT
    );
    CREATE TABLE IF NOT EXISTS ikan (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        kode TEXT UNIQUE NOT NULL,
        nama TEXT NOT NULL,
        satuan TEXT NOT NULL DEFAULT 'Kg',
        hpp REAL NOT NULL DEFAULT 0,
        stok REAL NOT NULL DEFAULT 0,
        created_at TEXT
    );
    CREATE TABLE IF NOT EXISTS supplier (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nama_pt TEXT NOT NULL,
        kontak TEXT,
        telp TEXT,
        alamat TEXT,
        created_at TEXT
    );
    CREATE TABLE IF NOT EXISTS pembelian (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        no_invoice TEXT UNIQUE NOT NULL,
        supplier_id INTEGER NOT NULL REFERENCES supplier(id),
        tgl_beli TEXT NOT NULL,
        total_kg REAL NOT NULL DEFAULT 0,
        gross_amount REAL NOT NULL DEFAULT 0,
        harga_nota REAL NOT NULL DEFAULT 0,
        selisih_packing REAL NOT NULL DEFAULT 0,
        penyesuaian_per_kg REAL NOT NULL DEFAULT 0,
        created_at TEXT
    );
    CREATE TABLE IF NOT EXISTS pembelian_item (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        pembelian_id INTEGER NOT NULL REFERENCES pembelian(id) ON DELETE CASCADE,
        ikan_id INTEGER NOT NULL REFERENCES ikan(id),
        qty REAL NOT NULL,
        harga REAL NOT NULL,
        jumlah REAL NOT NULL,
        jumlah_penyesuaian REAL NOT NULL DEFAULT 0,
        hpp_final REAL NOT NULL DEFAULT 0
    );
    """)
    # migrasi: tambah kolom stok bila belum ada (DB lama)
    cols = [r["name"] for r in db.execute("PRAGMA table_info(ikan)").fetchall()]
    if "stok" not in cols:
        db.execute("ALTER TABLE ikan ADD COLUMN stok REAL NOT NULL DEFAULT 0")
    cur = db.execute("SELECT COUNT(*) AS c FROM users WHERE username='admin'")
    if cur.fetchone()["c"] == 0:
        db.execute(
            "INSERT INTO users (username, password, nama) VALUES (?,?,?)",
            ("admin",
             generate_password_hash("admin123", method="pbkdf2:sha256:600000"),
             "Administrator"))
    db.commit()
    db.close()


def next_kode_ikan():
    row = get_db().execute("SELECT MAX(CAST(kode AS INTEGER)) AS m FROM ikan").fetchone()
    return str((row["m"] or 1000) + 1)


def next_invoice():
    row = get_db().execute(
        "SELECT no_invoice FROM pembelian ORDER BY id DESC LIMIT 1").fetchone()
    if row:
        try:
            n = int(row["no_invoice"].split("/")[1]) + 1
        except (IndexError, ValueError):
            n = 1
    else:
        n = 1
    return f"PI-{datetime.now().strftime('%y%m')}/{n:05d}"


def login_required(f):
    @wraps(f)
    def wrapper(*args, **kwargs):
        if not session.get("uid"):
            return redirect(url_for("login"))
        return f(*args, **kwargs)
    return wrapper


# --- Auth ---
@app.route("/login", methods=["GET", "POST"])
def login():
    if request.method == "POST":
        u = request.form.get("username", "").strip()
        p = request.form.get("password", "")
        row = get_db().execute(
            "SELECT * FROM users WHERE username=?", (u,)).fetchone()
        if row and check_password_hash(row["password"], p):
            session.clear()
            session["uid"] = row["id"]
            session["username"] = row["username"]
            session["nama"] = row["nama"] or row["username"]
            return redirect(url_for("dashboard"))
        flash("Username atau password salah / 用户名或密码错误", "error")
    return render_template("login.html", L=L)


@app.route("/logout")
def logout():
    session.clear()
    return redirect(url_for("login"))


# --- Dashboard ---
@app.route("/")
@login_required
def dashboard():
    db = get_db()
    n_ikan = db.execute("SELECT COUNT(*) AS c FROM ikan").fetchone()["c"]
    n_sup = db.execute("SELECT COUNT(*) AS c FROM supplier").fetchone()["c"]
    n_trans = db.execute("SELECT COUNT(*) AS c FROM pembelian").fetchone()["c"]
    tot_stok = db.execute("SELECT ROUND(SUM(stok),2) AS s FROM ikan").fetchone()["s"] or 0
    avg_hpp = db.execute(
        "SELECT ROUND(AVG(hpp),2) AS a FROM ikan WHERE hpp > 0").fetchone()["a"] or 0
    rows = db.execute("SELECT * FROM ikan ORDER BY CAST(kode AS INTEGER)").fetchall()
    sups = db.execute("SELECT * FROM supplier ORDER BY id").fetchall()
    return render_template("dashboard.html", L=L, n_ikan=n_ikan, n_sup=n_sup,
                           n_trans=n_trans, tot_stok=tot_stok, avg_hpp=avg_hpp,
                           rows=rows, sups=sups)


# --- Master Ikan ---
@app.route("/ikan")
@login_required
def ikan():
    db = get_db()
    rows = db.execute("SELECT * FROM ikan ORDER BY CAST(kode AS INTEGER)").fetchall()
    return render_template("ikan.html", L=L, rows=rows, next_kode=next_kode_ikan())


@app.route("/ikan/save", methods=["POST"])
@login_required
def ikan_save():
    fid = request.form.get("id", "").strip()
    nama = request.form.get("nama", "").strip()
    satuan = request.form.get("satuan", "").strip() or "Kg"
    hpp = request.form.get("hpp", "0").strip() or "0"
    if not nama:
        flash("Nama ikan wajib diisi / 请填写鱼名", "error")
        return redirect(url_for("ikan"))
    try:
        hpp = float(hpp)
        if hpp < 0:
            raise ValueError
    except ValueError:
        flash("HPP harus angka >= 0 / 成本价必须为不小于0的数字", "error")
        return redirect(url_for("ikan"))
    db = get_db()
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    if fid:
        db.execute("UPDATE ikan SET nama=?, satuan=?, hpp=? WHERE id=?",
                   (nama, satuan, hpp, fid))
        flash("Data ikan diperbarui / 鱼种已更新", "ok")
    else:
        kode = next_kode_ikan()
        db.execute("INSERT INTO ikan (kode, nama, satuan, hpp, stok, created_at) "
                   "VALUES (?,?,?,?,0,?)", (kode, nama, satuan, hpp, now))
        flash(f"Ikan '{nama}' ditambahkan dengan kode {kode} / 已添加鱼种（代码 {kode}）", "ok")
    db.commit()
    return redirect(url_for("ikan"))


@app.route("/ikan/<int:iid>/delete", methods=["POST"])
@login_required
def ikan_delete(iid):
    db = get_db()
    db.execute("DELETE FROM ikan WHERE id=?", (iid,))
    db.commit()
    flash("Ikan dihapus / 鱼种已删除", "ok")
    return redirect(url_for("ikan"))


# --- Master Supplier ---
@app.route("/supplier")
@login_required
def supplier():
    db = get_db()
    rows = db.execute("SELECT * FROM supplier ORDER BY id").fetchall()
    return render_template("supplier.html", L=L, rows=rows)


@app.route("/supplier/save", methods=["POST"])
@login_required
def supplier_save():
    sid = request.form.get("id", "").strip()
    nama_pt = request.form.get("nama_pt", "").strip()
    kontak = request.form.get("kontak", "").strip()
    telp = request.form.get("telp", "").strip()
    alamat = request.form.get("alamat", "").strip()
    if not nama_pt:
        flash("Nama PT wajib diisi / 请填写公司名称", "error")
        return redirect(url_for("supplier"))
    db = get_db()
    now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    if sid:
        db.execute("UPDATE supplier SET nama_pt=?, kontak=?, telp=?, alamat=? WHERE id=?",
                   (nama_pt, kontak, telp, alamat, sid))
        flash("Data supplier diperbarui / 供应商已更新", "ok")
    else:
        cur = db.execute("INSERT INTO supplier (nama_pt, kontak, telp, alamat, created_at) "
                         "VALUES (?,?,?,?,?)", (nama_pt, kontak, telp, alamat, now))
        flash(f"Supplier '{nama_pt}' ditambahkan dengan ID {cur.lastrowid} / 已添加供应商（ID {cur.lastrowid}）", "ok")
    db.commit()
    return redirect(url_for("supplier"))


@app.route("/supplier/<int:sid>/delete", methods=["POST"])
@login_required
def supplier_delete(sid):
    db = get_db()
    db.execute("DELETE FROM supplier WHERE id=?", (sid,))
    db.commit()
    flash("Supplier dihapus / 供应商已删除", "ok")
    return redirect(url_for("supplier"))


# --- Transaksi Pembelian ---
@app.route("/pembelian")
@login_required
def pembelian():
    db = get_db()
    rows = db.execute(
        "SELECT p.*, s.nama_pt FROM pembelian p "
        "JOIN supplier s ON s.id = p.supplier_id ORDER BY p.id DESC").fetchall()
    return render_template("pembelian.html", L=L, rows=rows)


@app.route("/pembelian/baru", methods=["GET", "POST"])
@login_required
def pembelian_baru():
    db = get_db()
    suppliers = db.execute("SELECT * FROM supplier ORDER BY id").fetchall()
    ikans = db.execute("SELECT * FROM ikan ORDER BY CAST(kode AS INTEGER)").fetchall()
    ikan_json = json.dumps(
        [{"id": r["id"], "kode": r["kode"], "nama": r["nama"], "satuan": r["satuan"]}
         for r in ikans],
        ensure_ascii=False)

    if request.method == "POST":
        supplier_id = request.form.get("supplier_id", "").strip()
        tgl = request.form.get("tgl", "").strip()
        harga_nota = request.form.get("harga_nota", "").strip() or "0"
        ikan_ids = request.form.getlist("ikan_id[]")
        qtys = request.form.getlist("qty[]")
        hargas = request.form.getlist("harga[]")

        if not supplier_id or not db.execute(
                "SELECT id FROM supplier WHERE id=?", (supplier_id,)).fetchone():
            flash("Pilih supplier dulu / 请先选择供应商", "error")
            return redirect(url_for("pembelian_baru"))
        if not tgl:
            flash("Tanggal beli wajib diisi / 请填写采购日期", "error")
            return redirect(url_for("pembelian_baru"))
        try:
            harga_nota = float(harga_nota)
            if harga_nota < 0:
                raise ValueError
        except ValueError:
            flash("Harga nota harus angka >= 0 / 发票金额必须为不小于0的数字", "error")
            return redirect(url_for("pembelian_baru"))

        items = []
        err = False
        for i in range(len(ikan_ids)):
            iid = ikan_ids[i].strip()
            if not iid:
                continue
            try:
                qty = float(qtys[i] or 0)
                harga = float(hargas[i] or 0)
            except ValueError:
                flash("Qty / harga harus angka / 数量或价格必须是数字", "error")
                err = True
                break
            if qty <= 0 or harga < 0:
                flash("Qty harus > 0 dan harga >= 0 / 数量必须大于0，价格不小于0", "error")
                err = True
                break
            items.append({"ikan_id": int(iid), "qty": qty, "harga": harga,
                          "jumlah": qty * harga})
        if err:
            return redirect(url_for("pembelian_baru"))
        if not items:
            flash("Minimal 1 item barang / 至少需要1个商品", "error")
            return redirect(url_for("pembelian_baru"))

        # --- hitung (rumus sesuai sheet) ---
        total_kg = sum(it["qty"] for it in items)
        gross = sum(it["jumlah"] for it in items)
        selisih = harga_nota - gross
        ppkg = round(selisih / total_kg) if total_kg else 0
        for it in items:
            it["hpp_final"] = round(it["harga"] + ppkg)
            it["jumlah_penyesuaian"] = round(it["hpp_final"] * it["qty"])

        now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        no_inv = next_invoice()
        cur = db.execute(
            "INSERT INTO pembelian (no_invoice, supplier_id, tgl_beli, total_kg, "
            "gross_amount, harga_nota, selisih_packing, penyesuaian_per_kg, created_at) "
            "VALUES (?,?,?,?,?,?,?,?,?)",
            (no_inv, supplier_id, tgl, round(total_kg, 2), round(gross, 2),
             round(harga_nota, 2), round(selisih, 2), ppkg, now))
        pid = cur.lastrowid
        for it in items:
            db.execute(
                "INSERT INTO pembelian_item (pembelian_id, ikan_id, qty, harga, "
                "jumlah, jumlah_penyesuaian, hpp_final) VALUES (?,?,?,?,?,?,?)",
                (pid, it["ikan_id"], it["qty"], it["harga"], round(it["jumlah"], 2),
                 it["jumlah_penyesuaian"], it["hpp_final"]))
            # update master ikan: stok + HPP rata-rata bergerak (moving average)
            ik = db.execute("SELECT * FROM ikan WHERE id=?", (it["ikan_id"],)).fetchone()
            stok_lama = ik["stok"] or 0
            stok_baru = stok_lama + it["qty"]
            if stok_baru:
                hpp_baru = (stok_lama * (ik["hpp"] or 0) + it["qty"] * it["hpp_final"]) / stok_baru
            else:
                hpp_baru = it["hpp_final"]
            db.execute("UPDATE ikan SET stok=?, hpp=? WHERE id=?",
                       (round(stok_baru, 2), round(hpp_baru, 2), it["ikan_id"]))
        db.commit()
        flash(f"Transaksi {no_inv} tersimpan, HPP ikan diperbarui / 交易 {no_inv} 已保存，鱼种成本价已更新", "ok")
        return redirect(url_for("pembelian_view", pid=pid))

    return render_template("pembelian_baru.html", L=L, suppliers=suppliers,
                           ikan_json=ikan_json, today=datetime.now().strftime("%Y-%m-%d"),
                           next_inv=next_invoice())


@app.route("/pembelian/<int:pid>")
@login_required
def pembelian_view(pid):
    db = get_db()
    p = db.execute(
        "SELECT p.*, s.nama_pt FROM pembelian p JOIN supplier s ON s.id=p.supplier_id "
        "WHERE p.id=?", (pid,)).fetchone()
    if not p:
        flash("Transaksi tidak ditemukan / 交易未找到", "error")
        return redirect(url_for("pembelian"))
    items = db.execute(
        "SELECT pi.*, ik.nama, ik.satuan FROM pembelian_item pi "
        "JOIN ikan ik ON ik.id = pi.ikan_id WHERE pi.pembelian_id=? "
        "ORDER BY pi.id", (pid,)).fetchall()
    return render_template("pembelian_view.html", L=L, p=p, items=items)


@app.route("/pembelian/<int:pid>/delete", methods=["POST"])
@login_required
def pembelian_delete(pid):
    db = get_db()
    p = db.execute("SELECT * FROM pembelian WHERE id=?", (pid,)).fetchone()
    if not p:
        flash("Transaksi tidak ditemukan / 交易未找到", "error")
        return redirect(url_for("pembelian"))
    # kembalikan stok
    for it in db.execute("SELECT * FROM pembelian_item WHERE pembelian_id=?", (pid,)).fetchall():
        ik = db.execute("SELECT * FROM ikan WHERE id=?", (it["ikan_id"],)).fetchone()
        stok_baru = max(0.0, (ik["stok"] or 0) - it["qty"])
        db.execute("UPDATE ikan SET stok=? WHERE id=?", (stok_baru, it["ikan_id"]))
    db.execute("DELETE FROM pembelian WHERE id=?", (pid,))
    db.commit()
    flash(f"Transaksi {p['no_invoice']} dihapus, stok dikembalikan / 交易已删除，库存已退回", "ok")
    return redirect(url_for("pembelian"))


if __name__ == "__main__":
    init_db()
    app.run(host=os.getenv("HOST", "0.0.0.0"),
            port=int(os.getenv("PORT", "5002")), debug=False)