#!/usr/bin/env python3
import cgi, sys
sys.path.insert(0, '/opt/ngon/apps')
from managers.auth_manager import AuthManager, generate_login_page_html

_form = cgi.FieldStorage()
_auth = AuthManager('mara_report')
_auth_required, _should_exit, _headers = _auth.require_auth(_form)

if _should_exit:
    print("Content-Type: application/json")
    if _headers:
        print(_headers)
    print("")
    if _auth_required:
        print('{"success": false, "error": "Authentication required"}')
    else:
        print('{"success": true}')
    sys.exit(0)

if _auth_required:
    print("Content-Type: text/html")
    print("")
    print(generate_login_page_html("MARA Report"))
    sys.exit(0)


import cgi
import cgitb
import sys
import json
import requests
from datetime import datetime, timedelta
from collections import defaultdict
import os

# Import the config manager
sys.path.insert(0, '/opt/ngon/apps')
from managers.config_manager import config

# Enable CGI error reporting for debugging
cgitb.enable()

# InfluxDB Configuration Constants
INFLUX_TOKEN = 'rehPqmCSlCkQnEAaFQEX75JD7J9lAsflaIS6TTYnIh3SetVyzEumX_dRMizxFEsEISG4VUdTd70rUHcJig7V6w=='
INFLUX_ORG = "ngonsolutions"
INFLUX_URL = "https://us-central1-1.gcp.cloud2.influxdata.com"
INFLUX_HASHRATE_BUCKET = 'MARAGON_Hashrate'
INFLUX_GENERATORS_BUCKET = 'MARAGON_Generators'

print("Content-Type: text/html\n")

# Get form parameters
form = cgi.FieldStorage()
start_date_str = form.getvalue('start_date', '')
end_date_str = form.getvalue('end_date', '')
export_format = form.getvalue('export', '')

# Default to empty if no dates provided (don't auto-load)
if not start_date_str:
    start_date_str = ''
if not end_date_str:
    end_date_str = ''

def get_daily_generator_archives(start_date, end_date):
    """Load daily generator-to-site mappings from archives"""
    daily_mappings = {}
    
    current_date = start_date
    while current_date <= end_date:
        date_str = current_date.strftime('%Y-%m-%d')
        archive_path = f'/opt/ngon/data/gen_status_saves/{date_str}.txt'
        
        if os.path.exists(archive_path):
            try:
                with open(archive_path, 'r') as f:
                    daily_mappings[date_str] = {}
                    for line in f:
                        line = line.strip()
                        if not line or ',' not in line:
                            continue
                        
                        parts = line.split(',')
                        site_pod = parts[0].strip()
                        
                        # Skip special entries
                        if site_pod in ('Out of Service', 'Spares'):
                            continue
                            
                        # Extract site name (remove trailing pod number/letter pattern)
                        import re
                        # Remove patterns like " 1", " 2A", " 1A", " 1B", etc. from end
                        site_name = re.sub(r'\s+\d+[A-Z]*$', '', site_pod).strip()
                        
                        # Map generators to this site
                        for gen_entry in parts[1:]:
                            gen_id = gen_entry.split(':')[0].strip()
                            if gen_id:
                                daily_mappings[date_str][gen_id] = site_name
                                
            except Exception as e:
                print(f"<!-- Error reading {archive_path}: {e} -->")
        
        current_date += timedelta(days=1)
    
    return daily_mappings

def get_gen_max_kw_map():
    """Build {gen_id: max_gen_kw} lookup from master_config generator groups"""
    result = {}
    master = getattr(config, '_master_config', None)
    if not master:
        return result
    for site_data in master.get("sites", {}).values():
        for group_data in site_data.get("generator_groups", {}).values():
            max_kw = group_data.get("max_gen_kw", 330)
            for gen_id in group_data.get("generators", {}):
                result[gen_id] = max_kw
    return result


def get_oos_generators_with_energy(start_date, end_date):
    """Find generators that consumed energy while marked as Out of Service"""
    # Track OOS periods per generator: {gen_id: [(start_date, end_date), ...]}
    oos_periods = defaultdict(list)

    # Scan daily archives to build OOS periods per generator
    current_date = start_date
    gen_oos_days = defaultdict(list)  # {gen_id: [date1, date2, ...]}

    while current_date <= end_date:
        date_str = current_date.strftime('%Y-%m-%d')
        archive_path = f'/opt/ngon/data/gen_status_saves/{date_str}.txt'

        if os.path.exists(archive_path):
            try:
                with open(archive_path, 'r') as f:
                    for line in f:
                        line = line.strip()
                        if line.startswith('Out of Service,'):
                            parts = line.split(',')
                            for gen_entry in parts[1:]:
                                gen_id = gen_entry.split(':')[0].strip()
                                if gen_id:
                                    gen_oos_days[gen_id].append(current_date)
            except Exception as e:
                print(f"<!-- Error reading {archive_path} for OOS check: {e} -->")

        current_date += timedelta(days=1)

    if not gen_oos_days:
        return []

    # Convert daily OOS entries into continuous periods
    for gen_id, days in gen_oos_days.items():
        days.sort()
        period_start = days[0]
        period_end = days[0]

        for day in days[1:]:
            if (day - period_end).days == 1:
                # Consecutive day, extend period
                period_end = day
            else:
                # Gap found, save current period and start new one
                oos_periods[gen_id].append((period_start, period_end))
                period_start = day
                period_end = day

        # Save final period
        oos_periods[gen_id].append((period_start, period_end))

    print(f"<!-- Debug: Found {len(oos_periods)} generators with OOS periods -->")

    # Query InfluxDB for energy consumption during OOS periods only
    oos_with_energy = []
    client = create_influx_client()

    for gen_id, periods in oos_periods.items():
        total_oos_energy = 0
        oos_date_ranges = []

        for period_start, period_end in periods:
            start_str = period_start.strftime('%Y-%m-%dT00:00:00Z')
            end_str = period_end.strftime('%Y-%m-%dT23:59:59Z')

            try:
                query = f'''
                from(bucket: "{INFLUX_GENERATORS_BUCKET}")
                  |> range(start: {start_str}, stop: {end_str})
                  |> filter(fn: (r) => r["_measurement"] == "{gen_id}")
                  |> filter(fn: (r) => r["_field"] == "energy_meter")
                  |> filter(fn: (r) => r._value > 0)
                  |> sort(columns: ["_time"])
                '''

                result = client.query_api().query(query, org=INFLUX_ORG)

                readings = []
                for table in result:
                    for record in table.records:
                        readings.append({
                            'value': record.get_value(),
                            'time': record.get_time()
                        })

                if len(readings) >= 2:
                    first_reading = readings[0]['value']
                    last_reading = readings[-1]['value']
                    energy_wh = last_reading - first_reading

                    if energy_wh > 0:
                        total_oos_energy += energy_wh
                        oos_date_ranges.append(f"{period_start.strftime('%m/%d')}-{period_end.strftime('%m/%d')}")

            except Exception as e:
                print(f"<!-- Debug: Error checking OOS period for {gen_id}: {e} -->")

        energy_kwh = total_oos_energy / 1000

        # Only include if >10 kWh consumed while OOS
        if energy_kwh > 10:
            oos_with_energy.append({
                'gen_id': gen_id,
                'energy_kwh': energy_kwh,
                'oos_periods': ', '.join(oos_date_ranges),
                'num_periods': len(periods)
            })
            print(f"<!-- Debug: OOS gen {gen_id} consumed {energy_kwh:.1f} kWh while OOS -->")

    client.close()

    # Sort by energy consumption descending
    oos_with_energy.sort(key=lambda x: x['energy_kwh'], reverse=True)

    return oos_with_energy

def get_site_to_pods_mapping():
    """Get mapping of site names to their pod names"""
    try:
        pod_to_gens = config.get_generator_to_pod_mapping()
        site_to_pods = defaultdict(list)
        
        for pod_name in pod_to_gens.keys():
            if pod_name in ("Out of Service", "Spares"):
                continue
                
            # Extract site name from pod name
            # Pattern: "Alpha 1" -> "Alpha", "John" -> "John"
            parts = pod_name.split()
            if len(parts) > 1 and parts[-1].isdigit():
                site_name = ' '.join(parts[:-1])
            elif len(parts) > 1 and parts[-1] in ('1A', '1B', '2A', '2B', '3A', '3B', '4A', '4B'):
                site_name = ' '.join(parts[:-1])
            else:
                site_name = pod_name
                
            site_to_pods[site_name].append(pod_name)
            
        return dict(site_to_pods)
    except Exception as e:
        print(f"<!-- Error loading pod mapping: {e} -->")
        return {}

def get_current_miners_installed():
    """Get current miners installed per site and pod from Status API"""
    site_to_pods = get_site_to_pods_mapping()
    site_miners_installed = defaultdict(int)
    pod_miners_installed = {}
    
    try:
        # Get current status data from local API
        response = requests.get('http://localhost:5050/api/status', verify=False, timeout=30)
        if response.status_code == 200:
            status_data = response.json()
            
            for site_name, pod_list in site_to_pods.items():
                if site_name in ("Out of Service", "Spares"):
                    continue
                
                site_total_installed = 0
                
                # Look through all sites in status API to find pods
                for api_site_name, site_data in status_data.get('sites', {}).items():
                    if 'generator_groups' in site_data:
                        for group_name, group_data in site_data['generator_groups'].items():
                            if 'pods' in group_data:
                                for pod_name, pod_data in group_data['pods'].items():
                                    if pod_name in pod_list:
                                        installed = pod_data.get('stats', {}).get('miners_installed', 0)
                                        site_total_installed += installed
                                        pod_miners_installed[pod_name] = installed
                                        print(f"<!-- Debug: Found pod {pod_name} in site {site_name} with {installed} miners installed -->")
                
                if site_total_installed > 0:
                    site_miners_installed[site_name] = site_total_installed
                    print(f"<!-- Debug: Site {site_name} total installed: {site_total_installed} miners -->")
        
        else:
            print(f"<!-- Debug: Status API error: HTTP {response.status_code} -->")
    
    except Exception as e:
        print(f"<!-- Debug: Error getting current miners installed: {e} -->")
    
    return dict(site_miners_installed), pod_miners_installed

def create_influx_client():
    """Create and return an InfluxDB client with standard configuration"""
    from influxdb_client import InfluxDBClient
    return InfluxDBClient(url=INFLUX_URL, token=INFLUX_TOKEN, org=INFLUX_ORG)

def execute_influx_query(query, bucket=None, debug_label=None):
    """Execute an InfluxDB query with standard error handling"""
    client = create_influx_client()
    try:
        result = client.query_api().query(query, org=INFLUX_ORG)
        if debug_label:
            print(f"<!-- Debug: {debug_label} query executed successfully -->")
        return result
    except Exception as e:
        if debug_label:
            print(f"<!-- Debug: {debug_label} query error: {e} -->")
        raise e
    finally:
        client.close()

def calculate_pod_up_miner_status_averages_bulk(pod_list, start_date, end_date):
    """Calculate pod-up averages for all status types and pods in a single optimized batch"""
    try:
        start_str = start_date.strftime('%Y-%m-%dT00:00:00Z')
        end_str = end_date.strftime('%Y-%m-%dT23:59:59Z')
        
        client = create_influx_client()
        
        # Build single bulk query for all status types and pods
        bucket_map = {
            'offline': 'MARAGON_Offline',
            'sleeping': 'MARAGON_Sleeping', 
            'zero_hash': 'MARAGON_Zero_Hash',
            'online_hashing': 'MARAGON_Online_Hashing'
        }
        
        pod_list_str = '", "'.join(pod_list)
        
        # Single bulk query for all status data across all pods
        bulk_status_query = f'''
        import "experimental/array"
        
        pods = ["{pod_list_str}"]
        
        offline_data = from(bucket: "MARAGON_Offline")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => contains(value: r._measurement, set: pods) and r._field == "count")
          |> group(columns: ["_measurement"], mode:"by")
          
        sleeping_data = from(bucket: "MARAGON_Sleeping")  
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => contains(value: r._measurement, set: pods) and r._field == "count")
          |> group(columns: ["_measurement"], mode:"by")
          
        zero_hash_data = from(bucket: "MARAGON_Zero_Hash")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => contains(value: r._measurement, set: pods) and r._field == "count")
          |> group(columns: ["_measurement"], mode:"by")
          
        online_hashing_data = from(bucket: "MARAGON_Online_Hashing")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => contains(value: r._measurement, set: pods) and r._field == "count")
          |> group(columns: ["_measurement"], mode:"by")
        
        // Combine all status data with tags
        tagged_offline = offline_data |> map(fn: (r) => ({{r with status_type: "offline"}}))
        tagged_sleeping = sleeping_data |> map(fn: (r) => ({{r with status_type: "sleeping"}}))  
        tagged_zero_hash = zero_hash_data |> map(fn: (r) => ({{r with status_type: "zero_hash"}}))
        tagged_online = online_hashing_data |> map(fn: (r) => ({{r with status_type: "online_hashing"}}))
        
        array.from(rows: [{{}}])
          |> map(fn: (r) => 
            array.concat(arrays: [
              tagged_offline |> tableFind(fn: (key) => true) |> getRecord(idx: 0),
              tagged_sleeping |> tableFind(fn: (key) => true) |> getRecord(idx: 0),
              tagged_zero_hash |> tableFind(fn: (key) => true) |> getRecord(idx: 0), 
              tagged_online |> tableFind(fn: (key) => true) |> getRecord(idx: 0)
            ])
          )
          |> yield()
        '''
        
        # Execute simplified per-pod queries instead
        pod_results = {}
        for pod_name in pod_list:
            pod_results[pod_name] = {'offline': 0, 'sleeping': 0, 'zero_hash': 0, 'online_hashing': 0}
            
            # Get just the averages directly from InfluxDB using aggregation
            for status_type, bucket in bucket_map.items():
                try:
                    avg_query = f'''
                    from(bucket: "{bucket}")
                      |> range(start: {start_str}, stop: {end_str})
                      |> filter(fn: (r) => r._measurement == "{pod_name}" and r._field == "count")
                      |> mean()
                    '''
                    
                    result = client.query_api().query(avg_query, org=INFLUX_ORG)
                    for table in result:
                        for record in table.records:
                            avg_value = record.get_value()
                            if avg_value is not None:
                                pod_results[pod_name][status_type] = avg_value
                                break
                                
                except Exception as e:
                    print(f"<!-- Debug: Error getting {status_type} average for {pod_name}: {e} -->")
        
        client.close()
        print(f"<!-- Debug: Bulk pod-up calculation completed for {len(pod_list)} pods -->")
        return pod_results
        
    except Exception as e:
        print(f"<!-- Debug: Error in bulk pod-up calculation: {e} -->")
        return {pod: {'offline': 0, 'sleeping': 0, 'zero_hash': 0, 'online_hashing': 0} for pod in pod_list}

def calculate_pod_up_miner_status_average(pod_name, start_date, end_date, interval, status_type):
    """Legacy function - now just calls simplified average calculation"""
    try:
        start_str = start_date.strftime('%Y-%m-%dT00:00:00Z')
        end_str = end_date.strftime('%Y-%m-%dT23:59:59Z')
        
        bucket_map = {
            'offline': 'MARAGON_Offline',
            'sleeping': 'MARAGON_Sleeping', 
            'zero_hash': 'MARAGON_Zero_Hash',
            'online_hashing': 'MARAGON_Online_Hashing'
        }
        
        if status_type not in bucket_map:
            return 0
            
        client = create_influx_client()
        
        # Simplified - just get the average without complex pod-up logic
        avg_query = f'''
        from(bucket: "{bucket_map[status_type]}")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => r._measurement == "{pod_name}" and r._field == "count")
          |> mean()
        '''
        
        result = client.query_api().query(avg_query, org=INFLUX_ORG)
        avg_value = 0
        
        for table in result:
            for record in table.records:
                avg_value = record.get_value() or 0
                break
        
        client.close()
        return avg_value
        
    except Exception as e:
        print(f"<!-- Debug: Error calculating {status_type} average for {pod_name}: {e} -->")
        return 0

def calculate_pod_up_offline_average(pod_name, start_date, end_date, interval):
    """Calculate average offline miners only when pod is up (online_hashing > 0)"""
    return calculate_pod_up_miner_status_average(pod_name, start_date, end_date, interval, 'offline')

def calculate_site_miner_status_averages(start_date, end_date):
    """Calculate average miner statuses per site for the date range - optimized version"""
    site_to_pods = get_site_to_pods_mapping()
    site_miner_stats = defaultdict(lambda: {'offline': 0, 'pod_up_offline': 0, 'sleeping': 0, 'zero_hash': 0, 'online_hashing': 0, 'miners_installed': 0, 'pods': {}})
    
    # Calculate report period in days
    report_days = (end_date - start_date).days + 1
    
    # Determine appropriate interval based on report duration
    if report_days <= 1:
        interval = '1h'  # Hourly for short periods
    elif report_days <= 7:
        interval = '6h'  # 6-hour intervals for weekly reports
    else:
        interval = '1d'  # Daily for longer periods
    
    try:
        # Collect all pods for bulk processing
        all_pods = []
        pod_to_site = {}
        for site_name, pod_list in site_to_pods.items():
            if site_name not in ("Out of Service", "Spares"):
                for pod_name in pod_list:
                    all_pods.append(pod_name)
                    pod_to_site[pod_name] = site_name
        
        print(f"<!-- Debug: Processing {len(all_pods)} pods in bulk for miner status averages -->")
        
        # Get bulk miner status averages for all pods at once
        bulk_results = calculate_pod_up_miner_status_averages_bulk(all_pods, start_date, end_date)
        
        # Process results by site
        for site_name, pod_list in site_to_pods.items():
            # Skip placeholder sites
            if site_name in ("Out of Service", "Spares"):
                continue
            
            site_total_offline = 0
            site_total_pod_up_offline = 0
            site_total_sleeping = 0
            site_total_zero_hash = 0
            site_total_online_hashing = 0
            pods_with_data = 0
            site_pod_data = {}
            
            for pod_name in pod_list:
                # Use bulk results instead of individual API calls
                if pod_name in bulk_results:
                    pod_stats = bulk_results[pod_name]
                    
                    # Extract averages from bulk results
                    offline_avg = pod_stats.get('offline', 0)
                    pod_up_offline_avg = pod_stats.get('offline', 0)  # Using same value for simplified logic
                    sleeping_avg = pod_stats.get('sleeping', 0)
                    zero_hash_avg = pod_stats.get('zero_hash', 0)
                    online_hashing_avg = pod_stats.get('online_hashing', 0)
                    
                    # Store individual pod data
                    site_pod_data[pod_name] = {
                        'offline': offline_avg,
                        'pod_up_offline': pod_up_offline_avg,
                        'sleeping': sleeping_avg,
                        'zero_hash': zero_hash_avg,
                        'online_hashing': online_hashing_avg
                    }
                    
                    site_total_offline += offline_avg
                    site_total_pod_up_offline += pod_up_offline_avg
                    site_total_sleeping += sleeping_avg
                    site_total_zero_hash += zero_hash_avg
                    site_total_online_hashing += online_hashing_avg
                    pods_with_data += 1
                    
                    print(f"<!-- Debug: Pod {pod_name} miner stats (bulk) - Online Hashing: {online_hashing_avg:.1f}, Offline: {offline_avg:.1f}, Sleeping: {sleeping_avg:.1f}, Zero Hash: {zero_hash_avg:.1f} -->")
                else:
                    print(f"<!-- Debug: No bulk data for pod {pod_name} -->")
            
            # Store site totals and pod data
            if pods_with_data > 0:
                site_miner_stats[site_name] = {
                    'offline': site_total_offline,
                    'pod_up_offline': site_total_pod_up_offline,
                    'sleeping': site_total_sleeping,
                    'zero_hash': site_total_zero_hash,
                    'online_hashing': site_total_online_hashing,
                    'pods': site_pod_data
                }
                print(f"<!-- Debug: Site {site_name} miner totals - Online Hashing: {site_total_online_hashing:.1f}, Offline: {site_total_offline:.1f}, Pod Up Offline: {site_total_pod_up_offline:.1f}, Sleeping: {site_total_sleeping:.1f}, Zero Hash: {site_total_zero_hash:.1f} -->")
    
    except Exception as e:
        print(f"<!-- Miner status API query error: {e} -->")
        site_miner_stats = {}
    
    return dict(site_miner_stats)

def calculate_site_hashrate_averages(start_date, end_date):
    """Calculate average hashrate per site for the date range"""
    client = create_influx_client()
    
    site_to_pods = get_site_to_pods_mapping()
    site_hashrates = defaultdict(list)
    
    start_str, end_str = format_time_range(start_date, end_date, include_end_buffer=True)
    
    try:
        # Query hashrate data for each site - calculate pod averages then sum
        site_avg_hashrates = {}
        
        for site_name, pod_list in site_to_pods.items():
            # Skip placeholder sites
            if site_name in ("Out of Service", "Spares"):
                continue
                
            print(f"<!-- Debug: Site {site_name} has pods: {pod_list} -->")
            
            pod_averages = []
            
            for pod_name in pod_list:
                query = f'''
                from(bucket: "{INFLUX_HASHRATE_BUCKET}")
                  |> range(start: {start_str}, stop: {end_str})
                  |> filter(fn: (r) => r["_measurement"] == "{pod_name}")
                  |> filter(fn: (r) => r["_field"] == "hashrate")
                  |> aggregateWindow(every: 1h, fn: mean, createEmpty: true)
                  |> fill(value: 0.0)
                  |> group(columns: [])
                  |> mean()
                '''
                
                result = client.query_api().query(query, org=INFLUX_ORG)
                
                pod_avg = 0
                for table in result:
                    for record in table.records:
                        pod_avg = record.get_value()
                        break
                
                if pod_avg is not None:
                    pod_averages.append(pod_avg)
                    print(f"<!-- Debug: Pod {pod_name} avg: {pod_avg / 1e15:.2f} PH/s -->")
                else:
                    # If no data at all, include as 0 for accurate averaging
                    pod_averages.append(0)
                    print(f"<!-- Debug: Pod {pod_name} had no data, using 0 -->")
            
            # Sum the pod averages to get total site hashrate
            site_total = sum(pod_averages)
            site_avg_hashrates[site_name] = site_total
            site_avg_ph = site_total / 1e15
            print(f"<!-- Debug: Site {site_name} total: {site_avg_ph:.2f} PH/s from {len(pod_averages)} pods -->")
                
    except Exception as e:
        print(f"<!-- InfluxDB hashrate query error: {e} -->")
        site_avg_hashrates = {}
    
    client.close()
    return site_avg_hashrates

# Global lists to track validation issues and generators with data quality issues
validation_notes = []
questionable_data_generators = []  # Any generator with fallback readings, estimations, etc.
impossible_energy_generators = []  # Gens whose reported kWh exceeds physical capacity

def add_validation_note(note):
    """Add a validation note and track questionable generators"""
    global validation_notes, questionable_data_generators
    validation_notes.append(note)
    
    # Extract generator ID from the note for tracking
    if "Gen " in note:
        gen_id = note.split("Gen ")[1].split(":")[0].strip()
        if gen_id not in questionable_data_generators:
            questionable_data_generators.append(gen_id)

def format_time_range(start_date, end_date, include_end_buffer=False):
    """Format date range for InfluxDB queries"""
    start_str = start_date.strftime('%Y-%m-%dT00:00:00Z')
    if include_end_buffer:
        end_str = (end_date + timedelta(days=1)).strftime('%Y-%m-%dT00:00:00Z')
    else:
        end_str = end_date.strftime('%Y-%m-%dT23:59:59Z')
    return start_str, end_str

def calculate_site_energy_totals(start_date, end_date):
    """Calculate total kWh per site using improved first/last reading approach"""
    client = create_influx_client()
    
    # Load daily site mappings
    daily_mappings = get_daily_generator_archives(start_date, end_date)
    print(f"<!-- Debug: Loaded {len(daily_mappings)} days of generator mappings -->")
    
    # Build generator assignment periods
    generator_periods = defaultdict(list)
    
    # Get all unique generators
    all_generators = set()
    for day_mapping in daily_mappings.values():
        all_generators.update(day_mapping.keys())
    
    print(f"<!-- Debug: Processing {len(all_generators)} generators -->")
    
    # For each generator, build timeline of site assignments
    for gen_id in all_generators:
        current_date = start_date
        current_site = None
        period_start = None
        
        while current_date <= end_date:
            date_str = current_date.strftime('%Y-%m-%d')
            
            # Get site assignment for this day
            site_for_day = None
            if date_str in daily_mappings and gen_id in daily_mappings[date_str]:
                site_for_day = daily_mappings[date_str][gen_id]
                if site_for_day in ("Out of Service", "Spares"):
                    site_for_day = None
            
            # Check for site change
            if site_for_day != current_site:
                # End current period if exists
                if current_site is not None and period_start is not None:
                    period_end = current_date - timedelta(days=1)
                    generator_periods[gen_id].append({
                        'site': current_site,
                        'start': period_start,
                        'end': period_end
                    })
                
                # Start new period
                current_site = site_for_day
                period_start = current_date if site_for_day else None
            
            current_date += timedelta(days=1)
        
        # End final period if exists
        if current_site is not None and period_start is not None:
            generator_periods[gen_id].append({
                'site': current_site,
                'start': period_start,
                'end': end_date
            })
    
    # Calculate energy consumption using first/last reading approach
    site_totals = defaultdict(float)
    total_queries = 0
    
    try:
        for gen_id, periods in generator_periods.items():
            if not periods:
                continue
                
            for period in periods:
                site_name = period['site']
                period_start = period['start']
                period_end = period['end']
                
                # Get energy consumption for this generator period
                period_kwh, queries_used = calculate_generator_period_energy(client, gen_id, period_start, period_end, INFLUX_GENERATORS_BUCKET, INFLUX_ORG)
                total_queries += queries_used
                
                if period_kwh > 0:
                    site_totals[site_name] += period_kwh
                    print(f"<!-- Debug: Gen {gen_id} at {site_name} from {period_start.strftime('%m-%d')} to {period_end.strftime('%m-%d')}: {period_kwh:.1f} kWh -->")
    
    except Exception as e:
        print(f"<!-- InfluxDB query error: {e} -->")
    
    print(f"<!-- Debug: Total InfluxDB queries: {total_queries} -->")
    print(f"<!-- Debug: Final site totals: {dict(site_totals)} -->")
    client.close()
    return dict(site_totals)

def calculate_generator_period_energy(client, gen_id, start_date, end_date, bucket, org):
    """Calculate energy consumption for a single generator period using first/last approach with validation"""
    global validation_notes
    
    start_str, end_str = format_time_range(start_date, end_date, include_end_buffer=False)
    queries_used = 0
    
    try:
        # Get all energy meter readings for the period
        query = f'''
        from(bucket: "{bucket}")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => r["_measurement"] == "{gen_id}")
          |> filter(fn: (r) => r["_field"] == "energy_meter")
          |> filter(fn: (r) => r._value > 0)
          |> sort(columns: ["_time"])
        '''
        
        result = client.query_api().query(query, org=org)
        queries_used += 1

        # Collect all readings to detect meter resets
        all_readings = []
        for table in result:
            for record in table.records:
                all_readings.append({
                    'value': record.get_value(),
                    'time': record.get_time()
                })

        first_reading = None
        last_reading = None
        first_time = None
        last_time = None

        if all_readings:
            first_reading = all_readings[0]['value']
            first_time = all_readings[0]['time']
            last_reading = all_readings[-1]['value']
            last_time = all_readings[-1]['time']

        # If no valid readings in period, try to find start reading from before period
        if first_reading is None:
            first_reading, first_time, fallback_queries = find_fallback_start_reading(client, gen_id, start_date, bucket, org)
            queries_used += fallback_queries

            if first_reading is not None:
                # Add validation note about using fallback reading
                add_validation_note(f"Gen {gen_id}: No valid energy_meter at period start, using reading from {first_time.strftime('%Y-%m-%d')} (fallback)")

        # If still no readings, return 0
        if first_reading is None or last_reading is None:
            return 0, queries_used

        # If readings are the same, likely no consumption
        if first_reading == last_reading:
            # Check if generator was actually running using engine hours validation
            engine_validation, validation_queries = validate_with_engine_hours(client, gen_id, first_time, last_time, bucket, org)
            queries_used += validation_queries

            if engine_validation:
                validation_notes.append(engine_validation)

            return 0, queries_used

        # Check for meter reset or dip within the readings (drop > 1000 Wh to filter out noise)
        if len(all_readings) >= 2:
            for i in range(1, len(all_readings)):
                if all_readings[i-1]['value'] - all_readings[i]['value'] > 1000:
                    pre_drop_value = all_readings[i-1]['value']

                    # Check if this is a temporary dip (values recover) vs a true reset
                    recovered = False
                    for j in range(i + 1, len(all_readings)):
                        if all_readings[j]['value'] >= pre_drop_value * 0.9:
                            recovered = True
                            break

                    if recovered:
                        # Dip - meter temporarily dropped but recovered, skip it
                        print(f"<!-- Debug: Gen {gen_id} meter dip on {all_readings[i]['time'].strftime('%Y-%m-%d')}, dropped to {all_readings[i]['value']:.0f} but recovered - ignoring -->")
                        continue

                    # True meter reset - calculate energy on both sides
                    post_reset_value = all_readings[i]['value']
                    reset_time = all_readings[i]['time']

                    pre_reset_energy_wh = pre_drop_value - all_readings[0]['value']
                    post_reset_energy_wh = all_readings[-1]['value'] - post_reset_value
                    total_energy_wh = pre_reset_energy_wh + post_reset_energy_wh

                    print(f"<!-- Debug: Gen {gen_id} meter reset on {reset_time.strftime('%Y-%m-%d')}, pre={pre_reset_energy_wh/1000:.1f}kWh post={post_reset_energy_wh/1000:.1f}kWh total={total_energy_wh/1000:.1f}kWh -->")

                    consumption_kwh = total_energy_wh / 1000 if total_energy_wh > 0 else 0
                    return consumption_kwh, queries_used

        # Calculate consumption (simple case - no reset detected)
        consumption_wh = last_reading - first_reading

        if consumption_wh < 0:
            # Negative consumption but couldn't find reset point in readings (edge case with fallback data)
            add_validation_note(f"Gen {gen_id}: Meter reset detected but unable to calculate split (insufficient readings), skipping period")
            return 0, queries_used

        consumption_kwh = consumption_wh / 1000
        return consumption_kwh, queries_used
        
    except Exception as e:
        print(f"<!-- Debug: Error calculating energy for {gen_id}: {e} -->")
        return 0, queries_used

def find_fallback_start_reading(client, gen_id, start_date, bucket, org):
    """Find a valid start reading by looking back in time"""
    queries_used = 0
    
    # Look back up to 90 days in 7-day increments
    for days_back in range(7, 91, 7):
        lookback_date = start_date - timedelta(days=days_back)
        lookback_start = lookback_date.strftime('%Y-%m-%dT00:00:00Z')
        lookback_end = (start_date - timedelta(days=1)).strftime('%Y-%m-%dT23:59:59Z')
        
        query = f'''
        from(bucket: "{bucket}")
          |> range(start: {lookback_start}, stop: {lookback_end})
          |> filter(fn: (r) => r["_measurement"] == "{gen_id}")
          |> filter(fn: (r) => r["_field"] == "energy_meter")
          |> filter(fn: (r) => r._value > 0)
          |> last()
        '''
        
        try:
            result = client.query_api().query(query, org=org)
            queries_used += 1
            
            for table in result:
                for record in table.records:
                    reading = record.get_value()
                    time = record.get_time()
                    if reading and reading > 0:
                        return reading, time, queries_used
        except Exception as e:
            print(f"<!-- Debug: Error in fallback search for {gen_id}: {e} -->")
    
    return None, None, queries_used

def validate_with_engine_hours(client, gen_id, start_time, end_time, bucket, org):
    """Validate energy readings using engine hours to detect missing data"""
    queries_used = 0
    
    try:
        # Get engine hours at start and end times
        start_str = start_time.strftime('%Y-%m-%dT%H:%M:%SZ')
        end_str = end_time.strftime('%Y-%m-%dT%H:%M:%SZ')
        
        # Query engine hours around start time
        start_query = f'''
        from(bucket: "{bucket}")
          |> range(start: {start_str}, stop: {(start_time + timedelta(hours=1)).strftime('%Y-%m-%dT%H:%M:%SZ')})
          |> filter(fn: (r) => r["_measurement"] == "{gen_id}")
          |> filter(fn: (r) => r["_field"] == "engine_hrs")
          |> first()
        '''
        
        # Query engine hours around end time  
        end_query = f'''
        from(bucket: "{bucket}")
          |> range(start: {(end_time - timedelta(hours=1)).strftime('%Y-%m-%dT%H:%M:%SZ')}, stop: {end_str})
          |> filter(fn: (r) => r["_measurement"] == "{gen_id}")
          |> filter(fn: (r) => r["_field"] == "engine_hrs")
          |> last()
        '''
        
        start_engine_hrs = None
        end_engine_hrs = None
        
        # Get start engine hours
        result = client.query_api().query(start_query, org=org)
        queries_used += 1
        for table in result:
            for record in table.records:
                start_engine_hrs = record.get_value()
                break
        
        # Get end engine hours
        result = client.query_api().query(end_query, org=org)
        queries_used += 1
        for table in result:
            for record in table.records:
                end_engine_hrs = record.get_value()
                break
        
        if start_engine_hrs is not None and end_engine_hrs is not None:
            engine_hrs_diff = end_engine_hrs - start_engine_hrs
            if engine_hrs_diff > 0.1:  # Generator ran for more than 6 minutes
                note = f"Gen {gen_id}: Invalid energy_meter (zero consumption) but engine_hrs increased {engine_hrs_diff:.1f} hrs from {start_time.strftime('%Y-%m-%d')} to {end_time.strftime('%Y-%m-%d')}"
                return (note, queries_used)
        
        return None, queries_used
        
    except Exception as e:
        print(f"<!-- Debug: Error validating engine hours for {gen_id}: {e} -->")
        return None, queries_used

def fetch_smart_generator_data(start_date, end_date):
    """Fetch only the essential data points we actually need"""
    client = create_influx_client()
    
    start_str, end_str = format_time_range(start_date, end_date, include_end_buffer=False)

    print(f"<!-- Debug: Smart fetch for exact date range {start_str} to {end_str} -->")
    print(f"<!-- Debug: Using bucket {INFLUX_GENERATORS_BUCKET} and org {INFLUX_ORG} -->")
    
    try:
        # Single smart query: get first/last energy readings and all power readings for the actual date range
        smart_query = f'''
        from(bucket: "{INFLUX_GENERATORS_BUCKET}")
          |> range(start: {start_str}, stop: {end_str})
          |> filter(fn: (r) => r._field == "energy_meter" or r._field == "power_avg")
        '''
        
        print(f"<!-- Debug: Executing query: {smart_query[:200]}... -->")
        result = client.query_api().query(smart_query, org=INFLUX_ORG)
        print(f"<!-- Debug: Query executed successfully -->")
        
        # Organize by generator
        generator_data = defaultdict(lambda: {'energy_readings': [], 'power_readings': []})
        
        for table in result:
            for record in table.records:
                gen_id = record.get_measurement()
                timestamp = record.get_time()
                field = record.get_field()
                value = record.get_value()
                
                if field == 'energy_meter' and value and value > 0:
                    generator_data[gen_id]['energy_readings'].append({
                        'time': timestamp,
                        'value': value
                    })
                elif field == 'power_avg' and value is not None:
                    try:
                        power_val = float(value) if value != 'NaN' else 0
                        generator_data[gen_id]['power_readings'].append({
                            'time': timestamp,
                            'value': power_val
                        })
                    except (ValueError, TypeError):
                        continue
        
        # Sort readings by time
        for gen_id in generator_data:
            generator_data[gen_id]['energy_readings'].sort(key=lambda x: x['time'])
            generator_data[gen_id]['power_readings'].sort(key=lambda x: x['time'])
        
        print(f"<!-- Debug: Found data for {len(generator_data)} generators -->")
        client.close()
        return generator_data
        
    except Exception as e:
        print(f"<!-- Error in smart fetch: {e} -->")
        client.close()
        return {}

def get_fallback_energy_reading(client, gen_id, before_date, bucket, org):
    """Get fallback energy reading only when needed"""
    # Look back 30 days max for a valid reading
    lookback_start = (before_date - timedelta(days=30)).strftime('%Y-%m-%dT00:00:00Z')
    before_str = before_date.strftime('%Y-%m-%dT00:00:00Z')
    
    fallback_query = f'''
    from(bucket: "{bucket}")
      |> range(start: {lookback_start}, stop: {before_str})
      |> filter(fn: (r) => r._measurement == "{gen_id}")
      |> filter(fn: (r) => r._field == "energy_meter")
      |> filter(fn: (r) => r._value > 0)
      |> last()
    '''
    
    try:
        result = client.query_api().query(fallback_query, org=org)
        for table in result:
            for record in table.records:
                return record.get_value(), record.get_time()
    except Exception as e:
        print(f"<!-- Debug: Error getting fallback for {gen_id}: {e} -->")
    
    return None, None

def calculate_detailed_stats_smart(start_date, end_date):
    """Calculate comprehensive statistics using bulk data fetching for optimal performance"""
    global validation_notes
    
    # Load daily site mappings
    daily_mappings = get_daily_generator_archives(start_date, end_date)
    
    # Build generator assignment periods
    generator_periods = defaultdict(list)
    
    # Get all unique generators
    all_generators = set()
    for day_mapping in daily_mappings.values():
        all_generators.update(day_mapping.keys())
    
    # For each generator, build timeline of site assignments
    for gen_id in all_generators:
        current_date = start_date
        current_site = None
        period_start = None
        
        while current_date <= end_date:
            date_str = current_date.strftime('%Y-%m-%d')
            
            # Get site assignment for this day
            site_for_day = None
            if date_str in daily_mappings and gen_id in daily_mappings[date_str]:
                site_for_day = daily_mappings[date_str][gen_id]
                if site_for_day in ("Out of Service", "Spares"):
                    site_for_day = None
            
            # Check for site change
            if site_for_day != current_site:
                # End current period if exists
                if current_site is not None and period_start is not None:
                    period_end = current_date - timedelta(days=1)
                    generator_periods[gen_id].append({
                        'site': current_site,
                        'start': period_start,
                        'end': period_end
                    })
                
                # Start new period
                current_site = site_for_day
                period_start = current_date if site_for_day else None
            
            current_date += timedelta(days=1)
        
        # End final period if exists
        if current_site is not None and period_start is not None:
            generator_periods[gen_id].append({
                'site': current_site,
                'start': period_start,
                'end': end_date
            })
    
    # Fetch smart generator data - only what we need
    smart_data = fetch_smart_generator_data(start_date, end_date)
    
    if not smart_data:
        print("<!-- Warning: No smart data retrieved -->")
        return {}
    
    # Debug: Show sample of what we got
    sample_gen = list(smart_data.keys())[0] if smart_data else None
    if sample_gen:
        sample_energy = len(smart_data[sample_gen]['energy_readings'])
        sample_power = len(smart_data[sample_gen]['power_readings'])
        print(f"<!-- Debug: Sample {sample_gen}: {sample_energy} energy, {sample_power} power readings -->")
    
    # Calculate comprehensive stats using bulk data
    site_stats = defaultdict(lambda: {
        'total_downtime_seconds': 0,
        'total_period_seconds': 0,
        'power_readings': [],
        'generator_count': 0,
        'total_kwh': 0,
        'generators': {}  # Individual generator stats
    })
    
    # Calculate total report duration in seconds
    report_duration_seconds = (end_date - start_date + timedelta(days=1)).total_seconds()
    
    try:
        for gen_id, periods in generator_periods.items():
            if not periods or gen_id not in smart_data:
                continue
                
            gen_data = smart_data[gen_id]
            
            for period in periods:
                site_name = period['site']
                period_start = period['start']
                period_end = period['end']
                
                # Convert period dates to timezone-aware datetimes for comparison with InfluxDB data
                from datetime import timezone
                if isinstance(period_start, datetime):
                    if period_start.tzinfo is None:
                        period_start_aware = period_start.replace(tzinfo=timezone.utc)
                    else:
                        period_start_aware = period_start
                else:
                    # Convert date to datetime
                    period_start_aware = datetime.combine(period_start, datetime.min.time()).replace(tzinfo=timezone.utc)
                
                if isinstance(period_end, datetime):
                    # Set to end of day (23:59:59) to include full day's data
                    period_end_aware = period_end.replace(hour=23, minute=59, second=59, tzinfo=timezone.utc)
                else:
                    # Convert date to datetime (end of day)
                    period_end_aware = datetime.combine(period_end, datetime.max.time()).replace(tzinfo=timezone.utc)
                
                # Calculate period duration in seconds
                period_seconds = (period_end - period_start + timedelta(days=1)).total_seconds()
                
                # Process energy and power data for this period using smart data
                gen_energy_kwh = calculate_energy_from_smart_data(gen_id, period_start_aware, period_end_aware, gen_data)
                power_stats, downtime_seconds = calculate_power_downtime_from_smart_data(gen_id, period_start_aware, period_end_aware, gen_data, report_duration_seconds, gen_energy_kwh)
                
                print(f"<!-- Debug: Gen {gen_id} at {site_name}: {gen_energy_kwh:.1f} kWh, {len(power_stats)} power readings, {downtime_seconds:.0f}s downtime -->")
                
                # Initialize generator stats if not exists
                if gen_id not in site_stats[site_name]['generators']:
                    site_stats[site_name]['generators'][gen_id] = {
                        'total_kwh': 0,
                        'total_downtime_seconds': 0,
                        'total_period_seconds': 0,
                        'power_readings': []
                    }
                    site_stats[site_name]['generator_count'] += 1
                    print(f"<!-- Debug: Added {gen_id} to {site_name}, count now {site_stats[site_name]['generator_count']} -->")
                
                # Add to generator-specific stats
                site_stats[site_name]['generators'][gen_id]['total_kwh'] += gen_energy_kwh
                site_stats[site_name]['generators'][gen_id]['total_downtime_seconds'] += downtime_seconds
                site_stats[site_name]['generators'][gen_id]['total_period_seconds'] += period_seconds
                site_stats[site_name]['generators'][gen_id]['power_readings'].extend(power_stats)
                
                # Add to site totals
                site_stats[site_name]['total_kwh'] += gen_energy_kwh
                site_stats[site_name]['total_downtime_seconds'] += downtime_seconds
                site_stats[site_name]['total_period_seconds'] += period_seconds
                site_stats[site_name]['power_readings'].extend(power_stats)
    
    except Exception as e:
        print(f"<!-- Error calculating detailed stats: {e} -->")
    
    # Calculate final statistics for both sites and generators
    final_stats = {}
    print(f"<!-- Debug: Processing final stats for {len(site_stats)} sites -->")
    print(f"<!-- Debug: Sites found: {list(site_stats.keys())} -->")
    for site_name, stats in site_stats.items():
        print(f"<!-- Debug: Site {site_name} has {len(stats['generators'])} generators, count={stats['generator_count']} -->")
        # Site-level calculations
        site_total_downtime_hours = stats['total_downtime_seconds'] / 3600
        site_total_period_hours = stats['total_period_seconds'] / 3600
        
        site_uptime_percentage = 0
        if site_total_period_hours > 0:
            site_uptime_percentage = ((site_total_period_hours - site_total_downtime_hours) / site_total_period_hours) * 100
        
        site_avg_power = 0
        if stats['power_readings']:
            valid_power_readings = [p for p in stats['power_readings'] if p > 0]
            if valid_power_readings:
                site_avg_power = sum(valid_power_readings) / len(valid_power_readings)
        
        # Generator-level calculations
        generator_stats = {}
        for gen_id, gen_data in stats['generators'].items():
            gen_downtime_hours = gen_data['total_downtime_seconds'] / 3600
            gen_period_hours = gen_data['total_period_seconds'] / 3600
            
            gen_uptime_percentage = 0
            if gen_period_hours > 0:
                gen_uptime_percentage = ((gen_period_hours - gen_downtime_hours) / gen_period_hours) * 100
            
            gen_avg_power = 0
            if gen_data['power_readings']:
                valid_gen_power_readings = [p for p in gen_data['power_readings'] if p > 0]
                if valid_gen_power_readings:
                    gen_avg_power = sum(valid_gen_power_readings) / len(valid_gen_power_readings)
            
            generator_stats[gen_id] = {
                'total_kwh': gen_data['total_kwh'],
                'total_downtime_hours': gen_downtime_hours,
                'uptime_percentage': gen_uptime_percentage,
                'average_power_kw': gen_avg_power
            }
        
        final_stats[site_name] = {
            'total_kwh': stats['total_kwh'],
            'total_downtime_hours': site_total_downtime_hours,
            'uptime_percentage': site_uptime_percentage,
            'average_power_kw': site_avg_power,
            'generator_count': stats['generator_count'],
            'generators': generator_stats
        }
    
    return final_stats

def calculate_energy_from_smart_data(gen_id, start_date, end_date, gen_data):
    """Calculate energy consumption using smart pre-fetched data"""
    global validation_notes
    from influxdb_client import InfluxDBClient
    
    energy_readings = gen_data['energy_readings']
    
    # Filter to period
    period_readings = [r for r in energy_readings if start_date <= r['time'] <= end_date]
    
    print(f"<!-- Debug: Gen {gen_id} has {len(period_readings)} energy readings in period -->")
    
    # Determine first and last readings (with fallback logic)
    first_reading = None
    last_reading = None
    first_time = None
    last_time = None
    
    if period_readings:
        # Use period readings if available
        first_reading = period_readings[0]['value']
        last_reading = period_readings[-1]['value']
        first_time = period_readings[0]['time']
        last_time = period_readings[-1]['time']
    else:
        # Need to find readings - try fallback for start, but we need something in period for end
        print(f"<!-- Debug: No period readings for {gen_id}, checking for any end reading -->")
        
        # Check if there are ANY energy readings up to the end of this period
        readings_up_to_period = [r for r in energy_readings if r['time'] <= end_date]
        if not readings_up_to_period:
            print(f"<!-- Debug: No energy readings up to period end for {gen_id} -->")
            return 0

        # Look for end reading (latest reading within this period's date range)
        readings_up_to_period.sort(key=lambda x: x['time'])
        potential_end_reading = readings_up_to_period[-1]

        # If the latest reading is before our period start, we can't calculate consumption
        if potential_end_reading['time'] < start_date:
            print(f"<!-- Debug: Latest reading for {gen_id} is before period start -->")
            return 0

        last_reading = potential_end_reading['value']
        last_time = potential_end_reading['time']
        
        # Now find a start reading by going back in time
        client = create_influx_client()
        fallback_reading, fallback_time = get_fallback_energy_reading(client, gen_id, start_date, INFLUX_GENERATORS_BUCKET, INFLUX_ORG)
        client.close()
        
        if fallback_reading:
            first_reading = fallback_reading
            first_time = fallback_time
            add_validation_note(f"Gen {gen_id}: No energy_meter at period start, using reading from {fallback_time.strftime('%Y-%m-%d')} (fallback)")
        else:
            print(f"<!-- Debug: No fallback reading found for {gen_id} -->")
            return 0
    
    # Ensure we have both readings
    if first_reading is None or last_reading is None:
        print(f"<!-- Debug: Missing readings for {gen_id}: first={first_reading}, last={last_reading} -->")
        return 0
    
    print(f"<!-- Debug: Gen {gen_id} using readings: start={first_reading:.0f}Wh at {first_time.strftime('%Y-%m-%d')}, end={last_reading:.0f}Wh at {last_time.strftime('%Y-%m-%d')} -->")
    
    # Handle same readings (no consumption)
    if first_reading == last_reading:
        return 0

    # Check for meter reset or dip within period readings (drop > 1000 Wh to filter out noise)
    if len(period_readings) >= 2:
        for i in range(1, len(period_readings)):
            if period_readings[i-1]['value'] - period_readings[i]['value'] > 1000:
                pre_drop_value = period_readings[i-1]['value']

                # Check if this is a temporary dip (values recover) vs a true reset
                recovered = False
                for j in range(i + 1, len(period_readings)):
                    if period_readings[j]['value'] >= pre_drop_value * 0.9:
                        recovered = True
                        break

                if recovered:
                    # Dip - meter temporarily dropped but recovered, skip it
                    print(f"<!-- Debug: Gen {gen_id} meter dip on {period_readings[i]['time'].strftime('%Y-%m-%d')}, dropped to {period_readings[i]['value']:.0f} but recovered - ignoring -->")
                    continue

                # True meter reset - calculate energy on both sides
                post_reset_value = period_readings[i]['value']
                reset_time = period_readings[i]['time']

                pre_reset_energy_wh = pre_drop_value - period_readings[0]['value']
                post_reset_energy_wh = period_readings[-1]['value'] - post_reset_value
                total_energy_wh = pre_reset_energy_wh + post_reset_energy_wh

                print(f"<!-- Debug: Gen {gen_id} meter reset on {reset_time.strftime('%Y-%m-%d')}, pre={pre_reset_energy_wh/1000:.1f}kWh post={post_reset_energy_wh/1000:.1f}kWh total={total_energy_wh/1000:.1f}kWh -->")

                return total_energy_wh / 1000 if total_energy_wh > 0 else 0

    # Calculate consumption (simple case - no reset detected)
    consumption_wh = last_reading - first_reading

    if consumption_wh < 0:
        # Negative consumption but couldn't find reset point in readings (edge case with fallback data)
        add_validation_note(f"Gen {gen_id}: Meter reset detected but unable to calculate split (insufficient readings), skipping period")
        return 0

    return consumption_wh / 1000  # Convert Wh to kWh

def calculate_power_downtime_from_smart_data(gen_id, start_date, end_date, gen_data, report_duration_seconds, energy_kwh=None):
    """Calculate power stats and downtime using smart pre-fetched data"""
    global validation_notes
    power_readings = gen_data['power_readings']
    
    # Filter to period
    period_power = [r for r in power_readings if start_date <= r['time'] <= end_date]
    
    print(f"<!-- Debug: Gen {gen_id} has {len(period_power)} power readings in period -->")
    
    if not period_power:
        # No power data - check if we have energy consumption to estimate
        print(f"<!-- Debug: Gen {gen_id} NO POWER DATA - estimation check: energy_kwh={energy_kwh} -->")
        if energy_kwh and energy_kwh > 0:
            # Generator consumed energy but no power readings - estimate power
            period_hours = report_duration_seconds / 3600
            estimated_power_kw = energy_kwh / period_hours
            
            print(f"<!-- Debug: Gen {gen_id} estimating power: {energy_kwh:.1f} kWh ÷ {period_hours:.1f} hrs = {estimated_power_kw:.3f} kW -->")
            
            # Format the power value appropriately - use more precision for low values
            if estimated_power_kw < 0.1:
                power_str = f"{estimated_power_kw:.3f} kW ({estimated_power_kw*1000:.0f} W)"
            else:
                power_str = f"{estimated_power_kw:.1f} kW"
            
            # Only add estimation note if we don't already have a fallback note for this generator
            existing_notes = [note for note in validation_notes if gen_id in note]
            if not any("fallback" in note for note in existing_notes):
                add_validation_note(f"Gen {gen_id}: No power_avg data found, estimated {power_str} based on energy consumption (assumed 100% uptime)")
            else:
                # Update the existing fallback note to include power estimation
                for i, note in enumerate(validation_notes):
                    if gen_id in note and "fallback" in note:
                        validation_notes[i] = note + f", estimated {power_str} from energy consumption"
                        break
            
            # Return estimated power with 0 downtime (100% uptime)
            return [estimated_power_kw], 0
        else:
            # No power data and no energy consumption, assume down the whole period
            print(f"<!-- Debug: Gen {gen_id} NO power data and NO energy consumption - marking as down -->")
            return [], report_duration_seconds
    
    # Has power readings - calculate normal downtime
    print(f"<!-- Debug: Gen {gen_id} HAS POWER DATA - calculating normal downtime -->")
    
    # Calculate downtime
    total_downtime_seconds = 0
    last_time = None
    last_status = None
    downtime_start = None
    
    # Convert end_date to timezone-aware datetime for comparison  
    if isinstance(end_date, datetime):
        end_time = end_date
    else:
        from datetime import timezone
        end_time = datetime.combine(end_date, datetime.min.time()).replace(tzinfo=timezone.utc)
    
    for reading in period_power:
        current_time = reading['time']
        power_value = reading['value']
        
        # Determine current status
        current_status = "down" if power_value == 0 else "running"
        
        # If this is the first entry and it's down, start counting downtime
        if last_time is None and current_status == "down":
            downtime_start = current_time
        
        # Transition from running to down - start downtime period
        if current_status == "down" and last_status == "running":
            downtime_start = current_time
        
        # Transition from down to running - end downtime period
        if current_status == "running" and last_status == "down" and downtime_start is not None:
            downtime_duration = (current_time - downtime_start).total_seconds()
            total_downtime_seconds += downtime_duration
            downtime_start = None
        
        last_time = current_time
        last_status = current_status
    
    # If still in downtime at end of period, count remaining time
    if downtime_start is not None and last_time is not None:
        remaining_downtime = (end_time - downtime_start).total_seconds()
        total_downtime_seconds += remaining_downtime
    
    # Extract valid power readings for averaging
    valid_power_readings = [r['value'] for r in period_power if r['value'] > 0]
    
    return valid_power_readings, total_downtime_seconds

# Removed redundant get_generator_power_and_downtime function - functionality is now in calculate_power_downtime_from_smart_data

# Removed redundant calculate_generator_downtime function - functionality is now in calculate_power_downtime_from_smart_data

# Import navigation functions
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from links import generate_header_html, generate_dropdown_css, generate_dropdown_js, generate_dropdown_html

# Generate header HTML with controls
additional_controls = f"""
    <div class="header-controls">
        <form id="reportForm" style="display: flex; gap: 15px; align-items: center; flex-wrap: wrap;">
            <div style="display: flex; gap: 10px; align-items: center;">
                <label for="start_date" style="color: #ccc; font-weight: 500;">Start Date:</label>
                <input type="date" id="start_date" name="start_date" value="{start_date_str}" style="padding: 8px; border: 1px solid #3a3a3a; border-radius: 4px; background: #2a2a2a; color: #e0e0e0;">
            </div>
            <div style="display: flex; gap: 10px; align-items: center;">
                <label for="end_date" style="color: #ccc; font-weight: 500;">End Date:</label>
                <input type="date" id="end_date" name="end_date" value="{end_date_str}" style="padding: 8px; border: 1px solid #3a3a3a; border-radius: 4px; background: #2a2a2a; color: #e0e0e0;">
            </div>
            <button type="submit" style="padding: 8px 16px; background: #00ff00; color: #000; border: none; border-radius: 4px; font-weight: 600; cursor: pointer;">Generate Report</button>
        </form>
    </div>
"""

# Generate header HTML manually to include description and spacing
header_html = f'''
<div class="header" style="display: flex; justify-content: space-between; align-items: flex-start; flex-wrap: wrap; gap: 20px;">
    <div>
        <div class="dropdown">
            <h1 class="dropdown-title">NGON Mining - MARA Report</h1>
            <div class="dropdown-content">{generate_dropdown_html(AuthManager.get_user_access())}</div>
        </div>
        <p style="color: #888; font-size: 14px; margin: 6px 0 0 0;">Comprehensive generator and miner performance reporting</p>
    </div>
    {additional_controls}
</div>
'''

# HTML header with modern styling matching index.py
print(f"""<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Maragon Mining Report</title>
    
    <style>
        {generate_dropdown_css()}
    </style>
    <style>
        * {{
            box-sizing: border-box;
            margin: 0;
            padding: 0;
        }}
        
        body {{
            font-family: -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif;
            background: #1a1a1a;
            color: #e0e0e0;
            line-height: 1.4;
            overflow-x: hidden;
            padding: 20px;
        }}
        
        /* Header */
        .header {{
            background-color: rgba(255,255,255,0.05);
            border-radius: 8px;
            padding: 30px;
            margin-bottom: 30px;
            border: 1px solid rgba(255,255,255,0.1);
        }}
        
        .header h1.dropdown-title {{
            color: #00ff00;
            font-size: 2.2em;
            font-weight: 600;
            margin: 3px 0 0 0;
            line-height: 1.2;
            vertical-align: baseline;
        }}
        
        /* Main content */
        .container {{
            max-width: none;
            margin: 0 auto;
        }}
        
        /* Form section */
        .form-section {{
            background-color: rgba(255,255,255,0.05);
            padding: 20px;
            margin-bottom: 20px;
            border-radius: 12px;
            border: 1px solid rgba(255,255,255,0.1);
        }}
        
        .form-section h2 {{
            color: #00ff00;
            font-size: 20px;
            margin-bottom: 15px;
            font-weight: 600;
        }}
        
        .form-row {{
            display: flex;
            gap: 20px;
            align-items: center;
            margin-bottom: 15px;
        }}
        
        .form-row label {{
            min-width: 100px;
            color: #e0e0e0;
            font-weight: 500;
        }}
        
        .form-row input {{
            padding: 10px 12px;
            border-radius: 8px;
            border: 1px solid #3a3a3a;
            background: #2a2a2a;
            color: #e0e0e0;
            font-size: 14px;
            transition: border-color 0.2s ease;
        }}
        
        .form-row input:focus {{
            outline: none;
            border-color: #00ff00;
        }}
        
        .form-row button {{
            padding: 10px 20px;
            background: #00ff00;
            color: #000;
            border: none;
            border-radius: 8px;
            cursor: pointer;
            font-weight: 600;
            transition: background-color 0.2s ease;
        }}
        
        .form-row button:hover {{
            background: #00dd00;
        }}
        
        /* Report section */
        .report-section {{
            background-color: rgba(255,255,255,0.05);
            padding: 20px;
            border-radius: 12px;
            border: 1px solid rgba(255,255,255,0.1);
        }}
        
        .report-section h2 {{
            color: #00ff00;
            font-size: 20px;
            margin-bottom: 15px;
            font-weight: 600;
        }}
        
        .report-table {{
            width: 100%;
            border-collapse: collapse;
            background: #2a2a2a;
            border-radius: 8px;
            overflow: hidden;
        }}
        
        .report-table th {{
            background: #3a3a3a;
            color: #00ff00;
            font-weight: 600;
            padding: 15px 20px;
            text-align: left;
            border-bottom: 1px solid #4a4a4a;
        }}
        
        .report-table td {{
            padding: 12px 20px;
            border-bottom: 1px solid #3a3a3a;
        }}
        
        .report-table tr:hover {{
            background: #333;
        }}
        
        .total-row {{
            background: #00ff00 !important;
            color: #000 !important;
            font-weight: 600;
        }}
        
        .total-row:hover {{
            background: #00ff00 !important;
        }}
        
        .no-data {{
            text-align: center;
            color: #888;
            font-style: italic;
            padding: 40px;
        }}
        
        .error {{
            background: #ff4444;
            color: #fff;
            padding: 15px;
            border-radius: 8px;
            margin: 20px 0;
        }}
        
        /* Expandable row styles */
        .site-row {{
            cursor: pointer;
            position: relative;
        }}
        
        .site-row:hover {{
            background: #333 !important;
        }}
        
        .expand-icon {{
            display: inline-block;
            margin-right: 8px;
            transition: transform 0.2s ease;
            color: #00ff00;
            font-weight: bold;
        }}
        
        .expanded .expand-icon {{
            transform: rotate(90deg);
        }}
        
        .generator-row {{
            background: #1a1a1a !important;
            border-left: 3px solid #00ff00;
            display: none;
        }}
        
        .generator-row.visible {{
            display: table-row;
        }}
        
        .generator-row td {{
            padding-left: 40px;
            font-size: 12px;
            color: #ccc;
            border-bottom: 1px solid #2a2a2a;
        }}
        
        .generator-row:hover {{
            background: #222 !important;
        }}
        
        .generator-name {{
            font-family: monospace;
            color: #00ff00;
        }}
        
        /* Estimated generator styling */
        .generator-row.estimated {{
            background: linear-gradient(90deg, #2a2a00 0%, #1a1a1a 100%) !important;
            border-left: 3px solid #ffaa00;
        }}
        
        .generator-row.estimated:hover {{
            background: linear-gradient(90deg, #3a3a00 0%, #222 100%) !important;
        }}
        
        .generator-row.estimated .generator-name {{
            color: #ffaa00;
        }}
        
        .estimated-badge {{
            color: #ffaa00;
            font-size: 10px;
            font-weight: bold;
            margin-left: 5px;
        }}

        .generator-row.impossible {{
            background: linear-gradient(90deg, #3a0000 0%, #1a1a1a 100%) !important;
            border-left: 3px solid #ff4444;
        }}

        .generator-row.impossible:hover {{
            background: linear-gradient(90deg, #4a0000 0%, #222 100%) !important;
        }}

        .generator-row.impossible .generator-name {{
            color: #ff4444;
        }}

        .impossible-badge {{
            color: #ff4444;
            font-size: 10px;
            font-weight: bold;
            margin-left: 5px;
        }}
        
        /* Subsection row styles */
        .subsection-row {{
            background: #2a2a2a !important;
            border-left: 3px solid #555;
            cursor: pointer;
            display: none;
        }}
        
        .subsection-row.visible {{
            display: table-row;
        }}
        
        .subsection-row:hover {{
            background: #333 !important;
        }}
        
        .subsection-icon {{
            color: #888;
            font-size: 12px;
        }}
        
        .subsection-row.expanded .subsection-icon {{
            transform: rotate(90deg);
        }}
        
        /* Pod row styles */
        .pod-row {{
            background: #1e1e1e !important;
            border-left: 3px solid #0088ff;
            display: none;
            font-size: 12px;
        }}
        
        .pod-row.visible {{
            display: table-row;
        }}
        
        .pod-row:hover {{
            background: #242424 !important;
        }}
        
        .pod-name {{
            font-family: monospace;
            color: #0088ff;
            font-size: 14px;
        }}
    </style>
</head>
<body>
""")

# Print wrapper container and header HTML with navigation  
print('<div class="container">')
print(header_html)

# Only generate report if both dates are provided AND form was submitted
if start_date_str and end_date_str and (form.getvalue('start_date') or form.getvalue('end_date')):
    try:
        # Clear global tracking lists for this report
        validation_notes = []
        questionable_data_generators = []
        impossible_energy_generators = []
        
        print(f'<!-- Debug: Received dates - start_date_str: "{start_date_str}", end_date_str: "{end_date_str}" -->')
        start_date = datetime.strptime(start_date_str, '%Y-%m-%d')
        end_date = datetime.strptime(end_date_str, '%Y-%m-%d')
        print(f'<!-- Debug: Parsed dates - start_date: {start_date}, end_date: {end_date} -->')
        print(f'<!-- Debug: Date range validation - start <= end: {start_date <= end_date} -->')
        
        print(f'<div class="report-section">')
        print(f'<h2>Maragon Report: {start_date_str} to {end_date_str}</h2>')
        
        print(f'<!-- Using smart optimized approach -->')
        
        print(f'<!-- Debug: Starting detailed stats calculation for {start_date} to {end_date} -->')
        detailed_stats = calculate_detailed_stats_smart(start_date, end_date)
        print(f'<!-- Debug: Detailed stats returned {len(detailed_stats)} sites -->')
        print(f'<!-- Debug: Detailed stats keys: {list(detailed_stats.keys())[:10]} -->')
        site_totals = {k: v.get('total_kwh', 0) for k, v in detailed_stats.items()}
        print(f'<!-- Debug: Site totals: {dict(list(site_totals.items())[:5])} -->')
            
        site_hashrates = calculate_site_hashrate_averages(start_date, end_date)
        
        # Try miner stats with error handling
        try:
            site_miner_stats = calculate_site_miner_status_averages(start_date, end_date)
            print(f'<!-- Debug: Miner stats loaded for {len(site_miner_stats)} sites -->')
        except Exception as e:
            print(f'<!-- Debug: Miner stats failed: {e} -->')
            site_miner_stats = {}  # Use empty dict if miner stats fail
        
        # Get current miners installed from Status API
        try:
            site_miners_installed, pod_miners_installed = get_current_miners_installed()
            print(f'<!-- Debug: Miners installed loaded for {len(site_miners_installed)} sites, {len(pod_miners_installed)} pods -->')
        except Exception as e:
            print(f'<!-- Debug: Miners installed failed: {e} -->')
            site_miners_installed = {}
            pod_miners_installed = {}
        print(f'<!-- Data collection complete -->')
        
        
        print(f'<!-- Debug: site_totals keys: {list(site_totals.keys())[:5]} -->')
        
        if site_totals:
            # Filter out placeholder sites and sort by total energy descending
            filtered_sites = {k: v for k, v in site_totals.items() if k not in ("Out of Service", "Spares")}
            sorted_sites = sorted(filtered_sites.items(), key=lambda x: x[1], reverse=True)
            total_kwh = sum(filtered_sites.values())
            print(f'<!-- Debug: Found {len(filtered_sites)} sites with total {total_kwh:.1f} kWh -->')
            print(f'<!-- Debug: detailed_stats has {len(detailed_stats)} sites: {list(detailed_stats.keys())} -->')
            
            print('<table class="report-table">')
            print('<thead><tr><th>Site</th><th>Total kWh</th><th>Avg Power (kW)</th><th>Gen Accum Downtime (hrs)</th><th>Gen Uptime (hrs)</th><th>Gen Uptime %</th><th>Avg Hashrate (PH/s)</th><th>Miners Installed</th><th>Hashing (Pod Up)</th><th>Offline (Pod Up)</th><th>Zero Hash (Pod Up)</th><th>Sleeping (Pod Up)</th></tr></thead>')
            print('<tbody>')
            
            total_downtime_hrs = 0
            total_period_hrs = 0
            total_avg_power = 0
            sites_with_power = 0
            
            for site_name, kwh in sorted_sites:
                hashrate = site_hashrates.get(site_name, 0)
                hashrate_ph = hashrate / 1e15  # Convert H/s to PH/s (1 PH = 10^15 H)
                
                # Get detailed stats for this site
                site_stats = detailed_stats.get(site_name, {})
                avg_power = site_stats.get('average_power_kw', 0)
                downtime_hrs = site_stats.get('total_downtime_hours', 0)
                uptime_pct = site_stats.get('uptime_percentage', 0)
                generator_count = site_stats.get('generator_count', 0)
                generators = site_stats.get('generators', {})
                
                # Calculate uptime hours from total period and downtime
                report_duration_hrs = (end_date - start_date).days * 24 + 24  # Add 1 day to include end date
                site_total_period_hrs = report_duration_hrs * generator_count
                uptime_hrs = site_total_period_hrs - downtime_hrs
                
                if avg_power > 0:
                    total_avg_power += avg_power
                    sites_with_power += 1
                total_downtime_hrs += downtime_hrs
                
                # Calculate total period hours for uptime percentage  
                # Each generator contributes (report duration * generator count) hours
                report_duration_hrs = (end_date - start_date).days * 24 + 24  # Add 1 day to include end date
                site_period_hrs = report_duration_hrs * generator_count
                total_period_hrs += site_period_hrs
                
                # Get miner status stats for this site (all based on pod up time)
                miner_stats = site_miner_stats.get(site_name, {})
                avg_hashing = miner_stats.get('online_hashing', 0)
                avg_offline = miner_stats.get('pod_up_offline', 0)  # Now using pod_up_offline as main offline metric
                avg_zero_hash = miner_stats.get('zero_hash', 0)
                avg_sleeping = miner_stats.get('sleeping', 0)
                
                # Get actual miners installed from Status API (current count, not average)
                miners_installed = site_miners_installed.get(site_name, 0)
                
                # Calculate percentages for miner status columns
                if miners_installed > 0:
                    hashing_pct = (avg_hashing / miners_installed) * 100
                    offline_pct = (avg_offline / miners_installed) * 100
                    zero_hash_pct = (avg_zero_hash / miners_installed) * 100
                    sleeping_pct = (avg_sleeping / miners_installed) * 100
                    total_pct = hashing_pct + offline_pct + zero_hash_pct + sleeping_pct
                else:
                    hashing_pct = offline_pct = zero_hash_pct = sleeping_pct = total_pct = 0
                
                # Site row with expand/collapse functionality
                print(f'<tr class="site-row" onclick="toggleSite(\'{site_name}\')">')
                pod_count = len(miner_stats.get('pods', {}))
                print(f'<td><span class="expand-icon">▶</span>{site_name} ({generator_count} gens, {pod_count} pods)</td>')
                print(f'<td>{kwh:,.1f}</td>')
                print(f'<td>{avg_power:,.1f}</td>')
                print(f'<td>{downtime_hrs:,.1f}</td>')
                print(f'<td>{uptime_hrs:,.1f}</td>')
                print(f'<td>{uptime_pct:,.1f}%</td>')
                print(f'<td>{hashrate_ph:,.2f}</td>')
                print(f'<td>{miners_installed:,.0f}</td>')
                print(f'<td>{avg_hashing:,.1f} ({hashing_pct:,.1f}%)</td>')
                print(f'<td>{avg_offline:,.1f} ({offline_pct:,.1f}%)</td>')
                print(f'<td>{avg_zero_hash:,.1f} ({zero_hash_pct:,.1f}%)</td>')
                print(f'<td>{avg_sleeping:,.1f} ({sleeping_pct:,.1f}%)</td>')
                print(f'<!-- Debug: {site_name} total percentage: {total_pct:.1f}% -->')
                print(f'</tr>')
                
                # Nested section rows (initially hidden)
                # Generators section header
                print(f'<tr class="subsection-row" data-site="{site_name}" onclick="toggleSubsection(\'{site_name}\', \'generators\')">')
                print(f'<td style="padding-left: 20px;"><span class="expand-icon subsection-icon">▶</span>Generators ({generator_count})</td>')
                print(f'<td colspan="11">Click to view individual generators</td>')
                print(f'</tr>')
                
                # Generator detail rows (initially hidden)
                sorted_generators = sorted(generators.items())
                gen_max_kw_map = get_gen_max_kw_map()
                for gen_id, gen_stats in sorted_generators:
                    gen_kwh = gen_stats.get('total_kwh', 0)
                    gen_avg_power = gen_stats.get('average_power_kw', 0)
                    gen_downtime_hrs = gen_stats.get('total_downtime_hours', 0)
                    gen_uptime_pct = gen_stats.get('uptime_percentage', 0)

                    # Calculate generator uptime hours
                    gen_total_period_hrs = report_duration_hrs  # Single generator period
                    gen_uptime_hrs = gen_total_period_hrs - gen_downtime_hrs

                    # Check if this generator has questionable data (fallback readings, estimations, etc.)
                    has_questionable_data = gen_id in questionable_data_generators

                    # Check for impossible energy: kWh greater than rated kW * hours (5% margin)
                    gen_max_kw = gen_max_kw_map.get(gen_id, 330)
                    max_possible_kwh = gen_max_kw * report_duration_hrs * 1.05
                    is_impossible_kwh = gen_kwh > max_possible_kwh
                    if is_impossible_kwh and gen_id not in [x['gen_id'] for x in impossible_energy_generators]:
                        impossible_energy_generators.append({
                            'gen_id': gen_id,
                            'site': site_name,
                            'kwh': gen_kwh,
                            'max_kwh': max_possible_kwh,
                            'max_kw': gen_max_kw,
                            'hours': report_duration_hrs,
                        })

                    classes = ["generator-row"]
                    if is_impossible_kwh:
                        classes.append("impossible")
                    elif has_questionable_data:
                        classes.append("estimated")
                    row_class = " ".join(classes)

                    print(f'<tr class="{row_class}" data-site="{site_name}" data-subsection="generators">')
                    badges = ''
                    if is_impossible_kwh:
                        badges += '<span class="impossible-badge" title="Reported kWh exceeds rated capacity">(IMPOSSIBLE)</span>'
                    if has_questionable_data:
                        badges += '<span class="estimated-badge">(EST)</span>'
                    print(f'<td class="generator-name" style="padding-left: 40px;">{gen_id}{badges}</td>')
                    if is_impossible_kwh:
                        print(f'<td style="color: #ff4444; font-weight: bold;" title="Max possible: {max_possible_kwh:,.0f} kWh ({gen_max_kw} kW × {report_duration_hrs:.0f} hrs × 1.05)">{gen_kwh:,.1f}</td>')
                    else:
                        print(f'<td>{gen_kwh:,.1f}</td>')
                    print(f'<td>{gen_avg_power:,.1f}</td>')
                    print(f'<td>{gen_downtime_hrs:,.1f}</td>')
                    print(f'<td>{gen_uptime_hrs:,.1f}</td>')
                    print(f'<td>{gen_uptime_pct:,.1f}%</td>')
                    print(f'<td>-</td>')  # No hashrate for individual generators
                    print(f'<td>-</td>')  # No miners for individual generators (installed)
                    print(f'<td>-</td>')  # No miner status for individual generators (hashing)
                    print(f'<td>-</td>')  # No miner status for individual generators (offline)
                    print(f'<td>-</td>')  # No miner status for individual generators (zero hash)
                    print(f'<td>-</td>')  # No miner status for individual generators (sleeping)
                    print(f'</tr>')
                
                # Pods section header
                pod_data = miner_stats.get('pods', {})
                print(f'<tr class="subsection-row" data-site="{site_name}" onclick="toggleSubsection(\'{site_name}\', \'pods\')">')
                print(f'<td style="padding-left: 20px;"><span class="expand-icon subsection-icon">▶</span>Pods ({len(pod_data)})</td>')
                print(f'<td colspan="11">Click to view pod miner data</td>')
                print(f'</tr>')
                
                # Pod detail rows (initially hidden)
                sorted_pods = sorted(pod_data.items())
                for pod_name_item, pod_stats in sorted_pods:
                    pod_hashing = pod_stats.get('online_hashing', 0)
                    pod_offline = pod_stats.get('pod_up_offline', 0)  # Using pod_up_offline as main offline metric
                    pod_zero_hash = pod_stats.get('zero_hash', 0)
                    pod_sleeping = pod_stats.get('sleeping', 0)
                    
                    # Get actual pod miners installed from Status API
                    pod_miners_installed_count = pod_miners_installed.get(pod_name_item, 0)
                    
                    # Calculate percentages
                    if pod_miners_installed_count > 0:
                        pod_hashing_pct = (pod_hashing / pod_miners_installed_count) * 100
                        pod_offline_pct = (pod_offline / pod_miners_installed_count) * 100
                        pod_zero_hash_pct = (pod_zero_hash / pod_miners_installed_count) * 100
                        pod_sleeping_pct = (pod_sleeping / pod_miners_installed_count) * 100
                        pod_total_pct = pod_hashing_pct + pod_offline_pct + pod_zero_hash_pct + pod_sleeping_pct
                    else:
                        pod_hashing_pct = pod_offline_pct = pod_zero_hash_pct = pod_sleeping_pct = pod_total_pct = 0
                    
                    print(f'<tr class="pod-row" data-site="{site_name}" data-subsection="pods">')
                    print(f'<td class="pod-name" style="padding-left: 40px;">{pod_name_item}</td>')
                    print(f'<td>-</td>')  # No energy data for individual pods
                    print(f'<td>-</td>')  # No power data for individual pods
                    print(f'<td>-</td>')  # No downtime data for individual pods
                    print(f'<td>-</td>')  # No uptime data for individual pods
                    print(f'<td>-</td>')  # No uptime % for individual pods
                    print(f'<td>-</td>')  # No hashrate for individual pods (could add this later)
                    print(f'<td>{pod_miners_installed_count:,.0f}</td>')  # Actual miners installed from Status API
                    print(f'<td>{pod_hashing:,.1f} ({pod_hashing_pct:,.1f}%)</td>')
                    print(f'<td>{pod_offline:,.1f} ({pod_offline_pct:,.1f}%)</td>')
                    print(f'<td>{pod_zero_hash:,.1f} ({pod_zero_hash_pct:,.1f}%)</td>')
                    print(f'<td>{pod_sleeping:,.1f} ({pod_sleeping_pct:,.1f}%)</td>')
                    print(f'<!-- Debug: {pod_name_item} total percentage: {pod_total_pct:.1f}% -->')
                    print(f'</tr>')
            
            total_hashrate = sum(site_hashrates.values())
            total_hashrate_ph = total_hashrate / 1e15  # Convert H/s to PH/s (1 PH = 10^15 H)
            overall_avg_power = total_avg_power / sites_with_power if sites_with_power > 0 else 0
            
            # Calculate overall uptime percentage
            if total_period_hrs > 0:
                overall_uptime_pct = ((total_period_hrs - total_downtime_hrs) / total_period_hrs) * 100
            else:
                overall_uptime_pct = 0
            
            print(f'<!-- Debug: Total period hrs: {total_period_hrs:.1f}, Total downtime hrs: {total_downtime_hrs:.1f}, Overall uptime: {overall_uptime_pct:.1f}% -->')
            
            # Calculate total uptime hours and miner status averages
            total_uptime_hrs = total_period_hrs - total_downtime_hrs
            
            total_hashing = sum(site_miner_stats.get(site, {}).get('online_hashing', 0) for site, _ in sorted_sites)
            total_offline = sum(site_miner_stats.get(site, {}).get('pod_up_offline', 0) for site, _ in sorted_sites)
            total_zero_hash = sum(site_miner_stats.get(site, {}).get('zero_hash', 0) for site, _ in sorted_sites)
            total_sleeping = sum(site_miner_stats.get(site, {}).get('sleeping', 0) for site, _ in sorted_sites)
            
            # Calculate total miners installed from actual Status API counts
            total_installed = sum(site_miners_installed.get(site, 0) for site, _ in sorted_sites)
            
            # Calculate overall percentages
            if total_installed > 0:
                total_hashing_pct = (total_hashing / total_installed) * 100
                total_offline_pct = (total_offline / total_installed) * 100
                total_zero_hash_pct = (total_zero_hash / total_installed) * 100
                total_sleeping_pct = (total_sleeping / total_installed) * 100
                grand_total_pct = total_hashing_pct + total_offline_pct + total_zero_hash_pct + total_sleeping_pct
            else:
                total_hashing_pct = total_offline_pct = total_zero_hash_pct = total_sleeping_pct = grand_total_pct = 0
            
            print('<tr class="total-row">')
            print(f'<td>TOTAL ({len(sorted_sites)} sites)</td>')
            print(f'<td>{total_kwh:,.1f}</td>')
            print(f'<td>{overall_avg_power:,.1f}</td>')
            print(f'<td>{total_downtime_hrs:,.1f}</td>')
            print(f'<td>{total_uptime_hrs:,.1f}</td>')
            print(f'<td>{overall_uptime_pct:,.1f}%</td>')
            print(f'<td>{total_hashrate_ph:,.2f}</td>')
            print(f'<td>{total_installed:,.0f}</td>')
            print(f'<td>{total_hashing:,.1f} ({total_hashing_pct:,.1f}%)</td>')
            print(f'<td>{total_offline:,.1f} ({total_offline_pct:,.1f}%)</td>')
            print(f'<td>{total_zero_hash:,.1f} ({total_zero_hash_pct:,.1f}%)</td>')
            print(f'<td>{total_sleeping:,.1f} ({total_sleeping_pct:,.1f}%)</td>')
            print(f'<!-- Debug: Grand total percentage: {grand_total_pct:.1f}% -->')
            print('</tr>')
            
            print('</tbody></table>')
        else:
            print('<div class="no-data">No energy data found for the selected date range.</div>')
            print(f'<!-- Debug: No data found. site_totals empty: {not site_totals} -->')
        print('</div>')
        
        # Add validation notes section if there are any issues
        if validation_notes:
            print('<div class="report-section">')
            print('<h2>Data Validation Notes</h2>')
            print(f'<p style="color: #ccc; margin-bottom: 10px;">Found {len(validation_notes)} data quality issues:</p>')
            print('<div style="background: #2a2a2a; padding: 15px; border-radius: 8px; font-family: monospace; font-size: 12px; max-height: 300px; overflow-y: auto;">')
            for i, note in enumerate(validation_notes, 1):
                # Color code different types of validation issues
                color = "#ffaa00"  # Default warning color
                if "fallback" in note.lower():
                    color = "#88ccff"  # Blue for fallback data
                elif "engine_hrs" in note:
                    color = "#ff8888"  # Red for engine hours mismatches
                elif "meter reset" in note.lower():
                    color = "#ffcc88"  # Orange for meter resets
                
                print(f'<div style="margin-bottom: 5px; color: {color};"><span style="color: #666;">{i:03d}.</span> {note}</div>')
            print('</div>')
            print('</div>')

        # Flag generators whose reported kWh exceeds physical capacity
        if impossible_energy_generators:
            print('<div class="report-section">')
            print('<h2>Impossible Energy Consumption</h2>')
            print(f'<p style="color: #ccc; margin-bottom: 10px;">Found {len(impossible_energy_generators)} generator(s) reporting more kWh than physically possible (rated kW &times; hours, +5% margin). Likely a meter glitch, control software bug, or stuck reading — investigate before trusting the totals.</p>')
            print('<table class="report-table" style="max-width: 800px;">')
            print('<thead><tr><th>Generator</th><th>Site</th><th>Reported kWh</th><th>Max Possible kWh</th><th>Rated kW</th><th>Hours</th><th>Excess</th></tr></thead>')
            print('<tbody>')
            for entry in sorted(impossible_energy_generators, key=lambda x: x['kwh'] - x['max_kwh'], reverse=True):
                excess = entry['kwh'] - entry['max_kwh']
                print('<tr>')
                print(f'<td style="color: #ff4444; font-family: monospace;">{entry["gen_id"]}</td>')
                print(f'<td>{entry["site"]}</td>')
                print(f'<td style="color: #ff4444; font-weight: bold;">{entry["kwh"]:,.1f}</td>')
                print(f'<td>{entry["max_kwh"]:,.1f}</td>')
                print(f'<td>{entry["max_kw"]}</td>')
                print(f'<td>{entry["hours"]:,.0f}</td>')
                print(f'<td>+{excess:,.1f}</td>')
                print('</tr>')
            print('</tbody></table>')
            print('</div>')

        # Check for OOS generators with significant energy consumption
        try:
            oos_with_energy = get_oos_generators_with_energy(start_date, end_date)
            if oos_with_energy:
                print('<div class="report-section">')
                print('<h2>Out of Service Generators with Energy Consumption</h2>')
                print(f'<p style="color: #ccc; margin-bottom: 10px;">Found {len(oos_with_energy)} generator(s) that consumed >10 kWh while marked as "Out of Service":</p>')
                print('<table class="report-table" style="max-width: 600px;">')
                print('<thead><tr><th>Generator</th><th>Energy While OOS (kWh)</th><th>OOS Periods</th></tr></thead>')
                print('<tbody>')
                for oos in oos_with_energy:
                    print(f'<tr>')
                    print(f'<td style="color: #ff8888; font-family: monospace;">{oos["gen_id"]}</td>')
                    print(f'<td>{oos["energy_kwh"]:,.1f}</td>')
                    print(f'<td>{oos["oos_periods"]}</td>')
                    print(f'</tr>')
                print('</tbody></table>')
                print('<p style="color: #888; margin-top: 10px; font-size: 12px;">These generators consumed energy while marked as Out of Service. They may need to be reassigned to the correct site for accurate reporting.</p>')
                print('</div>')
        except Exception as e:
            print(f'<!-- Error checking OOS generators: {e} -->')

        # CSV Export Section (only show if there was data)
        if site_totals:
            print('<div class="report-section">')
            print('<h2>CSV Export</h2>')
            print('<p>Copy and paste the data below into Google Sheets. Use separate sheets/tabs for each section.</p>')
            
            # Generator Data Section
            print('<div style="margin: 20px 0;">')
            print('<h3>Generator Data</h3>')
            print('<div style="background: #2a2a2a; padding: 15px; border: 1px solid #444; font-family: monospace; white-space: pre; font-size: 12px; overflow-x: auto;">')
            print('Site,Total_kWh,Avg_Power_kW,Gen_Downtime_hrs,Gen_Uptime_hrs,Gen_Uptime_pct', end='')
            
            # Output generator data
            for site_name, total_kwh in sorted_sites:
                site_stats = detailed_stats.get(site_name, {})
                total_downtime_hours = site_stats.get('total_downtime_hours', 0)
                uptime_percentage = site_stats.get('uptime_percentage', 0)
                average_power_kw = site_stats.get('average_power_kw', 0)
                
                # Calculate uptime hours from total period (per generator) minus downtime
                generator_count = site_stats.get('generator_count', 0)
                report_duration_hours = ((end_date - start_date).total_seconds() + 86400) / 3600
                uptime_hours = (report_duration_hours * generator_count) - total_downtime_hours
                
                print(f'\n{site_name},{total_kwh:.1f},{average_power_kw:.1f},{total_downtime_hours:.1f},{uptime_hours:.1f},{uptime_percentage:.1f}', end='')
            
            print('</div>')
            print('</div>')
            
            # Pod/Miner Data Section  
            print('<div style="margin: 20px 0;">')
            print('<h3>Pod/Miner Data</h3>')
            print('<div style="background: #2a2a2a; padding: 15px; border: 1px solid #444; font-family: monospace; white-space: pre; font-size: 12px; overflow-x: auto;">')
            print('Site,Avg_Hashrate_PH,Miners_Installed,Hashing_Pod_Up,Offline_Pod_Up,Zero_Hash_Pod_Up,Sleeping_Pod_Up', end='')
            
            # Output pod/miner data
            for site_name, total_kwh in sorted_sites:
                # Hashrate already averaged over entire period (query fills downtime with zeros)
                site_hashrate_avg = site_hashrates.get(site_name, 0) / 1e15  # Avg PH/s over period

                site_miners = site_miners_installed.get(site_name, 0)
                miner_stats = site_miner_stats.get(site_name, {})
                
                # Get miner stats (pod up only)
                hashing_pod_up = miner_stats.get('online_hashing', 0)
                offline_pod_up = miner_stats.get('pod_up_offline', 0)
                zero_hash_pod_up = miner_stats.get('zero_hash', 0)
                sleeping_pod_up = miner_stats.get('sleeping', 0)
                
                print(f'\n{site_name},{site_hashrate_avg:.2f},{site_miners},{hashing_pod_up:.1f},{offline_pod_up:.1f},{zero_hash_pod_up:.1f},{sleeping_pod_up:.1f}', end='')
            
            print('</div>')
            print('</div>')
            print('</div>')
            
    except ValueError as e:
        print(f'<div class="error">Invalid date format: {e}</div>')
    except Exception as e:
        print(f'<div class="error">Error generating report: {e}</div>')

# Add JavaScript for dropdown functionality
print("""
    <script>
    function toggleSite(siteName) {
        const siteRow = event.currentTarget;
        const subsectionRows = document.querySelectorAll(`tr.subsection-row[data-site="${siteName}"]`);
        const expandIcon = siteRow.querySelector('.expand-icon');
        
        const isExpanded = siteRow.classList.contains('expanded');
        
        if (isExpanded) {
            // Collapse site - hide all subsections and their content
            siteRow.classList.remove('expanded');
            subsectionRows.forEach(row => {
                row.classList.remove('visible');
                row.classList.remove('expanded');
                // Also hide subsection content
                const subsectionType = row.onclick.toString().includes('generators') ? 'generators' : 'pods';
                const contentRows = document.querySelectorAll(`tr[data-site="${siteName}"][data-subsection="${subsectionType}"]`);
                contentRows.forEach(contentRow => contentRow.classList.remove('visible'));
            });
        } else {
            // Expand site - show subsection headers
            siteRow.classList.add('expanded');
            subsectionRows.forEach(row => row.classList.add('visible'));
        }
    }
    
    function toggleSubsection(siteName, subsectionType) {
        const subsectionRow = event.currentTarget;
        const contentRows = document.querySelectorAll(`tr[data-site="${siteName}"][data-subsection="${subsectionType}"]`);
        const expandIcon = subsectionRow.querySelector('.subsection-icon');
        
        const isExpanded = subsectionRow.classList.contains('expanded');
        
        if (isExpanded) {
            // Collapse subsection
            subsectionRow.classList.remove('expanded');
            contentRows.forEach(row => row.classList.remove('visible'));
        } else {
            // Expand subsection
            subsectionRow.classList.add('expanded');
            contentRows.forEach(row => row.classList.add('visible'));
        }
    }
    </script>
</div>

<script>
{generate_dropdown_js()}
</script>

</body>
</html>
""")