import sqlite3
from pypos.core.base_model import BaseModel
from pypos.core.utils.path_utils import get_db_path
from pypos.core.utils.db_helper import connect_sqlite


class AuthModel(BaseModel):
    """Model untuk menangani query database terkait autentikasi"""

    def __init__(self, db_path=None):
        super().__init__()
        self.db_path = db_path or get_db_path()
        self._employee_columns_cache = None

    # edited by glg
    def _get_employee_columns(self, cursor):
        cached = self._employee_columns_cache
        if isinstance(cached, set) and cached:
            return set(cached)
        try:
            cursor.execute("PRAGMA table_info(per_employee)")
            rows = cursor.fetchall() or []
            columns = set()
            for row in rows:
                if not row or len(row) < 2:
                    continue
                col = str(row[1] or "").strip().lower()
                if col:
                    columns.add(col)
        except (sqlite3.Error, RuntimeError, TypeError, ValueError):
            columns = set()
        self._employee_columns_cache = set(columns)
        return set(columns)

    # edited by glg
    @staticmethod
    def _append_active_filters(base_query, available_columns):
        columns = set(available_columns or set())
        clauses = []
        if "status" in columns:
            clauses.append("COALESCE(status, 1) = 1")
        if "trash" in columns:
            clauses.append("COALESCE(trash, 0) = 0")
        if not clauses:
            return str(base_query or "")
        return f"{str(base_query or '').strip()} AND {' AND '.join(clauses)}"

    def get_user_by_username(self, username):
        """Ambil data user berdasarkan username"""
        uname = str(username or "").strip()
        if not uname:
            return None
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            columns = self._get_employee_columns(cursor)
            query = self._append_active_filters(
                "SELECT id, nama, password FROM per_employee WHERE nama_login = ?",
                columns,
            )
            cursor.execute(query, (uname,))
            user = cursor.fetchone()
            return dict(user) if user else None
        finally:
            conn.close()

    def get_user_by_id(self, user_id):
        try:
            uid = int(user_id)
        except (TypeError, ValueError):
            return None
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            columns = self._get_employee_columns(cursor)
            query = self._append_active_filters(
                "SELECT id, nama, password FROM per_employee WHERE id = ?",
                columns,
            )
            cursor.execute(query, (uid,))
            user = cursor.fetchone()
            return dict(user) if user else None
        finally:
            conn.close()

    def get_admin_by_username(self, username):
        """Ambil data admin berdasarkan username"""
        uname = str(username or "").strip()
        if not uname:
            return None
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            columns = self._get_employee_columns(cursor)
            query = self._append_active_filters(
                "SELECT id, nama, password FROM per_employee "
                "WHERE nama_login = ? AND LOWER(nama_login) LIKE 'admin%'",
                columns,
            )
            cursor.execute(query, (uname,))
            admin = cursor.fetchone()
            return dict(admin) if admin else None
        finally:
            conn.close()

    def get_admin_offline_status(self, username):
        uname = str(username or "").strip()
        if not uname:
            return None
        conn = connect_sqlite(self.db_path)
        conn.row_factory = sqlite3.Row
        cursor = conn.cursor()
        try:
            columns = self._get_employee_columns(cursor)
            query = self._append_active_filters(
                """
                SELECT id, nama, password, COALESCE(oto_settlement, 0) as oto_settlement
                FROM per_employee
                WHERE nama_login = ?
                """,
                columns,
            )
            cursor.execute(query, (uname,))
            row = cursor.fetchone()
            return dict(row) if row else None
        finally:
            conn.close()

    def update_password_hash_by_id(self, user_id, new_hash):
        conn = connect_sqlite(self.db_path)
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE per_employee SET password = ? WHERE id = ?",
            (new_hash, user_id)
        )
        conn.commit()
        conn.close()

    def update_password_hash_by_username(self, username, new_hash):
        conn = connect_sqlite(self.db_path)
        cursor = conn.cursor()
        cursor.execute(
            "UPDATE per_employee SET password = ? WHERE nama_login = ?",
            (new_hash, username)
        )
        conn.commit()
        conn.close()