import uuid
from collections.abc import Generator
from typing import Any

import pytest
from aws_testing_utils.lambda_handler import LambdaHandler
from test_fixtures.mysql import MySQLConnection

from config import EXECUTE_CONTENT_TRANSFER_FUNCTION_NAME

_ORIGINATING_VENDOR_ID = 6971
_DESTINATION_VENDOR_ID = 7123


def _placeholders(values: list[Any]) -> str:
    """Build a comma-separated `%s` string for a SQL IN clause of the given length."""
    return ",".join(["%s"] * len(values))


def _discover_single_pk(db: MySQLConnection, table_name: str) -> str:
    """Return the primary-key column name for a single-column-PK table.

    Used so the fixture doesn't have to hardcode PK names for tables whose schema
    isn't defined in any repo we read (notably `product_video`). Safe to interpolate
    the returned name into SQL because the value comes from INFORMATION_SCHEMA,
    not from test input.
    """
    rows = db.fetchall(
        """
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_KEY = 'PRI'
        ORDER BY ORDINAL_POSITION
        """,
        (table_name,),
    )
    pk_cols = [r["COLUMN_NAME"] for r in rows]
    assert len(pk_cols) == 1, f"Expected single-column primary key on {table_name}, got: {pk_cols}"
    return str(pk_cols[0])


@pytest.fixture
def transferable_project_setup(
    art_relations_db: MySQLConnection,
) -> Generator[dict[str, Any], None, None]:
    """Seed a transfer job ready for execute-content-transfer to consume.

    The lambda's downstream (ows-project-manager) mutates project, releases,
    product_video, release_artist, track_artist, and track_writer in a single
    transaction. We capture the pre-state of the first four — by primary key —
    and restore them on teardown so the shared QA art_relations DB stays usable
    across runs. (track_artist and track_writer accept some drift; restoring
    them safely would require subqueries through track that are fragile under
    concurrent modifications.)

    Picks a destination_artist_id at the destination vendor that's not already a
    contributor on any of the project's releases, so the assertion `artist_id =
    destination_artist_id` proves a real mutation (rather than coincidentally
    matching a pre-existing value).

    destination_subaccount_id is left NULL so the test also exercises PM's
    fallback path (project.subaccount_id = 0, releases.subaccount_id = NULL).
    """
    project = art_relations_db.fetchone(
        """
        SELECT project_id, vendor_id, subaccount_id, artist_id
        FROM project
        WHERE vendor_id = %s
          AND artist_id IS NOT NULL
          AND (SELECT COUNT(*) FROM releases WHERE project_id = project.project_id) BETWEEN 2 AND 5
        LIMIT 1
        """,
        (_ORIGINATING_VENDOR_ID,),
    )
    assert project, (
        f"No transferable project found for vendor_id {_ORIGINATING_VENDOR_ID} (need 2-5 releases and artist_id set)"
    )

    releases_original = art_relations_db.fetchall(
        "SELECT release_id, artist_id, subaccount_id FROM releases WHERE project_id = %s",
        (project["project_id"],),
    )
    assert releases_original, f"Project {project['project_id']} unexpectedly has no releases"
    release_ids = [r["release_id"] for r in releases_original]

    product_video_pk = _discover_single_pk(art_relations_db, "product_video")
    video_originals = art_relations_db.fetchall(
        f"SELECT {product_video_pk} AS pk, release_id, primary_artist_id "
        f"FROM product_video WHERE release_id IN ({_placeholders(release_ids)})",
        tuple(release_ids),
    )

    release_artist_originals = art_relations_db.fetchall(
        f"SELECT release_artist_id, release_id, artist_info_id "
        f"FROM release_artist WHERE release_id IN ({_placeholders(release_ids)})",
        tuple(release_ids),
    )

    # Destination artist must not already be a contributor on these releases —
    # otherwise `artist_id == destination_artist_id` post-condition is trivially true.
    excluded = {ra["artist_info_id"] for ra in release_artist_originals if ra["artist_info_id"]}
    excluded.add(project["artist_id"])
    excl_list = list(excluded)
    destination_artist_row = art_relations_db.fetchone(
        f"SELECT artist_id FROM artist_info "
        f"WHERE vendor_id = %s AND artist_id NOT IN ({_placeholders(excl_list)}) LIMIT 1",
        (_DESTINATION_VENDOR_ID, *excl_list),
    )
    assert destination_artist_row, (
        f"No destination artist available at vendor_id {_DESTINATION_VENDOR_ID} "
        f"after excluding {len(excl_list)} pre-existing contributors on the chosen project"
    )
    destination_artist_id = destination_artist_row["artist_id"]

    # Match the existing fixture pattern: only insert the columns that don't have
    # FK constraints we'd need to satisfy with bespoke seed data. project.subaccount_id
    # is often 0 (= "no subaccount") which is not a valid FK target, so leaving
    # originating_subaccount_id NULL avoids spurious integrity errors. The job's
    # destination_artist_id is also left NULL — PM falls back to products[0]
    # destination_artist_id, which we seed into product_transfer_history.
    created_by = str(uuid.uuid4())
    art_relations_db.execute(
        """
        INSERT INTO project_transfer_job
            (project_id, originating_vendor_id, destination_vendor_id, created_by_identity_id)
        VALUES (%s, %s, %s, %s)
        """,
        (project["project_id"], _ORIGINATING_VENDOR_ID, _DESTINATION_VENDOR_ID, created_by),
    )
    job = art_relations_db.fetchone(
        "SELECT * FROM project_transfer_job WHERE job_id = LAST_INSERT_ID()",
    )
    assert job is not None

    # One history row per release, with destination_video_artist_id set so PM also
    # remaps product_video rows that happen to exist for these releases.
    videos_by_release: dict[int, int] = {v["release_id"]: v["primary_artist_id"] for v in video_originals}
    for release in releases_original:
        source_video_artist_id = videos_by_release.get(release["release_id"])
        art_relations_db.execute(
            """
            INSERT INTO product_transfer_history
                (job_id, release_id, source_artist_id, destination_artist_id,
                 source_video_artist_id, destination_video_artist_id)
            VALUES (%s, %s, %s, %s, %s, %s)
            """,
            (
                job["job_id"],
                release["release_id"],
                release["artist_id"],
                destination_artist_id,
                source_video_artist_id,
                destination_artist_id,
            ),
        )

    yield {
        "job": job,
        "project_id": project["project_id"],
        "destination_artist_id": destination_artist_id,
        "release_ids": release_ids,
        "video_release_ids": [v["release_id"] for v in video_originals],
    }

    # Restore mutated rows by primary key, then delete the seeded job + history.
    art_relations_db.execute(
        "UPDATE project SET vendor_id = %s, subaccount_id = %s, artist_id = %s WHERE project_id = %s",
        (project["vendor_id"], project["subaccount_id"], project["artist_id"], project["project_id"]),
    )
    for r in releases_original:
        art_relations_db.execute(
            "UPDATE releases SET artist_id = %s, subaccount_id = %s WHERE release_id = %s",
            (r["artist_id"], r["subaccount_id"], r["release_id"]),
        )
    for v in video_originals:
        art_relations_db.execute(
            f"UPDATE product_video SET primary_artist_id = %s WHERE {product_video_pk} = %s",
            (v["primary_artist_id"], v["pk"]),
        )
    for ra in release_artist_originals:
        art_relations_db.execute(
            "UPDATE release_artist SET artist_info_id = %s WHERE release_artist_id = %s",
            (ra["artist_info_id"], ra["release_artist_id"]),
        )

    art_relations_db.execute(
        "DELETE FROM product_transfer_history WHERE job_id = %s",
        (job["job_id"],),
    )
    art_relations_db.execute(
        "DELETE FROM project_transfer_job WHERE job_id = %s",
        (job["job_id"],),
    )


@pytest.fixture
def transfer_job_with_unpopulated_destination_artists(
    art_relations_db: MySQLConnection,
) -> Generator[dict[str, Any], None, None]:
    """Seed a transfer job whose product_transfer_history rows have
    destination_artist_id IS NULL — the precondition that triggers
    ows-project-manager's 422 in /execute-content-transfer.

    No pre-state capture is needed: PM rejects the request before any DB
    mutations, so the only state we create is the job + history rows, both
    deleted on teardown.
    """
    project = art_relations_db.fetchone(
        """
        SELECT p.project_id, r.release_id
        FROM project p
        JOIN releases r ON r.project_id = p.project_id
        WHERE p.vendor_id = %s
        LIMIT 1
        """,
        (_ORIGINATING_VENDOR_ID,),
    )
    assert project, f"No project with releases found for vendor_id {_ORIGINATING_VENDOR_ID}"

    artist = art_relations_db.fetchone(
        "SELECT artist_id FROM artist_info WHERE vendor_id = %s LIMIT 1",
        (_ORIGINATING_VENDOR_ID,),
    )
    assert artist, f"No artist found for vendor_id {_ORIGINATING_VENDOR_ID} in artist_info"

    created_by = str(uuid.uuid4())
    art_relations_db.execute(
        """
        INSERT INTO project_transfer_job
            (project_id, originating_vendor_id, destination_vendor_id, created_by_identity_id)
        VALUES (%s, %s, %s, %s)
        """,
        (project["project_id"], _ORIGINATING_VENDOR_ID, _DESTINATION_VENDOR_ID, created_by),
    )
    job = art_relations_db.fetchone(
        "SELECT * FROM project_transfer_job WHERE job_id = LAST_INSERT_ID()",
    )
    assert job is not None

    # source_artist_id set, destination_artist_id deliberately NULL.
    art_relations_db.execute(
        """
        INSERT INTO product_transfer_history (job_id, release_id, source_artist_id)
        VALUES (%s, %s, %s)
        """,
        (job["job_id"], project["release_id"], artist["artist_id"]),
    )

    yield job

    art_relations_db.execute(
        "DELETE FROM product_transfer_history WHERE job_id = %s",
        (job["job_id"],),
    )
    art_relations_db.execute(
        "DELETE FROM project_transfer_job WHERE job_id = %s",
        (job["job_id"],),
    )


pytestmark = pytest.mark.lambda_name("execute-content-transfer")


def test_transfers_project_releases_and_videos_to_destination_vendor(
    art_relations_db: MySQLConnection,
    transferable_project_setup: dict[str, Any],
    lambda_handler: LambdaHandler,
) -> None:
    """End-to-end: a project with releases (and optionally product_videos) is moved
    to the destination vendor, with project.artist_id and every release's artist_id
    remapped via the artist mapping in product_transfer_history, all in one
    transaction handled by ows-project-manager.

    Covers BDD scenarios:
    - #1 Transfers a project with releases and product_videos
    - #5 Project artist_id is remapped
    - #6 product_video rows are remapped by artist mapping

    destination_subaccount_id is NULL on the job, so this also exercises PM's
    fallback: project.subaccount_id collapses to 0 and releases.subaccount_id stays NULL.
    """
    setup = transferable_project_setup
    job_id = setup["job"]["job_id"]

    lambda_handler.invoke(EXECUTE_CONTENT_TRANSFER_FUNCTION_NAME, {"job_id": job_id})

    project = art_relations_db.fetchone(
        "SELECT vendor_id, subaccount_id, artist_id FROM project WHERE project_id = %s",
        (setup["project_id"],),
    )
    assert project is not None
    assert project["vendor_id"] == _DESTINATION_VENDOR_ID, (
        f"project.vendor_id was not updated to destination vendor for job {job_id}"
    )
    assert project["subaccount_id"] == 0, (
        f"project.subaccount_id should collapse to 0 when destination_subaccount_id is NULL "
        f"for job {job_id}, got {project['subaccount_id']}"
    )
    assert project["artist_id"] == setup["destination_artist_id"], (
        f"project.artist_id was not remapped to destination_artist_id for job {job_id}"
    )

    release_ids = setup["release_ids"]
    releases = art_relations_db.fetchall(
        f"SELECT artist_id, subaccount_id FROM releases WHERE release_id IN ({_placeholders(release_ids)})",
        tuple(release_ids),
    )
    assert len(releases) == len(release_ids)
    for r in releases:
        assert r["artist_id"] == setup["destination_artist_id"], f"releases.artist_id was not remapped for job {job_id}"
        assert r["subaccount_id"] is None, (
            f"releases.subaccount_id should be NULL when destination_subaccount_id is NULL "
            f"for job {job_id}, got {r['subaccount_id']}"
        )

    if setup["video_release_ids"]:
        videos = art_relations_db.fetchall(
            f"SELECT primary_artist_id FROM product_video "
            f"WHERE release_id IN ({_placeholders(setup['video_release_ids'])})",
            tuple(setup["video_release_ids"]),
        )
        assert len(videos) >= 1
        for v in videos:
            assert v["primary_artist_id"] == setup["destination_artist_id"], (
                f"product_video.primary_artist_id was not remapped for job {job_id}"
            )


def test_raises_when_destination_artist_ids_not_populated(
    transfer_job_with_unpopulated_destination_artists: dict[str, Any],
    lambda_handler: LambdaHandler,
) -> None:
    """Scenario: ows-project-manager returns a 4xx during transfer.

    When any product_transfer_history row for the job has destination_artist_id IS NULL,
    ows-project-manager returns 422 from /execute-content-transfer (EnsureDestinationArtists
    is expected to populate destination artists before this step runs). The lambda's HTTP
    client raises RuntimeError on non-200, which surfaces as a FunctionError on the AWS
    response — the signal Step Functions uses to route the job to HandleError.

    invoke() is called with assertion=False so aws_testing_utils doesn't itself fail the
    test when it sees the FunctionError; we want to inspect the response.
    """
    job_id = transfer_job_with_unpopulated_destination_artists["job_id"]

    response = lambda_handler.invoke(
        EXECUTE_CONTENT_TRANSFER_FUNCTION_NAME,
        {"job_id": job_id},
        assertion=False,
    )

    assert response["StatusCode"] == 200, (
        f"AWS-level lambda invocation failed for job {job_id} (expected 200, got {response['StatusCode']})"
    )
    assert "FunctionError" in response, (
        f"Expected lambda to raise (FunctionError in response) for job {job_id} with "
        f"unpopulated destination_artist_ids, but response had no FunctionError: "
        f"{ {k: v for k, v in response.items() if k != 'Payload'} }"
    )