"""
Customer Notes Services - Customer-specific note operations
"""

from typing import Dict, List, Optional
from datetime import datetime
import logging
from fastapi import HTTPException, status
from sqlalchemy import select, and_, desc
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.customer_services import get_customer
from src.apps.notes.schemas.note_common import (
    NoteResponseSchema,
    NoteListResponseSchema
)
from src.apps.notes.schemas.note_requests import (
    NoteCreateRequestSchema,
    NoteUpdateRequestSchema
)
from src.helpers.pagination import QueryPaginator
from src.core.exceptions import APIException

logger = logging.getLogger(__name__)


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(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="customer_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}")
        raise APIException(
            message=f"Failed to create note: {str(e)}",
            status_code=500,
            module="customer_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(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="customer_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(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="customer_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="customer_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(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="customer_notes.services"
            )

        # Update note fields directly (optimized approach)
        if note_data.description is not None:
            note.description = note_data.description
        
        db.commit()
        db.refresh(note)
        
        # Load relationships for response
        note_with_relations = _load_note_with_relations(db, note.id)

        logger.info(f"Note updated 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}")
        raise APIException(
            message=f"Failed to update note: {str(e)}",
            status_code=500,
            module="customer_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(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="customer_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}")
        raise APIException(
            message=f"Failed to delete note: {str(e)}",
            status_code=500,
            module="customer_notes.services"
        )