"""
Generic Notes Services - For handling notes across different entities
"""

from typing import List, Optional
from datetime import datetime
import logging
from sqlalchemy import select, update
from sqlalchemy.orm import Session

from src.apps.notes.models.note import Note
from src.apps.notes.schemas.common import NoteSchema, NoteCreateRequestSchema, NoteUpdateRequestSchema
from src.core.exceptions import APIException
from fastapi import status

logger = logging.getLogger(__name__)


######### GENERIC NOTE SERVICES #########
async def create_note(
    db: Session,
    payload: NoteCreateRequestSchema,
    created_by_id: int,
) -> NoteSchema:
    """Create a new note."""
    try:
        note = Note(description=payload.description, created_by_id=created_by_id)
        db.add(note)
        db.commit()
        db.refresh(note)
        return NoteSchema.model_validate(note)
    except Exception as e:
        db.rollback()
        logger.error(f"Error creating note: {e}")
        raise APIException(
            module=__name__,
            error={},
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            message="Could not create note",
        )


async def get_note_by_id(db: Session, note_id: int) -> Optional[NoteSchema]:
    """Retrieve a note by ID."""
    stmt = select(Note).where(Note.id == note_id, Note.deleted_at.is_(None))
    note = db.execute(stmt).scalar_one_or_none()
    return NoteSchema.model_validate(note) if note else None


async def list_notes(db: Session, skip: int = 0, limit: int = 20) -> List[NoteSchema]:
    """List notes with pagination."""
    stmt = (
        select(Note)
        .where(Note.deleted_at.is_(None))
        .order_by(Note.created_at.desc())
        .offset(skip)
        .limit(limit)
    )
    notes = db.execute(stmt).scalars().all()
    return [NoteSchema.model_validate(n) for n in notes]


async def update_note_by_id(
    db: Session,
    object_id: int,
    payload: NoteUpdateRequestSchema,
) -> NoteSchema:
    """Update an existing note by ID."""
    # Fetch existing note
    stmt = select(Note).where(Note.id == object_id, Note.deleted_at.is_(None))
    note = db.execute(stmt).scalar_one_or_none()
    if not note:
        raise APIException(
            module=__name__,
            error={},
            status_code=status.HTTP_404_NOT_FOUND,
            message="Note not found",
        )

    update_data = payload.model_dump(exclude_unset=True)
    if update_data:
        update_data["updated_at"] = datetime.utcnow()
        upd = update(Note).where(Note.id == object_id).values(**update_data)
        db.execute(upd)
        db.commit()
        db.refresh(note)

    return NoteSchema.model_validate(note)


async def delete_note_by_id(db: Session, note_id: int) -> bool:
    """Soft delete a note by setting deleted_at."""
    stmt = select(Note).where(Note.id == note_id, Note.deleted_at.is_(None))
    note = db.execute(stmt).scalar_one_or_none()
    if not note:
        raise APIException(
            module=__name__,
            error={},
            status_code=status.HTTP_404_NOT_FOUND,
            message="Note not found",
        )

    upd = update(Note).where(Note.id == note_id).values(deleted_at=datetime.utcnow())
    db.execute(upd)
    db.commit()
    return True