from datetime import datetime

import sqlalchemy as sa
from sqlalchemy import Column, DateTime, ForeignKey, Integer, JSON, String, Text
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy.orm import relationship

from app.models.base import Base, TimestampMixin


class ToolJob(Base, TimestampMixin):
    __tablename__ = "tool_jobs"

    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False, index=True)
    tool_name = Column(String(128), nullable=False, index=True)
    status = Column(String(32), nullable=False, default="pending")
    input_file_name = Column(String(255), nullable=False)
    input_file_path = Column(String(512), nullable=False)
    error_message = Column(Text, nullable=True)
    started_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    output_summary = Column(JSON, nullable=True)

    user = relationship("User")
    srs_extraction = relationship(
        "SRSExtraction",
        back_populates="job",
        uselist=False,
        cascade="all, delete-orphan",
    )


class SRSExtraction(Base, TimestampMixin):
    __tablename__ = "srs_extractions"

    id = Column(Integer, primary_key=True, index=True)
    job_id = Column(Integer, ForeignKey("tool_jobs.id", ondelete="CASCADE"), nullable=False, unique=True)
    document_name = Column(String(255), nullable=False)
    document_title = Column(String(255), nullable=False)
    generated_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    total_requirements = Column(Integer, nullable=False, default=0)
    statistics = Column(JSON, nullable=True)
    raw_output = Column(JSON, nullable=True)

    job = relationship("ToolJob", back_populates="srs_extraction")
    requirements = relationship(
        "SRSRequirement",
        back_populates="extraction",
        cascade="all, delete-orphan",
        order_by="SRSRequirement.sort_order",
    )


class SRSRequirement(Base, TimestampMixin):
    __tablename__ = "srs_requirements"

    id = Column(Integer, primary_key=True, index=True)
    extraction_id = Column(Integer, ForeignKey("srs_extractions.id", ondelete="CASCADE"), nullable=False)
    requirement_uid = Column(String(64), nullable=False)
    title = Column(String(255), nullable=False)
    description = Column(LONGTEXT, nullable=False)
    priority = Column(String(16), nullable=False, default="中")
    acceptance_criteria = Column(JSON, nullable=False)
    source_field = Column(String(255), nullable=False)
    section_number = Column(String(64), nullable=True)
    section_title = Column(String(255), nullable=True)
    requirement_type = Column(String(64), nullable=True)
    sort_order = Column(Integer, nullable=False, default=0)

    extraction = relationship("SRSExtraction", back_populates="requirements")

    __table_args__ = (
        sa.UniqueConstraint("extraction_id", "requirement_uid", name="uq_srs_extraction_requirement_uid"),
        sa.Index("idx_srs_requirements_extraction_sort", "extraction_id", "sort_order"),
    )