#!/usr/bin/env python3
"""
vCenter Reporting Tool
Generate comprehensive reports on vCenter, ESXi hosts, and VMs.
Outputs to HTML and Excel formats.
"""

import argparse
import configparser
import ssl
import sys
from datetime import datetime, timedelta
from pathlib import Path

try:
    from pyVim.connect import SmartConnect, Disconnect
    from pyVmomi import vim
except ImportError:
    print("Error: pyvmomi is required. Install with: pip install pyvmomi")
    sys.exit(1)

try:
    from openpyxl import Workbook
    from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
    from openpyxl.utils import get_column_letter
except ImportError:
    print("Error: openpyxl is required. Install with: pip install openpyxl")
    sys.exit(1)


class VCenterReporter:
    """Main class for connecting to vCenter and generating reports."""

    def __init__(self, server, username, password, port=443):
        self.server = server
        self.username = username
        self.password = password
        self.port = port
        self.si = None
        self.content = None

    def connect(self):
        """Establish connection to vCenter."""
        # Disable SSL certificate verification (common in lab environments)
        context = ssl.SSLContext(ssl.PROTOCOL_TLS_CLIENT)
        context.check_hostname = False
        context.verify_mode = ssl.CERT_NONE

        try:
            self.si = SmartConnect(
                host=self.server,
                user=self.username,
                pwd=self.password,
                port=self.port,
                sslContext=context
            )
            self.content = self.si.RetrieveContent()
            print(f"Connected to vCenter: {self.server}")
            return True
        except vim.fault.InvalidLogin:
            print("Error: Invalid username or password")
            return False
        except Exception as e:
            print(f"Error connecting to vCenter: {e}")
            return False

    def disconnect(self):
        """Disconnect from vCenter."""
        if self.si:
            Disconnect(self.si)
            print("Disconnected from vCenter")

    def get_vcenter_info(self):
        """Get vCenter server information."""
        about = self.content.about

        # Get vCenter uptime from service instance
        current_time = self.si.CurrentTime()

        # Count objects
        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.ClusterComputeResource], True
        )
        cluster_count = len(container.view)
        container.Destroy()

        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.HostSystem], True
        )
        host_count = len(container.view)
        container.Destroy()

        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.VirtualMachine], True
        )
        vm_count = len(container.view)
        container.Destroy()

        return {
            'name': about.name,
            'version': about.version,
            'build': about.build,
            'full_name': about.fullName,
            'os_type': about.osType,
            'api_version': about.apiVersion,
            'instance_uuid': about.instanceUuid,
            'current_time': current_time.strftime('%Y-%m-%d %H:%M:%S'),
            'cluster_count': cluster_count,
            'host_count': host_count,
            'vm_count': vm_count
        }

    def get_hosts(self):
        """Get all ESXi host information."""
        hosts = []
        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.HostSystem], True
        )

        for host in container.view:
            try:
                summary = host.summary
                hardware = host.hardware
                runtime = host.runtime
                config = host.config

                # Calculate uptime
                boot_time = runtime.bootTime
                uptime_str = "N/A"
                if boot_time:
                    uptime = datetime.now(boot_time.tzinfo) - boot_time
                    days = uptime.days
                    hours, remainder = divmod(uptime.seconds, 3600)
                    minutes, _ = divmod(remainder, 60)
                    uptime_str = f"{days}d {hours}h {minutes}m"

                # Get management IP
                mgmt_ip = "N/A"
                for vnic in config.network.vnic:
                    if vnic.device == "vmk0":
                        mgmt_ip = vnic.spec.ip.ipAddress
                        break

                hosts.append({
                    'name': host.name,
                    'ip_address': mgmt_ip,
                    'version': summary.config.product.version,
                    'build': summary.config.product.build,
                    'full_name': summary.config.product.fullName,
                    'vendor': hardware.systemInfo.vendor,
                    'model': hardware.systemInfo.model,
                    'serial_number': hardware.systemInfo.serialNumber or "N/A",
                    'cpu_model': hardware.cpuPkg[0].description if hardware.cpuPkg else "N/A",
                    'cpu_sockets': hardware.cpuInfo.numCpuPackages,
                    'cpu_cores': hardware.cpuInfo.numCpuCores,
                    'cpu_threads': hardware.cpuInfo.numCpuThreads,
                    'memory_gb': round(hardware.memorySize / (1024**3), 2),
                    'uptime': uptime_str,
                    'connection_state': str(runtime.connectionState),
                    'power_state': str(runtime.powerState),
                    'maintenance_mode': runtime.inMaintenanceMode,
                    'overall_status': str(summary.overallStatus)
                })
            except Exception as e:
                print(f"Warning: Error processing host {host.name}: {e}")

        container.Destroy()
        return hosts

    def get_host_nics(self):
        """Get physical NIC information for all ESXi hosts."""
        host_nics = []
        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.HostSystem], True
        )

        for host in container.view:
            try:
                network_info = host.config.network

                # Get physical NICs
                for pnic in network_info.pnic:
                    link_speed = "Down"
                    if pnic.linkSpeed:
                        link_speed = f"{pnic.linkSpeed.speedMb} Mbps"

                    host_nics.append({
                        'host': host.name,
                        'device': pnic.device,
                        'driver': pnic.driver if hasattr(pnic, 'driver') else "N/A",
                        'mac': pnic.mac,
                        'link_speed': link_speed,
                        'pci': pnic.pci if hasattr(pnic, 'pci') else "N/A"
                    })
            except Exception as e:
                print(f"Warning: Error getting NICs for host {host.name}: {e}")

        container.Destroy()
        return host_nics

    def get_vms(self):
        """Get all VM information including backup-relevant details."""
        vms = []
        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.VirtualMachine], True
        )

        for vm in container.view:
            try:
                summary = vm.summary
                config_summary = summary.config
                guest = summary.guest
                runtime = vm.runtime

                # Get full config for CBT and advanced settings
                vm_config = vm.config

                # Get host name
                host_name = "N/A"
                if runtime.host:
                    host_name = runtime.host.name

                # Calculate uptime for powered-on VMs
                uptime_str = "N/A"
                if runtime.powerState == vim.VirtualMachinePowerState.poweredOn:
                    boot_time = runtime.bootTime
                    if boot_time:
                        uptime = datetime.now(boot_time.tzinfo) - boot_time
                        days = uptime.days
                        hours, remainder = divmod(uptime.seconds, 3600)
                        minutes, _ = divmod(remainder, 60)
                        uptime_str = f"{days}d {hours}h {minutes}m"

                # Calculate disk usage
                provisioned_gb = 0
                used_gb = 0
                if summary.storage:
                    provisioned_gb = round(summary.storage.committed / (1024**3) +
                                          summary.storage.uncommitted / (1024**3), 2)
                    used_gb = round(summary.storage.committed / (1024**3), 2)

                # Get guest OS info
                guest_full_name = config_summary.guestFullName or "N/A"
                guest_id = config_summary.guestId or "N/A"

                # Get tools status - use hasattr for compatibility across API versions
                tools_status = "N/A"
                tools_version = "N/A"
                if guest:
                    if hasattr(guest, 'toolsStatus'):
                        tools_status = guest.toolsStatus
                    if hasattr(guest, 'toolsVersion'):
                        tools_version = guest.toolsVersion
                    elif hasattr(guest, 'toolsVersionStatus'):
                        tools_version = guest.toolsVersionStatus
                    elif hasattr(guest, 'toolsVersionStatus2'):
                        tools_version = guest.toolsVersionStatus2

                # Check CBT (Changed Block Tracking) status
                cbt_enabled = False
                if vm_config and hasattr(vm_config, 'changeTrackingEnabled'):
                    cbt_enabled = vm_config.changeTrackingEnabled or False

                # Get snapshot count
                snapshot_count = 0
                if vm.snapshot:
                    def count_snapshots(snapshot_tree):
                        count = len(snapshot_tree)
                        for snap in snapshot_tree:
                            if snap.childSnapshotList:
                                count += count_snapshots(snap.childSnapshotList)
                        return count
                    snapshot_count = count_snapshots(vm.snapshot.rootSnapshotList)

                # Get hardware version
                hw_version = "N/A"
                if vm_config and hasattr(vm_config, 'version'):
                    hw_version = vm_config.version

                # Get datastore(s)
                datastores = []
                if vm.datastore:
                    for ds in vm.datastore:
                        datastores.append(ds.name)
                datastore_str = ", ".join(datastores) if datastores else "N/A"

                # Get disk info for backup analysis
                disk_count = 0
                disk_types = []
                if vm_config and vm_config.hardware:
                    for device in vm_config.hardware.device:
                        if isinstance(device, vim.vm.device.VirtualDisk):
                            disk_count += 1
                            backing = device.backing
                            if hasattr(backing, 'thinProvisioned') and backing.thinProvisioned:
                                disk_types.append("Thin")
                            elif hasattr(backing, 'eagerlyScrub') and backing.eagerlyScrub:
                                disk_types.append("Eager Zero")
                            else:
                                disk_types.append("Thick")

                vms.append({
                    'name': config_summary.name,
                    'power_state': str(runtime.powerState).replace('poweredOn', 'On').replace('poweredOff', 'Off'),
                    'guest_os': guest_full_name,
                    'guest_id': guest_id,
                    'vcpus': config_summary.numCpu,
                    'memory_gb': round(config_summary.memorySizeMB / 1024, 2),
                    'provisioned_gb': provisioned_gb,
                    'used_gb': used_gb,
                    'uptime': uptime_str,
                    'host': host_name,
                    'tools_status': str(tools_status) if tools_status else "N/A",
                    'tools_version': str(tools_version) if tools_version else "N/A",
                    'ip_address': guest.ipAddress if guest and guest.ipAddress else "N/A",
                    'hostname': guest.hostName if guest and guest.hostName else "N/A",
                    'annotation': config_summary.annotation or "",
                    # Backup-relevant fields
                    'cbt_enabled': cbt_enabled,
                    'snapshot_count': snapshot_count,
                    'hw_version': hw_version,
                    'datastores': datastore_str,
                    'disk_count': disk_count,
                    'disk_types': ", ".join(disk_types) if disk_types else "N/A"
                })
            except Exception as e:
                print(f"Warning: Error processing VM: {e}")

        container.Destroy()
        return sorted(vms, key=lambda x: x['name'].lower())

    def get_datastores(self):
        """Get all datastore information."""
        datastores = []
        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.Datastore], True
        )

        for ds in container.view:
            try:
                summary = ds.summary

                capacity_gb = round(summary.capacity / (1024**3), 2)
                free_gb = round(summary.freeSpace / (1024**3), 2)
                used_gb = round((summary.capacity - summary.freeSpace) / (1024**3), 2)
                used_pct = round((1 - summary.freeSpace / summary.capacity) * 100, 1) if summary.capacity > 0 else 0

                # Get provisioned space (uncommitted + committed)
                provisioned_gb = 0
                if hasattr(summary, 'uncommitted') and summary.uncommitted:
                    provisioned_gb = round((summary.capacity - summary.freeSpace + summary.uncommitted) / (1024**3), 2)
                else:
                    provisioned_gb = used_gb

                datastores.append({
                    'name': summary.name,
                    'type': summary.type,
                    'capacity_gb': capacity_gb,
                    'free_gb': free_gb,
                    'used_gb': used_gb,
                    'used_pct': used_pct,
                    'provisioned_gb': provisioned_gb,
                    'accessible': summary.accessible,
                    'maintenance_mode': summary.maintenanceMode or "normal",
                    'url': summary.url
                })
            except Exception as e:
                print(f"Warning: Error processing datastore: {e}")

        container.Destroy()
        return sorted(datastores, key=lambda x: x['name'].lower())

    def get_vm_performance(self, vm_name=None):
        """Get real-time performance stats for VMs (CPU, disk, network)."""
        perf_manager = self.content.perfManager
        vm_perf = []

        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.VirtualMachine], True
        )

        # Define the metrics we want
        # CPU: cpu.usage.average (percent * 100)
        # Disk: disk.read.average, disk.write.average (KBps)
        # Disk latency: disk.totalReadLatency.average, disk.totalWriteLatency.average (ms)
        # Network: net.received.average, net.transmitted.average (KBps)

        metric_ids = {
            'cpu.usage.average': None,
            'disk.read.average': None,
            'disk.write.average': None,
            'disk.totalReadLatency.average': None,
            'disk.totalWriteLatency.average': None,
            'net.received.average': None,
            'net.transmitted.average': None,
            'disk.maxTotalLatency.latest': None,
        }

        # Get counter IDs
        perf_counters = perf_manager.perfCounter
        for counter in perf_counters:
            full_name = f"{counter.groupInfo.key}.{counter.nameInfo.key}.{counter.rollupType}"
            if full_name in metric_ids:
                metric_ids[full_name] = counter.key

        for vm in container.view:
            if vm.runtime.powerState != vim.VirtualMachinePowerState.poweredOn:
                continue

            if vm_name and vm.name.lower() != vm_name.lower():
                continue

            try:
                # Build query spec for this VM
                metric_id_objs = []
                for name, counter_id in metric_ids.items():
                    if counter_id:
                        metric_id_objs.append(vim.PerformanceManager.MetricId(
                            counterId=counter_id,
                            instance=""
                        ))

                if not metric_id_objs:
                    continue

                query_spec = vim.PerformanceManager.QuerySpec(
                    entity=vm,
                    metricId=metric_id_objs,
                    intervalId=20,  # Real-time interval
                    maxSample=1
                )

                results = perf_manager.QueryPerf(querySpec=[query_spec])

                perf_data = {
                    'name': vm.name,
                    'cpu_usage_pct': 0,
                    'disk_read_kbps': 0,
                    'disk_write_kbps': 0,
                    'disk_read_latency_ms': 0,
                    'disk_write_latency_ms': 0,
                    'disk_max_latency_ms': 0,
                    'net_rx_kbps': 0,
                    'net_tx_kbps': 0,
                }

                if results:
                    for result in results:
                        for val in result.value:
                            counter_id = val.id.counterId
                            value = val.value[0] if val.value else 0

                            # Map counter ID back to metric name
                            for name, cid in metric_ids.items():
                                if cid == counter_id:
                                    if name == 'cpu.usage.average':
                                        perf_data['cpu_usage_pct'] = round(value / 100, 1)
                                    elif name == 'disk.read.average':
                                        perf_data['disk_read_kbps'] = value
                                    elif name == 'disk.write.average':
                                        perf_data['disk_write_kbps'] = value
                                    elif name == 'disk.totalReadLatency.average':
                                        perf_data['disk_read_latency_ms'] = value
                                    elif name == 'disk.totalWriteLatency.average':
                                        perf_data['disk_write_latency_ms'] = value
                                    elif name == 'disk.maxTotalLatency.latest':
                                        perf_data['disk_max_latency_ms'] = value
                                    elif name == 'net.received.average':
                                        perf_data['net_rx_kbps'] = value
                                    elif name == 'net.transmitted.average':
                                        perf_data['net_tx_kbps'] = value
                                    break

                vm_perf.append(perf_data)

            except Exception as e:
                print(f"Warning: Error getting performance for VM {vm.name}: {e}")

        container.Destroy()
        return sorted(vm_perf, key=lambda x: x['name'].lower())

    def get_datastore_performance(self):
        """Get datastore I/O performance stats."""
        perf_manager = self.content.perfManager
        ds_perf = []

        container = self.content.viewManager.CreateContainerView(
            self.content.rootFolder, [vim.Datastore], True
        )

        # Datastore metrics
        metric_ids = {
            'datastore.read.average': None,
            'datastore.write.average': None,
            'datastore.totalReadLatency.average': None,
            'datastore.totalWriteLatency.average': None,
        }

        # Get counter IDs
        perf_counters = perf_manager.perfCounter
        for counter in perf_counters:
            full_name = f"{counter.groupInfo.key}.{counter.nameInfo.key}.{counter.rollupType}"
            if full_name in metric_ids:
                metric_ids[full_name] = counter.key

        for ds in container.view:
            if not ds.summary.accessible:
                continue

            try:
                # Need to query from a host that has access to this datastore
                if not ds.host:
                    continue

                host = ds.host[0].key  # Get first host with access

                metric_id_objs = []
                for name, counter_id in metric_ids.items():
                    if counter_id:
                        metric_id_objs.append(vim.PerformanceManager.MetricId(
                            counterId=counter_id,
                            instance=ds.name
                        ))

                if not metric_id_objs:
                    continue

                query_spec = vim.PerformanceManager.QuerySpec(
                    entity=host,
                    metricId=metric_id_objs,
                    intervalId=20,
                    maxSample=1
                )

                results = perf_manager.QueryPerf(querySpec=[query_spec])

                perf_data = {
                    'name': ds.name,
                    'read_kbps': 0,
                    'write_kbps': 0,
                    'read_latency_ms': 0,
                    'write_latency_ms': 0,
                }

                if results:
                    for result in results:
                        for val in result.value:
                            if val.id.instance != ds.name:
                                continue
                            counter_id = val.id.counterId
                            value = val.value[0] if val.value else 0

                            for name, cid in metric_ids.items():
                                if cid == counter_id:
                                    if name == 'datastore.read.average':
                                        perf_data['read_kbps'] = value
                                    elif name == 'datastore.write.average':
                                        perf_data['write_kbps'] = value
                                    elif name == 'datastore.totalReadLatency.average':
                                        perf_data['read_latency_ms'] = value
                                    elif name == 'datastore.totalWriteLatency.average':
                                        perf_data['write_latency_ms'] = value
                                    break

                ds_perf.append(perf_data)

            except Exception as e:
                print(f"Warning: Error getting performance for datastore {ds.name}: {e}")

        container.Destroy()
        return sorted(ds_perf, key=lambda x: x['name'].lower())


def generate_html_report(vcenter_info, hosts, vms, datastores, host_nics, output_path):
    """Generate HTML report with all data including backup analysis."""

    timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

    html = f'''<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>vCenter Report - {vcenter_info['name']}</title>
    <style>
        * {{
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }}
        body {{
            font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, sans-serif;
            background: #f5f5f5;
            color: #333;
            line-height: 1.6;
            padding: 20px;
        }}
        .container {{
            max-width: 1400px;
            margin: 0 auto;
        }}
        h1 {{
            color: #1a1a2e;
            margin-bottom: 10px;
            font-size: 2em;
        }}
        h2 {{
            color: #16213e;
            margin: 30px 0 15px 0;
            padding-bottom: 10px;
            border-bottom: 2px solid #0f4c75;
        }}
        .meta {{
            color: #666;
            margin-bottom: 20px;
            font-size: 0.9em;
        }}
        .summary-grid {{
            display: grid;
            grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
            gap: 15px;
            margin-bottom: 30px;
        }}
        .summary-card {{
            background: white;
            padding: 20px;
            border-radius: 8px;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        }}
        .summary-card h3 {{
            color: #666;
            font-size: 0.85em;
            text-transform: uppercase;
            margin-bottom: 5px;
        }}
        .summary-card .value {{
            font-size: 1.8em;
            font-weight: bold;
            color: #0f4c75;
        }}
        table {{
            width: 100%;
            border-collapse: collapse;
            background: white;
            border-radius: 8px;
            overflow: hidden;
            box-shadow: 0 2px 4px rgba(0,0,0,0.1);
            margin-bottom: 30px;
        }}
        th, td {{
            padding: 12px 15px;
            text-align: left;
            border-bottom: 1px solid #eee;
        }}
        th {{
            background: #0f4c75;
            color: white;
            font-weight: 600;
            text-transform: uppercase;
            font-size: 0.85em;
        }}
        tr:hover {{
            background: #f8f9fa;
        }}
        .status-on, .status-green {{
            color: #28a745;
            font-weight: 600;
        }}
        .status-off, .status-red {{
            color: #dc3545;
            font-weight: 600;
        }}
        .status-yellow {{
            color: #ffc107;
            font-weight: 600;
        }}
        .progress-bar {{
            background: #e9ecef;
            border-radius: 4px;
            height: 20px;
            overflow: hidden;
            min-width: 100px;
        }}
        .progress-fill {{
            height: 100%;
            background: #0f4c75;
            text-align: center;
            color: white;
            font-size: 0.75em;
            line-height: 20px;
        }}
        .progress-fill.warning {{
            background: #ffc107;
        }}
        .progress-fill.danger {{
            background: #dc3545;
        }}
        @media print {{
            body {{ background: white; }}
            .summary-card, table {{ box-shadow: none; border: 1px solid #ddd; }}
        }}
    </style>
</head>
<body>
    <div class="container">
        <h1>vCenter Infrastructure Report</h1>
        <p class="meta">Generated: {timestamp} | Server: {vcenter_info['name']}</p>

        <h2>vCenter Summary</h2>
        <div class="summary-grid">
            <div class="summary-card">
                <h3>vCenter Version</h3>
                <div class="value">{vcenter_info['version']}</div>
                <div>Build {vcenter_info['build']}</div>
            </div>
            <div class="summary-card">
                <h3>Clusters</h3>
                <div class="value">{vcenter_info['cluster_count']}</div>
            </div>
            <div class="summary-card">
                <h3>ESXi Hosts</h3>
                <div class="value">{vcenter_info['host_count']}</div>
            </div>
            <div class="summary-card">
                <h3>Virtual Machines</h3>
                <div class="value">{vcenter_info['vm_count']}</div>
            </div>
        </div>
        <table>
            <tr><th>Property</th><th>Value</th></tr>
            <tr><td>Full Name</td><td>{vcenter_info['full_name']}</td></tr>
            <tr><td>API Version</td><td>{vcenter_info['api_version']}</td></tr>
            <tr><td>OS Type</td><td>{vcenter_info['os_type']}</td></tr>
            <tr><td>Instance UUID</td><td>{vcenter_info['instance_uuid']}</td></tr>
            <tr><td>Server Time</td><td>{vcenter_info['current_time']}</td></tr>
        </table>

        <h2>ESXi Hosts ({len(hosts)})</h2>
        <table>
            <tr>
                <th>Name</th>
                <th>IP Address</th>
                <th>Version</th>
                <th>Build</th>
                <th>Model</th>
                <th>CPU</th>
                <th>Memory</th>
                <th>Uptime</th>
                <th>Status</th>
            </tr>
'''

    for host in hosts:
        status_class = 'status-green' if host['connection_state'] == 'connected' else 'status-red'
        maint_badge = ' (Maint)' if host['maintenance_mode'] else ''
        html += f'''            <tr>
                <td>{host['name']}</td>
                <td>{host['ip_address']}</td>
                <td>{host['version']}</td>
                <td>{host['build']}</td>
                <td>{host['vendor']} {host['model']}</td>
                <td>{host['cpu_sockets']}x {host['cpu_cores']}c/{host['cpu_threads']}t</td>
                <td>{host['memory_gb']} GB</td>
                <td>{host['uptime']}</td>
                <td class="{status_class}">{host['connection_state']}{maint_badge}</td>
            </tr>
'''

    html += '''        </table>

        <h2>Host Physical NICs</h2>
        <table>
            <tr>
                <th>Host</th>
                <th>Device</th>
                <th>Driver</th>
                <th>MAC Address</th>
                <th>Link Speed</th>
            </tr>
'''

    for nic in host_nics:
        speed_class = 'status-green' if '10000' in nic['link_speed'] else ('status-yellow' if '1000' in nic['link_speed'] else 'status-red')
        html += f'''            <tr>
                <td>{nic['host']}</td>
                <td>{nic['device']}</td>
                <td>{nic['driver']}</td>
                <td>{nic['mac']}</td>
                <td class="{speed_class}">{nic['link_speed']}</td>
            </tr>
'''

    # Backup Analysis Section
    vms_without_cbt = [vm for vm in vms if not vm['cbt_enabled']]
    vms_with_snapshots = [vm for vm in vms if vm['snapshot_count'] > 0]

    html += '''        </table>

        <h2>Backup Analysis</h2>
        <div class="summary-grid">
            <div class="summary-card">
                <h3>VMs without CBT</h3>
                <div class="value">''' + str(len(vms_without_cbt)) + '''</div>
                <div>May cause slow incremental backups</div>
            </div>
            <div class="summary-card">
                <h3>VMs with Snapshots</h3>
                <div class="value">''' + str(len(vms_with_snapshots)) + '''</div>
                <div>Consider consolidating</div>
            </div>
        </div>

        <h3>VM Backup Readiness</h3>
        <table>
            <tr>
                <th>Name</th>
                <th>Power</th>
                <th>CBT</th>
                <th>Snapshots</th>
                <th>Disk Size</th>
                <th>Disk Type</th>
                <th>HW Version</th>
                <th>Host</th>
                <th>Datastore</th>
            </tr>
'''

    for vm in vms:
        power_class = 'status-on' if vm['power_state'] == 'On' else 'status-off'
        cbt_class = 'status-green' if vm['cbt_enabled'] else 'status-red'
        cbt_text = 'Enabled' if vm['cbt_enabled'] else 'Disabled'
        snap_class = 'status-red' if vm['snapshot_count'] > 0 else 'status-green'
        html += f'''            <tr>
                <td>{vm['name']}</td>
                <td class="{power_class}">{vm['power_state']}</td>
                <td class="{cbt_class}">{cbt_text}</td>
                <td class="{snap_class}">{vm['snapshot_count']}</td>
                <td>{vm['used_gb']} / {vm['provisioned_gb']} GB</td>
                <td>{vm['disk_types']}</td>
                <td>{vm['hw_version']}</td>
                <td>{vm['host']}</td>
                <td>{vm['datastores']}</td>
            </tr>
'''

    html += '''        </table>

        <h2>Virtual Machines (''' + str(len(vms)) + ''')</h2>
        <table>
            <tr>
                <th>Name</th>
                <th>Power</th>
                <th>Guest OS</th>
                <th>vCPUs</th>
                <th>Memory</th>
                <th>Disk Used</th>
                <th>Uptime</th>
                <th>Host</th>
                <th>IP Address</th>
            </tr>
'''

    for vm in vms:
        power_class = 'status-on' if vm['power_state'] == 'On' else 'status-off'
        html += f'''            <tr>
                <td>{vm['name']}</td>
                <td class="{power_class}">{vm['power_state']}</td>
                <td>{vm['guest_os']}</td>
                <td>{vm['vcpus']}</td>
                <td>{vm['memory_gb']} GB</td>
                <td>{vm['used_gb']} / {vm['provisioned_gb']} GB</td>
                <td>{vm['uptime']}</td>
                <td>{vm['host']}</td>
                <td>{vm['ip_address']}</td>
            </tr>
'''

    html += '''        </table>

        <h2>Datastores (''' + str(len(datastores)) + ''')</h2>
        <table>
            <tr>
                <th>Name</th>
                <th>Type</th>
                <th>Capacity</th>
                <th>Free</th>
                <th>Usage</th>
                <th>Provisioned</th>
                <th>Status</th>
            </tr>
'''

    for ds in datastores:
        progress_class = ''
        if ds['used_pct'] >= 90:
            progress_class = 'danger'
        elif ds['used_pct'] >= 75:
            progress_class = 'warning'

        status = 'Accessible' if ds['accessible'] else 'Not Accessible'
        status_class = 'status-green' if ds['accessible'] else 'status-red'

        html += f'''            <tr>
                <td>{ds['name']}</td>
                <td>{ds['type']}</td>
                <td>{ds['capacity_gb']} GB</td>
                <td>{ds['free_gb']} GB</td>
                <td>
                    <div class="progress-bar">
                        <div class="progress-fill {progress_class}" style="width: {ds['used_pct']}%">{ds['used_pct']}%</div>
                    </div>
                </td>
                <td>{ds['provisioned_gb']} GB</td>
                <td class="{status_class}">{status}</td>
            </tr>
'''

    html += '''        </table>
    </div>
</body>
</html>
'''

    with open(output_path, 'w') as f:
        f.write(html)

    print(f"HTML report saved: {output_path}")


def generate_excel_report(vcenter_info, hosts, vms, datastores, host_nics, output_path):
    """Generate Excel report with multiple sheets including backup analysis."""

    wb = Workbook()

    # Styles
    header_font = Font(bold=True, color='FFFFFF')
    header_fill = PatternFill(start_color='0F4C75', end_color='0F4C75', fill_type='solid')
    header_alignment = Alignment(horizontal='center', vertical='center')
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )

    def style_header(ws, row=1):
        for cell in ws[row]:
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border

    def auto_width(ws):
        for column in ws.columns:
            max_length = 0
            column_letter = get_column_letter(column[0].column)
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            adjusted_width = min(max_length + 2, 50)
            ws.column_dimensions[column_letter].width = adjusted_width

    # Sheet 1: vCenter Summary
    ws = wb.active
    ws.title = "vCenter Summary"
    ws.append(["Property", "Value"])
    style_header(ws)
    ws.append(["vCenter Name", vcenter_info['name']])
    ws.append(["Version", vcenter_info['version']])
    ws.append(["Build", vcenter_info['build']])
    ws.append(["Full Name", vcenter_info['full_name']])
    ws.append(["API Version", vcenter_info['api_version']])
    ws.append(["OS Type", vcenter_info['os_type']])
    ws.append(["Instance UUID", vcenter_info['instance_uuid']])
    ws.append(["Server Time", vcenter_info['current_time']])
    ws.append(["Cluster Count", vcenter_info['cluster_count']])
    ws.append(["Host Count", vcenter_info['host_count']])
    ws.append(["VM Count", vcenter_info['vm_count']])
    auto_width(ws)

    # Sheet 2: ESXi Hosts
    ws = wb.create_sheet("ESXi Hosts")
    headers = ["Name", "IP Address", "Version", "Build", "Full Name", "Vendor", "Model",
               "Serial Number", "CPU Model", "Sockets", "Cores", "Threads", "Memory (GB)",
               "Uptime", "Connection State", "Power State", "Maintenance Mode", "Status"]
    ws.append(headers)
    style_header(ws)

    for host in hosts:
        ws.append([
            host['name'], host['ip_address'], host['version'], host['build'],
            host['full_name'], host['vendor'], host['model'], host['serial_number'],
            host['cpu_model'], host['cpu_sockets'], host['cpu_cores'], host['cpu_threads'],
            host['memory_gb'], host['uptime'], host['connection_state'], host['power_state'],
            'Yes' if host['maintenance_mode'] else 'No', host['overall_status']
        ])
    auto_width(ws)

    # Sheet 3: Host NICs
    ws = wb.create_sheet("Host NICs")
    headers = ["Host", "Device", "Driver", "MAC Address", "Link Speed", "PCI Address"]
    ws.append(headers)
    style_header(ws)

    for nic in host_nics:
        ws.append([
            nic['host'], nic['device'], nic['driver'], nic['mac'],
            nic['link_speed'], nic['pci']
        ])
    auto_width(ws)

    # Sheet 4: Backup Analysis
    ws = wb.create_sheet("Backup Analysis")
    headers = ["Name", "Power State", "CBT Enabled", "Snapshot Count", "Provisioned (GB)",
               "Used (GB)", "Disk Count", "Disk Types", "HW Version", "Host", "Datastores"]
    ws.append(headers)
    style_header(ws)

    for vm in vms:
        ws.append([
            vm['name'], vm['power_state'],
            'Yes' if vm['cbt_enabled'] else 'No',
            vm['snapshot_count'], vm['provisioned_gb'], vm['used_gb'],
            vm['disk_count'], vm['disk_types'], vm['hw_version'],
            vm['host'], vm['datastores']
        ])
    auto_width(ws)

    # Sheet 5: Virtual Machines
    ws = wb.create_sheet("Virtual Machines")
    headers = ["Name", "Power State", "Guest OS", "Guest ID", "vCPUs", "Memory (GB)",
               "Provisioned (GB)", "Used (GB)", "Uptime", "Host", "Tools Status",
               "Tools Version", "IP Address", "Hostname", "Annotation"]
    ws.append(headers)
    style_header(ws)

    for vm in vms:
        ws.append([
            vm['name'], vm['power_state'], vm['guest_os'], vm['guest_id'],
            vm['vcpus'], vm['memory_gb'], vm['provisioned_gb'], vm['used_gb'],
            vm['uptime'], vm['host'], vm['tools_status'], vm['tools_version'],
            vm['ip_address'], vm['hostname'], vm['annotation'][:100] if vm['annotation'] else ""
        ])
    auto_width(ws)

    # Sheet 6: Datastores
    ws = wb.create_sheet("Datastores")
    headers = ["Name", "Type", "Capacity (GB)", "Free (GB)", "Used (GB)", "Used %",
               "Provisioned (GB)", "Accessible", "Maintenance Mode", "URL"]
    ws.append(headers)
    style_header(ws)

    for ds in datastores:
        ws.append([
            ds['name'], ds['type'], ds['capacity_gb'], ds['free_gb'], ds['used_gb'],
            ds['used_pct'], ds['provisioned_gb'], 'Yes' if ds['accessible'] else 'No',
            ds['maintenance_mode'], ds['url']
        ])
    auto_width(ws)

    wb.save(output_path)
    print(f"Excel report saved: {output_path}")


def main():
    parser = argparse.ArgumentParser(
        description='Generate vCenter infrastructure reports',
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog='''
Examples:
  %(prog)s --config config.ini
  %(prog)s --server vcenter.example.com --username admin@vsphere.local
  %(prog)s --config config.ini --output ./reports
        '''
    )

    parser.add_argument('--config', '-c', help='Path to configuration file')
    parser.add_argument('--server', '-s', help='vCenter server hostname or IP')
    parser.add_argument('--username', '-u', help='vCenter username')
    parser.add_argument('--password', '-p', help='vCenter password (will prompt if not provided)')
    parser.add_argument('--port', type=int, default=443, help='vCenter port (default: 443)')
    parser.add_argument('--output', '-o', default='.', help='Output directory (default: current directory)')

    args = parser.parse_args()

    # Get connection parameters
    server = args.server
    username = args.username
    password = args.password
    port = args.port

    # Read from config file if provided
    if args.config:
        config = configparser.ConfigParser()
        config.read(args.config)

        if 'vcenter' in config:
            server = server or config.get('vcenter', 'server', fallback=None)
            username = username or config.get('vcenter', 'username', fallback=None)
            password = password or config.get('vcenter', 'password', fallback=None)
            port = port or config.getint('vcenter', 'port', fallback=443)

    # Validate required parameters
    if not server:
        print("Error: vCenter server is required. Use --server or --config")
        sys.exit(1)
    if not username:
        print("Error: Username is required. Use --username or --config")
        sys.exit(1)
    if not password:
        import getpass
        password = getpass.getpass(f"Password for {username}@{server}: ")

    # Create output directory
    output_dir = Path(args.output)
    output_dir.mkdir(parents=True, exist_ok=True)

    # Generate timestamp for filenames
    timestamp = datetime.now().strftime('%Y-%m-%d_%H%M%S')

    # Connect and generate reports
    reporter = VCenterReporter(server, username, password, port)

    if not reporter.connect():
        sys.exit(1)

    try:
        print("\nCollecting vCenter information...")
        vcenter_info = reporter.get_vcenter_info()

        print("Collecting ESXi host information...")
        hosts = reporter.get_hosts()
        print(f"  Found {len(hosts)} hosts")

        print("Collecting host NIC information...")
        host_nics = reporter.get_host_nics()
        print(f"  Found {len(host_nics)} NICs")

        print("Collecting VM information...")
        vms = reporter.get_vms()
        print(f"  Found {len(vms)} VMs")

        # Backup analysis summary
        vms_without_cbt = [vm for vm in vms if not vm.get('cbt_enabled', False)]
        vms_with_snapshots = [vm for vm in vms if vm.get('snapshot_count', 0) > 0]
        if vms_without_cbt:
            print(f"  WARNING: {len(vms_without_cbt)} VMs have CBT disabled (slow backups)")
        if vms_with_snapshots:
            print(f"  WARNING: {len(vms_with_snapshots)} VMs have snapshots")

        print("Collecting datastore information...")
        datastores = reporter.get_datastores()
        print(f"  Found {len(datastores)} datastores")

        print("\nGenerating reports...")

        html_path = output_dir / f"vcenter_report_{timestamp}.html"
        generate_html_report(vcenter_info, hosts, vms, datastores, host_nics, html_path)

        excel_path = output_dir / f"vcenter_report_{timestamp}.xlsx"
        generate_excel_report(vcenter_info, hosts, vms, datastores, host_nics, excel_path)

        print("\nReport generation complete!")

    finally:
        reporter.disconnect()


if __name__ == '__main__':
    main()