backend/database.py

import os
from datetime import datetime
from uuid import uuid4
from sqlalchemy import create_engine, Column, String, Boolean, DateTime, Text, Integer, ForeignKey, JSON
from sqlalchemy.orm import declarative_base, sessionmaker, relationship
from passlib.context import CryptContext

Base = declarative_base()
pwd_context = CryptContext(schemes=["bcrypt"], deprecated="auto")

DATABASE_PATH = os.environ.get("DATA_DIR", "/data") + "/platform.db"
engine = create_engine(f"sqlite:///{DATABASE_PATH}", connect_args={"check_same_thread": False})
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)


class User(Base):
    __tablename__ = "users"
    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    email = Column(String, unique=True, nullable=False)
    hashed_password = Column(String, nullable=False)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    api_keys = relationship("APIKey", back_populates="user", cascade="all, delete-orphan")
    connections = relationship("Connection", back_populates="user", cascade="all, delete-orphan")


class APIKey(Base):
    __tablename__ = "api_keys"
    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    key_hash = Column(String, nullable=False, index=True)
    name = Column(String, nullable=False)
    last_used = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    is_active = Column(Boolean, default=True)
    user = relationship("User", back_populates="api_keys")


class Integration(Base):
    __tablename__ = "integrations"
    id = Column(String, primary_key=True)
    name = Column(String, nullable=False)
    description = Column(String, nullable=False)
    logo_url = Column(String, nullable=False)
    category = Column(String, nullable=False)
    auth_type = Column(String, nullable=False)
    oauth_scopes = Column(JSON, nullable=True)
    is_active = Column(Boolean, default=True)
    tool_count = Column(Integer, default=0)
    trigger_count = Column(Integer, default=0)
    tools = relationship("Tool", back_populates="integration", cascade="all, delete-orphan")


class AuthConfig(Base):
    """Dynamic credential schema per integration - defines what fields are needed."""
    __tablename__ = "auth_configs"
    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    integration_id = Column(String, ForeignKey("integrations.id"), nullable=False)
    auth_type = Column(String, nullable=False)  # oauth2, api_key, bearer, basic
    fields = Column(JSON, nullable=False)  # [{name, label, type, required, description, default}]
    oauth_authorize_url = Column(String, nullable=True)
    oauth_token_url = Column(String, nullable=True)
    is_active = Column(Boolean, default=True)
    integration = relationship("Integration")


class Connection(Base):
    __tablename__ = "connections"
    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    integration_id = Column(String, ForeignKey("integrations.id"), nullable=False)
    account_label = Column(String, nullable=True)
    status = Column(String, default="active")
    encrypted_credentials = Column(Text, nullable=False)
    connected_at = Column(DateTime, default=datetime.utcnow)
    last_used = Column(DateTime, nullable=True)
    conn_metadata = Column("metadata", JSON, nullable=True)
    user = relationship("User", back_populates="connections")
    integration = relationship("Integration")


class Tool(Base):
    __tablename__ = "tools"
    id = Column(String, primary_key=True)
    integration_id = Column(String, ForeignKey("integrations.id"), nullable=False)
    name = Column(String, nullable=False)
    description = Column(String, nullable=False)
    input_schema = Column(JSON, nullable=False)
    output_schema = Column(JSON, nullable=True)
    category = Column(String, nullable=True)
    is_active = Column(Boolean, default=True)
    integration = relationship("Integration", back_populates="tools")


class ToolExecution(Base):
    __tablename__ = "tool_executions"
    id = Column(String, primary_key=True, default=lambda: str(uuid4()))
    user_id = Column(String, ForeignKey("users.id"), nullable=False)
    tool_id = Column(String, ForeignKey("tools.id"), nullable=False)
    connection_id = Column(String, ForeignKey("connections.id"), nullable=True)
    input_params = Column(JSON, nullable=True)
    output_result = Column(JSON, nullable=True)
    status = Column(String, default="success")
    latency_ms = Column(Integer, default=0)
    error_message = Column(Text, nullable=True)
    executed_at = Column(DateTime, default=datetime.utcnow)
    source = Column(String, default="api")
    user = relationship("User")
    tool = relationship("Tool")
    connection = relationship("Connection")


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def init_db():
    Base.metadata.create_all(bind=engine)
    db = SessionLocal()
    try:
        existing = db.query(User).filter(User.email == "admin@platform.local").first()
        if not existing:
            admin = User(
                id=str(uuid4()),
                email="admin@platform.local",
                hashed_password=pwd_context.hash("admin123"),
                is_active=True
            )
            db.add(admin)
            db.commit()
    finally:
        db.close()