"""Tests for Delphi data source."""

import sqlite3
from unittest.mock import MagicMock, patch

import pytest
from snowflake.connector import SnowflakeConnection

from gridgen.connectors import helper, snowflake_connector
from gridgen.exceptions import ReleaseNotFound, TrackNotFound
from gridgen.models import metadata
from gridgen.models.datasource import delphi

TEST_PRODUCT_GRID = (
    ("1234567890", "A10101A0001", "G01000001R", "Y", "U"),
    ("2345678901", "A10101A0002", "G01000002G", "N", "U"),
)

TEST_TRACK_GRID = (
    (101, "03", "ABC12345", "Track 1", "2025 PLine", 180, 1, "U"),
    (102, "04", "DEF67890", "Track 2", "2025 PLine", 170, 1, "U"),
    (103, "05", "GHI12345", "Track 3", "2025 PLine", 150, 1, "U"),
)

TEST_TRACK_PRODUCT_GRID = (("G01000001R", 101, "03", "U"),)

delphi.QUERY_PRODUCT_GRID = delphi.QUERY_PRODUCT_GRID.replace("%s", "?")
delphi.QUERY_TRACK_GRID = delphi.QUERY_TRACK_GRID.replace("%s", "?")


def create_temp_gras_product_tables(db_conn: SnowflakeConnection, srno: int) -> None:
    """Create temporary tables to query test product grid data.

    Args:
        db_conn (SnowflakeConnection): open database connection.
        srno (int)
    """
    product_grid_query = "INSERT INTO v_gras_product VALUES (?, ?, ?, ?, ?)"
    helper.execute_query(
        db_conn,
        """
        CREATE TABLE v_gras_product (
            barcode TEXT, grid_no TEXT,
            prod_no TEXT, is_complete TEXT, mod_flag TEXT
        )
        """,
    )
    helper.execute_query(db_conn, product_grid_query, TEST_PRODUCT_GRID[srno])


def create_temp_gras_track_tables(db_conn: SnowflakeConnection) -> None:
    """Create temporary tables to query test track grid data.

    Args:
        db_conn (SnowflakeConnection): open database connection.
    """
    create_temp_gras_product_tables(db_conn, 0)
    helper.execute_query(
        db_conn,
        """
        CREATE TABLE v_gras_track (
            track_no INTEGER, track_ext TEXT,
            isrc TEXT,
            track_name TEXT,
            p_line TEXT,
            play_time INTEGER,
            track_media_key INTEGER,
            mod_flag TEXT
        )
        """,
    )
    track_grid_query = "INSERT INTO v_gras_track VALUES (?, ?, ?, ?, ?, ?, ?, ?)"
    helper.execute_query(db_conn, track_grid_query, TEST_TRACK_GRID[0])
    helper.execute_query(
        db_conn,
        """
        CREATE TABLE v_gras_product_track (
            prod_no TEXT, track_no INTEGER,
            track_ext TEXT, mod_flag TEXT
        )
        """,
    )
    track_product_grid_query = "INSERT INTO v_gras_product_track VALUES (?, ?, ?, ?)"
    helper.execute_query(db_conn, track_product_grid_query, TEST_TRACK_PRODUCT_GRID[0])


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_product_grid(mock_obj: MagicMock) -> None:
    """Test lookup_product_grid identifiers."""
    create_temp_gras_product_tables(snowflake_connector.get_connection(), 0)
    result = delphi.lookup_product_grid("1234567890")
    assert result == (
        TEST_PRODUCT_GRID[0][0],
        TEST_PRODUCT_GRID[0][1],
        TEST_PRODUCT_GRID[0][2],
        TEST_PRODUCT_GRID[0][3],
    )


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_track_grid(mock_obj: MagicMock) -> None:
    """Test lookup_track_grid identifiers."""
    create_temp_gras_track_tables(snowflake_connector.get_connection())
    result = delphi.lookup_track_grid("ABC12345")
    assert result == (
        TEST_TRACK_GRID[0][2],
        TEST_TRACK_GRID[0][3],
        TEST_TRACK_GRID[0][4],
        TEST_TRACK_GRID[0][5],
        TEST_PRODUCT_GRID[0][1],
        TEST_PRODUCT_GRID[0][2],
    )


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_release(mock_obj: MagicMock) -> None:
    """Test lookup_release."""
    create_temp_gras_product_tables(snowflake_connector.get_connection(), 0)
    result = delphi.lookup_release(metadata.ReleaseIdMeta(1001, "1234567890"))
    assert result == metadata.ReleaseMeta(
        None,
        TEST_PRODUCT_GRID[0][0],
        TEST_PRODUCT_GRID[0][1],
        TEST_PRODUCT_GRID[0][2],
        None,
        None,
        None,
        None,
        None,
        "complete",
        None,
    )


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_release_incomplete(mock_obj: MagicMock) -> None:
    """Test lookup_release."""
    create_temp_gras_product_tables(snowflake_connector.get_connection(), 1)
    result = delphi.lookup_release(metadata.ReleaseIdMeta(1002, "2345678901"))
    assert result == metadata.ReleaseMeta(
        None,
        TEST_PRODUCT_GRID[1][0],
        TEST_PRODUCT_GRID[1][1],
        TEST_PRODUCT_GRID[1][2],
        None,
        None,
        None,
        None,
        None,
        "incomplete",
        None,
    )


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_track(mock_obj: MagicMock) -> None:
    """Test lookup_track."""
    create_temp_gras_track_tables(snowflake_connector.get_connection())
    result = delphi.lookup_track(metadata.TrackIdMeta(20001, "ABC12345", "1234567890"))
    assert result == metadata.TrackMeta(
        None,
        None,
        TEST_TRACK_GRID[0][2],
        TEST_TRACK_GRID[0][3],
        "",
        TEST_TRACK_GRID[0][4],
        "",
        TEST_TRACK_GRID[0][5],
        set(),
        {metadata.GridMeta(TEST_PRODUCT_GRID[0][1], TEST_PRODUCT_GRID[0][2])},
    )


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_release_not_found(mock_obj: MagicMock) -> None:
    """Test lookup_release."""
    create_temp_gras_product_tables(snowflake_connector.get_connection(), 0)

    with pytest.raises(ReleaseNotFound) as exc:
        delphi.lookup_release(metadata.ReleaseIdMeta(2001, "1234567891"))

    assert exc.value.description == "GRAS product 1234567891 not found"


@patch("snowflake.connector.connect", return_value=sqlite3.connect(":memory:"))
def test_lookup_track_not_found(mock_obj: MagicMock) -> None:
    """Test lookup_track."""
    create_temp_gras_track_tables(snowflake_connector.get_connection())

    with pytest.raises(TrackNotFound) as exc:
        delphi.lookup_track(metadata.TrackIdMeta(10001, "BCD12345", "1234567891"))

    assert exc.value.description == "GRAS track BCD12345 not found"