# Copyright (C) 2008 Wes Hardaker
# License: Same as perl.  See the LICENSE file for details.
package Ham::Callsign::DB::US;

use Ham::Callsign::DB;
use Ham::Callsign;
our @ISA = qw(Ham::Callsign::DB);

use strict;

sub init {
    my ($self) = @_;
    # maybe use dbh to prepare a few things

sub do_load_data {
    my ($self, $place) = @_;
    $self->{'import_place'} = $place;
    $self->insert_stuff('HD', 50);
    $self->insert_stuff('EN', 27);
    $self->insert_stuff('AM', 18);
    $self->insert_stuff('VC', 6);

sub do_lookup {
    my ($self, $callsign) = @_;
    if (!$self->{'lookupvc'}) {
	$self->{'lookupvc'} = $self->{'dbh'}->prepare("
    select vc.callsign_requested as thecallsign, *
             from PUBACC_VC as vc
        left join PUBACC_HD as hd 
               on hd.unique_system_identifier = vc.unique_system_identifier
        left join PUBACC_EN as en
               on en.unique_system_identifier = vc.unique_system_identifier
        left join PUBACC_AM as am
               on am.unique_system_identifier = vc.unique_system_identifier
            where vc.callsign_requested = ?
        order by vc.unique_system_identifier desc
        limit 1
	$self->{'lookuphd'} = $self->{'dbh'}->prepare("
    select hd.call_sign as thecallsign, *
             from PUBACC_HD as hd
        left join PUBACC_VC as vc
               on vc.unique_system_identifier = hd.unique_system_identifier
        left join PUBACC_EN as en
               on en.unique_system_identifier = hd.unique_system_identifier
        left join PUBACC_AM as am
               on am.unique_system_identifier = hd.unique_system_identifier
            where hd.call_sign = ?
        order by hd.unique_system_identifier desc
        limit 1

    my $row1 = $self->{'lookupvc'}->fetchrow_hashref;

    my $row2 = $self->{'lookuphd'}->fetchrow_hashref;

    my @results;
    if ($row1 && $row2 && !$self->{'USmultiple'}) {
	# take the most recent ULS file number
	if ($row1->{'uls_file_number'} > $row2->{'uls_file_number'}) {
	    $row2 = undef;
	} else {
	    $row1 = undef;
    if ($row1) {
	$row1->{'qth'} = "$row1->{city}, $row1->{state}, USA";
	push @results,new Ham::Callsign(%$row1)
    if ($row2) {
	$row2->{'qth'} = "$row2->{city}, $row2->{state}, USA";
	push @results,new Ham::Callsign(%$row2) if ($row2);
    map { $_->{'FromDB'} = 'US' } @results;
    return \@results;

# from fcc docs:
#  EN: Names and Addresses
#  HD: Main form 605 that carries over to license
#  AM: Amature data
#  VC: Vanity Callsign
# data can be joined "whenever the column name is the same in both tables"
#   - primary column for joining is the call sign
#   - joining for application data is the ULS file number (we don't load apps)
#   - Each application and license has been assigned a unique 9-digit system id
#     This is useful in cases where a call sign has been reassigned.
sub do_create_tables {
    my ($self) = @_;

    my $dbh = $self->{'dbh'};

    # These SQL statements were pulled from the FCC documentation at
    # http://wireless.fcc.gov/uls/index.htm?job=transaction&page=weekly

    # the FCC updates their schema on a regular basis and this module
    # will need to track those changes.

    $dbh->do("drop table PUBACC_EN");
    $dbh->do("create table PUBACC_EN
      record_type               char(2)              not null,
      unique_system_identifier  numeric(9,0)         not null,
      uls_file_number           char(14)             null,
      ebf_number                varchar(30)          null,
      call_sign                 char(10)             null,
      entity_type               char(2)              null,
      licensee_id               char(9)              null,
      entity_name               varchar(200)         null,
      first_name                varchar(20)          null,
      mi                        char(1)              null,
      last_name                 varchar(20)          null,
      suffix                    char(3)              null,
      phone                     char(10)             null,
      fax                       char(10)             null,
      email                     varchar(50)          null,
      street_address            varchar(60)          null,
      city                      varchar(20)          null,
      state                     char(2)              null,
      zip_code                  char(9)              null,
      po_box                    varchar(20)          null,
      attention_line            varchar(35)          null,
      sgin                      char(3)              null,
      frn                       char(10)             null,
      applicant_type_code       char(1)              null,
      applicant_type_other      char(40)             null,
      status_code               char(1)		     null,
      status_date		datetime	     null

    $dbh->do("CREATE index callsign_index_en_id on PUBACC_EN(unique_system_identifier)");

    $dbh->do("drop table PUBACC_AM");
    $dbh->do("create table PUBACC_AM
      record_type               char(2)              not null,
      unique_system_identifier  numeric(9,0)         not null,
      uls_file_num              char(14)             null,
      ebf_number                varchar(30)          null,
      callsign                  char(10)             null,
      operator_class            char(1)              null,
      group_code                char(1)              null,
      region_code               tinyint          null,
      trustee_callsign          char(10)             null,
      trustee_indicator         char(1)              null,
      physician_certification   char(1)              null,
      ve_signature              char(1)              null,
      systematic_callsign_change char(1)             null,
      vanity_callsign_change    char(1)              null,
      vanity_relationship       char(12)             null,
      previous_callsign         char(10)             null,
      previous_operator_class   char(1)              null,
      trustee_name              varchar(50)          null

    $dbh->do("CREATE index callsign_index_am_id on PUBACC_AM(unique_system_identifier)");

    $dbh->do("drop table PUBACC_HD");
    $dbh->do("create table PUBACC_HD
      record_type               char(2)              not null,
      unique_system_identifier  numeric(9,0)         not null,
      uls_file_number           char(14)             null,
      ebf_number                varchar(30)          null,
      call_sign                 char(10)             null,
      license_status            char(1)              null,
      radio_service_code        char(2)              null,
      grant_date                char(10)             null,
      expired_date              char(10)             null,
      cancellation_date         char(10)             null,
      eligibility_rule_num      char(10)             null,
      applicant_type_code_reserved       char(1)              null,
      alien                     char(1)              null,
      alien_government          char(1)              null,
      alien_corporation         char(1)              null,
      alien_officer             char(1)              null,
      alien_control             char(1)              null,
      revoked                   char(1)              null,
      convicted                 char(1)              null,
      adjudged                  char(1)              null,
      involved_reserved      char(1)              null,
      common_carrier            char(1)              null,
      non_common_carrier        char(1)              null,
      private_comm              char(1)              null,
      fixed                     char(1)              null,
       mobile                    char(1)              null,
      radiolocation             char(1)              null,
      satellite                 char(1)              null,
      developmental_or_sta      char(1)              null,
      interconnected_service    char(1)              null,
      certifier_first_name      varchar(20)          null,
      certifier_mi              char(1)              null,
      certifier_last_name       varchar(20)          null,
      certifier_suffix          char(3)              null,
      certifier_title           char(40)             null,
      gender                    char(1)              null,
      african_american          char(1)              null,
      native_american           char(1)              null,
      hawaiian                  char(1)              null,
      asian                     char(1)              null,
      white                     char(1)              null,
      ethnicity                 char(1)              null,
      effective_date            char(10)             null,
      last_action_date          char(10)             null,
      auction_id                int              null,
      reg_stat_broad_serv       char(1)              null,
      band_manager              char(1)              null,
      type_serv_broad_serv      char(1)              null,
	alien_ruling              char(1)              null,
      licensee_name_change	char(1)		     null

    $dbh->do("CREATE index callsign_index_hd_id on PUBACC_HD(unique_system_identifier)");
    $dbh->do("CREATE index callsign_index_HD_SIGN on PUBACC_HD(call_sign)");

    $dbh->do("drop table PUBACC_VC");
    $dbh->do("create table PUBACC_VC

      record_type               char(2)              null,
      unique_system_identifier  numeric(9,0)         not null,
      uls_file_number           char(14)             null,
      ebf_number                varchar(30)          null,
      request_sequence          int              null,
      callsign_requested        char(10)             null

    $dbh->do("CREATE index callsign_index_VC_SIGN on PUBACC_VC(callsign_requested)");
    $dbh->do("CREATE index callsign_index_vc_id on PUBACC_VC(unique_system_identifier)");

# DB insertion code

# split doesn't always return the right number of entries...  it
# chomps the end for some reason sometimes.  This array fills in the blanks.
# XXX: this is likely a problem for some other reason and this is a
# possibly broken hack.
my @fill;

for (my $i = 1; $i <= 100; $i++) {
  for (my $j = 0; $j < $i; $j++) {
    push @{$fill[$i]}, '';

# a generic function that inserts $num rows into a table
sub insert_stuff {
    my ($self, $suffix, $num) = @_;

    my $count = 0;
    my @parts;

    my $dbh = $self->{'dbh'};

    my $sth = $dbh->prepare("insert into PUBACC_$suffix values(" .
			    ("?, " x ($num-1)). " ?)");

    $| = 1;
    print "starting $suffix\n";
    while (<I>) {
	@parts = split(/\|/);
#	print "parts: $_\n  " . join(",",@parts). "\n" if ($count == 0);
 	my $diff = $num - $#parts - 1;
 	$sth->execute(@parts, @{$fill[$diff]});
#	$sth->execute(@parts);
	if ($count % 10000 == 0) {
	    print ".";

    print "inserted: $count rows into $suffix\n";



=head1 NAME