from fastapi import APIRouter, Depends, File, UploadFile
from sqlalchemy.orm import Session
from typing import Annotated, List
from app.api.network.schemas import NetworkPlanningCreate,NetworkPlanningResponse,NetworkPlanningUpdate
from app.api.network.service import NetworkPlanningService
from app.database.main.mysql import get_db
from app.dependency.authantication import JWTPayloadSchema, get_current_student
from fastapi import HTTPException
import pandas as pd
from app.models.main.group_data import TblGroupData

network_planning_router = APIRouter()

@network_planning_router.post("/create_network_plan", response_model_exclude_none=True)
async def create_network_plan(request:List[NetworkPlanningCreate],db: Annotated[Session, Depends(get_db)],token: Annotated[JWTPayloadSchema, Depends(get_current_student)],):
    return await NetworkPlanningService(db, token).create_network_plan(request)

@network_planning_router.get("/create_network_plan/get/{group_id}",response_model_exclude_none=True)
async def get_simulation(group_id: int, db: Session = Depends(get_db), token: JWTPayloadSchema = Depends(get_current_student)):
    return await NetworkPlanningService(db, token).get_network(group_id)

@network_planning_router.get("/create_network_plan/{group_id}/total_network",response_model=List[NetworkPlanningResponse],response_model_exclude_none=True)
async def get_total_consumption_by_group(group_id: int,db: Session = Depends(get_db),token: JWTPayloadSchema = Depends(get_current_student),):
    return await NetworkPlanningService(db, token).get_network_by_group(group_id)

@network_planning_router.put("/update_network_plan", response_model_exclude_none=True)
async def update_network_plan(request: NetworkPlanningUpdate,db: Annotated[Session, Depends(get_db)],token: Annotated[JWTPayloadSchema, Depends(get_current_student)],):
    return await NetworkPlanningService(db, token).update_network_plan(request)

@network_planning_router.post("/upload_group_data_excel/")
async def upload_excel(file: UploadFile = File(...), db: Session = Depends(get_db)):
    if not file.filename.endswith(('.xls', '.xlsx')):
        raise HTTPException(status_code=400, detail="Only Excel files are allowed")

    df = pd.read_excel(file.file)
    df.columns = [col.strip() for col in df.columns]

    for _, row in df.iterrows():
        clean_row = row.where(pd.notnull(row), None)

     
        group_no = clean_row.get("GROUP NO")
        if group_no:
            group_no = str(group_no)[:100]

        db.add(TblGroupData(
            group_no=group_no,
            format_type=clean_row.get("FORMAT TYPE"),
            reference_retailer=clean_row.get("REFERENCE RETAILER"),
            no_of_stores=clean_row.get("NO OF STORES"),
            indicative_categories=clean_row.get("Indicative categories"),
            no_of_sub_cats_l=clean_row.get("No of Sub Cats(L)"),
            no_of_sub_cats_m=clean_row.get("No of Sub Cats(M)"),
            no_of_sub_cats_h=clean_row.get("No of Sub Cats(H)"),
            indicative_size_sqft=clean_row.get("Indicative Size [Sqaure Feet]"),
            indicative_capital_rs_cr=clean_row.get("Indicative Capital Available (O*50*0.6/100) [Rs in Crores]"),
            approx_investment_per_store_lakhs=clean_row.get("Approx Investment  Per store [in Rupees Lakhs]"),
            competition_intensity_l=clean_row.get("Competition intensity L"),
            competition_intensity_m=clean_row.get("Competition intensity M"),
            competition_intensity_h=clean_row.get("Competition intensity H"),
            tech_investment_h_lakhs=clean_row.get("Technology Investment in Rs.Lakhs(H)"),
            tech_investment_m_lakhs=clean_row.get("Technology Investment in Rs.Lakhs(M)"),
            tech_investment_l_lakhs=clean_row.get("Technology Investment in Rs.Lakhs(L)"),
            spillage_factor_h=clean_row.get("Spillage Factor (H)"),
            spillage_factor_l=clean_row.get("Spillage Factor (L)")
        ))

    db.commit()
    return {"status": "1", "message": "Excel data uploaded successfully"}

@network_planning_router.delete("/delete_network_plan", response_model_exclude_none=True)
async def delete_network_plan(network_id:int, db:Session = Depends(get_db), token:JWTPayloadSchema = Depends(get_current_student)):
    return await NetworkPlanningService(db,token).delete_network_plan(network_id)



# @network_planning_router.get("/group/{group_id}/main-data", response_model=MainDataSchema)
# def get_main_data_by_group(group_id: int, db: Session = Depends(get_db)):
#     # 1. Get the group
#     group = db.query(TblGroup).filter(TblGroup.group_id == group_id).first()
#     if not group:
#         raise HTTPException(status_code=404, detail="Group not found")

#     # 2. Determine category
#     category = group.category_assigned or group.category
#     if not category:
#         raise HTTPException(status_code=400, detail="Group has no category assigned")

#     # 3. Get the main data
#     main_data = db.query(TblMainData).filter(TblMainData.format_type == category).first()
#     if not main_data:
#         raise HTTPException(status_code=404, detail="Main data not found for this category")

#     # 4. Return response
#     return main_data\