""" Budget seed data for comparative analysis. Generates realistic hotel budget data for 2023, 2024, and 2025 to enable year-over-year and budget-vs-actual comparisons. """ from datetime import date, datetime from sqlalchemy.orm import Session from app.models.budget import Budget, BudgetLine, BudgetLineType, BudgetPeriod, BudgetStatus, VarianceAnalysis from app.models.department import Department from app.models.establishment import Establishment from app.models.user import User def seed_budgets(db: Session) -> None: """ Seed budget data for comparative analysis. Creates yearly, quarterly, and monthly budgets for 2023, 2024, 2025. """ # Get default establishment establishment = db.query(Establishment).first() if not establishment: print("No establishment found - skipping budget seeds") return admin = db.query(User).first() admin_id = admin.id if admin else None # Check if budgets already seeded existing = db.query(Budget).filter(Budget.establishment_id == establishment.id).count() if existing >= 10: print(f"Budget seeds already exist ({existing} budgets) - skipping") return print("Seeding budget data for comparative analysis...") # === YEARLY BUDGETS === _create_yearly_budgets(db, establishment.id, admin_id) # === QUARTERLY BUDGETS === _create_quarterly_budgets(db, establishment.id, admin_id) # === MONTHLY BUDGETS (2024 + 2025) === _create_monthly_budgets(db, establishment.id, admin_id) # === VARIANCE ANALYSES === _create_variance_analyses(db, establishment.id) db.commit() print("Budget seeds created successfully.") def _create_yearly_budgets(db: Session, establishment_id: int, admin_id: int) -> None: """Create yearly budgets for 2022, 2023, 2024, 2025.""" yearly_data = { 2022: { "name": "Budget Annuel 2022", "status": BudgetStatus.LOCKED, "revenue": { "HEBERG": 42_000_000, "REST": 9_800_000, "BAR": 3_200_000, "ROOM_SERVICE": 1_500_000, "SPA": 2_800_000, "BOUTIQUE": 1_200_000, "PARKING": 850_000, "LAUNDRY": 620_000, "MINIBAR": 480_000, "PHONE": 150_000, "MISC": 400_000, }, "expenses": { "HEBERG": 15_000_000, "REST": 6_500_000, "BAR": 1_800_000, "ROOM_SERVICE": 600_000, "SPA": 1_200_000, "BOUTIQUE": 700_000, "PARKING": 300_000, "LAUNDRY": 450_000, "MINIBAR": 200_000, "MISC": 2_500_000, } }, 2023: { "name": "Budget Annuel 2023", "status": BudgetStatus.LOCKED, "revenue": { "HEBERG": 48_000_000, "REST": 11_200_000, "BAR": 3_800_000, "ROOM_SERVICE": 1_800_000, "SPA": 3_200_000, "BOUTIQUE": 1_400_000, "PARKING": 950_000, "LAUNDRY": 720_000, "MINIBAR": 550_000, "PHONE": 180_000, "MISC": 450_000, }, "expenses": { "HEBERG": 17_000_000, "REST": 7_200_000, "BAR": 2_100_000, "ROOM_SERVICE": 750_000, "SPA": 1_400_000, "BOUTIQUE": 800_000, "PARKING": 350_000, "LAUNDRY": 520_000, "MINIBAR": 230_000, "MISC": 2_800_000, } }, 2024: { "name": "Budget Annuel 2024", "status": BudgetStatus.APPROVED, "revenue": { "HEBERG": 54_000_000, "REST": 12_500_000, "BAR": 4_200_000, "ROOM_SERVICE": 2_100_000, "SPA": 3_800_000, "BOUTIQUE": 1_600_000, "PARKING": 1_050_000, "LAUNDRY": 820_000, "MINIBAR": 620_000, "PHONE": 200_000, "MISC": 530_000, }, "expenses": { "HEBERG": 19_500_000, "REST": 8_100_000, "BAR": 2_400_000, "ROOM_SERVICE": 850_000, "SPA": 1_600_000, "BOUTIQUE": 900_000, "PARKING": 400_000, "LAUNDRY": 590_000, "MINIBAR": 260_000, "MISC": 3_200_000, } }, 2025: { "name": "Budget Annuel 2025", "status": BudgetStatus.APPROVED, "revenue": { "HEBERG": 60_000_000, "REST": 14_000_000, "BAR": 4_800_000, "ROOM_SERVICE": 2_400_000, "SPA": 4_400_000, "BOUTIQUE": 1_900_000, "PARKING": 1_200_000, "LAUNDRY": 950_000, "MINIBAR": 720_000, "PHONE": 220_000, "MISC": 610_000, }, "expenses": { "HEBERG": 21_500_000, "REST": 8_900_000, "BAR": 2_700_000, "ROOM_SERVICE": 960_000, "SPA": 1_850_000, "BOUTIQUE": 1_000_000, "PARKING": 450_000, "LAUNDRY": 650_000, "MINIBAR": 290_000, "MISC": 3_600_000, } }, } for year, data in yearly_data.items(): existing = db.query(Budget).filter( Budget.establishment_id == establishment_id, Budget.year == year, Budget.period_type == BudgetPeriod.YEARLY ).first() if existing: continue budget = Budget( establishment_id=establishment_id, name=data["name"], period_type=BudgetPeriod.YEARLY, year=year, start_date=date(year, 1, 1), end_date=date(year, 12, 31), status=data["status"], notes=f"Budget annuel {year} - données de référence pour analyse comparative", created_by_id=admin_id, approved_by_id=admin_id if data["status"] != BudgetStatus.DRAFT else None, approved_at=date(year, 1, 15) if data["status"] != BudgetStatus.DRAFT else None, ) db.add(budget) db.flush() # Revenue lines for dept_code, amount in data["revenue"].items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.REVENUE, department_code=dept_code, category="Revenus d'exploitation", amount=float(amount), currency="XOF", description=f"Revenus {dept_code} - {year}", ) db.add(line) # Expense lines for dept_code, amount in data["expenses"].items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.EXPENSE, department_code=dept_code, category="Charges d'exploitation", amount=float(amount), currency="XOF", description=f"Charges {dept_code} - {year}", ) db.add(line) db.flush() print(f" Created yearly budgets for 2022-2025") def _create_quarterly_budgets(db: Session, establishment_id: int, admin_id: int) -> None: """Create quarterly budgets for 2024 and 2025.""" # Seasonality factors per quarter (Senegal tourism peaks) seasonality = { 1: 0.28, # Q1: haute saison (jan-mars) - tourisme hivernal européen 2: 0.22, # Q2: saison intermédiaire (avr-juin) 3: 0.18, # Q3: basse saison (juil-sept) - saison des pluies 4: 0.32, # Q4: haute saison (oct-déc) - fêtes & tourisme } q_names = {1: "Jan-Mars", 2: "Avr-Juin", 3: "Juil-Sept", 4: "Oct-Déc"} q_months = {1: (1, 3), 2: (4, 6), 3: (7, 9), 4: (10, 12)} q_days = {1: (1, 31, 3), 2: (1, 30, 6), 3: (1, 30, 9), 4: (1, 31, 12)} annual_revenue_2024 = { "HEBERG": 54_000_000, "REST": 12_500_000, "BAR": 4_200_000, "ROOM_SERVICE": 2_100_000, "SPA": 3_800_000, "BOUTIQUE": 1_600_000, "PARKING": 1_050_000, "LAUNDRY": 820_000, "MINIBAR": 620_000, "PHONE": 200_000, "MISC": 530_000, } annual_expense_2024 = { "HEBERG": 19_500_000, "REST": 8_100_000, "BAR": 2_400_000, "ROOM_SERVICE": 850_000, "SPA": 1_600_000, "BOUTIQUE": 900_000, "PARKING": 400_000, "LAUNDRY": 590_000, "MINIBAR": 260_000, "MISC": 3_200_000, } annual_revenue_2025 = { "HEBERG": 60_000_000, "REST": 14_000_000, "BAR": 4_800_000, "ROOM_SERVICE": 2_400_000, "SPA": 4_400_000, "BOUTIQUE": 1_900_000, "PARKING": 1_200_000, "LAUNDRY": 950_000, "MINIBAR": 720_000, "PHONE": 220_000, "MISC": 610_000, } annual_expense_2025 = { "HEBERG": 21_500_000, "REST": 8_900_000, "BAR": 2_700_000, "ROOM_SERVICE": 960_000, "SPA": 1_850_000, "BOUTIQUE": 1_000_000, "PARKING": 450_000, "LAUNDRY": 650_000, "MINIBAR": 290_000, "MISC": 3_600_000, } for year, annual_rev, annual_exp in [ (2024, annual_revenue_2024, annual_expense_2024), (2025, annual_revenue_2025, annual_expense_2025), ]: for q in range(1, 5): existing = db.query(Budget).filter( Budget.establishment_id == establishment_id, Budget.year == year, Budget.quarter == q, Budget.period_type == BudgetPeriod.QUARTERLY ).first() if existing: continue factor = seasonality[q] qd = q_days[q] budget = Budget( establishment_id=establishment_id, name=f"Budget T{q} {year} ({q_names[q]})", period_type=BudgetPeriod.QUARTERLY, year=year, quarter=q, start_date=date(year, q_months[q][0], 1), end_date=date(year, qd[2], qd[1]), status=BudgetStatus.APPROVED if year == 2024 else BudgetStatus.APPROVED, notes=f"Budget trimestriel T{q} {year} - {q_names[q]}", created_by_id=admin_id, approved_by_id=admin_id, approved_at=date(year, q_months[q][0], 10), ) db.add(budget) db.flush() for dept_code, annual_amount in annual_rev.items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.REVENUE, department_code=dept_code, category="Revenus trimestriels", amount=round(annual_amount * factor, 0), currency="XOF", description=f"Revenus {dept_code} T{q} {year}", ) db.add(line) for dept_code, annual_amount in annual_exp.items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.EXPENSE, department_code=dept_code, category="Charges trimestrielles", amount=round(annual_amount * factor, 0), currency="XOF", description=f"Charges {dept_code} T{q} {year}", ) db.add(line) db.flush() print(" Created quarterly budgets for 2024-2025") def _create_monthly_budgets(db: Session, establishment_id: int, admin_id: int) -> None: """Create monthly budgets for all 12 months of 2024 and 2025.""" # Monthly seasonality weights (based on Senegal hotel market) monthly_weights = { 1: 0.100, # Janvier - haute saison 2: 0.095, # Février - haute saison 3: 0.085, # Mars - fin haute saison 4: 0.075, # Avril - intermédiaire 5: 0.070, # Mai - intermédiaire 6: 0.065, # Juin - début basse saison 7: 0.055, # Juillet - basse saison (pluies) 8: 0.050, # Août - basse saison (pluies) 9: 0.060, # Septembre - reprise 10: 0.085, # Octobre - haute saison 11: 0.100, # Novembre - haute saison 12: 0.110, # Décembre - pic fêtes } month_names = { 1: "Janvier", 2: "Février", 3: "Mars", 4: "Avril", 5: "Mai", 6: "Juin", 7: "Juillet", 8: "Août", 9: "Septembre", 10: "Octobre", 11: "Novembre", 12: "Décembre" } month_end_days = { 1: 31, 2: 28, 3: 31, 4: 30, 5: 31, 6: 30, 7: 31, 8: 31, 9: 30, 10: 31, 11: 30, 12: 31 } annual_configs = { 2024: { "revenue": { "HEBERG": 54_000_000, "REST": 12_500_000, "BAR": 4_200_000, "ROOM_SERVICE": 2_100_000, "SPA": 3_800_000, "BOUTIQUE": 1_600_000, "PARKING": 1_050_000, "LAUNDRY": 820_000, "MINIBAR": 620_000, "PHONE": 200_000, "MISC": 530_000, }, "expenses": { "HEBERG": 19_500_000, "REST": 8_100_000, "BAR": 2_400_000, "ROOM_SERVICE": 850_000, "SPA": 1_600_000, "BOUTIQUE": 900_000, "PARKING": 400_000, "LAUNDRY": 590_000, "MINIBAR": 260_000, "MISC": 3_200_000, } }, 2025: { "revenue": { "HEBERG": 60_000_000, "REST": 14_000_000, "BAR": 4_800_000, "ROOM_SERVICE": 2_400_000, "SPA": 4_400_000, "BOUTIQUE": 1_900_000, "PARKING": 1_200_000, "LAUNDRY": 950_000, "MINIBAR": 720_000, "PHONE": 220_000, "MISC": 610_000, }, "expenses": { "HEBERG": 21_500_000, "REST": 8_900_000, "BAR": 2_700_000, "ROOM_SERVICE": 960_000, "SPA": 1_850_000, "BOUTIQUE": 1_000_000, "PARKING": 450_000, "LAUNDRY": 650_000, "MINIBAR": 290_000, "MISC": 3_600_000, } } } for year, config in annual_configs.items(): for month in range(1, 13): existing = db.query(Budget).filter( Budget.establishment_id == establishment_id, Budget.year == year, Budget.month == month, Budget.period_type == BudgetPeriod.MONTHLY ).first() if existing: continue weight = monthly_weights[month] end_day = month_end_days[month] if month == 2 and year % 4 == 0: end_day = 29 status = BudgetStatus.LOCKED if year == 2024 and month <= 12 else BudgetStatus.APPROVED budget = Budget( establishment_id=establishment_id, name=f"Budget {month_names[month]} {year}", period_type=BudgetPeriod.MONTHLY, year=year, month=month, start_date=date(year, month, 1), end_date=date(year, month, end_day), status=status, notes=f"Budget mensuel {month_names[month]} {year}", created_by_id=admin_id, approved_by_id=admin_id, approved_at=date(year, month, 5) if month > 1 else date(year - 1, 12, 20), ) db.add(budget) db.flush() for dept_code, annual_amount in config["revenue"].items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.REVENUE, department_code=dept_code, category="Revenus mensuels", amount=round(annual_amount * weight, 0), currency="XOF", description=f"Revenus {dept_code} - {month_names[month]} {year}", ) db.add(line) for dept_code, annual_amount in config["expenses"].items(): line = BudgetLine( budget_id=budget.id, line_type=BudgetLineType.EXPENSE, department_code=dept_code, category="Charges mensuelles", amount=round(annual_amount * weight, 0), currency="XOF", description=f"Charges {dept_code} - {month_names[month]} {year}", ) db.add(line) db.flush() print(" Created monthly budgets for 2024-2025") def _create_variance_analyses(db: Session, establishment_id: int) -> None: """Create realistic variance analysis data comparing budget vs actual.""" # Performance variance factors per department (actual vs budget %) # Positive = over budget (favorable for revenue), negative = under budget variance_factors = { "HEBERG": {"2023": +0.05, "2024_Q1": +0.12, "2024_Q2": -0.03, "2024_Q3": -0.08, "2024_Q4": +0.15}, "REST": {"2023": +0.08, "2024_Q1": +0.10, "2024_Q2": +0.05, "2024_Q3": -0.05, "2024_Q4": +0.18}, "BAR": {"2023": +0.03, "2024_Q1": +0.07, "2024_Q2": +0.02, "2024_Q3": -0.10, "2024_Q4": +0.20}, "ROOM_SERVICE": {"2023": -0.02, "2024_Q1": +0.05, "2024_Q2": -0.05, "2024_Q3": -0.12, "2024_Q4": +0.08}, "SPA": {"2023": +0.15, "2024_Q1": +0.18, "2024_Q2": +0.10, "2024_Q3": -0.03, "2024_Q4": +0.22}, "BOUTIQUE": {"2023": -0.05, "2024_Q1": +0.03, "2024_Q2": -0.08, "2024_Q3": -0.15, "2024_Q4": +0.05}, "PARKING": {"2023": +0.02, "2024_Q1": +0.04, "2024_Q2": +0.01, "2024_Q3": -0.06, "2024_Q4": +0.09}, "LAUNDRY": {"2023": +0.00, "2024_Q1": +0.02, "2024_Q2": -0.02, "2024_Q3": -0.04, "2024_Q4": +0.06}, "MINIBAR": {"2023": +0.10, "2024_Q1": +0.08, "2024_Q2": +0.03, "2024_Q3": -0.08, "2024_Q4": +0.12}, "PHONE": {"2023": -0.10, "2024_Q1": -0.08, "2024_Q2": -0.12, "2024_Q3": -0.18, "2024_Q4": -0.05}, "MISC": {"2023": +0.05, "2024_Q1": +0.06, "2024_Q2": +0.03, "2024_Q3": -0.02, "2024_Q4": +0.10}, } existing_count = db.query(VarianceAnalysis).filter( VarianceAnalysis.establishment_id == establishment_id ).count() if existing_count > 0: print(f" Variance analyses already exist ({existing_count}) - skipping") return # Get 2024 annual budget budget_2024 = db.query(Budget).filter( Budget.establishment_id == establishment_id, Budget.year == 2024, Budget.period_type == BudgetPeriod.YEARLY ).first() budget_2023 = db.query(Budget).filter( Budget.establishment_id == establishment_id, Budget.year == 2023, Budget.period_type == BudgetPeriod.YEARLY ).first() if not budget_2024 or not budget_2023: print(" Required budgets not found for variance analysis - skipping") return # Create variance analyses for each department/quarter quarter_dates = { "2024_Q1": date(2024, 3, 31), "2024_Q2": date(2024, 6, 30), "2024_Q3": date(2024, 9, 30), "2024_Q4": date(2024, 12, 31), } seasonality_q = {1: 0.28, 2: 0.22, 3: 0.18, 4: 0.32} for dept_code in variance_factors.keys(): # Get 2024 revenue budget line for this dept budget_line_2024 = next( (l for l in budget_2024.lines if l.department_code == dept_code and l.line_type == BudgetLineType.REVENUE), None ) budget_line_2023 = next( (l for l in budget_2023.lines if l.department_code == dept_code and l.line_type == BudgetLineType.REVENUE), None ) if not budget_line_2024: continue annual_budget = budget_line_2024.amount prior_year = budget_line_2023.amount if budget_line_2023 else annual_budget * 0.9 # Annual 2023 variance v2023 = variance_factors[dept_code]["2023"] actual_2023 = prior_year * (1 + v2023) va_2023 = VarianceAnalysis( establishment_id=establishment_id, budget_id=budget_2023.id, analysis_date=date(2023, 12, 31), period_type=BudgetPeriod.YEARLY, department_code=dept_code, budget_amount=round(prior_year, 0), actual_amount=round(actual_2023, 0), variance_amount=round(actual_2023 - prior_year, 0), variance_percent=round(v2023 * 100, 2), prior_year_amount=round(prior_year * 0.92, 0), yoy_variance=round(actual_2023 - prior_year * 0.92, 0), yoy_variance_percent=round(((actual_2023 / (prior_year * 0.92)) - 1) * 100, 2), is_favorable=(v2023 >= 0), ) db.add(va_2023) # Quarterly 2024 variances for q in range(1, 5): key = f"2024_Q{q}" vq = variance_factors[dept_code].get(key, 0) q_budget = round(annual_budget * seasonality_q[q], 0) q_actual = round(q_budget * (1 + vq), 0) q_prior = round(prior_year * seasonality_q[q], 0) va = VarianceAnalysis( establishment_id=establishment_id, budget_id=budget_2024.id, analysis_date=quarter_dates[key], period_type=BudgetPeriod.QUARTERLY, department_code=dept_code, budget_amount=q_budget, actual_amount=q_actual, variance_amount=round(q_actual - q_budget, 0), variance_percent=round(vq * 100, 2), prior_year_amount=q_prior, yoy_variance=round(q_actual - q_prior, 0), yoy_variance_percent=round(((q_actual / q_prior) - 1) * 100, 2) if q_prior else 0, is_favorable=(vq >= 0), ) db.add(va) db.flush() print(" Created variance analyses for 2023-2024")