"""Conflict-safe magento_media_map upsert."""

from __future__ import annotations

import pytest
from sqlalchemy import create_engine, event, select
from sqlalchemy.dialects.postgresql import JSONB
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.orm import Session, sessionmaker

from db.base import Base
from db.magento_repositories import SqlAlchemyMagentoMediaMapRepository
from db.models import MagentoConnection, MagentoMediaMap


@compiles(JSONB, "sqlite")
def _compile_jsonb_sqlite(element, compiler, **kw):
    return "JSON"


@pytest.fixture()
def session():
    engine = create_engine("sqlite:///:memory:")

    @event.listens_for(engine, "connect")
    def _sqlite_pragma(dbapi_connection, connection_record):
        cursor = dbapi_connection.cursor()
        cursor.execute("PRAGMA foreign_keys=ON")
        cursor.close()

    Base.metadata.create_all(engine, tables=[MagentoConnection.__table__, MagentoMediaMap.__table__])
    SessionLocal = sessionmaker(bind=engine)
    sess = SessionLocal()
    conn = MagentoConnection(
        store_base_url="https://example.test",
        environment="staging",
        consumer_key="k",
        consumer_secret="s",
        access_token="t",
        access_token_secret="ts",
        status="active",
    )
    sess.add(conn)
    sess.commit()
    sess.connection_id = conn.id
    try:
        yield sess
    finally:
        sess.close()


def test_media_map_upsert_merges_same_sha_different_url(session: Session):
    repo = SqlAlchemyMagentoMediaMapRepository(session)
    cid = session.connection_id
    sha = "8a840d734a076a06d0b62281391f150925cfa1c8e914d176bd714e381e29cafe"
    repo.upsert(cid, "RTA-CSG-SBF39", "https://cdn/old.png", sha, magento_entry_id=101)
    session.commit()

    # Same bytes under a new CDN URL must not UniqueViolation — merge onto one row.
    repo.upsert(cid, "RTA-CSG-SBF39", "https://cdn/new.png", sha, magento_entry_id=101)
    session.commit()

    rows = session.scalars(
        select(MagentoMediaMap).where(
            MagentoMediaMap.connection_id == cid,
            MagentoMediaMap.sku == "RTA-CSG-SBF39",
        )
    ).all()
    assert len(rows) == 1
    assert rows[0].remote_url == "https://cdn/new.png"
    assert rows[0].content_sha256 == sha
    assert rows[0].magento_entry_id == 101


def test_media_map_upsert_idempotent_same_url_and_sha(session: Session):
    repo = SqlAlchemyMagentoMediaMapRepository(session)
    cid = session.connection_id
    sha = "abc123"
    repo.upsert(cid, "SKU-1", "https://cdn/a.png", sha, magento_entry_id=7, magento_file="/a.png")
    repo.upsert(cid, "SKU-1", "https://cdn/a.png", sha, magento_entry_id=7, magento_file="/a.png")
    session.commit()
    rows = session.scalars(select(MagentoMediaMap)).all()
    assert len(rows) == 1