import os
from typing import List
from openpyxl import load_workbook
from sqlalchemy.orm import Session
from app.api.online_store_format import schema
from app.dependency.authantication import JWTPayloadSchema
from app.models.main.online_store_format import OnlineStoreFormatBase, TblOnlineStoreFormat
from app.utils.schemas_utils import CustomResponse
from app.locale.messages import Messages
from fastapi import HTTPException

class OnlineStoreFormatService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    async def created(self, request:List[schema.OnlineStoreFormatCreate]):
        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).")
        for req in request:
            created_data = OnlineStoreFormatBase.model_validate(req.model_dump())
            TblOnlineStoreFormat.create(created_data, self.db)
            self.db.commit()
            if req.metric == "Investment & Capital Expenses":
                sheet_a = workbook["Plan Working Online"]
                sheet_a["F11"] = created_data.y1_y2
                sheet_a["H11"] = created_data.y3_y4
                sheet_a["J11"] = created_data.y5_y6
            elif req.metric == "Number of Transactions":
                sheet_b = workbook["Plan Working Online"]
                sheet_b["F33"] = created_data.y1_y2
                sheet_b["H33"] = created_data.y3_y4
                sheet_b["J33"] = created_data.y5_y6
            elif req.metric == "Average Transaction Value":
                sheet_c = workbook["Plan Working Online"]
                sheet_c["F35"] = created_data.y1_y2
                sheet_c["H35"] = created_data.y3_y4
                sheet_c["J35"] = created_data.y5_y6
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.ONLINE_STORE_FORMAT)
    
    async def geted(self, group_id:int):
        geted_data = TblOnlineStoreFormat.get(group_id, self.db)
        if not geted_data:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.OnlineStoreFormatResponse.model_validate(get_group) for get_group in geted_data]
    
    async def updated(self, request: List[schema.OnlineStoreFormatUpdate]):
        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).")
        for req in request:
            updated_data = OnlineStoreFormatBase.model_validate(req.model_dump())
            if updated_data.online_id is None:
                return CustomResponse(status="-1", message=Messages.ONLINE_STORE_FORMAT_NOT)
            db_obj = TblOnlineStoreFormat.update(updated_data.online_id, updated_data, self.db)
            if req.metric == "Investment & Capital Expenses":
                sheet = workbook["Plan Working Online"]
                sheet["F11"] = db_obj.y1_y2
                sheet["H11"] = db_obj.y3_y4
                sheet["J11"] = db_obj.y5_y6

            elif req.metric == "Number of Transactions":
                sheet = workbook["Plan Working Online"]
                sheet["F33"] = db_obj.y1_y2
                sheet["H33"] = db_obj.y3_y4
                sheet["J33"] = db_obj.y5_y6

            elif req.metric == "Average Transaction Value":
                sheet = workbook["Plan Working Online"]
                sheet["F35"] = db_obj.y1_y2
                sheet["H35"] = db_obj.y3_y4
                sheet["J35"] = db_obj.y5_y6

        workbook.save(OUTPUT_PATH)
        workbook.close()

        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.ONLINE_STORE_FORMAT_UPDATE)
        
    async def deleted(self, online_id:int):
        deleted_data = TblOnlineStoreFormat.delete(online_id, self.db)
        if not deleted_data:
            return CustomResponse(status="-1", message=Messages.ONLINE_STORE_FORMAT_NOT)
        return CustomResponse(status="1", message=Messages.ONLINE_STORE_FORMAT_DELETE)