@using System.IO
@using System.Data.SqlClient

<button onclick="@DownloadAndUpload">Download and Upload JSON</button>

@code {
  async Task DownloadAndUpload()
  {
    // Download the file
    string fileName = "tantalusa.json";
    string downloadPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), fileName);
    if (!File.Exists(downloadPath))
    {
      // Handle the case where the file doesn't exist locally
      return;
    }

    // Read the downloaded JSON file
    string jsonString = await File.ReadAllTextAsync(downloadPath);
    List<TantalusData> dataList = JsonConvert.DeserializeObject<List<TantalusData>>(jsonString);

    // Connect to SQL Server database (replace connection string)
    string connectionString = "YOUR_CONNECTION_STRING";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
      connection.Open();

      // Create the table if it doesn't exist (adjust column names and types as needed)
      string createTable = @"CREATE TABLE IF NOT EXISTS protosearch (
        id INT PRIMARY KEY IDENTITY,
        ssnaurl NVARCHAR(MAX),
        genre NVARCHAR(MAX),
        metadata NVARCHAR(MAX),
        description NVARCHAR(MAX),
        seo NVARCHAR(MAX),
        county NVARCHAR(MAX),
        postcode NVARCHAR(MAX)
      )";
      using (SqlCommand command = new SqlCommand(createTable, connection))
      {
        command.ExecuteNonQuery();
      }

      // Insert data into the table
      foreach (TantalusData data in dataList)
      {
        string insertQuery = @"INSERT INTO protosearch (ssnaurl, genre, metadata, description, seo, county, postcode)
                              VALUES (@ssnaurl, @genre, @metadata, @description, @seo, @county, @postcode)";
        using (SqlCommand command = new SqlCommand(insertQuery, connection))
        {
          command.Parameters.AddWithValue("@ssnaurl", data.Ssanaurl);
          command.Parameters.AddWithValue("@genre", data.Genre);
          command.Parameters.AddWithValue("@metadata", data.Metadata);
          command.Parameters.AddWithValue("@description", data.Description);
          command.Parameters.AddWithValue("@seo", data.Seo);
          command.Parameters.AddWithValue("@county", data.County);
          command.Parameters.AddWithValue("@postcode", data.Postcode);
          command.ExecuteNonQuery();
        }
      }
    }
  }

  public class TantalusData
  {
    public string Ssanaurl { get; set; }
    public string Genre { get; set; }
    public string Metadata { get; set; }
    public string Description { get; set; }
    public string Seo { get; set; }
    public string County { get; set; }
    public string Postcode { get; set; }
  }
}