"""
Report generation service.
"""
from datetime import date, datetime, timedelta
from typing import Optional, Dict, Any, List
from calendar import monthrange
from sqlalchemy.orm import Session
from sqlalchemy import func

from app.models.reservation import Reservation, ReservationStatus
from app.models.room import Room, RoomStatus
from app.models.folio import Folio, FolioItem
from app.models.payment import Payment, PaymentStatus
from app.models.establishment import Establishment
from app.models.stats import DailyStats
from app.services.calculator_service import CalculatorService


class ReportService:
    """Service for generating reports."""

    def __init__(self, db: Session):
        self.db = db
        self.calculator = CalculatorService(db)

    def get_daily_stats(
        self,
        establishment_id: int,
        target_date: date
    ) -> Dict[str, Any]:
        """
        Get or calculate daily statistics.
        """
        # Try to get from cached stats
        cached = self.db.query(DailyStats).filter(
            DailyStats.establishment_id == establishment_id,
            DailyStats.stat_date == target_date
        ).first()

        if cached:
            return {
                "rooms_available": cached.rooms_available,
                "rooms_sold": cached.rooms_sold,
                "nights_sold": cached.nights_sold,
                "arrivals": cached.arrivals,
                "departures": cached.departures,
                "revenue_accommodation": cached.revenue_accommodation,
                "revenue_fb": cached.revenue_fb,
                "revenue_other": cached.revenue_other,
                "revenue_total": cached.revenue_total,
                "occupancy_rate": cached.occupancy_rate,
                "adr": cached.adr,
                "revpar": cached.revpar
            }

        # Calculate fresh stats
        return self._calculate_daily_stats(establishment_id, target_date)

    def _calculate_daily_stats(
        self,
        establishment_id: int,
        target_date: date
    ) -> Dict[str, Any]:
        """Calculate daily statistics from live data."""
        # Get establishment
        establishment = self.db.query(Establishment).filter(
            Establishment.id == establishment_id
        ).first()

        if not establishment:
            return {}

        # Room counts
        total_rooms = self.db.query(Room).filter(
            Room.establishment_id == establishment_id,
            Room.is_active == True
        ).count()

        rooms_out_of_order = self.db.query(Room).filter(
            Room.establishment_id == establishment_id,
            Room.status.in_([RoomStatus.MAINTENANCE, RoomStatus.OUT_OF_ORDER])
        ).count()

        rooms_available = total_rooms - rooms_out_of_order

        # Reservations for the day
        rooms_sold = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date <= target_date,
            Reservation.check_out_date > target_date,
            Reservation.status.in_([
                ReservationStatus.CHECKED_IN,
                ReservationStatus.CHECKED_OUT
            ])
        ).count()

        # Guest nights
        nights_query = self.db.query(
            func.sum(Reservation.adults + Reservation.children)
        ).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date <= target_date,
            Reservation.check_out_date > target_date,
            Reservation.status.in_([
                ReservationStatus.CHECKED_IN,
                ReservationStatus.CHECKED_OUT
            ])
        ).scalar()

        nights_sold = int(nights_query) if nights_query else 0

        # Arrivals
        arrivals = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date == target_date,
            Reservation.status.in_([
                ReservationStatus.PENDING,
                ReservationStatus.CONFIRMED,
                ReservationStatus.CHECKED_IN
            ])
        ).count()

        # Departures
        departures = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_out_date == target_date,
            Reservation.status.in_([
                ReservationStatus.CHECKED_IN,
                ReservationStatus.CHECKED_OUT
            ])
        ).count()

        # Revenue by department
        date_start = datetime.combine(target_date, datetime.min.time())
        date_end = datetime.combine(target_date, datetime.max.time())

        revenue_query = self.db.query(
            FolioItem.department_code,
            func.sum(FolioItem.total_ttc).label('total')
        ).join(Folio).join(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            FolioItem.created_at.between(date_start, date_end),
            FolioItem.is_voided == False
        ).group_by(FolioItem.department_code).all()

        revenue_by_dept = {r.department_code: float(r.total) for r in revenue_query}
        revenue_accommodation = revenue_by_dept.get('HEBERG', 0)
        revenue_fb = sum(v for k, v in revenue_by_dept.items() if k in ['REST', 'BAR', 'ROOM_SERVICE'])
        revenue_other = sum(v for k, v in revenue_by_dept.items() if k not in ['HEBERG', 'REST', 'BAR', 'ROOM_SERVICE'])
        revenue_total = sum(revenue_by_dept.values())

        # Calculate KPIs
        occupancy_rate = self.calculator.calculate_occupancy_rate(rooms_sold, rooms_available, 1)
        adr = self.calculator.calculate_adr(revenue_accommodation, rooms_sold)
        revpar = self.calculator.calculate_revpar(revenue_accommodation, rooms_available, 1)

        return {
            "rooms_available": rooms_available,
            "rooms_sold": rooms_sold,
            "nights_sold": nights_sold,
            "arrivals": arrivals,
            "departures": departures,
            "revenue_accommodation": round(revenue_accommodation, 2),
            "revenue_fb": round(revenue_fb, 2),
            "revenue_other": round(revenue_other, 2),
            "revenue_total": round(revenue_total, 2),
            "occupancy_rate": round(occupancy_rate, 2),
            "adr": round(adr, 2),
            "revpar": round(revpar, 2)
        }

    def generate_daily_report(
        self,
        establishment_id: int,
        target_date: date
    ) -> Dict[str, Any]:
        """Generate detailed daily report."""
        establishment = self.db.query(Establishment).filter(
            Establishment.id == establishment_id
        ).first()

        if not establishment:
            return {}

        stats = self._calculate_daily_stats(establishment_id, target_date)

        # Get no-shows and cancellations
        no_shows = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date == target_date,
            Reservation.status == ReservationStatus.NO_SHOW
        ).count()

        cancellations = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date == target_date,
            Reservation.status == ReservationStatus.CANCELLED
        ).count()

        # In-house count
        in_house = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.status == ReservationStatus.CHECKED_IN
        ).count()

        # Revenue by department (detailed)
        date_start = datetime.combine(target_date, datetime.min.time())
        date_end = datetime.combine(target_date, datetime.max.time())

        revenue_details = self.db.query(
            FolioItem.department_code,
            func.sum(FolioItem.total_ht).label('total_ht'),
            func.sum(FolioItem.total_ttc).label('total_ttc')
        ).join(Folio).join(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            FolioItem.created_at.between(date_start, date_end),
            FolioItem.is_voided == False
        ).group_by(FolioItem.department_code).all()

        revenue_by_department = []
        total_ht = 0
        total_ttc = 0

        for r in revenue_details:
            ht = float(r.total_ht)
            ttc = float(r.total_ttc)
            total_ht += ht
            total_ttc += ttc
            revenue_by_department.append({
                "department_code": r.department_code,
                "department_name": r.department_code,
                "revenue_ht": round(ht, 2),
                "revenue_ttc": round(ttc, 2),
                "percentage": 0
            })

        # Calculate percentages
        for dept in revenue_by_department:
            if total_ttc > 0:
                dept["percentage"] = round((dept["revenue_ttc"] / total_ttc) * 100, 2)

        # Payments by method
        payments = self.db.query(
            Payment.payment_method,
            func.sum(Payment.amount).label('total')
        ).join(Folio).join(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Payment.payment_date.between(date_start, date_end),
            Payment.status == PaymentStatus.COMPLETED
        ).group_by(Payment.payment_method).all()

        payments_by_method = {p.payment_method.value: float(p.total) for p in payments}

        # Frequency index
        frequency_index = self.calculator.calculate_frequency_index(
            stats["nights_sold"], stats["rooms_sold"]
        )

        # Average guest revenue
        avg_guest_revenue = self.calculator.calculate_average_guest_revenue(
            stats["revenue_total"], stats["nights_sold"]
        )

        return {
            "date": target_date,
            "establishment_id": establishment_id,
            "establishment_name": establishment.name,
            "rooms_available": stats["rooms_available"],
            "rooms_sold": stats["rooms_sold"],
            "rooms_complimentary": 0,
            "rooms_out_of_order": establishment.capacity_rooms - stats["rooms_available"],
            "occupancy_rate": stats["occupancy_rate"],
            "arrivals": stats["arrivals"],
            "departures": stats["departures"],
            "no_shows": no_shows,
            "cancellations": cancellations,
            "in_house": in_house,
            "nights_sold": stats["nights_sold"],
            "revenue_by_department": revenue_by_department,
            "total_revenue_ht": round(total_ht, 2),
            "total_tax": round(total_ttc - total_ht, 2),
            "total_revenue_ttc": round(total_ttc, 2),
            "adr": stats["adr"],
            "revpar": stats["revpar"],
            "frequency_index": round(frequency_index, 2),
            "average_guest_revenue": round(avg_guest_revenue, 2),
            "payments_by_method": payments_by_method,
            "total_payments": sum(payments_by_method.values())
        }

    def generate_monthly_report(
        self,
        establishment_id: int,
        year: int,
        month: int
    ) -> Dict[str, Any]:
        """Generate monthly summary report."""
        establishment = self.db.query(Establishment).filter(
            Establishment.id == establishment_id
        ).first()

        if not establishment:
            return {}

        _, days_in_month = monthrange(year, month)
        start_date = date(year, month, 1)
        end_date = date(year, month, days_in_month)

        # Aggregate daily stats
        daily_stats = []
        totals = {
            "rooms_sold": 0,
            "nights_sold": 0,
            "arrivals": 0,
            "departures": 0,
            "revenue_accommodation": 0,
            "revenue_fb": 0,
            "revenue_other": 0,
            "revenue_total": 0
        }

        current_date = start_date
        while current_date <= end_date:
            day_stats = self.get_daily_stats(establishment_id, current_date)
            daily_stats.append({
                "date": current_date.isoformat(),
                **day_stats
            })

            totals["rooms_sold"] += day_stats.get("rooms_sold", 0)
            totals["nights_sold"] += day_stats.get("nights_sold", 0)
            totals["arrivals"] += day_stats.get("arrivals", 0)
            totals["departures"] += day_stats.get("departures", 0)
            totals["revenue_accommodation"] += day_stats.get("revenue_accommodation", 0)
            totals["revenue_fb"] += day_stats.get("revenue_fb", 0)
            totals["revenue_other"] += day_stats.get("revenue_other", 0)
            totals["revenue_total"] += day_stats.get("revenue_total", 0)

            current_date += timedelta(days=1)

        # Calculate averages
        rooms_available = establishment.capacity_rooms
        avg_occupancy = self.calculator.calculate_occupancy_rate(
            totals["rooms_sold"], rooms_available, days_in_month
        )
        avg_adr = self.calculator.calculate_adr(
            totals["revenue_accommodation"], totals["rooms_sold"]
        )
        avg_revpar = self.calculator.calculate_revpar(
            totals["revenue_accommodation"], rooms_available, days_in_month
        )
        avg_frequency = self.calculator.calculate_frequency_index(
            totals["nights_sold"], totals["rooms_sold"]
        )
        avg_guest_revenue = self.calculator.calculate_average_guest_revenue(
            totals["revenue_total"], totals["nights_sold"]
        )

        # Revenue by department for month
        month_start = datetime.combine(start_date, datetime.min.time())
        month_end = datetime.combine(end_date, datetime.max.time())

        revenue_details = self.db.query(
            FolioItem.department_code,
            func.sum(FolioItem.total_ht).label('total_ht'),
            func.sum(FolioItem.total_ttc).label('total_ttc')
        ).join(Folio).join(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            FolioItem.created_at.between(month_start, month_end),
            FolioItem.is_voided == False
        ).group_by(FolioItem.department_code).all()

        revenue_by_department = []
        total_ht = 0
        total_ttc = 0

        for r in revenue_details:
            ht = float(r.total_ht)
            ttc = float(r.total_ttc)
            total_ht += ht
            total_ttc += ttc
            revenue_by_department.append({
                "department_code": r.department_code,
                "department_name": r.department_code,
                "revenue_ht": round(ht, 2),
                "revenue_ttc": round(ttc, 2),
                "percentage": 0
            })

        for dept in revenue_by_department:
            if total_ttc > 0:
                dept["percentage"] = round((dept["revenue_ttc"] / total_ttc) * 100, 2)

        # No-shows and cancellations
        no_shows = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date.between(start_date, end_date),
            Reservation.status == ReservationStatus.NO_SHOW
        ).count()

        cancellations = self.db.query(Reservation).join(Room).filter(
            Room.establishment_id == establishment_id,
            Reservation.check_in_date.between(start_date, end_date),
            Reservation.status == ReservationStatus.CANCELLED
        ).count()

        return {
            "year": year,
            "month": month,
            "establishment_id": establishment_id,
            "establishment_name": establishment.name,
            "total_rooms_available": rooms_available * days_in_month,
            "total_rooms_sold": totals["rooms_sold"],
            "average_occupancy_rate": round(avg_occupancy, 2),
            "total_nights_sold": totals["nights_sold"],
            "total_arrivals": totals["arrivals"],
            "total_departures": totals["departures"],
            "total_no_shows": no_shows,
            "total_cancellations": cancellations,
            "revenue_by_department": revenue_by_department,
            "total_revenue_ht": round(total_ht, 2),
            "total_tax": round(total_ttc - total_ht, 2),
            "total_revenue_ttc": round(total_ttc, 2),
            "average_adr": round(avg_adr, 2),
            "average_revpar": round(avg_revpar, 2),
            "average_frequency_index": round(avg_frequency, 2),
            "average_guest_revenue": round(avg_guest_revenue, 2),
            "daily_stats": daily_stats
        }