import uuid
from datetime import datetime, timezone
from sqlalchemy import Column, String, Boolean, DateTime, Text, ForeignKey, UniqueConstraint, CheckConstraint, Index
from sqlalchemy.dialects.postgresql import UUID
from app.core.database import Base


def now_utc():
    return datetime.now(timezone.utc)


class StationUserAssignment(Base):
    """Which user works at which production station, and as what (OPERATOR or QA)."""
    __tablename__ = 'station_user_assignment'
    __table_args__ = (
        UniqueConstraint('user_id', 'station_id', 'role', name='uq_station_user_role'),
        CheckConstraint("role IN ('OPERATOR', 'QA')", name='ck_station_user_role'),
    )

    assignment_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id', ondelete='CASCADE'), nullable=False, index=True)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id', ondelete='CASCADE'), nullable=False, index=True)
    role = Column(String(20), nullable=False)
    is_active = Column(Boolean, nullable=False, default=True)
    created_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class QaResponsePhoto(Base):
    """Photos a QA inspector attaches to one checkpoint of one inspection."""
    __tablename__ = 'qa_response_photo'

    photo_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    instance_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_instance.instance_id', ondelete='CASCADE'), nullable=False, index=True)
    item_id = Column(UUID(as_uuid=True), ForeignKey('qa_checkpoint_item.item_id'), nullable=False)
    photo_url = Column(Text, nullable=False)
    uploaded_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    uploaded_at = Column(DateTime(timezone=True), default=now_utc)


class ChassisStationHandoff(Base):
    """
    Hand-over of a chassis from one station to the next, created when the operator marks
    work complete. Stations with QA checksheets hold the chassis until QA passes it and
    releases it; only then can the next station check it in.
    status: AWAITING_QA -> QA_PASSED -> RELEASED -> RECEIVED  (or SUPERSEDED on rework)
    """
    __tablename__ = 'chassis_station_handoff'
    __table_args__ = (
        CheckConstraint("status IN ('AWAITING_QA', 'QA_PASSED', 'RELEASED', 'RECEIVED', 'SUPERSEDED')", name='ck_handoff_status'),
        Index('ix_chassis_station_handoff_to_station', 'to_station_id', 'status'),
        Index('ix_chassis_station_handoff_from_station', 'from_station_id', 'status'),
    )

    handoff_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id', ondelete='CASCADE'), nullable=False, index=True)
    from_station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=False)
    to_station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True)  # NULL = end of line
    status = Column(String(20), nullable=False)
    completed_at = Column(DateTime(timezone=True), default=now_utc)
    completed_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    released_at = Column(DateTime(timezone=True), nullable=True)
    released_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    received_at = Column(DateTime(timezone=True), nullable=True)
    remarks = Column(Text, nullable=True)


class QaSectionRemark(Base):
    """Inspector's remark on one section of one inspection."""
    __tablename__ = 'qa_section_remark'
    __table_args__ = (UniqueConstraint('instance_id', 'section_id', name='uq_qa_section_remark'),)

    remark_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    instance_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_instance.instance_id', ondelete='CASCADE'), nullable=False, index=True)
    section_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_section.section_id'), nullable=False)
    remark = Column(Text, nullable=False)
    updated_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)