import os
import shutil
from fastapi.responses import FileResponse
import pandas as pd
from fastapi import UploadFile, HTTPException
from sqlalchemy.orm import Session
from app.dependency.authantication import JWTPayloadSchema
from app.models.main.group import TblGroup
from app.models.main.student import TblStudent


UPLOAD_DIR = "uploads"
ALLOWED_EXTENSIONS = {".png", ".jpg", ".jpeg", ".svg"}
MAX_FILE_SIZE_MB = 50
os.makedirs(UPLOAD_DIR, exist_ok=True)

class CorporateStoreService:
    
    def __init__(self, db:Session, token:JWTPayloadSchema):
        self.db = db
        self.token = token
        
    def validate_file(file: UploadFile):
        ext = os.path.splitext(file.filename)[1].lower()
        if ext not in ALLOWED_EXTENSIONS:
            raise HTTPException(status_code=400,detail=f"❌ Unsupported file format: {ext}. "f"Allowed formats: PNG, JPG, JPEG, SVG.")
        
    async def upload_corporate_and_concept_level_organizational(self, file: UploadFile, group_id: int):
    # Validate file
        CorporateStoreService.validate_file(file)
        contents = await file.read()
        size_mb = len(contents) / (1024 * 1024)
        if size_mb > MAX_FILE_SIZE_MB:
            raise HTTPException(
                status_code=400,
                detail=f"❌ File size {size_mb:.2f} MB exceeds maximum {MAX_FILE_SIZE_MB} MB."
            )
        await file.seek(0)

        # If group_id passed → use it, otherwise fallback to student's group
        if group_id:
            group = self.db.query(TblGroup).filter(TblGroup.group_id == group_id).first()
            if not group:
                raise HTTPException(status_code=404, detail="Group not found")
        else:
            student_id = getattr(self.token, "student_id", None)
            if student_id is None:
                raise HTTPException(status_code=400, detail="student_id missing in token")

            student = self.db.query(TblStudent).filter(TblStudent.student_id == student_id).first()
            if not student or not student.group:
                raise HTTPException(status_code=404, detail="Group not assigned")
            group = student.group

        # Delete old file
        old_file_path = group.corporate_and_concept_level_organisational_chart
        if old_file_path and os.path.exists(old_file_path):
            os.remove(old_file_path)

        # Save new file
        save_path = os.path.join(UPLOAD_DIR, file.filename)
        with open(save_path, "wb") as buffer:
            shutil.copyfileobj(file.file, buffer)

        # Update DB
        group.corporate_and_concept_level_organisational_chart = save_path
        self.db.commit()
        self.db.refresh(group)

        return {
            "message": "File uploaded successfully!",
            "group_id": group.group_id,
            "filename": save_path,
            "db_path": group.corporate_and_concept_level_organisational_chart
        }

        
    async def get_image_corporate(self, group_id:int):
        result = self.db.query(TblGroup.corporate_and_concept_level_organisational_chart).filter(TblGroup.group_id == group_id).first()
        if not result or not result[0]:
            return {"error": "No image found for this group"}

        image_url = result[0]  # first element of tuple
        return {
            "group_id" : group_id,
            "image_url" : image_url
            }
        


    async def upload_store_level_organization_chart(self, file: UploadFile, group_id:int):
        # Validate file
        CorporateStoreService.validate_file(file)
        contents = await file.read()
        size_mb = len(contents) / (1024 * 1024)
        if size_mb > MAX_FILE_SIZE_MB:
            raise HTTPException(
                status_code=400,
                detail=f"❌ File size {size_mb:.2f} MB exceeds maximum {MAX_FILE_SIZE_MB} MB."
            )
        await file.seek(0)

        # If group_id passed → use it, otherwise fallback to student's group
        if group_id:
            group = self.db.query(TblGroup).filter(TblGroup.group_id == group_id).first()
            if not group:
                raise HTTPException(status_code=404, detail="Group not found")
        else:
            student_id = getattr(self.token, "student_id", None)
            if student_id is None:
                raise HTTPException(status_code=400, detail="student_id missing in token")

            student = self.db.query(TblStudent).filter(TblStudent.student_id == student_id).first()
            if not student or not student.group:
                raise HTTPException(status_code=404, detail="Group not assigned")
            group = student.group

        # Delete old file if exists
        old_file_path = group.store_level_organisational_chart
        if old_file_path and os.path.exists(old_file_path):
            os.remove(old_file_path)

        # Save new file
        save_path = os.path.join(UPLOAD_DIR, file.filename)
        with open(save_path, "wb") as buffer:
            shutil.copyfileobj(file.file, buffer)

        # Update DB
        group.store_level_organisational_chart = save_path
        self.db.commit()
        self.db.refresh(group)

        return {
            "message": "File uploaded and old file replaced successfully!",
            "group_id": group.group_id,
            "filename": save_path,
            "db_path": group.store_level_organisational_chart
        }

        
    async def get_image_store_level(self, group_id: int):
        # Fetch the image URL stored in DB
        result = self.db.query(TblGroup.store_level_organisational_chart).filter(
            TblGroup.group_id == group_id
        ).first()

        if not result or not result[0]:
            return {"error": "No image found for this group"}

        image_url = result[0]  # first element of tuple
        return {
            "group_id" : group_id,
            "image_url" : image_url
            }