"""
Notes services for business logic operations.
"""
from typing import List, Optional
from sqlalchemy.orm import Session
from sqlalchemy import select, update
from datetime import datetime
import logging

from src.core.exceptions import APIException
from fastapi import status

from src.apps.notes.models.note import Note
from src.apps.notes.schemas.common import NoteSchema, NoteCreateRequestSchema, NoteUpdateRequestSchema

######remove this block after replacement######
from typing import Dict, List, Optional, Tuple, Union
from datetime import datetime, timezone
import logging
from fastapi import HTTPException, status
from sqlalchemy import select, and_, or_, func, desc, asc
from sqlalchemy.orm import Session, selectinload

from src.apps.notes.models.note import Note
from src.apps.customers.models.customer import Customer
from src.apps.users.models.user import User
from src.apps.customers.services import get_customer as get_customer_service
from src.apps.notes.schemas.note_common import (
    NoteResponseSchema,
    NoteCreateSchema,
    NoteUpdateSchema,
    NoteListResponseSchema
)
from src.apps.notes.schemas.note_requests import (
    NoteCreateRequestSchema,
    NoteUpdateRequestSchema
)
from src.helpers.pagination import QueryPaginator
from src.core.exceptions import APIException
from src.core.functions import generate_id
###############################################

logger = logging.getLogger(__name__)

##########remove this block after replacement######
def _load_note_with_relations(db: Session, note_id: int) -> Note:
    """
    Helper function to load a note with its relationships.
    
    Args:
        db: Database session
        note_id: ID of the note to load
    
    Returns:
        Note with loaded relationships
    """
    return db.execute(
        select(Note)
        .options(selectinload(Note.created_by))
        .where(Note.id == note_id)
    ).scalar_one()


def _build_note_query_with_relations():
    """
    Helper function to build a base note query with relationships loaded.
    
    Returns:
        SQLAlchemy select statement with relationships pre-loaded
    """
    return select(Note).options(selectinload(Note.created_by))


async def create_customer_note(
    db: Session,
    customer_id: str,
    note_data: NoteCreateRequestSchema,
    created_by_user: User
) -> NoteResponseSchema:
    """Create a new note for a customer."""
    try:
        # Validate customer exists using existing customer service
        customer_schema = await get_customer_service(db, customer_id)

        # Get customer model for relationship operations (optimized - use schema ID)
        customer = db.get(Customer, customer_schema.id)
        if not customer:
            raise APIException(
                message="Customer not found",
                status_code=404,
                module="notes.services"
            )

        # Create note object directly with current timestamp
        current_time = datetime.utcnow()
        note_obj = Note(
            description=note_data.description,
            created_by_id=created_by_user.id,
            created_at=current_time,
            updated_at=current_time
        )
        
        # Associate with customer through many-to-many relationship
        note_obj.customer.append(customer)
        
        db.add(note_obj)
        db.commit()
        db.refresh(note_obj)

        # Load the created_by relationship for response
        note_with_relations = _load_note_with_relations(db, note_obj.id)

        logger.info(f"Note created for customer {customer_id} with ID: {note_obj.id}")
        return NoteResponseSchema.model_validate(note_with_relations)
        
    except APIException:
        raise  
    except Exception as e:
        logger.error(f"Error creating note for customer {customer_id}: {e}")
        db.rollback()
        raise APIException(
            message=f"Failed to create note: {str(e)}",
            status_code=500,
            module="notes.services"
        )


async def get_customer_notes(
    db: Session,
    customer_id: str,
    limit: int = 10,
    offset: int = 0,
    search: Optional[str] = None
) -> Dict:
    """Get paginated list of notes for a customer."""
    try:
        # Validate customer exists using existing customer service
        customer_schema = await get_customer_service(db, customer_id)

        # Base query - get notes associated with this customer
        # Since Note has many-to-many with Customer, we need to join through the association table
        stmt = (
            _build_note_query_with_relations()
            .join(Note.customer)
            .where(Customer.id == customer_schema.id)
        )
        
        # Filter out deleted records
        stmt = stmt.where(Note.deleted_at.is_(None))
            
        # Apply search filter
        if search:
            search_term = f"%{search}%"
            stmt = stmt.where(Note.description.ilike(search_term))
        
        # Apply default sorting (created_at descending)
        stmt = stmt.order_by(desc(Note.created_at))
        
        # Use QueryPaginator for pagination
        paginator = QueryPaginator(
            query=stmt,
            schema=NoteResponseSchema,
            db=db,
            model=Note,
            url=f"/api/v1/customers/{customer_id}/notes",
            offset=offset,
            limit=limit,
            use_orm=True,
        )
        
        pagination_result = paginator.paginate()
        
        return {
            "data": pagination_result.result,
            "total": pagination_result.total,
            "page": pagination_result.page,
            "per_page": pagination_result.per_page,
            "next": pagination_result.next,
            "previous": pagination_result.previous,
            "first": pagination_result.first,
            "last": pagination_result.last,
        }
        
    except APIException:
        raise  
    except Exception as e:
        logger.error(f"Error getting notes for customer {customer_id}: {e}")
        raise APIException(
            message=f"Failed to get customer notes: {str(e)}",
            status_code=500,
            module="notes.services"
        )


async def get_customer_note(
    db: Session,
    customer_id: str,
    note_id: int
) -> NoteResponseSchema:
    """Get a specific customer note by ID."""
    try:
        # Validate customer exists using existing customer service
        customer_schema = await get_customer_service(db, customer_id)

        # Base query - get notes associated with this customer
        stmt = (
            _build_note_query_with_relations()
            .join(Note.customer)
            .where(
                and_(
                    Note.id == note_id,
                    Customer.id == customer_schema.id
                )
            )
        )
        stmt = stmt.where(Note.deleted_at.is_(None))
        
        note = db.execute(stmt).scalar_one_or_none()
        if not note:
            raise APIException(
                message="Note not found",
                status_code=404,
                module="notes.services"
            )

        return NoteResponseSchema.model_validate(note)
        
    except APIException:
        raise  
    except Exception as e:
        logger.error(f"Error getting note {note_id} for customer {customer_id}: {e}")
        raise APIException(
            message=f"Failed to get note: {str(e)}",
            status_code=500,
            module="notes.services"
        )


async def update_customer_note(
    db: Session,
    customer_id: str,
    note_id: int,
    note_data: NoteUpdateRequestSchema,
    updated_by_user: User
) -> NoteResponseSchema:
    """Update a customer note."""
    try:
        # Validate customer exists using existing customer service
        customer_schema = await get_customer_service(db, customer_id)

        # Query for specific note associated with this customer
        stmt = (
            select(Note)
            .join(Note.customer)
            .where(
                and_(
                    Note.id == note_id,
                    Customer.id == customer_schema.id
                )
            )
        )
        stmt = stmt.where(Note.deleted_at.is_(None))
        
        note = db.execute(stmt).scalar_one_or_none()
        if not note:
            raise APIException(
                message="Note not found",
                status_code=404,
                module="notes.services"
            )

        # Update note fields from request data
        update_dict = note_data.model_dump(exclude_none=True)
        for key, value in update_dict.items():
            if hasattr(note, key):
                setattr(note, key, value)
        
        # Update timestamp
        note.updated_at = datetime.utcnow()
        
        db.commit()
        db.refresh(note)
        
        # Load relationships for response
        note_with_relations = _load_note_with_relations(db, note.id)

        logger.info(f"Note retrieved for customer {customer_id}, note ID: {note_id}")
        return NoteResponseSchema.model_validate(note_with_relations)
        
    except APIException:
        raise 
    except Exception as e:
        logger.error(f"Error updating note {note_id} for customer {customer_id}: {e}")
        db.rollback()
        raise APIException(
            message=f"Failed to update note: {str(e)}",
            status_code=500,
            module="notes.services"
        )


async def delete_customer_note(
    db: Session,
    customer_id: str,
    note_id: int,
    deleted_by_user: User
) -> Dict[str, any]:
    """Soft delete a customer note."""
    try:
        # Validate customer exists using existing customer service
        customer_schema = await get_customer_service(db, customer_id)

        # Query for specific note associated with this customer
        stmt = (
            select(Note)
            .join(Note.customer)
            .where(
                and_(
                    Note.id == note_id,
                    Customer.id == customer_schema.id
                )
            )
        )
        stmt = stmt.where(Note.deleted_at.is_(None))
        
        note = db.execute(stmt).scalar_one_or_none()
        if not note:
            raise APIException(
                message="Note not found",
                status_code=404,
                module="notes.services"
            )

        # Soft delete the note
        note.deleted_at = datetime.utcnow()
        
        db.commit()

        logger.info(f"Note {note_id} deleted for customer {customer_id}")
        return {
            "success": True,
            "message": "Note deleted successfully",
            "status_code": 200
        }
        
    except APIException:
        raise 
    except Exception as e:
        logger.error(f"Error deleting note {note_id} for customer {customer_id}: {e}")
        db.rollback()
        raise APIException(
            message=f"Failed to delete note: {str(e)}",
            status_code=500,
            module="notes.services"
        )
###############################################



######### do not remove the below lines #########
# NOTE SERVICES START HERE
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