"""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