import datetime

import pandas as pd
import xlsxwriter
from odoo import http, tools
from odoo.http import content_disposition, request
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
        data_list = _self.jsonrequest.get("data_list", [])
        for v in data_list:
            data = {
                "business_date": v.get("business_date", False),
                "abstract": v.get("abstract", False),
                "income": v.get("income", False),
                "machinery_type": v.get("machinery_type", "其他"),
                "expenditure": v.get("expenditure", False),
                "customer": v.get("customer", False)
            }
            # 看是否有id，如果有的话就说明是更新，没有的话就说明是创建
            if v.get("id", False):
                expense_obj = _self.env["roke.product.income.expense"].sudo().search([("id", "=", v.get("id"))])
                if not expense_obj:
                    return {"code": 1, "message": "更新失败，没找到对应数据。"}
                expense_obj.write(data)
            else:
                _self.env(user=v.get("user_id"))["roke.product.income.expense"].create(data)
        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 business_date DESC, create_date DESC
            LIMIT %s OFFSET %s
        """
        params.extend([limit, offset])

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

        # 查询总数
        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}

    @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
