"""
Export functions for database data.
"""
import logging
from io import BytesIO
from typing import Optional, List, Dict, Any
from pathlib import Path

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import selectinload

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill
from openpyxl.utils import get_column_letter

from src.database.models import User, UserProfile
from src.database.enums import MassageTechnique

logger = logging.getLogger(__name__)


async def export_users_to_excel(
    session: AsyncSession,
    city: Optional[str] = None,
    massage_technique: Optional[MassageTechnique] = None,
) -> BytesIO:
    """
    Export users to Excel file filtered by city and/or massage technique.
    
    Args:
        session: Database session
        city: Optional city filter
        massage_technique: Optional massage technique filter
        
    Returns:
        BytesIO object with Excel file content
    """
    # Build query
    query = (
        select(User, UserProfile)
        .outerjoin(UserProfile, User.id == UserProfile.user_id)
        .options(selectinload(User.profile))
    )
    
    # Apply filters
    if city:
        query = query.where(User.city.ilike(f"%{city}%"))
    
    if massage_technique:
        query = query.where(UserProfile.massage_technique == massage_technique)
    
    # Execute query
    result = await session.execute(query)
    rows = result.all()
    
    # Create workbook
    wb = Workbook()
    ws = wb.active
    ws.title = "Пользователи"
    
    # Headers
    headers = [
        "ID",
        "Telegram ID",
        "Имя",
        "Пол",
        "Город",
        "Техника массажа",
        "Место практики",
        "Опыт с клиентами",
        "Навыки в соцсетях",
        "Удобство общения",
        "Telegram Username",
        "Telegram Link",
    ]
    
    # Style headers
    header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
    header_font = Font(bold=True, color="FFFFFF")
    
    for col_idx, header in enumerate(headers, start=1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.fill = header_fill
        cell.font = header_font
        cell.alignment = Alignment(horizontal="center", vertical="center")
    
    # Data rows
    row_num = 2
    for user, profile in rows:
        # Get telegram username if available (we'll need to store it or get from user data)
        tg_username = None
        tg_link = f"https://t.me/{tg_username}" if tg_username else f"tg://user?id={user.tg_id}"
        
        # If no username, create link with ID
        if not tg_username:
            tg_link = f"tg://user?id={user.tg_id}"
        
        row_data = [
            user.id,
            user.tg_id,
            user.user_name or "",
            user.user_gender.value if user.user_gender else "",
            user.city or "",
            profile.massage_technique.value if profile and profile.massage_technique else "",
            profile.where_practicing.value if profile and profile.where_practicing else "",
            profile.have_clients.value if profile and profile.have_clients else "",
            profile.social_skill.value if profile and profile.social_skill else "",
            profile.communication_ease.value if profile and profile.communication_ease else "",
            tg_username or "",
            tg_link,
        ]
        
        for col_idx, value in enumerate(row_data, start=1):
            cell = ws.cell(row=row_num, column=col_idx, value=value)
            cell.alignment = Alignment(horizontal="left", vertical="center")
        
        row_num += 1
    
    # Auto-adjust column widths
    for col_idx, header in enumerate(headers, start=1):
        column_letter = get_column_letter(col_idx)
        max_length = len(header)
        
        # Find max length in column
        for row in ws[column_letter]:
            if row.value:
                max_length = max(max_length, len(str(row.value)))
        
        # Set width (with some padding)
        ws.column_dimensions[column_letter].width = min(max_length + 2, 50)
    
    # Save to BytesIO
    output = BytesIO()
    wb.save(output)
    output.seek(0)
    
    logger.info(f"Exported {row_num - 2} users to Excel (city={city}, technique={massage_technique})")
    
    return output


async def get_users_export_data(
    session: AsyncSession,
    city: Optional[str] = None,
    massage_technique: Optional[MassageTechnique] = None,
) -> List[Dict[str, Any]]:
    """
    Get users data for export (without creating Excel file).
    Useful for preview or other formats.
    
    Args:
        session: Database session
        city: Optional city filter
        massage_technique: Optional massage technique filter
        
    Returns:
        List of user dictionaries
    """
    query = (
        select(User, UserProfile)
        .outerjoin(UserProfile, User.id == UserProfile.user_id)
        .options(selectinload(User.profile))
    )
    
    if city:
        query = query.where(User.city.ilike(f"%{city}%"))
    
    if massage_technique:
        query = query.where(UserProfile.massage_technique == massage_technique)
    
    result = await session.execute(query)
    rows = result.all()
    
    users_data = []
    for user, profile in rows:
        tg_link = f"tg://user?id={user.tg_id}"
        
        users_data.append({
            "id": user.id,
            "tg_id": user.tg_id,
            "name": user.user_name or "",
            "gender": user.user_gender.value if user.user_gender else "",
            "city": user.city or "",
            "massage_technique": profile.massage_technique.value if profile and profile.massage_technique else "",
            "where_practicing": profile.where_practicing.value if profile and profile.where_practicing else "",
            "have_clients": profile.have_clients.value if profile and profile.have_clients else "",
            "social_skill": profile.social_skill.value if profile and profile.social_skill else "",
            "communication_ease": profile.communication_ease.value if profile and profile.communication_ease else "",
            "tg_link": tg_link,
        })
    
    return users_data