from datetime import datetime, timedelta, timezone, date
from typing import List, Optional
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import delete, func, select
import pyotp
from fastapi import HTTPException, status

from src.Service.applicationservice import ApplicationService
from src.Service.companyService import CompanyService
from .schemas import BlogCreate, BlogUpdate, SubscriptionPlanCreate, UpdateSubscriptionPlanCreate
from src.models.company_user import User
from src.models.company import BillingCycle, Blog, BlogStatusEnum, Company, PlanModule
from src.models.application_user import ApplicationUser
from src.constants import UserType, CompanyPlanEnum

from src.external.email import transporter

#local utils
from .utils import (
encrypt_reset_payload,
decrypt_reset_token,
generate_qr_code_image,
create_jwt_access_token,
generate_password,
pwd_context

)

class CompanyUserAuthService(CompanyService):

    

    @staticmethod
    async def check_company_subscription(user: User, db: AsyncSession):
        stmt = select(Company).where(Company.id == user.company_id).limit(1)

        result = await db.execute(stmt)
        company = result.scalars().first()

        if not company:
            raise HTTPException(status_code=404, detail="Company not found")
        
        company_info = {
            "company_name": company.company_name,
            "legal_name": company.legal_name,
            "company_logo_url": company.company_logo_url,
            "next_billing_date": company.next_billing_date.isoformat() if company.next_billing_date else None,
            "plan": company.plan.value  # Enums must be converted to their string values
        }

        
        if (not company.next_billing_date or company.next_billing_date.date() < date.today()):
            
            return {"company": company_info, "subscription_status": "expired"}
        
        return {"company": company_info, "subscription_status": "active"}

    @staticmethod
    async def user_login(email: str, password: str, db: AsyncSession):
        
        user = await CompanyUserAuthService.get_companyuser_by_email(email, db)
        print("user",user.hashed_password)
        if user is None:
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="user not found."
            )
        
        result = await db.execute(
            select(Company).where(Company.company_name == user.company_name)
        )
        print(result)
        company = result.scalar_one_or_none()  # Returns None if not found
      
        if company.is_active is False:
            raise HTTPException(status_code=404, detail="Company is not active")
            


        
     
        if(user.is_active == False):
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="user is not active contact company IT admin"
            )


        if not pwd_context.verify(password, user.hashed_password):
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="Incorrect password"
            )

        subscription_info = await CompanyUserAuthService.check_company_subscription(user, db)
        print(subscription_info)
        if subscription_info["subscription_status"] == "expired":
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail=subscription_info
            )
        print(company.plan.value)
        #plan=await get_package_plan_by_name(db,company.plan.value)
        plan="basic"
        if not user.system_password_reset:
            payload = {
                "userId": user.id,
                "expiresAt": (datetime.now(timezone.utc) + timedelta(minutes=10)).isoformat()
            }
            encrypted_token = encrypt_reset_payload(payload)
            user.password_reset_token = encrypted_token
            await db.commit()

            return {
                "password_reset_token": encrypted_token,
                "token_type": "password_reset",
            }

        if user.is_2FA_required == True and user.is_2FA_enabled == False:
        
            two_FA_setup_token = create_jwt_access_token(data={"user_id": user.id,"company_name":user.company_name,"company_id":user.company_id,"role":user.role,"department":user.department,"user_type":UserType.COMPANY},ACCESS_TOKEN_EXPIRE_MINUTES=20)
                    
            return {
                "2FA_setup_token": two_FA_setup_token,
                "token_type":"2FA_setup_token",
                "message":"user required two factor authentication setup complete the setup",
                "user_id":user.id,

                
                }

        if user.is_2FA_enabled == True:
            two_FA_login_token = create_jwt_access_token(data={"user_id": user.id,"company_name":user.company_name,"company_id":user.company_id,"role":user.role,"department":user.department,"user_type":UserType.COMPANY},ACCESS_TOKEN_EXPIRE_MINUTES=5)

            return {
                    "2FA_login_token": two_FA_login_token,
                    "token_type":"2FA_setup_login",
                    "message":"user required two factor authentication"
                    
                    }
        
        access_token = create_jwt_access_token(
    data={
        "user_id": str(user.id),
        "company_name": user.company_name,
        "company_id": str(user.company_id),
        "role": user.role,
        "department": user.department,
        "user_type": UserType.COMPANY,
         "plan": subscription_info["company"]["plan"]

    }
)

    
        return {
                "access_token": access_token, 
                "token_type": "bearer",
                "user_role":user.role,
                "user_department":user.department,
                "company_name":user.company_name,
                "user_email":user.email,
                "user_name":user.full_name,
                "user_is_active":user.is_active,
                "Joined_on":user.created_at,
                "plan": plan
                
                }
    

    async def request_reset_password(email:str,db:AsyncSession):
        user = await CompanyUserAuthService.get_companyuser_by_email(email, db)

        payload = {
            "userId": user.id,
            "expiresAt": (datetime.now(timezone.utc) + timedelta(minutes=10)).isoformat()
        }

        encrypted_token = encrypt_reset_payload(payload)
        print(encrypted_token)
        user.password_reset_token = encrypted_token
        await db.commit()
        
        reset_link = f"https://finsightai.mavenerp.in/reset-password/{encrypted_token}"
        logo_url="https://finsightai.mavenerp.in/assets/logo-transparent-png-CpxLVvxs.png"

        password_reset = f"""
                    <!DOCTYPE html>
                    <html>
                    <head>
                    <meta charset="UTF-8">
                    <title>Password Reset - FPnAInsights</title>
                    <style>
                    @import url('https://fonts.googleapis.com/css2?family=Inter:wght@400;500;600&display=swap');
                    </style>
                    </head>
                    <body style="margin: 0; padding: 0; font-family: 'Inter', Arial, sans-serif; background-color: #f8fafc;">
                    <table role="presentation" border="0" cellpadding="0" cellspacing="0" width="100%">
                    <tr>
                        <td align="center" style="padding: 40px 0;">
                        <!-- Main Card -->
                        <table border="0" cellpadding="0" cellspacing="0" width="100%" style="max-width: 600px; background: #ffffff; border-radius: 16px; box-shadow: 0 4px 24px rgba(0, 0, 0, 0.08); overflow: hidden;">
                            <!-- Gradient Header -->
                            <tr>
                            <td style="height: 6px; background: linear-gradient(135deg, #38bdf8 0%, #0284c7 100%);"></td>
                            </tr>
                            
                            <!-- Content -->
                            <tr>
                            <td style="padding: 48px 48px 32px;">
                                <div style="text-align: center;">
                                <img src="{logo_url}" alt="FinsightAI Logo" style="height: 72px; margin-bottom: 24px;" />
                                
                                <h1 style="font-size: 22px; font-weight: 600; color: #0f172a; margin: 0 0 16px;">Hi {user.full_name},</h1>
                                
                                <p style="font-size: 15px; line-height: 1.6; color: #334155; margin: 0 0 32px;">
                                    We received a request to reset your FPnAInsights account password. Click the button below to securely reset your password:
                                </p>
                                
                                <!-- Primary Action Button -->
                                <table border="0" cellpadding="0" cellspacing="0" style="margin: 0 auto 32px;">
                                    <tr>
                                    <td align="center" style="border-radius: 8px;" bgcolor="#0284c7">
                                        <a href="{reset_link}" style="display: inline-block; padding: 16px 36px; font-size: 15px; font-weight: 500; color: #ffffff; text-decoration: none; border-radius: 8px; background: linear-gradient(135deg, #0ea5e9 0%, #0284c7 100%); box-shadow: 0 2px 8px rgba(2, 132, 199, 0.25);">Reset Password</a>
                                    </td>
                                    </tr>
                                </table>
                                
                                <p style="font-size: 14px; color: #64748b; margin: 0 0 24px;">
                                    <strong>Note:</strong> This link expires in <span style="color: #dc2626; font-weight: 500;">10 minutes</span> for your security.
                                </p>
                                
                                <div style="border-top: 1px solid #e2e8f0; margin: 24px 0;"></div>
                                
                                <p style="font-size: 13px; color: #64748b; margin: 0;">
                                    If you didn't request this, please ignore this email or <a href="mailto:support@fpna.com" style="color: #0284c7; text-decoration: none;">contact our support team</a> if you have concerns.
                                </p>
                                </div>
                            </td>
                            </tr>
                            
                            <!-- Footer -->
                            <tr>
                            <td style="padding: 24px; background-color: #f1f5f9; text-align: center;">
                                <p style="font-size: 12px; color: #64748b; margin: 0;">
                                © 2025 FPnAInsights. All rights reserved.<br>
                                <span style="color: #94a3b8;">123 Business Ave, Suite 100, San Francisco, CA 94107</span>
                                </p>
                            </td>
                            </tr>
                        </table>
                        </td>
                    </tr>
                    </table>
                    </body>
                    </html>
                    """

        transporter.connect()
        transporter.send(
            user.email,
            "Password Reset Request",
            password_reset
        )
        transporter.close()

        return {"message": f"Password Reset Link sent to mail {user.email}", "action_type": "password_reset"}



    async def reset_password(encrypted_token:str,new_password,db:AsyncSession):

        payload = decrypt_reset_token(encrypted_token)
        user_id = payload['userId']
        expires_at = datetime.fromisoformat(payload['expiresAt'])

        if datetime.now(timezone.utc) > expires_at:
            raise HTTPException(status_code=400, detail="Token has expired")
        

        user = await CompanyUserAuthService.get_companyuser_by_id(user_id,db)


        if not user or user.password_reset_token != encrypted_token:
            raise HTTPException(status_code=400, detail="Invalid token")

        user.hashed_password = pwd_context.hash(new_password)
        user.system_password_reset = True 
        user.password_reset_token = None

        await db.commit()

        
        return {"message": "Password reset successfully"}
    
    async def setup_2fa(user_id:str, db):
        totp = pyotp.TOTP(pyotp.random_base32())

        user =  await CompanyUserAuthService.get_companyuser_by_id(user_id,db)

        otpauth_url = totp.provisioning_uri(name=user.email, issuer_name="finsightAI")

        user.temp_2fa_secret = totp.secret

        qr_data_base64 = generate_qr_code_image(otpauth_url)

        await db.commit()
        await db.refresh(user)

        return {
            "message": "Scan this QR code with Google Authenticator",
            "qr_code_base64": qr_data_base64,
        }
    
    async def verify_2fa(user_id:str,token: str,db: AsyncSession):

        user =  await CompanyUserAuthService.get_companyuser_by_id(user_id,db)
        print(user)

        if user.secret_2fa:
            raise HTTPException(status_code=400, detail={"message":"2FA is already enabled"})
        
        temp_secret = user.temp_2fa_secret


        if not temp_secret:
            raise HTTPException(status_code=400, detail="No temp_secret found setup 2fa first")

        totp = pyotp.TOTP(temp_secret)
        if totp.verify(token):
            
            user.secret_2fa = temp_secret  
            user.temp_2fa_secret = None  
            user.is_2FA_enabled = True
            
            await db.commit()
            await db.refresh(user)

            return {"verified": True, "message": "2FA is now enabled"}
        else:
            raise HTTPException(status_code=400, detail="Invalid token")

    async def login_2fa(user_id,two_FA_code,db):
        
        user = await CompanyUserAuthService.get_companyuser_by_id(user_id,db)

        secret = user.secret_2fa

        if not secret:
            raise HTTPException(status_code=400, detail="No 2FA secret found")

        totp = pyotp.TOTP(secret)
        if totp.verify(two_FA_code):

            access_token = create_jwt_access_token(data={"user_id": user.id,"company_name":user.company_name,"company_id":user.company_id,"role":user.role,"department":user.department,"user_type":UserType.COMPANY})

        
            return {"access_token": access_token, 
                "token_type": "bearer",
                "user_role":user.role,
                "user_department":user.department,
                "company_name":user.company_name,
                "user_email":user.email,
                "user_name":user.full_name,
                "user_is_active":user.is_active,
               "Joined_on":user.created_at
               }
        
        else:
            raise HTTPException(status_code=400, detail="Invalid token")
    
    async def enable_disable_two_factor_auth(user_id:str,current_company_user_company_id:str,db:AsyncSession):
        
        user = await CompanyUserAuthService.get_companyuser_by_id(user_id,db)
        #company_active=await 
        if(current_company_user_company_id != user.company_id):# checking in company level
            raise HTTPException(
                status_code=status.HTTP_403_FORBIDDEN,
                detail="user not found."
            )

        if(user.is_2FA_required == True):

            message = f"disabled two factor auth for user {user.full_name}"

            user.is_2FA_enabled = False
            user.is_2FA_required = False
            user.secret_2fa = None
            
        else:
            message = f"enabled two factor auth for user {user.full_name}"
            user.is_2FA_required = True



        await db.commit()
        await db.refresh(user)

        return {"message":message}


class ApplicationUserAuthService(ApplicationService):
    
     
    
     @staticmethod
     async def user_login(email: str, password: str, db: AsyncSession):

        user = await ApplicationUserAuthService.get_applicationuser_by_email(email, db)
        
        if not pwd_context.verify(password, user.hashed_password):
            raise HTTPException(
                status_code=status.HTTP_401_UNAUTHORIZED,
                detail="Incorrect password"
            )
        
        access_token = create_jwt_access_token(data={"user_id": user.id,"role":user.role,"user_type":UserType.APPLICATION})

        return {"access_token": access_token, "token_type": "bearer","role":user.role}


from sqlalchemy.orm import Session
from datetime import date

from src.models.company import (
    Department, Role, RoleDepartment, Module,
    SubscriptionPlan, Permission, UserRole, Subscription
)
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.future import select
from sqlalchemy.orm import Session
from src.models.company import Department, Role, RoleDepartment, Module, SubscriptionPlan, Permission, UserRole, Subscription
from datetime import date

# ---------- Departments ----------
import logging
from fastapi import HTTPException, status
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

logger = logging.getLogger(__name__)

# ---------- Departments ----------

async def add_department(db: AsyncSession, name: str, description: str = None) -> Department:
    try:
        dept = Department(name=name, description=description)
        db.add(dept)
        await db.commit()
        await db.refresh(dept)
        return dept
    except SQLAlchemyError as e:
        logger.error(f"Failed to add department: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add department")

async def get_department(db: AsyncSession, department_id: int) -> Department | None:
    try:
        result = await db.execute(select(Department).filter(Department.department_id == department_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get department {department_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get department")

async def get_all_departments(db: AsyncSession) -> list[Department]:
    try:
        result = await db.execute(select(Department))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all departments: {e}")
        raise HTTPException(status_code=500, detail="Failed to get departments")

async def update_department(db: AsyncSession, department_id: int, name: str = None, description: str = None) -> Department | None:
    try:
        dept = await get_department(db, department_id)
        if not dept:
            raise HTTPException(status_code=404, detail="Department not found")
        if name:
            dept.name = name
        if description:
            dept.description = description
        await db.commit()
        return dept
    except SQLAlchemyError as e:
        logger.error(f"Failed to update department {department_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update department")

async def delete_department(db: AsyncSession, department_id: int) -> bool:
    try:
        dept = await get_department(db, department_id)
        if not dept:
            return False
        await db.delete(dept)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete department {department_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete department")

# ---------- Roles ----------

async def add_role(db: AsyncSession, name: str, description: str = None) -> Role:
    try:
        role = Role(name=name, description=description)
        db.add(role)
        await db.commit()
        await db.refresh(role)
        return role
    except SQLAlchemyError as e:
        logger.error(f"Failed to add role: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add role")

async def get_role(db: AsyncSession, role_id: int) -> Role | None:
    try:
        result = await db.execute(select(Role).filter(Role.role_id == role_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get role {role_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get role")

async def update_role(db: AsyncSession, role_id: int, name: str = None, description: str = None) -> Role | None:
    try:
        role = await get_role(db, role_id)
        if not role:
            raise HTTPException(status_code=404, detail="Role not found")
        if name:
            role.name = name
        if description:
            role.description = description
        await db.commit()
        return role
    except SQLAlchemyError as e:
        logger.error(f"Failed to update role {role_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update role")

async def delete_role(db: AsyncSession, role_id: int) -> bool:
    try:
        role = await get_role(db, role_id)
        if not role:
            return False
        await db.delete(role)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete role {role_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete role")


# ---------- RoleDepartment ----------
import logging
from fastapi import HTTPException
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import select

logger = logging.getLogger(__name__)

# ---------- RoleDepartment ----------

async def add_role_department(db: AsyncSession, role_id: int, department_id: int) -> RoleDepartment:
    try:
        rd = RoleDepartment(role_id=role_id, department_id=department_id)
        db.add(rd)
        await db.commit()
        await db.refresh(rd)
        return rd
    except SQLAlchemyError as e:
        logger.error(f"Failed to add RoleDepartment (role_id={role_id}, department_id={department_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add RoleDepartment")

async def get_role_department(db: AsyncSession, rd_id: int) -> RoleDepartment | None:
    try:
        result = await db.execute(select(RoleDepartment).filter(RoleDepartment.role_department_id == rd_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get RoleDepartment id={rd_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get RoleDepartment")

async def update_role_department(db: AsyncSession, rd_id: int, role_id: int = None, department_id: int = None) -> RoleDepartment | None:
    try:
        rd = await get_role_department(db, rd_id)
        if not rd:
            raise HTTPException(status_code=404, detail="RoleDepartment not found")
        if role_id:
            rd.role_id = role_id
        if department_id:
            rd.department_id = department_id
        await db.commit()
        return rd
    except SQLAlchemyError as e:
        logger.error(f"Failed to update RoleDepartment id={rd_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update RoleDepartment")

async def delete_role_department(db: AsyncSession, rd_id: int) -> bool:
    try:
        rd = await get_role_department(db, rd_id)
        if not rd:
            return False
        await db.delete(rd)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete RoleDepartment id={rd_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete RoleDepartment")

# ---------- Modules ----------

async def add_module(db: AsyncSession, name: str, description: str = None) -> Module:
    try:
        module = Module(name=name, description=description)
        db.add(module)
        await db.commit()
        await db.refresh(module)
        return module
    except SQLAlchemyError as e:
        logger.error(f"Failed to add Module name={name}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add Module")

async def get_module(db: AsyncSession, module_id: int) -> Module | None:
    try:
        result = await db.execute(select(Module).filter(Module.module_id == module_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get Module id={module_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get Module")

async def get_module_name(db: AsyncSession, module: str) -> Module | None:
    try:
        print("modulename:",module)
        result = await db.execute(select(Module).filter(Module.name == module))
        
        return result.scalar_one_or_none()
        
    except SQLAlchemyError as e:
        logger.error(f"Failed to get Module id={module}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get Module")

async def update_module(db: AsyncSession, module_id: int, name: str = None, description: str = None) -> Module | None:
    try:
        module = await get_module(db, module_id)
        if not module:
            raise HTTPException(status_code=404, detail="Module not found")
        if name:
            module.name = name
        if description:
            module.description = description
        await db.commit()
        return module
    except SQLAlchemyError as e:
        logger.error(f"Failed to update Module id={module_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update Module")

async def delete_module(db: AsyncSession, module_id: int) -> bool:
    try:
        module = await get_module(db, module_id)
        if not module:
            return False
        await db.delete(module)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete Module id={module_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete Module")

# ---------- Subscription Plans ----------
import logging
from fastapi import HTTPException
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import select

logger = logging.getLogger(__name__)

# ---------- SubscriptionPlan ----------

async def add_subscription_plan(db: AsyncSession, name: str,collabrator,
        bussiness_user,
        view_only,user_limit, billing_cycle, price) -> SubscriptionPlan:
    try:
        plan = SubscriptionPlan(name=name,collaborators=collabrator,
        business_users=bussiness_user,
        view_only=view_only, billing_cycle=billing_cycle,user_limit=user_limit, price=price)
        db.add(plan)
        await db.commit()
        await db.refresh(plan)
        return plan
    except SQLAlchemyError as e:
        logger.error(f"Failed to add SubscriptionPlan (name={name}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add subscription plan")

async def get_subscription_plan(db: AsyncSession, subscription_plan_id: int) -> SubscriptionPlan | None:
    try:
        result = await db.execute(select(SubscriptionPlan).filter(SubscriptionPlan.subscription_plan_id == subscription_plan_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get SubscriptionPlan id={subscription_plan_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get subscription plan")


async def get_subscription_plan_name(db: AsyncSession, plan_name: int) -> SubscriptionPlan | None:
    try:
        result = await db.execute(
            select(SubscriptionPlan)
            .filter(SubscriptionPlan.name == plan_name)
            
        )
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get SubscriptionPlan id={plan_name}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get subscription plan")

async def update_subscription_plan(db: AsyncSession,collabrator:int,
        bussiness_user :int,
        view_only: int , subscription_plan_id: int, name: str = None, billing_cycle = None, price = None) -> SubscriptionPlan | None:
    try:
        plan = await get_subscription_plan(db, subscription_plan_id)
        if not plan:
            raise HTTPException(status_code=404, detail="Subscription plan not found")
        if name:
            plan.name = name
        if billing_cycle:
            plan.billing_cycle = billing_cycle
        if price is not None:
            plan.price = price
        if   collabrator > 0:
            plan.collaborators=collabrator
        if bussiness_user > 0:
            plan.business_users=bussiness_user
        if view_only >0:
            plan.view_only =view_only
        await db.commit()
        return plan
    except SQLAlchemyError as e:
        logger.error(f"Failed to update SubscriptionPlan id={subscription_plan_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update subscription plan")

async def delete_subscription_plan(db: AsyncSession, subscription_plan_id: int) -> bool:
    try:
        plan = await get_subscription_plan(db, subscription_plan_id)
        if not plan:
            return False
        await db.delete(plan)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete SubscriptionPlan id={plan_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete subscription plan")

# ---------- Permissions ----------

async def add_permission(db: AsyncSession, name: str, module_id: int, description: str = None) -> Permission:
    try:
        permission = Permission(name=name, module_id=module_id, description=description)
        db.add(permission)
        await db.commit()
        await db.refresh(permission)
        return permission
    except SQLAlchemyError as e:
        logger.error(f"Failed to add Permission (name={name}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add permission")

async def get_permission(db: AsyncSession, permission_id: int) -> Permission | None:
    try:
        result = await db.execute(select(Permission).filter(Permission.permission_id == permission_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get Permission id={permission_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get permission")

async def update_permission(db: AsyncSession, permission_id: int, name: str = None, module_id: int = None, description: str = None) -> Permission | None:
    try:
        permission = await get_permission(db, permission_id)
        if not permission:
            raise HTTPException(status_code=404, detail="Permission not found")
        if name:
            permission.name = name
        if module_id:
            permission.module_id = module_id
        if description:
            permission.description = description
        await db.commit()
        return permission
    except SQLAlchemyError as e:
        logger.error(f"Failed to update Permission id={permission_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update permission")

async def delete_permission(db: AsyncSession, permission_id: int) -> bool:
    try:
        permission = await get_permission(db, permission_id)
        if not permission:
            return False
        await db.delete(permission)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete Permission id={permission_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete permission")

# ---------- UserRoles ----------
import logging
from fastapi import HTTPException
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy import select
from datetime import date

logger = logging.getLogger(__name__)

# ---------- UserRole ----------

async def add_user_role(db: AsyncSession, user_id: str, role_department_id: int, company_id: str) -> UserRole:
    try:
        user_role = UserRole(user_id=user_id, role_department_id=role_department_id, company_id=company_id)
        db.add(user_role)
        await db.commit()
        await db.refresh(user_role)
        return user_role
    except SQLAlchemyError as e:
        logger.error(f"Failed to add UserRole (user_id={user_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add user role")

async def get_user_role(db: AsyncSession, user_role_id: int) -> UserRole | None:
    try:
        result = await db.execute(select(UserRole).filter(UserRole.user_role_id == user_role_id))
        return result.scalar_one_or_none()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get UserRole id={user_role_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to get user role")

async def update_user_role(db: AsyncSession, user_role_id: int, user_id: str = None, role_department_id: int = None, company_id: str = None) -> UserRole | None:
    try:
        user_role = await get_user_role(db, user_role_id)
        if not user_role:
            raise HTTPException(status_code=404, detail="User role not found")
        if user_id:
            user_role.user_id = user_id
        if role_department_id:
            user_role.role_department_id = role_department_id
        if company_id:
            user_role.company_id = company_id
        await db.commit()
        return user_role
    except SQLAlchemyError as e:
        logger.error(f"Failed to update UserRole id={user_role_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update user role")

async def delete_user_role(db: AsyncSession, user_role_id: int) -> bool:
    try:
        user_role = await get_user_role(db, user_role_id)
        if not user_role:
            return False
        await db.delete(user_role)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete UserRole id={user_role_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete user role")

# ---------- Subscription ----------
async def add_subscription(
    db: AsyncSession,
    company_id: str,
    plan_id: int,
    collaborators: int,
    business_users: int,
    view_only: int,
    price: str,
    start_date: date,
    end_date: date = None,
    status: str = None
) -> Subscription:
    try:
        connection = await db.connection()
        db_name = connection.engine.url.database
        print(f"Connected to database: {db_name}")
        subscription = Subscription(
            company_id=company_id,
            plan_id=plan_id,
            collaborators=collaborators,
            business_users=business_users,
            view_only=view_only,
            price=price,
            start_date=start_date,
            end_date=end_date,
            status=status
        )
        db.add(subscription)
        await db.commit()
        await db.refresh(subscription)
        return subscription
    except SQLAlchemyError as e:
        logger.error(f"Failed to add Subscription (company_id={company_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add subscription")

async def update_subscription(db: AsyncSession, subscription_id: int, update_data: dict) -> Subscription:
    try:
        result = await db.execute(select(Subscription).where(Subscription.id == subscription_id))
        subscription = result.scalars().first()

        if not subscription:
            raise HTTPException(status_code=404, detail="Subscription not found")

        for key, value in update_data.items():
            if hasattr(subscription, key):
                setattr(subscription, key, value)

        await db.commit()
        await db.refresh(subscription)
        return subscription

    except SQLAlchemyError as e:
        logger.error(f"Failed to update Subscription (id={subscription_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update subscription")
async def get_subscription_by_id(db: AsyncSession, subscription_id: int) -> Subscription:
    result = await db.execute(select(Subscription).where(Subscription.id == subscription_id))
    subscription = result.scalars().first()

    if not subscription:
        raise HTTPException(status_code=404, detail="Subscription not found")

    return subscription

async def upsert_subscription(
    db: AsyncSession,
    update_data: dict
) -> Subscription:
    try:
        # Print the database name from the connection
        connection = await db.connection()
        db_name = connection.engine.url.database
        print(f"Connected to database: {db_name}")

        subscription = None
        subscription_id = update_data.get('subscription_id')
        print("Update data:", update_data)

        if subscription_id:
            # Try to fetch by subscription_id
            result = await db.execute(
                select(Subscription).where(Subscription.subscription_id == subscription_id)
            )
            subscription = result.scalars().first()

        if subscription:
            # Update existing subscription
            for key, value in update_data.items():
                if hasattr(subscription, key):
                    setattr(subscription, key, value)
        else:
            # Create new subscription
            subscription = Subscription(**update_data)
            db.add(subscription)

        await db.commit()
        await db.refresh(subscription)
        return subscription

    except SQLAlchemyError as e:
        await db.rollback()
        raise HTTPException(status_code=500, detail=f"Failed to upsert subscription: {str(e)}")
from sqlalchemy.future import select
from sqlalchemy.orm import selectinload
from datetime import date
from fastapi import HTTPException
from typing import List
 # Adjust as per your project structure
from sqlalchemy.orm import selectinload
from datetime import date

async def get_subscription_by_company_id(db: AsyncSession, company_id: str) -> List[dict]:
    today = date.today()

    result = await db.execute(
        select(Subscription)
        .options(selectinload(Subscription.plan))  # 👈 Eager load the plan relationship
        .where(Subscription.company_id == company_id)
    )

    subscriptions = result.scalars().all()

    if not subscriptions:
        raise HTTPException(status_code=404, detail="No subscriptions found for this company")
    for sub in subscriptions:
        plan = sub.plan     
        print(plan.name)
    #await get_subscription_plan(db,plan)
    return [
        {
            "subscription_id": sub.subscription_id,  # Or sub.id if that's the actual PK
            "company_id": sub.company_id,
            "plan": {
                "subscription_plan_id": sub.plan.subscription_plan_id,
                "name": sub.plan.name,
                "billing_cycle":sub.plan.billing_cycle,
                "price":sub.plan.price,
                "collaborators":sub.plan.collaborators,
                "business_users":sub.plan.business_users,
                "view_only":sub.plan.view_only,
                "user_limit":sub.plan.user_limit
                # "features": sub.plan.features if hasattr(sub.plan, "features") else None
            } if sub.plan else None,
            "start_date": sub.start_date,
            "end_date": sub.end_date,
            "is_active": sub.start_date <= today <= sub.end_date
        }
        for sub in subscriptions
    ]

async def delete_subscription(db: AsyncSession, subscription_id: int) -> dict:
    try:
        result = await db.execute(select(Subscription).where(Subscription.subscription_id == subscription_id))
        subscription = result.scalars().first()

        if not subscription:
            raise HTTPException(status_code=404, detail="Subscription not found")

        await db.delete(subscription)
        await db.commit()
        return {"detail": "Subscription deleted successfully"}

    except SQLAlchemyError as e:
        logger.error(f"Failed to delete Subscription (id={subscription_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete subscription")

# ---------- Get All Functions with exception handling ----------

async def get_all_departments(db: AsyncSession) -> list[Department]:
    try:
        result = await db.execute(select(Department))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all Departments: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve departments")

async def get_all_roles(db: AsyncSession) -> list[Role]:
    try:
        result = await db.execute(select(Role))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all Roles: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve roles")

async def get_all_role_departments(db: AsyncSession) -> list[RoleDepartment]:
    try:
        result = await db.execute(select(RoleDepartment))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all RoleDepartments: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve role departments")

async def get_all_modules(db: AsyncSession) -> list[Module]:
    try:
        result = await db.execute(select(Module))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all Modules: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve modules")

async def get_all_subscription_plans(db: AsyncSession) -> list[SubscriptionPlan]:
    try:
        result = await db.execute(select(SubscriptionPlan))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all SubscriptionPlans: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve subscription plans")

async def get_all_permissions(db: AsyncSession) -> list[Permission]:
    try:
        result = await db.execute(select(Permission))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all Permissions: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve permissions")

async def get_all_user_roles(db: AsyncSession) -> list[UserRole]:
    try:
        result = await db.execute(select(UserRole))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all UserRoles: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve user roles")

async def get_all_subscriptions(db: AsyncSession) -> list[Subscription]:
    try:
        result = await db.execute(select(Subscription))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all Subscriptions: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve subscriptions")


logger = logging.getLogger(__name__)

# ---------- PlanModule CRUD with exception handling ----------

async def add_plan_module(db: AsyncSession, plan_id: int, module_id: int) -> PlanModule:
    try:
        pm = PlanModule(plan_id=plan_id, module_id=module_id)
        db.add(pm)
        await db.commit()
        await db.refresh(pm)
        return pm
    except SQLAlchemyError as e:
        logger.error(f"Failed to add PlanModule (plan_id={plan_id}, module_id={module_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to add plan module")

async def get_plan_module(db: AsyncSession, plan_id: int, module_id: int) -> PlanModule | None:
    try:
        result = await db.execute(
            select(PlanModule).where(PlanModule.plan_id == plan_id, PlanModule.module_id == module_id)
        )
        return result.scalars().first()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get PlanModule (plan_id={plan_id}, module_id={module_id}): {e}")
        raise HTTPException(status_code=500, detail="Failed to get plan module")

async def delete_plan_module(db: AsyncSession, plan_id: int, module_id: int) -> bool:
    try:
        pm = await get_plan_module(db, plan_id, module_id)
        if not pm:
            return False
        await db.delete(pm)
        await db.commit()
        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete PlanModule (plan_id={plan_id}, module_id={module_id}): {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete plan module")

# ---------- get_all_roles ----------

async def get_all_roles(db: AsyncSession) -> List[Role]:
    try:
        result = await db.execute(select(Role))
        return result.scalars().all()
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all roles: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve roles")

# ---------- add_package ----------

async def add_package(
    db: AsyncSession,
    name: str,
    modules: List[int],collabrator:int,
        bussiness_user:int,
        view_only:int,
        user_limit:int,
    billing_cycle: Optional[BillingCycle] = None,
    price: str = None
) -> SubscriptionPlan:
    try:
        plan = await add_subscription_plan(db, name,collabrator,
        bussiness_user,
        view_only,user_limit, billing_cycle, price)
        print(plan)
        if not plan:

            raise HTTPException(status_code=400, detail="Subscription plan with this name does not exist")

        for module in modules:
            print(module)
            module = await get_module(db, module.id)
           
            if not module:
                raise HTTPException(status_code=404, detail=f"Module with id {module} not found")

            plan_module = await add_plan_module(db, plan.subscription_plan_id, module.module_id)
            if not plan_module:
                raise HTTPException(status_code=500, detail=f"Failed to associate module {module.name} with plan {plan.name}")
            print(plan_module, plan)
        return {"subscription_plan":plan, "module":module}
    except Exception as e:
        logger.error(f"Failed to add package plan '{name}': {e}")
        raise

# ---------- get_all_package_plans ----------

async def get_all_package_plans(db: AsyncSession) -> List[SubscriptionPlan]:
    try:
        result = await db.execute(
            select(SubscriptionPlan).order_by(SubscriptionPlan.subscription_plan_id.asc())
        )
        plans = result.scalars().all()

        for plan in plans:
            result = await db.execute(
                select(Module).join(PlanModule).where(PlanModule.plan_id == plan.subscription_plan_id)
            )
            plan.modules = result.scalars().all()
            

        return plans
    except SQLAlchemyError as e:
        logger.error(f"Failed to get all package plans: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve package plans")

# ---------- get_package_plan_by_id ----------

async def get_package_plan_by_id(db: AsyncSession, plan_id: int) -> SubscriptionPlan:
    try:
        result = await db.execute(
            select(SubscriptionPlan).where(SubscriptionPlan.subscription_plan_id == plan_id)
        )
        plan = result.scalars().first()

        if not plan:
            raise HTTPException(status_code=404, detail="Package plan not found")

        result = await db.execute(
            select(Module).join(PlanModule).where(PlanModule.plan_id == plan.subscription_plan_id)
        )
        plan.modules = result.scalars().all()
        return plan
    except SQLAlchemyError as e:
        logger.error(f"Failed to get package plan by id {plan_id}: {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve package plan")

# ---------- get_package_plan_by_name ----------

async def get_package_plan_by_name(db: AsyncSession, planname: str) -> SubscriptionPlan:
    try:
        result = await db.execute(
            select(SubscriptionPlan).where(func.lower(SubscriptionPlan.name) == planname.lower())
        )
        plan = result.scalars().first()

        if not plan:
            raise HTTPException(status_code=404, detail="Package plan not found")

        result = await db.execute(
            select(Module).join(PlanModule).where(PlanModule.plan_id == plan.subscription_plan_id)
        )
        plan.modules = result.scalars().all()
        return plan
    except SQLAlchemyError as e:
        logger.error(f"Failed to get package plan by name '{planname}': {e}")
        raise HTTPException(status_code=500, detail="Failed to retrieve package plan")

# ---------- update_package_plan ----------

async def update_package_plan(
    db: AsyncSession,
    plan_id: int,
    data: UpdateSubscriptionPlanCreate,
    billing_cycle: str
) -> SubscriptionPlan:
    try:
        # Update basic plan data
        plan = await update_subscription_plan(
            db,
            data.collabrator,
            data.bussiness_user,
            data.view_only,
            plan_id,
            data.name,
            billing_cycle,
            str(data.price)
        )

        if not plan:
            raise HTTPException(status_code=404, detail="Package plan not found")

        # Remove old module associations
        await db.execute(delete(PlanModule).where(PlanModule.plan_id == plan_id))
        await db.commit()

        # Add new module associations
        for module_data in data.modules:
            module_result = await db.execute(select(Module).where(Module.name == module_data.name))
            module = module_result.scalars().first()

            if not module:
                raise HTTPException(status_code=400, detail=f"Module '{module_data.name}' does not exist")

            await add_plan_module(db, plan_id=plan_id, module_id=module.module_id)

        # Reload updated plan with attached modules
        return await get_package_plan_by_id(db, plan_id)

    except SQLAlchemyError as e:
        logger.error(f"Failed to update package plan id={plan_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to update package plan")

# ---------- delete_package_plan ----------

async def delete_package_plan(db: AsyncSession, plan_id: int) -> bool:
    try:
        result = await db.execute(select(SubscriptionPlan).where(SubscriptionPlan.subscription_plan_id == plan_id))
        plan = result.scalars().first()

        if not plan:
            return False

        # Delete module links
        await db.execute(delete(PlanModule).where(PlanModule.plan_id == plan_id))
        # Delete plan itself
        await db.execute(delete(SubscriptionPlan).where(SubscriptionPlan.subscription_plan_id == plan_id))
        await db.commit()

        return True
    except SQLAlchemyError as e:
        logger.error(f"Failed to delete package plan id={plan_id}: {e}")
        await db.rollback()
        raise HTTPException(status_code=500, detail="Failed to delete package plan")

from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select

from fastapi import HTTPException


async def create_blog(payload: BlogCreate, db: AsyncSession):
    blog = Blog(**payload.dict())
    db.add(blog)
    await db.commit()
    await db.refresh(blog)
    return blog


async def update_blog(id: int, payload: BlogUpdate, db: AsyncSession):
    result = await db.execute(select(Blog).where(Blog.id == id))
    blog = result.scalar_one_or_none()
    if not blog:
        raise HTTPException(status_code=404, detail="Blog not found")

    for field, value in payload.dict(exclude_unset=True).items():
        setattr(blog, field, value)

    await db.commit()
    await db.refresh(blog)
    return blog


async def delete_blog(id: int, db: AsyncSession):
    result = await db.execute(select(Blog).where(Blog.id == id))
    blog = result.scalar_one_or_none()
    if not blog:
        raise HTTPException(status_code=404, detail="Blog not found")
    await db.delete(blog)
    await db.commit()
    return {"message": "Blog deleted successfully"}


async def update_blog_status(id: int, status: BlogStatusEnum, db: AsyncSession):
    result = await db.execute(select(Blog).where(Blog.id == id))
    blog = result.scalar_one_or_none()
    if not blog:
        raise HTTPException(status_code=404, detail="Blog not found")
    blog.status = status
    await db.commit()
    await db.refresh(blog)
    return blog


async def get_blog_by_id(id: int, db: AsyncSession):
    result = await db.execute(select(Blog).where(Blog.id == id))
    blog = result.scalar_one_or_none()
    if not blog:
        raise HTTPException(status_code=404, detail="Blog not found")
    return blog


from sqlalchemy import select, func


from sqlalchemy import func, and_

from typing import Optional, List, Dict

async def get_filtered_blogs(
    db: AsyncSession,
    status: Optional[str],
    author: Optional[str],
    tag: Optional[str],
    limit: int,
    offset: int,
) -> Dict:
    query = select(Blog)
    filters = []

    if status:
        filters.append(Blog.status == status)
    if author:
        filters.append(Blog.author.ilike(f"%{author}%"))
    if tag:
        filters.append(func.array_to_string(Blog.tags, ',').ilike(f"%{tag}%"))
        # Or: filters.append(Blog.tags.any(tag)) if using ARRAY with any()

    if filters:
        query = query.where(and_(*filters))

    # 🔢 Get total count first (for pagination metadata)
    count_query = select(func.count()).select_from(Blog)
    if filters:
        count_query = count_query.where(and_(*filters))

    total_result = await db.execute(count_query)
    total = total_result.scalar_one()

    # 📦 Get paginated data
    paginated_query = query.order_by(Blog.created_at.desc()).limit(limit).offset(offset)
    result = await db.execute(paginated_query)
    blogs = result.scalars().all()

    # 🔁 Pagination metadata
    current_page = (offset // limit) + 1 if limit else 1
    total_pages = (total + limit - 1) // limit if limit else 1
    has_next = offset + limit < total
    
    return {
        "data": blogs,
        "total": total,
        "current_page": current_page,
        "total_pages": total_pages,
        "has_next": has_next,
        "limit": limit,
        "offset": offset
    }

async def get_blogs_by_status(status: BlogStatusEnum, db: AsyncSession):
    result = await db.execute(select(Blog).where(Blog.status == status))
    return result.scalars().all()
