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


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


class QaStationTemplateMap(Base):
    """
    Maps QA Checksheet Templates to Production Stations (many-to-many),
    optionally scoped to specific vehicle models and body types.
    """
    __tablename__ = 'qa_station_template_map'

    map_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=False)
    template_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_template.template_id'), nullable=False)
    model_id = Column(UUID(as_uuid=True), ForeignKey('vehicle_model_master.model_id'), nullable=True)
    model_ids = Column(JSONB, nullable=True, default=list)
    body_type_id = Column(UUID(as_uuid=True), ForeignKey('body_type_master.body_type_id'), nullable=True)
    body_type_ids = Column(JSONB, nullable=True, default=list)
    sort_order = Column(Integer, default=1)
    is_mandatory = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class QaChecksheetTemplate(Base):
    """
    Checksheet Header Definition (e.g., In-Process Outer Paneling Check Sheet).
    """
    __tablename__ = 'qa_checksheet_template'

    template_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    template_code = Column(String(50), unique=True, nullable=False)
    template_name = Column(String(200), nullable=False)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=True)
    format_no = Column(String(50), nullable=True)     # e.g., CTC Format (4)
    revision = Column(String(20), nullable=True)      # e.g., Rev 09
    revision_date = Column(Date, nullable=True)       # e.g., 2026-05-01
    description = Column(Text, nullable=True)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)
    updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)


class QaChecksheetSection(Base):
    """
    Sections within a checksheet template (e.g., Special Features, Outer Structural Panel).
    """
    __tablename__ = 'qa_checksheet_section'

    section_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    template_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_template.template_id'), nullable=False)
    section_no = Column(Integer, nullable=False, default=1)
    section_name = Column(String(200), nullable=False)
    description = Column(Text, nullable=True)
    sort_order = Column(Integer, default=1)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class QaCheckpointItem(Base):
    """
    Individual Inspection Point / Checkpoint Item.
    """
    __tablename__ = 'qa_checkpoint_item'

    item_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    section_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_section.section_id'), nullable=False)
    item_no = Column(Integer, nullable=False, default=1)
    area_desc = Column(String(255), nullable=True)
    checkpoint_desc = Column(Text, nullable=False)
    checkpoint_class = Column(String(20), nullable=False, default='MAJOR')  # CRITICAL / MAJOR / MINOR
    verification_method = Column(String(100), nullable=True)  # VISUAL / MEASUREMENT / TORQUE_WRENCH / TIGHTNESS / TEST
    standard_specification = Column(Text, nullable=True)
    requires_measurement = Column(Boolean, nullable=False, default=False)
    min_value = Column(Numeric(10, 3), nullable=True)
    max_value = Column(Numeric(10, 3), nullable=True)
    unit_of_measure = Column(String(20), nullable=True)  # mm / Nm / bar / kg
    special_feature_flag = Column(String(100), nullable=True)  # e.g., 'EMERGENCY_DOOR', 'SCHOOL_BUS'
    frequency = Column(String(100), nullable=True)
    model_specs = Column(JSONB, nullable=True, default=dict)  # Model-specific targets & tolerances { MODEL_CODE: { target, min, max, tol, uom } }
    sub_points = Column(JSONB, nullable=True, default=list)    # Multi-point sub-checks [ { code, label, spec, min, max, uom, tool } ]
    reference_diagram_url = Column(String(500), nullable=True) # Schematic / aperture diagram URL
    sort_order = Column(Integer, default=1)
    is_active = Column(Boolean, nullable=False, default=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class QaChecksheetInstance(Base):
    """
    An actual inspection performed on a specific chassis at a station.
    """
    __tablename__ = 'qa_checksheet_instance'

    instance_id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    template_id = Column(UUID(as_uuid=True), ForeignKey('qa_checksheet_template.template_id'), nullable=False)
    chassis_id = Column(UUID(as_uuid=True), ForeignKey('chassis_master.chassis_id'), nullable=False)
    chassis_no = Column(String(50), nullable=True)
    station_id = Column(UUID(as_uuid=True), ForeignKey('station_master.station_id'), nullable=False)
    station_log_id = Column(UUID(as_uuid=True), ForeignKey('chassis_station_log.log_id'), nullable=True)
    inspector_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    inspection_date = Column(DateTime(timezone=True), default=now_utc)
    shift = Column(String(20), nullable=True)
    status = Column(String(20), nullable=True)
    overall_result = Column(String(20), nullable=False, default='PENDING')  # PENDING / PASSED / FAILED / ON_HOLD
    station_qa_status = Column(String(20), nullable=False, default='PENDING')  # PENDING / IN_PROGRESS / PASSED / FAILED / ON_HOLD
    remarks = Column(Text, nullable=True)
    rework_notes = Column(Text, nullable=True)
    completed_at = Column(DateTime(timezone=True), nullable=True)
    passed_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)
    updated_at = Column(DateTime(timezone=True), default=now_utc, onupdate=now_utc)


class QaCheckpointResponse(Base):
    """
    Response recorded for each item in an inspection instance.
    """
    __tablename__ = 'qa_checkpoint_response'

    response_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'), nullable=False)
    item_id = Column(UUID(as_uuid=True), ForeignKey('qa_checkpoint_item.item_id'), nullable=False)
    status = Column(String(20), nullable=False, default='OK')  # OK / NOT_OK / NA
    measured_value = Column(Numeric(10, 3), nullable=True)
    sub_point_values = Column(JSONB, nullable=True, default=dict) # Measured readings for sub-points { "A": 8.1, "B": 7.9 }
    observation = Column(Text, nullable=True)
    rework_status = Column(String(20), nullable=True)  # PENDING / DONE
    after_rework_observation = Column(Text, nullable=True)
    after_rework_verified_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    na_reason = Column(Text, nullable=True)
    is_flagged = Column(Boolean, nullable=False, default=False)
    flagged_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    flagged_at = Column(DateTime(timezone=True), nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)


class QaChecksheetSignoff(Base):
    """
    Digital Sign-off for a QA Checksheet Instance.
    """
    __tablename__ = 'qa_checksheet_signoff'

    signoff_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'), nullable=False)
    signoff_type = Column(String(50), nullable=False, default='INSPECTOR')
    signed_by_user_id = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    signed_by_name = Column(String(255), nullable=False, default='QA Inspector')
    signed_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=True)
    role = Column(String(50), nullable=True, default='INSPECTOR')  # INSPECTOR / SUPERVISOR / MANAGER
    signed_at = Column(DateTime(timezone=True), default=now_utc)
    remarks = Column(Text, nullable=True)


class QaShortageLog(Base):
    """
    Digitized shortage tracker (from Shortage sheet).
    Tracks BOM parts missing/unfitted for a chassis at a station.
    """
    __tablename__ = 'qa_shortage_log'

    shortage_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)
    bom_snapshot_id = Column(UUID(as_uuid=True), ForeignKey('chassis_bom_snapshot.id'), nullable=True)
    rm_id = Column(UUID(as_uuid=True), ForeignKey('rm_master.rm_id'), nullable=False)
    required_qty = Column(Numeric(14, 3), nullable=False, default=1)
    fitted_qty = Column(Numeric(14, 3), nullable=False, default=0)
    status = Column(String(20), nullable=False, default='OPEN')  # OPEN / RESOLVED
    noted_by = Column(UUID(as_uuid=True), ForeignKey('login_users.user_id'), nullable=False)
    noted_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)
    remarks = Column(Text, nullable=True)
    created_at = Column(DateTime(timezone=True), default=now_utc)