import os
from typing import List
from fastapi import HTTPException
from openpyxl import load_workbook
from sqlalchemy.orm import Session
import app.api.customer_location.schemas as schemas
from app.locale.messages import Messages
from app.models.main.customer_location import CustomerLocationBase,TblCustomerLocation
from app.utils.common_utils import calculate_total_household_consumption, fill_and_calculate_total_household_consumption, remove_external_links, safe_load_workbook
from app.utils.schemas_utils import CustomResponse

class Customer_location:
    def __init__(self, db: Session, token):
        self.db = db
        self.token = token

    async def create_customer_location(self, request: List[schemas.customer_location_creat]):
        for req in request:
            created_user = CustomerLocationBase.model_validate(req.model_dump())
            created_user.total_household_consumption_per_month = calculate_total_household_consumption(created_user)
            TblCustomerLocation.create(created_user, 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_a["K11"] = req.income_levels
            sheet_b = workbook["Assu Sum Mod B"]
            sheet_b["K11"] = req.income_levels
            workbook.save(OUTPUT_PATH)
            workbook.close()
            print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.CUSTOMER_CREAT)
    
    async def get_customer_location(self, customer_location_id: int):
        catchment = TblCustomerLocation.get_by_id(customer_location_id, self.db)
        if not catchment:
            raise HTTPException(status_code=404,detail=Messages.CUSTOMET_NOT_FOUND)
        return schemas.customerLocationResponse.model_validate(catchment)
    

    async def get_customer_locations_by_group(self, group_id: int):
        customer_locations = (self.db.query(TblCustomerLocation).filter(TblCustomerLocation.group_id == group_id).all())
        return [schemas.customerLocationResponse.model_validate(loc) for loc in customer_locations]
    
    async def get_total_potential_by_group(self, group_id: int) -> schemas.TotalConsumptionResponse:
        locations = (self.db.query(TblCustomerLocation).filter(TblCustomerLocation.group_id == group_id).all())
        if not locations:
            raise HTTPException(status_code=404, detail=Messages.CUSTOMER_GROUP_NOT_FOUND)
        total_consumption = sum(loc.shopping_frequency_per_month * loc.household_consumption_per_month for loc in locations)
        return schemas.TotalConsumptionResponse(group_id=group_id,total_household_consumption_per_month=total_consumption)

    async def update_customer_location(self, request: schemas.customerLocationUpdate):
        if request.customer_location_id is None:
            raise HTTPException(status_code=404, detail=Messages.CUSTOMER_ID_REQUIRED)
        existing = TblCustomerLocation.get_by_id(request.customer_location_id, self.db)
        if not existing:
            raise HTTPException(status_code=404, detail=Messages.CUSTOMET_NOT_FOUND)
        update_data = request.model_dump(exclude_unset=True)
        update_data = fill_and_calculate_total_household_consumption(update_data, existing)
        validated_model = CustomerLocationBase(**update_data)
        assert validated_model.customer_location_id is not None
        TblCustomerLocation.update(validated_model.customer_location_id, validated_model, 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_a["K11"] = request.income_levels
        sheet_b = workbook["Assu Sum Mod B"]
        sheet_b["K11"] = request.income_levels
        workbook.save(OUTPUT_PATH)
        workbook.close()
        print("✅ Excel updated successfully at:", OUTPUT_PATH)
        return CustomResponse(status="1", message=Messages.CUSTOMER_UPDATE)
    
    async def delete_customer_location(self, customer_location_id: int):
        deleted = TblCustomerLocation.delete(customer_location_id, self.db)
        if not deleted:
            raise HTTPException(status_code=404,detail=Messages.CUSTOMET_NOT_FOUND)
        return CustomResponse(status="1", message=Messages.CUSTOMER_DELETED)