"""Test SQLite Adapter."""

from __future__ import annotations

from dataclasses import dataclass
from pathlib import Path

import pytest
from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import column, create_engine, text
from sqlalchemy.dialects import sqlite as sqlite_dialect
from sqlalchemy.engine import URL, Engine

import abacus_common_logic.db.adapters.sqlite_adapter as mod
from abacus_common_logic.db.adapters.sqlite_adapter import (
    SQLiteAdapter,
    _normalize_filename,
    _sqlite_path_exists,
)

# Helpers


@dataclass
class Cfg:
    """Config dataclass."""

    SQLITE_DB_NAME: str
    SQLITE_DIR: str
    SQLITE_EXT: str = '.sqlite3'


def _make_file_engine(tmp_path: Path, filename: str) -> Engine:
    return create_engine(
        URL.create('sqlite+pysqlite', database=str((tmp_path / filename).resolve()))
    )  # type: ignore


# Tests


def test_normalize_filename():
    """Test _normalize_filename."""
    assert _normalize_filename(':memory:', '.db') == ':memory:'
    assert _normalize_filename('foo', '.db') == 'foo.db'
    assert _normalize_filename('bar', 'db') == 'bar.db'
    assert _normalize_filename('foobar.DB', '.db').lower().endswith('.db')


def test_sqlite_path_exists(tmp_path: Path):
    """Test _sqlite_path_exists."""
    p = tmp_path / 'x.sqlite3'
    assert not _sqlite_path_exists(str(p))

    # Create a real SQLite file so header validates
    SQLiteAdapter().create_db(Cfg('x', str(tmp_path)))  # type: ignore
    assert _sqlite_path_exists(str(p))
    assert _sqlite_path_exists(str(p), validate_header=True)


def test_build_url_memory_and_file(tmp_path: Path):
    """Test _build_url."""
    a = SQLiteAdapter()

    # Memory
    url, is_mem = a._build_url(Cfg(':memory:', str(tmp_path), '.db'))  # type: ignore
    assert is_mem is True
    assert isinstance(url, URL)
    assert url.database == ':memory:'

    # File
    url, is_mem = a._build_url(Cfg('testdb', str(tmp_path), '.db'))  # type: ignore
    assert is_mem is False
    assert Path(url.database).name == 'testdb.db'


def test_db_exists_and_create_db(tmp_path: Path):
    """Test db_exists and create_db."""
    a = SQLiteAdapter()
    cfg = Cfg('existsme', str(tmp_path), '.db')

    # Not created yet
    assert a.db_exists(cfg) is False  # type: ignore

    # Create the file
    a.create_db(cfg)  # type: ignore

    # File exists
    assert a.db_exists(cfg) is True  # type: ignore
    assert a.db_exists(cfg, validate_header=True) is True  # type: ignore


def test_create_engine_requires_existing_file(tmp_path: Path):
    """Test create_engine."""
    a = SQLiteAdapter()
    cfg = Cfg('nope', str(tmp_path), '.db')
    with pytest.raises(ValueError, match='does not exist'):
        a.create_engine(cfg)  # type: ignore

    # Memory DB should not raise
    mem = Cfg(':memory:', str(tmp_path), '.db')
    eng = a.create_engine(mem)  # type: ignore
    with eng.connect() as c:
        c.execute(text('SELECT 1'))


def test_create_engine_file_ok(tmp_path: Path):
    """Test create_engine."""
    a = SQLiteAdapter()
    cfg = Cfg('ok', str(tmp_path), '.db')
    a.create_db(cfg)  # type: ignore
    eng = a.create_engine(cfg)  # type: ignore
    with eng.connect() as c:
        c.execute(text('SELECT 1'))


def test_set_fk_toggles_foreign_keys():
    """Test set_fk."""
    eng = create_engine(
        'sqlite+pysqlite:///:memory:',
        poolclass=mod.StaticPool,
        connect_args={'check_same_thread': False},
    )
    a = SQLiteAdapter()
    with eng.connect() as conn:  # type: ignore
        # Enable foreign keys
        a.set_fk(conn, True)
        fk_on = conn.exec_driver_sql('PRAGMA foreign_keys').scalar()
        assert int(fk_on) == 1

        # Disables foreign keys
        a.set_fk(conn, False)
        fk_off = conn.exec_driver_sql('PRAGMA foreign_keys').scalar()
        assert int(fk_off) == 0


def test_truncate_tables_deletes_and_resets_sequences(tmp_path: Path):
    """Test truncate_tables."""
    a = SQLiteAdapter()
    cfg = Cfg('seqtest', str(tmp_path), '.db')
    a.create_db(cfg)  # type: ignore
    eng = a.create_engine(cfg)  # type: ignore

    with eng.begin() as conn:
        conn.exec_driver_sql(
            """
            CREATE TABLE t (
              id INTEGER PRIMARY KEY AUTOINCREMENT,
              name TEXT
            )
            """
        )

        # Insert two rows; ids 1,2
        conn.exec_driver_sql("INSERT INTO t (name) VALUES ('a'), ('b')")
        rows = conn.exec_driver_sql('SELECT COUNT(*) FROM t').scalar()
        assert rows == 2

        # Truncate via adapter (DELETE + reset sqlite_sequence)
        a.truncate_tables(conn, ['t'])

        rows = conn.exec_driver_sql('SELECT COUNT(*) FROM t').scalar()
        assert rows == 0

        # Insert again; id should restart at 1 if sequence reset worked
        conn.exec_driver_sql("INSERT INTO t (name) VALUES ('c')")
        last_id = conn.exec_driver_sql('SELECT id FROM t').scalar()
        assert last_id == 1


def test_json_contains_sql_shape_and_params():
    """Test json_contains."""
    expr = SQLiteAdapter().json_contains(column('c'), 'v', path='$.x')
    comp = expr.compile(dialect=sqlite_dialect.dialect())

    sql_txt = str(comp).lower()
    assert 'json_each' in sql_txt
    assert 'exists' in sql_txt
    vals = list(comp.params.values())
    assert '$.x' in vals
    assert 'v' in vals


def test_clone_db(tmp_path: Path):
    """Test clone_db."""
    a = SQLiteAdapter()

    # Source DB with a table + data
    src = _make_file_engine(tmp_path, 'src.sqlite3')
    with src.begin() as c:
        c.exec_driver_sql('CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)')
        c.exec_driver_sql("INSERT INTO t (id, name) VALUES (1, 'alice')")

    # Destination DB (empty file)
    dst = _make_file_engine(tmp_path, 'dst.sqlite3')
    with dst.begin() as c:
        c.exec_driver_sql(
            'CREATE TABLE IF NOT EXISTS t (id INTEGER PRIMARY KEY, name TEXT)'
        )
        c.exec_driver_sql('DELETE FROM t')

    # Clone
    a.clone_db(src, dst)

    # Verify data in destination DB
    with dst.connect() as c:
        row = c.exec_driver_sql('SELECT id, name FROM t').first()
        assert row == (1, 'alice')


def test_clone_db_same_database_raises(tmp_path: Path):
    """Test clone_db with same dbs."""
    a = SQLiteAdapter()
    eng = _make_file_engine(tmp_path, 'same.sqlite3')
    with pytest.raises(ValueError, match='same database'):
        a.clone_db(eng, eng)


def test_clone_db_non_sqlite_engines_raise(tmp_path: Path, monkeypatch):
    """Test clone_db with non-sqlite db."""
    a = SQLiteAdapter()
    src = _make_file_engine(tmp_path, 's.sqlite3')
    dst = _make_file_engine(tmp_path, 'd.sqlite3')

    # Test sqlite -> non-sqlite
    monkeypatch.setattr(src, 'dialect', type('D', (), {'name': 'sqlite'})())
    monkeypatch.setattr(dst, 'dialect', type('D', (), {'name': 'mysql'})())
    with pytest.raises(ValueError, match='Only SQLite engines'):
        a.clone_db(src, dst)

    # Test Non-sqlite -> sqlite
    class _D:
        pass

    monkeypatch.setattr(src, 'dialect', type('D', (), {'name': 'postgresql'})())
    monkeypatch.setattr(dst, 'dialect', type('D', (), {'name': 'sqlite'})())
    with pytest.raises(ValueError, match='Only SQLite engines'):
        a.clone_db(src, dst)


def test_setup_db_sets_flask_config(tmp_path: Path):
    """Test setup_db."""
    app = Flask(__name__)
    db = SQLAlchemy()

    a = SQLiteAdapter()
    cfg = Cfg(':memory:', str(tmp_path), '.db')
    a.setup_db(db, app, cfg)  # type: ignore

    assert app.config['SQLALCHEMY_DATABASE_URI'].endswith(':memory:')
    assert app.config['SQLALCHEMY_ENGINE_OPTIONS']['pool_pre_ping'] is True
    opts = app.config['SQLALCHEMY_ENGINE_OPTIONS']
    assert opts['poolclass'].__name__ == 'StaticPool'
    assert opts['connect_args']['check_same_thread'] is False