Spaces:

Ilnaa
/

SiPerak

Runtime error

Upload folder using huggingface_hub

8be0d81 verified 4 months ago

14.2 kB

"""Lapisan akses data (SQLite) untuk pengguna & laporan.
Memisahkan logika penyimpanan dari UI agar mudah diuji (lihat tests/test_db.py).
Semua fungsi menerima parameter `db_path` opsional supaya test bisa memakai
database sementara tanpa mengganggu data asli.
"""

from __future__ import annotations

import sqlite3
from datetime import datetime
from typing import Optional

from src.constants import (
    DB_PATH,
    STATUS_DIDISTRIBUSIKAN,
    STATUS_DITANGANI,
    STATUS_MENUNGGU,
    STATUS_SELESAI,
    STATUS_TERVERIFIKASI,
)


def get_connection(db_path=None) -> sqlite3.Connection:
    path = str(db_path or DB_PATH)
    if path != ":memory:":
        from pathlib import Path

        Path(path).parent.mkdir(parents=True, exist_ok=True)
    conn = sqlite3.connect(path, timeout=15)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys = ON")
    # Tunggu sampai 15 dtk bila DB sedang terkunci (banyak worker gunicorn)
    conn.execute("PRAGMA busy_timeout = 15000")
    if path != ":memory:":
        conn.execute("PRAGMA journal_mode = WAL")
        conn.execute("PRAGMA synchronous = NORMAL")
    return conn


def init_db(conn: sqlite3.Connection) -> None:
    """Buat tabel bila belum ada."""
    conn.executescript(
        """
        CREATE TABLE IF NOT EXISTS users (
            id            INTEGER PRIMARY KEY AUTOINCREMENT,
            nama          TEXT NOT NULL,
            email         TEXT NOT NULL UNIQUE,
            telepon       TEXT NOT NULL UNIQUE,
            password_hash TEXT NOT NULL,
            role          TEXT NOT NULL DEFAULT 'masyarakat',
            created_at    TEXT NOT NULL
        );
        CREATE TABLE IF NOT EXISTS reports (
            id                  INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id             INTEGER NOT NULL,
            judul               TEXT NOT NULL,
            deskripsi           TEXT NOT NULL,
            lokasi              TEXT,
            latitude            REAL,
            longitude           REAL,
            ai_kategori         TEXT,
            ai_kategori_conf    REAL,
            ai_spam_label       TEXT,
            ai_spam_conf        REAL,
            kategori_final      TEXT,
            prioritas           TEXT,
            rekomendasi_ai      TEXT,
            tingkat_eskalasi    TEXT DEFAULT 'Dinas Lokal',
            deadline_eskalasi   TEXT,
            tanggungjawab       TEXT DEFAULT 'Pemerintah Kabupaten',
            status              TEXT NOT NULL,
            instansi            TEXT,
            catatan_admin       TEXT,
            created_at          TEXT NOT NULL,
            verified_at         TEXT,
            lampiran            TEXT,
            FOREIGN KEY (user_id) REFERENCES users (id)
        );
        CREATE TABLE IF NOT EXISTS report_logs (
            id          INTEGER PRIMARY KEY AUTOINCREMENT,
            report_id   INTEGER NOT NULL,
            status      TEXT NOT NULL,
            catatan     TEXT,
            created_at  TEXT NOT NULL,
            FOREIGN KEY (report_id) REFERENCES reports (id)
        );
        CREATE INDEX IF NOT EXISTS idx_reports_user_id ON reports(user_id);
        CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status);
        """
    )
    # Migrasi ringan: tambah kolom bila DB lama belum punya (abaikan bila sudah ada).
    for col in (
        "lokasi TEXT", "instansi TEXT", "lampiran TEXT",
        "latitude REAL", "longitude REAL", "prioritas TEXT",
        "rekomendasi_ai TEXT", "tingkat_eskalasi TEXT DEFAULT 'Dinas Lokal'",
        "deadline_eskalasi TEXT", "tanggungjawab TEXT DEFAULT 'Pemerintah Kabupaten'"
    ):
        try:
            conn.execute(f"ALTER TABLE reports ADD COLUMN {col}")
        except sqlite3.OperationalError:
            pass
    # Index lokasi dibuat SETELAH migrasi agar kolom latitude/longitude pasti ada
    # (pada DB lama, membuat index ini sebelum ALTER TABLE akan menggagalkan init_db).
    try:
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_reports_location ON reports(latitude, longitude)"
        )
    except sqlite3.OperationalError:
        pass
    conn.commit()


# --------------------------- Users ---------------------------

def create_user(conn, nama, email, telepon, password_hash, role="masyarakat") -> int:
    cur = conn.execute(
        "INSERT INTO users (nama, email, telepon, password_hash, role, created_at) "
        "VALUES (?, ?, ?, ?, ?, ?)",
        (nama, email, telepon, password_hash, role, datetime.now().isoformat(timespec="seconds")),
    )
    conn.commit()
    return cur.lastrowid


def get_user_by_email(conn, email) -> Optional[sqlite3.Row]:
    return conn.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()


def email_exists(conn, email) -> bool:
    return conn.execute("SELECT 1 FROM users WHERE email = ?", (email,)).fetchone() is not None


def telepon_exists(conn, telepon) -> bool:
    return conn.execute("SELECT 1 FROM users WHERE telepon = ?", (telepon,)).fetchone() is not None


# --------------------------- Reports ---------------------------

def create_report(conn, user_id, judul, deskripsi, ai_result, lokasi=None, lampiran=None, latitude=None, longitude=None, deadline_eskalasi=None) -> int:
    """Simpan laporan baru beserta hasil analisis AI.
    `ai_result` adalah objek AIResult dari src.ml.classifier.
    """
    cur = conn.execute(
        """
        INSERT INTO reports (
            user_id, judul, deskripsi, lokasi, latitude, longitude,
            ai_kategori, ai_kategori_conf, ai_spam_label, ai_spam_conf,
            kategori_final, status, created_at, lampiran, prioritas, rekomendasi_ai,
            tingkat_eskalasi, deadline_eskalasi, tanggungjawab
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        """,
        (
            user_id,
            judul,
            deskripsi,
            lokasi,
            latitude,
            longitude,
            ai_result.kategori,
            ai_result.kategori_confidence,
            ai_result.spam_label,
            ai_result.spam_confidence,
            ai_result.kategori,  # kategori_final default mengikuti AI, bisa dikoreksi admin
            STATUS_MENUNGGU,
            datetime.now().isoformat(timespec="seconds"),
            lampiran,
            getattr(ai_result, "prioritas", "Rendah"),
            getattr(ai_result, "rekomendasi_ai", None),
            "Dinas Lokal",
            deadline_eskalasi,
            getattr(ai_result, "tanggungjawab", "Pemerintah Kabupaten") or "Pemerintah Kabupaten",
        ),
    )
    conn.commit()
    return cur.lastrowid


def update_report(conn, report_id, judul, deskripsi, ai_result, lokasi=None, latitude=None, longitude=None) -> None:
    """Perbarui isi laporan + hasil analisis AI (UC-03). Status tidak diubah di sini."""
    conn.execute(
        """
        UPDATE reports SET
            judul = ?, deskripsi = ?, lokasi = ?, latitude = ?, longitude = ?,
            ai_kategori = ?, ai_kategori_conf = ?, ai_spam_label = ?, ai_spam_conf = ?,
            kategori_final = ?, prioritas = ?, rekomendasi_ai = ?, tanggungjawab = ?
        WHERE id = ?
        """,
        (
            judul,
            deskripsi,
            lokasi,
            latitude,
            longitude,
            ai_result.kategori,
            ai_result.kategori_confidence,
            ai_result.spam_label,
            ai_result.spam_confidence,
            ai_result.kategori,
            getattr(ai_result, "prioritas", "Rendah"),
            getattr(ai_result, "rekomendasi_ai", None),
            getattr(ai_result, "tanggungjawab", "Pemerintah Kabupaten") or "Pemerintah Kabupaten",
            report_id,
        ),
    )
    conn.commit()


def get_reports_by_user(conn, user_id):
    return conn.execute(
        "SELECT * FROM reports WHERE user_id = ? ORDER BY created_at DESC",
        (user_id,),
    ).fetchall()


def get_reports_by_status(conn, status):
    return conn.execute(
        "SELECT r.*, u.nama AS pelapor FROM reports r "
        "JOIN users u ON u.id = r.user_id WHERE r.status = ? ORDER BY r.created_at ASC",
        (status,),
    ).fetchall()


def get_all_reports(conn):
    return conn.execute(
        "SELECT r.*, u.nama AS pelapor FROM reports r "
        "JOIN users u ON u.id = r.user_id ORDER BY r.created_at DESC"
    ).fetchall()


def review_report(conn, report_id, status, kategori_final=None, catatan_admin=None,
                  instansi=None, tanggungjawab=None) -> None:
    """Admin mengonfirmasi hasil AI: tetapkan status final & (opsional) koreksi kategori/tanggungjawab."""
    conn.execute(
        "UPDATE reports SET status = ?, kategori_final = COALESCE(?, kategori_final), "
        "instansi = COALESCE(?, instansi), catatan_admin = ?, verified_at = ?, "
        "tanggungjawab = COALESCE(?, tanggungjawab) WHERE id = ?",
        (
            status,
            kategori_final,
            instansi,
            catatan_admin,
            datetime.now().isoformat(timespec="seconds"),
            tanggungjawab,
            report_id,
        ),
    )
    conn.commit()


def get_report(conn, report_id):
    return conn.execute("SELECT * FROM reports WHERE id = ?", (report_id,)).fetchone()


# --------------------------- Log aktivitas (UC-05, UC-08) ---------------------------

def add_log(conn, report_id, status, catatan=None) -> None:
    """Catat satu entri riwayat aktivitas laporan (timeline)."""
    conn.execute(
        "INSERT INTO report_logs (report_id, status, catatan, created_at) VALUES (?, ?, ?, ?)",
        (report_id, status, catatan, datetime.now().isoformat(timespec="seconds")),
    )
    conn.commit()


def get_logs(conn, report_id):
    return conn.execute(
        "SELECT * FROM report_logs WHERE report_id = ? ORDER BY id ASC",
        (report_id,),
    ).fetchall()


def count_nearby_reports(conn, latitude, longitude, kategori, radius_km=0.5, days=30) -> int:
    """Menghitung aduan kategori serupa dalam radius km tertentu selama N hari terakhir."""
    if latitude is None or longitude is None or kategori is None:
        return 0
    from datetime import datetime, timedelta
    delta = (radius_km / 111.0)
    since_date = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")
    row = conn.execute(
        """
        SELECT COUNT(*) AS n FROM reports
        WHERE (kategori_final = ? OR ai_kategori = ?)
          AND latitude BETWEEN ? AND ?
          AND longitude BETWEEN ? AND ?
          AND created_at >= ?
        """,
        (kategori, kategori, latitude - delta, latitude + delta, longitude - delta, longitude + delta, since_date)
    ).fetchone()
    return row["n"] if row else 0


def get_overdue_reports(conn):
    """Ambil aduan berstatus 'Didistribusikan' atau 'Sedang Ditangani' yang sudah melewati deadline_eskalasi."""
    now_str = datetime.now().isoformat(timespec="seconds")
    return conn.execute(
        """
        SELECT * FROM reports
        WHERE status IN ('Didistribusikan', 'Sedang Ditangani')
          AND deadline_eskalasi IS NOT NULL
          AND deadline_eskalasi <= ?
        """,
        (now_str,)
    ).fetchall()


def distribute_report(conn, report_id, instansi) -> None:
    """UC-05: teruskan laporan ke instansi & ubah status menjadi Didistribusikan."""
    conn.execute(
        "UPDATE reports SET status = ?, instansi = ? WHERE id = ?",
        (STATUS_DIDISTRIBUSIKAN, instansi, report_id),
    )
    conn.commit()


def set_status(conn, report_id, status) -> None:
    """UC-08: perbarui status operasional laporan."""
    conn.execute("UPDATE reports SET status = ? WHERE id = ?", (status, report_id))
    conn.commit()


def get_reports_for_management(conn):
    """Laporan yang sudah lolos verifikasi (siap distribusi / sedang ditangani / selesai)."""
    return conn.execute(
        "SELECT r.*, u.nama AS pelapor FROM reports r JOIN users u ON u.id = r.user_id "
        "WHERE r.status IN (?, ?, ?, ?) ORDER BY r.created_at DESC",
        (STATUS_TERVERIFIKASI, STATUS_DIDISTRIBUSIKAN, STATUS_DITANGANI, STATUS_SELESAI),
    ).fetchall()


# --------------------------- Statistik (UC-07) ---------------------------

def count_by_status(conn):
    """Jumlah laporan per status -> dict {status: jumlah}."""
    rows = conn.execute(
        "SELECT status, COUNT(*) AS n FROM reports GROUP BY status"
    ).fetchall()
    return {r["status"]: r["n"] for r in rows}


def count_by_category(conn):
    """Jumlah laporan per kategori final (laporan yang sudah punya kategori)."""
    rows = conn.execute(
        "SELECT COALESCE(kategori_final, ai_kategori) AS kat, COUNT(*) AS n "
        "FROM reports WHERE COALESCE(kategori_final, ai_kategori) IS NOT NULL "
        "GROUP BY kat ORDER BY n DESC"
    ).fetchall()
    return {r["kat"]: r["n"] for r in rows}


def count_by_month(conn, months=6):
    """Jumlah laporan masuk per bulan (YYYY-MM) untuk `months` bulan terakhir."""
    rows = conn.execute(
        "SELECT substr(created_at, 1, 7) AS bulan, COUNT(*) AS n "
        "FROM reports GROUP BY bulan ORDER BY bulan DESC LIMIT ?",
        (months,),
    ).fetchall()
    return {r["bulan"]: r["n"] for r in reversed(rows)}


def get_nearby_reports(conn, latitude, longitude, kategori, exclude_id, radius_km=0.5, days=30):
    """Ambil aduan kategori serupa dalam radius km tertentu selama N hari terakhir, selain aduan itu sendiri."""
    if latitude is None or longitude is None or kategori is None:
        return []
    from datetime import datetime, timedelta
    delta = (radius_km / 111.0)
    since_date = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")
    rows = conn.execute(
        """
        SELECT r.*, u.nama AS pelapor FROM reports r
        JOIN users u ON u.id = r.user_id
        WHERE (r.kategori_final = ? OR r.ai_kategori = ?)
          AND r.latitude BETWEEN ? AND ?
          AND r.longitude BETWEEN ? AND ?
          AND r.created_at >= ?
          AND r.id != ?
        ORDER BY r.created_at DESC
        """,
        (kategori, kategori, latitude - delta, latitude + delta, longitude - delta, longitude + delta, since_date, exclude_id)
    ).fetchall()
    return rows

	"""Lapisan akses data (SQLite) untuk pengguna & laporan.

	Memisahkan logika penyimpanan dari UI agar mudah diuji (lihat tests/test_db.py).
	Semua fungsi menerima parameter `db_path` opsional supaya test bisa memakai
	database sementara tanpa mengganggu data asli.
	"""

	from __future__ import annotations

	import sqlite3
	from datetime import datetime
	from typing import Optional

	from src.constants import (
	DB_PATH,
	STATUS_DIDISTRIBUSIKAN,
	STATUS_DITANGANI,
	STATUS_MENUNGGU,
	STATUS_SELESAI,
	STATUS_TERVERIFIKASI,
	)


	def get_connection(db_path=None) -> sqlite3.Connection:
	path = str(db_path or DB_PATH)
	if path != ":memory:":
	from pathlib import Path

	Path(path).parent.mkdir(parents=True, exist_ok=True)
	conn = sqlite3.connect(path, timeout=15)
	conn.row_factory = sqlite3.Row
	conn.execute("PRAGMA foreign_keys = ON")
	# Tunggu sampai 15 dtk bila DB sedang terkunci (banyak worker gunicorn)
	conn.execute("PRAGMA busy_timeout = 15000")
	if path != ":memory:":
	conn.execute("PRAGMA journal_mode = WAL")
	conn.execute("PRAGMA synchronous = NORMAL")
	return conn


	def init_db(conn: sqlite3.Connection) -> None:
	"""Buat tabel bila belum ada."""
	conn.executescript(
	"""
	CREATE TABLE IF NOT EXISTS users (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	nama TEXT NOT NULL,
	email TEXT NOT NULL UNIQUE,
	telepon TEXT NOT NULL UNIQUE,
	password_hash TEXT NOT NULL,
	role TEXT NOT NULL DEFAULT 'masyarakat',
	created_at TEXT NOT NULL
	);

	CREATE TABLE IF NOT EXISTS reports (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	user_id INTEGER NOT NULL,
	judul TEXT NOT NULL,
	deskripsi TEXT NOT NULL,
	lokasi TEXT,
	latitude REAL,
	longitude REAL,
	ai_kategori TEXT,
	ai_kategori_conf REAL,
	ai_spam_label TEXT,
	ai_spam_conf REAL,
	kategori_final TEXT,
	prioritas TEXT,
	rekomendasi_ai TEXT,
	tingkat_eskalasi TEXT DEFAULT 'Dinas Lokal',
	deadline_eskalasi TEXT,
	tanggungjawab TEXT DEFAULT 'Pemerintah Kabupaten',
	status TEXT NOT NULL,
	instansi TEXT,
	catatan_admin TEXT,
	created_at TEXT NOT NULL,
	verified_at TEXT,
	lampiran TEXT,
	FOREIGN KEY (user_id) REFERENCES users (id)
	);

	CREATE TABLE IF NOT EXISTS report_logs (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	report_id INTEGER NOT NULL,
	status TEXT NOT NULL,
	catatan TEXT,
	created_at TEXT NOT NULL,
	FOREIGN KEY (report_id) REFERENCES reports (id)
	);

	CREATE INDEX IF NOT EXISTS idx_reports_user_id ON reports(user_id);
	CREATE INDEX IF NOT EXISTS idx_reports_status ON reports(status);
	"""
	)
	# Migrasi ringan: tambah kolom bila DB lama belum punya (abaikan bila sudah ada).
	for col in (
	"lokasi TEXT", "instansi TEXT", "lampiran TEXT",
	"latitude REAL", "longitude REAL", "prioritas TEXT",
	"rekomendasi_ai TEXT", "tingkat_eskalasi TEXT DEFAULT 'Dinas Lokal'",
	"deadline_eskalasi TEXT", "tanggungjawab TEXT DEFAULT 'Pemerintah Kabupaten'"
	):
	try:
	conn.execute(f"ALTER TABLE reports ADD COLUMN {col}")
	except sqlite3.OperationalError:
	pass
	# Index lokasi dibuat SETELAH migrasi agar kolom latitude/longitude pasti ada
	# (pada DB lama, membuat index ini sebelum ALTER TABLE akan menggagalkan init_db).
	try:
	conn.execute(
	"CREATE INDEX IF NOT EXISTS idx_reports_location ON reports(latitude, longitude)"
	)
	except sqlite3.OperationalError:
	pass
	conn.commit()


	# --------------------------- Users ---------------------------

	def create_user(conn, nama, email, telepon, password_hash, role="masyarakat") -> int:
	cur = conn.execute(
	"INSERT INTO users (nama, email, telepon, password_hash, role, created_at) "
	"VALUES (?, ?, ?, ?, ?, ?)",
	(nama, email, telepon, password_hash, role, datetime.now().isoformat(timespec="seconds")),
	)
	conn.commit()
	return cur.lastrowid


	def get_user_by_email(conn, email) -> Optional[sqlite3.Row]:
	return conn.execute("SELECT * FROM users WHERE email = ?", (email,)).fetchone()


	def email_exists(conn, email) -> bool:
	return conn.execute("SELECT 1 FROM users WHERE email = ?", (email,)).fetchone() is not None


	def telepon_exists(conn, telepon) -> bool:
	return conn.execute("SELECT 1 FROM users WHERE telepon = ?", (telepon,)).fetchone() is not None


	# --------------------------- Reports ---------------------------

	def create_report(conn, user_id, judul, deskripsi, ai_result, lokasi=None, lampiran=None, latitude=None, longitude=None, deadline_eskalasi=None) -> int:
	"""Simpan laporan baru beserta hasil analisis AI.

	`ai_result` adalah objek AIResult dari src.ml.classifier.
	"""
	cur = conn.execute(
	"""
	INSERT INTO reports (
	user_id, judul, deskripsi, lokasi, latitude, longitude,
	ai_kategori, ai_kategori_conf, ai_spam_label, ai_spam_conf,
	kategori_final, status, created_at, lampiran, prioritas, rekomendasi_ai,
	tingkat_eskalasi, deadline_eskalasi, tanggungjawab
	) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
	""",
	(
	user_id,
	judul,
	deskripsi,
	lokasi,
	latitude,
	longitude,
	ai_result.kategori,
	ai_result.kategori_confidence,
	ai_result.spam_label,
	ai_result.spam_confidence,
	ai_result.kategori, # kategori_final default mengikuti AI, bisa dikoreksi admin
	STATUS_MENUNGGU,
	datetime.now().isoformat(timespec="seconds"),
	lampiran,
	getattr(ai_result, "prioritas", "Rendah"),
	getattr(ai_result, "rekomendasi_ai", None),
	"Dinas Lokal",
	deadline_eskalasi,
	getattr(ai_result, "tanggungjawab", "Pemerintah Kabupaten") or "Pemerintah Kabupaten",
	),
	)
	conn.commit()
	return cur.lastrowid


	def update_report(conn, report_id, judul, deskripsi, ai_result, lokasi=None, latitude=None, longitude=None) -> None:
	"""Perbarui isi laporan + hasil analisis AI (UC-03). Status tidak diubah di sini."""
	conn.execute(
	"""
	UPDATE reports SET
	judul = ?, deskripsi = ?, lokasi = ?, latitude = ?, longitude = ?,
	ai_kategori = ?, ai_kategori_conf = ?, ai_spam_label = ?, ai_spam_conf = ?,
	kategori_final = ?, prioritas = ?, rekomendasi_ai = ?, tanggungjawab = ?
	WHERE id = ?
	""",
	(
	judul,
	deskripsi,
	lokasi,
	latitude,
	longitude,
	ai_result.kategori,
	ai_result.kategori_confidence,
	ai_result.spam_label,
	ai_result.spam_confidence,
	ai_result.kategori,
	getattr(ai_result, "prioritas", "Rendah"),
	getattr(ai_result, "rekomendasi_ai", None),
	getattr(ai_result, "tanggungjawab", "Pemerintah Kabupaten") or "Pemerintah Kabupaten",
	report_id,
	),
	)
	conn.commit()


	def get_reports_by_user(conn, user_id):
	return conn.execute(
	"SELECT * FROM reports WHERE user_id = ? ORDER BY created_at DESC",
	(user_id,),
	).fetchall()


	def get_reports_by_status(conn, status):
	return conn.execute(
	"SELECT r.*, u.nama AS pelapor FROM reports r "
	"JOIN users u ON u.id = r.user_id WHERE r.status = ? ORDER BY r.created_at ASC",
	(status,),
	).fetchall()


	def get_all_reports(conn):
	return conn.execute(
	"SELECT r.*, u.nama AS pelapor FROM reports r "
	"JOIN users u ON u.id = r.user_id ORDER BY r.created_at DESC"
	).fetchall()


	def review_report(conn, report_id, status, kategori_final=None, catatan_admin=None,
	instansi=None, tanggungjawab=None) -> None:
	"""Admin mengonfirmasi hasil AI: tetapkan status final & (opsional) koreksi kategori/tanggungjawab."""
	conn.execute(
	"UPDATE reports SET status = ?, kategori_final = COALESCE(?, kategori_final), "
	"instansi = COALESCE(?, instansi), catatan_admin = ?, verified_at = ?, "
	"tanggungjawab = COALESCE(?, tanggungjawab) WHERE id = ?",
	(
	status,
	kategori_final,
	instansi,
	catatan_admin,
	datetime.now().isoformat(timespec="seconds"),
	tanggungjawab,
	report_id,
	),
	)
	conn.commit()


	def get_report(conn, report_id):
	return conn.execute("SELECT * FROM reports WHERE id = ?", (report_id,)).fetchone()


	# --------------------------- Log aktivitas (UC-05, UC-08) ---------------------------

	def add_log(conn, report_id, status, catatan=None) -> None:
	"""Catat satu entri riwayat aktivitas laporan (timeline)."""
	conn.execute(
	"INSERT INTO report_logs (report_id, status, catatan, created_at) VALUES (?, ?, ?, ?)",
	(report_id, status, catatan, datetime.now().isoformat(timespec="seconds")),
	)
	conn.commit()


	def get_logs(conn, report_id):
	return conn.execute(
	"SELECT * FROM report_logs WHERE report_id = ? ORDER BY id ASC",
	(report_id,),
	).fetchall()


	def count_nearby_reports(conn, latitude, longitude, kategori, radius_km=0.5, days=30) -> int:
	"""Menghitung aduan kategori serupa dalam radius km tertentu selama N hari terakhir."""
	if latitude is None or longitude is None or kategori is None:
	return 0
	from datetime import datetime, timedelta
	delta = (radius_km / 111.0)
	since_date = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")
	row = conn.execute(
	"""
	SELECT COUNT(*) AS n FROM reports
	WHERE (kategori_final = ? OR ai_kategori = ?)
	AND latitude BETWEEN ? AND ?
	AND longitude BETWEEN ? AND ?
	AND created_at >= ?
	""",
	(kategori, kategori, latitude - delta, latitude + delta, longitude - delta, longitude + delta, since_date)
	).fetchone()
	return row["n"] if row else 0


	def get_overdue_reports(conn):
	"""Ambil aduan berstatus 'Didistribusikan' atau 'Sedang Ditangani' yang sudah melewati deadline_eskalasi."""
	now_str = datetime.now().isoformat(timespec="seconds")
	return conn.execute(
	"""
	SELECT * FROM reports
	WHERE status IN ('Didistribusikan', 'Sedang Ditangani')
	AND deadline_eskalasi IS NOT NULL
	AND deadline_eskalasi <= ?
	""",
	(now_str,)
	).fetchall()


	def distribute_report(conn, report_id, instansi) -> None:
	"""UC-05: teruskan laporan ke instansi & ubah status menjadi Didistribusikan."""
	conn.execute(
	"UPDATE reports SET status = ?, instansi = ? WHERE id = ?",
	(STATUS_DIDISTRIBUSIKAN, instansi, report_id),
	)
	conn.commit()


	def set_status(conn, report_id, status) -> None:
	"""UC-08: perbarui status operasional laporan."""
	conn.execute("UPDATE reports SET status = ? WHERE id = ?", (status, report_id))
	conn.commit()


	def get_reports_for_management(conn):
	"""Laporan yang sudah lolos verifikasi (siap distribusi / sedang ditangani / selesai)."""
	return conn.execute(
	"SELECT r.*, u.nama AS pelapor FROM reports r JOIN users u ON u.id = r.user_id "
	"WHERE r.status IN (?, ?, ?, ?) ORDER BY r.created_at DESC",
	(STATUS_TERVERIFIKASI, STATUS_DIDISTRIBUSIKAN, STATUS_DITANGANI, STATUS_SELESAI),
	).fetchall()


	# --------------------------- Statistik (UC-07) ---------------------------

	def count_by_status(conn):
	"""Jumlah laporan per status -> dict {status: jumlah}."""
	rows = conn.execute(
	"SELECT status, COUNT(*) AS n FROM reports GROUP BY status"
	).fetchall()
	return {r["status"]: r["n"] for r in rows}


	def count_by_category(conn):
	"""Jumlah laporan per kategori final (laporan yang sudah punya kategori)."""
	rows = conn.execute(
	"SELECT COALESCE(kategori_final, ai_kategori) AS kat, COUNT(*) AS n "
	"FROM reports WHERE COALESCE(kategori_final, ai_kategori) IS NOT NULL "
	"GROUP BY kat ORDER BY n DESC"
	).fetchall()
	return {r["kat"]: r["n"] for r in rows}


	def count_by_month(conn, months=6):
	"""Jumlah laporan masuk per bulan (YYYY-MM) untuk `months` bulan terakhir."""
	rows = conn.execute(
	"SELECT substr(created_at, 1, 7) AS bulan, COUNT(*) AS n "
	"FROM reports GROUP BY bulan ORDER BY bulan DESC LIMIT ?",
	(months,),
	).fetchall()
	return {r["bulan"]: r["n"] for r in reversed(rows)}


	def get_nearby_reports(conn, latitude, longitude, kategori, exclude_id, radius_km=0.5, days=30):
	"""Ambil aduan kategori serupa dalam radius km tertentu selama N hari terakhir, selain aduan itu sendiri."""
	if latitude is None or longitude is None or kategori is None:
	return []
	from datetime import datetime, timedelta
	delta = (radius_km / 111.0)
	since_date = (datetime.now() - timedelta(days=days)).isoformat(timespec="seconds")
	rows = conn.execute(
	"""
	SELECT r.*, u.nama AS pelapor FROM reports r
	JOIN users u ON u.id = r.user_id
	WHERE (r.kategori_final = ? OR r.ai_kategori = ?)
	AND r.latitude BETWEEN ? AND ?
	AND r.longitude BETWEEN ? AND ?
	AND r.created_at >= ?
	AND r.id != ?
	ORDER BY r.created_at DESC
	""",
	(kategori, kategori, latitude - delta, latitude + delta, longitude - delta, longitude + delta, since_date, exclude_id)
	).fetchall()
	return rows