from __future__ import annotations

from datetime import date, datetime, time, timezone

from sqlalchemy import Boolean, Date, DateTime, Float, ForeignKey, Integer, String, Text, Time, UniqueConstraint
from sqlalchemy.orm import Mapped, mapped_column, relationship

from app.core.db.common import CommonBase


class Employee(CommonBase):
    """Tenant and branch aware employee master.

    This is the v2 Employee Core foundation migrated from the older HRMS module.
    It intentionally keeps attendance, leave, payroll, documents and ESS out of
    this table so those features can be added safely in later phases.
    """

    __tablename__ = "employees"
    __table_args__ = (
        UniqueConstraint("tenant_id", "employee_code", name="uq_employees_tenant_code"),
        UniqueConstraint("tenant_id", "user_id", name="uq_employees_tenant_user"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)

    employee_code: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    full_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True, index=True)
    mobile: Mapped[str | None] = mapped_column(String(20), nullable=True)
    alternate_mobile: Mapped[str | None] = mapped_column(String(20), nullable=True)

    date_of_joining: Mapped[date | None] = mapped_column(Date, nullable=True)
    date_of_leaving: Mapped[date | None] = mapped_column(Date, nullable=True)
    employment_type: Mapped[str] = mapped_column(String(50), nullable=False, default="full_time", index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="active", index=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)

    department: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    designation: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    reporting_manager_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)

    pan: Mapped[str | None] = mapped_column(String(20), nullable=True, index=True)
    uan: Mapped[str | None] = mapped_column(String(30), nullable=True)
    esi_no: Mapped[str | None] = mapped_column(String(30), nullable=True)
    pf_no: Mapped[str | None] = mapped_column(String(30), nullable=True)
    aadhaar_last4: Mapped[str | None] = mapped_column(String(4), nullable=True)

    bank_name: Mapped[str | None] = mapped_column(String(100), nullable=True)
    bank_account_no: Mapped[str | None] = mapped_column(String(40), nullable=True)
    bank_ifsc: Mapped[str | None] = mapped_column(String(20), nullable=True)

    address: Mapped[str | None] = mapped_column(Text, nullable=True)
    emergency_contact_name: Mapped[str | None] = mapped_column(String(200), nullable=True)
    emergency_contact_mobile: Mapped[str | None] = mapped_column(String(20), nullable=True)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    user = relationship("User", foreign_keys=[user_id])
    reporting_manager = relationship("User", foreign_keys=[reporting_manager_user_id])



class EmployeeRegistrationRequest(CommonBase):
    """Employee self-registration / linkage request for ESS onboarding.

    This table is deliberately separate from employees so a logged-in user can
    request an employee profile without immediately creating an employee master.
    Firm Admin / Partner / Branch Manager can review and approve it.
    """

    __tablename__ = "employee_registration_requests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    user_id: Mapped[int] = mapped_column(ForeignKey("users.id", ondelete="CASCADE"), nullable=False, index=True)

    requested_employee_code: Mapped[str | None] = mapped_column(String(50), nullable=True, index=True)
    full_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
    email: Mapped[str | None] = mapped_column(String(255), nullable=True, index=True)
    mobile: Mapped[str | None] = mapped_column(String(20), nullable=True)
    department: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    designation: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    date_of_joining: Mapped[date | None] = mapped_column(Date, nullable=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_employee_id: Mapped[int | None] = mapped_column(ForeignKey("employees.id", ondelete="SET NULL"), nullable=True, index=True)

    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    user = relationship("User", foreign_keys=[user_id])
    reviewed_by = relationship("User", foreign_keys=[reviewed_by_user_id])
    created_employee = relationship("Employee", foreign_keys=[created_employee_id])



class EmployeeAttendance(CommonBase):
    """Daily attendance records for employee self-service and admin review.

    One row is maintained per employee per attendance date.  Phase 6C keeps the
    model deliberately simple and tenant/branch-safe.  Geo/IP validation can be
    added later without changing the employee master.
    """

    __tablename__ = "employee_attendance"
    __table_args__ = (
        UniqueConstraint("tenant_id", "employee_id", "attendance_date", name="uq_employee_attendance_employee_date"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)

    attendance_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    punch_in_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    punch_out_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    punch_in_local_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=False), nullable=True)
    punch_out_local_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=False), nullable=True)
    branch_timezone: Mapped[str] = mapped_column(String(64), nullable=False, default="Asia/Kolkata")
    scheduled_start_local: Mapped[time | None] = mapped_column(Time, nullable=True)
    scheduled_end_local: Mapped[time | None] = mapped_column(Time, nullable=True)
    late_by_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True)
    attendance_rule_status: Mapped[str | None] = mapped_column(String(40), nullable=True, index=True)
    is_weekly_off: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    work_duration_minutes: Mapped[int | None] = mapped_column(Integer, nullable=True)

    status: Mapped[str] = mapped_column(String(30), nullable=False, default="present", index=True)
    approval_status: Mapped[str] = mapped_column(String(30), nullable=False, default="approved", index=True)
    source: Mapped[str] = mapped_column(String(30), nullable=False, default="self_punch", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    punch_in_latitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_in_longitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_in_accuracy_meters: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_in_distance_meters: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_in_ip: Mapped[str | None] = mapped_column(String(80), nullable=True)
    punch_in_geo_status: Mapped[str | None] = mapped_column(String(40), nullable=True, index=True)
    punch_in_ip_status: Mapped[str | None] = mapped_column(String(40), nullable=True, index=True)

    punch_out_latitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_out_longitude: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_out_accuracy_meters: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_out_distance_meters: Mapped[float | None] = mapped_column(Float, nullable=True)
    punch_out_ip: Mapped[str | None] = mapped_column(String(80), nullable=True)
    punch_out_geo_status: Mapped[str | None] = mapped_column(String(40), nullable=True, index=True)
    punch_out_ip_status: Mapped[str | None] = mapped_column(String(40), nullable=True, index=True)

    reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    user = relationship("User", foreign_keys=[user_id])
    reviewed_by = relationship("User", foreign_keys=[reviewed_by_user_id])



class EmployeeLeaveType(CommonBase):
    """Tenant/branch aware leave type master for Phase 6D."""

    __tablename__ = "employee_leave_types"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "code", name="uq_employee_leave_types_tenant_branch_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)

    code: Mapped[str] = mapped_column(String(30), nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    annual_quota_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    carry_forward_allowed: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    allow_negative_balance: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    requires_approval: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_paid: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )


class EmployeeLeaveBalance(CommonBase):
    """Leave balance per employee and leave type."""

    __tablename__ = "employee_leave_balances"
    __table_args__ = (
        UniqueConstraint("tenant_id", "employee_id", "leave_type_id", name="uq_employee_leave_balances_employee_type"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    leave_type_id: Mapped[int] = mapped_column(ForeignKey("employee_leave_types.id", ondelete="CASCADE"), nullable=False, index=True)

    opening_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    credited_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    availed_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    adjusted_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    balance_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)

    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    leave_type = relationship("EmployeeLeaveType", foreign_keys=[leave_type_id])


class EmployeeLeaveRequest(CommonBase):
    """Employee leave request, review and approval workflow."""

    __tablename__ = "employee_leave_requests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    leave_type_id: Mapped[int] = mapped_column(ForeignKey("employee_leave_types.id", ondelete="RESTRICT"), nullable=False, index=True)

    from_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    to_date: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    days: Mapped[int] = mapped_column(Integer, nullable=False, default=1)
    reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    request_source: Mapped[str] = mapped_column(String(30), nullable=False, default="employee_portal", index=True)

    reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    user = relationship("User", foreign_keys=[user_id])
    leave_type = relationship("EmployeeLeaveType", foreign_keys=[leave_type_id])
    reviewed_by = relationship("User", foreign_keys=[reviewed_by_user_id])



class EmployeeDocumentType(CommonBase):
    """Tenant/branch aware employee document type master for Phase 6E."""

    __tablename__ = "employee_document_types"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "code", name="uq_employee_document_types_tenant_branch_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)

    code: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    name: Mapped[str] = mapped_column(String(150), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    is_mandatory: Mapped[bool] = mapped_column(Boolean, nullable=False, default=False)
    allow_employee_upload: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    requires_verification: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )


class EmployeeDocument(CommonBase):
    """Employee document metadata.

    Files are stored on disk under data/uploads/employee_documents.  The DB keeps
    only controlled metadata and the relative file path so a later storage
    backend such as Nextcloud/local office storage can be introduced safely.
    """

    __tablename__ = "employee_documents"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    document_type_id: Mapped[int | None] = mapped_column(ForeignKey("employee_document_types.id", ondelete="SET NULL"), nullable=True, index=True)

    title: Mapped[str] = mapped_column(String(200), nullable=False, index=True)
    document_no: Mapped[str | None] = mapped_column(String(100), nullable=True, index=True)
    issue_date: Mapped[date | None] = mapped_column(Date, nullable=True)
    expiry_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)

    original_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    stored_filename: Mapped[str] = mapped_column(String(255), nullable=False)
    storage_path: Mapped[str] = mapped_column(String(500), nullable=False)
    content_type: Mapped[str | None] = mapped_column(String(150), nullable=True)
    file_size_bytes: Mapped[int | None] = mapped_column(Integer, nullable=True)

    status: Mapped[str] = mapped_column(String(30), nullable=False, default="uploaded", index=True)
    visibility: Mapped[str] = mapped_column(String(30), nullable=False, default="employee_and_hr", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    verified_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    verified_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    verification_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    uploaded_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    document_type = relationship("EmployeeDocumentType", foreign_keys=[document_type_id])
    verified_by = relationship("User", foreign_keys=[verified_by_user_id])
    uploaded_by = relationship("User", foreign_keys=[uploaded_by_user_id])



class EmployeeOnboardingChecklistItem(CommonBase):
    """Reusable tenant/branch onboarding checklist master."""

    __tablename__ = "employee_onboarding_checklist_items"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "code", name="uq_employee_onboarding_items_tenant_branch_code"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)

    code: Mapped[str] = mapped_column(String(50), nullable=False, index=True)
    title: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    stage: Mapped[str] = mapped_column(String(50), nullable=False, default="joining", index=True)
    default_due_days: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    sort_order: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_mandatory: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )


class EmployeeOnboardingTask(CommonBase):
    """Employee-specific onboarding checklist task."""

    __tablename__ = "employee_onboarding_tasks"
    __table_args__ = (
        UniqueConstraint("tenant_id", "employee_id", "checklist_item_id", name="uq_employee_onboarding_task_employee_item"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    checklist_item_id: Mapped[int | None] = mapped_column(ForeignKey("employee_onboarding_checklist_items.id", ondelete="SET NULL"), nullable=True, index=True)

    title: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    stage: Mapped[str] = mapped_column(String(50), nullable=False, default="joining", index=True)
    due_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    assigned_to_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)

    completed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    checklist_item = relationship("EmployeeOnboardingChecklistItem", foreign_keys=[checklist_item_id])
    assigned_to = relationship("User", foreign_keys=[assigned_to_user_id])
    completed_by = relationship("User", foreign_keys=[completed_by_user_id])


class EmployeeOffboardingRequest(CommonBase):
    """Employee resignation/relieving/offboarding workflow request."""

    __tablename__ = "employee_offboarding_requests"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)

    request_type: Mapped[str] = mapped_column(String(50), nullable=False, default="resignation", index=True)
    requested_relieving_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    approved_relieving_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    reason: Mapped[str | None] = mapped_column(Text, nullable=True)
    handover_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)

    requested_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    reviewed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    reviewed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)
    completed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)

    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    employee = relationship("Employee", foreign_keys=[employee_id])
    requested_by = relationship("User", foreign_keys=[requested_by_user_id])
    reviewed_by = relationship("User", foreign_keys=[reviewed_by_user_id])
    completed_by = relationship("User", foreign_keys=[completed_by_user_id])


class EmployeeOffboardingTask(CommonBase):
    """Offboarding checklist task linked to an offboarding request."""

    __tablename__ = "employee_offboarding_tasks"

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    request_id: Mapped[int] = mapped_column(ForeignKey("employee_offboarding_requests.id", ondelete="CASCADE"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)

    title: Mapped[str] = mapped_column(String(200), nullable=False)
    description: Mapped[str | None] = mapped_column(Text, nullable=True)
    due_date: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="pending", index=True)
    assigned_to_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    completed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    completed_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    review_notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False
    )
    updated_at_utc: Mapped[datetime] = mapped_column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False,
    )

    request = relationship("EmployeeOffboardingRequest", foreign_keys=[request_id])
    employee = relationship("Employee", foreign_keys=[employee_id])
    assigned_to = relationship("User", foreign_keys=[assigned_to_user_id])
    completed_by = relationship("User", foreign_keys=[completed_by_user_id])



class EmployeeSalaryStructure(CommonBase):
    """Employee salary structure header for Phase 6G payroll foundation."""

    __tablename__ = "employee_salary_structures"
    __table_args__ = (
        UniqueConstraint("tenant_id", "employee_id", "effective_from", name="uq_employee_salary_structure_effective"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)

    effective_from: Mapped[date] = mapped_column(Date, nullable=False, index=True)
    effective_to: Mapped[date | None] = mapped_column(Date, nullable=True, index=True)
    pay_cycle: Mapped[str] = mapped_column(String(30), nullable=False, default="monthly", index=True)
    monthly_ctc_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    basic_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    hra_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    allowance_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    employee_pf_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    employee_esi_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    professional_tax_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    tds_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    other_deduction_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    is_active: Mapped[bool] = mapped_column(Boolean, nullable=False, default=True, index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    created_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    updated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    employee = relationship("Employee", foreign_keys=[employee_id])


class EmployeePayrollRun(CommonBase):
    """Monthly payroll run header."""

    __tablename__ = "employee_payroll_runs"
    __table_args__ = (
        UniqueConstraint("tenant_id", "branch_id", "pay_year", "pay_month", name="uq_employee_payroll_run_period"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    pay_year: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    pay_month: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    run_name: Mapped[str] = mapped_column(String(150), nullable=False)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="draft", index=True)
    total_employees: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    gross_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    deduction_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    net_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    notes: Mapped[str | None] = mapped_column(Text, nullable=True)

    processed_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    approved_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    approved_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    paid_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    paid_at_utc: Mapped[datetime | None] = mapped_column(DateTime(timezone=True), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    processed_by = relationship("User", foreign_keys=[processed_by_user_id])
    approved_by = relationship("User", foreign_keys=[approved_by_user_id])
    paid_by = relationship("User", foreign_keys=[paid_by_user_id])


class EmployeePayslip(CommonBase):
    """Employee payslip generated from an approved salary structure."""

    __tablename__ = "employee_payslips"
    __table_args__ = (
        UniqueConstraint("tenant_id", "payroll_run_id", "employee_id", name="uq_employee_payslip_run_employee"),
    )

    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
    tenant_id: Mapped[int] = mapped_column(ForeignKey("tenants.id", ondelete="CASCADE"), nullable=False, index=True)
    branch_id: Mapped[int] = mapped_column(ForeignKey("branches.id"), nullable=False, index=True)
    payroll_run_id: Mapped[int] = mapped_column(ForeignKey("employee_payroll_runs.id", ondelete="CASCADE"), nullable=False, index=True)
    employee_id: Mapped[int] = mapped_column(ForeignKey("employees.id", ondelete="CASCADE"), nullable=False, index=True)
    salary_structure_id: Mapped[int | None] = mapped_column(ForeignKey("employee_salary_structures.id", ondelete="SET NULL"), nullable=True, index=True)

    pay_year: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    pay_month: Mapped[int] = mapped_column(Integer, nullable=False, index=True)
    basic_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    hra_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    allowance_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    gross_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    employee_pf_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    employee_esi_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    professional_tax_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    tds_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    other_deduction_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    deduction_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    net_amount: Mapped[int] = mapped_column(Integer, nullable=False, default=0)
    status: Mapped[str] = mapped_column(String(30), nullable=False, default="generated", index=True)
    remarks: Mapped[str | None] = mapped_column(Text, nullable=True)

    generated_by_user_id: Mapped[int | None] = mapped_column(ForeignKey("users.id", ondelete="SET NULL"), nullable=True)
    created_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at_utc: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc), nullable=False)

    payroll_run = relationship("EmployeePayrollRun", foreign_keys=[payroll_run_id])
    employee = relationship("Employee", foreign_keys=[employee_id])
    salary_structure = relationship("EmployeeSalaryStructure", foreign_keys=[salary_structure_id])
    generated_by = relationship("User", foreign_keys=[generated_by_user_id])
