# apps/leave_form_app/models.py

from __future__ import annotations

from datetime import datetime, date
import uuid

from sqlalchemy import (
    Column,
    String,
    Date,
    DateTime,
    Text,
    Boolean,
    Float,
    ForeignKey,
    Integer,
    UniqueConstraint,
    Index,
)
from sqlalchemy.orm import relationship

from config.db import Base


def _uuid() -> str:
    return str(uuid.uuid4())


class RolePositionMap(Base):
    """
    8.1 role_position_map
    Mapea un position de AroFlo a un role lógico de la app.
    """
    __tablename__ = "role_position_map"

    # si AroFlo positionid existe → úsalo. Si no existe, deja null.
    positionid = Column(String(64), primary_key=True)  # PK (string por compatibilidad)
    positionname = Column(String(255), nullable=False)
    role = Column(String(32), nullable=False)  # worker/manager/admin
    is_enabled = Column(Boolean, nullable=False, default=True)

    updated_by_userid = Column(String(64), nullable=True)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)

    __table_args__ = (
        UniqueConstraint("positionname", name="uq_role_position_map_positionname"),
        Index("ix_role_position_map_role", "role"),
        Index("ix_role_position_map_is_enabled", "is_enabled"),
    )


class AppAdmin(Base):
    """
    8.2 app_admins
    Lista de usuarios con permisos administrativos dentro de la app.
    """
    __tablename__ = "app_admins"

    userid = Column(String(64), primary_key=True)  # AroFlo userid (string)
    email = Column(String(255), nullable=False)

    can_map_roles = Column(Boolean, nullable=False, default=False)
    can_upload_to_aroflo = Column(Boolean, nullable=False, default=False)
    is_super_admin = Column(Boolean, nullable=False, default=False)

    __table_args__ = (
        Index("ix_app_admins_email", "email"),
    )


class LeaveRequest(Base):
    """
    8.3 leave_requests
    Request principal (días u horas).
    """
    __tablename__ = "leave_requests"

    request_id = Column(String(36), primary_key=True, default=_uuid)

    # empleado
    employee_userid = Column(String(64), nullable=False)
    employee_email = Column(String(255), nullable=False)
    employee_name = Column(String(255), nullable=False)
    employee_orgname = Column(String(255), nullable=False)
    employee_positionname = Column(String(255), nullable=True)

    # tipo/overhead (AroFlo)
    type_overhead_name = Column(String(255), nullable=False)  # ej: "Annual Leave" / overhead name
    reason = Column(Text, nullable=True)

    # modo: hours/days
    mode = Column(String(16), nullable=False)  # hours | days

    # si mode=hours
    hours_date = Column(Date, nullable=True)
    hours_amount = Column(Float, nullable=True)

    # si mode=days
    start_date = Column(Date, nullable=True)
    end_date = Column(Date, nullable=True)

    # manager asignado a aprobar
    manager_userid = Column(String(64), nullable=True)

    # campos extra
    comments = Column(Text, nullable=True)
    contact_details = Column(Text, nullable=True)

    # contexto para UI automation (si aplica)
    bu_name = Column(String(255), nullable=True)        # solo manager/admin
    tracking_name = Column(String(255), nullable=True)  # solo manager/admin
    worktype = Column(String(32), nullable=False, default="NT")

    manager_decision = Column(String(16), nullable=False, default="pending")  # pending/approved/rejected
    manager_decision_at = Column(DateTime, nullable=True)

    status = Column(String(32), nullable=False, default="pending")  # estado global (puede evolucionar)
    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)
    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)

    attachments = relationship(
        "LeaveAttachment",
        back_populates="leave",
        cascade="all, delete-orphan",
    )

    upload_jobs = relationship(
        "LeaveUploadJob",
        back_populates="leave",
        cascade="all, delete-orphan",
    )

    __table_args__ = (
        Index("ix_leave_requests_employee_userid", "employee_userid"),
        Index("ix_leave_requests_manager_userid", "manager_userid"),
        Index("ix_leave_requests_orgname", "employee_orgname"),
        Index("ix_leave_requests_decision", "manager_decision"),
        Index("ix_leave_requests_created_at", "created_at"),
    )


class LeaveAttachment(Base):
    """
    8.4 leave_attachments
    Adjuntos ligados a un request.
    """
    __tablename__ = "leave_attachments"

    attachment_id = Column(String(36), primary_key=True, default=_uuid)
    request_id = Column(String(36), ForeignKey("leave_requests.request_id"), nullable=False)

    filename = Column(String(255), nullable=False)
    mime = Column(String(128), nullable=False)
    storage_path = Column(String(512), nullable=False)

    comment = Column(Text, nullable=True)
    filter = Column(String(128), nullable=True)  # Internal Only / Show Contractor / etc.

    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)

    leave = relationship("LeaveRequest", back_populates="attachments")

    __table_args__ = (
        Index("ix_leave_attachments_request_id", "request_id"),
    )


class LeaveUploadJob(Base):
    """
    8.5 leave_upload_jobs
    Auditoría de ejecuciones UI automation (create y upload docs).
    """
    __tablename__ = "leave_upload_jobs"

    job_id = Column(String(36), primary_key=True, default=_uuid)
    request_id = Column(String(36), ForeignKey("leave_requests.request_id"), nullable=False)

    run_by_userid = Column(String(64), nullable=False)

    status = Column(String(16), nullable=False)  # success/fail

    ui_create_cmd_snapshot = Column(Text, nullable=True)
    ui_create_stdout = Column(Text, nullable=True)

    ui_upload_docs_cmd_snapshot = Column(Text, nullable=True)
    ui_upload_docs_stdout = Column(Text, nullable=True)

    created_at = Column(DateTime, nullable=False, default=datetime.utcnow)

    leave = relationship("LeaveRequest", back_populates="upload_jobs")

    __table_args__ = (
        Index("ix_leave_upload_jobs_request_id", "request_id"),
        Index("ix_leave_upload_jobs_run_by_userid", "run_by_userid"),
        Index("ix_leave_upload_jobs_created_at", "created_at"),
    )


class BuTrackingMap(Base):
    """
    8.6 bu_tracking_map
    Solo si la relación BU <-> Tracking no la traes de AroFlo.
    """
    __tablename__ = "bu_tracking_map"

    bu_name = Column(String(255), primary_key=True)
    tracking_name = Column(String(255), primary_key=True)

    __table_args__ = (
        Index("ix_bu_tracking_map_bu", "bu_name"),
        Index("ix_bu_tracking_map_tracking", "tracking_name"),
    )


class LeaveOrgDefault(Base):
    """
    8.7 leave_org_defaults
    Defaults por organización (para no mantener settings user por user).
    """
    __tablename__ = "leave_org_defaults"

    orgname = Column(String(255), primary_key=True)

    default_daily_hours = Column(Float, nullable=False, default=7.6)
    default_leave_start_date = Column(Date, nullable=False, default=date(2026, 1, 1))


class LeaveUserSetting(Base):
    """
    8.8 leave_user_settings
    Override por usuario (opcional).
    """
    __tablename__ = "leave_user_settings"

    userid = Column(String(64), primary_key=True)

    daily_hours = Column(Float, nullable=True)
    leave_start_date = Column(Date, nullable=True)

    updated_at = Column(DateTime, nullable=False, default=datetime.utcnow, onupdate=datetime.utcnow)