import logging
import sqlite3
from typing import Dict, List

from pypos.core.utils.path_utils import get_db_path
from pypos.core.utils.db_helper import build_sqlite_readonly_uri, open_sqlite_connection


LOGGER = logging.getLogger(__name__)


class PpnModeStartupAuditService:
    # edited by glg
    # Audit startup read-only: hanya observasi kualitas data ppn_mode, tanpa mutasi DB.
    def __init__(self, db_path=None, logger=None):
        self.db_path = db_path or get_db_path()
        self.logger = logger or LOGGER

    # edited by glg
    # Read-only connection agar audit startup tidak memicu migrasi/penulisan data.
    def _connect_readonly(self):
        db_uri = build_sqlite_readonly_uri(self.db_path)
        return open_sqlite_connection(
            db_path=db_uri,
            timeout=2,
            uri=True,
            apply_pragmas=False,
            ensure_indexes=False,
            run_migrations=False,
        )

    def _table_exists(self, cursor, table_name: str) -> bool:
        cursor.execute(
            "SELECT name FROM sqlite_master WHERE type='table' AND name=?",
            (str(table_name or "").strip(),),
        )
        return cursor.fetchone() is not None

    def _get_columns(self, cursor, table_name: str) -> set:
        cursor.execute(f"PRAGMA table_info({table_name})")
        return {str(row[1]) for row in cursor.fetchall() if row and len(row) > 1}

    def _safe_fetchone_int(self, cursor, query: str, params=()) -> int:
        cursor.execute(query, params)
        row = cursor.fetchone()
        if not row:
            return 0
        try:
            return int(row[0] or 0)
        except (TypeError, ValueError):
            return 0

    def _fetch_missing_marker_samples(self, cursor, has_ppn_mode: bool, sample_limit: int) -> List[Dict]:
        if sample_limit <= 0:
            return []
        if has_ppn_mode:
            cursor.execute(
                """
                SELECT id, nomer, dtime, COALESCE(ppn_mode, 'exclude') AS ppn_mode
                FROM transaksi
                WHERE COALESCE(trash, 0) = 0
                  AND (
                        COALESCE(diskon_log, '') = ''
                        OR (
                            LOWER(COALESCE(diskon_log, '')) NOT LIKE '%ppn_mode=include%'
                            AND LOWER(COALESCE(diskon_log, '')) NOT LIKE '%ppn_mode=exclude%'
                        )
                  )
                ORDER BY id DESC
                LIMIT ?
                """,
                (sample_limit,),
            )
        else:
            cursor.execute(
                """
                SELECT id, nomer, dtime, 'exclude' AS ppn_mode
                FROM transaksi
                WHERE COALESCE(trash, 0) = 0
                ORDER BY id DESC
                LIMIT ?
                """,
                (sample_limit,),
            )
        rows = cursor.fetchall() or []
        samples = []
        for row in rows:
            samples.append(
                {
                    "id": row[0],
                    "nomer": row[1],
                    "dtime": row[2],
                    "ppn_mode": row[3],
                }
            )
        return samples

    def run_startup_audit(self, sample_limit=5) -> Dict:
        sample_cap = max(0, min(50, int(sample_limit or 0)))
        conn = None
        try:
            conn = self._connect_readonly()
            cursor = conn.cursor()

            if not self._table_exists(cursor, "transaksi"):
                result = {
                    "ok": False,
                    "reason": "table_transaksi_not_found",
                    "db_path": self.db_path,
                }
                self.logger.warning("[PPN_AUDIT_STARTUP] transaksi table tidak ditemukan. db=%s", self.db_path)
                return result

            cols = self._get_columns(cursor, "transaksi")
            has_ppn_mode = "ppn_mode" in cols
            has_diskon_log = "diskon_log" in cols

            total_transaksi = self._safe_fetchone_int(
                cursor,
                "SELECT COUNT(*) FROM transaksi WHERE COALESCE(trash, 0) = 0",
            )

            include_count = 0
            exclude_count = 0
            invalid_mode_count = 0
            if has_ppn_mode:
                include_count = self._safe_fetchone_int(
                    cursor,
                    "SELECT COUNT(*) FROM transaksi WHERE COALESCE(trash, 0)=0 AND LOWER(COALESCE(ppn_mode,''))='include'",
                )
                exclude_count = self._safe_fetchone_int(
                    cursor,
                    "SELECT COUNT(*) FROM transaksi WHERE COALESCE(trash, 0)=0 AND LOWER(COALESCE(ppn_mode,''))='exclude'",
                )
                invalid_mode_count = self._safe_fetchone_int(
                    cursor,
                    """
                    SELECT COUNT(*)
                    FROM transaksi
                    WHERE COALESCE(trash, 0)=0
                      AND LOWER(COALESCE(ppn_mode, '')) NOT IN ('include', 'exclude')
                    """,
                )

            missing_marker_count = 0
            mismatch_count = 0
            if has_diskon_log:
                missing_marker_count = self._safe_fetchone_int(
                    cursor,
                    """
                    SELECT COUNT(*)
                    FROM transaksi
                    WHERE COALESCE(trash, 0)=0
                      AND (
                            COALESCE(diskon_log, '') = ''
                            OR (
                                LOWER(COALESCE(diskon_log, '')) NOT LIKE '%ppn_mode=include%'
                                AND LOWER(COALESCE(diskon_log, '')) NOT LIKE '%ppn_mode=exclude%'
                            )
                      )
                    """,
                )
                if has_ppn_mode:
                    mismatch_count = self._safe_fetchone_int(
                        cursor,
                        """
                        SELECT COUNT(*)
                        FROM transaksi
                        WHERE COALESCE(trash, 0)=0
                          AND LOWER(COALESCE(diskon_log, '')) LIKE '%ppn_mode=%'
                          AND (
                                (LOWER(COALESCE(diskon_log, '')) LIKE '%ppn_mode=include%' AND LOWER(COALESCE(ppn_mode, '')) <> 'include')
                                OR
                                (LOWER(COALESCE(diskon_log, '')) LIKE '%ppn_mode=exclude%' AND LOWER(COALESCE(ppn_mode, '')) <> 'exclude')
                              )
                        """,
                    )
            else:
                missing_marker_count = total_transaksi

            missing_marker_samples = self._fetch_missing_marker_samples(
                cursor=cursor,
                has_ppn_mode=has_ppn_mode,
                sample_limit=sample_cap,
            )

            result = {
                "ok": True,
                "db_path": self.db_path,
                "has_ppn_mode_column": bool(has_ppn_mode),
                "has_diskon_log_column": bool(has_diskon_log),
                "total_transaksi": int(total_transaksi),
                "ppn_mode_include": int(include_count),
                "ppn_mode_exclude": int(exclude_count),
                "invalid_ppn_mode_count": int(invalid_mode_count),
                "missing_marker_count": int(missing_marker_count),
                "mismatch_count": int(mismatch_count),
                "sample_limit": int(sample_cap),
                "missing_marker_samples": missing_marker_samples,
            }

            self.logger.info(
                "[PPN_AUDIT_STARTUP] total=%s include=%s exclude=%s invalid_mode=%s missing_marker=%s mismatch=%s has_ppn_mode_col=%s has_diskon_log_col=%s",
                result["total_transaksi"],
                result["ppn_mode_include"],
                result["ppn_mode_exclude"],
                result["invalid_ppn_mode_count"],
                result["missing_marker_count"],
                result["mismatch_count"],
                int(result["has_ppn_mode_column"]),
                int(result["has_diskon_log_column"]),
            )
            if result["missing_marker_count"] > 0:
                self.logger.warning(
                    "[PPN_AUDIT_STARTUP] ditemukan %s transaksi legacy tanpa marker ppn_mode di diskon_log",
                    result["missing_marker_count"],
                )
            if result["mismatch_count"] > 0:
                self.logger.warning(
                    "[PPN_AUDIT_STARTUP] ditemukan %s transaksi mismatch antara ppn_mode kolom vs diskon_log",
                    result["mismatch_count"],
                )
            for sample in missing_marker_samples:
                self.logger.warning(
                    "[PPN_AUDIT_STARTUP][sample] id=%s nomer=%s dtime=%s ppn_mode=%s",
                    sample.get("id"),
                    sample.get("nomer"),
                    sample.get("dtime"),
                    sample.get("ppn_mode"),
                )

            return result
        except (sqlite3.Error, TypeError, ValueError, OSError) as exc:
            self.logger.warning("[PPN_AUDIT_STARTUP] audit gagal: %s", exc)
            return {
                "ok": False,
                "reason": str(exc),
                "db_path": self.db_path,
            }
        finally:
            try:
                if conn is not None:
                    conn.close()
            except sqlite3.Error:
                pass