# -*- coding: utf-8 -*-
"""Проверка миграции 129 (Обувь Зима) и её применения в живой базе."""
import pathlib
import sys

ROOT = pathlib.Path(__file__).resolve().parents[2]
MIG = (ROOT / "sql" / "129_ppe_card_shoes_winter.sql").read_text(encoding="utf-8")


def test_migration_rows_and_order_name():
    #  карточке задаётся заказное наименование
    assert "UPDATE ppe_size_card" in MIG and "'Ботинки 8743РКВНМ-1, РФ'" in MIG
    #  восемь типоразмеров 40–47, по одному артикулу на размер, без цвета
    assert MIG.count("('shoes_winter'") == 8
    for art in ("10703092", "10220076", "10256394", "10220084",
                "10220089", "10256407", "10703098", "10703103"):
        assert art in MIG, art
    for size in ("'40'", "'41'", "'42'", "'43'",
                 "'44'", "'45'", "'46'", "'47'"):
        assert f", '', {size}, ''," in MIG, size


def test_live_db_rows_and_matrix():
    sys.path.insert(0, str(ROOT / "engine"))
    import psycopg
    from psycopg.rows import dict_row
    conn = psycopg.connect(
        "postgresql://postgres:postgres@127.0.0.1:5434/ooucr",
        row_factory=dict_row)
    cur = conn.cursor()
    cur.execute(
        """SELECT height, size, chest, article FROM app.ppe_size_row
            WHERE card_code = 'shoes_winter' ORDER BY sort""")
    rows = cur.fetchall()
    assert [(r["size"], r["article"]) for r in rows] == [
        ("40", "10703092"), ("41", "10220076"), ("42", "10256394"),
        ("43", "10220084"), ("44", "10220089"), ("45", "10256407"),
        ("46", "10703098"), ("47", "10703103")]
    #  артикулы карточки сопоставились с остатками из файла: ботинки
    #  попадают в свод «Остатков МБП» в строку «Обувь Зима»
    cur.execute(
        """
        SELECT COALESCE(card.title, 'Прочее') AS g, sum(s.qty) AS qty
          FROM app.ppe_base_stock s
          JOIN app.ppe_item i ON i.id = s.ppe_item_id
          LEFT JOIN app.ppe_size_row r
                 ON nullif(btrim(r.article), '') = i.code
          LEFT JOIN app.ppe_size_card card ON card.code = r.card_code
         WHERE i.code IN ('10703092', '10220076', '10256394', '10220084',
                          '10220089', '10256407', '10703098', '10703103')
         GROUP BY 1
        """)
    agg = cur.fetchall()
    assert len(agg) == 1 and agg[0]["g"] == "Обувь Зима", agg
    #  матрица подбора строится, одна строка (цвета нет)
    from timesheet import settingsdb
    matrix = settingsdb.ppe_size_matrix(rows)
    assert matrix["rows"] == ["—"] and matrix["sizes"] == [
        "40", "41", "42", "43", "44", "45", "46", "47"]
    conn.close()
