from datetime import datetime, timezone
from decimal import Decimal
from uuid import UUID
from sqlalchemy import select
from sqlalchemy.orm import Session
from app.models.expense_income import Expense, ExpenseCategory
from app.models.membership import Membership
from app.models.payroll import PayrollProfile, PayrollRun

class PayrollError(ValueError): pass
def profile(db: Session, business_id: UUID, profile_id: UUID, lock=False):
    q=select(PayrollProfile).where(PayrollProfile.id==profile_id, PayrollProfile.business_id==business_id)
    return db.scalar(q.with_for_update() if lock else q)
def run(db: Session, business_id: UUID, run_id: UUID, lock=False):
    q=select(PayrollRun).where(PayrollRun.id==run_id, PayrollRun.business_id==business_id)
    return db.scalar(q.with_for_update() if lock else q)
def member(db: Session, business_id: UUID, membership_id: UUID, lock=False):
    q=select(Membership).where(Membership.id==membership_id, Membership.business_id==business_id)
    return db.scalar(q.with_for_update() if lock else q)
def ensure_salary_category(db, business_id):
    category=db.scalar(select(ExpenseCategory).where(ExpenseCategory.business_id==business_id, ExpenseCategory.name=="Salary / Payroll").with_for_update())
    if not category:
        category=ExpenseCategory(business_id=business_id,name="Salary / Payroll",description="System payroll expenses",is_active=True); db.add(category); db.flush()
    return category
def create_run(db, business_id, user_id, data):
    target=member(db,business_id,data.membership_id,True)
    prof=profile(db,business_id,data.payroll_profile_id,True)
    if not target: raise LookupError("Staff membership not found.")
    if not target.is_active: raise PayrollError("Inactive staff cannot receive new payroll.")
    if not prof or prof.membership_id != target.id: raise PayrollError("Payroll profile does not belong to this staff member.")
    if not prof.is_active: raise PayrollError("Payroll profile is inactive.")
    existing=db.scalar(select(PayrollRun.id).where(PayrollRun.business_id==business_id,PayrollRun.membership_id==target.id,PayrollRun.period_start==data.period_start,PayrollRun.period_end==data.period_end,PayrollRun.status!="CANCELLED").with_for_update())
    if existing: raise PayrollError("An active payroll run already exists for this staff member and period.")
    total=prof.salary+data.adjustment_amount
    if total < 0: raise PayrollError("Payroll total cannot be negative.")
    obj=PayrollRun(business_id=business_id,membership_id=target.id,payroll_profile_id=prof.id,period_start=data.period_start,period_end=data.period_end,base_salary=prof.salary,adjustment_amount=data.adjustment_amount,adjustment_note=data.adjustment_note,total_amount=total,status="DRAFT",created_by=user_id)
    db.add(obj); db.commit(); db.refresh(obj); return obj