"""Tests for Excel import data type inference correctness."""

import pytest
import sys
from pathlib import Path

import numpy as np
import pandas as pd

sys.path.insert(0, str(Path(__file__).resolve().parent.parent / "backend"))

from sync.excel_import import (
    infer_column_type,
    InferredColumn,
    _try_integer,
    _try_float,
    _try_boolean,
    _try_date,
    _has_leading_zeros,
)


class TestIntegerInference:
    def test_pure_integers(self):
        s = pd.Series([1, 2, 3, 100, -50], name="col")
        result = infer_column_type(s)
        assert "INT" in result.pg_type.upper()

    def test_integers_as_strings(self):
        s = pd.Series(["1", "2", "3", "100"], name="col")
        result = infer_column_type(s)
        assert "INT" in result.pg_type.upper() or result.pg_type == "SMALLINT"

    def test_small_integers(self):
        s = pd.Series([1, 2, 3, 100], name="col")
        result = infer_column_type(s)
        assert result.pg_type in ("SMALLINT", "INTEGER")

    def test_large_integers(self):
        s = pd.Series([1000000000, 2000000000, 3000000000], name="col")
        result = infer_column_type(s)
        assert result.pg_type in ("INTEGER", "BIGINT")

    def test_integers_with_nulls(self):
        s = pd.Series([1, None, 3, None, 5], name="col")
        result = infer_column_type(s)
        assert result.nullable == True


class TestFloatInference:
    def test_decimal_numbers(self):
        s = pd.Series([1.5, 2.3, 3.7, 100.99], name="col")
        result = infer_column_type(s)
        assert "NUMERIC" in result.pg_type or "DOUBLE" in result.pg_type

    def test_monetary_values(self):
        s = pd.Series(["123.45", "678.90", "0.01", "999.99"], name="col")
        result = infer_column_type(s)
        assert "NUMERIC" in result.pg_type

    def test_high_precision_floats(self):
        s = pd.Series([3.14159265, 2.71828182, 1.41421356], name="col")
        result = infer_column_type(s)
        assert "DOUBLE" in result.pg_type or "NUMERIC" in result.pg_type


class TestBooleanInference:
    def test_true_false(self):
        s = pd.Series(["True", "False", "True", "False"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "BOOLEAN"

    def test_yes_no(self):
        s = pd.Series(["Yes", "No", "Yes", "No"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "BOOLEAN"

    def test_y_n(self):
        s = pd.Series(["Y", "N", "Y", "N", "Y"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "BOOLEAN"

    def test_one_zero(self):
        s = pd.Series(["1", "0", "1", "0"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "BOOLEAN"

    def test_german_booleans(self):
        s = pd.Series(["Ja", "Nein", "Ja", "Nein"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "BOOLEAN"


class TestDateInference:
    def test_iso_dates(self):
        s = pd.Series(["2024-01-15", "2024-02-20", "2024-03-25"], name="col")
        result = infer_column_type(s)
        assert result.pg_type in ("DATE", "TIMESTAMP")

    def test_eu_dates(self):
        s = pd.Series(["15.01.2024", "20.02.2024", "25.03.2024"], name="col")
        result = infer_column_type(s)
        assert result.pg_type in ("DATE", "TIMESTAMP")

    def test_us_dates(self):
        s = pd.Series(["01/15/2024", "02/20/2024", "03/25/2024"], name="col")
        result = infer_column_type(s)
        assert result.pg_type in ("DATE", "TIMESTAMP")

    def test_iso_datetime(self):
        s = pd.Series(["2024-01-15 10:30:00", "2024-02-20 14:45:00"], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "TIMESTAMP"


class TestLeadingZeros:
    def test_detects_leading_zeros(self):
        s = pd.Series(["001234", "005678", "009012"])
        assert _has_leading_zeros(s) is True

    def test_no_leading_zeros(self):
        s = pd.Series(["1234", "5678", "9012"])
        assert _has_leading_zeros(s) is False

    def test_codes_stay_as_text(self):
        s = pd.Series(["001234", "005678", "009012"], name="product_code")
        result = infer_column_type(s)
        assert "TEXT" in result.pg_type or "VARCHAR" in result.pg_type


class TestTextInference:
    def test_short_text(self):
        s = pd.Series(["hello", "world", "foo", "bar"], name="col")
        result = infer_column_type(s)
        assert "VARCHAR" in result.pg_type or "TEXT" in result.pg_type

    def test_long_text(self):
        s = pd.Series(["a" * 300, "b" * 400], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "TEXT"


class TestMixedTypes:
    def test_mixed_numbers_and_text(self):
        s = pd.Series(["123", "abc", "456", "def"], name="col")
        result = infer_column_type(s)
        # Should fall back to TEXT since it can't be purely numeric
        assert "TEXT" in result.pg_type or "VARCHAR" in result.pg_type

    def test_all_null(self):
        s = pd.Series([None, None, None], name="col")
        result = infer_column_type(s)
        assert result.pg_type == "TEXT"


class TestEdgeCases:
    def test_empty_series(self):
        s = pd.Series([], dtype=object, name="col")
        result = infer_column_type(s)
        assert result.pg_type == "TEXT"

    def test_single_value(self):
        s = pd.Series([42], name="col")
        result = infer_column_type(s)
        assert "INT" in result.pg_type.upper()
