#!/usr/bin/perl

use strict;
use DBI;
use FS::UID qw(adminsuidsetup);
use FS::rate_prefix;
use FS::rate_region;
use FS::rate_detail;
use FS::Record qw(qsearch qsearchs dbh);

# delete from rate;
# Create interstate and intrastate rate plans
#
# delete from rate_detail;
# delete from rate_region;
# delete from rate_prefix;

# Assumption: 1-to-1 relationship between rate_region and rate_prefix, with
# two rate_detail per rate_region: one for interstate; one for intrastate
#
# run the script, setting the appropriate values below.

####### SET THESE! ####################
my $intra_ratenum = 3;
my $inter_ratenum = 2;
my $intra_class = 1;
my $inter_class = 2;
#my $file = "/home/levinse/domestic_interstate.xls";
my $file = "/home/ivan/vnes/VNES Domestic VoIP Termination Deck 8-9-11.csv";
#my $sheet_name = 'domestic_interstate';
#######################################

my $user = shift or die "no user specified";
adminsuidsetup $user;

local $SIG{HUP} = 'IGNORE';
local $SIG{INT} = 'IGNORE';
local $SIG{QUIT} = 'IGNORE';
local $SIG{TERM} = 'IGNORE';
local $SIG{TSTP} = 'IGNORE';
local $SIG{PIPE} = 'IGNORE';

my $oldAutoCommit = $FS::UID::AutoCommit;
local $FS::UID::AutoCommit = 0;
my $dbhfs = dbh;

#my $dbh = DBI->connect("DBI:Excel:file=$file")
#  or die "can't connect: $DBI::errstr";

#my $sth = $dbh->prepare("select * from $sheet_name")
#  or die "can't prepare: ". $dbh->errstr;
#$sth->execute
#  or die "can't execute: ". $sth->errstr;

use Text::CSV_XS;
my $csv = Text::CSV_XS->new or die Text::CSV->error_diag;

open(my $fh, "<$file") or die $!;
my $header = scalar(<$fh>); #NPA, NXX, LATA, State, Intrastate, Interstate

my @rp_cache = qsearch('rate_prefix', {} );# or die "can't cache rate_prefix";
my %rp_cache = map { $_->npa => $_ } @rp_cache;

sub fatal {
    my $msg = shift;
    $dbhfs->rollback if $oldAutoCommit;
    die $msg;
}

while ( my $row = $csv->getline($fh) ) {

  #my $lata = $row->{'lata'};
  #my $ocn = $row->{'ocn'};
  #my $state = $row->{'state'};
  #my $rate = $row->{'rate'};
  #my $npanxx = $row->{'lrn'};

  #NPA, NXX, LATA, State, Intrastate, Interstate
  my $npa        = $row->[0];
  my $nxx        = $row->[1];
  my $lata       = $row->[2];
  my $state      = $row->[3];
  ( my $intra_rate = $row->[4] ) =~ s/^\s*\$//;
  ( my $inter_rate = $row->[5] ) =~ s/^\s*\$//;

  next if $lata == '99999';

  my $error = '';

  my $rp;
  if ( $rp_cache{$npa.$nxx} ) {
      $rp = $rp_cache{$npa.$nxx};
  } 
  else {
     my $rr = new FS::rate_region { 'regionname' => $state };
     $error = $rr->insert;
     fatal("can't insert rr") if $error;

     $rp = new FS::rate_prefix {   'countrycode'   => '1',
                                   'npa'           => $npa.$nxx, #$npanxx
                                   #'ocn'           => $ocn,
                                   'state'         => $state,
                                   'latanum'       => $lata,
                                   'regionnum'     => $rr->regionnum,
                               }; 
     $error = $rp->insert;
     fatal("can't insert rp") if $error;
     $rp_cache{$npa.$nxx} = $rp;
  }

  #use Data::Dumper;
  #warn Dumper($rp);

  my %hash = ( 'min_included'    => 0,
               'sec_granularity' => 6, #60,
               'dest_regionnum'  => $rp->regionnum,
             );

  my $intra_rd = new FS::rate_detail { 'ratenum'         => $intra_ratenum,
                                       'min_charge'      => $intra_rate,
                                       'intra_class'     => $intra_class,
                                       %hash,
                                     };
  $error = $intra_rd->insert;
  fatal("can't insert (intra) rd: $error") if $error;

  my $inter_rd = new FS::rate_detail { 'ratenum'         => $inter_ratenum,
                                       'min_charge'      => $inter_rate,
                                       'inter_class'     => $inter_class,
                                       %hash,
                                     };
  $error = $inter_rd->insert;
  fatal("can't insert (inter) rd: $error") if $error;
}
$csv->eof or $csv->error_diag ();
close $fh;

$dbhfs->commit or die $dbhfs->errstr if $oldAutoCommit;