"""Unit tests for the data migration tool (migrate.py).

Focus areas:
- SQL identifier / type whitelist validation (injection hardening)
- Value conversion for every supported field type
- SQL literal formatting with quote escaping (injection hardening)
- Input dispatch by file extension

Run with:
    python -m pytest tests/
"""
from __future__ import annotations

import pathlib
import sys

import pytest

ROOT = pathlib.Path(__file__).resolve().parent.parent
sys.path.insert(0, str(ROOT))

import migrate  # noqa: E402


# --------------------------------------------------------------------------
# Identifier validation
# --------------------------------------------------------------------------
class TestValidateIdentifier:
    @pytest.mark.parametrize("name", ["emp_id", "m_employee", "_private", "A1", "x" * 63])
    def test_valid_identifiers(self, name):
        assert migrate._validate_identifier(name, "列") == name

    @pytest.mark.parametrize(
        "name",
        [
            "1abc",            # starts with a digit
            "emp id",          # contains a space
            "drop;table",      # punctuation / injection
            "users--",         # SQL comment chars
            "",                # empty
            "x" * 64,          # too long
            "テーブル",         # non-ascii
        ],
    )
    def test_invalid_identifiers_raise(self, name):
        with pytest.raises(ValueError):
            migrate._validate_identifier(name, "テーブル")


class TestValidateSqlType:
    @pytest.mark.parametrize(
        "type_str",
        ["VARCHAR(20)", "varchar2(100)", "NUMBER(10)", "NUMERIC(5,2)", "DATE", "boolean", "DOUBLE PRECISION"],
    )
    def test_allowed_types(self, type_str):
        # Returns normalized (uppercased) form without raising.
        assert migrate._validate_sql_type(type_str, "f").isupper()

    @pytest.mark.parametrize(
        "type_str",
        ["DROP TABLE", "VARCHAR(20); DELETE", "FOO", "INT'", ""],
    )
    def test_rejected_types(self, type_str):
        with pytest.raises(ValueError):
            migrate._validate_sql_type(type_str, "f")


# --------------------------------------------------------------------------
# Value conversion
# --------------------------------------------------------------------------
class TestConvertValue:
    def test_required_missing_raises(self):
        with pytest.raises(ValueError):
            migrate.convert_value("", "name", {"type": "str", "required": True})

    def test_optional_missing_returns_default(self):
        assert migrate.convert_value(None, "dept", {"type": "str", "default": "NA"}) == "NA"

    def test_str_max_len_enforced(self):
        with pytest.raises(ValueError):
            migrate.convert_value("abcdef", "code", {"type": "str", "max_len": 3})

    def test_int_strips_thousands_separator(self):
        assert migrate.convert_value("1,234", "n", {"type": "int"}) == 1234

    def test_int_invalid_raises(self):
        with pytest.raises(ValueError):
            migrate.convert_value("12x", "n", {"type": "int"})

    def test_float_conversion(self):
        assert migrate.convert_value("3.5", "r", {"type": "float"}) == 3.5

    @pytest.mark.parametrize(
        "raw,expected",
        [
            ("2024-01-02", "2024-01-02"),
            ("2024/01/02", "2024-01-02"),
            ("2024年01月02日", "2024-01-02"),
        ],
    )
    def test_date_formats_normalized(self, raw, expected):
        assert migrate.convert_value(raw, "d", {"type": "date"}) == expected

    def test_date_invalid_raises(self):
        with pytest.raises(ValueError):
            migrate.convert_value("not-a-date", "d", {"type": "date"})

    @pytest.mark.parametrize("raw,expected", [("はい", True), ("1", True), ("いいえ", False), ("0", False)])
    def test_bool_conversion(self, raw, expected):
        assert migrate.convert_value(raw, "b", {"type": "bool"}) is expected

    def test_bool_invalid_raises(self):
        with pytest.raises(ValueError):
            migrate.convert_value("maybe", "b", {"type": "bool"})


# --------------------------------------------------------------------------
# SQL literal formatting (injection hardening)
# --------------------------------------------------------------------------
class TestFormatSqlValue:
    def test_none_is_null(self):
        assert migrate._format_sql_value(None, "x", "postgresql", {}) == "NULL"

    def test_single_quote_is_escaped(self):
        # O'Brien must not break out of the string literal.
        out = migrate._format_sql_value("O'Brien", "name", "postgresql", {"name": {"type": "str"}})
        assert out == "'O''Brien'"

    def test_injection_attempt_is_neutralised(self):
        evil = "x'); DROP TABLE users;--"
        out = migrate._format_sql_value(evil, "name", "postgresql", {"name": {"type": "str"}})
        # The closing quote is doubled, so the payload stays inside the literal.
        assert out.startswith("'x'')")
        assert out.endswith("'")

    def test_oracle_string_uses_n_prefix(self):
        out = migrate._format_sql_value("山田", "name", "oracle", {"name": {"type": "str"}})
        assert out == "N'山田'"

    def test_bool_dialect_difference(self):
        fm = {"flag": {"type": "bool"}}
        assert migrate._format_sql_value(True, "flag", "oracle", fm) == "1"
        assert migrate._format_sql_value(True, "flag", "postgresql", fm) == "TRUE"

    def test_date_dialect_difference(self):
        fm = {"d": {"type": "date"}}
        assert "TO_DATE(" in migrate._format_sql_value("2024-01-02", "d", "oracle", fm)
        assert "::DATE" in migrate._format_sql_value("2024-01-02", "d", "postgresql", fm)

    def test_numeric_passthrough(self):
        assert migrate._format_sql_value(42, "n", "postgresql", {"n": {"type": "int"}}) == "42"


# --------------------------------------------------------------------------
# Input dispatch
# --------------------------------------------------------------------------
class TestLoadInput:
    def test_unsupported_extension_raises(self, tmp_path):
        p = tmp_path / "data.xyz"
        p.write_text("x", encoding="utf-8")
        with pytest.raises(ValueError):
            migrate.load_input(p)

    def test_json_array(self, tmp_path):
        p = tmp_path / "d.json"
        p.write_text('[{"a": 1}]', encoding="utf-8")
        assert migrate.load_input(p) == [{"a": 1}]

    def test_json_records_wrapper(self, tmp_path):
        p = tmp_path / "d.json"
        p.write_text('{"records": [{"a": 1}]}', encoding="utf-8")
        assert migrate.read_json_input(p) == [{"a": 1}]

    def test_csv_roundtrip(self, tmp_path):
        p = tmp_path / "d.csv"
        p.write_text("a,b\n1,2\n", encoding="utf-8")
        rows = migrate.load_input(p)
        assert rows == [{"a": "1", "b": "2"}]


# --------------------------------------------------------------------------
# transform_record integration
# --------------------------------------------------------------------------
class TestTransformRecord:
    FIELD_MAP = {
        "emp_id": {"source": "社員番号", "type": "str", "required": True},
        "salary": {"source": "給与", "type": "int", "default": 0},
    }

    def test_success(self):
        row = {"社員番号": "E001", "給与": "500000"}
        rec, errors = migrate.transform_record(row, 1, self.FIELD_MAP)
        assert errors == []
        assert rec == {"emp_id": "E001", "salary": 500000}

    def test_missing_required_collects_error(self):
        row = {"社員番号": "", "給与": "100"}
        rec, errors = migrate.transform_record(row, 5, self.FIELD_MAP)
        assert rec is None
        assert len(errors) == 1
        assert "行5" in errors[0]


class TestDemoMode:
    """同梱サンプルで即実行できる --demo の検証（叩けば即結果）。"""

    def test_bundled_sample_files_exist(self):
        assert (ROOT / "sample_employee.csv").is_file()
        assert (ROOT / "mapping_config.json").is_file()

    def test_demo_runs_without_args_and_produces_output(self, tmp_path):
        import json as _json
        import subprocess

        out = tmp_path / "demo.json"
        result = subprocess.run(
            [sys.executable, str(ROOT / "migrate.py"), "--demo", "-o", str(out),
             "-e", str(tmp_path / "demo.log")],
            cwd=str(ROOT),
            capture_output=True,
            text=True,
            encoding="utf-8",
            errors="replace",
        )
        assert result.returncode == 0, result.stderr
        assert out.is_file()
        records = _json.loads(out.read_text(encoding="utf-8"))
        assert isinstance(records, list) and len(records) >= 1


if __name__ == "__main__":
    sys.exit(pytest.main([__file__, "-v"]))
