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


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


class DelayReasonMaster(Base):
    """
    Standardized reason codes logged when a chassis is overdue at a station.
    """
    __tablename__ = 'delay_reason_master'

    delay_reason_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    reason_code = Column(String(50), unique=True, nullable=False)
    reason_label = Column(String(200), nullable=False)
    category = Column(String(50), nullable=False, default='OPERATIONAL')  # MATERIAL / COMMERCIAL / QUALITY / OPERATIONAL
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class ChassisStationLog(Base):
    """
    Core shopfloor tracking table.
    Each station visit creates an ARRIVAL log on arrival scan and a COMPLETION log on departure scan.
    """
    __tablename__ = 'chassis_station_log'

    log_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=False)
    event_type = Column(String(20), nullable=False)  # ARRIVAL / COMPLETION
    scanned_at = Column(DateTime(timezone=True), nullable=False, default=now_utc)
    scanned_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    scan_mode = Column(String(20), nullable=False, default='QR_SCAN')  # QR_SCAN / MANUAL_ENTRY

    # Timer & delay tracking (populated on completion or updated by supervisor)
    elapsed_minutes = Column(Integer, nullable=True)
    is_overdue = Column(Boolean, nullable=False, default=False)
    delay_reason_id = Column(UUID(as_uuid=True), ForeignKey('delay_reason_master.delay_reason_id'), nullable=True)
    delay_remarks = Column(Text, nullable=True)
    delay_logged_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    delay_logged_at = Column(DateTime(timezone=True), nullable=True)

    status = Column(String(20), nullable=False, default='AT_STATION')  # AT_STATION / COMPLETED / ON_HOLD
    remarks = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class ProductionHold(Base):
    """
    Tracks formal production holds placed on a chassis (Payment hold, QA rework hold, Client hold, etc.).
    Prevents station movement while ACTIVE.
    """
    __tablename__ = 'production_hold'

    hold_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True)
    hold_type = Column(String(50), nullable=False)  # PAYMENT_HOLD / QA_REWORK / MATERIAL_HOLD / CLIENT_HOLD / OTHER
    hold_reason = Column(Text, nullable=False)
    raised_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=False)
    raised_at = Column(DateTime(timezone=True), default=now_utc)
    resolved_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    resolved_at = Column(DateTime(timezone=True), nullable=True)
    resolution_notes = Column(Text, nullable=True)
    status = Column(String(20), nullable=False, default='ACTIVE')  # ACTIVE / RESOLVED
    created_at = Column(DateTime(timezone=True), default=now_utc)