import pytest
from unittest.mock import patch, MagicMock

from database.view_manager import (
    extract_credit_file_id,
    match_view,
    match_all_views,
    has_database_signals,
    build_query,
    execute_view_query,
    format_response,
    query_and_format,
    VIEW_REGISTRY,
    GENERAL_DB_KEYWORDS,
)


class TestExtractCreditFileId:

    def test_extracts_five_digit_id(self):
        assert extract_credit_file_id("12345 teklif") == "12345"

    def test_extracts_eight_digit_id(self):
        assert extract_credit_file_id("26000046 numaralı teklifin statüsü") == "26000046"

    def test_returns_none_when_no_id(self):
        assert extract_credit_file_id("teklif statüsü nedir") is None

    def test_ignores_short_numbers(self):
        assert extract_credit_file_id("1234 teklif") is None

    def test_extracts_first_match(self):
        assert extract_credit_file_id("26000046 ve 99999999") == "26000046"


class TestMatchView:

    def test_status_keywords(self):
        assert match_view("teklifin statüsü nedir") == "v_CurrentCreditFileStatus"
        assert match_view("teklif durum bilgisi") == "v_CurrentCreditFileStatus"

    def test_payment_keywords(self):
        assert match_view("ödeme onayı var mı") == "v_CurrentPaymentApprove"
        assert match_view("odeme onay durumu") == "v_CurrentPaymentApprove"

    def test_downpayment_keywords(self):
        assert match_view("peşinat durumu") == "v_CurrentDownPaymentStatus"
        assert match_view("pesinat bilgisi") == "v_CurrentDownPaymentStatus"

    def test_decision_keywords(self):
        assert match_view("karar sonucu ne") == "v_CurrentDecisionResult"
        assert match_view("decision bilgisi") == "v_CurrentDecisionResult"

    def test_no_match(self):
        assert match_view("hava durumu nasıl") is None

    def test_case_insensitive(self):
        assert match_view("STATÜ bilgisi") == "v_CurrentCreditFileStatus"


class TestMatchAllViews:

    def test_single_match(self):
        result = match_all_views("ödeme onayı")
        assert "v_CurrentPaymentApprove" in result
        assert len(result) == 1

    def test_multiple_matches(self):
        result = match_all_views("statü ve karar sonucu")
        assert "v_CurrentCreditFileStatus" in result
        assert "v_CurrentDecisionResult" in result

    def test_no_match(self):
        assert match_all_views("merhaba") == []


class TestHasDatabaseSignals:

    def test_id_plus_keyword(self):
        assert has_database_signals("26000046 numaralı teklifin statüsü") is True

    def test_id_plus_general_keyword(self):
        assert has_database_signals("26000046 teklif bilgisi") is True

    def test_no_id(self):
        assert has_database_signals("teklif statüsü nedir") is False

    def test_id_without_keyword(self):
        assert has_database_signals("26000046 numaralı kayıt") is False

    def test_payment_keyword(self):
        assert has_database_signals("26000123 ödeme onayı") is True

    def test_decision_keyword(self):
        assert has_database_signals("26000999 karar sonucu") is True


class TestBuildQuery:

    def test_simple_query(self):
        sql = build_query("v_CurrentCreditFileStatus", "26000046")
        assert sql == "SELECT * FROM v_CurrentCreditFileStatus WHERE CreditFileId = 26000046"


class TestExecuteViewQuery:

    @patch("database.view_manager.get_database_adapter")
    def test_success(self, mock_get_adapter):
        adapter = MagicMock()
        adapter.is_connected = True
        adapter.execute_query.return_value = [{"CreditFileId": 26000046, "StatusName": "Teklif"}]
        mock_get_adapter.return_value = adapter

        result = execute_view_query("v_CurrentCreditFileStatus", "26000046")
        assert result["success"] is True
        assert len(result["rows"]) == 1

    @patch("database.view_manager.get_database_adapter")
    def test_no_adapter(self, mock_get_adapter):
        mock_get_adapter.return_value = None
        result = execute_view_query("v_CurrentCreditFileStatus", "26000046")
        assert result["success"] is False
        assert "not connected" in result["error"]

    @patch("database.view_manager.get_database_adapter")
    def test_adapter_not_connected(self, mock_get_adapter):
        adapter = MagicMock()
        adapter.is_connected = False
        mock_get_adapter.return_value = adapter
        result = execute_view_query("v_CurrentCreditFileStatus", "26000046")
        assert result["success"] is False

    @patch("database.view_manager.get_database_adapter")
    def test_query_exception(self, mock_get_adapter):
        adapter = MagicMock()
        adapter.is_connected = True
        adapter.execute_query.side_effect = Exception("db error")
        mock_get_adapter.return_value = adapter

        result = execute_view_query("v_CurrentCreditFileStatus", "26000046")
        assert result["success"] is False
        assert "db error" in result["error"]


class TestFormatResponse:

    def test_status_with_data(self):
        rows = [{"CreditFileId": 26000046, "StatusName": "Sözleşme ve Ekleri Hazır"}]
        resp = format_response("v_CurrentCreditFileStatus", rows, "26000046")
        assert "Sözleşme ve Ekleri Hazır" in resp
        assert "26000046" in resp

    def test_no_rows(self):
        resp = format_response("v_CurrentCreditFileStatus", [], "26000046")
        assert "bulunamadı" in resp
        assert "26000046" in resp

    def test_no_rows_no_id(self):
        resp = format_response("v_CurrentCreditFileStatus", [], None)
        assert "bulunamadı" in resp

    def test_payment_approved(self):
        rows = [{"CreditFileId": 1, "PaymentApprove": "1"}]
        resp = format_response("v_CurrentPaymentApprove", rows, "12345")
        assert "Verildi" in resp

    def test_payment_not_approved(self):
        rows = [{"CreditFileId": 1, "PaymentApprove": "0"}]
        resp = format_response("v_CurrentPaymentApprove", rows, "12345")
        assert "Henüz" in resp

    def test_decision_result(self):
        rows = [{"CreditFileId": 1, "DecisionResultName": "Onay"}]
        resp = format_response("v_CurrentDecisionResult", rows, "12345")
        assert "Onay" in resp

    def test_downpayment_with_pesinat_durumu(self):
        rows = [{"CreditFileId": 1, "PesinatDurumu": "Peşinat tamamlandı", "IsReceivedSwtDownpayment": "1"}]
        resp = format_response("v_CurrentDownPaymentStatus", rows, "12345")
        assert "tamamlandı" in resp

    def test_unknown_view_fallback_format(self):
        rows = [{"Col1": "val1", "Col2": "val2"}]
        resp = format_response("v_SomeNewView", rows, "12345")
        assert "Col1" in resp or "val1" in resp


class TestQueryAndFormat:

    @patch("database.view_manager.execute_view_query")
    def test_specific_view_match(self, mock_exec):
        mock_exec.return_value = {
            "success": True,
            "sql": "SELECT * FROM v_CurrentCreditFileStatus WHERE CreditFileId = 26000046",
            "rows": [{"CreditFileId": 26000046, "StatusName": "Teklif"}],
            "error": None,
        }
        result = query_and_format("26000046 statüsü nedir")
        assert result["success"] is True
        assert "Teklif" in result["response"]
        mock_exec.assert_called_once_with("v_CurrentCreditFileStatus", "26000046")

    def test_no_credit_file_id(self):
        result = query_and_format("teklif nedir")
        assert result["success"] is False
        assert "çıkarılamadı" in result["response"]

    @patch("database.view_manager.execute_view_query")
    def test_no_view_match_queries_all(self, mock_exec):
        mock_exec.return_value = {"success": True, "sql": "SELECT ...", "rows": [], "error": None}
        result = query_and_format("26000046 teklif bilgisi")
        assert mock_exec.call_count == len(VIEW_REGISTRY)

    @patch("database.view_manager.execute_view_query")
    def test_explicit_credit_file_id_parameter(self, mock_exec):
        mock_exec.return_value = {
            "success": True,
            "sql": "SELECT ...",
            "rows": [{"CreditFileId": 99999, "StatusName": "Test"}],
            "error": None,
        }
        result = query_and_format("statüsü nedir", credit_file_id="99999")
        assert result["success"] is True
        mock_exec.assert_called_once_with("v_CurrentCreditFileStatus", "99999")


class TestViewRegistryExtensibility:

    def test_registry_has_four_views(self):
        assert len(VIEW_REGISTRY) == 4

    def test_each_view_has_required_keys(self):
        for name, meta in VIEW_REGISTRY.items():
            assert "keywords" in meta, f"{name} missing keywords"
            assert "display_field" in meta, f"{name} missing display_field"
            assert "label" in meta, f"{name} missing label"
            assert "format" in meta, f"{name} missing format"
            assert callable(meta["format"]), f"{name} format not callable"

    def test_general_keywords_exist(self):
        assert len(GENERAL_DB_KEYWORDS) > 0