import datetime
import bcrypt
from sqlalchemy import Column, Integer, String, Text, DateTime, JSON, ForeignKey, Table, Boolean, Float
from sqlalchemy.orm import relationship
from app.database import Base


batch_plan_concepts = Table(
    "batch_plan_concepts",
    Base.metadata,
    Column("batch_plan_id", Integer, ForeignKey("batch_plans.id"), primary_key=True),
    Column("concept_id", Integer, ForeignKey("concepts.id"), primary_key=True),
)


class AuraUser(Base):
    __tablename__ = "aura_users"

    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(100), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    is_admin = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    last_login = Column(DateTime, nullable=True)

    def set_password(self, password: str):
        self.password_hash = bcrypt.hashpw(password.encode("utf-8"), bcrypt.gensalt()).decode("utf-8")

    def check_password(self, password: str) -> bool:
        return bcrypt.checkpw(password.encode("utf-8"), self.password_hash.encode("utf-8"))


class AppSettings(Base):
    __tablename__ = "app_settings"

    id = Column(Integer, primary_key=True, index=True)
    key = Column(String(100), unique=True, nullable=False, index=True)
    value = Column(Text, nullable=True)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class Concept(Base):
    __tablename__ = "concepts"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(500), nullable=False)
    raw_input = Column(Text, nullable=False)
    status = Column(String(20), nullable=False, default="captured")
    source_type = Column(String(20), nullable=False, default="manual")
    source_id = Column(Integer, nullable=True)
    domain = Column(String(255), nullable=True)
    context_snapshot = Column(JSON, nullable=True)
    tags = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    revisions = relationship("ConceptRevision", back_populates="concept", cascade="all, delete-orphan", order_by="ConceptRevision.created_at.desc()")
    batch_plans = relationship("BatchPlan", secondary=batch_plan_concepts, back_populates="concepts")

    def to_dict(self):
        return {
            "id": self.id,
            "title": self.title,
            "raw_input": self.raw_input,
            "status": self.status,
            "source_type": self.source_type,
            "source_id": self.source_id,
            "domain": self.domain,
            "context_snapshot": self.context_snapshot,
            "tags": self.tags,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
            "revision_count": len(self.revisions) if self.revisions else 0,
        }


class ConceptRevision(Base):
    __tablename__ = "concept_revisions"

    id = Column(Integer, primary_key=True, index=True)
    concept_id = Column(Integer, ForeignKey("concepts.id"), nullable=False)
    revision_type = Column(String(50), nullable=False)
    content = Column(JSON, nullable=False)
    context_snapshot = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    concept = relationship("Concept", back_populates="revisions")

    def to_dict(self):
        return {
            "id": self.id,
            "concept_id": self.concept_id,
            "revision_type": self.revision_type,
            "content": self.content,
            "context_snapshot": self.context_snapshot,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class BatchPlan(Base):
    __tablename__ = "batch_plans"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(500), nullable=False)
    status = Column(String(20), nullable=False, default="draft")
    plan_content = Column(JSON, nullable=True)
    dependency_graph = Column(JSON, nullable=True)
    parallel_tracks = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    concepts = relationship("Concept", secondary=batch_plan_concepts, back_populates="batch_plans")

    def to_dict(self):
        return {
            "id": self.id,
            "title": self.title,
            "status": self.status,
            "plan_content": self.plan_content,
            "dependency_graph": self.dependency_graph,
            "parallel_tracks": self.parallel_tracks,
            "concept_ids": [c.id for c in self.concepts] if self.concepts else [],
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }


class FtpProfile(Base):
    __tablename__ = "ftp_profiles"

    id = Column(Integer, primary_key=True, index=True)
    label = Column(String(255), nullable=False)
    host = Column(String(255), nullable=False)
    port = Column(Integer, nullable=False, default=22)
    username = Column(String(255), nullable=False)
    encrypted_password = Column(Text, nullable=False)
    base_path = Column(String(500), nullable=False, default="/")
    protocol = Column(String(10), nullable=False, default="sftp")
    is_default = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    bindings = relationship("FtpProjectBinding", back_populates="profile", cascade="all, delete-orphan")

    def to_dict(self, include_host=True):
        d = {
            "id": self.id,
            "label": self.label,
            "port": self.port,
            "protocol": self.protocol,
            "base_path": self.base_path,
            "is_default": self.is_default,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }
        if include_host:
            d["host"] = self.host
            d["username"] = self.username
        return d


class FtpProjectBinding(Base):
    __tablename__ = "ftp_project_bindings"

    id = Column(Integer, primary_key=True, index=True)
    ftp_profile_id = Column(Integer, ForeignKey("ftp_profiles.id"), nullable=False)
    domain = Column(String(255), nullable=False, index=True)
    target_directory = Column(String(500), nullable=False)
    is_default = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    profile = relationship("FtpProfile", back_populates="bindings")

    def to_dict(self):
        return {
            "id": self.id,
            "ftp_profile_id": self.ftp_profile_id,
            "domain": self.domain,
            "target_directory": self.target_directory,
            "is_default": self.is_default,
            "profile_label": self.profile.label if self.profile else None,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class DeploymentLog(Base):
    __tablename__ = "deployment_logs"

    id = Column(Integer, primary_key=True, index=True)
    ftp_profile_id = Column(Integer, ForeignKey("ftp_profiles.id"), nullable=False)
    domain = Column(String(255), nullable=False, index=True)
    status = Column(String(20), nullable=False, default="queued")
    files_uploaded = Column(Integer, default=0)
    files_total = Column(Integer, default=0)
    duration_seconds = Column(Integer, nullable=True)
    error_message = Column(Text, nullable=True)
    started_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    def to_dict(self):
        return {
            "id": self.id,
            "ftp_profile_id": self.ftp_profile_id,
            "domain": self.domain,
            "status": self.status,
            "files_uploaded": self.files_uploaded,
            "files_total": self.files_total,
            "duration_seconds": self.duration_seconds,
            "error_message": self.error_message,
            "started_at": self.started_at.isoformat() + "Z" if self.started_at else None,
            "completed_at": self.completed_at.isoformat() + "Z" if self.completed_at else None,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class SiteSnapshot(Base):
    __tablename__ = "site_snapshots"

    id = Column(Integer, primary_key=True, index=True)
    domain = Column(String(255), nullable=False, index=True)
    snapshot_type = Column(String(20), nullable=False, default="site")
    version = Column(Integer, nullable=False, default=1)
    file_path = Column(Text, nullable=True)
    thumbnail_data = Column(Text, nullable=True)
    content_hash = Column(String(64), nullable=True)
    package_updated_at = Column(DateTime, nullable=True)
    meta = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    def to_dict(self):
        return {
            "id": self.id,
            "domain": self.domain,
            "snapshot_type": self.snapshot_type,
            "version": self.version,
            "file_path": self.file_path,
            "content_hash": self.content_hash,
            "package_updated_at": self.package_updated_at.isoformat() + "Z" if self.package_updated_at else None,
            "meta": self.meta,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class Domain(Base):
    __tablename__ = "domains"

    id = Column(Integer, primary_key=True, index=True)
    domain = Column(String(255), unique=True, nullable=False, index=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    analysis = Column(JSON, nullable=True)
    analyzed_at = Column(DateTime, nullable=True)
    default_niche = Column(String(255), nullable=True)

    packages = relationship("Package", back_populates="domain_rel", cascade="all, delete-orphan")


class Package(Base):
    __tablename__ = "packages"

    id = Column(Integer, primary_key=True, index=True)
    domain_id = Column(Integer, ForeignKey("domains.id"), nullable=False)
    domain_name = Column(String(255), nullable=False)
    chosen_niche = Column(String(255), nullable=False)
    brand = Column(JSON, nullable=True)
    site_copy = Column(JSON, nullable=True)
    sales_letter = Column(Text, nullable=True)
    hero_image_url = Column(Text, nullable=True)
    feature_images = Column(JSON, nullable=True)
    template_type = Column(String(50), nullable=True, default="hero")
    layout_style = Column(String(50), nullable=True, default="single-scroll")
    density = Column(String(50), nullable=True, default="balanced")
    discovery_answers = Column(JSON, nullable=True)
    atmosphere = Column(JSON, nullable=True)
    graphics_pack = Column(JSON, nullable=True)
    business_box = Column(JSON, nullable=True)
    site_designations = Column(JSON, nullable=True)
    calculators = Column(JSON, nullable=True)
    reference_library = Column(JSON, nullable=True)
    asset_manifest = Column(JSON, nullable=True)
    quality_score = Column(Float, nullable=True)
    luxury_tier = Column(String(20), nullable=True, default="standard")
    style_tier = Column(String(20), nullable=True, default="premium")
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    domain_rel = relationship("Domain", back_populates="packages")
    revisions = relationship("PackageRevision", back_populates="package_rel", cascade="all, delete-orphan", order_by="PackageRevision.created_at.desc()")
    augments = relationship("Augment", back_populates="package_rel", cascade="all, delete-orphan", order_by="Augment.created_at.desc()")


class PackageRevision(Base):
    __tablename__ = "package_revisions"

    id = Column(Integer, primary_key=True, index=True)
    package_id = Column(Integer, ForeignKey("packages.id"), nullable=False)
    revision_type = Column(String(50), nullable=False)
    section_key = Column(String(100), nullable=True)
    action = Column(String(50), nullable=False)
    description = Column(Text, nullable=True)
    before_data = Column(JSON, nullable=True)
    after_data = Column(JSON, nullable=True)
    ai_prompt = Column(Text, nullable=True)
    ai_response = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    package_rel = relationship("Package", back_populates="revisions")


class Augment(Base):
    __tablename__ = "augments"

    id = Column(Integer, primary_key=True, index=True)
    package_id = Column(Integer, ForeignKey("packages.id"), nullable=False)
    domain_name = Column(String(255), nullable=False)
    augment_type = Column(String(50), nullable=False)
    title = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)
    config = Column(JSON, nullable=True)
    html_content = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    package_rel = relationship("Package", back_populates="augments")


class BrandKit(Base):
    __tablename__ = "brand_kits"

    id = Column(Integer, primary_key=True, index=True)
    domain = Column(String(255), nullable=False, index=True, unique=True)
    status = Column(String(20), nullable=False, default="pending")
    raw_text = Column(Text, nullable=True)
    extracted = Column(JSON, nullable=True)
    image_classifications = Column(JSON, nullable=True)
    processing_error = Column(Text, nullable=True)
    summary = Column(JSON, nullable=True)
    gap_analysis = Column(JSON, nullable=True)
    image_suggestions = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    assets = relationship("BrandKitAsset", back_populates="brand_kit", cascade="all, delete-orphan")


class BrandKitAsset(Base):
    __tablename__ = "brand_kit_assets"

    id = Column(Integer, primary_key=True, index=True)
    brand_kit_id = Column(Integer, ForeignKey("brand_kits.id"), nullable=False)
    asset_type = Column(String(20), nullable=False)
    filename = Column(String(255), nullable=False)
    file_path = Column(Text, nullable=False)
    file_size = Column(Integer, nullable=True)
    file_hash = Column(String(64), nullable=True, index=True)
    classification = Column(String(50), nullable=True)
    tags = Column(JSON, nullable=True)
    ai_description = Column(Text, nullable=True)
    sort_order = Column(Integer, default=0)
    is_default_logo = Column(Boolean, default=False)
    suggested_sections = Column(JSON, nullable=True)
    used_in_sections = Column(JSON, nullable=True)
    classified_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    brand_kit = relationship("BrandKit", back_populates="assets")


class RoadmapItem(Base):
    __tablename__ = "roadmap_items"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(255), nullable=False)
    description = Column(Text, nullable=True)
    category = Column(String(50), nullable=True)
    status = Column(String(20), nullable=False, default="planned")
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    completed_at = Column(DateTime, nullable=True)


class BuildTask(Base):
    __tablename__ = "build_tasks"

    id = Column(Integer, primary_key=True, index=True)
    list_name = Column(String(255), nullable=False, index=True)
    title = Column(String(500), nullable=False)
    description = Column(Text, nullable=True)
    status = Column(String(20), nullable=False, default="pending")
    sort_order = Column(Integer, default=0)
    category = Column(String(50), nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)
    completed_at = Column(DateTime, nullable=True)


class SiteProfile(Base):
    __tablename__ = "site_profiles"

    id = Column(Integer, primary_key=True, index=True)
    name = Column(String(255), nullable=False, unique=True)
    slug = Column(String(100), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    is_default = Column(Integer, default=0)
    config = Column(JSON, nullable=False)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class ProjectContext(Base):
    __tablename__ = "project_contexts"

    id = Column(Integer, primary_key=True, index=True)
    domain = Column(String(255), nullable=False, unique=True, index=True)
    context_state = Column(JSON, nullable=True)
    event_log = Column(JSON, nullable=True)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class GlobalContext(Base):
    __tablename__ = "global_context"

    id = Column(Integer, primary_key=True, index=True)
    master_rules = Column(Text, nullable=True)
    style_prefs = Column(JSON, nullable=True)
    guardrails = Column(JSON, nullable=True)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)


class Job(Base):
    __tablename__ = "jobs"

    id = Column(Integer, primary_key=True, index=True)
    job_id = Column(String(64), unique=True, nullable=False, index=True)
    job_type = Column(String(50), nullable=False)
    domain = Column(String(255), nullable=False)
    status = Column(String(20), nullable=False)
    current_step = Column(Text, nullable=True)
    current_step_key = Column(String(50), nullable=True)
    steps_completed = Column(Integer, default=0)
    total_steps = Column(Integer, default=1)
    progress_pct = Column(Integer, default=0)
    steps_detail = Column(JSON, nullable=True)
    step_started_at = Column(DateTime, nullable=True)
    result = Column(JSON, nullable=True)
    error = Column(Text, nullable=True)
    retry_params = Column(JSON, nullable=True)
    retry_of = Column(String(64), nullable=True)
    started_at = Column(DateTime, nullable=True)
    completed_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    def to_dict(self):
        return {
            "job_id": self.job_id,
            "job_type": self.job_type,
            "domain": self.domain,
            "status": self.status,
            "current_step": self.current_step,
            "current_step_key": self.current_step_key,
            "steps_completed": self.steps_completed,
            "total_steps": self.total_steps or 1,
            "progress_pct": self.progress_pct or 0,
            "steps_detail": self.steps_detail,
            "step_started_at": self.step_started_at.isoformat() + "Z" if self.step_started_at else None,
            "result": self.result,
            "error": self.error,
            "retry_params": self.retry_params,
            "retry_of": self.retry_of,
            "started_at": self.started_at.isoformat() + "Z" if self.started_at else None,
            "completed_at": self.completed_at.isoformat() + "Z" if self.completed_at else None,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }


class ChatConversation(Base):
    __tablename__ = "chat_conversations"

    id = Column(Integer, primary_key=True, index=True)
    title = Column(String(500), nullable=False, default="New Chat")
    domain = Column(String(255), nullable=True)
    source_page = Column(String(100), nullable=True)
    metadata_json = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    messages = relationship("ChatMessage", back_populates="conversation", cascade="all, delete-orphan", order_by="ChatMessage.created_at")

    def to_dict(self, include_messages=False):
        d = {
            "id": self.id,
            "title": self.title,
            "domain": self.domain,
            "source_page": self.source_page,
            "message_count": len(self.messages) if self.messages else 0,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }
        if include_messages:
            d["messages"] = [m.to_dict() for m in (self.messages or [])]
        return d


class ChatMessage(Base):
    __tablename__ = "chat_messages"

    id = Column(Integer, primary_key=True, index=True)
    conversation_id = Column(Integer, ForeignKey("chat_conversations.id", ondelete="CASCADE"), nullable=False, index=True)
    role = Column(String(20), nullable=False)
    content = Column(Text, nullable=False)
    context_snapshot = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    conversation = relationship("ChatConversation", back_populates="messages")

    def to_dict(self):
        return {
            "id": self.id,
            "conversation_id": self.conversation_id,
            "role": self.role,
            "content": self.content,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class GenerationRun(Base):
    __tablename__ = "generation_runs"

    id = Column(Integer, primary_key=True, index=True)
    domain_name = Column(String(255), nullable=True, index=True)
    package_id = Column(Integer, ForeignKey("packages.id", ondelete="SET NULL"), nullable=True, index=True)
    operation = Column(String(100), nullable=False)
    model_used = Column(String(100), nullable=False)
    provider = Column(String(50), nullable=False)
    input_tokens = Column(Integer, nullable=True)
    output_tokens = Column(Integer, nullable=True)
    total_tokens = Column(Integer, nullable=True)
    cost_estimate = Column(Float, nullable=True)
    response_time_ms = Column(Integer, nullable=True)
    status = Column(String(20), nullable=False, default="success")
    error_message = Column(Text, nullable=True)
    extra_metadata = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)


class DomainEvent(Base):
    __tablename__ = "domain_events"

    id = Column(Integer, primary_key=True, index=True)
    domain_name = Column(String(255), nullable=False, index=True)
    package_id = Column(Integer, ForeignKey("packages.id", ondelete="SET NULL"), nullable=True)
    event_type = Column(String(100), nullable=False)
    description = Column(Text, nullable=True)
    before_snapshot = Column(JSON, nullable=True)
    after_snapshot = Column(JSON, nullable=True)
    triggered_by = Column(String(50), nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)


class ModelShootoutResult(Base):
    __tablename__ = "model_shootout_results"

    id = Column(Integer, primary_key=True, index=True)
    shootout_id = Column(String(36), nullable=False, index=True)
    domain_name = Column(String(255), nullable=False)
    model_name = Column(String(100), nullable=False)
    provider = Column(String(50), nullable=False)
    raw_response = Column(Text, nullable=True)
    parsed_result = Column(JSON, nullable=True)
    niche_count = Column(Integer, nullable=True)
    avg_niche_score = Column(Float, nullable=True)
    response_time_ms = Column(Integer, nullable=True)
    input_tokens = Column(Integer, nullable=True)
    output_tokens = Column(Integer, nullable=True)
    total_tokens = Column(Integer, nullable=True)
    cost_estimate = Column(Float, nullable=True)
    citations = Column(JSON, nullable=True)
    quality_scores = Column(JSON, nullable=True)
    error_message = Column(Text, nullable=True)
    status = Column(String(20), nullable=False, default="success")
    created_at = Column(DateTime, default=datetime.datetime.utcnow)


class LlmProvider(Base):
    __tablename__ = "llm_providers"

    id = Column(Integer, primary_key=True, index=True)
    slug = Column(String(50), unique=True, nullable=False, index=True)
    name = Column(String(100), nullable=False)
    provider_type = Column(String(30), nullable=False, default="cloud")
    base_url = Column(Text, nullable=True)
    sdk_type = Column(String(30), nullable=False, default="openai")
    supports_json = Column(Boolean, default=True)
    supports_vision = Column(Boolean, default=False)
    supports_image_gen = Column(Boolean, default=False)
    supports_streaming = Column(Boolean, default=True)
    is_active = Column(Boolean, default=True)
    color = Column(String(20), nullable=True, default="#6366F1")
    icon = Column(String(10), nullable=True)
    display_order = Column(Integer, default=0)
    meta_json = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    credentials = relationship("LlmCredential", back_populates="provider", cascade="all, delete-orphan")
    models = relationship("LlmModel", back_populates="provider", cascade="all, delete-orphan")

    def to_dict(self, include_creds=False):
        d = {
            "id": self.id,
            "slug": self.slug,
            "name": self.name,
            "provider_type": self.provider_type,
            "base_url": self.base_url,
            "sdk_type": self.sdk_type,
            "supports_json": self.supports_json,
            "supports_vision": self.supports_vision,
            "supports_image_gen": self.supports_image_gen,
            "supports_streaming": self.supports_streaming,
            "is_active": self.is_active,
            "color": self.color,
            "icon": self.icon,
            "display_order": self.display_order,
            "meta_json": self.meta_json,
            "model_count": len(self.models) if self.models else 0,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }
        if include_creds and self.credentials:
            d["credentials"] = [c.to_dict() for c in self.credentials]
        if self.models:
            d["models"] = [m.to_dict() for m in self.models]
        return d


class LlmCredential(Base):
    __tablename__ = "llm_credentials"

    id = Column(Integer, primary_key=True, index=True)
    provider_id = Column(Integer, ForeignKey("llm_providers.id", ondelete="CASCADE"), nullable=False)
    label = Column(String(100), nullable=False, default="Default")
    encrypted_api_key = Column(Text, nullable=False)
    is_default = Column(Boolean, default=True)
    is_active = Column(Boolean, default=True)
    last_tested_at = Column(DateTime, nullable=True)
    last_test_status = Column(String(20), nullable=True)
    last_test_latency_ms = Column(Integer, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    provider = relationship("LlmProvider", back_populates="credentials")

    def to_dict(self):
        return {
            "id": self.id,
            "provider_id": self.provider_id,
            "label": self.label,
            "is_default": self.is_default,
            "is_active": self.is_active,
            "key_preview": "****" + self.encrypted_api_key[-8:] if self.encrypted_api_key else None,
            "last_tested_at": self.last_tested_at.isoformat() + "Z" if self.last_tested_at else None,
            "last_test_status": self.last_test_status,
            "last_test_latency_ms": self.last_test_latency_ms,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class LlmModel(Base):
    __tablename__ = "llm_models"

    id = Column(Integer, primary_key=True, index=True)
    provider_id = Column(Integer, ForeignKey("llm_providers.id", ondelete="CASCADE"), nullable=False)
    model_id = Column(String(200), nullable=False)
    display_name = Column(String(200), nullable=False)
    mode = Column(String(100), nullable=False, default="text")
    quality_tier = Column(String(20), nullable=False, default="premium")
    context_window = Column(Integer, nullable=True)
    input_cost_per_mtok = Column(Float, nullable=True)
    output_cost_per_mtok = Column(Float, nullable=True)
    is_active = Column(Boolean, default=True)
    meta_json = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)

    provider = relationship("LlmProvider", back_populates="models")

    def to_dict(self):
        return {
            "id": self.id,
            "provider_id": self.provider_id,
            "model_id": self.model_id,
            "display_name": self.display_name,
            "mode": self.mode,
            "quality_tier": self.quality_tier,
            "context_window": self.context_window,
            "input_cost_per_mtok": self.input_cost_per_mtok,
            "output_cost_per_mtok": self.output_cost_per_mtok,
            "is_active": self.is_active,
            "meta_json": self.meta_json,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
        }


class LlmRoute(Base):
    __tablename__ = "llm_routes"

    id = Column(Integer, primary_key=True, index=True)
    stage_key = Column(String(100), nullable=False, index=True)
    provider_id = Column(Integer, ForeignKey("llm_providers.id", ondelete="CASCADE"), nullable=False)
    model_id = Column(String(200), nullable=False)
    priority = Column(Integer, default=1)
    is_active = Column(Boolean, default=True)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    provider = relationship("LlmProvider")

    def to_dict(self):
        return {
            "id": self.id,
            "stage_key": self.stage_key,
            "provider_id": self.provider_id,
            "provider_slug": self.provider.slug if self.provider else None,
            "provider_name": self.provider.name if self.provider else None,
            "model_id": self.model_id,
            "priority": self.priority,
            "is_active": self.is_active,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }


class MarketplaceListing(Base):
    __tablename__ = "marketplace_listings"

    id = Column(Integer, primary_key=True, index=True)
    domain = Column(String(255), nullable=False, index=True)
    platform = Column(String(50), nullable=False)
    listing_url = Column(Text, nullable=True)
    listing_status = Column(String(30), nullable=False, default="active")
    asking_price = Column(Float, nullable=True)
    current_bid = Column(Float, nullable=True)
    bid_count = Column(Integer, default=0)
    views = Column(Integer, default=0)
    watchers = Column(Integer, default=0)
    listed_at = Column(DateTime, nullable=True)
    expires_at = Column(DateTime, nullable=True)
    source = Column(String(20), nullable=False, default="manual")
    notes = Column(Text, nullable=True)
    extra_data = Column(JSON, nullable=True)
    created_at = Column(DateTime, default=datetime.datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.datetime.utcnow, onupdate=datetime.datetime.utcnow)

    def to_dict(self):
        return {
            "id": self.id,
            "domain": self.domain,
            "platform": self.platform,
            "listing_url": self.listing_url,
            "listing_status": self.listing_status,
            "asking_price": self.asking_price,
            "current_bid": self.current_bid,
            "bid_count": self.bid_count,
            "views": self.views,
            "watchers": self.watchers,
            "listed_at": self.listed_at.isoformat() + "Z" if self.listed_at else None,
            "expires_at": self.expires_at.isoformat() + "Z" if self.expires_at else None,
            "source": self.source,
            "notes": self.notes,
            "extra_data": self.extra_data,
            "created_at": self.created_at.isoformat() + "Z" if self.created_at else None,
            "updated_at": self.updated_at.isoformat() + "Z" if self.updated_at else None,
        }