# crud.py
from sqlalchemy.orm import Session
import os
import sys
from pathlib import Path
from .models import UserDatasetsMetadata, Organization
from .schemas import UserDatasetsMetadataCreate , OrganizationCreate,OrganizationUpdate,OrganizationResponse
 
# 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 .models import User, ConversationData
from .schemas import UserCreate
from uuid import UUID


# -----------------------------
# User CRUD Operations
# -----------------------------

def get_user_by_username(db: Session, username: str):
    """Fetch user by username."""
    return db.query(User).filter(User.username == username).first()


def get_user_by_email(db: Session, email: str):
    """Fetch user by email."""
    return db.query(User).filter(User.email == email).first()


def create_user(db: Session, user: UserCreate, hashed_password: str):
    """Create a new user."""
    db_user = User(
        fullname=user.fullname,
        username=user.username,
        email=user.email,
        password=hashed_password,
        role=user.role,
        is_active=user.is_active if user.is_active is not None else True,
        organization_id=user.organization_id,
        provider_id=user.provider_id,
    )
    db.add(db_user)
    db.commit()
    db.refresh(db_user)
    return db_user


# -----------------------------
# Conversation CRUD Operations
# -----------------------------

def save_conversation(db: Session, convo_id: str, user_query: dict, response: dict, file_metadata: dict, is_saved: bool, user_id: str):
    """Save conversation data."""
    rec = ConversationData(
        convo_id=convo_id, 
        user_query=user_query,
        response=response,
        file_metadata=file_metadata,
        is_saved=is_saved,
        user_id=user_id
    )
    db.add(rec)
    db.commit()
    db.refresh(rec)
    return rec

# -----------------------------
# UserMetadata CRUD Operations
# -----------------------------

def create_user_dataset_metadata(db: Session, metadata_in: UserDatasetsMetadataCreate):
    """Create new record for user dataset metadata."""
    record = UserDatasetsMetadata(
        user_id=metadata_in.user_id,
        user_metadata=metadata_in.user_metadata
    )
    db.add(record)
    db.commit()
    db.refresh(record)
    return record


def get_user_dataset_metadata(db: Session, user_id: str):
    """Fetch all dataset metadata records for a given user."""
    return db.query(UserDatasetsMetadata).filter(UserDatasetsMetadata.user_id == user_id).all()



def create_organization(db: Session, org_data: OrganizationCreate, created_by_user_id: UUID):
    """Create a new organization — only superadmin can create."""
    # Check user role
    user = db.query(User).filter(User.id == created_by_user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")
    if user.role.lower() != "superadmin":
        raise HTTPException(status_code=403, detail="Only superadmin can create an organization")

    org = Organization(
        name=org_data.name,
        domain=org_data.domain,
        plan_id=org_data.plan_id,
        location=org_data.location,
        city=org_data.city,
        state=org_data.state,
        country=org_data.country,
        is_active=org_data.is_active,
        created_by=created_by_user_id,
    )
    db.add(org)
    db.commit()
    db.refresh(org)
    return org


def get_organization_by_id(db: Session, org_id: UUID):
    org = db.query(Organization).filter(Organization.id == org_id).first()
    if not org:
        raise HTTPException(status_code=404, detail="Organization not found")
    return org


def get_all_organizations(db: Session):
    return db.query(Organization).all()


def update_organization(db: Session, org_id: UUID, org_data: OrganizationUpdate):
    org = db.query(Organization).filter(Organization.id == org_id).first()
    if not org:
        raise HTTPException(status_code=404, detail="Organization not found")

    for key, value in org_data.dict(exclude_unset=True).items():
        setattr(org, key, value)

    db.commit()
    db.refresh(org)
    return org


def delete_organization(db: Session, org_id: UUID):
    org = db.query(Organization).filter(Organization.id == org_id).first()
    if not org:
        raise HTTPException(status_code=404, detail="Organization not found")

    db.delete(org)
    db.commit()
    return {"message": "Organization deleted successfully"}