# coding: utf-8
# 回款明细
import os
import re
import urllib.parse
import warnings

import pandas as pd
from DrissionPage import ChromiumPage
from DrissionPage.errors import ElementNotFoundError
from lxml import etree
from pandas.errors import ParserError

from helper import helper, excel, file, domain, logger

country = None
payeeCode = None

log = logger.ConsoleLog()

page = ChromiumPage()
page.set.load_mode.normal()
page.set.when_download_file_exists('overwrite')
# 下载目录
download_path = os.getcwd()
# 检查下载目录是否存在，如果不存在则创建
helper.make_dir(download_path)
# 设置下载路径，确保在打开浏览器前设置
page.set.download_path(download_path)

# 忽略 openpyxl 样式警告
warnings.filterwarnings("ignore", category=UserWarning, module="openpyxl")


def page_get(url):
    host = domain.switch_domain(country)
    full_url = host + url
    page.get(full_url, timeout=3)


def export_list_read_data():
    new_file_name = 'new_Payments.xlsx'
    if os.path.isfile(new_file_name):
        df = pd.read_excel(new_file_name)
        return df

    file_name = 'Payments.xlsx'
    if not os.path.isfile(file_name):
        raise FileNotFoundError(f"{file_name},文件不存在")
        pass

    # page_get(f"hz/vendor/members/remittance/home")
    # page.ele("#remittance-home-select-all", timeout=2).click()
    # page.ele("#remittance-home-export-link", timeout=2).click.to_download()
    # file.wait_for_downloads(file_name)

    all_df = pd.read_excel(file_name, header=None)

    # 找到所有空行的索引，这里假设完全空的行为表头之间的分界线
    empty_rows = all_df[all_df.isnull().all(axis=1)].index.tolist()

    # 定位表头与数据的分隔
    first_header_start = empty_rows[1] + 1  # 第一个表头开始的行
    second_header_start = empty_rows[2] + 3  # 第二个表头开始的行

    first_df = pd.read_excel(file_name, skiprows=first_header_start, nrows=second_header_start - 7)
    second_df = pd.read_excel(file_name, skiprows=second_header_start)

    # 定义正则表达式模式，匹配包含 'Price' 或 'PCR' 或 'XXXXXXXX/XXXX/' 的描述
    pattern = r'Price Claim|PCR|Missed Adjustment|Shortage Claim|^[A-Z0-9]{8}/[A-Z0-9]{4}/'
    # 过滤符合条件的行
    filtered_second_df = second_df[second_df['Description'].str.contains(pattern, na=False, regex=True)]

    merged_df = pd.merge(filtered_second_df, first_df[['Payment Number', 'Payment Date']], on='Payment Number',
                         how='left')

    excel.save_xls(merged_df, new_file_name, "Remittance payments")

    return merged_df


def export_details_read_data(invoice_number):
    params = {
        "invoiceNumber": invoice_number,
        "payeeCode": payeeCode,
        "activeTab": "lineItems",
    }
    # 将字典转换为 URL 查询参数
    query_string = urllib.parse.urlencode(params)
    full_url = f"hz/vendor/members/inv-mgmt/invoice-details?" + query_string
    page_get(full_url)

    # # 读取详情内容
    file_name = f"payment\\{invoice_number}.csv"
    # if os.path.isfile(file_name):
    #     return pd.read_csv(file_name)

    while True:
        try:
            page.ele("#line-items-export-to-spreadsheet-announce", timeout=5).click.to_download(rename=file_name)
            file.wait_for_downloads(file_name)
            excel.remove_last_comma(file_name)
            break
        except ElementNotFoundError:
            log.warning("导出按钮不存在刷新网页")
            page.refresh()

    return pd.read_csv(file_name)


def get_content(tree, row_index: int, cell_index: int) -> str:
    """获取指定行和列的内容，如果没有找到，则返回 None。"""
    content = tree.xpath(f'//*[@role="row"][{row_index}]/*[@role="cell"][{cell_index}]/text()')
    return content[0] if content else None


def get_po_code(index, po_id) -> dict:
    result = {
        "index": index,
        "po_id": po_id
    }

    page_get(f"po/vendor/members/po-mgmt/order?poId={po_id}")

    po_table = page.ele("#po-header", timeout=5).html
    # 使用 lxml 解析 HTML
    tree = etree.HTML(po_table)

    # 获取 Vendor 内容
    result["vendor"] = get_content(tree, 2, 2)

    # 正则表达式查找数字和%之间的内容
    match = re.search(r'Payment Terms.*?(\d+%)', po_table)
    if match:
        result["payment_terms"] = match.group(1)[:-1]  # 去掉%
    else:
        result["payment_terms"] = 0

    return result


def price_extract_data(html_content):
    # 使用正则表达式删除所有 HTML 注释
    html_content = re.sub(r'<!--.*?-->', '', html_content)

    # 使用 lxml 解析 HTML
    tree = etree.HTML(html_content)

    # 提取所有行的数据
    rows = tree.xpath('//tr[contains(@class, "mt-row")]')

    data_list = []

    for row in rows:
        # 定义 data 字典，提取并去除多余字符
        data = {
            'PO_NUMBER': row.xpath('string(./td[@data-column="PO_NUMBER"]/span/span/a)').strip(),
            'ASIN': row.xpath('string(./td[@data-column="ASIN"]/span/span/a)').strip(),
            'EXTERNAL_ID': row.xpath('string(./td[@data-column="EXTERNAL_ID"]/span/span/a)').strip(),
            'TITLE': row.xpath('string(./td[@data-column="TITLE"])').strip(),
            'QUANTITY': row.xpath('string(./td[@data-column="QUANTITY"])').strip(),
            'INVOICE_COST': row.xpath('string(./td[@data-column="INVOICE_COST"])').strip().replace('$', ''),
            'PO_COST': row.xpath('string(./td[@data-column="PO_COST"])').strip().replace('$', ''),
            'INITIAL_RESEARCH_COST': row.xpath('string(./td[@data-column="INITIAL_RESEARCH_COST"])').strip().replace(
                '$', ''),
            'RESOLUTION_DECISION': row.xpath('string(./td[@data-column="RESOLUTION_DECISION"])').strip(),
            'RESOLUTION_COST': row.xpath('string(./td[@data-column="RESOLUTION_COST"])').strip().replace('$', '')
        }

        # 如果字段为空则设为空字符串
        for key in data:
            if not data[key]:
                data[key] = ""  # 将 None 转为 ""

        data_list.append(data)

    return data_list


def click_get_price_data():
    try:
        # 获取 Amounts 表格html
        page_html = page.ele(".a-box-inner", timeout=3).html
        # 使用 lxml 解析 HTML
        tree = etree.HTML(page_html)
        # 提取第二个 class="a-column a-span4" 下的第三个 class="a-row invoice-property-field-row" 下的 span 的值
        price_variance_amount = tree.xpath(
            '(//div[@class="a-column a-span4"])[2]//div[@class="a-row invoice-property-field-row"][3]//div[@class="a-column a-span6 a-span-last"]/span/text()')
        # 检查内容是否有效
        if price_variance_amount and price_variance_amount[0].strip() != "-":
            while True:
                try:
                    page.ele("#pd", timeout=5).click()
                    log.debug("等待争议数据加载,5秒后获取表单数据")
                    page.wait(5)
                    table_html = page.ele("#priceDiscrepancyWithDMSGridForm", timeout=5).html
                    price_data = price_extract_data(table_html)
                    return price_data
                except ElementNotFoundError:
                    log.warning("未获取到表数据")
                    break

        return []
    except ElementNotFoundError:
        page.refresh()
        click_get_price_data()


def handle_price_data(price_data, detail_data):
    result = {}
    """处理争议数据"""
    for price in price_data:
        if price['ASIN'] == detail_data.get('ASIN'):
            result = detail_data.to_dict()
            result['Quantity received'] = price['QUANTITY']
            if price['RESOLUTION_DECISION'] == "Approved":
                result['Shortage quantity'] = 0
                result['Amount'] = price['RESOLUTION_COST']
            else:
                result['Shortage quantity'] = 1
                amount = (float(price['INVOICE_COST']) - float(price['INITIAL_RESEARCH_COST'])) * int(price['QUANTITY'])
                result['Amount'] = f"${amount:.2f}"
            break

    return result


def handle_data(detail_datum, vendor, deduction_points):
    """处理正常数据"""
    amount = detail_datum.get('Amount', '$0.00')  # 默认值设为 '$0.00' 以避免错误
    amount = float(amount.replace('$', '').replace(',', ''))
    # 如果是0则回款完成
    is_finished = "否"
    if detail_datum.get('Shortage quantity', -1) == 0:
        is_finished = "是"

    amount_after_deduction = amount
    if deduction_points > 0:
        # 计算扣除后的金额
        amount_after_deduction = amount - (amount * (deduction_points / 100))

    # 复制原始行数据，避免直接修改
    record = detail_datum.copy()
    record["IsFinished"] = is_finished
    record["DeductionPoints"] = f"{deduction_points}%"  # 拼接百分号
    record["Code"] = vendor
    record["AmountAfterDeduction"] = amount_after_deduction

    return record


def main():
    list_data = export_list_read_data()
    # list_data = list_data[25:]
    excel.save_xls(list_data, "回款数据.xlsx", "Remittance payments")
    log.info(f"共计：{len(list_data)} 订单")

    all_normal_pay_data = []
    all_price_pay_data = []
    i = 0
    for _, data in list_data.iterrows():
        i += 1
        invoice_number = data.get("Invoice Number")

        # 获取当前订单的Payee和优惠比例
        vendor_payment_terms = get_po_code(i, invoice_number[:8])
        log.info(vendor_payment_terms)

        vendor = vendor_payment_terms['vendor']
        deduction_points = int(vendor_payment_terms['payment_terms'])

        if len(invoice_number) > 8:
            last_two = invoice_number[-2:]  # 取后两位
            last_three = invoice_number[-3:]  # 取后三位
            # 检查后两位是否在测试列表中
            if last_two in ["PC", "MA", "SC"]:
                invoice_number = invoice_number[:-2]  # 去掉后两位
            if last_three == "PCR":
                invoice_number = invoice_number[:-3]  # 去掉最后三位

        # 下载excel文件并读取数据
        detail_data = export_details_read_data(invoice_number)

        # 获取争议数据
        all_price_data = click_get_price_data()

        # 初始化列表存储新字段数据
        normal_pay_data = []
        price_pay_data = []

        for index, detail_datum in detail_data.iterrows():
            # 正常回款数据
            success_data = handle_data(detail_datum, vendor, deduction_points)
            # 将处理后的记录添加到临时列表
            normal_pay_data.append(success_data)

            if all_price_data:
                # 争议回款
                handle_after_price_data = handle_price_data(all_price_data, detail_datum)
                if handle_after_price_data:
                    price_data = handle_data(handle_after_price_data, vendor, deduction_points)
                    # 将处理后的记录添加到临时列表
                    price_pay_data.append(price_data)

        # 添加到汇总列表
        all_normal_pay_data.append(pd.DataFrame(normal_pay_data))

        if len(price_pay_data) > 0:
            all_price_pay_data.append(pd.DataFrame(price_pay_data))

    if all_normal_pay_data:
        # 将所有数据合并为一个 DataFrame
        normal_pay_summary = pd.concat(all_normal_pay_data, ignore_index=True)
        excel.save_xls(normal_pay_summary, "回款数据.xlsx", "正常回款导出明细")

    if all_price_pay_data:
        price_pay_summary = pd.concat(all_price_pay_data, ignore_index=True)
        excel.save_xls(price_pay_summary, "回款数据.xlsx", "Price导出明细")

    page.close()


if __name__ == '__main__':
    try:
        country = helper.get_input_with_default("国家(目前支持[DE,FR,JP,CA,UK,US])", "US")
        payeeCode = helper.get_input_with_default("payeeCode", "VECET")
        domain.domain_page(page, country)
        main()
        page.close()
    except KeyboardInterrupt:
        pass
    except Exception as e:
        log.error(e)
        helper.print_trace("main", e)
