app/models.py

import uuid
from datetime import datetime, timezone
from sqlalchemy import Column, String, Integer, Boolean, DateTime, ForeignKey, Text, Float, JSON
from sqlalchemy.dialects.postgresql import JSONB, UUID
from sqlalchemy.orm import relationship
from app.database import Base

# Timezone-aware UTC timestamp factory — avoids naive/aware comparison TypeError
def utcnow():
    return datetime.now(timezone.utc)

class User(Base):
    __tablename__ = "users"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    firebase_uid = Column(String, unique=True, index=True, nullable=False)
    email = Column(String, unique=True, index=True, nullable=False)
    name = Column(String, nullable=True)
    avatar_url = Column(String, nullable=True)
    plan = Column(String, default="free")  # 'free', 'starter', 'pro'
    display_mode = Column(String, default="founder")  # 'founder', 'engineer'
    credits_remaining = Column(Integer, default=2)
    credits_reset_at = Column(DateTime(timezone=True), default=utcnow)
    stripe_customer_id = Column(String, nullable=True)
    github_connected = Column(Boolean, default=False)
    total_audits = Column(Integer, default=0)
    total_designs = Column(Integer, default=0)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), default=utcnow)
    updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)

    projects = relationship("Project", back_populates="user", cascade="all, delete-orphan")
    github_connection = relationship("GitHubConnection", back_populates="user", uselist=False, cascade="all, delete-orphan")

class Project(Base):
    __tablename__ = "projects"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), index=True, nullable=False)
    name = Column(String(100), nullable=False)
    type = Column(String, nullable=False)  # 'audit', 'system_design'
    source = Column(String, nullable=False)  # 'upload', 'github'
    status = Column(String, index=True, default="queued")  # 'queued', 'processing', 'complete', 'failed'
    job_id = Column(String, index=True, nullable=True)
    error_msg = Column(Text, nullable=True)
    github_repo = Column(String, nullable=True)
    github_branch = Column(String, nullable=True)
    created_at = Column(DateTime(timezone=True), default=utcnow)
    updated_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)

    user = relationship("User", back_populates="projects")
    audit_report = relationship("AuditReport", back_populates="project", uselist=False, cascade="all, delete-orphan")
    system_design = relationship("SystemDesign", back_populates="project", uselist=False, cascade="all, delete-orphan")

class AuditReport(Base):
    __tablename__ = "audit_reports"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), index=True, unique=True, nullable=False)
    overall_score = Column(Integer, nullable=False)
    confidence = Column(JSONB, nullable=False)  # JSON: level, score, label, based_on, limitations, to_increase_confidence
    capacity_estimate = Column(JSONB, nullable=False)  # JSON: safe_range, peak_range, description, reasoning, confidence
    agents = Column(JSONB, nullable=False)  # JSON: SRE, backend, infrastructure, cloud_architect reports
    top_critical_issues = Column(JSONB, nullable=False)  # Array of issues
    quick_wins = Column(JSONB, nullable=False)  # Array of quick wins
    benchmark_percentile = Column(Float, nullable=True)
    score_disclaimer = Column(Text, nullable=False)
    files_analyzed = Column(Integer, default=0)
    files_skipped = Column(Integer, default=0)
    was_truncated = Column(Boolean, default=False)
    created_at = Column(DateTime(timezone=True), default=utcnow)

    project = relationship("Project", back_populates="audit_report")

class SystemDesign(Base):
    __tablename__ = "system_designs"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    project_id = Column(UUID(as_uuid=True), ForeignKey("projects.id", ondelete="CASCADE"), index=True, unique=True, nullable=False)
    idea_prompt = Column(Text, nullable=False)
    title = Column(String, nullable=False)
    founder_summary = Column(Text, nullable=False)
    engineer_summary = Column(Text, nullable=False)
    architecture_type = Column(String, nullable=False)  # 'Monolith', 'Microservices', 'Hybrid'
    reasoning = Column(Text, nullable=False)
    stack = Column(JSONB, nullable=False)  # JSON: Frontend, Backend, Database, Infrastructure chips with reasons
    database_design = Column(JSONB, nullable=False)  # JSON: primary_db, cache, key_tables
    api_design = Column(JSONB, nullable=False)  # JSON: style, auth_strategy, core_endpoints
    infrastructure = Column(JSONB, nullable=False)  # JSON: cloud_provider, components, scaling_strategy
    reliability = Column(JSONB, nullable=False)  # JSON: uptime_target, strategies, backup_dr
    cost_estimates = Column(JSONB, nullable=False)  # JSON: 1k, 100k, 1m tier costs and drivers
    diagram = Column(JSONB, nullable=False)  # JSON: nodes, edges
    created_at = Column(DateTime(timezone=True), default=utcnow)

    project = relationship("Project", back_populates="system_design")
    shared_link = relationship("SharedLink", back_populates="design", uselist=False, cascade="all, delete-orphan")

class SharedLink(Base):
    __tablename__ = "shared_links"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    design_id = Column(UUID(as_uuid=True), ForeignKey("system_designs.id", ondelete="CASCADE"), unique=True, nullable=False)
    slug = Column(String, unique=True, index=True, nullable=False)
    view_count = Column(Integer, default=0)
    created_at = Column(DateTime(timezone=True), default=utcnow)

    design = relationship("SystemDesign", back_populates="shared_link")

class GitHubConnection(Base):
    __tablename__ = "github_connections"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id", ondelete="CASCADE"), unique=True, index=True, nullable=False)
    github_username = Column(String, nullable=False)
    access_token = Column(String, nullable=False)  # Encrypted text
    token_expires_at = Column(DateTime(timezone=True), nullable=True)
    connected_at = Column(DateTime(timezone=True), default=utcnow)
    last_used_at = Column(DateTime(timezone=True), default=utcnow, onupdate=utcnow)

    user = relationship("User", back_populates="github_connection")

class BenchmarkData(Base):
    __tablename__ = "benchmark_data"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    score_range = Column(String, nullable=False)  # e.g., '80-89'
    industry_tag = Column(String, nullable=False)  # e.g., 'E-Commerce'
    arch_type = Column(String, nullable=False)  # e.g., 'Serverless'
    created_at = Column(DateTime(timezone=True), default=utcnow)

class WebhookEvent(Base):
    __tablename__ = "webhook_events"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    event_id = Column(String, unique=True, index=True, nullable=False)
    event_type = Column(String, nullable=False)
    processed_at = Column(DateTime(timezone=True), default=utcnow)