backend/models.py

from datetime import datetime, timezone
import enum
import uuid
from uuid import UUID
from sqlalchemy import (
    Column, String, Boolean, ForeignKey, Float,
    DateTime, Text, Enum as SQLEnum, Integer
)
from sqlalchemy.orm import relationship
from sqlalchemy.dialects.postgresql import JSONB, UUID as PG_UUID
import os
import sys
from pathlib import Path
 
# ensure project root (agent/) is on sys.path so sibling packages like "s3" can be imported
project_root = Path(__file__).resolve().parents[2]  # -> ...\openai_agents\agent
if str(project_root) not in sys.path:
    sys.path.insert(0, str(project_root))
from .database import Base


# ---------- ENUMS ----------

class UserRole(str, enum.Enum):
    superadmin = "superadmin"
    org_admin = "org_admin"
    employee = "employee"
    provider = "provider"
    user = "user"  # Fixed: Was "User" → lowercase "user" for consistency


class DatasetStatus(str, enum.Enum):
    pending = "pending"
    approved = "approved"
    rejected = "rejected"
    published = "published"
    in_review = "in_review"
    deprecated = "deprecated"
    paused = "paused"


class LeadStatus(str, enum.Enum):
    new = "new"
    contacted = "contacted"
    closed = "closed"
    interested = "interested"
    meeting_scheduled = "meeting_scheduled"
    follow_up = "follow_up"
    won = "won"
    lost = "lost"


# ---------- MODELS ----------

class Plan(Base):
    __tablename__ = "plans"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    plan_type = Column(String(100), nullable=False)
    price = Column(Float, nullable=False)
    features = Column(Text)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)
    created_by = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"),
        nullable=True,
        index=True
    )
    organizations = relationship("Organization", back_populates="plan")

    def __repr__(self):
        return f"<Plan(id={self.id}, plan_type={self.plan_type}, price={self.price})>"


class Organization(Base):
    __tablename__ = "organizations"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    name = Column(String(255), nullable=False)
    domain = Column(String(255), nullable=True, index=True)  # Fixed: Removed duplicate definition
    plan_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("plans.id", ondelete="RESTRICT", deferrable=True, initially="DEFERRED"), 
        nullable=False,
        index=True
    )
    location = Column(Text)
    city = Column(String(100))
    state = Column(String(100))
    country = Column(String(100))
    is_active = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), 
                       onupdate=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)
    created_by = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"),
        nullable=True,
        index=True
    )
    admin_name = Column(String(255), nullable=True)
    admin_email = Column(String(255), unique=True, nullable=True, index=True)
    timezone = Column(String(50))
    seats = Column(Integer, default=5)
    storage_quota_gb = Column(Integer, default=100)
    data_residency = Column(String(50), nullable=True)
    status = Column(String(50), default="active", nullable=False)
    contact_number = Column(String(50), nullable=True)
    notes = Column(Text, nullable=True)

    plan = relationship("Plan", back_populates="organizations")
    users = relationship("User", back_populates="organization", foreign_keys="User.organization_id")
    leads = relationship("Lead", back_populates="organization")
    datasets = relationship("Dataset", back_populates="organization")
    # audit_logs = relationship("ActivityLog", back_populates="organization")

    def __repr__(self):
        return f"<Organization(id={self.id}, name={self.name}, domain={self.domain})>"


class ThirdPartyProvider(Base):
    __tablename__ = "third_party_providers"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    provider_name = Column(String(255), nullable=False)
    is_active = Column(Boolean, default=True, nullable=False)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)
    created_by = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"),
        nullable=True,
        index=True
    )
    domain = Column(String(255), nullable=True, index=True)
    full_name = Column(String(255), nullable=True, index=True)
    provider_email = Column(String(255), unique=True, nullable=False, index=True)
    providerphone_number = Column(String(50), nullable=True)
    country = Column(String(100))
    data_residency = Column(String(50), nullable=True)
    storage_quota = Column(Integer, default=100)
    timezone = Column(String(50))
    provider_status = Column(String(50), default="active", nullable=False)
    provider_notes = Column(Text, nullable=True)

    users = relationship("User", back_populates="provider", foreign_keys="User.provider_id")
    datasets = relationship("Dataset", back_populates="provider")
    leads = relationship("Lead", back_populates="provider")

    def __repr__(self):
        return f"<ThirdPartyProvider(id={self.id}, provider_name={self.provider_name})>"


class User(Base):
    __tablename__ = "users"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    fullname = Column(String(255), nullable=False)
    username = Column(String(100), unique=True, nullable=False, index=True)
    password = Column(String(500), nullable=False)  # Increased for bcrypt compatibility
    email = Column(String(255), unique=True, nullable=False, index=True)
    role = Column(SQLEnum(UserRole), nullable=False, index=True)
    is_active = Column(Boolean, default=True, nullable=False, index=True)
    last_login = Column(DateTime(timezone=True))
    organization_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("organizations.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"), 
        nullable=True,
        index=True
    )
    provider_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("third_party_providers.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"), 
        nullable=True,
        index=True
    )
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), 
                       onupdate=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    organization = relationship("Organization", back_populates="users", foreign_keys=[organization_id])
    provider = relationship("ThirdPartyProvider", back_populates="users", foreign_keys=[provider_id])
    conversation_data = relationship("ConversationData", back_populates="user", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<User(id={self.id}, username={self.username}, email={self.email}, role={self.role})>"


class Dataset(Base):
    __tablename__ = "datasets"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    dataset_name = Column(String(255), nullable=False)
    dataset_type = Column(String(100))
    tags = Column(String(500))
    price = Column(Float)
    submitted_time = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    description = Column(Text)
    file_url = Column(Text)
    status = Column(SQLEnum(DatasetStatus), default=DatasetStatus.pending, nullable=False, index=True)
    created_by = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"),
        nullable=True,
        index=True
    )
    organization_id = Column(  # Added: Assumed missing FK based on relationship; adjust if not needed
        PG_UUID(as_uuid=True), 
        ForeignKey("organizations.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"), 
        nullable=True,
        index=True
    )
    provider_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("third_party_providers.id", ondelete="RESTRICT", deferrable=True, initially="DEFERRED"), 
        nullable=False,
        index=True
    )
    reviewed_by = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"), 
        nullable=True,
        index=True
    )
    approved_at = Column(DateTime(timezone=True))
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    organization = relationship("Organization", back_populates="datasets")  # Added: To match new FK
    provider = relationship("ThirdPartyProvider", back_populates="datasets")
    reviewer = relationship("User", foreign_keys=[reviewed_by])
    creator = relationship("User", foreign_keys=[created_by])

    def __repr__(self):
        return f"<Dataset(id={self.id}, dataset_name={self.dataset_name}, status={self.status})>"


class Lead(Base):
    __tablename__ = "leads"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    organization_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("organizations.id", ondelete="CASCADE", deferrable=True, initially="DEFERRED"), 
        nullable=False,
        index=True
    )
    provider_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("third_party_providers.id", ondelete="RESTRICT", deferrable=True, initially="DEFERRED"), 
        nullable=False,
        index=True
    )
    dataset_id = Column(
        PG_UUID(as_uuid=True), 
        ForeignKey("datasets.id", ondelete="RESTRICT", deferrable=True, initially="DEFERRED"), 
        nullable=False,
        index=True
    )
    contact = Column(String(255), nullable=False)
    status = Column(SQLEnum(LeadStatus), default=LeadStatus.new, nullable=False, index=True)
    note = Column(Text)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), 
                       onupdate=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

    organization = relationship("Organization", back_populates="leads")
    provider = relationship("ThirdPartyProvider", back_populates="leads")
    dataset = relationship("Dataset", foreign_keys=[dataset_id])

    def __repr__(self):
        return f"<Lead(id={self.id}, contact={self.contact}, status={self.status})>"


class ConversationData(Base):
    __tablename__ = "conversation_data"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    convo_id = Column(String(255), nullable=False, index=True)
    user_query = Column(JSONB, nullable=False)
    response = Column(JSONB, nullable=False)
    file_metadata = Column(JSONB, nullable=True)
    is_saved = Column(Boolean, default=False, nullable=False)
    user_id = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="CASCADE", deferrable=True, initially="DEFERRED"),
        nullable=False,
        index=True
    )
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), 
                       onupdate=lambda: datetime.now(timezone.utc), nullable=False)
    deleted_at = Column(DateTime(timezone=True), nullable=True)

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

    def __repr__(self):
        return f"<ConversationData(id={self.id}, convo_id={self.convo_id})>"


class UserDatasetsMetadata(Base):
    __tablename__ = "user_datasets_metadata"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(
        PG_UUID(as_uuid=True),
        ForeignKey("users.id", ondelete="CASCADE", deferrable=True, initially="DEFERRED"),
        nullable=False,
        index=True
    )
    user_metadata = Column(JSONB, nullable=False)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=False)
    updated_at = Column(
        DateTime(timezone=True),
        default=lambda: datetime.now(timezone.utc),
        onupdate=lambda: datetime.now(timezone.utc),
        nullable=False
    )
    path = Column(String(512), nullable=True) 
    url = Column(String(1024), nullable=True)
    filename = Column(String(255), nullable=True)
    file_type = Column(String(50), nullable=True)
    file_size_bytes = Column(Integer, nullable=True)
    file_hash = Column(String(1024), nullable=True)
    # Relationship
    user = relationship("User", backref="user_datasets_metadata")

    def __repr__(self):
        return f"<UserDatasetsMetadata(id={self.id}, user_id={self.user_id})>"


class PasswordReset(Base):
    __tablename__ = "password_reset"

    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(PG_UUID(as_uuid=True), ForeignKey("users.id", deferrable=True, initially="DEFERRED"), nullable=True)
    otp = Column(String(6), nullable=True)
    is_verified = Column(Boolean, default=True)
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc))
    expires_at = Column(DateTime(timezone=True), nullable=True)

    user = relationship("User", backref="password_resets")


class GeneratedReport(Base):
    __tablename__ = "generated_reports"
    id = Column(PG_UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    report_name = Column(String(400), nullable=True)
    user_id = Column(PG_UUID(as_uuid=True), ForeignKey("users.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"))
    org_id = Column(PG_UUID(as_uuid=True), ForeignKey("organizations.id", ondelete="SET NULL", deferrable=True, initially="DEFERRED"))
    format = Column(String(20))  # pdf, pptx
    # content_items = Column(JSONB)  # list of query ids / dataset ids
    theme = Column(String(100))
    file_url = Column(Text)
    status = Column(String(50), default="ready")  # generating, ready, failed
    created_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), nullable=True)
    updated_at = Column(DateTime(timezone=True), default=lambda: datetime.now(timezone.utc), 
                       onupdate=lambda: datetime.now(timezone.utc), nullable=True)
    file_path = Column(String(512), nullable=True)
    filename = Column(String(255), nullable=True)
    size_bytes = Column(Integer, nullable=True)

    user = relationship("User", back_populates="generated_reports")  # Added: back_populates for bidirectionality
    organization = relationship("Organization", back_populates="generated_reports")  # Added: back_populates for bidirectionality

# Add to User model (after its definition) for GeneratedReport relationships
# Note: Since User is defined earlier, add these lines after the User class in the file
User.generated_reports = relationship("GeneratedReport", back_populates="user", foreign_keys="GeneratedReport.user_id")

# Add to Organization model (after its definition) for GeneratedReport relationships
Organization.generated_reports = relationship("GeneratedReport", back_populates="organization", foreign_keys="GeneratedReport.org_id")