from sqlalchemy import Column, Integer, String, DateTime, Text, JSON, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime

Base = declarative_base()

class Workflow(Base):
    __tablename__ = "workflows"
    id = Column(Integer, primary_key=True)
    workflow_id = Column(String(64), unique=True, nullable=False)
    name = Column(String(128), nullable=False)
    definition = Column(JSON, nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    version = Column(Integer, default=1)

    executions = relationship("WorkflowExecution", back_populates="workflow")
    steps = relationship("WorkflowStep", back_populates="workflow")  # <-- added

class WorkflowStep(Base):
    __tablename__ = "workflow_steps"
    id = Column(Integer, primary_key=True)
    step_id = Column(String(64), unique=True, nullable=False)
    workflow_id = Column(Integer, ForeignKey("workflows.id"))
    name = Column(String(128), nullable=False)
    step_type = Column(String(32), nullable=False)
    config = Column(JSON, nullable=False)
    order = Column(Integer, default=0)
    workflow = relationship("Workflow", back_populates="steps")

class WorkflowExecution(Base):
    __tablename__ = "workflow_executions"
    id = Column(Integer, primary_key=True)
    execution_id = Column(String(64), unique=True, nullable=False)
    workflow_id = Column(Integer, ForeignKey("workflows.id"))
    status = Column(String(32), default="pending")
    started_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    current_step_index = Column(Integer, default=0)
    context = Column(JSON, default={})
    error = Column(Text, nullable=True)
    workflow = relationship("Workflow", back_populates="executions")
    step_results = relationship("StepResult", back_populates="execution")
    approvals = relationship("ApprovalRequest", back_populates="execution")

class StepResult(Base):
    __tablename__ = "step_results"
    id = Column(Integer, primary_key=True)
    execution_id = Column(Integer, ForeignKey("workflow_executions.id"))
    step_id = Column(String(64), nullable=False)
    status = Column(String(32), default="pending")
    result = Column(JSON, nullable=True)
    error = Column(Text, nullable=True)
    started_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    execution = relationship("WorkflowExecution", back_populates="step_results")

class ApprovalRequest(Base):
    __tablename__ = "approval_requests"
    id = Column(Integer, primary_key=True)
    approval_id = Column(String(64), unique=True, nullable=False)
    execution_id = Column(Integer, ForeignKey("workflow_executions.id"))
    step_id = Column(String(64), nullable=False)
    status = Column(String(32), default="pending")
    requested_at = Column(DateTime, default=datetime.utcnow)
    responded_at = Column(DateTime, nullable=True)
    comment = Column(Text, nullable=True)
    execution = relationship("WorkflowExecution", back_populates="approvals")