import pytest

from app.services.catalog.csv_import import import_csv
from app.services.infra.database import bootstrap_tenant, get_db_connection

HEADER = ("Handle,Title,Body (HTML),Vendor,Type,Tags,Published,"
          "Option1 Name,Option1 Value,Option2 Name,Option2 Value,"
          "Variant SKU,Variant Inventory Qty,Variant Price,"
          "Variant Compare At Price,Image Src,Status")


def csv_text(*handles):
    rows = [f"{h},{h.title()},A product.,Brand,Shirts,tag,TRUE,Size,M,,,"
            f"SKU-{h},5,19.00,,https://x.example/{h}.jpg,active"
            for h in handles]
    return "\n".join([HEADER, *rows])


def _rows(schema, where="TRUE"):
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'SELECT product_key, name, source_kind, source_ref '
                        f'FROM "{schema}".strategist_products WHERE {where}')
            return cur.fetchall()
    finally:
        conn.close()


@pytest.fixture
def tenant(temp_tenant):
    bootstrap_tenant(temp_tenant)
    return temp_tenant


def test_products_are_imported(tenant):
    report = import_csv(tenant, csv_text("shirt", "belt"), "spring.csv", "USD")
    assert report["imported"] == 2
    assert len(_rows(tenant)) == 2


def test_imported_rows_are_scoped_to_csv(tenant):
    # Without this a CSV upload and a Shopify sync delete each other.
    import_csv(tenant, csv_text("shirt"), "spring.csv", "USD")
    kinds = {r[2] for r in _rows(tenant)}
    refs = {r[3] for r in _rows(tenant)}
    assert kinds == {"csv"}
    assert refs == {"spring.csv"}


def test_re_uploading_the_same_file_replaces_that_batch(tenant):
    import_csv(tenant, csv_text("shirt", "belt"), "spring.csv", "USD")
    report = import_csv(tenant, csv_text("shirt"), "spring.csv", "USD")
    names = {r[1] for r in _rows(tenant)}
    assert names == {"Shirt"}
    assert report["replaced"] == 1


def test_a_second_file_adds_rather_than_replaces(tenant):
    import_csv(tenant, csv_text("shirt"), "spring.csv", "USD")
    import_csv(tenant, csv_text("coat"), "winter.csv", "USD")
    assert len(_rows(tenant)) == 2


def test_another_sources_products_are_never_touched(tenant):
    # The hazard this scoping exists for.
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                f'INSERT INTO "{tenant}".strategist_products '
                "(product_key, name, product_url, source_kind, source_ref) "
                "VALUES ('shopify:1','Shopify Product','https://x/1',"
                "'shopify','shop.myshopify.com')")
        conn.commit()
    finally:
        conn.close()

    import_csv(tenant, csv_text("shirt"), "spring.csv", "USD")
    import_csv(tenant, csv_text("belt"), "spring.csv", "USD")
    assert len(_rows(tenant, "source_kind = 'shopify'")) == 1


def test_a_dry_run_writes_nothing(tenant):
    report = import_csv(tenant, csv_text("shirt"), "spring.csv", "USD",
                        dry_run=True)
    assert report["dry_run"] is True
    assert report["products"] == 1
    assert _rows(tenant) == []


def test_bad_rows_are_reported_but_good_ones_land(tenant):
    text = csv_text("shirt") + "\n,,,,,,,,,,,,,,,,"
    report = import_csv(tenant, text, "spring.csv", "USD")
    assert report["imported"] == 1
    assert report["errors"]
    assert len(_rows(tenant)) == 1


def test_the_declared_currency_is_stored(tenant):
    import_csv(tenant, csv_text("shirt"), "spring.csv", "INR")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'SELECT currency FROM "{tenant}".strategist_products')
            assert cur.fetchone()[0] == "INR"
    finally:
        conn.close()


def test_imported_products_are_flagged_for_having_no_url(tenant):
    # A CSV carries no product URL, so they are visible in the catalogue but
    # must not be served. This is the most likely surprise in the feature.
    import_csv(tenant, csv_text("shirt"), "spring.csv", "USD")
    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'SELECT missing_fields FROM "{tenant}".strategist_products')
            assert "product_url" in cur.fetchone()[0]
    finally:
        conn.close()


def test_the_first_import_establishes_the_reference_currency(tenant):
    # Without this every price is unconvertible, normalise() flags the whole
    # catalogue price_reference, and pairing then skips every product -- an
    # import that reports success and produces nothing downstream.
    from app.services.infra.database import get_setting

    import_csv(tenant, csv_text("shirt"), "spring.csv", "GBP")
    assert get_setting(tenant, "reference_currency") == "GBP"


def test_imported_products_are_not_flagged_when_they_have_a_url(tenant):
    text = (HEADER + ",Product URL\n"
            "shirt,Shirt,A product.,Brand,Shirts,tag,TRUE,Size,M,,,SKU-s,5,"
            "19.00,,https://x.example/s.jpg,active,https://x.example/p/shirt")
    import_csv(tenant, text, "spring.csv", "USD")

    conn = get_db_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(f'SELECT missing_fields FROM "{tenant}".strategist_products')
            assert cur.fetchone()[0] == []
    finally:
        conn.close()