#!/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::Log;
use FS::cdr;
use FS::DBI;
use Getopt::Std;

$DEBUG = 0;

my %opt;
getopts('e:H:U:P:D:T:V:Q', \%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};

adminsuidsetup $user;

my $log = FS::Log->new( 'freeside-cdr-asterisk_sql' );

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

if ( $dbi ) {
  log_msg( info => "Established connection to CDR database at dsn($dsn)" );
} else {
  log_and_die( error =>
    sprintf 'Fatal error connecting to CDR database at dsn(%s): %s',
      $dsn,
      $FS::DBI::errstr
  );
}

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 ) {
    log_msg( warn => "Adding freesidestatus column" );
    $dbi->do("ALTER TABLE $table ADD COLUMN freesidestatus varchar(32)")
      or log_and_die( error => $dbi->errstr );
  } else {
    log_msg( info => "freesidestatus column present" );
  }
}

my @cols = ();
if ( $opt{V} >= 12 ) {

  push @cols, qw( id )
    unless $opt{Q};

  push @cols, qw( start answer );

  if ( $engine =~ /^Pg/ ) {
    push @cols, "'end'";
  } else {
    push @cols, "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
  or log_and_die( error => $sth->errstr );

log_msg( info => sprintf 'Importing %s records', $sth->rows );

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

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));
    $cdr->cdrid($cdr->id);
  } else {
    $cdr->startdate(str2time($cdr->calldate));
  }

  $cdr->cdrbatchnum($cdrbatchnum);

  $cdr->amaflags('') unless $cdr->amaflags =~ /^(\d+)$/;

  if ( my $error = $cdr->insert ) {
    log_and_die( error =>
      sprintf 'Fatal failure to import uniqueid(%s) from table(%s): %s',
        $row->{'uniqueid'},
        $table,
        $error
    );
  } else {

    $imports++;

    my $usql = "UPDATE $table SET freesidestatus = 'done' WHERE ";
    my @args = ();
    if ( $opt{V} >= 12 ) {
      if ( $row->{'id'} ) {
        $usql .= ' id = '. $row->{'id'};
        @args = ( undef );
      } else {
        $usql .= ' uniqueid = ?';
        @args = ( undef, $row->{'uniqueid'} );
      }
    } else {
      $usql .= ' calldate = ? AND src = ? AND dst = ?';
      @args = ( undef, $row->{'calldate'}, $row->{'src'}, $row->{'dst'}, );
    }

    if ( $dbi->do($usql, @args) ) {
      $updates++;
    } else {
      log_and_die( error =>
        sprintf 'Fatal failure set status(done) uniqueid(%s) table(%s): %s',
          $row->{'uniqueid'},
          $table,
          $dbi->errstr
      );
    }

  }

}

log_and_warn(
  info => "Done.\nImported $imports CDRs, marked $updates CDRs as done"
);

$dbi->disconnect;

sub log_and_die {
  my ( $level, $message ) = @_;
  $log->$level( $message );
  die "[$level] $message\n";
}

sub log_msg {
  my ( $level, $message ) = @_;
  $log->$level( $message );
  warn "[$level] $message\n"
    if $opt{v};
}

sub log_and_warn {
  my ( $level, $message ) = @_;
  $log->$level( $message );
  warn "$message\n";
}


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.

-Q: Use uniqueid field instead of id (Asterisk v12 only).  Less efficient, but useful if the field is missing.

=cut

1;