# -*- coding: utf-8 -*-
"""M2 Bab 2: menjalankan SQL spasial yang sama pada GeoPackage (dialek SQLite/SpatiaLite lewat GDAL),
lalu membandingkan angkanya dengan hasil PostGIS. Penulis: Badar Mubarok Yogaswara.
Pemakaian: python-qgis.bat m2_02c_silang_geopackage.py <folder paket-m2>
Bila PostGIS tidak tersedia, bagian pembanding dilewati (hanya mencetak angka GeoPackage)."""
import os
import sys
from osgeo import gdal, ogr

gdal.UseExceptions()
ogr.UseExceptions()
paket = sys.argv[1]
gpkg = os.path.join(paket, "hasil", "kph_contoh.gpkg")

# 1) kumpulkan semua layer ke satu GeoPackage (sekali saja)
if not os.path.exists(gpkg):
    for berkas, layer in [("Petak", "Petak"), ("Tanah", "Tanah"), ("Jalan", "Jalan"), ("Fasilitas", "Fasilitas"),
                          ("Kejadian", "Kejadian"), ("Stasiun_Hujan", "Stasiun_Hujan"), ("Batas_Wilayah", "Batas_Wilayah")]:
        gdal.VectorTranslate(gpkg, os.path.join(paket, berkas + ".gpkg"),
                             options=gdal.VectorTranslateOptions(format="GPKG", layerName=layer, accessMode="append"))
ds = gdal.OpenEx(gpkg)


def tanya(sql):
    lyr = ds.ExecuteSQL(sql, dialect="SQLite")
    baris = [tuple(f.GetField(i) for i in range(f.GetFieldCount())) for f in lyr]
    ds.ReleaseResultSet(lyr)
    return baris


sqlite = {
    "total_ha": tanya("SELECT ROUND(SUM(ST_Area(geom)) / 10000.0, 2) FROM Petak")[0][0],
    "per_petak_teratas": tanya("SELECT p.kode, COUNT(k.fid) AS n FROM Petak p LEFT JOIN Kejadian k ON ST_Intersects(p.geom, k.geom) "
                               "GROUP BY p.kode ORDER BY n DESC, p.kode LIMIT 3"),
    "tebangan_dekat_jalan": tanya("SELECT COUNT(*) FROM Kejadian k WHERE k.jenis = 'Tebangan liar' AND "
                                  "EXISTS (SELECT 1 FROM Jalan j WHERE ST_Distance(k.geom, j.geom) <= 100)")[0][0],
}
print("GeoPackage/SpatiaLite:", sqlite)

try:
    import psycopg2
    con = psycopg2.connect(dbname=os.environ["PGDATABASE"])      # host, port, user dari PGHOST, PGPORT, PGUSER
    cur = con.cursor()
    cur.execute("SELECT ROUND((SUM(ST_Area(geom)) / 10000.0)::numeric, 2) FROM kph.petak")
    pg_total = float(cur.fetchone()[0])
    cur.execute("SELECT p.kode, COUNT(k.gid) n FROM kph.petak p LEFT JOIN kph.kejadian k ON ST_Intersects(p.geom, k.geom) "
                "GROUP BY p.kode ORDER BY n DESC, p.kode LIMIT 3")
    pg_top = [tuple(r) for r in cur.fetchall()]
    cur.execute("SELECT COUNT(*) FROM kph.kejadian k WHERE k.jenis = 'Tebangan liar' AND "
                "EXISTS (SELECT 1 FROM kph.jalan j WHERE ST_DWithin(k.geom, j.geom, 100))")
    pg_dekat = cur.fetchone()[0]
    print("PostGIS              :", {"total_ha": pg_total, "per_petak_teratas": pg_top, "tebangan_dekat_jalan": pg_dekat})
    cocok = (abs(pg_total - sqlite["total_ha"]) < 0.01 and pg_top == sqlite["per_petak_teratas"] and pg_dekat == sqlite["tebangan_dekat_jalan"])
    print("HASIL SAMA:", cocok)
except Exception as galat:                                          # PostGIS tidak ada atau tidak terhubung
    print("Pembanding PostGIS dilewati:", galat)