"""
models/code_result.py
One row per code extracted from a transcript chunk.
Mirrors exactly the columns in detailed_justifications.xlsx:
  patient_id → transcript_id
  original_file_id → transcript.original_filename
  chunk_id, code, theme, group, quote,
  raw_evidence_snippet, excel_context, llm_justification
"""
import uuid
from datetime import datetime
from sqlalchemy import Column, String, Text, Integer, DateTime, ForeignKey
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import relationship
from app.database import Base


class CodeResult(Base):
    __tablename__ = "code_results"

    id              = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    project_id      = Column(UUID(as_uuid=True), ForeignKey("projects.id"), nullable=False)
    transcript_id   = Column(UUID(as_uuid=True), ForeignKey("transcripts.id"), nullable=False)
    chunk_id        = Column(Integer, nullable=True)
    group_label     = Column(String, nullable=True)     # FACILITATORS / BARRIERS - ...
    theme           = Column(String, nullable=True)
    code            = Column(String, nullable=True)
    quote           = Column(Text, nullable=True)
    evidence_snippet = Column(Text, nullable=True)      # raw_evidence_snippet
    excel_context   = Column(Text, nullable=True)
    llm_justification = Column(Text, nullable=True)
    created_at      = Column(DateTime, default=datetime.utcnow)

    project    = relationship("Project", back_populates="code_results")
    transcript = relationship("Transcript", back_populates="code_results")

    def __repr__(self):
        return f"<CodeResult {self.code} — {self.group_label}>"