import sqlite3

from pypos.core.base_model import BaseModel
from pypos.core.utils.db_helper import connect_sqlite
from pypos.core.utils.path_utils import get_db_path
from pypos.core.utils.sql_query_builder import (
    build_insert_sql,
    build_sql_with_optional_where,
    build_update_sql,
)


class CustomerModel(BaseModel):
    def __init__(self):
        super().__init__()
        self.db_path = get_db_path()

    def connect(self):
        return connect_sqlite(self.db_path)

    # edited by glg
    def _customer_table_exists(self, cursor):
        try:
            cursor.execute(
                "SELECT name FROM sqlite_master WHERE type='table' AND name='per_customers'"
            )
            return cursor.fetchone() is not None
        except sqlite3.Error:
            return False

    # edited by glg
    def _get_customer_columns(self, cursor):
        try:
            cursor.execute("PRAGMA table_info(per_customers)")
            return {
                str(row[1])
                for row in cursor.fetchall()
                if row and len(row) > 1 and row[1]
            }
        except sqlite3.Error:
            return set()

    # edited by glg
    @staticmethod
    def _build_active_filters(customer_cols):
        filters = []
        if "status" in customer_cols:
            filters.append("COALESCE(status, 1) = 1")
        if "trash" in customer_cols:
            filters.append("COALESCE(trash, 0) = 0")
        return filters

    # edited by glg
    @staticmethod
    def _build_where_sql(active_filters, search_term=None):
        clauses = list(active_filters or [])
        params = []
        if search_term:
            clauses.append("COALESCE(nama, '') LIKE ?")
            params.append("%" + str(search_term) + "%")
        if not clauses:
            return "", params
        return " WHERE " + " AND ".join(clauses), params

    def get_customers(self, search_term=None):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                return []
            customer_cols = self._get_customer_columns(cursor)
            where_sql, params = self._build_where_sql(
                self._build_active_filters(customer_cols),
                search_term=search_term,
            )
            query = build_sql_with_optional_where(
                "SELECT id, nama, alamat_1, tlp_1 FROM per_customers",
                where_sql,
                sql_suffix="ORDER BY id ASC",
            )
            cursor.execute(query, tuple(params))
            return cursor.fetchall()
        finally:
            conn.close()

    def count_customers(self, search_term=None):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                return 0
            customer_cols = self._get_customer_columns(cursor)
            where_sql, params = self._build_where_sql(
                self._build_active_filters(customer_cols),
                search_term=search_term,
            )
            query = build_sql_with_optional_where(
                "SELECT COUNT(1) FROM per_customers",
                where_sql,
            )
            cursor.execute(query, tuple(params))
            row = cursor.fetchone()
            return int(row[0] if row else 0)
        finally:
            conn.close()

    def get_customers_paged(self, search_term=None, limit=100, offset=0):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                return []
            customer_cols = self._get_customer_columns(cursor)
            paging_limit = max(1, int(limit or 100))
            paging_offset = max(0, int(offset or 0))
            where_sql, params = self._build_where_sql(
                self._build_active_filters(customer_cols),
                search_term=search_term,
            )
            query = build_sql_with_optional_where(
                "SELECT id, nama, alamat_1, tlp_1 FROM per_customers",
                where_sql,
                sql_suffix="ORDER BY id ASC LIMIT ? OFFSET ?",
            )
            cursor.execute(query, tuple(params + [paging_limit, paging_offset]))
            return cursor.fetchall()
        finally:
            conn.close()

    def get_customer_by_id(self, customer_id):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                return None
            customer_cols = self._get_customer_columns(cursor)
            where_clauses = ["id = ?"] + self._build_active_filters(customer_cols)
            where_sql = " AND ".join(where_clauses)
            query = build_sql_with_optional_where(
                "SELECT id, nama, alamat_1, tlp_1 FROM per_customers",
                where_sql,
                sql_suffix="LIMIT 1",
            )
            cursor.execute(query, (customer_id,))
            return cursor.fetchone()
        finally:
            conn.close()

    def add_customer(self, nama, alamat, telepon):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                raise RuntimeError("Tabel per_customers tidak ditemukan.")
            customer_cols = self._get_customer_columns(cursor)
            columns = ["nama", "alamat_1", "tlp_1"]
            values = [nama, alamat, telepon]
            if "status" in customer_cols:
                columns.append("status")
                values.append(1)
            if "trash" in customer_cols:
                columns.append("trash")
                values.append(0)
            query = build_insert_sql("per_customers", columns)
            cursor.execute(query, tuple(values))
            conn.commit()
        finally:
            conn.close()

    def update_customer(self, customer_id, nama, alamat, telepon):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                raise RuntimeError("Tabel per_customers tidak ditemukan.")
            customer_cols = self._get_customer_columns(cursor)
            where_clauses = ["id = ?"] + self._build_active_filters(customer_cols)
            where_sql = " AND ".join(where_clauses)
            query = build_update_sql(
                "per_customers",
                ["nama", "alamat_1", "tlp_1"],
                where_sql=where_sql,
            )
            cursor.execute(query, (nama, alamat, telepon, customer_id))
            conn.commit()
        finally:
            conn.close()

    def delete_customer(self, customer_id):
        conn = self.connect()
        try:
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                raise RuntimeError("Tabel per_customers tidak ditemukan.")
            customer_cols = self._get_customer_columns(cursor)
            if "trash" in customer_cols or "status" in customer_cols:
                set_columns = []
                params = []
                if "trash" in customer_cols:
                    set_columns.append("trash")
                    params.append(1)
                if "status" in customer_cols:
                    set_columns.append("status")
                    params.append(0)
                params.append(customer_id)
                query = build_update_sql(
                    "per_customers",
                    set_columns,
                    where_sql="id = ?",
                )
                cursor.execute(query, tuple(params))
            else:
                # Fallback legacy schema tanpa kolom soft-delete.
                cursor.execute("DELETE FROM per_customers WHERE id = ?", (customer_id,))
            conn.commit()
        finally:
            conn.close()

    def load_all_customers(self):
        conn = self.connect()
        try:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            if not self._customer_table_exists(cursor):
                return []
            customer_cols = self._get_customer_columns(cursor)
            where_sql, params = self._build_where_sql(
                self._build_active_filters(customer_cols),
                search_term=None,
            )
            query = build_sql_with_optional_where(
                "SELECT id, nama, alamat_1, tlp_1 FROM per_customers",
                where_sql,
                sql_suffix="ORDER BY id ASC",
            )
            cursor.execute(query, tuple(params))
            rows = cursor.fetchall()
            return [dict(row) for row in rows]
        finally:
            conn.close()