import datetime

import pandas as pd
import xlsxwriter
import psycopg2  # Odoo 依赖 psycopg2 处理 PostgreSQL 交互
from psycopg2 import sql  # 用于安全拼接 SQL（防止 SQL 注入）
from odoo import http, tools
from odoo.http import content_disposition, request
from odoo import models, fields, api, SUPERUSER_ID, _
import os
import io
from jinja2 import FileSystemLoader, Environment
import logging

_logger = logging.getLogger(__name__)

BASE_DIR = os.path.dirname(os.path.dirname(__file__))
templateloader = FileSystemLoader(searchpath=BASE_DIR + "/static/src/js")
env = Environment(loader=templateloader)


class ProductIncomeExpenseIframe(http.Controller):

    @http.route("/roke/pub/product_income_expense_iframe", type="http", auth='user', cors='*', csrf=False)
    def product_income_expense_iframe(self, **kwargs):
        _self = http.request
        user_id = _self.env.user.id
        user_name = _self.env.user.name
        template = env.get_template('index.html')
        data = {"code": 1, "message": "请求通过", "user_id": user_id, "user_name": user_name}
        return template.render(data)

    @http.route("/roke/product/product_income_expense/create", type="json", auth='none', cors='*', csrf=False)
    def product_income_expense_create(self):
        _self = http.request
        cr = _self.env.cr  # 获取 cursor
        data_list = _self.jsonrequest.get("data_list", [])
        try:
            # 获取模型环境
            model = _self.env(user=SUPERUSER_ID)["roke.product.income.expense"]
            for v in data_list:
                if v.get("id"):  # 更新
                    # 获取当前要更新的记录（旧数据）
                    current_record = model.browse(int(v.get("id")))
                    if not current_record:
                        return {"code": 1, "message": f"更新失败，ID为{v.get('id')}的记录不存在"}
                    # 提取旧数据（用于还原历史影响）
                    old_income = float(current_record.income or 0)
                    old_expenditure = float(current_record.expenditure or 0)
                    old_diff = old_income - old_expenditure  # 旧的收支差额
                    # 提取更新后的值
                    new_income = float(v.get("income", 0) or 0)
                    new_expenditure = float(v.get("expenditure", 0) or 0)
                    new_diff = new_income - new_expenditure  # 新的收支差额
                    # 计算差额变化
                    diff_change = new_diff - old_diff  # 新旧差额的差值
                    # 当前的一条数据
                    latest_record = model.search([('id', '=', int(v.get("id")))], order="business_date desc,create_date desc", limit=1)

                    balance = round(latest_record.balance + diff_change, 2);
                    cr.execute("""
                        UPDATE roke_product_income_expense
                           SET business_date = %s,
                               abstract = %s,
                               income = %s,
                               machinery_type = %s,
                               expenditure = %s,
                               customer = %s,
                               balance = %s
                         WHERE id = %s
                    """, (
                        v.get("business_date"), v.get("abstract"), v.get("income"),
                        v.get("machinery_type"), v.get("expenditure"), v.get("customer"), balance,
                        v.get("id")
                    ))

                    if cr.rowcount == 0:  # 没有找到对应记录
                        return {"code": 1, "message": "更新失败，没找到对应数据。"}

                else:  # 创建
                    # # 支出
                    # expenditure = float(v.get("expenditure", 0) or 0)
                    # # 收入
                    # income = float(v.get("income", 0) or 0)
                    if v.get("expenditure") in ['', None, False]:
                        v.update({"expenditure": 0})
                    if v.get("income") in ['', None, False]:
                        v.update({"income": 0})
                    # 最新一条数据
                    latest_record = model.search([], order="business_date desc,create_date desc", limit=1)

                    # 计算结余
                    balance = round(latest_record.balance + float(v.get("income")) - float(v.get("expenditure")), 2)

                    cr.execute("""
                        INSERT INTO roke_product_income_expense
                            (business_date, abstract, income, machinery_type, expenditure, customer, balance, create_uid, create_date)
                        VALUES (%s, %s, %s, %s, %s, %s, %s, %s,  NOW() - INTERVAL '8 hours')
                    """, (
                        v.get("business_date"), v.get("abstract"), v.get("income"),
                        v.get("machinery_type"), v.get("expenditure"), v.get("customer"),
                        balance,
                        v.get("user_id", 1)  # 默认 user_id 没传的话用 1（管理员）
                    ))
            _self.env.cr.commit()  # 提交事务，防止数据丢失
        except Exception as e:
            return {"code": 1, "message": f"操作失败：{str(e)}"}
        return {"code": 0, "message": "操作成功！"}

    @http.route("/roke/product/product_income_expense/get", type="json", auth='none', cors='*', csrf=False)
    def product_income_expense_get_list(self):
        _self = http.request
        limit = int(_self.jsonrequest.get("limit", 20))
        page = int(_self.jsonrequest.get("page", 1))
        start_date = _self.jsonrequest.get("start_date", "")
        end_date = _self.jsonrequest.get("end_date", "")
        type_str = _self.jsonrequest.get("type_str", False)  # income收入/expenditure支出
        machinery_type = _self.jsonrequest.get("machinery_type", False)
        customer = _self.jsonrequest.get("customer", False)
        abstract = _self.jsonrequest.get("abstract", False)

        # 拼接 SQL 条件
        where_clauses = ["1=1"]
        params = []

        if start_date and end_date:
            where_clauses.append("business_date >= %s")
            where_clauses.append("business_date <= %s")
            params.extend([start_date, end_date])

        if type_str:  # income 或 expenditure
            where_clauses.append(f"{type_str} > 0")

        if machinery_type:
            where_clauses.append("machinery_type = %s")
            params.append(machinery_type)

        if customer:
            where_clauses.append("customer ILIKE %s")
            params.append("%%%s%%" % customer)

        if abstract:
            where_clauses.append("abstract ILIKE %s")
            params.append("%%%s%%" % abstract)

        where_sql = " AND ".join(where_clauses)

        # 查询数据
        offset = (page - 1) * limit
        sql = f"""
            SELECT id, business_date, abstract, customer, income, expenditure, balance, machinery_type, create_uid, create_date
            FROM roke_product_income_expense
            WHERE {where_sql}
            ORDER BY create_date DESC,business_date DESC
            LIMIT %s OFFSET %s
        """
        params.extend([limit, offset])

        _self.env.cr.execute(sql, params)
        rows = _self.env.cr.dictfetchall()

        # 计算当前页的收入合计和支出合计
        total_income = round(sum(row.get('income', 0) or 0 for row in rows), 2)
        total_expenditure = round(sum(row.get('expenditure', 0) or 0 for row in rows), 2)

        # 查询总数
        sql_count = f"SELECT COUNT(*) FROM roke_product_income_expense WHERE {where_sql}"
        _self.env.cr.execute(sql_count, params[:-2])  # 去掉 limit 和 offset
        count = _self.env.cr.fetchone()[0]

        data = []
        for v in rows:
            business_date = v["business_date"].strftime('%Y-%m-%d') if v["business_date"] else ""
            create_date = (v["create_date"] + datetime.timedelta(hours=8)).strftime('%Y-%m-%d %H:%M') if v[
                "create_date"] else ""
            user_name = _self.env['res.users'].sudo().browse(v["create_uid"]).name or ""

            data.append({
                "id": v["id"],
                "business_date": business_date,
                "abstract": v["abstract"] or "",
                "customer": v["customer"] or "",
                "income": round(v["income"] or 0, 2),
                "machinery_type": v["machinery_type"] or "其他",
                "expenditure": round(v["expenditure"] or 0, 2),
                "balance": round(v["balance"] or 0, 2),
                "user_name": user_name,
                "create_date": create_date,
            })
        return {"code": 0, "message": "获取成功！", "data": data, "count": count, "total_income": total_income, "total_expenditure": total_expenditure}

    @http.route("/roke/product/product_income_expense/delete", type="json", auth='none', cors='*', csrf=False)
    def product_income_expense_delete(self):
        _self = http.request
        del_id = _self.jsonrequest.get("del_id", False)
        if not del_id:
            return {"code": 1, "message": "删除失败，没找到对应数据。"}
        data = _self.env["roke.product.income.expense"].sudo().search([("id", "=", del_id)])
        if not data:
            return {"code": 1, "message": "删除失败，没找到对应数据。"}
        data.unlink()
        return {"code": 0, "message": "删除成功!"}

    @http.route("/roke/product/product_income_expense/export", type="http", auth='none', cors='*', csrf=False)
    def get_product_income_expense_export(self, **kwargs):
        _self = http.request
        start_date = kwargs.get("start_date", "")
        end_date = kwargs.get("end_date", "")
        type_str = kwargs.get("type_str", False)  # income收入/expenditure支出
        machinery_type = kwargs.get("machinery_type", False)
        customer = kwargs.get("customer", False)
        abstract = kwargs.get("abstract", False)
        domain = []
        if start_date and end_date:
            domain.append(("business_date", ">=", start_date))
            domain.append(("business_date", "<=", end_date))
        if type_str:
            domain.append((type_str, ">", 0.0))
        if machinery_type:
            domain.append(("machinery_type", "=", machinery_type))
        if customer:
            domain.append(("customer", "ilike", customer))
        if abstract:
            domain.append(("abstract", "ilike", abstract))
        data_list = _self.env["roke.product.income.expense"].sudo().search(domain, order="business_date desc, create_date desc")
        data = []
        for v in data_list:
            data.append({
                "business_date": v.business_date,
                "abstract": v.abstract or "",
                "customer": v.customer or "",
                "income": v.income or 0,
                "expenditure": v.expenditure or 0,
                "balance": v.balance or 0,
                "machinery_type": v.machinery_type or '其他',
                "user_name": v.create_uid.name or "",
                "create_date": v.create_date + datetime.timedelta(hours=8),
            })

        output = io.BytesIO()
        workbook = xlsxwriter.Workbook(output, {'in_memory': True})
        worksheet = workbook.add_worksheet('Sheet1')

        header_format = workbook.add_format({
            'bold': True, 'border': 1,
            'fg_color': '#17a2b8', 'font_color': '#FFFFFF',
            'align': 'center', 'valign': 'vcenter'
        })
        date_format = workbook.add_format({'num_format': 'YYYY-MM-DD'})
        datetime_format = workbook.add_format({'num_format': 'YYYY-MM-DD HH:MM'})
        currency_format = workbook.add_format({'num_format': '#,##0.00'})  # 逗号作为千分位分隔符

        worksheet.write(0, 0, "业务日期", header_format)
        worksheet.write(0, 1, "摘要", header_format)
        worksheet.write(0, 2, "客户", header_format)
        worksheet.write(0, 3, "收入", header_format)
        worksheet.write(0, 4, "支出", header_format)
        worksheet.write(0, 5, "结余", header_format)
        worksheet.write(0, 6, "类型", header_format)
        worksheet.write(0, 7, "创建人", header_format)
        worksheet.write(0, 8, "创建时间", header_format)

        for row_num, row_data in enumerate(data):
            worksheet.write_datetime(row_num + 1, 0, row_data.get("business_date"), date_format)
            worksheet.write(row_num + 1, 1, row_data.get("abstract"))
            worksheet.write(row_num + 1, 2, row_data.get("customer"))
            worksheet.write_number(row_num + 1, 3, row_data.get("income"), currency_format)
            worksheet.write_number(row_num + 1, 4, row_data.get("expenditure"), currency_format)
            worksheet.write_number(row_num + 1, 5, row_data.get("balance"), currency_format)
            worksheet.write(row_num + 1, 6, row_data.get("machinery_type"))
            worksheet.write(row_num + 1, 7, row_data.get("user_name"))
            worksheet.write_datetime(row_num + 1, 8, row_data.get("create_date"), datetime_format)

        workbook.close()
        output.seek(0)

        file_name = '财务收支记录.xlsx'
        response = request.make_response(
            None,
            headers=[
                ('Content-Type', 'application/vnd.ms-excel'),
                ('Content-Disposition', content_disposition(file_name))
            ]
        )
        response.stream.write(output.read())
        output.close()
        return response
