app/models.py

from flask_sqlalchemy import SQLAlchemy
from datetime import datetime
import enum

db = SQLAlchemy()

class Country(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(100), unique=True, nullable=False)
    region = db.Column(db.String(100), nullable=False)
    cases = db.relationship("Case", backref="country_context", lazy=True)
    reports = db.relationship("Report", backref="report_country_context", lazy=True)

    def __repr__(self):
        return f"<Country {self.name}>"

class CaseStatus(enum.Enum):
    ACTIVE = "Active"
    PENDING = "Pending"
    COMPLETED = "Completed"
    CLOSED = "Closed"

class Case(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    case_id_display = db.Column(db.String(50), unique=True, nullable=False)
    case_type = db.Column(db.String(100), nullable=False)
    suspect_name = db.Column(db.String(150), nullable=False)
    profile_details = db.Column(db.Text, nullable=True)
    evidence_summary = db.Column(db.Text, nullable=True)
    status = db.Column(db.Enum(CaseStatus), default=CaseStatus.PENDING, nullable=False)
    created_at = db.Column(db.DateTime, default=datetime.utcnow)
    updated_at = db.Column(db.DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    country_id = db.Column(db.Integer, db.ForeignKey("country.id"), nullable=True) # For recommendation context

    interrogation_sessions = db.relationship("InterrogationSession", backref="case", lazy=True, cascade="all, delete-orphan")
    reports = db.relationship("Report", backref="case", lazy=True, cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Case {self.case_id_display}>"

class InterrogationSession(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    case_id = db.Column(db.Integer, db.ForeignKey("case.id"), nullable=False)
    session_date = db.Column(db.DateTime, default=datetime.utcnow)
    summary_notes = db.Column(db.Text, nullable=True)
    generated_questions = db.relationship("GeneratedQuestion", backref="session", lazy=True, cascade="all, delete-orphan")

    def __repr__(self):
        return f"<InterrogationSession {self.id} for Case {self.case_id}>"

class GeneratedQuestion(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    interrogation_session_id = db.Column(db.Integer, db.ForeignKey("interrogation_session.id"), nullable=False)
    question_text = db.Column(db.Text, nullable=False)
    category = db.Column(db.String(100), nullable=True)
    generated_at = db.Column(db.DateTime, default=datetime.utcnow)
    responses = db.relationship("InterrogationResponse", backref="question", lazy=True, cascade="all, delete-orphan")

    def __repr__(self):
        return f"<GeneratedQuestion {self.id}>"

class InterrogationResponse(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    generated_question_id = db.Column(db.Integer, db.ForeignKey("generated_question.id"), nullable=False)
    response_text = db.Column(db.Text, nullable=True)
    tags = db.Column(db.String(200), nullable=True) # e.g., "evasiveness,contradiction"
    responded_at = db.Column(db.DateTime, default=datetime.utcnow)

    def __repr__(self):
        return f"<InterrogationResponse {self.id} for Question {self.generated_question_id}>"

class Report(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    case_id = db.Column(db.Integer, db.ForeignKey("case.id"), nullable=False)
    generated_at = db.Column(db.DateTime, default=datetime.utcnow)
    llm_json_output = db.Column(db.Text, nullable=True) # Raw JSON from LLM
    report_content_summary = db.Column(db.Text, nullable=True) # Processed summary for display
    recommendations = db.Column(db.Text, nullable=True) # Country-specific recommendations
    country_id = db.Column(db.Integer, db.ForeignKey("country.id"), nullable=True) # Context for recommendations

    def __repr__(self):
        return f"<Report {self.id} for Case {self.case_id}>"