---

import os
import requests
import psycopg2

from psycopg2.extras import execute_values
from dotenv import load_dotenv
from datetime import datetime, timedelta

load_dotenv()

API_KEY = os.getenv("VP_API_KEY")

DB_CONFIG = {
    "host": os.getenv("DB_HOST"),
    "port": os.getenv("DB_PORT"),
    "dbname": os.getenv("DB_NAME"),
    "user": os.getenv("DB_USER"),
    "password": os.getenv("DB_PASSWORD"),
}

BASE_URL = "https://tl.myvirtualpos.ru/api/receipt"

KRASNOYARSK_WAREHOUSE_IDS = {
    229,
    226,
    224,
    225,
    228,
    230,
    232,
    234
}


def safe_float(value):
    if value is None or value == "":
        return 0.0

    try:
        return float(value)
    except:
        return 0.0


def fetch_sales(date_from, date_to):

    params = {
        "apikey": API_KEY,
        "format": "json",
        "datefrom": date_from.strftime("%Y%m%d"),
        "dateto": date_to.strftime("%Y%m%d"),
        "fields": "itemname:article:user_fio:manufacturer_name"
    }

    response = requests.get(
        BASE_URL,
        params=params,
        timeout=120
    )

    response.raise_for_status()

    data = response.json()

    return data.get("receipts", [])


def transform_data(receipts):

    rows = []

    for receipt_wrapper in receipts:

        receipt = receipt_wrapper.get("receipt", {})

        receipt_id = receipt.get("receipt_id")
        sale_type = receipt.get("doc_type")
        sale_datetime_raw = receipt.get("date")
        warehouse_id = receipt.get("warehouseid")
        seller_name = receipt.get("user_fio")

        if warehouse_id in KRASNOYARSK_WAREHOUSE_IDS:
            continue

        if not receipt_id:
            continue

        if not sale_datetime_raw:
            continue

        sale_datetime = datetime.strptime(
            sale_datetime_raw,
            "%d.%m.%Y %H:%M:%S"
        )

        items = receipt.get("items", [])

        for item_wrapper in items:

            item = item_wrapper.get("item", {})

            item_id = item.get("itemid")

            if item_id is None:
                continue

            row = (
                receipt_id,
                sale_type,
                sale_datetime,
                warehouse_id,
                item.get("manufacturer_name"),
                item_id,
                item.get("article"),
                item.get("itemname"),
                safe_float(item.get("cogs")),
                safe_float(item.get("quantity")),
                safe_float(item.get("amount")),
                safe_float(item.get("discount")),
                seller_name
            )

            rows.append(row)

    return rows


def load_to_postgres(rows):

    if not rows:
        return

    conn = psycopg2.connect(**DB_CONFIG)

    try:

        with conn:

            with conn.cursor() as cur:

                query = """
                    INSERT INTO sales_raw (
                        receipt_id,
                        sale_type,
                        sale_datetime,
                        warehouse_id,
                        manufacturer,
                        item_id,
                        sku,
                        product_name,
                        unit_cost,
                        quantity,
                        line_amount,
                        discount_amount,
                        seller_name
                    )
                    VALUES %s
                    ON CONFLICT (receipt_id, item_id)
                    DO NOTHING
                """

                execute_values(cur, query, rows)

        print(f"Загружено строк: {len(rows)}")

    finally:

        conn.close()


def print_uncategorized_items():

    conn = psycopg2.connect(**DB_CONFIG)

    try:

        with conn.cursor() as cur:

            cur.execute("""
                SELECT DISTINCT
                    s.item_id,
                    s.sku,
                    s.product_name,
                    s.manufacturer
                FROM sales_raw s
                LEFT JOIN product_categories c
                    ON s.item_id::text = c.item_id
                WHERE c.item_id IS NULL
                ORDER BY s.product_name
            """)

            rows = cur.fetchall()

            print("")
            print("Товары без категории")
            print("-" * 80)

            if not rows:
                print("Все товары категоризированы")
                return

            for row in rows:

                item_id, sku, product_name, manufacturer = row

                print(
                    f"item_id={item_id} | "
                    f"sku={sku} | "
                    f"manufacturer={manufacturer} | "
                    f"{product_name}"
                )

            print("-" * 80)
            print(f"Всего: {len(rows)}")

    finally:

        conn.close()


def main():

    start_date = datetime(2026, 1, 1)

    end_date = datetime.now()

    current = start_date

    while current < end_date:

        next_day = current + timedelta(days=1)

        print("")
        print(
            f"Загрузка продаж за "
            f"{current.strftime('%d.%m.%Y')}"
        )

        receipts = fetch_sales(
            current,
            next_day
        )

        print(f"Получено чеков: {len(receipts)}")

        rows = transform_data(receipts)

        print(
            f"Подготовлено строк: {len(rows)}"
        )

        load_to_postgres(rows)

        current = next_day

    print_uncategorized_items()

    print("")
    print("Готово")


if __name__ == "__main__":
    main()