Import ALL THE DATA

4 files changed, 776 insertions, 10 deletions

diff --git a/Cargo.lock b/Cargo.lock
index b4fb50e..4deb7ee 100644
--- a/Cargo.lock
+++ b/Cargo.lock
@@ -20,6 +20,15 @@ dependencies = [
 ]
 
 [[package]]
+name = "aho-corasick"
+version = "0.7.19"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "b4f55bd91a0978cbfd91c457a164bab8b4001c833b7f323132c0a4e1922dd44e"
+dependencies = [
+ "memchr",
+]
+
+[[package]]
 name = "android_system_properties"
 version = "0.1.5"
 source = "registry+https://github.com/rust-lang/crates.io-index"
@@ -36,6 +45,7 @@ dependencies = [
  "csv",
  "filetime",
  "indicatif",
+ "regex",
  "serde",
  "sqlx",
  "tokio",
@@ -859,12 +869,29 @@ dependencies = [
 ]
 
 [[package]]
+name = "regex"
+version = "1.7.0"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "e076559ef8e241f2ae3479e36f97bd5741c0330689e217ad51ce2c76808b868a"
+dependencies = [
+ "aho-corasick",
+ "memchr",
+ "regex-syntax",
+]
+
+[[package]]
 name = "regex-automata"
 version = "0.1.10"
 source = "registry+https://github.com/rust-lang/crates.io-index"
 checksum = "6c230d73fb8d8c1b9c0b3135c5142a8acee3a0558fb8db5cf1cb65f8d7862132"
 
 [[package]]
+name = "regex-syntax"
+version = "0.6.28"
+source = "registry+https://github.com/rust-lang/crates.io-index"
+checksum = "456c603be3e8d448b072f410900c09faf164fbce2d480456f50eea6e25f9c848"
+
+[[package]]
 name = "ring"
 version = "0.16.20"
 source = "registry+https://github.com/rust-lang/crates.io-index"
diff --git a/Cargo.toml b/Cargo.toml
index a0ea2ed..7554b0f 100644
--- a/Cargo.toml
+++ b/Cargo.toml
@@ -10,6 +10,7 @@ chrono = "0.4.22"
 csv = "1.1.6"
 filetime = "0.2.18"
 indicatif = "0.17.1"
+regex = "1.7.0"
 serde = { version = "1.0.147", features = ["derive"] }
 sqlx = { version = "0.6.2", features = ["sqlite", "runtime-tokio-rustls"] }
 tokio = { version = "1.21.2", features = ["full"] }
diff --git a/migrations/01-create-db.sql b/migrations/01-create-db.sql
index 32803cb..ce7cd30 100644
--- a/migrations/01-create-db.sql
+++ b/migrations/01-create-db.sql
@@ -3,8 +3,38 @@ CREATE TABLE migrations (
   name VARCHAR(255) NOT NULL,
   created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
 );
+CREATE TABLE amateurs (
+  record_type varchar(2) not null default 'AM',
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  ebf_number varchar(30),
+  call_sign varchar(10),
+  operator_class varchar(1),
+  group_code varchar(1),
+  region_code integer,
+  trustee_call_sign varchar(10),
+  trustee_indicator varchar(1),
+  physician_certification varchar(1),
+  ve_signature varchar(1),
+  systematic_call_sign_change varchar(1),
+  vanity_call_sign_change varchar(1),
+  vainty_relationship varchar(1),
+  previous_call_sign varchar(10),
+  previous_operator_class varchar(1),
+  trustee_name varchar(50)
+);
+CREATE TABLE comments (
+  record_type varchar(2) not null default "CO",
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  call_sign varchar(10),
+  comment_date datetime,
+  description varchar(255),
+  status_code varchar(1),
+  status_date datetime
+);
 CREATE TABLE entities (
-  record_type varchar(2) not null,
+  record_type varchar(2) not null default 'EN',
   unique_system_identifier integer not null,
   uls_file_number varchar(14),
   ebf_number varchar(30),
@@ -35,5 +65,108 @@ CREATE TABLE entities (
   linked_license_id integer null,
   linked_callsign varchar(10)
 );
+CREATE TABLE headers (
+  record_type varchar(2) not null default 'HD',
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  ebf_number varchar(30),
+  call_sign varchar(10),
+  license_status varchar(1),
+  radio_service_code varchar(2),
+  grant_date datetime,
+  expired_date datetime,
+  cancellation_date datetime,
+  eligibility_rule_number varchar(10),
+  reserved varchar(1),
+  alien varchar(1),
+  alien_government varchar(1),
+  alien_corporation varchar(1),
+  alien_officer varchar(1),
+  alien_control varchar(1),
+  revoked varchar(1),
+  convicted varchar(1),
+  adjudged varchar(1),
+  reserved2 varchar(1),
+  common_carrier varchar(1),
+  non_common_carrier varchar(1),
+  private_comm varchar(1),
+  fixed varchar(1),
+  mobile varchar(1),
+  radiolocation varchar(1),
+  satellite varchar(1),
+  developmental_or_sta varchar(1),
+  interconnected_service varchar(1),
+  certifier_first_name varchar(20),
+  certifier_mi varchar(1),
+  certifier_last_name varchar(20),
+  certifier_suffix varchar(3),
+  certifier_title varchar(40),
+  gender varchar(1),
+  african_american varchar(1),
+  native_american varchar(1),
+  hawaiian varchar(1),
+  asian varchar(1),
+  white varchar(1),
+  ethnicity varchar(1),
+  effective_date datetime,
+  last_action_date datetime,
+  auction_id integer,
+  reg_stat_broad_serv varchar(1),
+  band_manager varchar(1),
+  type_serv_broad_serv varchar(1),
+  alien_ruling varchar(1),
+  licensee_name_change varchar(1),
+  whitespace_ind varchar(1),
+  additional_cert_choice varchar(1),
+  additional_cert_answer varchar(1),
+  discontinuation_ind varchar(1),
+  regulatory_compliance_ind varchar(1),
+  eligibility_cert_900 varchar(1),
+  transition_plan_cert_900 varchar(1),
+  return_spectrum_cert_900 varchar(1),
+  payment_cert_900 varchar(1)
+);
+CREATE TABLE history (
+  record_type varchar(2) not null default 'HS',
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  call_sign varchar(10),
+  log_date datetime,
+  code varchar(6)
+);
+CREATE TABLE license_attachments (
+  record_type varchar(2) not null default 'LA',
+  unique_system_identifier integer not null,
+  call_sign varchar(10),
+  attachment_code varchar(1),
+  attachment_description varchar(60),
+  attachment_date datetime,
+  attachment_file_name varchar(60),
+  action_performed varchar(1)
+);
+CREATE TABLE special_conditions (
+  record_type varchar(2) not null default 'SC',
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  ebf_number varchar(30),
+  call_sign varchar(10),
+  special_conditions_type varchar(1),
+  special_conditions_code integer,
+  status_code varchar(1),
+  status_date datetime
+);
+CREATE TABLE special_conditions_free_form (
+  record_type varchar(2) not null default 'SF',
+  unique_system_identifier integer not null,
+  uls_file_number varchar(14),
+  ebf_number varchar(30),
+  call_sign varchar(10),
+  license_free_form_type varchar(1),
+  unique_license_free_form_identifier varchar(10),
+  sequence_number integer,
+  license_free_form_condition varchar(255),
+  status_code varchar(1),
+  status_date datetime
+);
 INSERT INTO migrations (name)
 VALUES ('01-create-db.sql');
\ No newline at end of file
diff --git a/src/main.rs b/src/main.rs
index 53d2f5b..9487823 100644
--- a/src/main.rs
+++ b/src/main.rs
@@ -1,17 +1,59 @@
 use chrono::DateTime;
 use filetime::{self, FileTime};
 use indicatif::{ProgressBar, ProgressDrawTarget, ProgressStyle};
+use regex::Regex;
 use serde::Deserialize;
 use sqlx::sqlite::SqlitePool;
-use sqlx::{Executor, Pool, Sqlite, Statement};
+use sqlx::{Pool, Sqlite};
 use std::fs::{self, File};
+use std::io::BufRead;
 use std::io::{Read, Write};
-use std::sync::Arc;
 
 const WEEKLY_DUMP_URL: &str = "https://data.fcc.gov/download/pub/uls/complete/l_amat.zip";
-const INSERT_ENTITY_SQL: &str = r"INSERT INTO entities (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, entity_type, licensee_id, entity_name, first_name, mi, last_name, suffix, phone, fax, email, street_address, city, state, zip_code, po_box, attention_line, sgin, frn, applicant_type_code, applicant_type_other, status_code, status_date, lic_category_code, linked_license_id, linked_callsign) VALUES ('EN', ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_AMATEUR_SQL: &str = r"INSERT INTO amateurs (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, operator_class, group_code, region_code, trustee_call_sign, trustee_indicator, physician_certification, ve_signature, systematic_call_sign_change, vanity_call_sign_change, vainty_relationship, previous_call_sign, previous_operator_class, trustee_name) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_COMMENT_SQL: &str = r"INSERT INTO comments (record_type, unique_system_identifier, uls_file_number, call_sign, comment_date, description, status_code, status_date) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_ENTITY_SQL: &str = r"INSERT INTO entities (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, entity_type, licensee_id, entity_name, first_name, mi, last_name, suffix, phone, fax, email, street_address, city, state, zip_code, po_box, attention_line, sgin, frn, applicant_type_code, applicant_type_other, status_code, status_date, lic_category_code, linked_license_id, linked_callsign) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_HEADER_SQL: &str = r"INSERT INTO headers (record_type, unique_system_identifier, uls_file_number, ebf_number, call_sign, license_status, radio_service_code, grant_date, expired_date, cancellation_date, eligibility_rule_number, reserved, alien, alien_government, alien_corporation, alien_officer, alien_control, revoked, convicted, adjudged, reserved2, common_carrier, non_common_carrier, private_comm, fixed, mobile, radiolocation, satellite, developmental_or_sta, interconnected_service, certifier_first_name, certifier_mi, certifier_last_name, certifier_suffix, certifier_title, gender, african_american, native_american, hawaiian, asian, white, ethnicity, effective_date, last_action_date, auction_id, reg_stat_broad_serv, band_manager, type_serv_broad_serv, alien_ruling, licensee_name_change, whitespace_ind, additional_cert_choice, additional_cert_answer, discontinuation_ind, regulatory_compliance_ind, eligibility_cert_900, transition_plan_cert_900, return_spectrum_cert_900, payment_cert_900) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
+const INSERT_HISTORY_SQL: &str = r"INSERT INTO history (record_type, unique_system_identifier, uls_file_number, call_sign, log_date, code) VALUES (?, ?, ?, ?, ?, ?)";
+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 (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
 
 #[allow(dead_code, non_snake_case)]
+#[derive(Debug, Deserialize)]
+struct Amateur<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: u32,
+    pub UlsFileNumber: &'a str,
+    pub EBFNumber: &'a str,
+    pub CallSign: &'a str,
+    pub OperatorClass: &'a str,
+    pub GroupCode: &'a str,
+    pub RegionCode: &'a str,
+    pub TrusteeCallSign: &'a str,
+    pub TrusteeIndicator: &'a str,
+    pub PhysicianCertification: &'a str,
+    pub VESignature: &'a str,
+    pub SystematicCallSignChange: &'a str,
+    pub VanityCallSignChange: &'a str,
+    pub VanityRelationship: &'a str,
+    pub PreviousCallSign: &'a str,
+    pub PreviousOperatorClass: &'a str,
+    pub TrusteeName: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
+#[derive(Debug, Deserialize)]
+struct Comment<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: &'a str,
+    pub UlsFileNumber: &'a str,
+    pub CallSign: &'a str,
+    pub CommentDate: &'a str,
+    pub Description: &'a str,
+    pub StatusCode: &'a str,
+    pub StatusDate: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
 #[derive(Deserialize, Debug)]
 struct Entity<'a> {
     pub RecordType: &'a str,
@@ -46,6 +88,119 @@ struct Entity<'a> {
     pub LinkedCallsign: &'a str,
 }
 
+#[allow(dead_code, non_snake_case)]
+#[derive(Deserialize, Debug)]
+struct Header<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: u32,
+    pub UlsFileNumber: &'a str,
+    pub EBFNumber: &'a str,
+    pub CallSign: &'a str,
+    pub LicenseStatus: &'a str,
+    pub RadioServiceCode: &'a str,
+    pub GrantDate: &'a str,
+    pub ExpiredDate: &'a str,
+    pub CancellationDate: &'a str,
+    pub EligibilityRuleNumber: &'a str,
+    pub Reserved: &'a str,
+    pub Alien: &'a str,
+    pub AlienGovernment: &'a str,
+    pub AlienCorporation: &'a str,
+    pub AlienOfficers: &'a str,
+    pub AlienControl: &'a str,
+    pub Revoked: &'a str,
+    pub Convicted: &'a str,
+    pub Adjudged: &'a str,
+    pub Reserved2: &'a str,
+    pub CommonCarrier: &'a str,
+    pub NonCommonCarrier: &'a str,
+    pub PrivateComm: &'a str,
+    pub Fixed: &'a str,
+    pub Mobile: &'a str,
+    pub Radiolocation: &'a str,
+    pub Sattelite: &'a str,
+    pub DevelopmentalOrSta: &'a str,
+    pub InterconnectedService: &'a str,
+    pub CertifierFirstName: &'a str,
+    pub CertifierMiddleInitial: &'a str,
+    pub CertifierLastName: &'a str,
+    pub CertifierSuffix: &'a str,
+    pub CertifierTitle: &'a str,
+    pub Female: &'a str,
+    pub BlackOrAfricanAmerican: &'a str,
+    pub NativeAmerican: &'a str,
+    pub Hawaiian: &'a str,
+    pub Asian: &'a str,
+    pub White: &'a str,
+    pub Hispanic: &'a str,
+    pub EffectiveDate: &'a str,
+    pub LastActionDate: &'a str,
+    pub AuctionId: Option<i32>,
+    pub BroadcastServicesRegulatoryStatus: &'a str,
+    pub BandManagerRegulatoryStatus: &'a str,
+    pub BroadcastServicesTypeOfRadioService: &'a str,
+    pub AlienRuling: &'a str,
+    pub LicenseeNameChange: &'a str,
+    pub WhitespaceIndicator: &'a str,
+    pub OperationRequirementChoice: &'a str,
+    pub OperationRequirementAnswer: &'a str,
+    pub DiscontinuationOfService: &'a str,
+    pub RegulatoryCompliance: &'a str,
+    pub EligibilityCertification900Mhz: &'a str,
+    pub TransitionPlanCertification900Mhz: &'a str,
+    pub ReturnSpectrumCertification900Mhz: &'a str,
+    pub PaymentCertification900Mhz: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
+#[derive(Deserialize, Debug)]
+struct History<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: &'a str,
+    pub UlsFileNumber: &'a str,
+    pub CallSign: &'a str,
+    pub LogDate: &'a str,
+    pub Code: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
+#[derive(Deserialize, Debug)]
+struct LicenseAttachment<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: u32,
+    pub CallSign: &'a str,
+    pub AttachmentCode: &'a str,
+    pub AttachmentDescription: &'a str,
+    pub AttachmentDate: &'a str,
+    pub AttachmentFileName: &'a str,
+    pub ActionPerformed: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
+#[derive(Deserialize, Debug)]
+struct SpecialCondition<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: u32,
+    pub UlsFileNumber: &'a str,
+    pub EBFNumber: &'a str,
+    pub CallSign: &'a str,
+    pub SpecialConditionType: &'a str,
+    pub SpecialConditionCode: Option<i32>,
+    pub StatusCode: &'a str,
+    pub StatusDate: &'a str,
+}
+#[allow(dead_code, non_snake_case)]
+#[derive(Deserialize, Debug)]
+struct SpecialConditionFreeForm<'a> {
+    pub RecordType: &'a str,
+    pub UniqueSystemIdentifier: u32,
+    pub UlsFileNumber: &'a str,
+    pub EBFNumber: &'a str,
+    pub CallSign: &'a str,
+    pub LicenseFreeFormType: &'a str,
+    pub UniqueLicenseFreeFormIdentifier: &'a str,
+    pub SequenceNumber: Option<i32>,
+    pub LicenseFreeFormCondition: &'a str,
+    pub StatusCode: &'a str,
+    pub StatusDate: &'a str,
+}
 fn download_file() -> Result<File, ()> {
     let resp = ureq::get(WEEKLY_DUMP_URL)
         .call()
@@ -177,9 +332,124 @@ fn unzip_file(zip_file: File) -> Result<(), ()> {
     Ok(())
 }
 
-async fn load_entities(db: Pool<Sqlite>) {
+async fn load_amateurs(db: &Pool<Sqlite>) {
+    let amateurs_file = File::open("AM.dat").expect("Error opening file");
+    let amateurs_file_meta = fs::metadata("AM.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&amateurs_file).lines().count();
+    drop(amateurs_file);
+
+    let amateurs_file = File::open("AM.dat").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(false)
+        .from_reader(amateurs_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("AM.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let amateur: Amateur = line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_AMATEUR_SQL);
+        statement
+            .bind(amateur.RecordType)
+            .bind(amateur.UniqueSystemIdentifier)
+            .bind(amateur.UlsFileNumber)
+            .bind(amateur.EBFNumber)
+            .bind(amateur.CallSign)
+            .bind(amateur.OperatorClass)
+            .bind(amateur.GroupCode)
+            .bind(amateur.RegionCode)
+            .bind(amateur.TrusteeCallSign)
+            .bind(amateur.TrusteeIndicator)
+            .bind(amateur.PhysicianCertification)
+            .bind(amateur.VESignature)
+            .bind(amateur.SystematicCallSignChange)
+            .bind(amateur.VanityCallSignChange)
+            .bind(amateur.VanityRelationship)
+            .bind(amateur.PreviousCallSign)
+            .bind(amateur.PreviousOperatorClass)
+            .bind(amateur.TrusteeName)
+            .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();
+}
+
+async fn load_comments(db: &Pool<Sqlite>) {
+    let comments_file = File::open("CO.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&comments_file).lines().count();
+    drop(comments_file);
+
+    let comments_file = File::open("CO.dat").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(false)
+        .from_reader(comments_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("CO.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let comment: Comment = line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_COMMENT_SQL);
+        statement
+            .bind(comment.RecordType)
+            .bind(comment.UniqueSystemIdentifier)
+            .bind(comment.UlsFileNumber)
+            .bind(comment.CallSign)
+            .bind(comment.CommentDate)
+            .bind(comment.Description)
+            .bind(comment.StatusCode)
+            .bind(comment.StatusDate)
+            .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();
+}
+
+async fn load_entities(db: &Pool<Sqlite>) {
     let entities_file = File::open("EN.dat").expect("Error opening file");
     let entities_file_meta = fs::metadata("EN.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&entities_file).lines().count();
+    drop(entities_file);
+
+    let entities_file = File::open("EN.dat").expect("Error opening file");
     let mut transaction = db.begin().await.expect("Error starting transaction");
     let mut reader = csv::ReaderBuilder::new()
         .has_headers(false)
@@ -187,13 +457,22 @@ async fn load_entities(db: Pool<Sqlite>) {
         .quoting(false)
         .from_reader(entities_file);
 
-    // let statement = sqlx::query(INSERT_ENTRY_SQL);
+    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("EN.dat");
 
     for line in reader.records() {
         let line = line.expect("Error reading entry");
         let entity: Entity = line.deserialize(None).expect("Error deserializing entry");
         let statement = sqlx::query(INSERT_ENTITY_SQL);
         statement
+            .bind(entity.RecordType)
             .bind(entity.UniqueSystemIdentifier)
             .bind(entity.UlsFileNumber)
             .bind(entity.EBFNumber)
@@ -226,23 +505,349 @@ async fn load_entities(db: Pool<Sqlite>) {
             .execute(&mut transaction)
             .await
             .expect("Error executing statement");
-        // println!("{:?}", entity);
+        progress_bar.set_position(line.position().unwrap().line());
+    }
+
+    transaction
+        .commit()
+        .await
+        .expect("Error committing transaction");
+    progress_bar.finish();
+}
+
+async fn load_headers(db: &Pool<Sqlite>) {
+    let headers_file = File::open("HD.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&headers_file).lines().count();
+    drop(headers_file);
+
+    let headers_file = File::open("HD.dat").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(false)
+        .from_reader(headers_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("HD.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let header: Header = line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_HEADER_SQL);
+        statement
+            .bind(header.RecordType)
+            .bind(header.UniqueSystemIdentifier)
+            .bind(header.UlsFileNumber)
+            .bind(header.EBFNumber)
+            .bind(header.CallSign)
+            .bind(header.LicenseStatus)
+            .bind(header.RadioServiceCode)
+            .bind(header.GrantDate)
+            .bind(header.ExpiredDate)
+            .bind(header.CancellationDate)
+            .bind(header.EligibilityRuleNumber)
+            .bind(header.Reserved)
+            .bind(header.Alien)
+            .bind(header.AlienGovernment)
+            .bind(header.AlienCorporation)
+            .bind(header.AlienOfficers)
+            .bind(header.AlienControl)
+            .bind(header.Revoked)
+            .bind(header.Convicted)
+            .bind(header.Adjudged)
+            .bind(header.Reserved2)
+            .bind(header.CommonCarrier)
+            .bind(header.NonCommonCarrier)
+            .bind(header.PrivateComm)
+            .bind(header.Fixed)
+            .bind(header.Mobile)
+            .bind(header.Radiolocation)
+            .bind(header.Sattelite)
+            .bind(header.DevelopmentalOrSta)
+            .bind(header.InterconnectedService)
+            .bind(header.CertifierFirstName)
+            .bind(header.CertifierMiddleInitial)
+            .bind(header.CertifierLastName)
+            .bind(header.CertifierSuffix)
+            .bind(header.CertifierTitle)
+            .bind(header.Female)
+            .bind(header.BlackOrAfricanAmerican)
+            .bind(header.NativeAmerican)
+            .bind(header.Hawaiian)
+            .bind(header.Asian)
+            .bind(header.White)
+            .bind(header.Hispanic)
+            .bind(header.EffectiveDate)
+            .bind(header.LastActionDate)
+            .bind(header.AuctionId)
+            .bind(header.BroadcastServicesRegulatoryStatus)
+            .bind(header.BandManagerRegulatoryStatus)
+            .bind(header.BroadcastServicesTypeOfRadioService)
+            .bind(header.AlienRuling)
+            .bind(header.LicenseeNameChange)
+            .bind(header.WhitespaceIndicator)
+            .bind(header.OperationRequirementChoice)
+            .bind(header.OperationRequirementAnswer)
+            .bind(header.DiscontinuationOfService)
+            .bind(header.RegulatoryCompliance)
+            .bind(header.EligibilityCertification900Mhz)
+            .bind(header.TransitionPlanCertification900Mhz)
+            .bind(header.ReturnSpectrumCertification900Mhz)
+            .bind(header.PaymentCertification900Mhz)
+            .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();
+}
+
+async fn load_history(db: &Pool<Sqlite>) {
+    let history_file = File::open("HS.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&history_file).lines().count();
+    drop(history_file);
+
+    let history_file = File::open("HS.dat").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(false)
+        .from_reader(history_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("HS.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let history: History = line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_HISTORY_SQL);
+        statement
+            .bind(history.RecordType)
+            .bind(history.UniqueSystemIdentifier)
+            .bind(history.UlsFileNumber)
+            .bind(history.CallSign)
+            .bind(history.LogDate)
+            .bind(history.Code)
+            .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();
+}
+
+async fn load_license_attachments(db: &Pool<Sqlite>) {
+    let attachments_file = File::open("LA.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&attachments_file).lines().count();
+    drop(attachments_file);
+
+    let attachments_file = File::open("LA.dat").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(false)
+        .from_reader(attachments_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("LA.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let attachment: LicenseAttachment =
+            line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_SPECIAL_CONDITION_SQL);
+        statement
+            .bind(attachment.RecordType)
+            .bind(attachment.UniqueSystemIdentifier)
+            .bind(attachment.CallSign)
+            .bind(attachment.AttachmentCode)
+            .bind(attachment.AttachmentDescription)
+            .bind(attachment.AttachmentDate)
+            .bind(attachment.AttachmentFileName)
+            .bind(attachment.ActionPerformed)
+            .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();
+}
+
+async fn load_special_conditions(db: &Pool<Sqlite>) {
+    let conditions_file = File::open("SC.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&conditions_file).lines().count();
+    drop(conditions_file);
+
+    let conditions_file = File::open("SC.dat").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(false)
+        .from_reader(conditions_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("SC.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let condition: SpecialCondition =
+            line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_SPECIAL_CONDITION_SQL);
+        statement
+            .bind(condition.RecordType)
+            .bind(condition.UniqueSystemIdentifier)
+            .bind(condition.UlsFileNumber)
+            .bind(condition.EBFNumber)
+            .bind(condition.CallSign)
+            .bind(condition.SpecialConditionType)
+            .bind(condition.SpecialConditionCode)
+            .bind(condition.StatusCode)
+            .bind(condition.StatusDate)
+            .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();
+}
+
+async fn load_special_conditions_free_form(db: &Pool<Sqlite>) {
+    let conditions_file = File::open("SF.dat").expect("Error opening file");
+    // let comments_file_meta = fs::metadata("CO.dat").expect("Error getting file metadata");
+    let line_count = std::io::BufReader::new(&conditions_file).lines().count();
+    drop(conditions_file);
+
+    let conditions_file = File::open("SF.dat").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(false)
+        .from_reader(conditions_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("SF.dat");
+
+    for line in reader.records() {
+        let line = line.expect("Error reading entry");
+        let condition: SpecialConditionFreeForm =
+            line.deserialize(None).expect("Error deserializing entry");
+        let statement = sqlx::query(INSERT_SPECIAL_CONDITION_FREE_FORM_SQL);
+        statement
+            .bind(condition.RecordType)
+            .bind(condition.UniqueSystemIdentifier)
+            .bind(condition.UlsFileNumber)
+            .bind(condition.EBFNumber)
+            .bind(condition.CallSign)
+            .bind(condition.LicenseFreeFormType)
+            .bind(condition.UniqueLicenseFreeFormIdentifier)
+            .bind(condition.SequenceNumber)
+            .bind(condition.LicenseFreeFormCondition)
+            .bind(condition.StatusCode)
+            .bind(condition.StatusDate)
+            .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();
 }
 
 #[tokio::main]
 async fn main() {
-    // let output_file = download_file().expect("Error downloading file");
+    let output_file = download_file().expect("Error downloading file");
 
-    // unzip_file(output_file).expect("Error unzipping file");
+    unzip_file(output_file).expect("Error unzipping file");
 
     let db = SqlitePool::connect("sqlite://fcc.db")
         .await
         .expect("Error connecting to database");
-    load_entities(db).await;
+
+    // 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 = fs::read_to_string("CO.dat").expect("Error reading file");
+    fs::write("CO.dat", re.replace_all(&comments, " ").to_string()).expect("Error writing file");
+
+    load_amateurs(&db).await;
+    load_comments(&db).await;
+    load_entities(&db).await;
+    load_headers(&db).await;
+    load_history(&db).await;
+    load_license_attachments(&db).await;
+    load_special_conditions(&db).await;
+    load_special_conditions_free_form(&db).await;
 }