import arcpy
import pandas as pd
import os

# Define the output directory and shapefile name
output_directory = r'C:\temp'
shapefile_name = 'prompt_1_data_subset.shp'
shapefile_path = os.path.join(output_directory, shapefile_name)

# Define the spatial reference - NAD83 / New York Long Island (ftUS)
spatial_reference = arcpy.SpatialReference(2263)  # EPSG code 2263 for NAD83 / New York Long Island (ftUS)

# Placeholder for the CSV file path - Update this with the actual file path
csv_file_path = r'C:\Users\BrianT\RIT\TEACHING\IGME_386_C_drive\Week_12_SDS_Predictive_Models\prompts\data_output\prompt_1_data_subset.csv'

try:
    print("Starting the shapefile creation script...")

    # Step 1: Create the Shapefile
    print("Creating the shapefile with NAD83 / New York Long Island (ftUS) projection...")
    arcpy.management.CreateFeatureclass(output_directory, shapefile_name, "POINT", spatial_reference=spatial_reference)
    
    # Step 2: Read the CSV file
    print("Reading the CSV file...")
    data = pd.read_csv(csv_file_path)

    # Step 3: Add fields to the shapefile based on CSV columns
    print("Adding fields to the shapefile...")
    for column in data.columns:
        if column == 'Vol':
            # Add Vol field as a long integer
            arcpy.management.AddField(shapefile_path, column, "LONG")
            print("Added field 'Vol' as LONG.")
        elif column == 'WktGeom':
            # Include WktGeom field as specified, as TEXT for reference
            arcpy.management.AddField(shapefile_path, column, "TEXT", field_length=255)
            print("Included 'WktGeom' as a TEXT field for reference.")
        else:
            # Default all other fields to text
            arcpy.management.AddField(shapefile_path, column, "TEXT", field_length=255)
            print(f"Added field '{column}' as TEXT.")

    # Step 4: Populate shapefile with data from CSV
    print("Populating the shapefile with data from the CSV file...")
    # Define fields for the cursor, excluding geometry
    fields = [field for field in data.columns if field != 'WktGeom'] + ["SHAPE@"]

    with arcpy.da.InsertCursor(shapefile_path, fields) as cursor:
        for index, row in data.iterrows():
            try:
                # Convert WktGeom to PointGeometry
                wkt = row['WktGeom']
                point = arcpy.FromWKT(wkt, spatial_reference)  # Convert WKT to point geometry
            except Exception as e:
                print(f"Error in geometry conversion at row {index}: {e}")
                continue

            # Prepare attribute data (excluding WktGeom but including Vol handling)
            row_data = []
            for field in fields[:-1]:  # Exclude SHAPE@ in this loop
                if field == 'Vol':
                    try:
                        # Convert Vol to integer if possible, with error handling
                        vol_value = int(row['Vol'])
                        row_data.append(vol_value)
                    except ValueError:
                        print(f"Conversion error for 'Vol' at row {index}. Setting Vol to 0.")
                        row_data.append(0)  # Default to 0 if conversion fails
                else:
                    row_data.append(row[field])

            # Add the geometry as the final element in the row data
            row_data.append(point)

            # Insert the row with geometry and attribute data
            cursor.insertRow(row_data)
            print(f"Inserted record {index + 1} into the shapefile.")

    print("Shapefile creation and data insertion completed successfully.")

except FileNotFoundError:
    print(f"Error: The file at {csv_file_path} could not be found. Please check the file path.")
except pd.errors.EmptyDataError:
    print("Error: The file is empty or cannot be read. Please verify the file content.")
except arcpy.ExecuteError as e:
    print("ArcPy error:", e)
except Exception as e:
    print(f"An unexpected error occurred: {e}")

print("Data processing script completed.")
