"""SQLAlchemy model for procedure-improvement suggestions.

When an operator submits a round with a filled-in procedure field
(``procedure_text`` for Red, ``detect_procedure`` for Blue), the command(s)
in it are extracted heuristically and proposed as an update to the
originating template's suggested-procedure field. Nothing is written to
the template automatically — a lead reviews and approves or rejects each
suggestion, so a junior who later picks up the same template only ever
sees vetted guidance.
"""

import uuid

from sqlalchemy import Column, DateTime, ForeignKey, String, Text, func
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship

from app.database import Base


class ProcedureSuggestion(Base):
    """A proposed update to a TestTemplate's suggested procedure, pending lead review."""

    __tablename__ = "procedure_suggestions"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    template_id = Column(UUID(as_uuid=True), ForeignKey("test_templates.id"), nullable=False)
    team = Column(String(10), nullable=False)  # "red" or "blue"
    suggested_text = Column(Text, nullable=False)
    source_test_id = Column(UUID(as_uuid=True), ForeignKey("tests.id"), nullable=True)
    submitted_by = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)

    status = Column(String(10), nullable=False, default="pending", server_default="pending")  # pending/approved/rejected
    reviewed_by = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=True)
    reviewed_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime(timezone=True), server_default=func.now())

    template = relationship("TestTemplate", foreign_keys=[template_id])
    source_test = relationship("Test", foreign_keys=[source_test_id])
    submitter = relationship("User", foreign_keys=[submitted_by])
    reviewer = relationship("User", foreign_keys=[reviewed_by])