2026-04-03-create_missing_reservation_extra_fees.py

"""
Create missing ReservationExtraFee records for BOOKING reservations affected by PCPv2
(created between 2026-03-24 13:00 UTC and 2026-04-01 09:54 UTC) and generate
invoices for those outside the automatic creation window.

Accounts for Booking.com fee renaming:
- resort_fee, destination_fee, etc. may arrive as "service_charge"
- tourism_fee is mapped into taxe_sejour (city tax)

Usage:
    docker compose exec -T bnbstaff python manage.py shell < scratches/2026-04-03-create_missing_reservation_extra_fees.py
"""

import datetime
import logging

from django.db import connection

from accounting.constants import INVOICE_CATEGORIES

from accounting_manager.constants import ENGINE_CHOICES
from accounting_manager.models import AntenneCategoryInvoiceSettings
from accounting_manager.services.invoices.extra_fee_invoices import (
    CreateExtraFeeInvoice,
)
from extra_fees.constants import PAID_TO_CHOICES
from extra_fees.models import ExtraFee, PlatformRentalExtraFee, ReservationExtraFee
from staffing.models import Reservation
from staffing.services.reservations.calculators import CalculateExtraFeeAmount

logger = logging.getLogger(__name__)

today = datetime.date.today()

# PCPv2 affected window
PCPV2_START = datetime.datetime(2026, 3, 24, 13, 0, tzinfo=datetime.timezone.utc)
PCPV2_END = datetime.datetime(2026, 4, 1, 9, 54, tzinfo=datetime.timezone.utc)

# Booking.com renames these fees to "service_charge"
SERVICE_CHARGE_MAPPED_KEYS = {
    "resort_fee",
    "destination_fee",
    "environment_fee",
    "spa_tax",
    "towel_fee",
    "electricity_fee",
    "linen_fee",
    "gas_fee",
    "oil_fee",
    "wood_fee",
    "water_fee",
    "transfer_fee",
}

service_charge_id = ExtraFee.objects.get(key_name="service_charge").id
service_charge_mapped_ids = set(
    ExtraFee.objects.filter(key_name__in=SERVICE_CHARGE_MAPPED_KEYS).values_list(
        "id", flat=True
    )
)
tourism_fee_id = ExtraFee.objects.get(key_name="tourism_fee").id

# -- Step 1: Find candidate reservations with missing extra fees (BOOKING only) --

MISSING_RESERVATIONS_SQL = """
SELECT DISTINCT reservation.id
FROM extra_fees_platformrentalextrafee platform_rental_extra_fee
JOIN extra_fees_rentalextrafee rental_extra_fee
    ON rental_extra_fee.id = platform_rental_extra_fee.rental_extra_fee_id
JOIN staffing_appartement rental
    ON rental.numero_contrat = rental_extra_fee.rental_id
JOIN staffing_reservationplatform platform
    ON platform.id = platform_rental_extra_fee.reservation_platform_id
JOIN extra_fees_extrafee extra_fee
    ON extra_fee.id = rental_extra_fee.extra_fee_id
JOIN staffing_reservation reservation
    ON reservation.appartement_id = rental_extra_fee.rental_id
    AND reservation.reservation_platform_id = platform_rental_extra_fee.reservation_platform_id
WHERE reservation.status NOT IN ('CANCELLED', 'CANCELLED_FEE', 'ARCHIVED')
  AND reservation.date_fin_reservation >= '2026-01-01'
  AND reservation.created_at < reservation.date_fin_reservation
  AND reservation.split_finished_at IS NULL
  AND reservation.created_at >= %s
  AND reservation.created_at <= %s
  AND platform.name = 'BOOKING'
  AND reservation.channel_manager_id IS NOT NULL
  AND NOT EXISTS (
      SELECT 1
      FROM extra_fees_reservationextrafee reservation_extra_fee
      WHERE reservation_extra_fee.reservation_id = reservation.id
        AND (
            reservation_extra_fee.extra_fee_id = extra_fee.id
            OR (
                extra_fee.key_name IN (
                    'resort_fee', 'destination_fee', 'environment_fee',
                    'spa_tax', 'towel_fee', 'electricity_fee',
                    'linen_fee', 'gas_fee', 'oil_fee',
                    'wood_fee', 'water_fee', 'transfer_fee'
                )
                AND reservation_extra_fee.extra_fee_id = (
                    SELECT id FROM extra_fees_extrafee WHERE key_name = 'service_charge'
                )
            )
        )
  )
  AND NOT (
      extra_fee.key_name = 'tourism_fee'
      AND reservation.taxe_sejour > 0
  )
"""

EXTRA_RESERVATION_IDS = [2008441, 2364312, 2493475]

with connection.cursor() as cursor:
    cursor.execute(MISSING_RESERVATIONS_SQL, [PCPV2_START, PCPV2_END])
    reservation_ids = [row[0] for row in cursor.fetchall()]
reservation_ids = list(set(reservation_ids + EXTRA_RESERVATION_IDS))
reservations = Reservation.objects.filter(id__in=reservation_ids).select_related(
    "appartement__antenne__property_manager__invoice_settings",
    "appartement__proprietaire_appartement",
    "reservation_platform",
)

print(f"Found {len(reservation_ids)} candidate reservations\n")

# -- Step 2 & 3: Create missing ReservationExtraFee and generate invoices --

# Invoice eligibility: checkout more than 10 days ago (outside the automatic window)
invoice_cutoff = today - datetime.timedelta(days=10)

created_count = 0
invoice_count = 0
skipped_count = 0
skipped_invoice_count = 0
failed_count = 0

for reservation in reservations:
    platform_rental_extra_fees = PlatformRentalExtraFee.objects.filter(
        rental_extra_fee__rental=reservation.appartement,
        reservation_platform=reservation.reservation_platform,
    ).select_related("rental_extra_fee__extra_fee")

    existing_extra_fee_ids = set(
        reservation.extra_fees.values_list("extra_fee_id", flat=True)
    )
    has_service_charge = service_charge_id in existing_extra_fee_ids

    for platform_ref in platform_rental_extra_fees:
        extra_fee = platform_ref.rental_extra_fee.extra_fee

        # Skip if reservation already has this exact fee
        if extra_fee.id in existing_extra_fee_ids:
            continue

        # Skip if Booking.com mapped this fee to service_charge and it exists
        if extra_fee.id in service_charge_mapped_ids and has_service_charge:
            skipped_count += 1
            continue

        # Skip tourism_fee if city tax is already present
        if extra_fee.id == tourism_fee_id and reservation.taxe_sejour > 0:
            skipped_count += 1
            continue

        amount = CalculateExtraFeeAmount.execute(
            number_of_guests=reservation.number_travellers,
            duration=reservation.duration(),
            fee_value=platform_ref.fee_value,
            fee_type=platform_ref.fee_type,
            is_stay_extension=reservation.is_stay_extension,
            base_total=reservation.chiffre_affaire,
        )

        if amount == 0:
            continue

        ref = ReservationExtraFee.objects.create(
            reservation=reservation,
            extra_fee=extra_fee,
            amount=amount,
            paid_to=platform_ref.rental_extra_fee.paid_to,
        )
        created_count += 1
        print(
            f"Created ReservationExtraFee#{ref.id} "
            f"for Reservation#{reservation.id} "
            f"({reservation.date_debut_reservation} - {reservation.date_fin_reservation}): "
            f"{extra_fee.name} = {amount}"
        )

        # -- Invoice generation --
        should_generate_invoice = (
            reservation.date_fin_reservation < invoice_cutoff
            and amount > 0
            and reservation.type_reservation == "CLASSIQUE"
            and reservation.date_fin_reservation
            > reservation.appartement.first_published_date.date()
            and hasattr(
                reservation.appartement.antenne.property_manager, "invoice_settings"
            )
            and reservation.appartement.antenne.property_manager.invoice_settings.engine
            == ENGINE_CHOICES.RENTALREADY
            and AntenneCategoryInvoiceSettings.objects.filter(
                antenne=reservation.appartement.antenne,
                category__name=INVOICE_CATEGORIES.EXTRA_FEE,
                active=True,
            ).exists()
        )

        if not should_generate_invoice:
            skipped_invoice_count += 1
            print(f"  -> Skipped invoice (will be handled by nightly task or not eligible)")
            continue

        # Check paid_to eligibility
        is_pm_fee = ref.paid_to == PAID_TO_CHOICES.PROPERTY_MANAGER
        owner = reservation.appartement.proprietaire_appartement
        if not is_pm_fee:
            owner_vat_setting_exists = AntenneCategoryInvoiceSettings.objects.filter(
                antenne=reservation.appartement.antenne,
                category__name=INVOICE_CATEGORIES.EXTRA_FEE,
            )
            if owner and not owner.vat_free:
                owner_vat_setting_exists = owner_vat_setting_exists.filter(
                    owner_vat_settings__vat_registered_active=True
                )
            elif owner and owner.vat_free:
                owner_vat_setting_exists = owner_vat_setting_exists.filter(
                    owner_vat_settings__vat_free_active=True
                )
            else:
                skipped_invoice_count += 1
                print(f"  -> Skipped invoice (no owner VAT settings)")
                continue

            if not owner_vat_setting_exists.exists():
                skipped_invoice_count += 1
                print(f"  -> Skipped invoice (owner VAT settings not active)")
                continue

        try:
            CreateExtraFeeInvoice.execute(reservation_extra_fee=ref)
            invoice_count += 1
            print(f"  -> Invoice created")
        except Exception as e:
            failed_count += 1
            print(f"  -> Invoice FAILED: {e}")

print(
    f"\nDone. "
    f"Extra fees created: {created_count}, "
    f"Skipped (service_charge/tourism_fee mapping): {skipped_count}, "
    f"Invoices created: {invoice_count}, "
    f"Invoices skipped: {skipped_invoice_count}, "
    f"Invoices failed: {failed_count}"
)