#!/usr/bin/perl

use strict;
use Text::CSV;
use FS::Misc::Getopt;
use FS::Record qw(qsearchs qsearch dbh);
use FS::rate;
use FS::rate_region;
use FS::rate_prefix;
use FS::rate_detail;

getopts('');

$FS::UID::AutoCommit = 0;
my $dbh = dbh;

my $file = shift or usage();
open my $in, '<', $file or die "$file: $!\n";
my $csv = Text::CSV->new({ binary => 1, auto_diag => 2 });
# set header row
$csv->column_names($csv->getline($in));

my $error;

my $granularity = 1;
# default is to charge per second; edit this if needed

while (my $row = $csv->getline_hr($in)) {
  print $csv->string;

  # ProfileKey is just a number
  my $rate = qsearchs('rate', { 'ratename' => $row->{'ProfileKey'} });
  if (!$rate) {
    $rate = FS::rate->new({ 'ratename' => $row->{'ProfileKey'} });
    $error = $rate->insert;
    die $error if $error;
  }

  # DestinationId looks like "Country - City" or "Country - Mobile -
  # Carrier" (or sometimes just "Country - Mobile").
  my $region = qsearchs('rate_region', {
      'regionname' => $row->{'DestinationId'}
  });
  if (!$region) {
    $region = FS::rate_region->new({
      'regionname' => $row->{'DestinationId'}
    });
    $error = $region->insert;
    die $error if $error;
  }

  # Prefix strings found in there look like
  # "e164:123-45-6nnnnnnn-"
  # The first group of digits is the country code, any others are the
  # prefix. Sometimes the nnnn's are NNNN's. The dashes are not guaranteed
  # to be anywhere specific.
  # Catchall prefixes start with "-A", which has a meaning like "match
  # anything, but at a lower priority than a digit match".
  # NANPA numbers use "1-", and for a catchall area code use "1-AAA-".
  my $cc_long = $row->{CountryCodeLong};
  $cc_long =~ /^e164:(\d+)-([\d-]*)A*-?n+-$/i;
  my $countrycode = $1;
  if (!$countrycode) { # totally legit reasons for this, e.g. 1-AAA-411
    warn "can't parse number prefix:\n$cc_long\n";
    next;
  }
  my $prefix = $2;
  $prefix =~ s/-//g;

  my %prefix = (
      'regionnum'   => $region->regionnum,
      'countrycode' => $countrycode,
      'npa'         => $prefix,
  );
  my $rate_prefix = qsearchs('rate_prefix', \%prefix);
  if (!$rate_prefix) {
    $rate_prefix = FS::rate_prefix->new(\%prefix);
    $error = $rate_prefix->insert;
    die $error if $error;
  }

  # enough to identify the detail
  my %detail = (
    'ratenum'         => $rate->ratenum,
    'dest_regionnum'  => $region->regionnum,
    'cdrtypenum'      => '',
    'ratetimenum'     => '',
  );
  my $dest_detail = qsearchs('rate_detail', \%detail);
  # ProfileRate is 5 decimal places, same as rate_detail.min_charge
  if (!$dest_detail) {
    $dest_detail = FS::rate_detail->new({
        %detail,
        'min_included'    => 0,
        'min_charge'      => $row->{ProfileRate},
        'sec_granularity' => $granularity,
    });
    $error = $dest_detail->insert;
  } else {
    local $FS::Record::nowarn_identical = 1;
    $dest_detail->set('min_charge' => $row->{ProfileRate});
    $error = $dest_detail->replace;
  }
  die $error if $error;
}
dbh->commit;
print "Finished.\n";


sub usage {
  die "Usage: rate-intl.import <user> <file>.csv\n\n";
}