# Skrip 6.1: menggabungkan (join) tabel Inventarisasi.csv ke lapisan Petak (QGIS Python Console)
# Penulis: Badar Mubarok Yogaswara
# Jalankan Skrip 5.1 lebih dulu (menghasilkan Petak_Hitung.gpkg di folder keluaran).
import os
import processing
from qgis.core import QgsVectorLayer, QgsVectorLayerJoinInfo

DATA = os.environ.get("B2_DATA", r"C:/KPH_Contoh/paket-b2")
KELUAR = os.environ.get("B2_KELUAR", r"C:/KPH_Contoh/keluaran")
petak = QgsVectorLayer(KELUAR + "/Petak_Hitung.gpkg|layername=Petak_Hitung", "Petak_Hitung", "ogr")
uri = "file:///" + DATA.replace("\\", "/") + "/Inventarisasi.csv?type=csv&delimiter=,&detectTypes=yes&geomType=none"
inv = QgsVectorLayer(uri, "Inventarisasi", "delimitedtext")
print("Tabel valid:", inv.isValid(), "| baris:", inv.featureCount())
print("Tipe kolom:", [(f.name(), f.typeName()) for f in inv.fields()])

# 1. Join permanen (hasilnya lapisan baru): kunci KODE_PETAK di kedua tabel
g = processing.run("native:joinattributestable", {
    "INPUT": petak, "FIELD": "KODE_PETAK", "INPUT_2": inv, "FIELD_2": "KODE_PETAK",
    "FIELDS_TO_COPY": ["JML_POHON", "VOLUME_M3", "TGL_UKUR"], "METHOD": 1, "DISCARD_NONMATCHING": False, "PREFIX": "inv_",
    "OUTPUT": "memory:", "NON_MATCHING": "memory:"})
hasil = g["OUTPUT"]
cocok = [f["KODE_PETAK"] for f in hasil.getFeatures() if f["inv_JML_POHON"] is not None]
kosong = [f["KODE_PETAK"] for f in hasil.getFeatures() if f["inv_JML_POHON"] is None]
print("Join 1 | petak cocok:", len(cocok), "| petak tanpa pasangan:", kosong)
kode_petak = {f["KODE_PETAK"] for f in petak.getFeatures()}
print("          baris tabel yang tidak punya petak:", sorted(f["KODE_PETAK"] for f in inv.getFeatures() if f["KODE_PETAK"] not in kode_petak))

# 2. Perbaiki kuncinya: huruf kecil menjadi huruf besar. Kolom bantu dibuat di tabel, lalu join diulang.
inv2 = processing.run("native:fieldcalculator", {
    "INPUT": inv, "FIELD_NAME": "KUNCI", "FIELD_TYPE": 2, "FIELD_LENGTH": 10, "FIELD_PRECISION": 0,
    "FORMULA": "upper(trim(\"KODE_PETAK\"))", "OUTPUT": "memory:"})["OUTPUT"]
g2 = processing.run("native:joinattributestable", {
    "INPUT": petak, "FIELD": "KODE_PETAK", "INPUT_2": inv2, "FIELD_2": "KUNCI",
    "FIELDS_TO_COPY": ["JML_POHON", "VOLUME_M3", "TGL_UKUR"], "METHOD": 1, "DISCARD_NONMATCHING": False, "PREFIX": "inv_",
    "OUTPUT": "memory:", "NON_MATCHING": "memory:"})
h2 = g2["OUTPUT"]
cocok2 = [f for f in h2.getFeatures() if f["inv_JML_POHON"] is not None]
kosong2 = [f["KODE_PETAK"] for f in h2.getFeatures() if f["inv_JML_POHON"] is None]
print("Join 2 | petak cocok:", len(cocok2), "| petak tanpa pasangan:", kosong2,
      "| baris tabel tanpa petak:", sorted(f["KUNCI"] for f in inv2.getFeatures() if f["KUNCI"] not in kode_petak))

# 3. Hitung kepadatan pohon dan volume per hektare dari hasil join
h3 = processing.run("native:fieldcalculator", {
    "INPUT": h2, "FIELD_NAME": "POHON_HA", "FIELD_TYPE": 0, "FIELD_LENGTH": 10, "FIELD_PRECISION": 1,
    "FORMULA": "round(\"inv_JML_POHON\" / \"LUAS_HA\", 1)", "OUTPUT": "memory:"})["OUTPUT"]
h3 = processing.run("native:fieldcalculator", {
    "INPUT": h3, "FIELD_NAME": "VOL_HA", "FIELD_TYPE": 0, "FIELD_LENGTH": 10, "FIELD_PRECISION": 1,
    "FORMULA": "round(\"inv_VOLUME_M3\" / \"LUAS_HA\", 1)", "OUTPUT": "memory:"})["OUTPUT"]
for f in h3.getFeatures():
    print(f["KODE_PETAK"], f["LUAS_HA"], f["inv_JML_POHON"], f["POHON_HA"], f["inv_VOLUME_M3"], f["VOL_HA"])
tot_pohon = sum(f["inv_JML_POHON"] for f in cocok2)
tot_vol = round(sum(f["inv_VOLUME_M3"] for f in cocok2), 1)
print("Total pohon (15 petak):", tot_pohon, "| total volume m3:", tot_vol)
tertinggi = max((f for f in h3.getFeatures() if f["VOL_HA"] is not None), key=lambda x: x["VOL_HA"])
print("Volume per ha tertinggi:", tertinggi["KODE_PETAK"], tertinggi["VOL_HA"])

# 4. Join virtual (tidak membuat berkas baru), setara Layer Properties > Joins
info = QgsVectorLayerJoinInfo()
info.setJoinLayer(inv)
info.setJoinFieldName("KODE_PETAK")
info.setTargetFieldName("KODE_PETAK")
info.setPrefix("inv_")
info.setUsingMemoryCache(True)
petak.addJoin(info)
print("Join virtual | kolom setelah join:", [f.name() for f in petak.fields()][-5:])
n = sum(1 for f in petak.getFeatures() if f["inv_JML_POHON"] is not None)
print("Join virtual | petak yang terisi:", n)

# 5. Simpan hasil akhir untuk Bab 7
processing.run("native:savefeatures", {"INPUT": h3, "OUTPUT": KELUAR + "/Petak_Inventarisasi.gpkg", "LAYER_NAME": "Petak_Inventarisasi"})
print("Tersimpan:", os.path.exists(KELUAR + "/Petak_Inventarisasi.gpkg"))