"""
Shrishti AI API Test - Create Excel File (Goa)
Creates an Excel file with Goa-only locations for testing predictions.
"""

import pandas as pd

EXCEL_FILE_GOA = "backend/data/prediction_results_goa.xlsx"

# Goa-only locations for testing
GOA_LOCATIONS = [
    {"name": "Panaji, Goa", "lat": 15.4909, "lon": 73.8278},
    {"name": "Margao, Goa", "lat": 15.2993, "lon": 73.9581},
    {"name": "Vasco da Gama, Goa", "lat": 15.3860, "lon": 73.8448},
    {"name": "Mapusa, Goa", "lat": 15.5937, "lon": 73.8130},
    {"name": "Ponda, Goa", "lat": 15.4030, "lon": 74.0156},
    {"name": "Calangute, Goa", "lat": 15.5439, "lon": 73.7553},
    {"name": "Candolim, Goa", "lat": 15.5185, "lon": 73.7622},
    {"name": "Baga, Goa", "lat": 15.5560, "lon": 73.7517},
    {"name": "Anjuna, Goa", "lat": 15.5752, "lon": 73.7390},
    {"name": "Colva, Goa", "lat": 15.2797, "lon": 73.9228},
    {"name": "Dona Paula, Goa", "lat": 15.4619, "lon": 73.8032},
    {"name": "Old Goa, Goa", "lat": 15.5009, "lon": 73.9119},
    {"name": "Pernem, Goa", "lat": 15.7230, "lon": 73.7950},
    {"name": "Arambol, Goa", "lat": 15.6868, "lon": 73.7037},
    {"name": "Bicholim, Goa", "lat": 15.5883, "lon": 73.9457},
    {"name": "Sanquelim, Goa", "lat": 15.5642, "lon": 74.0078},
    {"name": "Quepem, Goa", "lat": 15.2128, "lon": 74.0772},
    {"name": "Canacona, Goa", "lat": 15.0100, "lon": 74.0243},
    {"name": "Palolem, Goa", "lat": 15.0094, "lon": 74.0236},
    {"name": "Sanguem, Goa", "lat": 15.2290, "lon": 74.1510},
    {"name": "Curchorem, Goa", "lat": 15.2635, "lon": 74.1087},
    {"name": "Valpoi, Goa", "lat": 15.5323, "lon": 74.1356},
    {"name": "Morjim, Goa", "lat": 15.6302, "lon": 73.7396},
]


def create_excel_goa():
    """Create an Excel file with Goa-only locations."""

    data = []
    for loc in GOA_LOCATIONS:
        data.append(
            {
                "Location Name": loc["name"],
                "Latitude": loc["lat"],
                "Longitude": loc["lon"],
                # HazardGuard columns
                "HazardGuard Prediction": "",
                "HazardGuard Main Model Confidence": "",
                "HazardGuard Disaster Types": "",
                # WeatherWise columns (60 days forecast as lists)
                "WeatherWise Temperature (°C) [60 days]": "",
                "WeatherWise Humidity (%) [60 days]": "",
                "WeatherWise Precipitation (mm) [60 days]": "",
                # GeoVision columns
                "GeoVision Disaster Type": "",
                "GeoVision Weather Type": "",
                "GeoVision Confidence": "",
                # Metadata
                "Prediction Timestamp": "",
            }
        )

    df = pd.DataFrame(data)

    with pd.ExcelWriter(EXCEL_FILE_GOA, engine="openpyxl") as writer:
        df.to_excel(writer, sheet_name="Predictions", index=False)

        worksheet = writer.sheets["Predictions"]

        column_widths = {
            "A": 30,  # Location Name
            "B": 12,  # Latitude
            "C": 12,  # Longitude
            "D": 22,  # HazardGuard Prediction
            "E": 35,  # HazardGuard Main Model Confidence
            "F": 28,  # HazardGuard Disaster Types
            "G": 50,  # WeatherWise Temperature [60 days]
            "H": 50,  # WeatherWise Humidity [60 days]
            "I": 50,  # WeatherWise Precipitation [60 days]
            "J": 24,  # GeoVision Disaster Type
            "K": 22,  # GeoVision Weather Type
            "L": 20,  # GeoVision Confidence
            "M": 22,  # Prediction Timestamp
        }

        for col, width in column_widths.items():
            worksheet.column_dimensions[col].width = width

    print(f"Excel file created: {EXCEL_FILE_GOA}")
    print(f"Total locations: {len(GOA_LOCATIONS)}")
    print("\nLocations added:")
    for i, loc in enumerate(GOA_LOCATIONS, 1):
        print(f"  {i}. {loc['name']} ({loc['lat']}, {loc['lon']})")

    return df


if __name__ == "__main__":
    create_excel_goa()