#!/usr/bin/env python

import requests
import csv

# Send a GET request to the SimpleMaps US Cities Database URL
url = "https://simplemaps.com/static/data/us-cities/1.73/basic/simplemaps_uscities_basicv1.73.zip"
response = requests.get(url)

# Check if the request was successful
if response.status_code == 200:
    # Save the ZIP file
    with open("simplemaps_uscities_basic.zip", "wb") as file:
        file.write(response.content)
    print("ZIP file downloaded successfully.")
else:
    print("Failed to download the ZIP file.")

# Extract the CSV file from the ZIP (assuming you have already downloaded and saved the ZIP file)
import zipfile

with zipfile.ZipFile("simplemaps_uscities_basic.zip", "r") as zip_ref:
    zip_ref.extractall(".")

# Read the extracted CSV file
csv_file = "uscities.csv"
output_file = "cities_data.csv"

with open(csv_file, "r", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    
    # Open the output CSV file in write mode
    with open(output_file, "w", newline="", encoding="utf-8") as output:
        fieldnames = ["city", "state_id", "county_fips", "population"]
        writer = csv.DictWriter(output, fieldnames=fieldnames)
        
        # Write the header
        writer.writeheader()
        
        # Iterate over each row in the input CSV
        for row in reader:
            # Extract the required fields
            city = row["city"]
            state_id = row["state_id"]
            county_fips = row["county_fips"]
            population = row["population"]
            
            # Write the data to the output CSV file
            writer.writerow({"city": city, "state_id": state_id, "county_fips": county_fips, "population": population})

print("Data extraction completed. Output saved as", output_file)