externals/databases/pg_crud.py

from typing import Optional, List
from uuid import UUID

from sqlalchemy import select, and_, update, delete
from sqlalchemy.ext.asyncio import AsyncSession
from externals.databases.pg_models import (
    CVUser,
    CVTenant,
    CVFile,
    CVProfile,
    CVFilter,
    CVWeight,
    CVMatching,
    CVScore,
)

# =========================
# USER
# =========================
from externals.databases.schemas.user import UserCreate, UserResponse
from utils.security import hash_password        

async def get_user_by_username(
    db: AsyncSession,
    username: str,
) -> CVUser | None:
    result = await db.execute(
        select(CVUser).where(CVUser.username == username)
    )
    return result.scalar_one_or_none()


async def create_user(
    db: AsyncSession,
    user_in: UserCreate,
) -> CVUser:
    # ✅ CONVERT Pydantic → ORM
    user = CVUser(
        username=user_in.username,
        hashed_password=hash_password(user_in.password),
        email=user_in.email,
        full_name=user_in.full_name,
        role=user_in.role,
        is_active=True,
        tenant_id=user_in.tenant_id,
        notes=user_in.notes,
    )

    db.add(user)           # ✅ ORM
    await db.commit()
    await db.refresh(user)

    return user

async def deactivate_user(
    db: AsyncSession,
    username: str,
) -> CVUser | None:
    result = await db.execute(
        select(CVUser).where(CVUser.username == username)
    )
    user = result.scalar_one_or_none()

    if not user:
        return None

    user.is_active = False
    await db.commit()
    await db.refresh(user)

    return user


async def get_user_by_email(db, email: str):
    result = await db.execute(
        select(CVUser).where(CVUser.email == email)
    )
    return result.scalar_one_or_none()


async def get_user_by_id(db, user_id: str):
    result = await db.execute(
        select(CVUser).where(CVUser.user_id == user_id)
    )
    return result.scalar_one_or_none()


# =========================
# TENANT
# =========================
from externals.databases.schemas.tenant import TenantCreate

async def get_tenant_by_name(
    db: AsyncSession,
    tenant_name: str,
) -> CVTenant | None:
    result = await db.execute(
        select(CVTenant).where(CVTenant.tenant_name == tenant_name)
    )
    return result.scalar_one_or_none()


async def create_tenant(
    db: AsyncSession,
    tenant_in: TenantCreate,
) -> CVTenant:
    # ✅ CONVERT Pydantic → ORM
    tenant = CVTenant(
        tenant_name=tenant_in.tenant_name,
        notes=tenant_in.notes,
    )

    db.add(tenant)          # ✅ ORM
    await db.commit()
    await db.refresh(tenant)

    return tenant


# =========================
# FILE
# =========================

# async def get_file_by_filename(
#     db: AsyncSession,
#     filename: str,
# ) -> Optional[CVFile]:
#     stmt = select(CVFile).where(CVFile.filename == filename)
#     result = await db.execute(stmt)
#     return result.scalar_one_or_none()


async def mark_file_extracted(
    db: AsyncSession,
    file_id: UUID,
) -> None:
    stmt = (
        update(CVFile)
        .where(CVFile.file_id == file_id)
        .values(is_extracted=True)
    )
    await db.execute(stmt)
    await db.commit()


async def create_cv_file(
    db: AsyncSession,
    *,
    user_id: str,
    filename: str,
    file_type: str,
    url: str,
):
    cv_file = CVFile(
        filename=filename,
        file_type=file_type,
        url=url,
        is_extracted=False,
        user_id=user_id,
    )

    db.add(cv_file)
    await db.commit()
    await db.refresh(cv_file)

    return cv_file


async def delete_file_by_filename(
    db: AsyncSession,
    filename: str,
) -> bool:
    stmt = delete(CVFile).where(CVFile.filename == filename)
    result = await db.execute(stmt)
    await db.commit()
    return result.rowcount > 0

async def get_file_by_user_id(
    db: AsyncSession,
    user_id: str,
) -> CVFile | None:
    result = await db.execute(
        select(CVFile).where(CVFile.user_id == user_id)
    )
    return result.scalars().all()

async def get_file_by_filename(
    db: AsyncSession,
    filename: str,
    user_id: UUID,
) -> CVFile | None:

    result = await db.execute(
        select(CVFile)
        .where(
            CVFile.filename == filename,
            CVFile.user_id == user_id,
        )
    )
    return result.scalar_one_or_none()


async def mark_file_extracted(db: AsyncSession, file_id: UUID):
    await db.execute(
        update(CVFile)
        .where(CVFile.file_id == file_id)
        .values(is_extracted=True)
    )

# =========================
# PROFILE
# =========================

# async def create_profile(
#     db: AsyncSession,
#     profile: CVProfile,
# ) -> CVProfile:
#     db.add(profile)
#     await db.commit()
#     await db.refresh(profile)
#     return profile

from services.models.data_model import AIProfile

async def create_profile(
    db: AsyncSession,
    filename: str,
    file_id: str,
    profile: AIProfile,
) -> CVProfile:

    profile = CVProfile(
        fullname=profile.get("fullname"),
        gpa_edu_1=profile.get("gpa_edu_1", None),
        univ_edu_1=profile.get("univ_edu_1", None),
        major_edu_1=profile.get("major_edu_1", None),

        gpa_edu_2=profile.get("gpa_edu_2", None),
        univ_edu_2=profile.get("univ_edu_2", None),
        major_edu_2=profile.get("major_edu_2", None),
        gpa_edu_3=profile.get("gpa_edu_3", None),
        univ_edu_3=profile.get("univ_edu_3", None),
        major_edu_3=profile.get("major_edu_3", None),

        domicile=profile.get("domicile", None),
        yoe=profile.get("yoe", None),

        hardskills=profile.get("hardskills", []),
        softskills=profile.get("softskills", []),
        certifications=profile.get("certifications", []),
        business_domain=profile.get("business_domain", []),

        filename=filename,
        file_id=file_id,
    )

    db.add(profile)
    await db.flush()
    return profile


async def get_profile_by_filename(
    db: AsyncSession,
    filename: str,
) -> Optional[CVProfile]:
    stmt = select(CVProfile).where(CVProfile.filename == filename)
    result = await db.execute(stmt)
    return result.scalar_one_or_none()


async def get_profile_by_id(
    db: AsyncSession,
    profile_id: str,
) -> Optional[CVProfile]:
    stmt = select(CVProfile).where(CVProfile.profile_id == profile_id)
    result = await db.execute(stmt)
    return result.scalar_one_or_none()


async def list_profiles(
    db: AsyncSession,
    limit: int = 50,
    offset: int = 0,
) -> List[CVProfile]:
    stmt = select(CVProfile).limit(limit).offset(offset)
    result = await db.execute(stmt)
    return result.scalars().all()


# =========================
# FILTER & WEIGHT
# =========================
from sqlalchemy import select, and_
from sqlalchemy.ext.asyncio import AsyncSession

async def get_filter(
    db: AsyncSession,
    filter: CVFilter,
):
    conditions = []

    # --- GPA ---
    if filter.gpa_edu_1 is not None:
        conditions.append(CVFilter.gpa_edu_1 >= filter.gpa_edu_1)
    if filter.gpa_edu_2 is not None:
        conditions.append(CVFilter.gpa_edu_2 >= filter.gpa_edu_2)
    if filter.gpa_edu_3 is not None:
        conditions.append(CVFilter.gpa_edu_3 >= filter.gpa_edu_3)

    # --- University ---
    if filter.univ_edu_1:
        conditions.append(CVFilter.univ_edu_1 == filter.univ_edu_1)
    if filter.univ_edu_2:
        conditions.append(CVFilter.univ_edu_2 == filter.univ_edu_2)
    if filter.univ_edu_3:
        conditions.append(CVFilter.univ_edu_3 == filter.univ_edu_3)

    # --- Major ---
    if filter.major_edu_1:
        conditions.append(CVFilter.major_edu_1 == filter.major_edu_1)
    if filter.major_edu_2:
        conditions.append(CVFilter.major_edu_2 == filter.major_edu_2)
    if filter.major_edu_3:
        conditions.append(CVFilter.major_edu_3 == filter.major_edu_3)

    # --- Others ---
    if filter.domicile:
        conditions.append(CVFilter.domicile == filter.domicile)

    if filter.yoe is not None:
        conditions.append(CVFilter.yoe >= filter.yoe)

    # --- ARRAY fields (exact match) ---
    if filter.hardskills:
        conditions.append(CVFilter.hardskills == filter.hardskills)

    if filter.softskills:
        conditions.append(CVFilter.softskills == filter.softskills)

    if filter.certifications:
        conditions.append(CVFilter.certifications == filter.certifications)

    if filter.business_domain:
        conditions.append(CVFilter.business_domain == filter.business_domain)

    # ⛔ Prevent full table scan
    if not conditions:
        return None

    stmt = select(CVFilter).where(and_(*conditions))
    result = await db.execute(stmt)

    return result.scalar_one_or_none()


async def get_filter_by_id(
    db: AsyncSession,
    criteria_id: str,
) -> Optional[CVFilter]:
    stmt = select(CVFilter).where(CVFilter.criteria_id == criteria_id)
    result = await db.execute(stmt)
    return result.scalar_one_or_none()



async def create_filter(
    db: AsyncSession,
    filter: CVFilter,
):
    db.add(filter)
    await db.commit()
    return filter
    

async def get_filter_and_weight(
    db: AsyncSession,
    criteria_id: str,
):
    stmt = (
        select(CVFilter, CVWeight)
        .join(CVWeight, CVFilter.criteria_id == CVWeight.criteria_id)
        .where(CVFilter.criteria_id == criteria_id)
    )
    result = await db.execute(stmt)
    return result.first()


async def get_weight_by_id(
    db: AsyncSession,
    weight_id: str,
):
    stmt = (
        select(CVWeight)
        .where(CVFilter.weight_id == weight_id)
    )
    result = await db.execute(stmt)
    return result.first()


async def create_filter_and_weight(
    db: AsyncSession,
    cv_filter: CVFilter,
    cv_weight: CVWeight,
):
    db.add_all([cv_filter, cv_weight])
    await db.commit()

async def create_weight(
    db: AsyncSession,
    cv_weight: CVWeight,
):
    db.add(cv_weight)
    await db.commit()


# =========================
# MATCHING
# =========================

async def create_matching(
    db: AsyncSession,
    matching: CVMatching,
) -> CVMatching:
    db.add(matching)
    await db.commit()
    await db.refresh(matching)
    return matching


async def get_matching_by_profile_and_criteria(
    db: AsyncSession,
    profile_id: str,
    criteria_id: str,
) -> Optional[CVMatching]:
    stmt = (
        select(CVMatching)
        .where(
            and_(
                CVMatching.profile_id == profile_id,
                CVMatching.criteria_id == criteria_id,
            )
        )
    )
    result = await db.execute(stmt)
    return result.scalar_one_or_none()


# =========================
# SCORE
# =========================

async def create_score(
    db: AsyncSession,
    score: CVScore,
) -> CVScore:
    db.add(score)
    await db.commit()
    await db.refresh(score)
    return score


async def get_scores_by_criteria(
    db: AsyncSession,
    criteria_id: str,
) -> List[CVScore]:
    stmt = (
        select(CVScore)
        .join(CVMatching, CVScore.matching_id == CVMatching.matching_id)
        .where(CVMatching.criteria_id == criteria_id)
        .order_by(CVScore.score.desc())
    )
    result = await db.execute(stmt)
    return result.scalars().all()