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

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

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

country = None
payeeCode = None

log = logger.ConsoleLog()
rdb = redisx.RedisClient()

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 invoice_details(invoice_number,last_two,last_three):
    if len(invoice_number) > 8:
        # 检查后两位是否在测试列表中
        if last_two in ["MA", "PC"]:
            invoice_number = invoice_number[:-2]  # 去掉后两位
        if last_three in ["PCR"]:
            invoice_number = invoice_number[:-3]  # 去掉最后三位
        if last_three in ["+SC", "SC-"]:
            invoice_number = invoice_number[:-3]  # 去掉最后三位
            invoice_number = invoice_number + 'SCR'
        if last_two == "SC":
            invoice_number = invoice_number + 'R'

    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)


def export_details_read_data(file_name):
    count = 0
    while True:
        try:
            page.ele("#line-items-export-to-spreadsheet-announce").click.to_download(rename=file_name)
            file.wait_for_downloads(file_name)
            excel.remove_last_comma(file_name)
            break
        except ElementNotFoundError:
            if count == 3: return None
            count += 1
            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
    }

    po_id = po_id[:8]

    cache_key = "payment"
    payment_cache = rdb.get_client().hget(cache_key, po_id)
    if payment_cache:
        cache_value = json.loads(payment_cache)
        result["vendor"] = cache_value['vendor']
        result["payment_terms"] = cache_value['payment_terms']
        return result

    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

    cache_value = result.copy()
    del cache_value['index']
    rdb.get_client().hset(cache_key, po_id, json.dumps(cache_value))

    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():
    while True:
        try:
            # 点击争议价tab
            page.ele("#pd").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


def handle_price_data(price_data_list, invoice_amount):
    result = {}
    invoice_amount = abs(float(f"{invoice_amount:.2f}"))
    """处理争议数据"""
    for data_list in price_data_list:
        # 计算差异金额单价
        unit_cost_float = Decimal(data_list['INVOICE_COST']) - Decimal(data_list['INITIAL_RESEARCH_COST'])
        unit_cost = unit_cost_float * int(data_list['QUANTITY'])
        amount = float(f"{unit_cost:.2f}")
        if amount == invoice_amount:
            result = data_list
            result['Quantity received'] = data_list['QUANTITY']
            result['UnitCost'] = unit_cost_float
            result['Amount'] = f"${amount:.2f}"
            if data_list['RESOLUTION_DECISION'] == "Approved":
                result['Shortage quantity'] = 0
            else:
                result['Shortage quantity'] = 1
            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.update({"Amount": amount})
    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:]

    # 获取当前日期和时间并格式化
    current_datetime = datetime.now().strftime('%Y-%m-%d_%H-%M')  # 格式化为 'YYYY-MM-DD_HH-MM-SS'
    # 原文件名
    file_name = "回款数据.xlsx"
    # 拼接新的文件名
    new_file_name = f"{current_datetime}_{file_name}"
    excel.save_xls(list_data, new_file_name, "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")
        invoice_amount = data.get("Invoice Amount")

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

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

        # 处理单号主要为了进入详情页
        last_two = invoice_number[-2:]  # 取后两位
        last_three = invoice_number[-3:]  # 取后三位

        # 判断是否为争议订单
        if last_three == "PCR" or last_two == "PC":
            cache_key = "price_data"
            price_data = rdb.get_client().hget(cache_key, invoice_number)
            if price_data:
                price_data = json.loads(price_data)
            else:
                # 进入详情页
                invoice_details(invoice_number, last_two, last_three)
                # 获取争议数据
                price_data = click_get_price_data()
                # 缓存数据
                rdb.get_client().hset(cache_key, invoice_number, json.dumps(price_data))
            # 争议回款
            handle_after_price_data = handle_price_data(price_data, invoice_amount)
            format_price_data = handle_data(handle_after_price_data, vendor, deduction_points)
            all_price_pay_data.append(pd.DataFrame(format_price_data, index=[0]))
        else:
            file_name = f"payment\\{invoice_number}.csv"
            if os.path.isfile(file_name):
                detail_data = pd.read_csv(file_name)
            else:
                # 进入详情页
                invoice_details(invoice_number, last_two, last_three)
                # 下载excel文件并读取数据
                detail_data = export_details_read_data(file_name)
                if detail_data is None:
                    log.error("数据存在问题,请手动处理")
                    continue

            # 初始化列表存储新字段数据
            normal_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)
            # 添加到汇总列表
            all_normal_pay_data.append(pd.DataFrame(normal_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, new_file_name, "正常回款导出明细")

    if all_price_pay_data:
        price_pay_summary = pd.concat(all_price_pay_data, ignore_index=True)
        excel.save_xls(price_pay_summary, new_file_name, "Price导出明细")


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)
