import os

import requests
import pandas as pd
from dotenv import load_dotenv

load_dotenv()
TOKEN_WB = os.getenv('WB_TOKEN')


def get_wildberries_report(api_key, date_from, date_to, limit=100000):
    """
    Fetches the report details from the Wildberries API by period.

    Args:
        api_key (str): Your API key for authentication.
        date_from (str): Start date in RFC3339 format (e.g., "2019-06-20").
        date_to (str): End date in RFC3339 format (e.g., "2019-06-20").
        limit (int): Maximum number of rows to fetch in each API call (default is 100000).
        realization_report_id (int): The ID of the realization report.
    Returns:
        list: A list of report records.
    """
    url = "https://statistics-api.wildberries.ru/api/v5/supplier/reportDetailByPeriod"
    headers = {
        "Authorization": api_key
    }
    params = {
        "dateFrom": date_from,
        "dateTo": date_to,
        "limit": limit,
        "rrdid": 0
    }
    all_data = []

    while True:
        response = requests.get(url, headers=headers, params=params)

        if response.status_code != 200:
            print(f"Error: {response.status_code} - {response.text}")
            break

        data = response.json()
        all_data.extend(data)

        if len(data) < limit:
            break

        params["rrdid"] = data[-1]["rrd_id"]

    return all_data


def process_dataframe(df, realization_report_id):
    """
    Processes the dataframe by filtering and summarizing data according to the specifications.

    Args:
        df (pd.DataFrame): Raw data from the Wildberries API.
        realization_report_id (int): The realization report ID to filter by.

    Returns:
        pd.DataFrame: Processed and summarized dataframe.
    """
    # Filter by realization report ID
    df = df[df["realizationreport_id"] == realization_report_id].copy()

    # Convert Артикулы to strings
    if "Артикул" in df.columns:
        df["Артикул"] = df["Артикул"].astype(str)

    # Handle "К перечислению за товар" (ppvz_for_pay) adjustments for "Возврат"
    if "doc_type_name" in df.columns and "ppvz_for_pay" in df.columns:
        df.loc[:, "ppvz_for_pay_adjusted"] = df.apply(
            lambda row: -row["ppvz_for_pay"] if row["doc_type_name"] == "возврат" else row["ppvz_for_pay"],
            axis=1
        )

    # Summarize required fields
    summary = {
        "Продажа (Скидка WB) налогооблагаемая база (retail_amount)": df["retail_amount"].sum(),
        "Продажа (Без скидки WB) (retail_price_withdisc_rub)": df["retail_price_withdisc_rub"].sum(),
        "К перечислению за товар (ppvz_for_pay)": df["ppvz_for_pay_adjusted"].sum(),
        "Стоимость логистики (delivery_rub)": df["delivery_rub"].sum(),
        "Общая сумма штрафов (penalty)": df["penalty"].sum(),
        "Стоимость хранения (storage_fee)": df["storage_fee"].sum(),
        "Стоимость платной приемки (acceptance)": df["acceptance"].sum(),
        "Прочие удержания/выплаты (deduction)": df["deduction"].sum(),
    }

    # Calculate "Комиссия маркетплейса"
    summary["Комиссия маркетплейса"] = (
        summary["Продажа (Без скидки WB) (retail_price_withdisc_rub)"] -
        summary["К перечислению за товар (ppvz_for_pay)"]
    )
    summary["Итого"] = (
            summary["Продажа (Без скидки WB) (retail_price_withdisc_rub)"]
            - summary["Комиссия маркетплейса"]
            + summary["Стоимость платной приемки (acceptance)"]
            - summary["Прочие удержания/выплаты (deduction)"]
            - summary["Стоимость хранения (storage_fee)"]
            - summary["Общая сумма штрафов (penalty)"]
            - summary["Стоимость логистики (delivery_rub)"]
    )
    # Convert the summary dictionary to a dataframe for better output handling
    summary_df = pd.DataFrame([summary])

    return summary_df


def save_reports_to_excel(reports, report_id, file_name="wildberries_report.xlsx"):
    """
    Saves the reports to an Excel file.

    Args:
        reports (list): A list of dictionaries containing the report data.
        file_name (str): The name of the Excel file to save (default is "wildberries_report.xlsx").

    Returns:
        None
    """
    if not reports:
        print("No data to save.")
        return

    df = pd.DataFrame(reports)
    df = process_dataframe(df, report_id)

    try:
        df.to_excel(file_name, index=False, engine="openpyxl")
        print(f"Report saved successfully to {file_name}")
    except Exception as e:
        print(f"Error saving report to Excel: {e}")


if __name__ == "__main__":
    date_from = "2024-11-04T00:00:00"
    date_to = "2024-11-10T23:59:59"
    # report_id = 1100558938
    report_id = 1100558937
    reports = get_wildberries_report(TOKEN_WB, date_from, date_to)
    print(f"Fetched {len(reports)} records.")

    save_reports_to_excel(reports, report_id, "wildberries_report.xlsx")