# -*- coding: utf-8 -*-
"""M2 Bab 2: padanan Q2 (kejadian per petak) lewat alat Processing QGIS tanpa basis data:
(a) Count points in polygon, (b) Execute SQL (kueri pada layer maya, dialek SQLite; layer masukan bernama input1, input2, dan seterusnya).
Penulis: Badar Mubarok Yogaswara. Pemakaian: python-qgis.bat m2_02d_qgis_setara.py <folder paket-m2>"""
import os
import sys
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import _inisialisasi  # noqa: F401
import processing
from qgis.core import QgsVectorLayer

paket = sys.argv[1]
petak = QgsVectorLayer(os.path.join(paket, "Petak.gpkg|layername=Petak"), "Petak", "ogr")
kej = QgsVectorLayer(os.path.join(paket, "Kejadian.gpkg|layername=Kejadian"), "Kejadian", "ogr")

a = processing.run("native:countpointsinpolygon", {"POLYGONS": petak, "POINTS": kej, "FIELD": "jumlah", "OUTPUT": "memory:"})["OUTPUT"]
hasil_a = sorted(((f["kode"], f["jumlah"]) for f in a.getFeatures()), key=lambda t: (-t[1], t[0]))[:3]
print("Count points in polygon:", hasil_a)

sql = ("SELECT p.kode AS kode, COUNT(k.id) AS jumlah FROM input1 p LEFT JOIN input2 k ON ST_Intersects(p.geometry, k.geometry) "
       "GROUP BY p.kode ORDER BY jumlah DESC, p.kode LIMIT 3")
b = processing.run("qgis:executesql", {"INPUT_DATASOURCES": [petak, kej], "INPUT_QUERY": sql, "INPUT_UID_FIELD": "",
                                       "INPUT_GEOMETRY_FIELD": "", "INPUT_GEOMETRY_TYPE": 1, "INPUT_GEOMETRY_CRS": None,
                                       "OUTPUT": "memory:"})["OUTPUT"]
hasil_b = [(f["kode"], f["jumlah"]) for f in b.getFeatures()]
print("Execute SQL           :", hasil_b)
print("SAMA:", hasil_a == hasil_b)