Add special conditions code file

4 files changed, 114 insertions, 21 deletions

diff --git a/README.md b/README.md
index 899dae1..2aaff96 100644
--- a/README.md
+++ b/README.md
@@ -19,3 +19,11 @@ Some very general information about the database can be found at [www.fcc.gov/si
 **NOTE:** In theory, this structure can also be algorithmically determined from [www.fcc.gov/sites/default/files/public_access_database_definitions_sql_v4.txt](https://www.fcc.gov/sites/default/files/public_access_database_definitions_sql_v4.txt). However, that is a can of worms I don't want to unpack anytime soon.
 
 To start out, you will need the weekly dump, as daily dumps only contain changes. The latest weekly file can be found at [data.fcc.gov/download/pub/uls/complete/l_amat.zip](https://data.fcc.gov/download/pub/uls/complete/l_amat.zip)
+
+### Special Conditions
+The Special Conditions Code are defined in https://www.fcc.gov/file/20669/download. It is another pipe-separated value file, but this one has no header whatsoever. My reverse engineering of it is below. The only codes that show up in the amateur radio dumps are 999 and 4020 but I wanna be complete.
+
+The format seems to be vaguely as follows:
+```csv
+code|service?|text1|text2|text3|text4|text5|[RPT]?
+```
diff --git a/migrations/01-create-db.sql b/migrations/01-create-db.sql
index 573952b..e81c1e0 100644
--- a/migrations/01-create-db.sql
+++ b/migrations/01-create-db.sql
@@ -170,5 +170,11 @@ CREATE TABLE special_conditions_free_form (
   status_code varchar(1),
   status_date datetime
 );
+CREATE TABLE special_condition_codes (
+  code INTEGER PRIMARY KEY,
+  service varchar(2),
+  description TEXT,
+  unknown TEXT
+);
 INSERT INTO migrations (name)
 VALUES ('01-create-db.sql');
diff --git a/src/load.rs b/src/load.rs
index e651b55..6adb6d7 100644
--- a/src/load.rs
+++ b/src/load.rs
@@ -12,6 +12,7 @@ const INSERT_HISTORY_SQL: &str = r"INSERT INTO history (record_type, unique_syst
 const INSERT_LICENSE_ATTACHMENT_SQL: &str = r"INSERT INTO license_attachments (record_type, unique_system_identifier, call_sign, attachment_code, attachment_description, attachment_date, attachment_file_name, action_performed) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
 const INSERT_SPECIAL_CONDITION_SQL: &str = r"INSERT INTO special_conditions (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, special_conditions_type, special_conditions_code, status_code, status_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
 const INSERT_SPECIAL_CONDITION_FREE_FORM_SQL: &str = r"INSERT INTO special_conditions_free_form (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, license_free_form_type, unique_license_free_form_identifier, sequence_number, license_free_form_condition, status_code, status_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_SPECIAL_CONDITION_CODES_SQL: &str = r"INSERT INTO special_condition_codes (code, service, description, unknown) VALUES (?, ?, ?, ?)";
 
 pub async fn load_amateurs(db: &SqlitePool) {
     let amateurs_file = File::open("AM.dat").expect("Error opening file");
@@ -506,3 +507,56 @@ pub async fn load_special_conditions_free_form(db: &SqlitePool) {
 
     progress_bar.finish();
 }
+
+pub async fn load_special_condition_codes(db: &SqlitePool) {
+    let codes_file = File::open("special_condition_codes.txt").expect("Error opening file");
+    // let history_file_meta = fs::metadata("special_condition_codes.txt").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&codes_file).lines().count();
+    drop(codes_file);
+
+    let codes_file = File::open("special_condition_codes.txt").expect("Error opening file");
+    let mut transaction = db.begin().await.expect("Error starting transaction");
+    let mut reader = csv::ReaderBuilder::new()
+        .has_headers(false)
+        .delimiter(b'|')
+        .quoting(true)
+        .from_reader(codes_file);
+
+    let progress_bar = ProgressBar::new(line_count.try_into().unwrap());
+    progress_bar.set_style(
+        ProgressStyle::with_template(
+            "[{elapsed}+{eta}/{duration}] [{bar:40.cyan/blue}] {human_pos}/{human_len} ({per_sec}) {msg}",
+        )
+        .unwrap()
+        .progress_chars("#>-"),
+    );
+    progress_bar.set_message("special_condition_codes.txt");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let statement = sqlx::query(INSERT_SPECIAL_CONDITION_CODES_SQL);
+        statement
+            .bind(line.get(0))
+            .bind(line.get(1))
+            .bind(format!(
+                "{} {} {} {} {}",
+                line.get(2).unwrap_or_default(),
+                line.get(3).unwrap_or_default(),
+                line.get(4).unwrap_or_default(),
+                line.get(5).unwrap_or_default(),
+                line.get(6).unwrap_or_default()
+            ))
+            .bind(line.get(7))
+            .execute(&mut transaction)
+            .await
+            .expect("Error executing statement");
+        progress_bar.set_position(line.position().unwrap().line());
+    }
+
+    transaction
+        .commit()
+        .await
+        .expect("Error committing transaction");
+
+    progress_bar.finish();
+}
diff --git a/src/main.rs b/src/main.rs
index c5efab0..c81d08f 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -36,15 +36,11 @@ fn download_file(url: &str, file_name: Option<&str>) -> Result<File, ()> {
         .expect("Error parsing Content-Length header");
 
     let last_modified = match resp.header("Last-Modified") {
-        Some(last_mod) => {
-            match DateTime::parse_from_rfc2822(last_mod) {
-                Ok(dt) => Some(dt.timestamp()),
-                Err(_) => None,
-            }
-        }
-        None => {
-            None
-        }
+        Some(last_mod) => match DateTime::parse_from_rfc2822(last_mod) {
+            Ok(dt) => Some(dt.timestamp()),
+            Err(_) => None,
+        },
+        None => None,
     };
 
     // Time to determine the file name!
@@ -77,7 +73,13 @@ fn download_file(url: &str, file_name: Option<&str>) -> Result<File, ()> {
                         Some(cd_match) => {
                             // We have a filename, so use that
                             // TODO: Make less unwrappy
-                            cd_match.iter().last().unwrap().unwrap().as_str().to_string()
+                            cd_match
+                                .iter()
+                                .last()
+                                .unwrap()
+                                .unwrap()
+                                .as_str()
+                                .to_string()
                         }
                         None => {
                             // It doesn't, so we have to fall back to the file name in the URL
@@ -95,7 +97,10 @@ fn download_file(url: &str, file_name: Option<&str>) -> Result<File, ()> {
         let file_metadata = fs::metadata(&output_file_name).expect("Error getting file metadata");
         let mtime = FileTime::from_last_modification_time(&file_metadata);
 
-        match (mtime.seconds() >= last_modified.unwrap_or(1), file_metadata.len() == len) {
+        match (
+            mtime.seconds() >= last_modified.unwrap_or(1),
+            file_metadata.len() == len,
+        ) {
             (true, true) => {
                 println!("File already downloaded");
                 return Ok(File::open(output_file_name).expect("Error opening file"));
@@ -201,22 +206,40 @@ fn unzip_file(zip_file: File) -> Result<(), ()> {
 
 #[tokio::main]
 async fn main() {
-    let output_file = download_file(WEEKLY_DUMP_URL, None).expect("Error downloading weekly dump file");
-
-    #[allow(unused_variables)]
-    let conditions_file = download_file(SPECIAL_CONDITIONS_URL, None).expect("Error downloading Special Conditions file");
+    let output_file =
+        download_file(WEEKLY_DUMP_URL, None).expect("Error downloading weekly dump file");
+    // Hardcoding this file name because it might change and I don't want to deal with that
+    let _conditions_file =
+        download_file(SPECIAL_CONDITIONS_URL, Some("special_condition_codes.txt"))
+            .expect("Error downloading Special Conditions file");
 
     unzip_file(output_file).expect("Error unzipping file");
 
-    let db = SqlitePool::connect("sqlite://fcc.db")
-        .await
-        .expect("Error connecting to database");
-
     // Some idiot at the FCC decided that unescaped newlines in the middle of a field were cool
     // Uncle Ted may have had some good ideas after all
-    let re = Regex::new(r"\s*\r\r\n").unwrap();
+    let comments_regex = Regex::new(r"\s*\r\r\n").unwrap();
     let comments = fs::read_to_string("CO.dat").expect("Error reading file");
-    fs::write("CO.dat", re.replace_all(&comments, " ").to_string()).expect("Error writing file");
+    fs::write(
+        "CO.dat",
+        comments_regex.replace_all(&comments, " ").to_string(),
+    )
+    .expect("Error writing file");
+
+    // This is somehow worse, newlines can either be \n (more common) OR \r\n.
+    // The first one is easy, if there's a newline without a preceeding carriage return, it's bad and should be gone
+    // CRLF is what's normally used, however the last character of every entry is either R, P, T, or |, so if there's a CRLF
+    // without one of those immediately before, yeet it
+    let conditions_regex = Regex::new(r"(([^\r]\n)|([^RPT\|]\r\n))").unwrap();
+    let conditions = fs::read_to_string("special_condition_codes.txt").expect("Error reading file");
+    fs::write(
+        "special_condition_codes.txt",
+        conditions_regex.replace_all(&conditions, " ").to_string(),
+    )
+    .expect("Error writing file");
+
+    let db = SqlitePool::connect("sqlite://fcc.db")
+        .await
+        .expect("Error connecting to database");
 
     load::load_amateurs(&db).await;
     load::load_comments(&db).await;
@@ -226,4 +249,6 @@ async fn main() {
     load::load_license_attachments(&db).await;
     load::load_special_conditions(&db).await;
     load::load_special_conditions_free_form(&db).await;
+
+    load::load_special_condition_codes(&db).await;
 }