import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy.orm import Session
from app.api.sales import schema
from app.dependency.authantication import JWTPayloadSchema
from app.models.main.sales import SalesBase, TblSales
from app.utils.common_utils import remove_external_links, safe_load_workbook
from app.utils.schemas_utils import CustomResponse
from app.locale.messages import Messages
from fastapi import HTTPException

class SalesService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
    
    async def create_sales(self, request: List[schema.SalesCreate]):
        created_sales = []
        for item in request:
            sale = TblSales.create_sales(item, self.db)
            created_sales.append(sale)
        self.db.commit()
        OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        workbook = safe_load_workbook(OUTPUT_PATH, EXCEL_PATH)
        remove_external_links(workbook)
        workbook.save(OUTPUT_PATH)
        # EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        # OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        # os.makedirs("uploaded_files", exist_ok=True)
        # workbook = load_workbook(EXCEL_PATH)
        # if os.path.exists(OUTPUT_PATH):
        #     workbook = load_workbook(OUTPUT_PATH)
        #     # print("📂 Loaded existing output file to keep previous data.")
        # else:
        #     workbook = load_workbook(EXCEL_PATH)
        #     # print("🆕 Loaded clean template (first run).")
        sheet_a = workbook["Assu Sum Mod A"]
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_online = workbook["Assu Sum Mod Online"]
        last_item = request[-1]

        sheet_a["K10"] = last_item.cogs
        sheet_b["K10"] = last_item.cogs

        sheet_online["G6"] = last_item.average_traffic_expected * 12
        sheet_online["G7"] = last_item.number_of_transaction_per_month * 12
        avg_val = float(last_item.average_transaction_value or 0)
        sheet_online["G8"] = (avg_val * 12) / 100000
        sheet_online["G9"] = last_item.cogs * 12
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.SALES)

    async def get_sales(self, group_id:int):
        new_get_sales = TblSales.get_sales(group_id, self.db)
        if not new_get_sales:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.SalesResponse.model_validate(get_group) for get_group in new_get_sales]
    
    async def get_multiple(self, get_id:int) ->"TblSales":
        # get_multiple_data = self.db.query(TblSales.average_transaction_value * TblSales.number_of_transaction_per_month).label("table").filter(TblSales.sales_id == get_id).first()
        get_multiple_data = (
            self.db.query(
                (TblSales.average_transaction_value * TblSales.number_of_transaction_per_month).label("table")
            )
            .filter(TblSales.sales_id == get_id)
            .first()
        )
        if not get_multiple_data:
            raise HTTPException(status_code=404, detail="ID not found")
        # return get_multiple_data
        return {"total": get_multiple_data.table}
    
    async def update_sales(self, request:List[schema.SalesUpdate]):
        for req in request:
            updated_sales = SalesBase.model_validate(req.model_dump())
            if updated_sales.sales_id is None:
                return CustomResponse(status="-1", message=Messages.SALES_NOT)
            TblSales.update_sales(updated_sales.sales_id, updated_sales, self.db)
        self.db.commit()
        OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        workbook = safe_load_workbook(OUTPUT_PATH, EXCEL_PATH)
        remove_external_links(workbook)
        workbook.save(OUTPUT_PATH)
        # EXCEL_PATH = "files/Simulation_Test_file_clean_final.xlsx"
        # OUTPUT_PATH = "uploaded_files/Simulation_Test_file_clean_final_output.xlsx"
        # os.makedirs("uploaded_files", exist_ok=True)
        # workbook = load_workbook(EXCEL_PATH)
        # if os.path.exists(OUTPUT_PATH):
        #     workbook = load_workbook(OUTPUT_PATH)
        #     # print("📂 Loaded existing output file to keep previous data.")
        # else:
        #     workbook = load_workbook(EXCEL_PATH)
        #     # print("🆕 Loaded clean template (first run).")
        sheet_a = workbook["Assu Sum Mod A"]
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_online = workbook["Assu Sum Mod Online"]
        last_item = request[-1]
        sheet_a["K10"] = last_item.cogs
        sheet_b["K10"] = last_item.cogs
        sheet_online["G6"] = last_item.average_traffic_expected * 12
        sheet_online["G7"] = last_item.number_of_transaction_per_month * 12
        avg_val = float(last_item.average_transaction_value or 0)
        sheet_online["G8"] = (avg_val * 12) / 100000
        sheet_online["G9"] = last_item.cogs * 12
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.SALES_UPDATE)
    
    async def delete_sales(self, sales_id:int):
        deleted_sales = TblSales.delete_sales(sales_id, self.db)
        if not deleted_sales:
            return CustomResponse(status="-1", message=Messages.SALES_NOT)
        return CustomResponse(status="1", message=Messages.SALES_DELETE)