# edited by glg
import sqlite3
import tempfile
from pathlib import Path

from pypos.modules.customer.models.customer_search_model import CustomerSearchModel


def _seed_customer_schema(db_path: str):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS per_customers (
            id INTEGER PRIMARY KEY,
            nama TEXT,
            kode TEXT,
            member_id TEXT,
            mid TEXT,
            email TEXT,
            tlp_1 TEXT,
            level_nama TEXT,
            level_id TEXT,
            loyalti_point REAL DEFAULT 0,
            propinsi TEXT,
            status INTEGER DEFAULT 1,
            trash INTEGER DEFAULT 0
        )
        """
    )
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS per_customer_level (
            id INTEGER PRIMARY KEY,
            nama TEXT
        )
        """
    )
    conn.commit()
    conn.close()


def _seed_customer_schema_minimal(db_path: str):
    conn = sqlite3.connect(db_path)
    cur = conn.cursor()
    cur.execute(
        """
        CREATE TABLE IF NOT EXISTS per_customers (
            id INTEGER PRIMARY KEY,
            nama TEXT
        )
        """
    )
    conn.commit()
    conn.close()


def test_customer_search_keyword_and_paging():
    with tempfile.TemporaryDirectory() as td:
        db_path = str(Path(td) / "customer_search.db")
        _seed_customer_schema(db_path)
        model = CustomerSearchModel(db_path=db_path)

        conn = sqlite3.connect(db_path)
        cur = conn.cursor()
        cur.execute("INSERT INTO per_customer_level (id, nama) VALUES (1, 'Silver')")
        cur.execute(
            """
            INSERT INTO per_customers (id, nama, kode, member_id, mid, email, tlp_1, level_id, loyalti_point, propinsi)
            VALUES (1, 'Andi', 'A-001', 'M-001', 'MID-1', 'andi@test.com', '081111', 1, 12, 'Jawa Barat')
            """
        )
        cur.execute(
            """
            INSERT INTO per_customers (id, nama, kode, member_id, mid, email, tlp_1, level_id, loyalti_point, propinsi)
            VALUES (2, 'Budi', 'B-001', 'M-002', 'MID-2', 'budi@test.com', '082222', 1, 34, 'Jawa Timur')
            """
        )
        conn.commit()
        conn.close()

        total = model.count_customers(keyword="M-")
        assert total == 2

        rows = model.search_customers(keyword="Andi", limit=10, offset=0)
        assert len(rows) == 1
        assert rows[0]["nama"] == "Andi"
        assert rows[0]["level_nama"] == "Silver"

        rows_page = model.search_customers(keyword="M-", limit=1, offset=1)
        assert len(rows_page) == 1
        assert rows_page[0]["nama"] in {"Andi", "Budi"}


def test_customer_search_returns_empty_when_table_missing():
    with tempfile.TemporaryDirectory() as td:
        db_path = str(Path(td) / "customer_empty.db")
        model = CustomerSearchModel(db_path=db_path)
        assert model.count_customers(keyword="apa saja") == 0
        assert model.search_customers(keyword="apa saja", limit=10, offset=0) == []


def test_customer_search_compatible_when_optional_columns_missing_and_no_runtime_schema_mutation():
    with tempfile.TemporaryDirectory() as td:
        db_path = str(Path(td) / "customer_minimal.db")
        _seed_customer_schema_minimal(db_path)
        model = CustomerSearchModel(db_path=db_path)

        conn = sqlite3.connect(db_path)
        cur = conn.cursor()
        cur.execute("INSERT INTO per_customers (id, nama) VALUES (1, 'Minimal User')")
        conn.commit()

        cur.execute("PRAGMA table_info(per_customers)")
        cols = [str(row[1]) for row in cur.fetchall() if row and len(row) > 1]
        conn.close()

        assert cols == ["id", "nama"]
        rows = model.search_customers(keyword="Minimal", limit=10, offset=0)
        assert len(rows) == 1
        assert rows[0]["nama"] == "Minimal User"
        assert rows[0]["email"] == ""


def test_customer_search_filters_inactive_and_trashed_records():
    with tempfile.TemporaryDirectory() as td:
        db_path = str(Path(td) / "customer_active_filter.db")
        _seed_customer_schema(db_path)
        model = CustomerSearchModel(db_path=db_path)

        conn = sqlite3.connect(db_path)
        cur = conn.cursor()
        cur.execute(
            """
            INSERT INTO per_customers (id, nama, status, trash)
            VALUES (1, 'Aktif', 1, 0)
            """
        )
        cur.execute(
            """
            INSERT INTO per_customers (id, nama, status, trash)
            VALUES (2, 'Nonaktif', 0, 0)
            """
        )
        cur.execute(
            """
            INSERT INTO per_customers (id, nama, status, trash)
            VALUES (3, 'Terhapus', 1, 1)
            """
        )
        conn.commit()
        conn.close()

        assert model.count_customers(keyword="") == 1
        rows = model.search_customers(keyword="", limit=50, offset=0)
        assert [str(row.get("nama") or "") for row in rows] == ["Aktif"]