"""Fixtures for integration tests against the QA environment."""

import json
import os
import uuid

import boto3
import pymysql
import pytest

from jwtauth.testing.schemas import SecretLookupInfo


def pytest_configure(config):
    """Register jwtauth plugin for integration tests."""
    config.pluginmanager.import_plugin("jwtauth.testing.pytest_plugin")


_QA_BASE_URL = os.environ.get("BASE_URL", "https://qa-ows-project-manager.theorchard.io")
_VENDOR_ID = 7123
_DESTINATION_VENDOR_ID = 6971
_DESTINATION_VENDOR_ID_WITH_SUBACCOUNT = 16055
_AR_SECRET_NAME = "qa/e2e-test-secrets/db/art-relations"
_AWS_REGION = "us-east-1"


def _ar_db_connection():
    client = boto3.client("secretsmanager", region_name=_AWS_REGION)
    creds = json.loads(client.get_secret_value(SecretId=_AR_SECRET_NAME)["SecretString"])
    return pymysql.connect(
        host=creds["host"],
        user=creds["user"],
        password=creds["password"],
        database=creds["database"],
        cursorclass=pymysql.cursors.DictCursor,
        autocommit=False,
    )


def _insert_project(cur, name="Integration Test Project", artist_id=None):
    """Insert a test project for vendor 7123 and return its project_id."""
    project_code = f"INT-TEST-{uuid.uuid4().hex[:8].upper()}"
    now = "2026-01-01 00:00:00"
    cur.execute(
        """
        INSERT INTO project
            (project_code, vendor_id, subaccount_id, project_name,
             artist_id, created_date_utc, updated_date_utc, correlation_id, deletions)
        VALUES (%s, %s, 0, %s, %s, %s, %s, %s, 'N')
        """,
        (project_code, _VENDOR_ID, name, artist_id, now, now, str(uuid.uuid4())),
    )
    return cur.lastrowid


def _delete_project(cur, project_id, release_ids=None):
    """Hard-delete a test project and all associated rows."""
    cur.execute(
        "DELETE FROM product_transfer_history WHERE job_id IN "
        "(SELECT job_id FROM project_transfer_job WHERE project_id = %s)",
        (project_id,))
    cur.execute("DELETE FROM project_transfer_job WHERE project_id = %s", (project_id,))
    if release_ids:
        cur.execute("DELETE FROM track WHERE release_id IN %s", (release_ids,))
        cur.execute("DELETE FROM product_video WHERE release_id IN %s", (release_ids,))
        cur.execute("DELETE FROM releases WHERE release_id IN %s", (release_ids,))
    cur.execute("DELETE FROM project WHERE project_id = %s", (project_id,))


@pytest.fixture(scope="session")
def qa_base_url():
    """Return the QA base URL."""
    return _QA_BASE_URL


@pytest.fixture(scope="session")
def vendor_id():
    """Return the test vendor ID."""
    return _VENDOR_ID


@pytest.fixture(scope="session")
def bearer_token(generate_bearer_token, jwtauth_secrets_manager):
    """Generate a bearer token for the integration test user."""
    return generate_bearer_token(
        get_user_creds_args=SecretLookupInfo(
            environment="qa",
            service_name="ows-project-manager",
            secret_name="integration-test/TRANSFER_TEST_USER_CREDENTIALS",
        ),
        get_auth0_creds_args=SecretLookupInfo(
            environment="qa",
            service_name="ows-project-manager",
            secret_name="integration-test/ABACUS_AUTH0_CREDENTIALS",
        ),
        secrets_manager=jwtauth_secrets_manager,
    )


@pytest.fixture(scope="session")
def auth_headers(bearer_token):
    """Return auth headers scoped to the test vendor."""
    return {
        "Authorization": f"Bearer {bearer_token}",
        "Grass-Account-Type": "vendor",
        "Grass-Account-Id": str(_VENDOR_ID),
    }


@pytest.fixture(scope="session")
def qa_artist_id():
    """Return a valid artist_id for vendor 7123 from QA."""
    conn = _ar_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT artist_id FROM artist_info WHERE vendor_id = %s LIMIT 1",
                (_VENDOR_ID,),
            )
            row = cur.fetchone()
        assert row, f"No artist found for vendor {_VENDOR_ID} in QA artist_info"
        return row["artist_id"]
    finally:
        conn.close()


@pytest.fixture(scope="session")
def qa_destination_artist_id():
    """Return a valid artist_id for vendor 6971 (the default destination vendor) from QA."""
    conn = _ar_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT artist_id FROM artist_info WHERE vendor_id = %s LIMIT 1",
                (_DESTINATION_VENDOR_ID,),
            )
            row = cur.fetchone()
        assert row, f"No artist found for vendor {_DESTINATION_VENDOR_ID} in QA artist_info"
        return row["artist_id"]
    finally:
        conn.close()


@pytest.fixture(scope="session")
def qa_destination_subaccount_artist_id():
    """Return a valid artist_id for vendor 16055 (the subaccount destination vendor) from QA."""
    conn = _ar_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT artist_id FROM artist_info WHERE vendor_id = %s LIMIT 1",
                (_DESTINATION_VENDOR_ID_WITH_SUBACCOUNT,),
            )
            row = cur.fetchone()
        assert row, (
            f"No artist found for vendor {_DESTINATION_VENDOR_ID_WITH_SUBACCOUNT} in QA artist_info"
        )
        return row["artist_id"]
    finally:
        conn.close()


@pytest.fixture(scope="function")
def db_connection():
    """Yield an AR QA DB connection and close it after the test."""
    conn = _ar_db_connection()
    try:
        yield conn
    finally:
        conn.close()


@pytest.fixture(scope="function")
def qa_project_no_releases():
    """Insert a project with no releases into AR QA, yield its ID, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(cur, name="Integration Test Project No Releases")
        conn.commit()
        yield {"project_id": project_id}
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id)
        conn.commit()
        conn.close()


@pytest.fixture(scope="function")
def qa_project_with_soft_deleted_release():
    """Insert a project + soft-deleted release (deletions='Y') into AR QA, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    release_id = None
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(cur, name="Integration Test Project Soft Deleted Release")
            cur.execute(
                "INSERT INTO releases (project_id, artist_id, upc, deletions) "
                "VALUES (%s, NULL, %s, 'Y')",
                (project_id, uuid.uuid4().int % (10 ** 12)),
            )
            release_id = cur.lastrowid
        conn.commit()
        yield {"project_id": project_id, "release_id": release_id}
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id, (release_id,) if release_id else None)
        conn.commit()
        conn.close()


@pytest.fixture(scope="function")
def qa_project(qa_artist_id):
    """Insert a project + release into AR QA, yield their IDs, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    release_id = None
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(cur, artist_id=qa_artist_id)
            cur.execute(
                "INSERT INTO releases (project_id, artist_id, upc, deletions) "
                "VALUES (%s, %s, %s, 'N')",
                (project_id, qa_artist_id, uuid.uuid4().int % (10 ** 12)),
            )
            release_id = cur.lastrowid
        conn.commit()
        yield {"project_id": project_id, "release_id": release_id, "artist_id": qa_artist_id}
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id, (release_id,) if release_id else None)
        conn.commit()
        conn.close()


@pytest.fixture(scope="function")
def qa_project_multiple_releases(qa_artist_id):
    """Insert a project + 3 releases into AR QA, yield their IDs, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    release_ids = []
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(
                cur,
                name="Integration Test Project Multiple Releases",
                artist_id=qa_artist_id,
            )
            for _ in range(3):
                cur.execute(
                    "INSERT INTO releases (project_id, artist_id, upc, deletions) "
                    "VALUES (%s, %s, %s, 'N')",
                    (project_id, qa_artist_id, uuid.uuid4().int % (10 ** 12)),
                )
                release_ids.append(cur.lastrowid)
        conn.commit()
        yield {"project_id": project_id, "release_ids": release_ids, "artist_id": qa_artist_id}
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id, tuple(release_ids) if release_ids else None)
        conn.commit()
        conn.close()


@pytest.fixture(scope="function")
def qa_project_with_video(qa_artist_id):
    """Insert a project + release + product_video row into AR QA, yield their IDs, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    release_id = None
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(
                cur,
                name="Integration Test Project With Video",
                artist_id=qa_artist_id,
            )
            cur.execute(
                "INSERT INTO releases (project_id, artist_id, upc, deletions) "
                "VALUES (%s, %s, %s, 'N')",
                (project_id, qa_artist_id, uuid.uuid4().int % (10 ** 12)),
            )
            release_id = cur.lastrowid
            cur.execute(
                "INSERT INTO product_video (release_id, primary_artist_id) VALUES (%s, %s)",
                (release_id, qa_artist_id),
            )
        conn.commit()
        yield {
            "project_id": project_id,
            "release_id": release_id,
            "artist_id": qa_artist_id,
            "source_video_artist_id": qa_artist_id,
        }
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id, (release_id,) if release_id else None)
        conn.commit()
        conn.close()


@pytest.fixture(scope="function")
def qa_project_with_tracks(qa_artist_id):
    """Insert a project + release + 2 tracks with ISRCs into AR QA, then clean up."""
    conn = _ar_db_connection()
    project_id = None
    release_id = None
    try:
        with conn.cursor() as cur:
            project_id = _insert_project(
                cur,
                name="Integration Test Project With Tracks",
                artist_id=qa_artist_id,
            )
            upc = (uuid.uuid4().int % (10 ** 12 - 1)) + 1
            cur.execute(
                "INSERT INTO releases (project_id, artist_id, upc, deletions) "
                "VALUES (%s, %s, %s, 'N')",
                (project_id, qa_artist_id, upc),
            )
            release_id = cur.lastrowid
            isrcs = [f"TST{uuid.uuid4().hex[:9].upper()}" for _ in range(2)]
            for isrc in isrcs:
                cur.execute(
                    "INSERT INTO track (release_id, isrc) VALUES (%s, %s)",
                    (release_id, isrc),
                )
        conn.commit()
        yield {
            "project_id": project_id,
            "release_id": release_id,
            "upc": str(upc),
            "isrcs": isrcs,
        }
    finally:
        with conn.cursor() as cur:
            if project_id:
                _delete_project(cur, project_id, (release_id,) if release_id else None)
        conn.commit()
        conn.close()