import os
from pathlib import Path
from typing import Any

import tempfile
import sqlite3

import pytest

from mloda.user import DataAccessCollection
from mloda.user import Feature
from mloda.provider import HashableDict
from mloda.user import Options
from mloda.user import PluginCollector
from mloda.user import mloda
from mloda_plugins.feature_group.input_data.read_db import ReadDB
from mloda_plugins.feature_group.input_data.read_dbs.sqlite import SQLITEReader
from tests.test_plugins.feature_group.input_data.test_classes.test_input_classes import DBInputDataTestFeatureGroup
from tests.test_core.test_integration.test_core.test_runner_one_compute_framework import SumFeature


class TestInputDataDB:
    def setup_method(self) -> None:
        # Create a temporary file to act as the SQLite database
        self.db_fd, self.db_path = tempfile.mkstemp(suffix=".sqlite")
        # Initialize the SQLite database with a sample table
        self.conn = sqlite3.connect(self.db_path)
        self.cursor = self.conn.cursor()
        self.cursor.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, name TEXT)")
        self.cursor.execute('INSERT INTO test_table (name) VALUES ("Alice")')
        self.cursor.execute('INSERT INTO test_table (name) VALUES ("Bob")')

        self.cursor.execute("CREATE TABLE test_table_2 (id INTEGER PRIMARY KEY, name TEXT)")

        self.conn.commit()

    def teardown_method(self) -> None:
        self.conn.close()
        os.close(self.db_fd)
        os.remove(self.db_path)

    def test_load_csv_local_feature_scope_data_access_with_a_concrete_file(self) -> Any:
        f = Feature(
            name="id",
            options={
                SQLITEReader.__name__: HashableDict({SQLITEReader.db_path(): self.db_path, "table_name": "test_table"})
            },
        )

        result = mloda.run_all(
            [f],
            compute_frameworks=["PyArrowTable"],
            plugin_collector=PluginCollector.enabled_feature_groups({DBInputDataTestFeatureGroup}),
        )
        assert "id" in result[0].to_pydict()

    def test_load_sqlite_found_in_data_access_collection(self) -> Any:
        result = mloda.run_all(
            ["name", "id"],
            compute_frameworks=["PyArrowTable"],
            data_access_collection=DataAccessCollection(credential_dicts={SQLITEReader.db_path(): self.db_path}),
            plugin_collector=PluginCollector.enabled_feature_groups({DBInputDataTestFeatureGroup}),
        )
        assert "name" in result[0].to_pydict()

    def test_aggr_load_sqlite_found_in_data_access_collection(self) -> Any:
        f = Feature(
            name="sum_of_",
            options={"sum": ("id", "id")},
        )

        result = mloda.run_all(
            [f],
            compute_frameworks=["PyArrowTable"],
            data_access_collection=DataAccessCollection(credential_dicts={SQLITEReader.db_path(): self.db_path}),
            plugin_collector=PluginCollector.enabled_feature_groups({DBInputDataTestFeatureGroup, SumFeature}),
        )
        assert "SumFeature_idid" in result[0].to_pydict()


class TestSqliteConnectionLifecycle:
    """Regression coverage for #424: ReadDB.read_db must close every
    connection returned by get_connection. Otherwise on Windows the
    underlying SQLite file stays locked until the process exits.
    """

    def test_run_all_closes_every_sqlite_connection(self, tmp_path: Path, monkeypatch: pytest.MonkeyPatch) -> None:
        db_path = tmp_path / "lifecycle.sqlite"
        seed = sqlite3.connect(db_path)
        seed.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, name TEXT)")
        seed.execute('INSERT INTO test_table (name) VALUES ("Alice")')
        seed.commit()
        seed.close()

        opened: list[sqlite3.Connection] = []
        original_connect = SQLITEReader.connect

        def tracking_connect(credentials: Any) -> Any:
            conn = original_connect(credentials)
            opened.append(conn)
            return conn

        monkeypatch.setattr(SQLITEReader, "connect", tracking_connect)

        try:
            mloda.run_all(
                ["name", "id"],
                compute_frameworks=["PyArrowTable"],
                data_access_collection=DataAccessCollection(credential_dicts={SQLITEReader.db_path(): str(db_path)}),
                plugin_collector=PluginCollector.enabled_feature_groups({DBInputDataTestFeatureGroup}),
            )

            assert opened, "expected SQLITEReader.connect to be invoked at least once"
            for conn in opened:
                with pytest.raises(sqlite3.ProgrammingError):
                    conn.execute("SELECT 1")
        finally:
            for conn in opened:
                conn.close()


class TestReadDB:
    def setup_method(self) -> None:
        # Create a temporary file to act as the SQLite database
        self.db_fd, self.db_path = tempfile.mkstemp(suffix=".sqlite")
        # Initialize the SQLite database with a sample table
        self.conn = sqlite3.connect(self.db_path)
        self.cursor = self.conn.cursor()
        self.cursor.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, name TEXT)")
        self.cursor.execute('INSERT INTO test_table (name) VALUES ("Alice")')
        self.cursor.execute('INSERT INTO test_table (name) VALUES ("Bob")')
        self.conn.commit()

    def teardown_method(self) -> None:
        self.conn.close()
        os.close(self.db_fd)
        os.remove(self.db_path)

    def test_load_data_not_implemented(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.load_data(None, None)  # type: ignore

    def test_connect_not_implemented(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.connect(None)

    def test_build_query_not_implemented(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.build_query(None)  # type: ignore

    def test_is_valid_credentials_not_implemented(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.is_valid_credentials({})

    def test_check_feature_in_data_access_not_implemented(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.check_feature_in_data_access("", None)

    def test_init_reader_no_options(self) -> None:
        read_db = ReadDB()
        with pytest.raises(ValueError):
            read_db.init_reader(None)

    def test_init_reader_no_data_access(self) -> None:
        read_db = ReadDB()
        options = Options()
        with pytest.raises(ValueError):
            read_db.init_reader(options)

    def test_match_subclass_data_access(self) -> None:
        data_access = DataAccessCollection(credential_dicts={SQLITEReader.db_path(): self.db_path})
        feature_names = ["name"]
        result = ReadDB.match_subclass_data_access(data_access, feature_names, options=Options({}))
        assert not result

    def test_get_connection_no_credentials(self) -> None:
        with pytest.raises(NotImplementedError):
            ReadDB.get_connection(None)

    def test_init_reader_none_options_message(self) -> None:
        """When init_reader is called with None, the error should mention the class name.

        Currently the message is a generic 'Options were not set.' without indicating
        which ReadDB subclass encountered the problem.
        """

        class CustomReadDB(ReadDB):
            @classmethod
            def connect(cls, credentials: Any) -> Any:
                return None

        reader = CustomReadDB()
        with pytest.raises(ValueError, match=r"CustomReadDB"):
            reader.init_reader(None)

    def test_init_reader_missing_base_input_data_message(self) -> None:
        """When options lack BaseInputData key, the error should mention 'BaseInputData'.

        Currently the message is a generic 'Reader data access was not set.' without
        telling the user which key is missing from options.
        """
        read_db = ReadDB()
        with pytest.raises(ValueError, match=r"BaseInputData"):
            read_db.init_reader(Options())

    def test_match_read_db_data_access_multiple_features_message(self) -> None:
        """When match_read_db_data_access receives multiple feature names, the error
        should mention the number of feature names received.

        Currently the message is just 'This should not happen.' which gives no context
        about the actual problem or how many feature names were passed.
        """
        with pytest.raises(ValueError, match=r"2"):
            ReadDB.match_read_db_data_access([], ["feat_a", "feat_b"])

    def test_get_connection_returns_none_message(self) -> None:
        """When connect() returns None, the error should mention the subclass name.

        Currently the message is a generic 'Connection to database failed.' without
        indicating which ReadDB subclass failed to connect.
        """

        class NoneConnectDB(ReadDB):
            @classmethod
            def connect(cls, credentials: Any) -> None:
                return None

        with pytest.raises(ValueError, match=r"NoneConnectDB"):
            NoneConnectDB.get_connection("dummy")

    def test_read_db_success(self) -> None:
        class MockReadDB(ReadDB):
            @classmethod
            def connect(cls, credentials: Any) -> Any:
                return sqlite3.connect(credentials[SQLITEReader.db_path()])

            @classmethod
            def is_valid_credentials(cls, credentials: dict[str, Any]) -> bool:
                return SQLITEReader.db_path() in credentials

            @classmethod
            def check_feature_in_data_access(cls, feature_name: str, data_access: Any) -> bool:
                return True

        credentials = {SQLITEReader.db_path(): self.db_path}
        query = "SELECT * FROM test_table"
        result, column_names = MockReadDB.read_db(credentials, query)
        assert len(result) == 2
        assert column_names == ["id", "name"]
