



from datetime import datetime
from io import BytesIO

from typing import Dict, List, Optional
from fastapi import APIRouter, Depends, File, Form, HTTPException, Query, UploadFile
import pandas as pd
from sqlalchemy import select

from src.constants import PeriodType
from src.HRAndCRM.models import CRM
from .schema import CRMBase, CRMUpdate, HRBase, HRUpdate
from sqlalchemy.orm import Session
from src.core.database import get_db, Base, engine
from src.HRAndCRM.service import CRMDataHandler, HRDataHandler, IntegrationService, get_date_range, get_payroll_data, prepare_payroll_report
from src.common.dependencies import get_current_company_user
from src.core.database import get_db_on_the_fly
from sqlalchemy.ext.asyncio import AsyncSession

from src.schemas.compnay_user import CurrentCompanyUser
import json
HR_router = APIRouter(prefix="/company/HR_and_CRM", tags=["HR and CRM module"])

import logging
import time
import json
from io import BytesIO
from typing import Dict
from fastapi import UploadFile, File, Form, HTTPException, Depends
from sqlalchemy.ext.asyncio import AsyncSession
import pandas as pd

from sqlalchemy import Column, Integer, String
from src.core.database import Base


logger = logging.getLogger(__name__)
async def extract_excel_df(
    file: UploadFile,
    column_mapping: str,  # or dict actually
    *,
    logger,
    field_name: str = "file"
) -> dict:
    overall_start = time.time()

    # Parse column_mapping if needed only once
    if isinstance(column_mapping, str):
        try:
            mapping = json.loads(column_mapping)
        except json.JSONDecodeError:
            raise HTTPException(status_code=400, detail="Invalid JSON in column_mapping.")
    elif isinstance(column_mapping, dict):
        mapping = column_mapping
    else:
        raise ValueError(f"Invalid column_mapping type: {type(column_mapping)}")

    if not file.filename.lower().endswith(".xlsx"):
        raise HTTPException(status_code=400, detail="Invalid file type. Please upload an Excel (.xlsx) file.")

    contents = await file.read()
    logger.info(f"{field_name} read in {time.time() - overall_start:.2f}s")

    try:
        df = pd.read_excel(BytesIO(contents), engine="openpyxl")
    except Exception as e:
        raise HTTPException(status_code=400, detail=f"Error reading Excel file: {e}")

    logger.info(f"{field_name} columns: {df.columns.tolist()}")
    logger.info(f"{field_name} preview:\n{df.head(5)}")
    logger.info(f"Extraction total time: {time.time() - overall_start:.2f}s")

    return {"df": df, "mapping": mapping}


@HR_router.post("/HR_data/")
async def upload_hr_data(
    file: UploadFile = File(...),
     column_mapping: dict = Form(...),
    db: AsyncSession = Depends(get_db_on_the_fly),
    user = Depends(get_current_company_user)
):
    try:

        extract = await extract_excel_df(file, column_mapping, logger=logger, field_name="HR file")

        # Validate early to fail fast
        validation_mask = HRDataHandler.validate_dataframe(extract["df"])
        if not validation_mask.any():
            raise HTTPException(status_code=400, detail="All rows are invalid after validation.")

        result = await HRDataHandler.file_hr_data_upload(
            df=extract["df"],
            column_mapping=extract["mapping"],
            db=db,
            user_id=user.user_id
        )
        return result
    except HTTPException as he:
        logger.error(f"HR upload failed: {he.detail}")
        raise he
    except Exception as e:
        logger.exception("Unexpected error during HR data upload")
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")


@HR_router.post("/crm/upload/")
async def upload_crm_data(
    file: UploadFile = File(...),
    column_mapping: str = Form(...),
    db: AsyncSession = Depends(get_db_on_the_fly),
    user=Depends(get_current_company_user)
):
    try:
        import json

        if isinstance(column_mapping, dict):
            mapping_dict = column_mapping
        elif isinstance(column_mapping, str):
            mapping_dict = json.loads(column_mapping)
        else:
            raise HTTPException(status_code=400, detail="Invalid column_mapping format")

        logger.debug(f"Parsed column_mapping: {mapping_dict}")

        extract = await extract_excel_df(file, mapping_dict, logger=logger, field_name="CRM file")
        df = extract["df"]

        valid_mask = CRMDataHandler.validate_dataframe(df)
        if valid_mask.sum() == 0:
            raise HTTPException(status_code=400, detail="All rows are invalid after validation.")

        result = await CRMDataHandler.file_crm_data_upload(
            df=df.loc[valid_mask],
            column_mapping=None,  # already renamed
            db=db,
            user_id=user.user_id
        )

        if not result["success"]:
            raise HTTPException(status_code=400, detail=result["detail"])

        return result

    except HTTPException as he:
        logger.error(f"CRM upload failed: {he.detail}")
        raise he
    except Exception as e:
        logger.exception("Unexpected error during CRM data upload")
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")

from typing import Dict, Any

@HR_router.get("/hr/details/", response_model=Dict[str, Any])
async def list_employees(
    page: int = 1,
    limit: int = 10,
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await HRDataHandler.get_all_employees_paginated(db, page, limit)



@HR_router.get("/hr/details/{employee_id}", response_model=HRBase)
async def get_hr(employee_id: str, db: AsyncSession = Depends(get_db_on_the_fly)):
    employee = await HRDataHandler.get_employee_by_id(employee_id, db)
    if not employee:
        raise HTTPException(status_code=404, detail="Employee not found")
    return employee

@HR_router.delete("/hr/details/{employee_id}")
async def delete_hr(employee_id: str, db: AsyncSession = Depends(get_db_on_the_fly)):
    await HRDataHandler.delete_employee(employee_id, db)
    return {"message": "Employee deleted"}

@HR_router.put("/hr/details/{employee_id}", response_model=HRBase)
async def update_hr(employee_id: str, data: HRUpdate, db: AsyncSession = Depends(get_db_on_the_fly)):
    update_data = data.dict(exclude_unset=True)
    employee = await HRDataHandler.update_employee(employee_id, update_data, db)
    if not employee:
        raise HTTPException(status_code=404, detail="Employee not found")
    return employee


### CRM APIs ###

@HR_router.get("/crm/details/", response_model=Dict[str, Any])
async def list_crm(
    page: int = 1,
    limit: int = 10,
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await CRMDataHandler.get_all_opportunities_paginated(db, page, limit)


@HR_router.get("/crm/details/{opportunity_id}", response_model=CRMBase)
async def get_crm(opportunity_id: str, db: AsyncSession = Depends(get_db_on_the_fly)):
    opportunity = await CRMDataHandler.get_CRM_by_id(opportunity_id, db)
    if not opportunity:
        raise HTTPException(status_code=404, detail="Opportunity not found")
    return opportunity

@HR_router.delete("/crm/details/{opportunity_id}")
async def delete_crm(opportunity_id: str, db: AsyncSession = Depends(get_db_on_the_fly)):
    await CRMDataHandler.delete_CRM(opportunity_id, db)
    return {"message": "Opportunity deleted"}

@HR_router.put("/crm/details/{opportunity_id}", response_model=CRMBase)
async def update_crm(opportunity_id: str, data: CRMUpdate, db: AsyncSession = Depends(get_db_on_the_fly)):
    update_data = data.dict(exclude_unset=True)
    opportunity = await CRMDataHandler.update_CRM(opportunity_id, update_data, db)
    if not opportunity:
        raise HTTPException(status_code=404, detail="Opportunity not found")
    return opportunity



# ---------------- CRM Integration History Routes ----------------

@HR_router.get("/crm/integration-history")
async def list_crm_integration_history(
    page: int = Query(1, ge=1),
    limit: int = Query(100, ge=1),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await IntegrationService.get_crm_history(page, limit, db)


@HR_router.get("/crm/integration-history/{integration_id}/entries")
async def list_crm_entries_by_integration_id(
    integration_id: str,
    page: int = Query(1, ge=1),
    limit: int = Query(20, le=100),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await IntegrationService.get_crm_entries_paginated(integration_id, page, limit, db)


@HR_router.delete("/crm/integration-history/{integration_id}")
async def delete_crm_integration(
    integration_id: str,
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await IntegrationService.delete_crm_history(integration_id, db)


# ---------------- HR Integration History Routes ----------------

@HR_router.get("/hr/integration-history")
async def list_hr_integration_history(
    page: int = Query(1, ge=1),
    limit: int = Query(100, ge=1),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    #return {"hello":"jee"}
    return await IntegrationService.get_hr_history(page, limit, db)


@HR_router.get("/hr/integration-history/{integration_id}/entries")
async def list_get_hr_entries_integration_id(
    integration_id: str,
    page: int = Query(1, ge=1),
    limit: int = Query(20, le=100),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await IntegrationService.get_hr_entries_paginated(integration_id, page, limit, db)

@HR_router.delete("/hr/integration-history/{integration_id}")
async def delete_hr_integration(
    integration_id: str,
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    return await IntegrationService.delete_hr_history(integration_id, db)

@HR_router.get("/api/v1/payroll/report")
async def full_payroll_report(
    year: int = datetime.now().year,
    period: PeriodType = Query(default="yearly"),
    custom_start: Optional[datetime] = Query(default=None),
    custom_end: Optional[datetime] = Query(default=None),
    entity: Optional[str] = Query(default=None),
    hierarchy: Optional[str] = Query(default=None),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    
    start = time.perf_counter()
    payroll_data = await get_payroll_data(
        db=db,
        year=year,
        period=period,
        custom_start=custom_start,
        custom_end=custom_end,
        entity=entity,
        hierarchy=hierarchy
    )
    end = time.perf_counter()
    print(f"Elapsed time: {end - start} seconds")
    print(payroll_data)
    report = prepare_payroll_report(payroll_data, months=12)
    return {
        "year": year,
        "report": report
    }





from fastapi import Query
from typing import Optional, List, Dict
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from datetime import datetime

# Example: you could load these from config or DB later
DEFAULT_STAGE_ORDER = [
    "Leads",
    "Qualified (MQL)",
    "Opportunity (SQL)",
    "Proposal Sent",
    "Negotiation",
    "Closed Won"
]

DEFAULT_STAGE_MAPPING = {
    "Lead": "Leads",
    "Qualified": "Qualified (MQL)",
    "Opportunity": "Opportunity (SQL)",
    "Proposal": "Proposal Sent",
    "Negotiation": "Negotiation",
    "Closed Won": "Closed Won"
}

def map_stage(stage: str, mapping: Dict[str, str]) -> str:
    """Map raw CRM stages to funnel stages using a dynamic mapping."""
    return mapping.get(stage, stage)

import json
from typing import Optional, List, Dict
from datetime import datetime
from fastapi import Query, HTTPException

logger = logging.getLogger(__name__)

DEFAULT_STAGE_ORDER = [
    "Leads",
    "Qualified (MQL)",
    "Opportunity (SQL)",
    "Proposal Sent",
    "Negotiation",
    "Closed Won"
]

DEFAULT_STAGE_MAPPING = {
    "Lead": "Leads",
    "Qualified": "Qualified (MQL)",
    "Opportunity": "Opportunity (SQL)",
    "Proposal": "Proposal Sent",
    "Negotiation": "Negotiation",
    "Closed Won": "Closed Won"
}

def map_stage(stage: str, mapping: dict) -> str:
    return mapping.get(stage, stage)

@HR_router.get("/api/sales-funnel-metrics")
async def sales_funnel_metrics(
    period: str = Query(default="yearly", regex="^(daily|weekly|monthly|quarterly|yearly|all|custom)$"),
    custom_start: Optional[datetime] = Query(default=None),
    custom_end: Optional[datetime] = Query(default=None),
    entity: Optional[str] = Query(default=None),
    hierarchy: Optional[str] = Query(default=None),
    stage_order: Optional[List[str]] = Query(default=DEFAULT_STAGE_ORDER),
    stage_mapping_str: Optional[str] = Query(default=None),
    db: AsyncSession = Depends(get_db_on_the_fly)
):
    try:
        if stage_mapping_str:
            try:
                stage_mapping = json.loads(stage_mapping_str)
            except json.JSONDecodeError:
                raise HTTPException(status_code=400, detail="Invalid JSON for stage_mapping")
        else:
            stage_mapping = DEFAULT_STAGE_MAPPING

        result = await db.execute(select(CRM))
        opportunities = result.scalars().all()

        #start_date, end_date = get_date_range(period,year, custom_start, custom_end)

        def filter_opp(opp):
            if entity and getattr(opp, "entity", None) != entity:
                return False
            if hierarchy and getattr(opp, "hierarchy", None) != hierarchy:
                return False
            # if start_date and end_date:
            #     created_date = getattr(opp, "created_at", None)
            #     if not created_date or not (start_date <= created_date.date() <= end_date):
            #         return False
            return True

        filtered_opps = [opp for opp in opportunities if filter_opp(opp)]

        stage_counts = {stage: 0 for stage in stage_order}
        stage_dates = []

        for opp in filtered_opps:
            stage = map_stage(opp.stage, stage_mapping)
            if stage in stage_counts:
                stage_counts[stage] += 1

            if stage == "Closed Won" and opp.created_at and opp.expected_close_date:
                delta = (opp.expected_close_date - opp.created_at).days
                stage_dates.append(delta)

        funnel = []
        previous_count = None
        for stage in stage_order:
            count = stage_counts.get(stage, 0)
            conversion = f"{(count / previous_count * 100):.1f}%" if previous_count and previous_count > 0 else "N/A"
            funnel.append({
                "stage": stage,
                "count": count,
                "conversion_rate": conversion
            })
            previous_count = count

        total_leads = stage_counts.get(stage_order[0], 0) if stage_order else 0
        total_won = stage_counts.get("Closed Won", 0) if "Closed Won" in stage_counts else 0
        overall_conversion = f"{(total_won / total_leads * 100):.1f}%" if total_leads > 0 else "0%"

        avg_sales_cycle = round(sum(stage_dates) / len(stage_dates), 1) if stage_dates else 0

        return {
            "funnel_stages": funnel,
            "key_metrics": {
                "total_leads": total_leads,
                "total_won": total_won,
                "overall_conversion_rate": overall_conversion,
                "average_sales_cycle_days": avg_sales_cycle
            }
        }

    except Exception as e:
        logger.exception("Error computing sales funnel metrics")
        raise HTTPException(status_code=500, detail=f"Internal server error: {str(e)}")
    

