#!/usr/bin/perl

use strict;
use vars qw( $DEBUG );
use Date::Parse 'str2time';
use Date::Format 'time2str';
use FS::UID qw(adminsuidsetup dbh);
use FS::cdr;
use DBI;
use Getopt::Std;

$DEBUG = 0;

my %opt;
getopts('e:H:U:P:D:T:V:', \%opt);
my $user = shift or die &usage;

my $engine = $opt{e} || 'mysql';

my $dsn = "dbi:$engine";
$dsn .= ":database=$opt{D}"; # if $opt{D};
$dsn .= ";host=$opt{H}" if $opt{H};

my $dbi = DBI->connect($dsn, $opt{U}, $opt{P}) 
  or die $DBI::errstr;

adminsuidsetup $user;

my $fsdbh = FS::UID::dbh;

my $table = $opt{T} || 'cdr';

# check for existence of freesidestatus
if ( $engine =~ /^mysql/ ) {
  my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'freesidestatus'");
  if( ! @$status ) {
    warn "Adding freesidestatus column...\n" if $DEBUG;
    $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
      or die $dbi->errstr;
  } else {
    warn "freesidestatus column present\n" if $DEBUG;
  }
}

my @cols = ();
if ( $opt{V} > 12 ) {
  push @cols, qw( start answer end );
} else {
  push @cols, qw( calldate );
}

push @cols,  qw( 
  clid src dst dcontext channel lastapp lastdata duration 
  billsec disposition amaflags accountcode userfield
);

# check for existence of uniqueid
if ( $engine =~ /^mysql/ ) {
  my $status = $dbi->selectall_arrayref("SHOW COLUMNS FROM $table WHERE Field = 'uniqueid'");
  if( @$status ) {
    push @cols, 'uniqueid';
  }
} else {
  #assume there's a uniqueid
  push @cols, 'uniqueid';
}

my $sql =
  'SELECT '.join(',', @cols). " FROM $table WHERE freesidestatus IS NULL";
my $sth = $dbi->prepare($sql);
$sth->execute;
warn "Importing ".$sth->rows." records...\n" if $DEBUG;

my $cdr_batch = new FS::cdr_batch({ 
    'cdrbatch' => 'sql-import-'. time2str('%Y/%m/%d-%T',time),
  });
my $error = $cdr_batch->insert;
die $error if $error;
my $cdrbatchnum = $cdr_batch->cdrbatchnum;

my $imports = 0;
my $updates = 0;

while ( my $row = $sth->fetchrow_hashref ) {

  my $cdr = FS::cdr->new($row);

  if ( $opt{V} > 12 ) {
    $cdr->calldate($cdr->start);
    $cdr->startdate(str2time($cdr->start));
    $cdr->answerdate(str2time($cdr->answer));
    $cdr->enddate(str2time($cdr->end));
  } else {
    $cdr->startdate(str2time($cdr->calldate));
  }

  $cdr->cdrbatchnum($cdrbatchnum);

  my $error = $cdr->insert;
  if ($error) {
    warn "failed import: $error\n";
  } else {
    $imports++;
    if( $dbi->do("UPDATE cdr SET freesidestatus = 'done' 
        WHERE calldate = ? AND src = ? AND dst = ?",
                undef,
                $row->{'calldate'},
                $row->{'src'},
                $row->{'dst'},

      ) ) {
        $updates++;
    } else {
      warn "failed to set status: ".$dbi->errstr."\n";
    }
  }

}

warn "Done.\nImported $imports CDRs, marked $updates CDRs as done.\n";
$dbi->disconnect;

sub usage {
  "Usage: \n  freeside-cdr-asterisk_sql\n\t-e mysql|Pg|... [ -H host ]n\t-D database\n\t[ -T table ]\n\t[ -V asterisk_version]\n\t-U user\n\t-P password\n\tfreesideuser\n";
}

=head1 NAME

freeside-cdr-asterisk_sql - Import CDRs from an Asterisk SQL database

=head1 SYNOPSIS

  freeside-cdr-asterisk_sql -e mysql|Pg|... [ -H host ] -D database [ -T table ][ -V asterisk_version ] -U user -P password freesideuser

=head1 DESCRIPTION

Imports CDR records from an Asterisk SQL database.

-H: hostname

-e: Database engine (default mysql)

-D: database name

-T: table name (default cdr)

-U: username

-P: password

-V: Asterisk version.  Values of 12 or higher have a new database schema which no longer includes calldate.  Currently defaults to 11, but may default to 12+ or be required in a future, so best to always specify the version.

=cut

1;