import uuid
from sqlalchemy import Column, String, Text, ForeignKey, DateTime, Index, UniqueConstraint, Numeric, BigInteger
from sqlalchemy.orm import relationship
from utils.utils import Base, utc_now


class Job(Base):
    __tablename__ = "jobs"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    title = Column(String(500), nullable=False, index=True)
    description = Column(Text, nullable=True)
    job_type_id = Column(String(36), ForeignKey("job_types.id", ondelete="RESTRICT"), nullable=False, index=True)
    job_status_id = Column(String(36), ForeignKey("job_statuses.id", ondelete="RESTRICT"), nullable=False, index=True)
    job_priority_id = Column(String(36), ForeignKey("job_priorities.id", ondelete="RESTRICT"), nullable=False, index=True)
    created_by_user_id = Column(String(36), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False, index=True)
    assigned_to_user_id = Column(String(36), ForeignKey("users.id", ondelete="SET NULL"), nullable=True, index=True)
    department_id = Column(String(36), ForeignKey("departments.id", ondelete="RESTRICT"), nullable=False, index=True)
    team_id = Column(String(36), ForeignKey("teams.id", ondelete="SET NULL"), nullable=True, index=True)
    parent_job_id = Column(String(36), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=True, index=True)
    due_date = Column(DateTime(timezone=True), nullable=True)
    started_at = Column(DateTime(timezone=True), nullable=True)
    completed_at = Column(DateTime(timezone=True), nullable=True)
    estimated_hours = Column(Numeric(10, 2), nullable=True)
    created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False)

    job_type = relationship("Jobtype", back_populates="jobs")
    job_status = relationship("Jobstatus", back_populates="jobs")
    job_priority = relationship("Jobpriority", back_populates="jobs")
    created_by_user = relationship("User", foreign_keys=[created_by_user_id], back_populates="created_jobs")
    assigned_to_user = relationship("User", foreign_keys=[assigned_to_user_id], back_populates="assigned_jobs")
    department = relationship("Department", back_populates="jobs")
    team = relationship("Team", back_populates="jobs")
    parent_job = relationship("Job", remote_side=[id], back_populates="child_jobs")
    child_jobs = relationship("Job", back_populates="parent_job", cascade="all, delete-orphan", passive_deletes=True)
    job_tags = relationship("Jobtag", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)
    comments = relationship("Comment", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)
    attachments = relationship("Attachment", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)
    job_history = relationship("Jobhistory", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)
    time_logs = relationship("Timelog", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)
    checklists = relationship("Checklist", back_populates="job", cascade="all, delete-orphan", passive_deletes=True)

    __table_args__ = (
        Index("ix_jobs_status_created", "job_status_id", "created_at"),
        Index("ix_jobs_assigned_user", "assigned_to_user_id", "job_status_id"),
        Index("ix_jobs_department_team", "department_id", "team_id"),
    )


class Jobtag(Base):
    __tablename__ = "job_tags"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    job_id = Column(String(36), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=False, index=True)
    tag_id = Column(String(36), ForeignKey("tags.id", ondelete="CASCADE"), nullable=False, index=True)
    created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False)

    job = relationship("Job", back_populates="job_tags")
    tag = relationship("Tag", back_populates="job_tags")

    __table_args__ = (
        UniqueConstraint("job_id", "tag_id", name="uq_job_tag"),
    )


class Comment(Base):
    __tablename__ = "comments"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    job_id = Column(String(36), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=False, index=True)
    user_id = Column(String(36), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False, index=True)
    content = Column(Text, nullable=False)
    created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False)

    job = relationship("Job", back_populates="comments")
    user = relationship("User", back_populates="comments")


class Attachment(Base):
    __tablename__ = "attachments"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    job_id = Column(String(36), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=False, index=True)
    uploaded_by_user_id = Column(String(36), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False, index=True)
    file_name = Column(String(500), nullable=False)
    file_path = Column(String(1000), nullable=False)
    file_type = Column(String(100), nullable=True)
    file_size = Column(BigInteger, nullable=False)
    created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False)

    job = relationship("Job", back_populates="attachments")
    uploaded_by_user = relationship("User", back_populates="attachments")


class Jobhistory(Base):
    __tablename__ = "job_history"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    job_id = Column(String(36), ForeignKey("jobs.id", ondelete="CASCADE"), nullable=False, index=True)
    user_id = Column(String(36), ForeignKey("users.id", ondelete="RESTRICT"), nullable=False, index=True)
    action_type = Column(String(100), nullable=False, index=True)
    field_name = Column(String(255), nullable=True)
    old_value = Column(Text, nullable=True)
    new_value = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), default=utc_now, nullable=False)
    updated_at = Column(DateTime(timezone=True), default=utc_now, onupdate=utc_now, nullable=False)

    job = relationship("Job", back_populates="job_history")
    user = relationship("User", back_populates="job_history_entries")

    __table_args__ = (
        Index("ix_job_history_created", "job_id", "created_at"),
    )