import arcpy
import pandas as pd
from datetime import datetime
import os

# Placeholder for file path - Replace 'YOUR_FILE_PATH' with the actual path to Automated_Traffic_Volume_Counts.csv
file_path = 'YOUR_FILE_PATH\\Automated_Traffic_Volume_Counts.csv'
output_directory = r'C:\temp'

# Function to check if required fields are in the dataset
def check_required_fields(data, required_fields):
    for field in required_fields:
        if field not in data.columns:
            raise ValueError(f"Field '{field}' is missing from the dataset.")

try:
    print("Starting the data processing script...")

    # Define chunk size for processing large file in chunks
    chunksize = 100000  # Adjust based on system memory
    filtered_data = []  # Store matching records here

    # Define required fields for filtering
    required_fields = ["Boro", "Yr", "M", "D", "HH"]

    # Open and read the large CSV file in chunks
    print("Opening and reading data in chunks due to large file size...")
    for chunk in pd.read_csv(file_path, chunksize=chunksize):
        # Verify required fields are present
        check_required_fields(chunk, required_fields)

        # Filtering conditions based on specified criteria
        print("Filtering records in the current chunk...")

        # Filter by 'Boro' being Manhattan
        chunk_filtered = chunk[chunk['Boro'] == "Manhattan"]

        # Rename 'Yr', 'M', and 'D' to 'year', 'month', and 'day' for datetime conversion
        chunk_filtered = chunk_filtered.rename(columns={'Yr': 'year', 'M': 'month', 'D': 'day'})

        # Convert to datetime to filter by Monday
        chunk_filtered['Date'] = pd.to_datetime(chunk_filtered[['year', 'month', 'day']], errors='coerce')

        # Filter records to only include those that are Mondays and have HH between 18 and 23
        chunk_filtered = chunk_filtered[
            (chunk_filtered['Date'].dt.weekday == 0) &  # 0 represents Monday
            (chunk_filtered['HH'].between(18, 23))
        ]

        # Append filtered chunk to the list if not empty
        if not chunk_filtered.empty:
            filtered_data.append(chunk_filtered)

        print(f"Chunk processed, records found: {len(chunk_filtered)}")

    # Combine all filtered chunks into a single DataFrame
    if filtered_data:
        final_data = pd.concat(filtered_data, ignore_index=True)
        print("All chunks processed and combined into final filtered dataset.")

        # Generate output file path with timestamp
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        output_file = os.path.join(output_directory, f"Filtered_Traffic_Data_{timestamp}.csv")

        # Save filtered data to CSV
        final_data.to_csv(output_file, index=False)
        print(f"Filtered data saved successfully to {output_file}")
    else:
        print("No records found matching the specified criteria.")

except FileNotFoundError:
    print(f"Error: The file at {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 ValueError as ve:
    print(f"Error: {ve}")
except Exception as e:
    print(f"An unexpected error occurred: {e}")

print("Data processing script completed.")
