"""SQLAlchemy модели."""
from datetime import datetime
from sqlalchemy import Column, Integer, String, Float, DateTime, Boolean, Text, Index
from app.db.database import Base


class PlateRecord(Base):
    """Запись о распознанном номере."""
    __tablename__ = "plates"

    id              = Column(Integer, primary_key=True, autoincrement=True)
    image_filename  = Column(String, nullable=False)
    annotated_filename = Column(String, nullable=False)

    plate_text      = Column(String(16), index=True)
    plate_text_raw  = Column(String(16))
    is_valid_gost   = Column(Boolean, default=False)

    bbox_x1         = Column(Integer)
    bbox_y1         = Column(Integer)
    bbox_x2         = Column(Integer)
    bbox_y2         = Column(Integer)
    confidence      = Column(Float)
    is_fallback     = Column(Boolean, default=False)
    source_type     = Column(String(10), default="image")    # "image" / "video"
    video_filename  = Column(String, nullable=True)

    detected_at     = Column(DateTime, default=datetime.utcnow, index=True)

    def to_dict(self):
        return {
            "id": self.id,
            "image_filename": self.image_filename,
            "annotated_filename": self.annotated_filename,
            "plate_text": self.plate_text,
            "plate_text_raw": self.plate_text_raw,
            "is_valid_gost": self.is_valid_gost,
            "bbox": [self.bbox_x1, self.bbox_y1, self.bbox_x2, self.bbox_y2],
            "confidence": self.confidence,
            "is_fallback": self.is_fallback,
            "is_fallback": self.is_fallback,
            "source_type": self.source_type or "image",
            "video_filename": self.video_filename,
            "detected_at": self.detected_at.isoformat() if self.detected_at else None,
        }


class Employee(Base):
    """Сотрудник в реестре пропусков."""
    __tablename__ = "employees"

    id              = Column(Integer, primary_key=True, autoincrement=True)
    full_name       = Column(String(200), nullable=False, index=True)
    position        = Column(String(120))                # должность
    department      = Column(String(120), index=True)    # отдел
    plate_number    = Column(String(16), nullable=False, index=True)  # А123ВС777

    access_allowed  = Column(Boolean, default=True, nullable=False)
    notes           = Column(Text)                       # комментарий (за что заблокирован и т.п.)

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

    def to_dict(self):
        return {
            "id": self.id,
            "full_name": self.full_name,
            "position": self.position,
            "department": self.department,
            "plate_number": self.plate_number,
            "access_allowed": self.access_allowed,
            "notes": self.notes,
            "created_at": self.created_at.isoformat() if self.created_at else None,
            "updated_at": self.updated_at.isoformat() if self.updated_at else None,
        }


Index("idx_plate_text_date", PlateRecord.plate_text, PlateRecord.detected_at.desc())
Index("idx_employee_plate", Employee.plate_number)