"""
Budget Export Service - Generates structured Excel reports
compatible with verif_budget_reporting_2018.py verification script.

Feuilles générées:
  - Budget N          → budget de référence (mois + total)
  - Réalisé N         → réalisations agrégées depuis les données réelles
  - Réalisé N-1       → réalisations année précédente
  - Janvier … Décembre → feuilles mensuelles (source de vérité)
  - Cumul Fevrier … Cumul Decembre → cumuls avec écarts
  - Ratio F&B / Cumul Ratio F&B
"""

from datetime import date, datetime
from typing import List, Dict, Optional, Any
from io import BytesIO
from decimal import Decimal

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill, NamedStyle
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.worksheet import Worksheet
from sqlalchemy.orm import Session
from sqlalchemy import func

from app.models.budget import Budget, BudgetLine, BudgetLineType, BudgetPeriod
from app.models.folio import FolioItem, Folio
from app.models.reservation import Reservation
from app.models.room import Room


# Constantes
MOIS_NAMES = [
    "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin",
    "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Decembre"
]

MOIS_NAMES_FR = [
    "Janvier", "Février", "Mars", "Avril", "Mai", "Juin",
    "Juillet", "Août", "Septembre", "Octobre", "Novembre", "Décembre"
]

DEPARTMENTS = [
    ("HEBERG", "Hébergement"),
    ("REST", "Restaurant"),
    ("BAR", "Bar"),
    ("ROOM_SERVICE", "Room Service"),
    ("SPA", "Spa & Wellness"),
    ("BLANCHISSERIE", "Blanchisserie"),
    ("MINIBAR", "Minibar"),
    ("PARKING", "Parking"),
    ("TELEPHONE", "Téléphone"),
    ("AUTRES", "Autres services"),
]

FB_DEPARTMENTS = ["REST", "BAR", "ROOM_SERVICE", "MINIBAR"]

# Lignes budgétaires standard
BUDGET_LINES = [
    # Revenus
    {"label": "REVENUS", "type": "header", "line_type": None},
    {"label": "Hébergement", "type": "revenue", "line_type": "revenue", "dept": "HEBERG"},
    {"label": "Restaurant", "type": "revenue", "line_type": "revenue", "dept": "REST"},
    {"label": "Bar", "type": "revenue", "line_type": "revenue", "dept": "BAR"},
    {"label": "Room Service", "type": "revenue", "line_type": "revenue", "dept": "ROOM_SERVICE"},
    {"label": "Spa & Wellness", "type": "revenue", "line_type": "revenue", "dept": "SPA"},
    {"label": "Blanchisserie", "type": "revenue", "line_type": "revenue", "dept": "BLANCHISSERIE"},
    {"label": "Minibar", "type": "revenue", "line_type": "revenue", "dept": "MINIBAR"},
    {"label": "Parking", "type": "revenue", "line_type": "revenue", "dept": "PARKING"},
    {"label": "Téléphone", "type": "revenue", "line_type": "revenue", "dept": "TELEPHONE"},
    {"label": "Autres services", "type": "revenue", "line_type": "revenue", "dept": "AUTRES"},
    {"label": "TOTAL REVENUS", "type": "total_revenue", "line_type": None},
    {"label": "", "type": "spacer", "line_type": None},
    # Dépenses
    {"label": "DÉPENSES", "type": "header", "line_type": None},
    {"label": "Coût matières F&B", "type": "expense", "line_type": "expense", "dept": "FB_COST"},
    {"label": "Salaires & Charges", "type": "expense", "line_type": "expense", "dept": "SALAIRES"},
    {"label": "Énergie", "type": "expense", "line_type": "expense", "dept": "ENERGIE"},
    {"label": "Entretien & Maintenance", "type": "expense", "line_type": "expense", "dept": "MAINTENANCE"},
    {"label": "Marketing & Commercial", "type": "expense", "line_type": "expense", "dept": "MARKETING"},
    {"label": "Frais généraux", "type": "expense", "line_type": "expense", "dept": "FRAIS_GEN"},
    {"label": "Amortissements", "type": "expense", "line_type": "expense", "dept": "AMORT"},
    {"label": "TOTAL DÉPENSES", "type": "total_expense", "line_type": None},
    {"label": "", "type": "spacer", "line_type": None},
    {"label": "RÉSULTAT NET", "type": "net_result", "line_type": None},
]


class BudgetExportService:
    """Service for exporting budgets to Excel format."""

    def __init__(self, db: Session):
        self.db = db
        self._setup_styles()

    def _setup_styles(self):
        """Configure Excel styles."""
        self.header_font = Font(bold=True, size=12)
        self.title_font = Font(bold=True, size=14)
        self.money_format = '#,##0'
        self.percent_format = '0.0%'

        self.header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
        self.header_font_white = Font(bold=True, color="FFFFFF")
        self.total_fill = PatternFill(start_color="D9E2F3", end_color="D9E2F3", fill_type="solid")
        self.positive_font = Font(color="006400")
        self.negative_font = Font(color="DC143C")

        self.thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )

    def get_actual_by_department(
        self,
        establishment_id: int,
        start_date: date,
        end_date: date
    ) -> Dict[str, float]:
        """Get actual revenue by department for a period."""
        items = self.db.query(
            FolioItem.department_code,
            func.sum(FolioItem.total_ttc).label('total')
        ).join(
            FolioItem.folio
        ).join(
            Folio.reservation
        ).join(
            Reservation.room
        ).filter(
            Room.establishment_id == establishment_id,
            FolioItem.created_at >= start_date,
            FolioItem.created_at <= end_date,
            FolioItem.is_voided == False
        ).group_by(FolioItem.department_code).all()

        return {item.department_code: float(item.total or 0) for item in items}

    def get_budget_data_by_month(
        self,
        establishment_id: int,
        year: int
    ) -> Dict[int, Dict[str, float]]:
        """Get budget data organized by month."""
        budgets = self.db.query(Budget).filter(
            Budget.establishment_id == establishment_id,
            Budget.year == year,
            Budget.period_type == BudgetPeriod.MONTHLY
        ).all()

        data = {}
        for budget in budgets:
            if budget.month:
                month_data = {}
                for line in budget.lines:
                    key = f"{line.department_code}_{line.line_type.value}"
                    month_data[key] = float(line.amount)
                data[budget.month] = month_data

        return data

    def get_actual_data_by_month(
        self,
        establishment_id: int,
        year: int
    ) -> Dict[int, Dict[str, float]]:
        """Get actual revenue data organized by month."""
        data = {}
        for month in range(1, 13):
            start = date(year, month, 1)
            if month == 12:
                end = date(year + 1, 1, 1)
            else:
                end = date(year, month + 1, 1)

            actuals = self.get_actual_by_department(establishment_id, start, end)
            data[month] = actuals

        return data

    def _apply_header_style(self, ws: Worksheet, row: int, max_col: int):
        """Apply header style to a row."""
        for col in range(1, max_col + 1):
            cell = ws.cell(row=row, column=col)
            cell.fill = self.header_fill
            cell.font = self.header_font_white
            cell.alignment = Alignment(horizontal='center', vertical='center')
            cell.border = self.thin_border

    def _create_budget_n_sheet(
        self,
        wb: Workbook,
        establishment_id: int,
        year: int,
        budget_data: Dict[int, Dict[str, float]]
    ):
        """Create 'Budget N' sheet with monthly columns and totals."""
        ws = wb.create_sheet("Budget N")

        # Headers
        ws.cell(row=1, column=1, value=f"BUDGET {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=14)

        # Column headers
        ws.cell(row=3, column=1, value="Libellé")
        for i, mois in enumerate(MOIS_NAMES_FR, start=2):
            ws.cell(row=3, column=i, value=mois)
        ws.cell(row=3, column=14, value="Total")

        self._apply_header_style(ws, 3, 14)

        # Data rows
        row = 4
        for line_def in BUDGET_LINES:
            label = line_def["label"]
            line_type = line_def["type"]

            ws.cell(row=row, column=1, value=label)

            if line_type == "header":
                ws.cell(row=row, column=1).font = self.header_font
            elif line_type in ("revenue", "expense"):
                dept = line_def.get("dept", "")
                key = f"{dept}_{line_def.get('line_type', '')}"

                for month in range(1, 13):
                    month_data = budget_data.get(month, {})
                    value = month_data.get(key, 0)
                    ws.cell(row=row, column=month + 1, value=value)
                    ws.cell(row=row, column=month + 1).number_format = self.money_format

                # Total formula
                ws.cell(row=row, column=14, value=f"=SUM(B{row}:M{row})")
                ws.cell(row=row, column=14).number_format = self.money_format

            elif line_type == "total_revenue":
                ws.cell(row=row, column=1).font = self.header_font
                ws.row_dimensions[row].height = 20
                for col in range(2, 15):
                    # Sum revenue rows (rows 5-11)
                    ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}5:{get_column_letter(col)}11)")
                    ws.cell(row=row, column=col).number_format = self.money_format
                    ws.cell(row=row, column=col).fill = self.total_fill

            elif line_type == "total_expense":
                ws.cell(row=row, column=1).font = self.header_font
                for col in range(2, 15):
                    # Sum expense rows
                    ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}16:{get_column_letter(col)}22)")
                    ws.cell(row=row, column=col).number_format = self.money_format
                    ws.cell(row=row, column=col).fill = self.total_fill

            elif line_type == "net_result":
                ws.cell(row=row, column=1).font = self.header_font
                for col in range(2, 15):
                    # Revenue - Expenses
                    ws.cell(row=row, column=col, value=f"={get_column_letter(col)}12-{get_column_letter(col)}23")
                    ws.cell(row=row, column=col).number_format = self.money_format
                    ws.cell(row=row, column=col).fill = self.total_fill

            row += 1

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 15):
            ws.column_dimensions[get_column_letter(col)].width = 12

    def _create_realise_sheet(
        self,
        wb: Workbook,
        sheet_name: str,
        establishment_id: int,
        year: int,
        actual_data: Dict[int, Dict[str, float]]
    ):
        """Create 'Réalisé N' or 'Réalisé N-1' sheet with formulas referencing monthly sheets."""
        ws = wb.create_sheet(sheet_name)

        # Headers
        ws.cell(row=1, column=1, value=f"RÉALISÉ {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=14)

        # Column headers
        ws.cell(row=3, column=1, value="Libellé")
        for i, mois in enumerate(MOIS_NAMES_FR, start=2):
            ws.cell(row=3, column=i, value=mois)
        ws.cell(row=3, column=14, value="Total")

        self._apply_header_style(ws, 3, 14)

        # Data rows - only revenue departments
        row = 4
        for line_def in BUDGET_LINES:
            label = line_def["label"]
            line_type = line_def["type"]

            ws.cell(row=row, column=1, value=label)

            if line_type == "header":
                ws.cell(row=row, column=1).font = self.header_font
            elif line_type == "revenue":
                dept = line_def.get("dept", "")

                # Use formulas referencing monthly sheets
                for month in range(1, 13):
                    month_sheet = MOIS_NAMES[month - 1]
                    # Reference the "Réalisé" column (C) from the monthly sheet
                    ws.cell(row=row, column=month + 1, value=f"='{month_sheet}'!C{row}")
                    ws.cell(row=row, column=month + 1).number_format = self.money_format

                # Total formula
                ws.cell(row=row, column=14, value=f"=SUM(B{row}:M{row})")
                ws.cell(row=row, column=14).number_format = self.money_format

            elif line_type == "total_revenue":
                ws.cell(row=row, column=1).font = self.header_font
                for col in range(2, 15):
                    ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}5:{get_column_letter(col)}11)")
                    ws.cell(row=row, column=col).number_format = self.money_format
                    ws.cell(row=row, column=col).fill = self.total_fill
            elif line_type in ("expense", "total_expense", "net_result"):
                # Skip expense lines for Réalisé (only tracking revenue)
                pass

            row += 1

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 15):
            ws.column_dimensions[get_column_letter(col)].width = 12

    def _create_monthly_sheet(
        self,
        wb: Workbook,
        month: int,
        establishment_id: int,
        year: int,
        budget_data: Dict[int, Dict[str, float]],
        actual_data: Dict[int, Dict[str, float]],
        prior_year_data: Dict[int, Dict[str, float]]
    ):
        """Create monthly detail sheet with budget, actual, and variances."""
        sheet_name = MOIS_NAMES[month - 1]
        ws = wb.create_sheet(sheet_name)

        month_budget = budget_data.get(month, {})
        month_actual = actual_data.get(month, {})
        month_prior = prior_year_data.get(month, {})

        # Title
        ws.cell(row=1, column=1, value=f"{MOIS_NAMES_FR[month - 1].upper()} {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7)

        # Headers
        headers = ["Libellé", "Budget", "Réalisé", "Écart", "Écart %", "N-1", "Réalisé - N-1"]
        for col, header in enumerate(headers, start=1):
            ws.cell(row=3, column=col, value=header)

        self._apply_header_style(ws, 3, 7)

        # Data rows
        row = 4
        for line_def in BUDGET_LINES:
            label = line_def["label"]
            line_type = line_def["type"]

            ws.cell(row=row, column=1, value=label)

            if line_type == "header":
                ws.cell(row=row, column=1).font = self.header_font
            elif line_type == "revenue":
                dept = line_def.get("dept", "")
                key = f"{dept}_revenue"

                budget_val = month_budget.get(key, 0)
                actual_val = month_actual.get(dept, 0)
                prior_val = month_prior.get(dept, 0)

                ws.cell(row=row, column=2, value=budget_val)
                ws.cell(row=row, column=3, value=actual_val)
                # Écart = Réalisé - Budget
                ws.cell(row=row, column=4, value=f"=C{row}-B{row}")
                # Écart % = (Réalisé - Budget) / Budget
                ws.cell(row=row, column=5, value=f"=IF(B{row}<>0,(C{row}-B{row})/B{row},0)")
                ws.cell(row=row, column=6, value=prior_val)
                # Réalisé - N-1
                ws.cell(row=row, column=7, value=f"=C{row}-F{row}")

                # Formatting
                for col in [2, 3, 4, 6, 7]:
                    ws.cell(row=row, column=col).number_format = self.money_format
                ws.cell(row=row, column=5).number_format = self.percent_format

            elif line_type == "total_revenue":
                ws.cell(row=row, column=1).font = self.header_font
                for col in range(2, 8):
                    if col != 5:  # Not percentage
                        ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}5:{get_column_letter(col)}11)")
                        ws.cell(row=row, column=col).number_format = self.money_format
                    else:
                        ws.cell(row=row, column=col, value=f"=IF(B{row}<>0,(C{row}-B{row})/B{row},0)")
                        ws.cell(row=row, column=col).number_format = self.percent_format
                    ws.cell(row=row, column=col).fill = self.total_fill

            row += 1

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 8):
            ws.column_dimensions[get_column_letter(col)].width = 15

    def _create_cumul_sheet(
        self,
        wb: Workbook,
        end_month: int,
        year: int
    ):
        """Create cumulative sheet referencing monthly sheets."""
        sheet_name = f"Cumul {MOIS_NAMES[end_month - 1]}"
        ws = wb.create_sheet(sheet_name)

        # Title
        ws.cell(row=1, column=1, value=f"CUMUL JANVIER - {MOIS_NAMES_FR[end_month - 1].upper()} {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=7)

        # Headers
        headers = ["Libellé", "Budget Cumul", "Réalisé Cumul", "Écart", "Écart %", "N-1 Cumul", "Réalisé - N-1"]
        for col, header in enumerate(headers, start=1):
            ws.cell(row=3, column=col, value=header)

        self._apply_header_style(ws, 3, 7)

        # Data rows with formulas referencing monthly sheets
        row = 4
        for line_def in BUDGET_LINES:
            label = line_def["label"]
            line_type = line_def["type"]

            ws.cell(row=row, column=1, value=label)

            if line_type == "header":
                ws.cell(row=row, column=1).font = self.header_font
            elif line_type == "revenue":
                # Sum from monthly sheets
                budget_parts = []
                actual_parts = []
                prior_parts = []

                for m in range(1, end_month + 1):
                    month_sheet = MOIS_NAMES[m - 1]
                    budget_parts.append(f"'{month_sheet}'!B{row}")
                    actual_parts.append(f"'{month_sheet}'!C{row}")
                    prior_parts.append(f"'{month_sheet}'!F{row}")

                ws.cell(row=row, column=2, value=f"={'+'.join(budget_parts)}")
                ws.cell(row=row, column=3, value=f"={'+'.join(actual_parts)}")
                ws.cell(row=row, column=4, value=f"=C{row}-B{row}")
                ws.cell(row=row, column=5, value=f"=IF(B{row}<>0,(C{row}-B{row})/B{row},0)")
                ws.cell(row=row, column=6, value=f"={'+'.join(prior_parts)}")
                ws.cell(row=row, column=7, value=f"=C{row}-F{row}")

                for col in [2, 3, 4, 6, 7]:
                    ws.cell(row=row, column=col).number_format = self.money_format
                ws.cell(row=row, column=5).number_format = self.percent_format

            elif line_type == "total_revenue":
                ws.cell(row=row, column=1).font = self.header_font
                for col in range(2, 8):
                    if col != 5:
                        ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}5:{get_column_letter(col)}11)")
                        ws.cell(row=row, column=col).number_format = self.money_format
                    else:
                        ws.cell(row=row, column=col, value=f"=IF(B{row}<>0,(C{row}-B{row})/B{row},0)")
                        ws.cell(row=row, column=col).number_format = self.percent_format
                    ws.cell(row=row, column=col).fill = self.total_fill

            row += 1

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 8):
            ws.column_dimensions[get_column_letter(col)].width = 15

    def _create_ratio_fb_sheet(
        self,
        wb: Workbook,
        year: int
    ):
        """Create 'Ratio F&B' sheet."""
        ws = wb.create_sheet("Ratio F&B")

        # Title
        ws.cell(row=1, column=1, value=f"RATIO F&B {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=14)

        # Headers
        ws.cell(row=2, column=1, value="Indicateur")
        for i, mois in enumerate(MOIS_NAMES_FR, start=2):
            ws.cell(row=2, column=i, value=mois)
        ws.cell(row=2, column=14, value="Moyenne")

        self._apply_header_style(ws, 2, 14)

        # F&B Departments
        fb_depts = [
            ("Restaurant", "REST"),
            ("Bar", "BAR"),
            ("Room Service", "ROOM_SERVICE"),
            ("Minibar", "MINIBAR"),
        ]

        row = 3

        # CA F&B par département
        ws.cell(row=row, column=1, value="CA F&B")
        ws.cell(row=row, column=1).font = self.header_font
        row += 1

        for label, dept in fb_depts:
            ws.cell(row=row, column=1, value=label)
            for month in range(1, 13):
                month_sheet = MOIS_NAMES[month - 1]
                # Reference actual value from monthly sheet
                dept_row = 5 + ["HEBERG", "REST", "BAR", "ROOM_SERVICE"].index(dept) if dept in ["HEBERG", "REST", "BAR", "ROOM_SERVICE"] else 5
                if dept == "REST":
                    dept_row = 6
                elif dept == "BAR":
                    dept_row = 7
                elif dept == "ROOM_SERVICE":
                    dept_row = 8
                elif dept == "MINIBAR":
                    dept_row = 11
                ws.cell(row=row, column=month + 1, value=f"='{month_sheet}'!C{dept_row}")
                ws.cell(row=row, column=month + 1).number_format = self.money_format
            ws.cell(row=row, column=14, value=f"=AVERAGE(B{row}:M{row})")
            ws.cell(row=row, column=14).number_format = self.money_format
            row += 1

        # Total CA F&B
        ws.cell(row=row, column=1, value="TOTAL CA F&B")
        ws.cell(row=row, column=1).font = self.header_font
        start_row = 4
        end_row = row - 1
        for col in range(2, 15):
            ws.cell(row=row, column=col, value=f"=SUM({get_column_letter(col)}{start_row}:{get_column_letter(col)}{end_row})")
            ws.cell(row=row, column=col).number_format = self.money_format
            ws.cell(row=row, column=col).fill = self.total_fill
        total_fb_row = row
        row += 2

        # Ratio F&B / CA Total
        ws.cell(row=row, column=1, value="Ratio F&B / CA Total")
        ws.cell(row=row, column=1).font = self.header_font
        for month in range(1, 13):
            month_sheet = MOIS_NAMES[month - 1]
            ws.cell(row=row, column=month + 1, value=f"=IF('{month_sheet}'!C12>0,{get_column_letter(month + 1)}{total_fb_row}/'{month_sheet}'!C12,0)")
            ws.cell(row=row, column=month + 1).number_format = self.percent_format
        ws.cell(row=row, column=14, value=f"=AVERAGE(B{row}:M{row})")
        ws.cell(row=row, column=14).number_format = self.percent_format

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 15):
            ws.column_dimensions[get_column_letter(col)].width = 12

    def _create_cumul_ratio_fb_sheet(
        self,
        wb: Workbook,
        year: int
    ):
        """Create 'Cumul Ratio F&B' sheet referencing 'Ratio F&B'."""
        ws = wb.create_sheet("Cumul Ratio F&B")

        # Title
        ws.cell(row=1, column=1, value=f"CUMUL RATIO F&B {year}")
        ws.cell(row=1, column=1).font = self.title_font
        ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=14)

        # Headers
        ws.cell(row=2, column=1, value="Cumul jusqu'à")
        for i, mois in enumerate(MOIS_NAMES_FR, start=2):
            ws.cell(row=2, column=i, value=mois)

        self._apply_header_style(ws, 2, 13)

        row = 3

        # Cumul CA F&B
        ws.cell(row=row, column=1, value="Cumul CA F&B")
        ws.cell(row=row, column=1).font = self.header_font
        for month in range(1, 13):
            # Sum from Ratio F&B sheet up to current month
            formula_parts = [f"'Ratio F&B'!{get_column_letter(m + 1)}8" for m in range(1, month + 1)]
            ws.cell(row=row, column=month + 1, value=f"={'+'.join(formula_parts)}")
            ws.cell(row=row, column=month + 1).number_format = self.money_format
        row += 1

        # Cumul Ratio F&B
        ws.cell(row=row, column=1, value="Ratio F&B Cumulé")
        ws.cell(row=row, column=1).font = self.header_font
        for month in range(1, 13):
            ws.cell(row=row, column=month + 1, value=f"='Ratio F&B'!{get_column_letter(month + 1)}10")
            ws.cell(row=row, column=month + 1).number_format = self.percent_format

        # Column widths
        ws.column_dimensions['A'].width = 25
        for col in range(2, 14):
            ws.column_dimensions[get_column_letter(col)].width = 12

    def generate_budget_report(
        self,
        establishment_id: int,
        year: int
    ) -> BytesIO:
        """
        Generate complete budget report Excel file.

        Returns BytesIO containing the Excel file.
        """
        wb = Workbook()

        # Remove default sheet
        default_sheet = wb.active
        wb.remove(default_sheet)

        # Get data
        budget_data = self.get_budget_data_by_month(establishment_id, year)
        actual_data = self.get_actual_data_by_month(establishment_id, year)
        prior_year_data = self.get_actual_data_by_month(establishment_id, year - 1)

        # Create sheets in order
        self._create_budget_n_sheet(wb, establishment_id, year, budget_data)
        self._create_realise_sheet(wb, "Réalisé N", establishment_id, year, actual_data)
        self._create_realise_sheet(wb, "Réalisé N-1", establishment_id, year - 1, prior_year_data)

        # Monthly sheets
        for month in range(1, 13):
            self._create_monthly_sheet(
                wb, month, establishment_id, year,
                budget_data, actual_data, prior_year_data
            )

        # Cumul sheets (February to December)
        for month in range(2, 13):
            self._create_cumul_sheet(wb, month, year)

        # Ratio F&B sheets
        self._create_ratio_fb_sheet(wb, year)
        self._create_cumul_ratio_fb_sheet(wb, year)

        # Save to BytesIO
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)

        return buffer

    def generate_reference_file(
        self,
        year: int = 2018,
        output_path: Optional[str] = None
    ) -> BytesIO:
        """
        Generate a reference Excel file with sample data.
        Used for testing the verification script.
        """
        wb = Workbook()
        default_sheet = wb.active
        wb.remove(default_sheet)

        # Sample budget data
        sample_budget = {}
        sample_actual = {}
        sample_prior = {}

        import random
        random.seed(42)  # Reproducible

        for month in range(1, 13):
            sample_budget[month] = {
                "HEBERG_revenue": random.randint(15000000, 25000000),
                "REST_revenue": random.randint(5000000, 10000000),
                "BAR_revenue": random.randint(2000000, 5000000),
                "ROOM_SERVICE_revenue": random.randint(1000000, 3000000),
                "SPA_revenue": random.randint(500000, 2000000),
                "BLANCHISSERIE_revenue": random.randint(200000, 800000),
                "MINIBAR_revenue": random.randint(300000, 1000000),
                "PARKING_revenue": random.randint(100000, 500000),
                "TELEPHONE_revenue": random.randint(50000, 200000),
                "AUTRES_revenue": random.randint(100000, 500000),
            }

            sample_actual[month] = {
                "HEBERG": sample_budget[month]["HEBERG_revenue"] * random.uniform(0.85, 1.15),
                "REST": sample_budget[month]["REST_revenue"] * random.uniform(0.85, 1.15),
                "BAR": sample_budget[month]["BAR_revenue"] * random.uniform(0.85, 1.15),
                "ROOM_SERVICE": sample_budget[month]["ROOM_SERVICE_revenue"] * random.uniform(0.85, 1.15),
                "SPA": sample_budget[month]["SPA_revenue"] * random.uniform(0.85, 1.15),
                "BLANCHISSERIE": sample_budget[month]["BLANCHISSERIE_revenue"] * random.uniform(0.85, 1.15),
                "MINIBAR": sample_budget[month]["MINIBAR_revenue"] * random.uniform(0.85, 1.15),
                "PARKING": sample_budget[month]["PARKING_revenue"] * random.uniform(0.85, 1.15),
                "TELEPHONE": sample_budget[month]["TELEPHONE_revenue"] * random.uniform(0.85, 1.15),
                "AUTRES": sample_budget[month]["AUTRES_revenue"] * random.uniform(0.85, 1.15),
            }

            sample_prior[month] = {
                k: v * random.uniform(0.9, 1.1) for k, v in sample_actual[month].items()
            }

        # Create all sheets
        self._create_budget_n_sheet(wb, 1, year, sample_budget)
        self._create_realise_sheet(wb, "Réalisé N", 1, year, sample_actual)
        self._create_realise_sheet(wb, "Réalisé N-1", 1, year - 1, sample_prior)

        for month in range(1, 13):
            self._create_monthly_sheet(
                wb, month, 1, year,
                sample_budget, sample_actual, sample_prior
            )

        for month in range(2, 13):
            self._create_cumul_sheet(wb, month, year)

        self._create_ratio_fb_sheet(wb, year)
        self._create_cumul_ratio_fb_sheet(wb, year)

        # Save
        buffer = BytesIO()
        wb.save(buffer)
        buffer.seek(0)

        if output_path:
            with open(output_path, 'wb') as f:
                f.write(buffer.getvalue())
            buffer.seek(0)

        return buffer