from django.shortcuts import render, redirect
from django.utils.decorators import method_decorator
from django.views.decorators.csrf import csrf_exempt
from django.db.models import Sum, Value, DecimalField
from django.http import JsonResponse
from django.db.models import Q
from django.db.models import Count
from decimal import Decimal, ROUND_HALF_UP
from decimal import InvalidOperation
from datetime import datetime
from sheets.services.halfyear_calc import compute_halfyear_context
import calendar
from django.utils import timezone
import re
from django.views.generic import TemplateView, View
from .models import (
    Client, SecondTableEntry, Institute, ExcelEntry, 
    Betriebskosten, MonthlySheet, Cell, MonthlySummary ,BetriebskostenSummary,AbrechnungCell
)
from django.db.models.functions import Coalesce
from django.utils.dateparse import parse_date  
import json
FIRST_SHEET_YEAR = 2025
FIRST_SHEET_MONTH = 1

CLIENT_GROUPS = {
    'ikp': {
        'label': 'IKP',
        # exactly as in the Clients admin
        'names': ['IKP'],
    },
    'phys_chem_bunt_fohrer': {
        'label': 'Buntkowsky + Dr. Fohrer',
        # include all variants you might have used for Buntkowsky
        'names': [
            'AG Buntk.',        # the one in your new entry
            'AG Buntkowsky.',   # from your original list
            'AG Buntkowsky',
            'Dr. Fohrer',
        ],
    },
    'mawi_alff_gutfleisch': {
        'label': 'Alff + AG Gutfleisch',
        # include both short and full forms
        'names': [
            'AG Alff',
            'AG Gutfl.',
            'AG Gutfleisch',
        ],
    },
    'm3_group': {
        'label': 'M3 Buntkowsky + M3 Thiele + M3 Gutfleisch',
        'names': [
            'M3 Buntkowsky',
            'M3 Thiele',
            'M3 Gutfleisch',
        ],
    },
    'merck': {
        'label': 'Merck',
        'names': [
            'Merck',   
        ],
    },
}

# Add this CALCULATION_CONFIG at the top of views.py

CALCULATION_CONFIG = {
    'top_left': {
        # Row mappings: Django row_index (0-based) to Excel row
        # Excel B4 -> Django row_index 1 (UI row 2)
        # Excel B5 -> Django row_index 2 (UI row 3)
        # Excel B6 -> Django row_index 3 (UI row 4)
        
        # B6 (row_index 3) = B5 (row_index 2) / 0.75
        3: "2 / 0.75",
        
        # B11 (row_index 10) = B9 (row_index 8)
        10: "8",
        
        # B14 (row_index 13) = B13 (row_index 12) - B11 (row_index 10) + B12 (row_index 11)
        13: "12 - 10 + 11",
        
        # Note: B5, B17, B19, B20 require IF logic, so they'll be handled separately
    },

    # other tables (top_right, bottom_1, ...) stay as they are


     ' top_right': {
    # UI Row 1 (Excel Row 4): Stand der Gaszähler (Vormonat) (Nm³)
    0: {
        'L': "9 / (9 + 9) if (9 + 9) > 0 else 0",  # L4 = L13/(L13+M13)
        'M': "9 / (9 + 9) if (9 + 9) > 0 else 0",  # M4 = M13/(L13+M13)
        'N': "9 / (9 + 9) if (9 + 9) > 0 else 0",  # N4 = N13/(N13+O13)
        'O': "9 / (9 + 9) if (9 + 9) > 0 else 0",  # O4 = O13/(N13+O13)
        'P': None,  # Editable
        'Q': None,  # Editable
        'R': None,  # Editable
    },
    
    # UI Row 2 (Excel Row 5): Gasrückführung (Nm³)
    1: {
        'L': "4",  # L5 = L8
        'M': "4",  # M5 = L8 (merged)
        'N': "4",  # N5 = N8
        'O': "4",  # O5 = N8 (merged)
        'P': "4 * 0",  # P5 = P8 * P4
        'Q': "4 * 0",  # Q5 = P8 * Q4
        'R': "4 * 0",  # R5 = P8 * R4
    },
    
    # UI Row 3 (Excel Row 6): Rückführung flüssig (Lit. L-He)
    2: {
    'L': "4",  # L6 = L8 (Sammelrückführungen)
    'M': "4",
    'N': "4",
    'O': "4",
    'P': "4",
    'Q': "4",
    'R': "4",
},
    
    # UI Row 4 (Excel Row 7): Sonderrückführungen (Lit. L-He) - EDITABLE
    3: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },
    
    # UI Row 5 (Excel Row 8): Sammelrückführungen (Lit. L-He)
    4: {
        'L': None,  # Will be populated from ExcelEntry
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },
    
    # UI Row 6 (Excel Row 9): Bestand in Kannen-1 (Lit. L-He) - EDITABLE
    5: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },
    
    # UI Row 7 (Excel Row 10): Summe Bestand (Lit. L-He)
    6: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },

    # UI Row 11 (Excel Row 14): Rückführ. Soll (Lit. L-He)
    # handled in calculate_top_right_dependents (merged pairs + M3)
    10: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },

    # UI Row 12 (Excel Row 15): Verluste (Soll-Rückf.) (Lit. L-He)
    # handled in calculate_top_right_dependents
    11: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },

    # UI Row 14 (Excel Row 17): Kaltgas Rückgabe (Lit. L-He) – Faktor
    # handled in calculate_top_right_dependents (different formulas for pair 1 vs pair 2 + M3)
    13: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },

    # UI Row 16 (Excel Row 19): Verbraucherverluste (Liter L-He)
    # handled in calculate_top_right_dependents
    15: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    },

    # UI Row 17 (Excel Row 20): %
    # handled in calculate_top_right_dependents
    16: {
        'L': None,
        'M': None,
        'N': None,
        'O': None,
        'P': None,
        'Q': None,
        'R': None,
    }
},
    'bottom_1': {
        5: "4 + 3 + 2",
        8: "7 - 6",
    },
    'bottom_2': {
        3: "1 + 2",
        6: "5 - 4",
    },
    'bottom_3': {
        2: "0 + 1",
        5: "3 + 4",
    },
    # Special configuration for summation column (last column)
    'summation_column': {
        # For each row that should be summed across columns
        'rows_to_sum': [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23],  # All rows
        # OR specify specific rows:
        # 'rows_to_sum': [0, 5, 10, 15, 20],  # Only specific rows
        # The last column index (0-based)
        'sum_column_index': 5,  # 6th column (0-5) since you have 6 clients
    }
}

ONE_DEC = Decimal("0.0")

def q1(x: Decimal) -> Decimal:
    """Quantize to exactly 1 decimal."""
    if x is None:
        return None
    return Decimal(x).quantize(ONE_DEC, rounding=ROUND_HALF_UP)

def d(x) -> Decimal:
    """Your safe Decimal helper (keep if you already have one)."""
    if x in (None, ""):
        return Decimal("0")
    return Decimal(str(x).replace(",", "."))
def build_halfyear_window(interval_year: int, start_month: int):
    """
    Build a list of (year, month) for the 6-month interval, possibly crossing into the next year.
    Example: (2025, 10) -> [(2025,10), (2025,11), (2025,12), (2026,1), (2026,2), (2026,3)]
    """
    window = []
    for offset in range(6):
        total_index = (start_month - 1) + offset  # 0-based
        y = interval_year + (total_index // 12)
        m = (total_index % 12) + 1
        window.append((y, m))
    return window
# ---------------------------------------------------------------------------
# Halbjahres-Bilanz helpers
# ---------------------------------------------------------------------------


# Assuming:
# - bottom_1.table has row "Gasbestand" at some fixed row index,
#   and columns: ... Nm³, Lit. LHe
GASBESTAND_ROW_INDEX = 9       
GASBESTAND_COL_NM3 =  3       

# In top_left / top_right, "Bestand in Kannen-1 (Lit. L-He)" is row_index 5
BESTAND_KANNEN_ROW_INDEX = 5

def pick_sheet_by_gasbestand(window, sheets_by_ym, prev_sheet):
    """
    Returns the last sheet in the window whose Gasbestand (J36, Nm³ column) != 0.
    If none found, returns prev_sheet (Übertrag_Dez__Vorjahr equivalent).
    """
    for (y, m) in reversed(window):
        sheet = sheets_by_ym.get((y, m))
        if not sheet:
            continue
        gasbestand_nm3 = get_bottom1_value(sheet, GASBESTAND_ROW_INDEX, GASBESTAND_COL_NM3)
        if gasbestand_nm3 != 0:
            return sheet
    return prev_sheet
def get_bottom1_value(sheet, row_index: int, col_index: int) -> Decimal:
    """Get a numeric value from bottom_1, or 0 if missing."""
    if sheet is None:
        return Decimal('0')

    cell = Cell.objects.filter(
        sheet=sheet,
        table_type='bottom_1',
        row_index=row_index,
        column_index=col_index,
    ).first()

    if cell is None or cell.value in (None, ''):
        return Decimal('0')

    try:
        return Decimal(str(cell.value))
    except Exception:
        return Decimal('0')

# MUST match the column order in your monthly_sheets top-right table



def get_top_right_value(sheet, client_name: str, row_index: int) -> Decimal:
    """
    Read a numeric value from the top_right table of a MonthlySheet for
    a given client (by column) and row_index.

    top_right cells are keyed by (sheet, table_type='top_right',
    row_index, column_index), where column_index is the position of the
    client in HALFYEAR_RIGHT_CLIENTS.
    """
    if sheet is None:
        return Decimal('0')

    col_index = RIGHT_CLIENT_INDEX.get(client_name)
    if col_index is None:
        return Decimal('0')

    cell = Cell.objects.filter(
        sheet=sheet,
        table_type='top_right',
        row_index=row_index,
        column_index=col_index,
    ).first()

    if cell is None or cell.value in (None, ''):
        return Decimal('0')

    try:
        return Decimal(str(cell.value))
    except Exception:
        return Decimal('0')

TR_RUECKF_FLUESSIG_ROW = 2     # confirmed by your March value 172.840560
TR_BESTAND_KANNEN_ROW  = 5     # confirmed by your earlier query
def get_bestand_kannen_for_month(sheet, client_name: str) -> Decimal:
    """
    'B9' in your description: Bestand in Kannen-1 (Lit. L-He)
    For this implementation we take it from top_left row_index = 5 for that client.
    """
    return get_top_left_value(sheet, client_name, row_index=BESTAND_KANNEN_ROW_INDEX)



# You already have HALFYEAR_CLIENTS for the left table (AG Vogel, AG Halfm, IKP)
HALFYEAR_CLIENTS = ["AG Vogel", "AG Halfm", "IKP"]

# NEW: clients for the top-right half-year table
HALFYEAR_RIGHT_CLIENTS = [
    "Dr. Fohrer",
    "AG Buntk.",
    "AG Alff",
    "AG Gutfl.",
    "M3 Thiele",
    "M3 Buntkowsky",
    "M3 Gutfleisch",
    "Merck",
]
BOTTOM1_COL_VOLUME = 0
BOTTOM1_COL_BAR    = 1
BOTTOM1_COL_KORR   = 2
BOTTOM1_COL_NM3    = 3
BOTTOM1_COL_LHE    = 4
BOTTOM2_ROW_ANLAGE = 0
BOTTOM2_COL_G39 = 0   # "Gefäss 2,5" (cell id shows column_index=0)
BOTTOM2_COL_I39 = 1   # "Gefäss 1,0" (cell id shows column_index=1)
BOTTOM2_ROW_INPUTS = {
    "g39": (0, 0),  # row_index=0, column_index=0  (your G39)
    "i39": (0, 1),  # row_index=0, column_index=1  (your I39)
}
FACTOR_NM3_TO_LHE = Decimal("0.75")
RIGHT_CLIENT_INDEX = {name: idx for idx, name in enumerate(HALFYEAR_RIGHT_CLIENTS)}


def halfyear_balance_view(request):
    interval_year = request.session.get('halfyear_year')
    interval_start = request.session.get('halfyear_start_month')

    if not interval_year or not interval_start:
        return redirect('clients_list')

    interval_year = int(interval_year)
    interval_start = int(interval_start)

    context = compute_halfyear_context(interval_year, interval_start)
    return render(request, 'halfyear_balance.html', context)
def get_bottom2_value(sheet, row_index: int, col_index: int) -> Decimal:
    """Get numeric value from bottom_2 or 0 if missing."""
    if sheet is None:
        return Decimal("0")
    cell = Cell.objects.filter(
        sheet=sheet,
        table_type="bottom_2",
        row_index=row_index,
        column_index=col_index,
    ).first()
    if cell is None or cell.value in (None, ""):
        return Decimal("0")
    try:
        return Decimal(str(cell.value))
    except Exception:
        return Decimal("0")

def get_top_left_value(sheet, client_name: str, row_index: int) -> Decimal:
    """
    Read a numeric value from the top_left table for a given month, client and row.
    Does NOT use column_index, because top_left is keyed only by client + row_index.
    """
    if sheet is None:
        return Decimal('0')

    client_obj = Client.objects.filter(name=client_name).first()
    if not client_obj:
        return Decimal('0')

    cell = Cell.objects.filter(
        sheet=sheet,
        table_type='top_left',
        client=client_obj,
        row_index=row_index,
    ).first()

    if cell is None or cell.value in (None, ''):
        return Decimal('0')

    try:
        return Decimal(str(cell.value))
    except Exception:
        return Decimal('0')
def get_group_clients(group_key):
    """Return queryset of clients that belong to a logical group."""
  

    group = CLIENT_GROUPS.get(group_key)
    if not group:
        return Client.objects.none()
    
    return Client.objects.filter(name__in=group['names'])

def calculate_summation(sheet, table_type, row_index, sum_column_index):
    """Calculate summation for a row, with special handling for % row"""
   
    try:
        # Special case: top_left, % row (Excel B20 -> row_index 19)
        if table_type == 'top_left' and row_index == 19:
            # K13 = sum of row 13 (Excel B13 -> row_index 12) across all clients
            cells_row13 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                row_index=12,  # Excel B13 = row_index 12
                column_index__lt=sum_column_index  # Exclude sum column itself
            )
            total_13 = Decimal('0')
            for cell in cells_row13:
                if cell.value is not None:
                    total_13 += Decimal(str(cell.value))
            
            # K19 = sum of row 19 (Excel B19 -> row_index 18) across all clients
            cells_row19 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                row_index=18,  # Excel B19 = row_index 18
                column_index__lt=sum_column_index
            )
            total_19 = Decimal('0')
            for cell in cells_row19:
                if cell.value is not None:
                    total_19 += Decimal(str(cell.value))
            
            # Calculate: IF(K13=0; 0; K19/K13)
            if total_13 == 0:
                return Decimal('0')
            return total_19 / total_13
        
        # Normal summation for other rows
        cells_in_row = Cell.objects.filter(
            sheet=sheet,
            table_type=table_type,
            row_index=row_index,
            column_index__lt=sum_column_index
        )
        
        total = Decimal('0')
        for cell in cells_in_row:
            if cell.value is not None:
                total += Decimal(str(cell.value))
        
        return total
        
    except Exception as e:
        print(f"Error calculating summation for {table_type}[{row_index}]: {e}")
        return None

# Helper function for calculations
def evaluate_formula(formula, values_dict):
    """
    Safely evaluate a formula like "10 + 9" where numbers are row indices
    values_dict: {row_index: decimal_value}
    """
   
    try:
        # Create a copy of the formula to work with
        expr = formula
        
        # Find all row numbers in the formula
        row_refs = re.findall(r'\b\d+\b', expr)
        
        for row_ref in row_refs:
            row_num = int(row_ref)
            if row_num in values_dict and values_dict[row_num] is not None:
                # Replace row reference with actual value
                expr = expr.replace(row_ref, str(values_dict[row_num]))
            else:
                # Missing value - can't calculate
                return None
        
        # Evaluate the expression
        # Note: In production, use a safer evaluator like `asteval`
        result = eval(expr, {"__builtins__": {}}, {})
        
        # Convert to Decimal with proper rounding
        return Decimal(str(round(result, 6)))
        
    except Exception:
        return None
        
# Monthly Sheet View
class MonthlySheetView(TemplateView):
    template_name = 'monthly_sheet.html'
    
    def populate_helium_input_to_top_right(self, sheet):
        """Populate bezug data from SecondTableEntry to top-right table (row 8 = Excel row 12)"""
       
        
        year = sheet.year
        month = sheet.month
        
        TOP_RIGHT_CLIENTS = [
            "Dr. Fohrer",      # Column index 0 (L)
            "AG Buntk.",       # Column index 1 (M)  
            "AG Alff",         # Column index 2 (N)
            "AG Gutfl.",       # Column index 3 (O)
            "M3 Thiele",       # Column index 4 (P)
            "M3 Buntkowsky",   # Column index 5 (Q)
            "M3 Gutfleisch",   # Column index 6 (R)
            "Merck",
        ]
        
        # For each client in top-right table
        for client_name in TOP_RIGHT_CLIENTS:
            try:
                client = Client.objects.get(name=client_name)
                column_index = TOP_RIGHT_CLIENTS.index(client_name)
                
                # Calculate total LHe_output for this client in this month from SecondTableEntry
                total_lhe_output = SecondTableEntry.objects.filter(
                    client=client,
                    date__year=year,
                    date__month=month
                ).aggregate(
                    total=Coalesce(Sum('lhe_output'), Decimal('0'))
                )['total']
                
                # Get or create the cell for row_index 8 (Excel row 12) - Bezug
                cell, created = Cell.objects.get_or_create(
                    sheet=sheet,
                    table_type='top_right',
                    client=client,
                    row_index=8,  # Bezug row (Excel row 12)
                    column_index=column_index,
                    defaults={'value': total_lhe_output}
                )
                
                if not created and cell.value != total_lhe_output:
                    cell.value = total_lhe_output
                    cell.save()
                    
            except Client.DoesNotExist:
                continue
        
        # After populating bezug, trigger calculation for all dependent cells
        # Get any cell to start the calculation
        first_cell = Cell.objects.filter(
            sheet=sheet,
            table_type='top_right'
        ).first()
        
        if first_cell:
            save_view = SaveCellsView()
            save_view.calculate_top_right_dependents(sheet, first_cell)
        
        return True
    def calculate_bezug_from_entries(self, sheet, year, month):
        """Calculate B11 (Bezug) from SecondTableEntry for all clients - ONLY for non-start sheets"""
    
        
        # Check if this is the start sheet
        if year == 2025 and month == 1:
            return  # Don't auto-calculate for start sheet
        
        for client in Client.objects.all():
            # Calculate total LHe output for this client in this month
            lhe_output_sum = SecondTableEntry.objects.filter(
                client=client,
                date__year=year,
                date__month=month
            ).aggregate(
                total=Coalesce(Sum('lhe_output'), Decimal('0'))
            )['total']
            
            # Update B11 cell (row_index 8 = UI Row 9)
            b11_cell = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                client=client,
                row_index=8  # Excel B11
            ).first()
            
            if b11_cell and (b11_cell.value != lhe_output_sum or b11_cell.value is None):
                b11_cell.value = lhe_output_sum
                b11_cell.save()
                
                # Also trigger dependent calculations
                
                save_view = SaveCellsView()
                save_view.calculate_top_left_dependents(sheet, b11_cell)
    # In MonthlySheetView.get_context_data() method, update the TOP_RIGHT_CLIENTS and row count:
    

        
        return True
    def get_context_data(self, **kwargs):
        
        context = super().get_context_data(**kwargs)
        year = self.kwargs.get('year', datetime.now().year)
        month = self.kwargs.get('month', datetime.now().month)
        is_start_sheet = (year == FIRST_SHEET_YEAR and month == FIRST_SHEET_MONTH)
        
        # Get or create the monthly sheet
        sheet, created = MonthlySheet.objects.get_or_create(
            year=year, month=month
        )

        
        # All clients (used for bottom tables etc.)
        clients = Client.objects.all().order_by('name')
        
        # Pre-fill cells if creating new sheet
        if created:
            self.initialize_sheet_cells(sheet, clients)
        
        # Apply previous month links (for B4 and B12)
        self.apply_previous_month_links(sheet, year, month)
        self.calculate_bezug_from_entries(sheet, year, month)
        self.populate_helium_input_to_top_right(sheet)
        self.apply_previous_month_links_top_right(sheet, year, month)
        
        # ----------------------------------------------------
        # Recalculate dependents once when opening the sheet
        # ----------------------------------------------------

       
        save_view = SaveCellsView()

        # ---- TOP LEFT ----
        TOP_LEFT_CLIENTS = ["AG Vogel", "AG Halfm", "IKP"]

        for cname in TOP_LEFT_CLIENTS:
            trigger_left = Cell.objects.filter(
                sheet=sheet,
                table_type="top_left",
                client__name=cname,
                row_index=0,   # any stable row works as a trigger
            ).first()

            if trigger_left:
                save_view.calculate_top_left_dependents(sheet, trigger_left)

        # ---- TOP RIGHT ----
        trigger_right = Cell.objects.filter(
            sheet=sheet,
            table_type="top_right"
        ).first()

        if trigger_right:
            save_view.calculate_top_right_dependents(sheet, trigger_right)
        # Define client groups
        TOP_LEFT_CLIENTS = [
            "AG Vogel",
            "AG Halfm",
            "IKP",
        ]
        
        TOP_RIGHT_CLIENTS = [
            "Dr. Fohrer",
            "AG Buntk.",
            "AG Alff",
            "AG Gutfl.",
            "M3 Thiele",
            "M3 Buntkowsky",
            "M3 Gutfleisch",
            "Merck",
        ]
        current_summary = MonthlySummary.objects.filter(sheet=sheet).first()

        # Get previous month summary (for Bottom Table 3: K46 = prev K44)
        prev_month_info = self.get_prev_month(year, month)
        prev_summary = None
        if not is_start_sheet:
            prev_sheet = MonthlySheet.objects.filter(
                year=prev_month_info['year'],
                month=prev_month_info['month']
            ).first()
            if prev_sheet:
                prev_summary = MonthlySummary.objects.filter(sheet=prev_sheet).first()

        context.update({
            # ... your existing context ...
            'current_summary': current_summary,
            'prev_summary': prev_summary,
        })
        # Update row counts in build_group_rows function
                # Update row counts in build_group_rows function
        def build_group_rows(sheet, table_type, client_names):
            """Build rows for display in monthly sheet."""
            
            MERGED_ROWS = {2, 3, 5, 6, 7, 9, 10, 12, 14, 15}
            MERGED_PAIRS = [
                ("Dr. Fohrer", "AG Buntk."),
                ("AG Alff", "AG Gutfl."),
            ]

            MERGED_TRIPLES = [
                ("M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"),
            ]   
            rows = []

            # Determine row count
            row_counts = {
                "top_left": 16,
                "top_right": 16,  # rows 0–15
                "bottom_1": 10,
                "bottom_2": 10,
                "bottom_3": 10,
            }
            row_count = row_counts.get(table_type, 0)

            # Get all cells for this sheet and table
            all_cells = (
                Cell.objects.filter(
                    sheet=sheet,
                    table_type=table_type,
                ).select_related("client")
            )

            # Group cells by row index
            cells_by_row = {}
            for cell in all_cells:
                if cell.row_index not in cells_by_row:
                    cells_by_row[cell.row_index] = {}
                cells_by_row[cell.row_index][cell.client.name] = cell

            # We will store row sums for Bezug (row 8) and Verbraucherverluste (row 14)
            # for top_left / top_right so we can compute the overall % in row 15.
            sum_bezug = None
            sum_verbrauch = None

            # Build each row
            for row_idx in range(row_count):
                display_cells = []
                row_cells_dict = cells_by_row.get(row_idx, {})

                # Build cells in the requested client order
                for name in client_names:
                    cell = row_cells_dict.get(name)
                    display_cells.append(cell)

                # Calculate sum for this row (includes editable + calculated cells)
                sum_value = None
                total = Decimal("0")
                has_value = False

                merged_second_indices = set()

                if table_type == 'top_right' and row_idx in MERGED_ROWS:

                    # Handle pairs
                    for left_name, right_name in MERGED_PAIRS:
                        try:
                            right_idx = client_names.index(right_name)
                            merged_second_indices.add(right_idx)
                        except ValueError:
                            pass

                    # Handle M3 triple group
                    MERGED_TRIPLES = [
                        ("M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"),
                    ]

                    for a, b, c in MERGED_TRIPLES:
                        try:
                            b_idx = client_names.index(b)
                            c_idx = client_names.index(c)
                            merged_second_indices.add(b_idx)
                            merged_second_indices.add(c_idx)
                        except ValueError:
                            pass

                for col_idx, cell in enumerate(display_cells):
                    # Skip the duplicate (second) column of each merged pair
                    if col_idx in merged_second_indices:
                        continue

                    if cell and cell.value is not None:
                        try:
                            total += Decimal(str(cell.value))
                            has_value = True
                        except Exception:
                            pass

                if has_value:
                    sum_value = total

                    # Remember special rows for top tables
                    if table_type in ("top_left", "top_right"):
                        if row_idx == 8:      # Bezug
                            sum_bezug = total
                        elif row_idx == 14:   # Verbraucherverluste
                            sum_verbrauch = total

                rows.append(
                    {
                        "cells": display_cells,
                        "sum": sum_value,
                        "row_index": row_idx,
                    }
                )

            # Adjust the % row sum for top_left / top_right:
            # Sum(%) = Sum(Verbraucherverluste) / Sum(Bezug)
            if table_type in ("top_left", "top_right"):
                perc_row_idx = 15  # % row
                if 0 <= perc_row_idx < len(rows):
                    if sum_bezug is not None and sum_bezug != 0 and sum_verbrauch is not None:
                        rows[perc_row_idx]["sum"] = sum_verbrauch / sum_bezug
                    else:
                        rows[perc_row_idx]["sum"] = None

            return rows

        
        # Now call the local function
        top_left_rows = build_group_rows(sheet, 'top_left', TOP_LEFT_CLIENTS)
        top_right_rows = build_group_rows(sheet, 'top_right', TOP_RIGHT_CLIENTS)

        # --- Build combined summary of top-left + top-right Sum columns ---

        # Helper to safely get the Sum for a given row index
        def get_row_sum(rows, row_index):
            if 0 <= row_index < len(rows):
                return rows[row_index].get('sum')
            return None

        # Row definitions we want in the combined Σ table
        # (row_index in top tables, label shown in the small table)
        summary_row_defs = [
            (2,  "Rückführung flüssig (Lit. L-He)"),
            (3,  "Sonderrückführungen (Lit. L-He)"),
            (4,  "Sammelrückführungen (Lit. L-He)"),
            (5,  "Bestand in Kannen-1 (Lit. L-He)"),
            (6,  "Summe Bestand (Lit. L-He)"),
            (7,  "Best. in Kannen Vormonat (Lit. L-He)"),
            (8,  "Bezug (Liter L-He)"),
            (9,  "Rückführ. Soll (Lit. L-He)"),
            (10, "Verluste (Soll-Rückf.) (Lit. L-He)"),
            (11, "Füllungen warm (Lit. L-He)"),
            (12, "Kaltgas Rückgabe (Lit. L-He) – Faktor"),
            (13, "Faktor 0.06"),
            (14, "Verbraucherverluste (Liter L-He)"),
            (15, "%"),
        ]

        # Precompute totals for Bezug and Verbraucherverluste across both tables
        bezug_left = get_row_sum(top_left_rows, 8) or Decimal('0')
        bezug_right = get_row_sum(top_right_rows, 8) or Decimal('0')
        total_bezug = bezug_left + bezug_right

        verb_left = get_row_sum(top_left_rows, 14) or Decimal('0')
        verb_right = get_row_sum(top_right_rows, 14) or Decimal('0')
        total_verbrauch = verb_left + verb_right

        summary_rows = []

        for row_index, label in summary_row_defs:
            # Faktor row: always fixed 0.06
            if row_index == 13:
                summary_value = Decimal('0.06')

            # % row: total Verbraucherverluste / total Bezug
            elif row_index == 15:
                if total_bezug != 0:
                    summary_value = total_verbrauch / total_bezug
                else:
                    summary_value = None

            else:
                left_sum = get_row_sum(top_left_rows, row_index)
                right_sum = get_row_sum(top_right_rows, row_index)

                # Sammelrückführungen: only from top-right table
                if row_index == 4:
                    left_sum = None

                total = Decimal('0')

                has_any = False
                if left_sum is not None:
                    total += Decimal(str(left_sum))
                    has_any = True
                if right_sum is not None:
                    total += Decimal(str(right_sum))
                    has_any = True

                summary_value = total if has_any else None

            summary_rows.append({
                'row_index': row_index,
                'label': label,
                'sum': summary_value,
            })

        # Get cells for bottom tables
        cells_by_table = self.get_cells_by_table(sheet)

        context.update({
            'sheet': sheet,
            'clients': clients,
            'year': year,
            'month': month,
            'month_name': calendar.month_name[month],
            'prev_month': self.get_prev_month(year, month),
            'next_month': self.get_next_month(year, month),
            'cells_by_table': cells_by_table,
            'top_left_headers': TOP_LEFT_CLIENTS + ['Sum'],
            'top_right_headers': TOP_RIGHT_CLIENTS + ['Sum'],
            'top_left_rows': top_left_rows,
            'top_right_rows': top_right_rows,
            'summary_rows': summary_rows,          # 👈 NEW
            'is_start_sheet': is_start_sheet,
        })
        return context
    
    def get_cells_by_table(self, sheet):
        """Organize cells by table type for easy template rendering"""
        cells = sheet.cells.select_related('client').all()
        organized = {
                'top_left': [[] for _ in range(16)],
                'top_right': [[] for _ in range(16)],  # now 16 rows
                'bottom_1': [[] for _ in range(10)],
                'bottom_2': [[] for _ in range(10)],
                'bottom_3': [[] for _ in range(10)],
            }
        
        for cell in cells:
            if cell.table_type not in organized:
                continue

            max_rows = len(organized[cell.table_type])
            if cell.row_index < 0 or cell.row_index >= max_rows:
                # This is an "extra" cell from an older layout (e.g. top_left rows 18–23)
                continue

            row_list = organized[cell.table_type][cell.row_index]
            while len(row_list) <= cell.column_index:
                row_list.append(None)

            row_list[cell.column_index] = cell
        
        return organized

    
    def initialize_sheet_cells(self, sheet, clients):
        """Create all empty cells for a new monthly sheet"""
        cells_to_create = []
        summation_config = CALCULATION_CONFIG.get('summation_column', {})
        sum_column_index = summation_config.get('sum_column_index', len(clients) - 1)  # Last column
        
        # For each table type and row
        table_configs = [
            ('top_left', 16),
            ('top_right', 16),
            ('bottom_1', 10),
            ('bottom_2', 10),
            ('bottom_3', 10),
        ]
        
        for table_type, row_count in table_configs:
            for row_idx in range(row_count):
                for col_idx, client in enumerate(clients):
                    is_summation = (col_idx == sum_column_index)
                    cells_to_create.append(Cell(
                        sheet=sheet,
                        client=client,
                        table_type=table_type,
                        row_index=row_idx,
                        column_index=col_idx,
                        value=None,
                        is_formula=is_summation,  # Mark summation cells as formulas
                    ))
        
        # Bulk create all cells at once
        Cell.objects.bulk_create(cells_to_create)
    def get_prev_month(self, year, month):
        """Get previous month year and month"""
        if month == 1:
            return {'year': year - 1, 'month': 12}
        return {'year': year, 'month': month - 1}
    
    def get_next_month(self, year, month):
        """Get next month year and month"""
        if month == 12:
            return {'year': year + 1, 'month': 1}
        return {'year': year, 'month': month + 1}
    def apply_previous_month_links(self, sheet, year, month):
        """
        For non-start sheets:
        B4 (row 2) = previous sheet B3 (row 1)
        B10 (row 8) = previous sheet B9 (row 7)
        """
        # Do nothing on the first sheet
        if year == FIRST_SHEET_YEAR and month == FIRST_SHEET_MONTH:
            return

        # Figure out previous month
        if month == 1:
            prev_year = year - 1
            prev_month = 12
        else:
            prev_year = year
            prev_month = month - 1

    

        prev_sheet = MonthlySheet.objects.filter(
            year=prev_year,
            month=prev_month
        ).first()

        if not prev_sheet:
            # No previous sheet created yet → nothing to copy
            return

        # For each client, copy values
        for client in Client.objects.all():
            # B3(prev) → B4(curr): UI row 1 → row 2 → row_index 0 → 1
            prev_b3 = Cell.objects.filter(
                sheet=prev_sheet,
                table_type='top_left',
                client=client,
                row_index=0,   # UI row 1
            ).first()

            curr_b4 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                client=client,
                row_index=1,   # UI row 2
            ).first()

            if prev_b3 and curr_b4:
                curr_b4.value = prev_b3.value
                curr_b4.save()
            
            # B9(prev) → B10(curr): UI row 7 → row 8 → row_index 6 → 7
            prev_b9 = Cell.objects.filter(
                sheet=prev_sheet,
                table_type='top_left',
                client=client,
                row_index=6,  # UI row 7 (Summe Bestand)
            ).first()

            curr_b10 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                client=client,
                row_index=7,  # UI row 8 (Best. in Kannen Vormonat)
            ).first()

            if prev_b9 and curr_b10:
                curr_b10.value = prev_b9.value
                curr_b10.save()

    def apply_previous_month_links_top_right(self, sheet, year, month):
        """
        top_right row 7: Best. in Kannen Vormonat (Lit. L-He)
        = previous sheet's Summe Bestand (row 6).
        
        For merged pairs:
        - Dr. Fohrer + AG Buntk. share the SAME value (from previous month's AG Buntk. or Dr. Fohrer)
        - AG Alff + AG Gutfl. share the SAME value (from previous month's AG Alff or AG Gutfl.)
        M3 clients just copy their own value.
        """
    

        # Do nothing on first sheet
        if year == FIRST_SHEET_YEAR and month == FIRST_SHEET_MONTH:
            return

        # find previous month
        if month == 1:
            prev_year = year - 1
            prev_month = 12
        else:
            prev_year = year
            prev_month = month - 1

        prev_sheet = MonthlySheet.objects.filter(
            year=prev_year,
            month=prev_month
        ).first()
        
        if not prev_sheet:
            return  # nothing to copy from

        TOP_RIGHT_CLIENTS = [
            "Dr. Fohrer",
            "AG Buntk.",
            "AG Alff",
            "AG Gutfl.",
            "M3 Thiele",
            "M3 Buntkowsky",
            "M3 Gutfleisch",
        ]

        # Helper function to get a cell value
        def get_cell_value(sheet_obj, client_name, row_index):
            client_obj = Client.objects.filter(name=client_name).first()
            if not client_obj:
                return None
            
            try:
                col_idx = TOP_RIGHT_CLIENTS.index(client_name)
            except ValueError:
                return None
            
            cell = Cell.objects.filter(
                sheet=sheet_obj,
                table_type='top_right',
                client=client_obj,
                row_index=row_index,
                column_index=col_idx,
            ).first()
            
            return cell.value if cell else None

        # Helper function to set a cell value
        def set_cell_value(sheet_obj, client_name, row_index, value):
            client_obj = Client.objects.filter(name=client_name).first()
            if not client_obj:
                return False
            
            try:
                col_idx = TOP_RIGHT_CLIENTS.index(client_name)
            except ValueError:
                return False
            
            cell, created = Cell.objects.get_or_create(
                sheet=sheet_obj,
                table_type='top_right',
                client=client_obj,
                row_index=row_index,
                column_index=col_idx,
                defaults={'value': value}
            )
            
            if not created and cell.value != value:
                cell.value = value
                cell.save()
            elif created:
                cell.save()
            
            return True

        # ----- Pair 1: Dr. Fohrer + AG Buntk. -----
        # Get previous month's Summe Bestand (row 6) for either client in the pair
        pair1_prev_val = None
        
        # Try AG Buntk. first
        prev_buntk_val = get_cell_value(prev_sheet, "AG Buntk.", 6)
        if prev_buntk_val is not None:
            pair1_prev_val = prev_buntk_val
        else:
            # Try Dr. Fohrer if AG Buntk. is empty
            prev_fohrer_val = get_cell_value(prev_sheet, "Dr. Fohrer", 6)
            if prev_fohrer_val is not None:
                pair1_prev_val = prev_fohrer_val
        
        # Apply the value to both clients in the pair
        if pair1_prev_val is not None:
            set_cell_value(sheet, "Dr. Fohrer", 7, pair1_prev_val)
            set_cell_value(sheet, "AG Buntk.", 7, pair1_prev_val)

        # ----- Pair 2: AG Alff + AG Gutfl. -----
        pair2_prev_val = None
        
        # Try AG Alff first
        prev_alff_val = get_cell_value(prev_sheet, "AG Alff", 6)
        if prev_alff_val is not None:
            pair2_prev_val = prev_alff_val
        else:
            # Try AG Gutfl. if AG Alff is empty
            prev_gutfl_val = get_cell_value(prev_sheet, "AG Gutfl.", 6)
            if prev_gutfl_val is not None:
                pair2_prev_val = prev_gutfl_val
        
        # Apply the value to both clients in the pair
        if pair2_prev_val is not None:
            set_cell_value(sheet, "AG Alff", 7, pair2_prev_val)
            set_cell_value(sheet, "AG Gutfl.", 7, pair2_prev_val)

        # ----- M3 clients: copy their own Summe Bestand -----
        for name in ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"]:
            prev_val = get_cell_value(prev_sheet, name, 6)
            if prev_val is not None:
                set_cell_value(sheet, name, 7, prev_val)
# Add this helper function to views.py
def get_factor_value(table_type, row_index):
    """Get factor value (like 0.06 for top_left row 17)"""
    factors = {
        ('top_left', 17): Decimal('0.06'),  # A18 in Excel (UI row 17, 0-based index 16)
    }
    return factors.get((table_type, row_index), Decimal('0'))
# Save Cells View
# views.py - Updated SaveCellsView
# views.py - Update SaveCellsView class


def recalculate_stand_der_gaszahler(self, sheet):
    """Recalculate Stand der Gaszähler for all client pairs"""
   
    
    # For Dr. Fohrer and AG Buntk. (L & M columns)
    try:
        # Get Dr. Fohrer's bezug
        dr_fohrer_client = Client.objects.get(name="Dr. Fohrer")
        dr_fohrer_cell = Cell.objects.filter(
            sheet=sheet,
            table_type='top_right',
            client=dr_fohrer_client,
            row_index=9  # Row 9 = Bezug
        ).first()
        L13 = Decimal(str(dr_fohrer_cell.value)) if dr_fohrer_cell and dr_fohrer_cell.value else Decimal('0')
        
        # Get AG Buntk.'s bezug
        ag_buntk_client = Client.objects.get(name="AG Buntk.")
        ag_buntk_cell = Cell.objects.filter(
            sheet=sheet,
            table_type='top_right',
            client=ag_buntk_client,
            row_index=9
        ).first()
        M13 = Decimal(str(ag_buntk_cell.value)) if ag_buntk_cell and ag_buntk_cell.value else Decimal('0')
        
        total = L13 + M13
        if total > 0:
            # Update Dr. Fohrer's row 0
            dr_fohrer_row0 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_right',
                client=dr_fohrer_client,
                row_index=0
            ).first()
            if dr_fohrer_row0:
                dr_fohrer_row0.value = L13 / total
                dr_fohrer_row0.save()
            
            # Update AG Buntk.'s row 0
            ag_buntk_row0 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_right',
                client=ag_buntk_client,
                row_index=0
            ).first()
            if ag_buntk_row0:
                ag_buntk_row0.value = M13 / total
                ag_buntk_row0.save()
    except Exception as e:
        print(f"Error recalculating Stand der Gaszähler for Dr. Fohrer/AG Buntk.: {e}")
    
    # For AG Alff and AG Gutfl. (N & O columns)
    try:
        # Get AG Alff's bezug
        ag_alff_client = Client.objects.get(name="AG Alff")
        ag_alff_cell = Cell.objects.filter(
            sheet=sheet,
            table_type='top_right',
            client=ag_alff_client,
            row_index=9
        ).first()
        N13 = Decimal(str(ag_alff_cell.value)) if ag_alff_cell and ag_alff_cell.value else Decimal('0')
        
        # Get AG Gutfl.'s bezug
        ag_gutfl_client = Client.objects.get(name="AG Gutfl.")
        ag_gutfl_cell = Cell.objects.filter(
            sheet=sheet,
            table_type='top_right',
            client=ag_gutfl_client,
            row_index=9
        ).first()
        O13 = Decimal(str(ag_gutfl_cell.value)) if ag_gutfl_cell and ag_gutfl_cell.value else Decimal('0')
        
        total = N13 + O13
        if total > 0:
            # Update AG Alff's row 0
            ag_alff_row0 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_right',
                client=ag_alff_client,
                row_index=0
            ).first()
            if ag_alff_row0:
                ag_alff_row0.value = N13 / total
                ag_alff_row0.save()
            
            # Update AG Gutfl.'s row 0
            ag_gutfl_row0 = Cell.objects.filter(
                sheet=sheet,
                table_type='top_right',
                client=ag_gutfl_client,
                row_index=0
            ).first()
            if ag_gutfl_row0:
                ag_gutfl_row0.value = O13 / total
                ag_gutfl_row0.save()
    except Exception as e:
        print(f"Error recalculating Stand der Gaszähler for AG Alff/AG Gutfl.: {e}")

ABRECHNUNG_COL_CLIENTS = {
    # first 3 columns
    "pkm_vogel": ["AG Vogel"],
    "iap_halfmann": ["AG Halfm"],   # or whatever exact name you use in Client admin
    "ikp": ["IKP"],

    # your “5 columns” group (excluding Chemie/MaWi/Physik summaries)
    "orgchem_thiele": ["M3 Thiele"],  # if OrgChem Thiele corresponds to M3 Thiele in your DB
    "phychem_m3_buntkow": ["AG Buntk.", "M3 Buntkowsky"],
    "orgchem_fohrer": ["Dr. Fohrer"],
    "mawi_m3_gutfl": ["AG Gutfl.", "M3 Gutfleisch"],
    "mawi_alff": ["AG Alff"],

    # summary columns at the end
    "chemie": ["Dr. Fohrer", "AG Buntk.", "M3 Buntkowsky", "M3 Thiele"],
    "mawi": ["AG Alff", "AG Gutfl.", "M3 Gutfleisch"],
    "physik": ["AG Vogel", "AG Halfm", "IKP"],

    # “Gesamt-summe” is computed as sum of all columns (not client-driven)
    "gesamt_summe": [],
}

class AbrechnungView(TemplateView):


    template_name = "abrechnung.html"

    # Columns EXACTLY from your screenshot
    COLUMNS = [
        ("pkm_vogel", "PKM Vogel"),
        ("iap_halfmann", "IAP Halfmann"),
        ("ikp", "IKP"),
        ("orgchem_thiele", "OrgChem Thiele"),
        ("phychem_m3_buntkow", "PhyChem + M3 Buntkow"),
        ("orgchem_fohrer", "OrgChem Fohrer"),
        ("mawi_m3_gutfl", "MaWi + M3 Gutfl"),
        ("mawi_alff", "MaWi Alff"),
        ("chemie", "Chemie"),
        ("mawi", "MaWi"),
        ("physik", "Physik"),
        ("gesamt_summe", "Gesamt-summe"),
     ]

    # Rows EXACTLY from your screenshot (Excel row 11 is skipped)
    ROWS = [
    ("bezogen_menge", "Bezogen. Menge", "LHe"),
    ("kaltg_warmfuell", "Kaltg. Warmfüll.", "Anzahl"),
    ("anzahl_15", "Anzahl", "LHe"),
    ("he_verbrauch", "He-Verbrauch (LHe)", "LHe"),
    ("umlage_instandhaltung_1", "1-Umlage Instandhaltung", "EUR"),
    ("lhe_verluste", "LHe – Verluste", "LHe"),
    ("umlage_heliumkosten_3", "3 Umlage Heliumkosten", "EUR"),
    ("ghe_bezug", "GHe - Bezug", "m³"),
    ("kosten_he_gas_bezug_4", "4-Kosten He-Gas-Bezug", "EUR"),
    ("rel_anteil", "rel. Anteil", "Anteil"),
    ("summe_anteile_1_4", "Summe Anteile 1-4", "EUR"),
    ("sonstiges", "Sonstiges", "EUR"),
    ("betrag", "Betrag", "EUR"),
    ("umlage_personal_5", "5-Umlage Personal", "EUR"),
    ("gutschriften", "Gutschriften", "EUR"),
    ("rechnungsbetrag", "Rechnungsbetrag", "EUR"),
    ("eff_lhe_preis", "eff. L-He-Preis", "EUR/L"),
]
    SUMMARY_ROWS = {
        "bezogen_menge",
        "kaltg_warmfuell",
        "he_verbrauch",
        "lhe_verluste",
    }

    # Editable rows = your yellow rows
    EDITABLE_ROW_KEYS = {"ghe_bezug", "betrag", "gutschriften"}

    def get_context_data(self, **kwargs):
      
        context = super().get_context_data(**kwargs)

        interval_year = self.request.session.get("halfyear_year")
        interval_start = self.request.session.get("halfyear_start_month")
        if not interval_year or not interval_start:
            context["needs_interval"] = True
            return context

        interval_year = int(interval_year)
        interval_start = int(interval_start)

        qs = AbrechnungCell.objects.filter(
            interval_year=interval_year,
            interval_start_month=interval_start,
        )
        value_map = {(c.row_key, c.col_key): c.value for c in qs}
        def d(x):
            """safe Decimal"""
            if x in (None, ""):
                return Decimal("0")
            try:
                return Decimal(str(x).replace(",", "."))
            except Exception:
                return Decimal("0")

        def safe_div(a: Decimal, b: Decimal):
            return (a / b) if b not in (None, 0, Decimal("0")) else Decimal("0")

        # ---- interval + window ----
        window = build_halfyear_window(interval_year, interval_start)

        # ---- BetriebskostenSummary values ----
        bs = BetriebskostenSummary.objects.first()
        instandhaltung = d(bs.instandhaltung) if bs else Decimal("0")
        heliumkosten = d(bs.heliumkosten) if bs else Decimal("0")
        bezugskosten_gashe = d(bs.bezugskosten_gashe) if bs else Decimal("0")
        umlage_personal_total = d(bs.umlage_personal) if bs else Decimal("0")

        # ---- helper: sum lhe_output in the 6-month window for a list of client names ----
        def sum_output_for_clients_exact(client_names):
            total = Decimal("0")
            if not client_names:
                return total
            for (y, m) in window:
                total += SecondTableEntry.objects.filter(
                    client__name__in=client_names,
                    date__year=y,
                    date__month=m,
                ).aggregate(
                    total=Coalesce(Sum("lhe_output"), Value(0, output_field=DecimalField()))
                )["total"] or Decimal("0")
            return total

        # Use exact to avoid edge cases
        sum_output_for_clients = sum_output_for_clients_exact

        # ---- helper: warm fillings (from Halbjahresbilanz row “Füllungen warm”) ----
        # In your halfyear_balance logic, “Füllungen warm” is summed from MonthlySheet cells row_index=11
        # We replicate that part by reading stored top_left/top_right cells for each month.
        def sum_fuellungen_warm(client_name):
            total = Decimal("0")
            for (y, m) in window:
                sheet = MonthlySheet.objects.filter(year=y, month=m).first()
                if not sheet:
                    continue
                # warm row_index = 11 in both top_left and top_right tables in your existing code
                # Try top_left first, then top_right
                c = Cell.objects.filter(sheet=sheet, client__name=client_name, table_type="top_left", row_index=11).first()
                if not c:
                    c = Cell.objects.filter(sheet=sheet, client__name=client_name, table_type="top_right", row_index=11).first()
                total += d(c.value if c else None)
            return total

        # ---- helper: verbraucherverluste (from Halbjahresbilanz) ----
        # In halfyear_balance view this is a *computed* metric, not directly stored.
        # So we must either:
        #   A) replicate the whole halfyear_balance math (big), OR
        #   B) read it from stored cells *if you store it*.
        #
        # Right now your MonthlySheet stores the table rows, but “Verbraucherverluste” is row_index=15
        # in your debug mapping. If those cells are saved in DB (they usually are), we can sum row_index=15.
    

        # ----------------------------------------------------------------------
        # Build computed row values per column
        # ----------------------------------------------------------------------
        computed = {}  # (row_key, col_key) -> Decimal OR special string

        # Gather base rows (bezug + warm fills) per column
        bezogen = {}
        warmfills = {}

        for col_key, _label in self.COLUMNS:
            client_names = ABRECHNUNG_COL_CLIENTS.get(col_key, [])
            bezogen[col_key] = sum_output_for_clients(client_names)
            warmfills[col_key] = sum_fuellungen_warm(client_names[0]) if len(client_names) == 1 else sum(sum_fuellungen_warm(n) for n in client_names)

        # “Gesamt-summe” = sum of all “normal” columns (everything except itself)
        def sum_all_cols(values_dict):
            return sum(v for k, v in values_dict.items() if k != "gesamt_summe")

        bezogen["gesamt_summe"] = (
            d(bezogen.get("chemie")) + d(bezogen.get("mawi")) + d(bezogen.get("physik"))
        )
        warmfills["gesamt_summe"] = (
            d(warmfills.get("chemie")) + d(warmfills.get("mawi")) + d(warmfills.get("physik"))
        )
        # ---- Row: Bezogen. Menge ----
        for col_key in bezogen:
            computed[("bezogen_menge", col_key)] = bezogen[col_key]

        # ---- Row: Kaltg. Warmfüll. (á 15l L-He) ----
        for col_key in warmfills:
            computed[("kaltg_warmfuell", col_key)] = warmfills[col_key]

        # ---- Row: Anzahl (15) = warmfills * 15 ----
        for col_key in warmfills:
            computed[("anzahl_15", col_key)] = warmfills[col_key] * Decimal("15")
        he_verbrauch = {}

        for col_key in bezogen:
            hv = d(bezogen.get(col_key)) + d(computed.get(("anzahl_15", col_key)))
            he_verbrauch[col_key] = hv
            computed[("he_verbrauch", col_key)] = hv

        # ---- Row: He-Verbrauch (LHe) = Bezogen + Anzahl_15 ----
        half_ctx = compute_halfyear_context(interval_year, interval_start)

        # Extract Verbraucherverluste row from LEFT table
        verbrauch_left = {}
        for row in half_ctx["rows_left"]:
            if row["label"] == "Verbraucherverluste (Liter L-He)":
                for client, value in zip(half_ctx["clients_left"], row["values"]):
                    verbrauch_left[client] = value
                break

        # Extract Verbraucherverluste row from RIGHT table
        verbrauch_right = {}
        for row in half_ctx["rows_right"]:
            if row["label"] == "Verbraucherverluste (Liter L-He)":
                for client, value in zip(half_ctx["clients_right"], row["values"]):
                    verbrauch_right[client] = value
                break

        # ---- Row: 1-Umlage Instandhaltung ----
        # Your rule (as you stated):
        #   - first 3 columns: instandhaltung * (He-Verbrauch of this column / He-Verbrauch of “Physik”) / 3
        #   - next 3 columns: instandhaltung / 9
        #   - next 2 columns: instandhaltung / 6
        #   - next 2 columns: instandhaltung / 3   (if you later add more)
        #   - gesamt_summe: sum of first three columns (same row)
        #
        # We apply this to the current UI order.
        first3 = ["pkm_vogel", "iap_halfmann", "ikp"]
        next3 = ["orgchem_thiele", "phychem_m3_buntkow", "orgchem_fohrer"]
        next2 = ["mawi_m3_gutfl", "mawi_alff"]

        physik_hv = he_verbrauch.get("physik", Decimal("0"))

        row1_vals = {}
        for col_key in he_verbrauch:
            if col_key in first3:
                row1_vals[col_key] = instandhaltung * safe_div(he_verbrauch[col_key], physik_hv) / Decimal("3")

            elif col_key in next3:
                row1_vals[col_key] = instandhaltung / Decimal("9")

            elif col_key in next2:
                row1_vals[col_key] = instandhaltung / Decimal("6")

            # ✅ NEW: summary columns
            elif col_key in ("chemie", "mawi"):
                row1_vals[col_key] = instandhaltung / Decimal("3")

            elif col_key == "physik":
                row1_vals[col_key] = (
                    d(row1_vals.get("pkm_vogel")) +
                    d(row1_vals.get("iap_halfmann")) +
                    d(row1_vals.get("ikp"))
                )

            elif col_key == "gesamt_summe":
        # keep whatever you want here; example: chemie+mawi+physik
                row1_vals[col_key] = (
                    d(row1_vals.get("chemie")) + d(row1_vals.get("mawi")) + d(row1_vals.get("physik"))
                )

            else:
                row1_vals[col_key] = Decimal("0")

        for col_key, v in row1_vals.items():
            computed[("umlage_instandhaltung_1", col_key)] = v

        # ---- Row: LHe – Verluste = Verbraucherverluste from Halbjahresbilanz (sum if combined) ----
        # ---- Row: LHe – Verluste = Verbraucherverluste from Halbjahresbilanz ----
        lhe_verluste = {}

        for col_key, _label in self.COLUMNS:

            # --- LEFT clients ---
            if col_key == "pkm_vogel":
                v = d(verbrauch_left.get("AG Vogel"))

            elif col_key == "iap_halfmann":
                v = d(verbrauch_left.get("AG Halfm"))

            elif col_key == "ikp":
                v = d(verbrauch_left.get("IKP"))

            # --- RIGHT single clients ---
            elif col_key == "orgchem_thiele":
                v = d(verbrauch_right.get("M3 Thiele"))

            elif col_key == "orgchem_fohrer":
                v = d(verbrauch_right.get("Dr. Fohrer"))

            elif col_key == "mawi_alff":
                v = d(verbrauch_right.get("AG Alff"))

            # --- RIGHT combined columns ---
            elif col_key == "phychem_m3_buntkow":
                v = (
                    d(verbrauch_right.get("AG Buntk.")) +
                    d(verbrauch_right.get("M3 Buntkowsky"))
                )

            elif col_key == "mawi_m3_gutfl":
                v = (
                    d(verbrauch_right.get("AG Gutfl.")) +
                    d(verbrauch_right.get("M3 Gutfleisch"))
                )

            # --- Summary columns ---
            elif col_key == "chemie":
                v = (
                    d(verbrauch_right.get("Dr. Fohrer")) +
                    d(verbrauch_right.get("AG Buntk.")) 
                )

            elif col_key == "mawi":
                # MaWi = MaWi Gutfl + MaWi Alff (Abrechnung columns)
                v = (
                    d(lhe_verluste.get("AG Gutfl")) +
                    d(lhe_verluste.get("mawi_alff"))
                )

            elif col_key == "physik":
                            v = (
                    d(verbrauch_right.get("M3 Thiele")) +
                    d(verbrauch_right.get("M3 Buntkowsky")) +
                    d(verbrauch_right.get("M3 Gutfleisch"))
                )

            elif col_key == "gesamt_summe":
                # will compute after loop
                continue

            else:
                v = Decimal("0")

            lhe_verluste[col_key] = v
            computed[("lhe_verluste", col_key)] = v


        # --- Gesamt-summe ---
        lhe_verluste["gesamt_summe"] = (
            d(lhe_verluste.get("chemie")) + d(lhe_verluste.get("mawi")) + d(lhe_verluste.get("physik"))
        )
        computed[("lhe_verluste", "gesamt_summe")] = lhe_verluste["gesamt_summe"]


        # ---- Row: 3 Umlage Heliumkosten = heliumkosten * (LHe-Verluste col / LHe-Verluste gesamt_summe) ----
        FIRST8 = [
            "pkm_vogel",
            "iap_halfmann",
            "ikp",
            "orgchem_thiele",
            "phychem_m3_buntkow",
            "orgchem_fohrer",
            "mawi_m3_gutfl",
            "mawi_alff",
        ]

        den = sum(d(lhe_verluste.get(k)) for k in FIRST8)  # denominator = sum of first 8 LHe–Verluste

        # 1) compute first 8 columns
        for k in FIRST8:
            computed[("umlage_heliumkosten_3", k)] = heliumkosten * safe_div(d(lhe_verluste.get(k)), den)

        # 2) compute the 3 summary columns from those first 8 (so they stay consistent)
        # 2) compute the 3 summary columns directly from their LHe–Verluste shares (Excel logic)
        computed[("umlage_heliumkosten_3", "chemie")] = heliumkosten * safe_div(d(lhe_verluste.get("chemie")), den)
        computed[("umlage_heliumkosten_3", "mawi")]   = heliumkosten * safe_div(d(lhe_verluste.get("mawi")), den)
        computed[("umlage_heliumkosten_3", "physik")] = heliumkosten * safe_div(d(lhe_verluste.get("physik")), den)


        # 3) last column = sum of first 8 columns (your requirement)
        computed[("umlage_heliumkosten_3", "gesamt_summe")] = sum(
            d(computed.get(("umlage_heliumkosten_3", k))) for k in FIRST8
        )
        # ---- Row: 4-Kosten He-Gas-Bezug = Umlage Heliumkosten * Bezugskosten-GasHe ----
        for col_key, _label in self.COLUMNS:
            ghe = d(value_map.get(("ghe_bezug", col_key)))
            computed[("kosten_he_gas_bezug_4", col_key)] = ghe * bezugskosten_gashe
        # ---- Row: Summe Anteile 1-4 = (1) + (3) + (4) ----
        summe_anteile = {}
        for col_key in lhe_verluste:
            v = (
                d(computed.get(("umlage_instandhaltung_1", col_key))) +
                d(computed.get(("umlage_heliumkosten_3", col_key))) +
                d(computed.get(("kosten_he_gas_bezug_4", col_key)))
            )
            summe_anteile[col_key] = v
            computed[("summe_anteile_1_4", col_key)] = v

        # ---- Row: rel. Anteil = summe_anteile col / summe_anteile gesamt ----
        for col_key in summe_anteile:
            computed[("rel_anteil", col_key)] = safe_div(summe_anteile[col_key], summe_anteile.get("gesamt_summe", Decimal("0")))

        # ---- Row: 5-Umlage Personal ----
        # Your rules:
        # - first 3 clients => 0
        # - next columns up to (excluding Chemie): weighted over the 5 columns:
        #     OrgChem Thiele, PhyChem+M3 Buntkow, OrgChem Fohrer, MaWi+M3 Gutfl, MaWi Alff
        # - for Chemie/MaWi/Physik: weighted over the 3 summary columns (Chemie, MaWi, Physik)
        five_cols = ["orgchem_thiele", "phychem_m3_buntkow", "orgchem_fohrer", "mawi_m3_gutfl", "mawi_alff"]
        sum5 = sum(he_verbrauch.get(k, Decimal("0")) for k in five_cols)

        sum3 = (
            he_verbrauch.get("chemie", Decimal("0")) +
            he_verbrauch.get("mawi", Decimal("0")) +
            he_verbrauch.get("physik", Decimal("0"))
        )

        for col_key in he_verbrauch:
            if col_key in first3:
                v = Decimal("0")
            elif col_key in five_cols:
                v = safe_div(he_verbrauch[col_key], sum5) * umlage_personal_total
            elif col_key in ("chemie", "mawi", "physik"):
                v = safe_div(he_verbrauch[col_key], sum3) * umlage_personal_total
            elif col_key == "gesamt_summe":
    # sum of the FIRST 8 columns only (exclude chemie/mawi/physik/gesamt_summe)
                v = (
                    d(computed.get(("umlage_personal_5", "pkm_vogel"))) +
                    d(computed.get(("umlage_personal_5", "iap_halfmann"))) +
                    d(computed.get(("umlage_personal_5", "ikp"))) +
                    d(computed.get(("umlage_personal_5", "orgchem_thiele"))) +
                    d(computed.get(("umlage_personal_5", "phychem_m3_buntkow"))) +
                    d(computed.get(("umlage_personal_5", "orgchem_fohrer"))) +
                    d(computed.get(("umlage_personal_5", "mawi_m3_gutfl"))) +
                    d(computed.get(("umlage_personal_5", "mawi_alff")))
                )
            else:
                v = Decimal("0")
            computed[("umlage_personal_5", col_key)] = v

        # ---- Row: Rechnungsbetrag = Summe Anteile 1-4 − Gutschriften + Betrag + 5-Umlage Personal ----
        for col_key in he_verbrauch:
            gutsch = d(value_map.get(("gutschriften", col_key)))
            betrag = d(value_map.get(("betrag", col_key)))
            personal = d(computed.get(("umlage_personal_5", col_key)))
            sa = d(computed.get(("summe_anteile_1_4", col_key)))
            computed[("rechnungsbetrag", col_key)] = sa - gutsch + betrag + personal

        # ---- Row: eff. L-He-Preis = Rechnungsbetrag / He-Verbrauch ----
        for col_key in he_verbrauch:
            rb = d(computed.get(("rechnungsbetrag", col_key)))
            hv = he_verbrauch.get(col_key, Decimal("0"))
            computed[("eff_lhe_preis", col_key)] = safe_div(rb, hv)

        # ---- Row: Sonstiges (TEXT) based on Betrag ----
        # IF(Betrag=0,"--------------",IF(Betrag<0,"Gutschrift","Nachzahlung"))
        sonstiges_text = {}
        for col_key in he_verbrauch:
            b = d(value_map.get(("betrag", col_key)))
            if b == 0:
                sonstiges_text[col_key] = "--------------"
            elif b < 0:
                sonstiges_text[col_key] = "Gutschrift"
            else:
                sonstiges_text[col_key] = "Nachzahlung"
        # overwrite display values for non-editable computed rows
        FIRST4 = {"bezogen_menge", "kaltg_warmfuell", "anzahl_15", "he_verbrauch"}
        FIRST8 = ["pkm_vogel", "iap_halfmann", "ikp", "orgchem_thiele",
                "phychem_m3_buntkow", "orgchem_fohrer", "mawi_m3_gutfl", "mawi_alff"]

        for rk, _label, _unit in self.ROWS:
            if rk in FIRST4:
                computed[(rk, "gesamt_summe")] = (
                    d(computed.get((rk, "chemie"))) +
                    d(computed.get((rk, "mawi"))) +
                    d(computed.get((rk, "physik")))
                )
            else:
                computed[(rk, "gesamt_summe")] = sum(d(computed.get((rk, ck))) for ck in FIRST8)
        non_editable_formula_rows = {
            "bezogen_menge",
            "kaltg_warmfuell",
            "anzahl_15",
            "he_verbrauch",
            "umlage_instandhaltung_1",
            "lhe_verluste",
            "umlage_heliumkosten_3",
            "kosten_he_gas_bezug_4",
            "rel_anteil",
            "summe_anteile_1_4",
            "umlage_personal_5",
            "rechnungsbetrag",
            "eff_lhe_preis",
        }

        for (rk, ck), val in computed.items():
            if rk in non_editable_formula_rows:
                value_map[(rk, ck)] = val
        rows = []
        for row_key, label, unit in self.ROWS:
            rows.append({
                "row_key": row_key,
                "label": label,
                "unit": unit,  # 👈 ADD THIS
                "editable": row_key in self.EDITABLE_ROW_KEYS,
                "cells": [
                    {
                        "col_key": col_key,
                        "value": value_map.get((row_key, col_key)),
                    }
                    for col_key, _ in self.COLUMNS
                ]
            })
        start_year = interval_year
        start_month = interval_start
        # end month = start + 5 months
        end_total = (start_month - 1) + 5
        end_year = start_year + (end_total // 12)
        end_month = (end_total % 12) + 1

        start_date_str = f"01.{start_month:02d}.{start_year}"
        last_day = calendar.monthrange(end_year, end_month)[1]
        end_date_str = f"{last_day:02d}.{end_month:02d}.{end_year}"
        # -------------------------------
        # Right-side summary table (new)
        # -------------------------------

        GROUP_IJKL = ["orgchem_thiele", "phychem_m3_buntkow", "orgchem_fohrer", "mawi_m3_gutfl", "mawi_alff"]
        GROUP_NOP = ["chemie", "mawi", "physik"]
        GROUP_STADT = ["pkm_vogel", "iap_halfmann", "ikp"]
      
        def val(row_key, col_key):
            """Get the final numeric value for a cell (prefer computed, fallback to stored)."""
            if (row_key, col_key) in computed:
                return d(computed.get((row_key, col_key)))
            return d(value_map.get((row_key, col_key)))

        def sum_group(row_key, cols):
            return sum(val(row_key, ck) for ck in cols)

        right_rows = []
        def nop_cols_for_row(row_key: str):
            # For gutschriften, NOP should sum the 5 IJKL client columns
            if row_key == "gutschriften":
                return GROUP_IJKL
            # otherwise NOP is the 3 summary columns
            return GROUP_NOP
        # These are your normal rows (same order as UI)
        # We will create one summary row per Abrechnung row_key.
        for row_key, label, unit in self.ROWS:
            # Sonstiges is text-only in this right table
            if row_key == "sonstiges":
                right_rows.append({
                    "label": label,
                    "unit": unit,
                    "ijkl": "Gutschriften",
                    "nop": "Gutschriften",
                    "stadt": "Gutschriften",
                    "check": "Gutschriften",
                    "is_text": True,
                })
                continue

            ijkl = sum_group(row_key, GROUP_IJKL)
            if row_key == "umlage_heliumkosten_3":
                nop = (
                    val(row_key, "chemie") +
                    val(row_key, "mawi") +
                    val(row_key, "physik")
                )
            else:
                nop = sum_group(row_key, nop_cols_for_row(row_key))
            stadt = sum_group(row_key, GROUP_STADT)
            check = nop + stadt

            # Special rule: Rechnungsbetrag row gets +Betrag +Gutschriften (for NOP only)
            if row_key == "rechnungsbetrag":
                # Rechnungsbetrag (NOP) = (Chemie+MaWi+Physik Rechnungsbetrag)
                #                       + (Chemie+MaWi+Physik Betrag)
                #                       + (IJKL Gutschriften)   <-- because you want gutschriften NOP from IJKL
                nop = (
                    sum_group("rechnungsbetrag", GROUP_NOP)
                    + sum_group("betrag", GROUP_NOP)
                    + sum_group("gutschriften", GROUP_IJKL)
    )
                check = nop + stadt

            right_rows.append({
                "label": label,
                "unit": unit,
                "ijkl": ijkl,
                "nop": nop,
                "stadt": stadt,
                "check": check,
                "is_text": False,
            })

        # --- penultimate row (custom) ---
        # "sum of Summe Anteile 1-4, Betrag, 5-Umlage Personal and Gutschriften"
        def custom_total(cols):
            return (
                sum_group("summe_anteile_1_4", cols)
                + sum_group("betrag", cols)
                + sum_group("umlage_personal_5", cols)
                + sum_group("gutschriften", cols)
            )

        right_rows.append({
            "label": "",      # no label in the screenshot row
            "unit": "",       # blank
            "ijkl": "",       # blank for IJKL
            "nop": custom_total(GROUP_NOP),
            "stadt": custom_total(GROUP_STADT),
            "check": custom_total(GROUP_NOP) + custom_total(GROUP_STADT),
            "is_text": False,
            "is_total_row": True,
        })

        # --- last row (custom "Check") ---
        right_rows.append({
            "label": "Check",
            "unit": "",
            "ijkl": "",
            "nop": "",
            "stadt": "Check",
            "check": "",
            "is_text": True,
            "is_check_row": True,
        })

        context["right_summary_rows"] = right_rows

        context["interval_text"] = f"{start_date_str} bis {end_date_str}"
        context["sonstiges_text"] = sonstiges_text
        context.update({
            "page_title": "Abrechnung",
            "columns": self.COLUMNS,
            "rows": rows,
            "interval_year": interval_year,
            "interval_start": interval_start,
        })
        context["bezugskosten_gashe"] = bezugskosten_gashe

     
        return context
class RechnungView(TemplateView):
    template_name = "rechnung.html"

    # Order must match Abrechnung order (first 8 “real” client columns)
    CLIENT_COLS = [
        ("pkm_vogel", "PKM Vogel", "Vogel", "Prof. Dr. Vogel"),
        ("iap_halfmann", "IAP Halfmann", "Halfmann", "Prof. Dr. Halfmann"),
        ("ikp", "IKP", "Kernphysik", "von Dungen"),
        ("orgchem_thiele", "OrgChem Thiele", "Thiele", "Prof. Dr. Thiele"),
        ("phychem_m3_buntkow", "PhyChem + M3 Buntkow", "Buntkowsky", "Prof. Dr. Buntkowsky"),
        ("orgchem_fohrer", "OrgChem Fohrer", "Fohrer", "Herr Dr. Fohrer"),
        ("mawi_m3_gutfl", "MaWi + M3 Gutfl", "Gutfleisch", "Prof. Dr. Gutfleisch"),
        ("mawi_alff", "MaWi Alff", "Alff", "Prof. Dr. Alff"),
    ]

    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)

        # interval from session (same as Abrechnung)
        interval_year = self.request.session.get("halfyear_year")
        interval_start = self.request.session.get("halfyear_start_month")
        if not interval_year or not interval_start:
            context["needs_interval"] = True
            return context

        interval_year = int(interval_year)
        interval_start = int(interval_start)

        def d(x):
            if x in (None, ""):
                return Decimal("0")
            try:
                return Decimal(str(x).replace(",", "."))
            except Exception:
                return Decimal("0")

        # ------------------------------------------------------------
        # 1) Reuse AbrechnungView computed context (NO duplicated logic)
        # ------------------------------------------------------------
        abrech = AbrechnungView()
        abrech.request = self.request
        abrech_ctx = abrech.get_context_data()

        # Helper: get a numeric cell value from Abrechnung context by row_key & col_key
        def ab_cell(row_key, col_key):
            for r in abrech_ctx["rows"]:
                if r["row_key"] == row_key:
                    for c in r["cells"]:
                        if c["col_key"] == col_key:
                            return d(c["value"])
            return Decimal("0")

        # ------------------------------------------------------------
        # 2) Values that are constant for ALL rows (from Gesamt-summe col)
        # ------------------------------------------------------------
        heliumverluste_eur = ab_cell("umlage_heliumkosten_3", "gesamt_summe")  # “3 Umlage Heliumkosten” Gesamt-summe
        verbr_u_repar_eur = ab_cell("umlage_instandhaltung_1", "gesamt_summe")  # “1 Umlage Instandhaltung” Gesamt-summe
        gesamtverfluessigung_l = ab_cell("he_verbrauch", "gesamt_summe")        # “He-Verbrauch (LHe)” Gesamt-summe

        # ------------------------------------------------------------
        # 3) Heliumverluste (l) from Halbjahresbilanz total Verbraucherverluste
        #    (sum all clients left+right)
        # ------------------------------------------------------------
        
        half_ctx = compute_halfyear_context(interval_year, interval_start)

        heliumverluste_l = Decimal("0")
        target_label = "Verbraucherverluste (Liter L-He)"

        # left table
        for row in half_ctx.get("rows_left", []):
            if row.get("label") == target_label:
                heliumverluste_l += sum(d(v) for v in row.get("values", []))

        # right table
        for row in half_ctx.get("rows_right", []):
            if row.get("label") == target_label:
                heliumverluste_l += sum(d(v) for v in row.get("values", []))

        # ------------------------------------------------------------
        # 4) Preis (€/l) = Verflüssigungskosten L-He from Betriebskosten
        #    i.e. instandhaltung / (sum of all outputs over the 6-month window)
        # ------------------------------------------------------------
        # compute total output over the 6-month window (all clients)
        window = []
        for offset in range(6):
            total_index = (interval_start - 1) + offset
            y = interval_year + (total_index // 12)
            m = (total_index % 12) + 1
            window.append((y, m))

        interval_total_output_lhe = Decimal("0")
        for (y, m) in window:
            interval_total_output_lhe += (
                SecondTableEntry.objects.filter(date__year=y, date__month=m).aggregate(
                    total=Coalesce(Sum("lhe_output"), Value(0, output_field=DecimalField()))
                )["total"] or Decimal("0")
            )

        bs = BetriebskostenSummary.objects.first()
        instandhaltung = d(bs.instandhaltung) if bs else Decimal("0")
        personalkosten = d(bs.umlage_personal) if bs else Decimal("0")

        preis_eur_pro_l = (instandhaltung / interval_total_output_lhe) if interval_total_output_lhe != 0 else Decimal("0")

        # ------------------------------------------------------------
        # 5) Build per-client rows (order = Abrechnung)
        # ------------------------------------------------------------
        rows = []
        for col_key, tab_kuerzel, arbeitsgruppe, name in self.CLIENT_COLS:
            rows.append({
                "arbeitsgruppe": arbeitsgruppe,
                "tabellenkuerzel": tab_kuerzel,
                "name": name,

                # constants (same for all clients)
                "heliumverluste_eur": heliumverluste_eur,
                "heliumverluste_l": heliumverluste_l,
                "verbr_u_repar_eur": verbr_u_repar_eur,
                "gesamtverfluessigung_l": gesamtverfluessigung_l,
                "preis_eur_l": preis_eur_pro_l,

                # per-client (from Abrechnung, same order)
                "bezogene_menge_l": ab_cell("bezogen_menge", col_key),
                "verfluessigungskosten_eur": ab_cell("umlage_instandhaltung_1", col_key),
                "verlustkosten_eur": ab_cell("umlage_heliumkosten_3", col_key),
                "gaskosten_eur": ab_cell("kosten_he_gas_bezug_4", col_key),
                "gutschriften_eur": ab_cell("betrag", col_key),  # you said “Betrag”
                "personalkosten_eur": personalkosten,
                "anteil_personal_eur": ab_cell("umlage_personal_5", col_key),
                "gesamtkosten_eur": ab_cell("rechnungsbetrag", col_key),
            })

        context.update({
            "page_title": "Rechnung",
            "rows": rows,
            "interval_year": interval_year,
            "interval_start": interval_start,
        })
        return context

@method_decorator(csrf_exempt, name="dispatch")
class SaveAbrechnungCellsView(View):
    def post(self, request):
        interval_year = request.session.get("halfyear_year")
        interval_start = request.session.get("halfyear_start_month")
        if not interval_year or not interval_start:
            return JsonResponse({"ok": False, "error": "No halfyear interval selected."}, status=400)

        interval_year = int(interval_year)
        interval_start = int(interval_start)

        try:
            payload = json.loads(request.body.decode("utf-8"))
        except Exception:
            return JsonResponse({"ok": False, "error": "Invalid JSON."}, status=400)

        changes = payload.get("changes", [])

        for item in changes:
            row_key = item.get("row_key")
            col_key = item.get("col_key")
            raw_val = item.get("value")

            # allow empty -> NULL
            if raw_val in ("", None):
                val = None
            else:
                try:
                    val = Decimal(str(raw_val).replace(",", "."))
                except Exception:
                    continue

            AbrechnungCell.objects.update_or_create(
                interval_year=interval_year,
                interval_start_month=interval_start,
                row_key=row_key,
                col_key=col_key,
                defaults={"value": val},
            )

        return JsonResponse({"ok": True})

class SaveCellsView(View):
    
    def calculate_bottom_3_dependents(self, sheet):
        updated_cells = []

        def get_cell(row_idx, col_idx):
            return Cell.objects.filter(
                sheet=sheet,
                table_type='bottom_3',
                row_index=row_idx,
                column_index=col_idx
            ).first()

        def set_cell(row_idx, col_idx, value):
            cell, created = Cell.objects.get_or_create(
                sheet=sheet,
                table_type='bottom_3',
                row_index=row_idx,
                column_index=col_idx,
                defaults={'value': value}
            )
            if not created and cell.value != value:
                cell.value = value
                cell.save()
            elif created:
                cell.save()

            updated_cells.append({
                'id': cell.id,
                'value': str(cell.value) if cell.value is not None else '',
                'is_calculated': True,
            })

        def dec(x):
            if x in (None, ''):
                return Decimal('0')
            try:
                return Decimal(str(x))
            except Exception:
                return Decimal('0')

        # ---- current summary ----
        cur_sum = MonthlySummary.objects.filter(sheet=sheet).first()
        curr_k44 = dec(cur_sum.gesamtbestand_neu_lhe) if cur_sum else Decimal('0')
        total_verb = dec(cur_sum.verbraucherverlust_lhe) if cur_sum else Decimal('0')

        # ---- previous month summary ----
        year, month = sheet.year, sheet.month
        if month == 1:
            prev_year, prev_month = year - 1, 12
        else:
            prev_year, prev_month = year, month - 1

        prev_sheet = MonthlySheet.objects.filter(year=prev_year, month=prev_month).first()
        if prev_sheet:
            prev_sum = MonthlySummary.objects.filter(sheet=prev_sheet).first()
            prev_k44 = dec(prev_sum.gesamtbestand_neu_lhe) if prev_sum else Decimal('0')
        else:
            prev_k44 = Decimal('0')

        # ---- read editable inputs from bottom_3: F47,G47,I47,I50 ----
        def get_val(r, c):
            cell = get_cell(r, c)
            return dec(cell.value if cell else None)

    
        g47 = get_val(1, 1)
        i47 = get_val(1, 2)
        i50 = get_val(4, 2)

        # Now apply your formulas using prev_k44, curr_k44, total_verb, f47,g47,i47,i50
        # Row indices: 0..7 correspond to 46..53
        # col 3 = J, col 4 = K

        # Row 46
        k46 = prev_k44
        j46 = k46 * Decimal('0.75')
        set_cell(0, 3, j46)
        set_cell(0, 4, k46)

        # Row 47
        g47 = self._dec((get_cell(1, 1) or {}).value if get_cell(1, 1) else None)
        i47 = self._dec((get_cell(1, 2) or {}).value if get_cell(1, 2) else None)

        j47 = g47 + i47
        k47 = (j47 / Decimal('0.75')) + g47 if j47 != 0 else g47

        set_cell(1, 3, j47)
        set_cell(1, 4, k47)

        # Row 48
        k48 = k46 + k47
        j48 = k48 * Decimal('0.75')
        set_cell(2, 3, j48)
        set_cell(2, 4, k48)

        # Row 49
        k49 = curr_k44
        j49 = k49 * Decimal('0.75')
        set_cell(3, 3, j49)
        set_cell(3, 4, k49)

        # Row 50
        j50 = i50
        k50 = j50 / Decimal('0.75') if j50 != 0 else Decimal('0')
        set_cell(4, 3, j50)
        set_cell(4, 4, k50)

        # Row 51
        k51 = k48 - k49 - k50
        j51 = k51 * Decimal('0.75')
        set_cell(5, 3, j51)
        set_cell(5, 4, k51)

        # Row 52
        k52 = total_verb
        j52 = k52 * Decimal('0.75')
        set_cell(6, 3, j52)
        set_cell(6, 4, k52)

        # Row 53
        j53 = j51 - j52
        k53 = k51 - k52
        set_cell(7, 3, j53)
        set_cell(7, 4, k53)

        return updated_cells


    def _dec(self, value):
        """Convert value to Decimal or return 0."""
        if value is None or value == '':
            return Decimal('0')
        try:
            return Decimal(str(value))
        except Exception:
            return Decimal('0')

    def post(self, request, *args, **kwargs):
        """
        Handle AJAX saves from monthly_sheet.html
        - Single-cell save: when cell_id is present (blur on one cell)
        - Bulk save: when the 'Save All Cells' button is used (no cell_id)
        """
        try:
            sheet_id = request.POST.get('sheet_id')
            if not sheet_id:
                return JsonResponse({
                    'status': 'error',
                    'message': 'Missing sheet_id'
                })

            sheet = MonthlySheet.objects.get(id=sheet_id)

            # -------- Single-cell update (blur) --------
            cell_id = request.POST.get('cell_id')
            if cell_id:
                value_raw = (request.POST.get('value') or '').strip()

                try:
                    cell = Cell.objects.get(id=cell_id, sheet=sheet)
                except Cell.DoesNotExist:
                    return JsonResponse({
                        'status': 'error',
                        'message': 'Cell not found'
                    })

                # Convert value to Decimal or None
                if value_raw == '':
                    new_value = None
                else:
                    try:
                        # Allow comma or dot
                        value_clean = value_raw.replace(',', '.')
                        new_value = Decimal(value_clean)
                    except (InvalidOperation, ValueError):
                        # If conversion fails, treat as empty
                        new_value = None

                cell.value = new_value
                cell.save()

                updated_cells = [{
                    'id': cell.id,
                    'value': '' if cell.value is None else str(cell.value),
                    'is_calculated': cell.is_formula,  # model field
                }]

                # Recalculate dependents depending on table_type
                if cell.table_type == 'top_left':
                    updated_cells.extend(
                        self.calculate_top_left_dependents(sheet, cell)
                    )
                elif cell.table_type == 'top_right':
                    updated_cells.extend(
                        self.calculate_top_right_dependents(sheet, cell)
                    )
                elif cell.table_type == 'bottom_1':
                    updated_cells.extend(self.calculate_bottom_1_dependents(sheet, cell))
                elif cell.table_type == 'bottom_3':
                    updated_cells.extend(
                     self.calculate_bottom_3_dependents(sheet)
                    )   
                # bottom_1 / bottom_2 / bottom_3 currently have no formulas:
                # they just save the new value.
                updated_cells += self.calculate_bottom_3_dependents(sheet)
                return JsonResponse({
                    'status': 'success',
                    'updated_cells': updated_cells
                })
                

            # -------- Bulk save (Save All button) --------
            return self.save_bulk_cells(request, sheet)

        except MonthlySheet.DoesNotExist:
            return JsonResponse({
                'status': 'error',
                'message': 'Sheet not found'
            })
        except Exception as e:
            # Generic safety net so the frontend sees an error message
            return JsonResponse({
                'status': 'error',
                'message': str(e)
            })

    # ... your other methods above ...

    # Update the calculate_top_right_dependents method in SaveCellsView class

    def calculate_top_right_dependents(self, sheet, changed_cell):
        """
        Recalculate all dependent cells in the top-right table according to Excel formulas.
        
        Excel rows (4-20) -> 0-based indices (0-15)
        Rows:
        0: Stand der Gaszähler (Vormonat) (Nm³) - shares for M3 clients
        1: Gasrückführung (Nm³)
        2: Rückführung flüssig (Lit. L-He)
        3: Sonderrückführungen (Lit. L-He) - editable
        4: Sammelrückführungen (Lit. L-He) - from helium_input groups
        5: Bestand in Kannen-1 (Lit. L-He) - editable, merged in pairs
        6: Summe Bestand (Lit. L-He) = row 5
        7: Best. in Kannen Vormonat (Lit. L-He) - from previous month
        8: Bezug (Liter L-He) - from SecondTableEntry
        9: Rückführ. Soll (Lit. L-He) - calculated
        10: Verluste (Soll-Rückf.) (Lit. L-He) - calculated
        11: Füllungen warm (Lit. L-He) - from SecondTableEntry warm outputs
        12: Kaltgas Rückgabe (Lit. L-He) – Faktor - calculated
        13: Faktor 0.06 - fixed
        14: Verbraucherverluste (Liter L-He) - calculated
        15: % - calculated
        """
     
        
        TOP_RIGHT_CLIENTS = [
            "Dr. Fohrer",      # L
            "AG Buntk.",       # M (merged with L)
            "AG Alff",         # N
            "AG Gutfl.",       # O (merged with N)
            "M3 Thiele",       # P
            "M3 Buntkowsky",   # Q
            "M3 Gutfleisch",   # R
            "Merck"
        ]
        
        # Define merged pairs
        MERGED_PAIRS = [
            ("Dr. Fohrer", "AG Buntk."),   # L and M are merged
            ("AG Alff", "AG Gutfl."),      # N and O are merged
        ]
        
        # M3 clients (not merged, calculated individually)
        M3_CLIENTS = ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"]
        
        # Groups for Sammelrückführungen (helium_input)
        HELIUM_INPUT_GROUPS = {
            "fohrer_buntk": ["Dr. Fohrer", "AG Buntk."],
            "alff_gutfl": ["AG Alff", "AG Gutfl."],
            "m3": ["M3 Thiele", "M3 Buntkowsky", "M3 Gutfleisch"],
            "merck": ["Merck"],
        }
        
        year = sheet.year
        month = sheet.month
        factor = Decimal('0.06')  # Fixed factor from Excel
        updated_cells = []
        
        # Helper functions
        def get_val(client_name, row_idx):
            """Get cell value for a client and row"""
            try:
                client = Client.objects.get(name=client_name)
                col_idx = TOP_RIGHT_CLIENTS.index(client_name)
                cell = Cell.objects.filter(
                    sheet=sheet,
                    table_type='top_right',
                    client=client,
                    row_index=row_idx,
                    column_index=col_idx
                ).first()
                if cell and cell.value is not None:
                    return Decimal(str(cell.value))
            except (Client.DoesNotExist, ValueError, KeyError):
                pass
            return Decimal('0')
        
        def set_val(client_name, row_idx, value, is_calculated=True):
            """Set cell value for a client and row"""
            try:
                client = Client.objects.get(name=client_name)
                col_idx = TOP_RIGHT_CLIENTS.index(client_name)
                
                cell, created = Cell.objects.get_or_create(
                    sheet=sheet,
                    table_type='top_right',
                    client=client,
                    row_index=row_idx,
                    column_index=col_idx,
                    defaults={'value': value}
                )
                
                # Only update if value changed
                if not created and cell.value != value:
                    cell.value = value
                    cell.save()
                elif created:
                    cell.save()
                
                updated_cells.append({
                    'id': cell.id,
                    'value': str(cell.value) if cell.value else '',
                    'is_calculated': is_calculated
                })
                
                return True
            except (Client.DoesNotExist, ValueError):
                return False
        
        # 1. Update Summe Bestand (row 6) from Bestand in Kannen-1 (row 5)
        # For merged pairs: copy value from changed cell to its pair
        if changed_cell and changed_cell.table_type == 'top_right':
            if changed_cell.row_index == 5:  # Bestand in Kannen-1
                client_name = changed_cell.client.name
                new_value = changed_cell.value
                
                # Check if this client is in a merged pair
                for pair in MERGED_PAIRS:
                    if client_name in pair:
                        # Update both clients in the pair
                        for client_in_pair in pair:
                            if client_in_pair != client_name:
                                set_val(client_in_pair, 5, new_value, is_calculated=False)
                        break
        
        # 2. For all clients: Set Summe Bestand (row 6) = Bestand in Kannen-1 (row 5)
        for client_name in TOP_RIGHT_CLIENTS:
            bestand_value = get_val(client_name, 5)
            set_val(client_name, 6, bestand_value, is_calculated=True)
        
        # 3. Update Sammelrückführungen (row 4) from helium_input groups
        for group_name, client_names in HELIUM_INPUT_GROUPS.items():
            # Get total helium_input for this group
            clients_in_group = Client.objects.filter(name__in=client_names)
            total_helium = ExcelEntry.objects.filter(
                client__in=clients_in_group,
                date__year=year,
                date__month=month
            ).aggregate(total=Coalesce(Sum('lhe_ges'), Decimal('0')))['total']
            
            # Set same value for all clients in group
            for client_name in client_names:
                set_val(client_name, 4, total_helium, is_calculated=True)
        
        # 4. Calculate Rückführung flüssig (row 2)
        # For merged pairs: =L8 for Dr. Fohrer/AG Buntk., =N8 for AG Alff/AG Gutfl.
        # For M3 clients: =$P$8 * P4, $P$8 * Q4, $P$8 * R4
        
        # Get Sammelrückführungen values for groups
        sammel_fohrer_buntk = get_val("Dr. Fohrer", 4)  # L8
        sammel_alff_gutfl = get_val("AG Alff", 4)  # N8
        sammel_m3_group = get_val("M3 Thiele", 4)  # P8 (same for all M3)
        
        # For merged pairs
        set_val("Dr. Fohrer", 2, sammel_fohrer_buntk, is_calculated=True)
        set_val("AG Buntk.", 2, sammel_fohrer_buntk, is_calculated=True)
        set_val("AG Alff", 2, sammel_alff_gutfl, is_calculated=True)
        set_val("AG Gutfl.", 2, sammel_alff_gutfl, is_calculated=True)
        
        # For M3 clients: =$P$8 * column4 (Stand der Gaszähler)
        for m3_client in M3_CLIENTS:
            stand_value = get_val(m3_client, 0)  # Stand der Gaszähler (row 0)
            rueck_value = sammel_m3_group * stand_value
            set_val(m3_client, 2, rueck_value, is_calculated=True)
        
        # 5. Calculate Füllungen warm (row 11) from SecondTableEntry warm outputs
          # 5. Calculate Füllungen warm (row 11) as NUMBER of warm fillings
        #    (sum of 1s where each warm SecondTableEntry is one filling)
        for client_name in TOP_RIGHT_CLIENTS:
            client = Client.objects.get(name=client_name)
            warm_count = SecondTableEntry.objects.filter(
                client=client,
                date__year=year,
                date__month=month,
                is_warm=True
            ).aggregate(
                total=Coalesce(Count('id'), 0)
            )['total']

            # store as Decimal so later formulas (warm * 15) still work nicely
            warm_value = Decimal(warm_count)
            set_val(client_name, 11, warm_value, is_calculated=True)
        # 6. Set Faktor row (13) to 0.06
        for client_name in TOP_RIGHT_CLIENTS:
            set_val(client_name, 13, factor, is_calculated=True)

                # 6a. Recalculate Stand der Gaszähler (row 0) for the merged pairs
        #     according to Excel:
        #     L4 = L13 / (L13 + M13), M4 = M13 / (L13 + M13)
        #     N4 = N13 / (N13 + O13), O4 = O13 / (N13 + O13)

        # Pair 1: Dr. Fohrer / AG Buntk.
        bezug_dr = get_val("Dr. Fohrer", 8)   # L13
        bezug_buntk = get_val("AG Buntk.", 8) # M13
        total_pair1 = bezug_dr + bezug_buntk

        if total_pair1 != 0:
            set_val("Dr. Fohrer", 0, bezug_dr / total_pair1, is_calculated=True)
            set_val("AG Buntk.", 0, bezug_buntk / total_pair1, is_calculated=True)
        else:
            # if no Bezug, both shares are 0
            set_val("Dr. Fohrer", 0, Decimal('0'), is_calculated=True)
            set_val("AG Buntk.", 0, Decimal('0'), is_calculated=True)

        # Pair 2: AG Alff / AG Gutfl.
        bezug_alff = get_val("AG Alff", 8)    # N13
        bezug_gutfl = get_val("AG Gutfl.", 8) # O13
        total_pair2 = bezug_alff + bezug_gutfl

        if total_pair2 != 0:
            set_val("AG Alff", 0, bezug_alff / total_pair2, is_calculated=True)
            set_val("AG Gutfl.", 0, bezug_gutfl / total_pair2, is_calculated=True)
        else:
            set_val("AG Alff", 0, Decimal('0'), is_calculated=True)
            set_val("AG Gutfl.", 0, Decimal('0'), is_calculated=True)

        
        # 7. Calculate all other dependent rows for merged pairs
        for pair in MERGED_PAIRS:
            client1, client2 = pair
            
            # Get values for the pair
            bezug1 = get_val(client1, 8)  # Bezug client1
            bezug2 = get_val(client2, 8)  # Bezug client2
            total_bezug = bezug1 + bezug2  # L13+M13 or N13+O13
            
            summe_bestand = get_val(client1, 6)  # L11 or N11 (merged, same value)
            best_vormonat = get_val(client1, 7)  # L12 or N12 (merged, same value)
            rueck_fl = get_val(client1, 2)  # L6 or N6 (merged, same value)
            warm1 = get_val(client1, 11)  # L16 or N16
            warm2 = get_val(client2, 11)  # M16 or O16
            total_warm = warm1 + warm2  # L16+M16 or N16+O16
            
            # Calculate Rückführ. Soll (row 9)
            # = L13+M13 - L11 + L12 for first pair
            # = N13+O13 - N11 + N12 for second pair
            rueck_soll = total_bezug - summe_bestand + best_vormonat
            set_val(client1, 9, rueck_soll, is_calculated=True)
            set_val(client2, 9, rueck_soll, is_calculated=True)
            
            # Calculate Verluste (row 10) = Rückführ. Soll - Rückführung flüssig
            verluste = rueck_soll - rueck_fl
            set_val(client1, 10, verluste, is_calculated=True)
            set_val(client2, 10, verluste, is_calculated=True)
            
            # Calculate Kaltgas Rückgabe (row 12)
            # = (L13+M13)*$A18 + (L16+M16)*15
            kaltgas = (total_bezug * factor) + (total_warm * Decimal('15'))
            set_val(client1, 12, kaltgas, is_calculated=True)
            set_val(client2, 12, kaltgas, is_calculated=True)
            
            # Calculate Verbraucherverluste (row 14) = Verluste - Kaltgas
            verbrauch = verluste - kaltgas
            set_val(client1, 14, verbrauch, is_calculated=True)
            set_val(client2, 14, verbrauch, is_calculated=True)
            
            # Calculate % (row 15) = Verbraucherverluste / (L13+M13)
            if total_bezug != 0:
                prozent = verbrauch / total_bezug
            else:
                prozent = Decimal('0')
            set_val(client1, 15, prozent, is_calculated=True)
            set_val(client2, 15, prozent, is_calculated=True)
        
        # 8. Calculate all dependent rows for M3 clients (individual calculations)
        for m3_client in M3_CLIENTS:
            # Get individual values
            bezug = get_val(m3_client, 8)  # Bezug for this M3 client
            summe_bestand = get_val(m3_client, 6)  # Summe Bestand
            best_vormonat = get_val(m3_client, 7)  # Best. in Kannen Vormonat
            rueck_fl = get_val(m3_client, 2)  # Rückführung flüssig
            warm = get_val(m3_client, 11)  # Füllungen warm
            
            # Calculate Rückführ. Soll (row 9) = Bezug - Summe Bestand + Best. Vormonat
            rueck_soll = bezug - summe_bestand + best_vormonat
            set_val(m3_client, 9, rueck_soll, is_calculated=True)
            
            # Calculate Verluste (row 10) = Rückführ. Soll - Rückführung flüssig
            verluste = rueck_soll - rueck_fl
            set_val(m3_client, 10, verluste, is_calculated=True)
            
            # Calculate Kaltgas Rückgabe (row 12) = Bezug * factor + warm * 15
            kaltgas = (bezug * factor) + (warm * Decimal('15'))
            set_val(m3_client, 12, kaltgas, is_calculated=True)
            
            # Calculate Verbraucherverluste (row 14) = Verluste - Kaltgas
            verbrauch = verluste - kaltgas
            set_val(m3_client, 14, verbrauch, is_calculated=True)
            
            # Calculate % (row 15) = Verbraucherverluste / Bezug
            if bezug != 0:
                prozent = verbrauch / bezug
            else:
                prozent = Decimal('0')
            set_val(m3_client, 15, prozent, is_calculated=True)
             # 9. Simple client(s): only Bezug, Sammelrückführungen, Verbraucherverluste = Sammel - Bezug
        SIMPLE_RIGHT_CLIENTS = ["Merck"]  # use the exact database name here

        for client_name in SIMPLE_RIGHT_CLIENTS:
            # Bezug (row 8)
            bezug = get_val(client_name, 8)

            # Sammelrückführungen (row 4)
            sammel = get_val(client_name, 4)

            # Verbraucherverluste (row 14) = Sammelrückführungen - Bezug
            verbrauch =  bezug - sammel
            set_val(client_name, 14, verbrauch, is_calculated=True)

            # % (row 15) = Verbraucherverluste / Bezug
            if bezug != 0:
                prozent = verbrauch / bezug
            else:
                prozent = Decimal("0")
            set_val(client_name, 15, prozent, is_calculated=True)
        
        return updated_cells
    


    def calculate_bottom_1_dependents(self, sheet, changed_cell):
        """
        Recalculate Bottom Table 1 (table_type='bottom_1').

        Layout (row_index 0–9, col_index 0–4):

        Rows 0–8:
            0: Batterie 1
            1: 2
            2: 3
            3: 4
            4: 5
            5: 6
            6: 2 Bündel
            7: 2 Ballone
            8: Reingasspeicher

        Row 9:
            9: Gasbestand  (totals row)

        Columns:
            0: Volumen          (fixed values, not editable)
            1: bar              (editable for rows 0–8)
            2: korrigiert       = bar / (bar/2000 + 1)
            3: Nm³              = Volumen * korrigiert
            4: Lit. LHe         = Nm³ / 0.75

        Row 9:
            - Volumen (col 0): empty
            - bar      (col 1): empty
            - korrigiert (col 2): empty
            - Nm³ (col 3): SUM Nm³ rows 0–8
            - Lit. LHe (col 4): SUM Lit. LHe rows 0–8
        """
    
        updated_cells = []

        DATA_ROWS = list(range(0, 9))  # 0–8
        TOTAL_ROW = 9

        COL_VOL = 0
        COL_BAR = 1
        COL_KORR = 2
        COL_NM3 = 3
        COL_LHE = 4

        # Fixed Volumen values for rows 0–8
        VOLUMES = [
            Decimal("2.4"),   # row 0
            Decimal("5.1"),   # row 1
            Decimal("4.0"),   # row 2
            Decimal("1.0"),   # row 3
            Decimal("4.0"),   # row 4
            Decimal("0.4"),   # row 5
            Decimal("1.2"),   # row 6
            Decimal("20.0"),  # row 7
            Decimal("5.0"),   # row 8
        ]

        def get_cell(row_idx, col_idx):
            return Cell.objects.filter(
                sheet=sheet,
                table_type="bottom_1",
                row_index=row_idx,
                column_index=col_idx,
            ).first()

        def set_calc(row_idx, col_idx, value):
            """
            Set a calculated cell and add it to updated_cells.
            If value is None, we clear the cell.
            """
            cell = get_cell(row_idx, col_idx)
            if not cell:
                cell = Cell(
                    sheet=sheet,
                    table_type="bottom_1",
                    row_index=row_idx,
                    column_index=col_idx,
                    value=value,
                )
            else:
                cell.value = value
            cell.save()

            updated_cells.append(
                {
                    "id": cell.id,
                    "value": "" if cell.value is None else str(cell.value),
                    "is_calculated": True,
                }
            )

        # ---------- Rows 0–8: per-gasspeicher calculations ----------
        for row_idx in DATA_ROWS:
            bar_cell = get_cell(row_idx, COL_BAR)

            # Volumen: fixed constant or, if present, value from DB
            vol = VOLUMES[row_idx]
            vol_cell = get_cell(row_idx, COL_VOL)
            if vol_cell and vol_cell.value is not None:
                try:
                    vol = Decimal(str(vol_cell.value))
                except (InvalidOperation, ValueError):
                    # fall back to fixed constant
                    vol = VOLUMES[row_idx]

            bar = None
            try:
                if bar_cell and bar_cell.value is not None:
                    bar = Decimal(str(bar_cell.value))
            except (InvalidOperation, ValueError):
                bar = None

            # korrigiert = bar / (bar/2000 + 1)
            if bar is not None and bar != 0:
                korr = bar / (bar / Decimal("2000") + Decimal("1"))
            else:
                korr = None

            # Nm³ = Volumen * korrigiert
            if korr is not None:
                nm3 = vol * korr
            else:
                nm3 = None

            # Lit. LHe = Nm³ / 0.75
            if nm3 is not None:
                lit_lhe = nm3 / Decimal("0.75")
            else:
                lit_lhe = None

            # Write calculated cells back (NOT Volumen or bar)
            set_calc(row_idx, COL_KORR, korr)
            set_calc(row_idx, COL_NM3, nm3)
            set_calc(row_idx, COL_LHE, lit_lhe)

        # ---------- Row 9: totals (Gasbestand) ----------
        total_nm3 = Decimal("0")
        total_lhe = Decimal("0")
        has_nm3 = False
        has_lhe = False

        for row_idx in DATA_ROWS:
            nm3_cell = get_cell(row_idx, COL_NM3)
            if nm3_cell and nm3_cell.value is not None:
                try:
                    total_nm3 += Decimal(str(nm3_cell.value))
                    has_nm3 = True
                except (InvalidOperation, ValueError):
                    pass

            lhe_cell = get_cell(row_idx, COL_LHE)
            if lhe_cell and lhe_cell.value is not None:
                try:
                    total_lhe += Decimal(str(lhe_cell.value))
                    has_lhe = True
                except (InvalidOperation, ValueError):
                    pass

        # Volumen (0), bar (1), korrigiert (2) on total row stay empty
        set_calc(TOTAL_ROW, COL_KORR, None)  # explicitly clear korrigiert
        set_calc(TOTAL_ROW, COL_NM3, total_nm3 if has_nm3 else None)
        set_calc(TOTAL_ROW, COL_LHE, total_lhe if has_lhe else None)

        return updated_cells





    def calculate_top_left_dependents(self, sheet, changed_cell):
            """Calculate dependent cells in top_left table"""
           
            
            client_id = changed_cell.client_id
            updated_cells = []
            
            # Get all cells for this client in top_left table
            client_cells = Cell.objects.filter(
                sheet=sheet,
                table_type='top_left',
                client_id=client_id
            )
            
            # Create a dict for easy access
            cell_dict = {}
            for cell in client_cells:
                cell_dict[cell.row_index] = cell
            
            # Get values with safe defaults
            def get_cell_value(row_idx):
                cell = cell_dict.get(row_idx)
                if cell and cell.value is not None:
                    try:
                        return Decimal(str(cell.value))
                    except Exception:
                        return Decimal('0')
                return Decimal('0')
            
            # 1. B5 = IF(B3>0; B3-B4; 0) - Gasrückführung
            b3 = get_cell_value(0)  # row_index 0 = Excel B3 (UI row 1)
            b4 = get_cell_value(1)  # row_index 1 = Excel B4 (UI row 2)
            
            # Calculate B5
            if b3 > 0:
                b5_value = b3 - b4
                if b5_value < 0:
                    b5_value = Decimal('0')
            else:
                b5_value = Decimal('0')
            
            # Update B5 - FORCE update even if value appears the same
            b5_cell = cell_dict.get(2)  # row_index 2 = Excel B5 (UI row 3)
            if b5_cell:
                # Always update B5 when B3 or B4 changes
                b5_cell.value = b5_value
                b5_cell.save()
                updated_cells.append({
                    'id': b5_cell.id,
                    'value': str(b5_cell.value),
                    'is_calculated': True
                })
            
            # 2. B6 = B5 / 0.75 - Rückführung flüssig
            b6_value = b5_value / Decimal('0.75')
            b6_cell = cell_dict.get(3)  # row_index 3 = Excel B6 (UI row 4)
            if b6_cell:
                b6_cell.value = b6_value
                b6_cell.save()
                updated_cells.append({
                    'id': b6_cell.id,
                    'value': str(b6_cell.value),
                    'is_calculated': True
                })
            
            # 3. B9 = B7 + B8 - Summe Bestand
            b7 = get_cell_value(4)  # row_index 4 = Excel B7 (UI row 5)
            b8 = get_cell_value(5)  # row_index 5 = Excel B8 (UI row 6)
            b9_value = b7 + b8
            b9_cell = cell_dict.get(6)  # row_index 6 = Excel B9 (UI row 7)
            if b9_cell:
                b9_cell.value = b9_value
                b9_cell.save()
                updated_cells.append({
                    'id': b9_cell.id,
                    'value': str(b9_cell.value),
                    'is_calculated': True
                })
            
            # 4. B11 = Sum of LHe Output from SecondTableEntry for this client/month - Bezug
            
            client = changed_cell.client
            
            # Calculate total LHe output for this client in this month
            # 4. B11 = Bezug (Liter L-He)
            # For start sheet: manual entry, for other sheets: auto-calculated from SecondTableEntry
         
            client = changed_cell.client

            b11_cell = cell_dict.get(8)  # row_index 8 = Excel B11 (UI row 9)

            # Check if this is the start sheet (2025-01)
            is_start_sheet = (sheet.year == 2025 and sheet.month == 1)

            # Get the B11 value for calculations
            b11_value = Decimal('0')
            
            if is_start_sheet:
                # For start sheet, keep whatever value is already there (manual entry)
                if b11_cell and b11_cell.value is not None:
                    b11_value = Decimal(str(b11_cell.value))
            else:
                # For non-start sheets: auto-calculate from SecondTableEntry
                lhe_output_sum = SecondTableEntry.objects.filter(
                    client=client,
                    date__year=sheet.year,
                    date__month=sheet.month
                ).aggregate(
                    total=Coalesce(Sum('lhe_output'), Decimal('0'))
                )['total']
                
                b11_value = lhe_output_sum
                
                if b11_cell and (b11_cell.value != b11_value or b11_cell.value is None):
                    b11_cell.value = b11_value
                    b11_cell.save()
                    updated_cells.append({
                        'id': b11_cell.id,
                        'value': str(b11_cell.value),
                        'is_calculated': True  # Calculated from SecondTableEntry
                    })
            
            # 5. B12 = B11 + B10 - B9 - Rückführ. Soll
            b10 = get_cell_value(7)  # row_index 7 = Excel B10 (UI row 8)
            b12_value = b11_value + b10 - b9_value  # Use b11_value instead of lhe_output_sum
            b12_cell = cell_dict.get(9)  # row_index 9 = Excel B12 (UI row 10)
            if b12_cell:
                b12_cell.value = b12_value
                b12_cell.save()
                updated_cells.append({
                    'id': b12_cell.id,
                    'value': str(b12_cell.value),
                    'is_calculated': True
                })
            
            # 6. B13 = B12 - B6 - Verluste (Soll-Rückf.)
            # 6. B13 = Verluste (Soll-Rückf.)
            # Default (AG Vogel, AG Halfm): B12 - B6
            # IKP: B12 - B6 - Sonderrückführungen (B7)
            if (client.name or "").strip() == "IKP":
                b13_value = b12_value - b6_value - b7
            else:
                b13_value = b12_value - b6_value
            b13_cell = cell_dict.get(10)  # row_index 10 = Excel B13 (UI row 11)
            if b13_cell:
                b13_cell.value = b13_value
                b13_cell.save()
                updated_cells.append({
                    'id': b13_cell.id,
                    'value': str(b13_cell.value),
                    'is_calculated': True
                })
            
            # 7. B14 = Count of warm outputs
            warm_count = SecondTableEntry.objects.filter(
                client=client,
                date__year=sheet.year,
                date__month=sheet.month,
                is_warm=True
            ).count()
            
            b14_cell = cell_dict.get(11)  # row_index 11 = Excel B14 (UI row 12)
            if b14_cell:
                b14_cell.value = Decimal(str(warm_count))
                b14_cell.save()
                updated_cells.append({
                    'id': b14_cell.id,
                    'value': str(b14_cell.value),
                    'is_calculated': True
                })
            
            # 8. B15 = IF(B11>0; B11 * factor + B14 * 15; 0) - Kaltgas Rückgabe
            factor = get_cell_value(13)  # row_index 13 = Excel B16 (Faktor) (UI row 14)
            if factor == 0:
                factor = Decimal('0.06')  # default factor
            
            if b11_value > 0:  # Use b11_value
                b15_value = b11_value * factor + Decimal(str(warm_count)) * Decimal('15')
            else:
                b15_value = Decimal('0')
            
            b15_cell = cell_dict.get(12)  # row_index 12 = Excel B15 (UI row 13)
            if b15_cell:
                b15_cell.value = b15_value
                b15_cell.save()
                updated_cells.append({
                    'id': b15_cell.id,
                    'value': str(b15_cell.value),
                    'is_calculated': True
                })
            
            # 9. B17 = B13 - B15 - Verbraucherverluste
            b17_value = b13_value - b15_value
            b17_cell = cell_dict.get(14)  # row_index 14 = Excel B17 (UI row 15)
            if b17_cell:
                b17_cell.value = b17_value
                b17_cell.save()
                updated_cells.append({
                    'id': b17_cell.id,
                    'value': str(b17_cell.value),
                    'is_calculated': True
                })
            
            # 10. B18 = IF(B11=0; 0; B17/B11) - %
            if b11_value == 0:  # Use b11_value
                b18_value = Decimal('0')
            else:
                b18_value = b17_value / b11_value  # Use b11_value
            
            b18_cell = cell_dict.get(15)  # row_index 15 = Excel B18 (UI row 16)
            if b18_cell:
                b18_cell.value = b18_value
                b18_cell.save()
                updated_cells.append({
                    'id': b18_cell.id,
                    'value': str(b18_cell.value),
                    'is_calculated': True
                })
            
            return updated_cells 
    def save_bulk_cells(self, request, sheet):
        """Original bulk save logic (for backward compatibility)"""
        # Get all cell updates
        cell_updates = {}
        for key, value in request.POST.items():
            if key.startswith('cell_'):
                cell_id = key.replace('cell_', '')
                cell_updates[cell_id] = value
        
        # Update cells and track which ones changed
        updated_cells = []
        changed_clients = set()
        
        for cell_id, new_value in cell_updates.items():
            try:
                cell = Cell.objects.get(id=cell_id, sheet=sheet)
                old_value = cell.value
                
                # Convert new value
                try:
                    if new_value.strip():
                        cell.value = Decimal(new_value)
                    else:
                        cell.value = None
                except Exception:
                    cell.value = None
                
                # Only save if value changed
                if cell.value != old_value:
                    cell.save()
                    updated_cells.append({
                        'id': cell.id,
                        'value': str(cell.value) if cell.value else ''
                    })
                    # bottom_3 has no client, so this will just add None for those cells,
                    # which is harmless. Top-left cells still add their real client_id.
                    changed_clients.add(cell.client_id)
                    
            except Cell.DoesNotExist:
                continue
        
        # Recalculate for each changed client (top-left tables)
        for client_id in changed_clients:
            if client_id is not None:
                self.recalculate_top_left_table(sheet, client_id)
        
        # --- NEW: recalc bottom_3 for the whole sheet, independent of clients ---
        bottom3_updates = self.calculate_bottom_3_dependents(sheet)
        
        # Get all updated cells for response (top-left)
        all_updated_cells = []
        for client_id in changed_clients:
            if client_id is None:
                continue  # skip bottom_3 / non-client cells
            client_cells = Cell.objects.filter(
                sheet=sheet,
                client_id=client_id
            )
            for cell in client_cells:
                all_updated_cells.append({
                    'id': cell.id,
                    'value': str(cell.value) if cell.value else '',
                    'is_calculated': cell.is_formula
                })
        
        # Add bottom_3 recalculated cells (J46..K53, etc.)
        all_updated_cells.extend(bottom3_updates)
        
        return JsonResponse({
            'status': 'success',
            'message': f'Saved {len(updated_cells)} cells',
            'updated_cells': all_updated_cells
        })

    
    def recalculate_top_left_table(self, sheet, client_id):
        """Recalculate the top-left table for a specific client"""
        
        
        # Get all cells for this client in top_left table
        cells = Cell.objects.filter(
            sheet=sheet,
            table_type='top_left',
            client_id=client_id
        ).order_by('row_index')
        
        # Create a dictionary of cell values
        cell_dict = {}
        for cell in cells:
            cell_dict[cell.row_index] = cell
        
        # Excel logic implementation for top-left table
        # B3 (row_index 0) = Stand der Gaszähler (Nm³) - manual
        # B4 (row_index 1) = Stand der Gaszähler (Vormonat) (Nm³) - from previous sheet
        
        # Get B3 and B4
        b3_cell = cell_dict.get(0)  # UI Row 3
        b4_cell = cell_dict.get(1)  # UI Row 4
        
        if b3_cell and b3_cell.value and b4_cell and b4_cell.value:
            # B5 = IF(B3>0; B3-B4; 0)
            b3 = Decimal(str(b3_cell.value))
            b4 = Decimal(str(b4_cell.value))
            
            if b3 > 0:
                b5 = b3 - b4
                if b5 < 0:
                    b5 = Decimal('0')
            else:
                b5 = Decimal('0')
            
            # Update B5 (row_index 2)
            b5_cell = cell_dict.get(2)
            if b5_cell and (b5_cell.value != b5 or b5_cell.value is None):
                b5_cell.value = b5
                b5_cell.save()
            
            # B6 = B5 / 0.75 (row_index 3)
            b6 = b5 / Decimal('0.75')
            b6_cell = cell_dict.get(3)
            if b6_cell and (b6_cell.value != b6 or b6_cell.value is None):
                b6_cell.value = b6
                b6_cell.save()
        
        # Get previous month's sheet for B10
        if sheet.month == 1:
            prev_year = sheet.year - 1
            prev_month = 12
        else:
            prev_year = sheet.year
            prev_month = sheet.month - 1
        
        prev_sheet = MonthlySheet.objects.filter(
            year=prev_year,
            month=prev_month
        ).first()
        
        if prev_sheet:
            # Get B9 from previous sheet (row_index 7 in previous)
            prev_b9 = Cell.objects.filter(
                sheet=prev_sheet,
                table_type='top_left',
                client_id=client_id,
                row_index=7  # UI Row 9
            ).first()
            
            if prev_b9 and prev_b9.value:
                # Update B10 in current sheet (row_index 8)
                b10_cell = cell_dict.get(8)
                if b10_cell and (b10_cell.value != prev_b9.value or b10_cell.value is None):
                    b10_cell.value = prev_b9.value
                    b10_cell.save()
@method_decorator(csrf_exempt, name='dispatch')
class SaveMonthSummaryView(View):
    """
    Saves per-month summary values such as K44 (Gesamtbestand neu).
    Called from JS after 'Save All' finishes.
    """

    def post(self, request, *args, **kwargs):
        try:
            data = json.loads(request.body.decode('utf-8'))
        except json.JSONDecodeError:
            return JsonResponse(
                {'success': False, 'message': 'Invalid JSON'},
                status=400
            )

        sheet_id = data.get('sheet_id')
        if not sheet_id:
            return JsonResponse(
                {'success': False, 'message': 'Missing sheet_id'},
                status=400
            )

        try:
            sheet = MonthlySheet.objects.get(id=sheet_id)
        except MonthlySheet.DoesNotExist:
            return JsonResponse(
                {'success': False, 'message': 'Sheet not found'},
                status=404
            )

        # More tolerant decimal conversion: accepts "123.45" and "123,45"
        def to_decimal(value):
            if value is None:
                return None
            s = str(value).strip()
            if s == '':
                return None
            s = s.replace(',', '.')
            try:
                return Decimal(s)
            except (InvalidOperation, ValueError):
                # Debug: show what failed in the dev server console
                print("SaveMonthSummaryView to_decimal failed for:", repr(value))
                return None

        raw_k44 = data.get('gesamtbestand_neu_lhe')
        raw_gas = data.get('gasbestand_lhe')
        raw_verb = data.get('verbraucherverlust_lhe')

        gesamtbestand_neu_lhe = to_decimal(raw_k44)
        gasbestand_lhe = to_decimal(raw_gas)
        verbraucherverlust_lhe = to_decimal(raw_verb)

        summary, created = MonthlySummary.objects.get_or_create(sheet=sheet)

        if gesamtbestand_neu_lhe is not None:
            summary.gesamtbestand_neu_lhe = gesamtbestand_neu_lhe
        if gasbestand_lhe is not None:
            summary.gasbestand_lhe = gasbestand_lhe
        if verbraucherverlust_lhe is not None:
            summary.verbraucherverlust_lhe = verbraucherverlust_lhe

        summary.save()

        # Small debug output so you can see in the server console what was saved
        print(
            f"Saved MonthlySummary for {sheet.year}-{sheet.month:02d}: "
            f"K44={summary.gesamtbestand_neu_lhe}, "
            f"Gasbestand={summary.gasbestand_lhe}, "
            f"Verbraucherverlust={summary.verbraucherverlust_lhe}"
        )

        return JsonResponse({'success': True})



# Calculate View (placeholder for calculations)
class CalculateView(View):
    def post(self, request):
        # This will be implemented when you provide calculation rules
        return JsonResponse({
            'status': 'success',
            'message': 'Calculation endpoint ready'
        })

# Summary Sheet View
class SummarySheetView(TemplateView):
    template_name = 'summary_sheet.html'
    
    def get_context_data(self, **kwargs):
        context = super().get_context_data(**kwargs)
        start_month = int(self.kwargs.get('start_month', 1))
        year = int(self.kwargs.get('year', datetime.now().year))
        
        # Get 6 monthly sheets
    
        sheets = MonthlySheet.objects.filter(
            year=year,
            month__in=list(range(start_month, start_month + 6))
        ).order_by('month')
        
        # Aggregate data across months
        summary_data = self.calculate_summary(sheets)
        
        context.update({
            'year': year,
            'start_month': start_month,
            'end_month': start_month + 5,
            'sheets': sheets,
            'clients': Client.objects.all(),
            'summary_data': summary_data,
        })
        return context
    
    def calculate_summary(self, sheets):
        """Calculate totals across 6 months"""
        summary = {}
        
        for client in Client.objects.all():
            client_total = 0
            for sheet in sheets:
                # Get specific cells and sum
                cells = sheet.cells.filter(
                    client=client,
                    table_type='top_left',
                    row_index=0  # Example: first row
                )
                for cell in cells:
                    if cell.value:
                        try:
                            client_total += float(cell.value)
                        except (ValueError, TypeError):
                            continue
            
            summary[client.id] = client_total
        
        return summary

# Existing views below (keep all your existing functions)
def clients_list(request):
    # --- Clients for the yearly summary table ---
    clients = Client.objects.all()

    # --- Available years for output data (same as before) ---
    available_years_qs = SecondTableEntry.objects.dates('date', 'year', order='DESC')
    available_years = [y.year for y in available_years_qs]

    # === 1) Year used for the "Helium Output Yearly Summary" table ===
    # Uses ?year=... in the dropdown
    year_param = request.GET.get('year')
    if year_param:
        selected_year = int(year_param)
    else:
        selected_year = available_years[0] if available_years else datetime.now().year

    # === 2) GLOBAL half-year interval (shared with other pages) =======
    # Try GET params first
    interval_year_param = request.GET.get('interval_year')
    start_month_param = request.GET.get('interval_start_month')

    # Fallbacks from session
    session_year = request.session.get('halfyear_year')
    session_start_month = request.session.get('halfyear_start_month')

    # Determine final interval_year
    if interval_year_param:
        interval_year = int(interval_year_param)
    elif session_year:
        interval_year = int(session_year)
    else:
        # default: same as selected_year for summary
        interval_year = selected_year

    # Determine final interval_start_month
    if start_month_param:
        interval_start_month = int(start_month_param)
    elif session_start_month:
        interval_start_month = int(session_start_month)
    else:
        interval_start_month = 1  # default Jan

    # Store back into the session so other views can read them
    request.session['halfyear_year'] = interval_year
    request.session['halfyear_start_month'] = interval_start_month

    # === 3) Build a 6-month window, allowing wrap into the next year ===
    # Example: interval_year=2025, interval_start_month=12
    # window = [(2025,12), (2026,1), (2026,2), (2026,3), (2026,4), (2026,5)]
    window = []
    for offset in range(6):
        total_index = (interval_start_month - 1) + offset  # 0-based index
        y = interval_year + (total_index // 12)
        m = (total_index % 12) + 1
        window.append((y, m))
    def d(x):
        if x in (None, ""):
            return Decimal("0")
        try:
            return Decimal(str(x).replace(",", "."))
        except Exception:
            return Decimal("0")

    interval_total_output_lhe = Decimal("0")

    # Sum ALL helium output for ALL clients over the selected 6 months
    for (y, m) in window:
        interval_total_output_lhe += (
            SecondTableEntry.objects.filter(
                date__year=y,
                date__month=m,
            ).aggregate(
                total=Coalesce(Sum("lhe_output"), Value(0, output_field=DecimalField()))
            )["total"] or Decimal("0")
        )

    # put it into context
   
    # === 4) Totals per client in that 6-month window ==================
    monthly_data = []
    for client in clients:
        monthly_totals = []
        for (y, m) in window:
            total = SecondTableEntry.objects.filter(
                client=client,
                date__year=y,
                date__month=m
            ).aggregate(
                total=Coalesce(
                    Sum('lhe_output'),
                    Value(0, output_field=DecimalField())
                )
            )['total']
            monthly_totals.append(total)

        monthly_data.append({
            'client': client,
            'monthly_totals': monthly_totals,
            'year_total': sum(monthly_totals),
        })

    # === 5) Month labels for the header (only the 6-month window) =====
    month_labels = [calendar.month_abbr[m] for (y, m) in window]
    
    # === 6) FINALLY: return the response ==============================
    return render(request, 'clients_table.html', {
        'available_years': available_years,
        'current_year': selected_year,           # used by year dropdown
        'interval_year': interval_year,          # used by "Global 6-Month Interval" form
        'interval_start_month': interval_start_month,
        'months': month_labels,
        'monthly_data': monthly_data,
        'interval_total_output_lhe': interval_total_output_lhe,
    })

    # === 5) Month labels for the header (only the 6-month window) =====
    month_labels = [calendar.month_abbr[m] for (y, m) in window]


def set_halfyear_interval(request):
    if request.method == 'POST':
        year = int(request.POST.get('year'))
        start_month = int(request.POST.get('start_month'))

        request.session['halfyear_year'] = year
        request.session['halfyear_start_month'] = start_month

        return redirect(request.META.get('HTTP_REFERER', 'clients_list'))

    return redirect('clients_list')
# Table One View (ExcelEntry)
def table_one_view(request):
    

     # --- Base queryset for the main Helium Input table ---
    base_entries = ExcelEntry.objects.all().select_related('client', 'client__institute')

    # Read the global 6-month interval from the session
    interval_year = request.session.get('halfyear_year')
    interval_start = request.session.get('halfyear_start_month')

    if interval_year and interval_start:
        interval_year = int(interval_year)
        interval_start = int(interval_start)

        # Build the same 6-month window as on the main page (can cross year)
        window = []
        for offset in range(6):
            total_index = (interval_start - 1) + offset  # 0-based
            y = interval_year + (total_index // 12)
            m = (total_index % 12) + 1
            window.append((y, m))

        # Build Q filter: (year=m_year AND month=m_month) for any of those 6
        q = Q()
        for (y, m) in window:
            q |= Q(date__year=y, date__month=m)

        entries_table1 = base_entries.filter(q).order_by('-date')
    else:
        # Fallback: if no global interval yet, show everything
        entries_table1 = base_entries.order_by('-date')
    clients = Client.objects.all().select_related('institute')
    institutes = Institute.objects.all()

    # ---- Overview filters ----
    # years present in ExcelEntry.date
    year_qs = ExcelEntry.objects.dates('date', 'year', order='DESC')
    available_years = [d.year for d in year_qs]

    # default year/start month
        # default year/start month (if no global interval yet)
    if available_years:
        default_year = available_years[0]  # newest year in ExcelEntry
    else:
        default_year = timezone.now().year

    # 🔸 Read global half-year interval from session (set on main page)
    session_year = request.session.get('halfyear_year')
    session_start = request.session.get('halfyear_start_month')

    # If the user has set a global interval, use it.
    # Otherwise fall back to default year / January.
    year = int(session_year) if session_year else int(default_year)
    start_month = int(session_start) if session_start else 1


    # six-month window
        # --- Build a 6-month window, allowing wrap into the next year ---
    # Example: year=2025, start_month=10
    # window = [(2025,10), (2025,11), (2025,12), (2026,1), (2026,2), (2026,3)]
    window = []
    for offset in range(6):
        total_index = (start_month - 1) + offset  # 0-based
        y_for_month = year + (total_index // 12)
        m_for_month = (total_index % 12) + 1
        window.append((y_for_month, m_for_month))

    overview = None

    if window:
        # Build per-group data
        groups_entries = []  # for internal calculations

        for key, group in CLIENT_GROUPS.items():
            clients_qs = get_group_clients(key)

            values = []
            group_total = Decimal('0')

            for (y_m, m_m) in window:
                total = ExcelEntry.objects.filter(
                    client__in=clients_qs,
                    date__year=y_m,
                    date__month=m_m
                ).aggregate(
                    total=Coalesce(Sum('lhe_ges'), Decimal('0'))
                )['total']

                values.append(total)
                group_total += total

            groups_entries.append({
                'key': key,
                'label': group['label'],
                'values': values,
                'total': group_total,
            })

        # month totals across all groups
        month_totals = []
        for idx in range(len(window)):
            s = sum((g['values'][idx] for g in groups_entries), Decimal('0'))
            month_totals.append(s)

        grand_total = sum(month_totals, Decimal('0'))

        # Build rows for the template
        rows = []
        for idx, (y_m, m_m) in enumerate(window):
            row_values = [g['values'][idx] for g in groups_entries]
            rows.append({
                'month_number': m_m,
                'month_label': calendar.month_name[m_m],
                'values': row_values,
                'total': month_totals[idx],
            })

        groups_meta = [{'key': g['key'], 'label': g['label']} for g in groups_entries]
        group_totals = [g['total'] for g in groups_entries]

        # Start/end for display – include years so wrap is clear
        start_year = window[0][0]
        start_month_disp = window[0][1]
        end_year = window[-1][0]
        end_month_disp = window[-1][1]

        overview = {
            'year': year,  # keep for backwards compatibility if needed
            'start_month': start_month_disp,
            'start_year': start_year,
            'end_month': end_month_disp,
            'end_year': end_year,
            'rows': rows,
            'groups': groups_meta,
            'group_totals': group_totals,
            'grand_total': grand_total,
        }


    # Month dropdown labels
    MONTH_CHOICES = [
        (1, 'Jan'), (2, 'Feb'), (3, 'Mar'), (4, 'Apr'),
        (5, 'May'), (6, 'Jun'), (7, 'Jul'), (8, 'Aug'),
        (9, 'Sep'), (10, 'Oct'), (11, 'Nov'), (12, 'Dec'),
    ]

    return render(request, 'table_one.html', {
        'entries_table1': entries_table1,
        'clients': clients,
        'institutes': institutes,
        'available_years': available_years,
        'month_choices': MONTH_CHOICES,
        'overview': overview,
    })

# Table Two View (SecondTableEntry)
def table_two_view(request):
    try:
        clients = Client.objects.all().select_related('institute')
        institutes = Institute.objects.all()

        # 🔸 Read global half-year interval from session
        interval_year = request.session.get('halfyear_year')
        interval_start = request.session.get('halfyear_start_month')

        if interval_year and interval_start:
            interval_year = int(interval_year)
            interval_start = int(interval_start)

            # Build the same 6-month window as in clients_list (can cross years)
            window = []
            for offset in range(6):
                total_index = (interval_start - 1) + offset  # 0-based
                y = interval_year + (total_index // 12)
                m = (total_index % 12) + 1
                window.append((y, m))

            # Build a Q object matching any of those (year, month) pairs
            q = Q()
            for (y, m) in window:
                q |= Q(date__year=y, date__month=m)

            entries = SecondTableEntry.objects.filter(q).order_by('-date')
        else:
            # Fallback if no global interval yet: show all
            entries = SecondTableEntry.objects.all().order_by('-date')

        return render(request, 'table_two.html', {
            'entries_table2': entries,
            'clients': clients,
            'institutes': institutes,
            'interval_year': interval_year,
            'interval_start_month': interval_start,
        })

    except Exception as e:
        return render(request, 'table_two.html', {
            'error_message': f"Failed to load data: {str(e)}",
            'entries_table2': [],
            'clients': clients,
            'institutes': institutes,
        })


def monthly_sheet_root(request):
    """
    Redirect /sheet/ to the sheet matching the globally selected
    half-year start (year + month). If not set, fall back to latest.
    """
    year = request.session.get('halfyear_year')
    start_month = request.session.get('halfyear_start_month')

    if year and start_month:
        try:
            year = int(year)
            start_month = int(start_month)
            return redirect('monthly_sheet', year=year, month=start_month)
        except ValueError:
            pass  # fall through

    # Fallback: latest MonthlySheet if exists
    latest_sheet = MonthlySheet.objects.order_by('-year', '-month').first()
    if latest_sheet:
        return redirect('monthly_sheet', year=latest_sheet.year, month=latest_sheet.month)
    else:
        now = timezone.now()
        return redirect('monthly_sheet', year=now.year, month=now.month)
# Add Entry (Generic)
def add_entry(request, model_name):
    if request.method == 'POST':
        try:
            if model_name == 'SecondTableEntry':
                model = SecondTableEntry

                # Parse date
                date_str = request.POST.get('date')
                try:
                    date_obj = datetime.strptime(date_str, '%Y-%m-%d').date() if date_str else None
                except (ValueError, TypeError):
                    return JsonResponse({
                        'status': 'error',
                        'message': 'Invalid date format. Use YYYY-MM-DD'
                    }, status=400)

                # NEW: robust parse of warm flag (handles 0/1, true/false, on/off)
                raw_warm = request.POST.get('is_warm')
                is_warm = str(raw_warm).lower() in ('1', 'true', 'on', 'yes')

                lhe_delivery = request.POST.get('lhe_delivery', '')

                raw_vor = request.POST.get("vor")
                raw_nach = request.POST.get("nach")

                def d(x):
                    if x in (None, ""):
                        return None
                    try:
                        return Decimal(str(x).replace(",", "."))
                    except Exception:
                        return None

                vor = d(raw_vor)
                nach = d(raw_nach)

                # computed
                if vor is not None and nach is not None:
                    lhe_output = nach - vor
                else:
                    lhe_output = None

                entry = model.objects.create(
                    client=Client.objects.get(id=request.POST.get('client_id')),
                    date=datetime.strptime(request.POST.get('date'), '%Y-%m-%d').date() if request.POST.get('date') else None,
                    is_warm=is_warm,
                    lhe_delivery=lhe_delivery,
                    vor=vor,
                    nach=nach,
                    lhe_output=lhe_output,
                    notes=request.POST.get('notes', '')
                )

                return JsonResponse({
                    'status': 'success',
                    'id': entry.id,
                    'client_name': entry.client.name,
                    'institute_name': entry.client.institute.name,
                    'date': entry.date.strftime('%Y-%m-%d') if entry.date else '',
                    'is_warm': entry.is_warm,
                    'lhe_delivery': entry.lhe_delivery,
                    'vor': str(entry.vor) if entry.vor is not None else '',
                    'nach': str(entry.nach) if entry.nach is not None else '',
                    'lhe_output': str(entry.lhe_output) if entry.lhe_output is not None else '',
                    'notes': entry.notes,
                })


            elif model_name == 'ExcelEntry':
                model = ExcelEntry

                # Parse the date string into a date object
                date_str = request.POST.get('date')
                try:
                    date_obj = datetime.strptime(date_str, '%Y-%m-%d').date() if date_str else None
                except (ValueError, TypeError):
                    date_obj = None

                try:
                    pressure = Decimal(request.POST.get('pressure', 0))
                    purity = Decimal(request.POST.get('purity', 0))
                    druckkorrektur = Decimal(request.POST.get('druckkorrektur', 1))
                    lhe_zus = Decimal(request.POST.get('lhe_zus', 0))
                    constant_300 = Decimal(request.POST.get('constant_300'))
                    korrig_druck = Decimal(request.POST.get('korrig_druck', 0))
                    nm3 = Decimal(request.POST.get('nm3', 0))
                    lhe = Decimal(request.POST.get('lhe', 0))
                    lhe_ges = Decimal(request.POST.get('lhe_ges', 0))
                    korrig_druck = q1(korrig_druck)
                    nm3 = q1(nm3)
                    lhe = q1(lhe)
                    lhe_ges = q1(lhe_ges)
                except InvalidOperation:
                    return JsonResponse({
                        'status': 'error',
                        'message': 'Invalid numeric value in Helium Input'
                    }, status=400)

                # Create the entry with ALL fields
                entry = model.objects.create(
                    client=Client.objects.get(id=request.POST.get('client_id')),
                    date=date_obj,
                    pressure=pressure,
                    purity=purity,
                    druckkorrektur=druckkorrektur,
                    lhe_zus=lhe_zus,
                    constant_300=constant_300,
                    korrig_druck=korrig_druck,
                    nm3=nm3,
                    lhe=lhe,
                    lhe_ges=lhe_ges,
                    notes=request.POST.get('notes', '')
                )

                # Prepare the response
                response_data = {
                    'status': 'success',
                    'id': entry.id,
                    'client_name': entry.client.name,
                    'institute_name': entry.client.institute.name,
                    'pressure': str(entry.pressure),
                    'purity': str(entry.purity),
                    'druckkorrektur': str(entry.druckkorrektur),
                    'constant_300': str(entry.constant_300),
                    'korrig_druck': str(entry.korrig_druck),
                    'nm3': str(entry.nm3),
                    'lhe': str(entry.lhe),
                    'lhe_zus': str(entry.lhe_zus),
                    'lhe_ges': str(entry.lhe_ges),
                    'notes': entry.notes,
                }

                if entry.date:
                    # JS uses this for the Date column and for the Month column
                    response_data['date'] = entry.date.strftime('%Y-%m-%d')
                    response_data['month'] = f"{entry.date.month:02d}"
                else:
                    response_data['date'] = ''
                    response_data['month'] = ''

                return JsonResponse(response_data)


        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

    return JsonResponse({'status': 'error', 'message': 'Invalid request'}, status=400)

# Update Entry (Generic)
def update_entry(request, model_name):
    if request.method == 'POST':
        try:
            if model_name == 'SecondTableEntry':
                model = SecondTableEntry
            elif model_name == 'ExcelEntry':
                model = ExcelEntry
            else:
                return JsonResponse({'status': 'error', 'message': 'Invalid model'}, status=400)
                
            entry_id = int(request.POST.get('id'))
            entry = model.objects.get(id=entry_id)

            # Common updates for both models
            entry.client = Client.objects.get(id=request.POST.get('client_id'))
            entry.notes = request.POST.get('notes', '')

            # Handle date properly for both models
            date_str = request.POST.get('date')
            if date_str:
                try:
                    entry.date = datetime.strptime(date_str, '%Y-%m-%d').date()
                except ValueError:
                    return JsonResponse({
                        'status': 'error',
                        'message': 'Invalid date format. Use YYYY-MM-DD'
                    }, status=400)

            if model_name == 'SecondTableEntry':
                # Handle Helium Output specific fields

                raw_warm = request.POST.get('is_warm')
                entry.is_warm = str(raw_warm).lower() in ('1', 'true', 'on', 'yes')

                entry.lhe_delivery = request.POST.get('lhe_delivery', '')

                raw_vor = request.POST.get("vor")
                raw_nach = request.POST.get("nach")

                def d(x):
                    if x in (None, ""):
                        return None
                    try:
                        return Decimal(str(x).replace(",", "."))
                    except Exception:
                        return None

                entry.vor = d(raw_vor)     # ✅ NEW
                entry.nach = d(raw_nach)   # ✅ NEW

                # ✅ computed
                if entry.vor is not None and entry.nach is not None:
                    entry.lhe_output = entry.nach - entry.vor
                else:
                    entry.lhe_output = None

                entry.save()

                return JsonResponse({
                    'status': 'success',
                    'id': entry.id,
                    'client_name': entry.client.name,
                    'institute_name': entry.client.institute.name,
                    'date': entry.date.strftime('%Y-%m-%d') if entry.date else '',
                    'is_warm': entry.is_warm,
                    'lhe_delivery': entry.lhe_delivery,
                    'vor': str(entry.vor) if entry.vor is not None else '',
                    'nach': str(entry.nach) if entry.nach is not None else '',
                    'lhe_output': str(entry.lhe_output) if entry.lhe_output is not None else '',
                    'notes': entry.notes
                })


            elif model_name == 'ExcelEntry':
                # Handle Helium Input specific fields
                try:
                    entry.pressure = Decimal(request.POST.get('pressure', 0))
                    entry.purity = Decimal(request.POST.get('purity', 0))
                    entry.druckkorrektur = Decimal(request.POST.get('druckkorrektur', 1))
                    entry.lhe_zus = Decimal(request.POST.get('lhe_zus', 0))
                    entry.constant_300 = Decimal(request.POST.get('constant_300', 300))
                    entry.korrig_druck = q1(Decimal(request.POST.get('korrig_druck', 0)))
                    entry.nm3         = q1(Decimal(request.POST.get('nm3', 0)))
                    entry.lhe         = q1(Decimal(request.POST.get('lhe', 0)))
                    entry.lhe_ges     = q1(Decimal(request.POST.get('lhe_ges', 0)))
                except InvalidOperation:
                    return JsonResponse({
                        'status': 'error',
                        'message': 'Invalid numeric value in Helium Input'
                    }, status=400)

                entry.save()

                return JsonResponse({
                    'status': 'success',
                    'id': entry.id,
                    'client_name': entry.client.name,
                    'institute_name': entry.client.institute.name,
                    'date': entry.date.strftime('%Y-%m-%d') if entry.date else '',
                    'month': f"{entry.date.month:02d}" if entry.date else '',
                    'pressure': str(entry.pressure),
                    'purity': str(entry.purity),
                    'druckkorrektur': str(entry.druckkorrektur),
                    'constant_300': str(entry.constant_300),
                    'korrig_druck': str(entry.korrig_druck),
                    'nm3': str(entry.nm3),
                    'lhe': str(entry.lhe),
                    'lhe_zus': str(entry.lhe_zus),
                    'lhe_ges': str(entry.lhe_ges),
                    'notes': entry.notes
                })


        except model.DoesNotExist:
            return JsonResponse({'status': 'error', 'message': 'Entry not found'}, status=404)
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

    return JsonResponse({'status': 'error', 'message': 'Invalid request method'}, status=400)

# Delete Entry (Generic)
def delete_entry(request, model_name):
    if request.method == 'POST':
        try:
            if model_name == 'SecondTableEntry':
                model = SecondTableEntry
            elif model_name == 'ExcelEntry':
                model = ExcelEntry
            else:
                return JsonResponse({'status': 'error', 'message': 'Invalid model'}, status=400)
                
            entry_id = request.POST.get('id')
            entry = model.objects.get(id=entry_id)
            entry.delete()
            return JsonResponse({'status': 'success', 'message': 'Entry deleted'})

        except model.DoesNotExist:
            return JsonResponse({'status': 'error', 'message': 'Entry not found'}, status=404)
        except Exception as e:
            return JsonResponse({'status': 'error', 'message': str(e)}, status=400)

    return JsonResponse({'status': 'error', 'message': 'Invalid request'}, status=400)

def betriebskosten_list(request):
    items = Betriebskosten.objects.all().order_by('-buchungsdatum')

    summary = get_summary()
    summary.recalculate()
    

    interval_year = request.session.get("halfyear_year")
    interval_start = request.session.get("halfyear_start_month")

    interval_total_output_lhe = Decimal("0")

    if interval_year and interval_start:
        window = build_halfyear_window(int(interval_year), int(interval_start))
        for (y, m) in window:
            interval_total_output_lhe += (
                SecondTableEntry.objects.filter(
                    date__year=y,
                    date__month=m,
                ).aggregate(
                    total=Coalesce(Sum("lhe_output"), Value(0, output_field=DecimalField()))
                )["total"] or Decimal("0")
            )

    # avoid division by zero
    if interval_total_output_lhe in (None, Decimal("0")):
        verfluessigungskosten_lhe = Decimal("0")
    else:
        verfluessigungskosten_lhe = Decimal(summary.instandhaltung) / interval_total_output_lhe
    context = {
        'items': items,
        'summary': summary,
         'interval_total_output_lhe': interval_total_output_lhe,
         'verfluessigungskosten_lhe': verfluessigungskosten_lhe,
    }
    return render(request, 'betriebskosten_list.html', context)

def betriebskosten_create(request):
    if request.method != 'POST':
        return JsonResponse({'status': 'error', 'message': 'Invalid request method'})

    try:
        entry_id = request.POST.get('id')
        if entry_id:
            entry = Betriebskosten.objects.get(id=entry_id)
        else:
            entry = Betriebskosten()
        gegenstand = request.POST.get("gegenstand", "").strip()
        buchungsdatum_str = request.POST.get('buchungsdatum')
        rechnungsnummer = request.POST.get('rechnungsnummer')  # UI label: Firma
        kostentyp = request.POST.get('kostentyp')
        betrag_raw = request.POST.get('betrag')
        beschreibung = request.POST.get('beschreibung') or ''
        gas_volume_raw = request.POST.get('gas_volume')  # UI label: Gasvolumen (m³)

        # Validate required fields
        if not all([buchungsdatum_str, rechnungsnummer, kostentyp, betrag_raw]):
            return JsonResponse({'status': 'error', 'message': 'All required fields must be filled'})

        allowed = {'sach', 'helium'}
        if kostentyp not in allowed:
            return JsonResponse({'status': 'error', 'message': 'Invalid Kostentyp'})

        # Date
        buchungsdatum = parse_date(buchungsdatum_str)
        if not buchungsdatum:
            return JsonResponse({'status': 'error', 'message': 'Invalid date format'})

        # Betrag
        try:
            betrag = Decimal(str(betrag_raw))
        except Exception:
            return JsonResponse({'status': 'error', 'message': 'Invalid Betrag'})

        # Gas volume (m³) only for helium
        gas_m3 = None
        if kostentyp == 'helium':
            if gas_volume_raw not in (None, '', 'None'):
                try:
                    gas_m3 = Decimal(str(gas_volume_raw))
                except Exception:
                    return JsonResponse({'status': 'error', 'message': 'Invalid Gasvolumen (m³)'})
        entry.gegenstand = gegenstand
        entry.buchungsdatum = buchungsdatum
        entry.rechnungsnummer = rechnungsnummer
        entry.kostentyp = kostentyp
        entry.betrag = betrag
        entry.beschreibung = beschreibung
        entry.gas_volume = gas_m3

        entry.save()
        summary = get_summary()
        summary.recalculate()

        # Computed values for UI (read-only fields)
        gas_liter = None
        price_per_m3 = None
        price_per_liter = None

        if entry.kostentyp == 'helium' and entry.gas_volume:
            # Liter = m³ / 0.75
            gas_liter = (Decimal(entry.gas_volume) / Decimal('0.75'))
            if entry.gas_volume != 0:
                price_per_m3 = (Decimal(entry.betrag) / Decimal(entry.gas_volume))
            if gas_liter != 0:
                price_per_liter = (Decimal(entry.betrag) / gas_liter)

        return JsonResponse({
            'status': 'success',
            'id': entry.id,
            'gegenstand': entry.gegenstand,
            'buchungsdatum': entry.buchungsdatum.strftime('%Y-%m-%d'),
            'rechnungsnummer': entry.rechnungsnummer,
            'kostentyp': entry.kostentyp,
            'kostentyp_display': entry.get_kostentyp_display(),
            'gas_volume_m3': str(entry.gas_volume) if entry.gas_volume is not None else '-',
            'gas_volume_liter': str(gas_liter.quantize(Decimal("0.01"))) if gas_liter is not None else '',
            'price_per_m3': str(price_per_m3.quantize(Decimal("0.01"))) if price_per_m3 is not None else '',
            'price_per_liter': str(price_per_liter.quantize(Decimal("0.01"))) if price_per_liter is not None else '',
            'betrag': str(entry.betrag),
            'beschreibung': entry.beschreibung or '',
        })

    except Betriebskosten.DoesNotExist:
        return JsonResponse({'status': 'error', 'message': 'Entry not found'}, status=404)
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)})


def betriebskosten_delete(request):
    if request.method == 'POST':
        entry_id = request.POST.get('id')

        if not entry_id or not entry_id.isdigit():
            return JsonResponse({
                'status': 'error',
                'message': 'Invalid ID'
            })

        try:
            entry = Betriebskosten.objects.get(id=int(entry_id))
            entry.delete()

            summary = get_summary()
            summary.recalculate()

            return JsonResponse({'status': 'success'})

        except Betriebskosten.DoesNotExist:
            return JsonResponse({
                'status': 'error',
                'message': 'Entry not found'
            })

    return JsonResponse({'status': 'error'})


def get_summary():
    summary, created = BetriebskostenSummary.objects.get_or_create(id=1)
    return summary



def update_personalkosten(request):
    if request.method == "POST":
        value = request.POST.get("personalkosten")
        summary = get_summary()
        summary.personalkosten = Decimal(value or "0")
        summary.recalculate()
        return JsonResponse({
            "status": "success",
            "umlage_personal": str(summary.umlage_personal),
        })
    return JsonResponse({"status": "error"})


class CheckSheetView(View):
    def get(self, request):
        # Get current month/year
        current_year = datetime.now().year
        current_month = datetime.now().month
        
        # Get all sheets
        sheets = MonthlySheet.objects.all()
        
        sheet_data = []
        for sheet in sheets:
            cells_count = sheet.cells.count()
            # Count non-empty cells
            non_empty = sheet.cells.exclude(value__isnull=True).count()
            
            sheet_data.append({
                'id': sheet.id,
                'year': sheet.year,
                'month': sheet.month,
                'month_name': calendar.month_name[sheet.month],
                'total_cells': cells_count,
                'non_empty_cells': non_empty,
                'has_data': non_empty > 0
            })
        
        # Also check what the default view would show
        default_sheet = MonthlySheet.objects.filter(
            year=current_year,
            month=current_month
        ).first()
        
        return JsonResponse({
            'current_year': current_year,
            'current_month': current_month,
            'current_month_name': calendar.month_name[current_month],
            'default_sheet_exists': default_sheet is not None,
            'default_sheet_id': default_sheet.id if default_sheet else None,
            'sheets': sheet_data,
            'total_sheets': len(sheet_data)
        })



    
class TestFormulaView(View):
    def get(self, request):
        # Test the formula evaluation directly
        test_values = {
            8: 2,   # Row 9 value (0-based index 8)
            9: 2,   # Row 10 value (0-based index 9)
        }
        
        # Test formula "9 + 8" (using 0-based indices)
        formula = "9 + 8"
        result = evaluate_formula(formula, test_values)
        
        return JsonResponse({
            'test_values': test_values,
            'formula': formula,
            'result': str(result),
            'note': 'Formula uses 0-based indices. 9=Row10, 8=Row9'
        })



def halfyear_settings(request):
    """
    Global settings page: choose a year + first month for the 6-month interval.
    These values are stored in the session and used by other views.
    """
    # Determine available years from your data (use ExcelEntry or SecondTableEntry)
    # Here I use SecondTableEntry; you can switch to ExcelEntry if you prefer.
    available_years_qs = SecondTableEntry.objects.dates('date', 'year', order='DESC')
    available_years = [d.year for d in available_years_qs]

    if not available_years:
        available_years = [timezone.now().year]

    # Defaults (if nothing in session yet)
    default_year = request.session.get('halfyear_year', available_years[0])
    default_start_month = request.session.get('halfyear_start_month', 1)

    if request.method == 'POST':
        year = int(request.POST.get('year', default_year))
        start_month = int(request.POST.get('start_month', default_start_month))

        request.session['halfyear_year'] = year
        request.session['halfyear_start_month'] = start_month

        # Redirect back to where user came from, or to this page again
        next_url = request.POST.get('next') or request.GET.get('next') or 'halfyear_settings'
        return redirect(next_url)

    # Month choices for the dropdown
    month_choices = [(i, calendar.month_name[i]) for i in range(1, 13)]

    context = {
        'available_years': available_years,
        'selected_year': int(default_year),
        'selected_start_month': int(default_start_month),
        'month_choices': month_choices,
    }
    return render(request, 'halfyear_settings.html', context)