import os
from typing import List

from openpyxl import load_workbook
from app.api.concept_office import schema
from app.models.main.concept_office import ConceptOfficeBase, TblConceptOffice
from app.utils.schemas_utils import CustomResponse
from sqlalchemy.orm import Session
from app.dependency.authantication import JWTPayloadSchema
from app.locale.messages import Messages
from fastapi import HTTPException

class ConceptOfficeService:
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    # async def created(self, request:List[schema.ConceptOfficeCreate]):
    #     create = []
    #     for req in request:
    #         created_data = ConceptOfficeBase.model_validate(req.model_dump())
    #         sale = TblConceptOffice.create(created_data, self.db)
    #         create.append(sale)
    #     self.db.commit()
    #     return CustomResponse(status="1", message=Messages.CONCEPT_OFFICE)
    
    async def created(self, request: List[schema.ConceptOfficeCreate]):
        create = []
        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 = workbook["Org Structure"]
        for req in request:
            created = ConceptOfficeBase.model_validate(req.model_dump())
            sale = TblConceptOffice.create(created, self.db)
            self.db.add(sale)
            self.db.flush()
            create.append(sale)
            cid = sale.concept_id
            row_map = {1: 17, 2: 18, 3: 19, 4: 20, 5: 21}
            if cid in row_map:
                row = row_map[cid]
                sheet[f"D{row}"] = float(created.cost_to_company or 0)
                sheet[f"C{row}"] = float(created.y1 or 0)
                sheet[f"F{row}"] = float(created.y2 or 0)
                sheet[f"I{row}"] = float(created.y3 or 0)
                sheet[f"L{row}"] = float(created.y4 or 0)
                sheet[f"O{row}"] = float(created.y5 or 0)
        self.db.commit()
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", os.path.abspath(OUTPUT_PATH))
        return CustomResponse(status="1", message=Messages.CORPORATE_CONCEPT_OFFICE)
    
    async def geted(self, group_id:int):
        geted_data = TblConceptOffice.get(group_id, self.db)
        if not geted_data:
            raise HTTPException(status_code=404, detail="Get group ID not found")
        return [schema.ConceptOfficeResponse.model_validate(get_group) for get_group in geted_data]
    
    
    # async def updated(self, request:List[schema.ConceptOfficeUpdate]):
    #     for req in request:
    #         updated_data = ConceptOfficeBase.model_validate(req.model_dump())
    #         if updated_data.concept_id is None:
    #             return CustomResponse(status="-1", message=Messages.CONCEPT_OFFICE_NOT)
    #         TblConceptOffice.update(updated_data.concept_id, updated_data, self.db)
    #     self.db.commit()
    #     return CustomResponse(status="1", message=Messages.CONCEPT_OFFICE_UPDATE)
    async def updated(self, request: List[schema.ConceptOfficeUpdate]):
        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 = workbook["Org Structure"]
        row_map = {1: 17, 2: 18, 3: 19, 4: 20, 5: 21}
        for req in request:
            updated = ConceptOfficeBase.model_validate(req.model_dump())
            if updated.concept_id is None:
                return CustomResponse(status="-1", message=Messages.CORPORATE_CONCEPT_OFFICE_NOT)
            TblConceptOffice.update(updated.concept_id, updated, self.db)
            cid = updated.concept_id
            if cid in row_map:
                row = row_map[cid]
            else:
                row = sheet.max_row + 1
            sheet[f"D{row}"] = float(updated.cost_to_company or 0)
            sheet[f"C{row}"] = float(updated.y1 or 0)
            sheet[f"F{row}"] = float(updated.y2 or 0)
            sheet[f"I{row}"] = float(updated.y3 or 0)
            sheet[f"L{row}"] = float(updated.y4 or 0)
            sheet[f"O{row}"] = float(updated.y5 or 0)
        self.db.commit()
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", os.path.abspath(OUTPUT_PATH))
        return CustomResponse(status="1", message=Messages.CORPORATE_CONCEPT_OFFICE_UPDATE)
    
    async def deleted(self, corporate_id:int):
        deleted_data = TblConceptOffice.delete(corporate_id, self.db)
        if not deleted_data:
            return CustomResponse(status="-1", message=Messages.CONCEPT_OFFICE_NOT)
        return CustomResponse(status="1", message=Messages.CONCEPT_OFFICE_DELETE)