#!/usr/bin/perl -Tw

#to delay loading dbdef until we're ready
BEGIN { $FS::Record::setup_hack = 1; }

use strict;
use vars qw($opt_s);
use Getopt::Std;
use DBI;
use DBIx::DBSchema 0.21;
use DBIx::DBSchema::Table;
use DBIx::DBSchema::Column;
use DBIx::DBSchema::ColGroup::Unique;
use DBIx::DBSchema::ColGroup::Index;
use FS::UID qw(adminsuidsetup datasrc checkeuid getsecrets);
use FS::Record;
use FS::cust_main_county;
use FS::raddb;
use FS::part_bill_event;

die "Not running uid freeside!" unless checkeuid();

my %attrib2db =
  map { lc($FS::raddb::attrib{$_}) => $_ } keys %FS::raddb::attrib;

getopts("s");
my $user = shift or die &usage;
getsecrets($user);

#needs to match FS::Record
my($dbdef_file) = "/usr/local/etc/freeside/dbdef.". datasrc;

###

#print "\nEnter the maximum username length: ";
#my($username_len)=&getvalue;
my $username_len = 32; #usernamemax config file

#print "\n\n", <<END, ":";
#Freeside tracks the RADIUS User-Name, check attribute Password and
#reply attribute Framed-IP-Address for each user.  You can specify additional
#check and reply attributes (or you can add them later with the
#fs-radius-add-check and fs-radius-add-reply programs).
#
#First enter any additional RADIUS check attributes you need to track for each 
#user, separated by whitespace.
#END
#my @check_attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; }
#                         split(" ",&getvalue);
#
#print "\n\n", <<END, ":";
#Now enter any additional reply attributes you need to track for each user,
#separated by whitespace.
#END
#my @attributes = map { $attrib2db{lc($_)} or die "unknown attribute $_"; }
#                   split(" ",&getvalue);
#
#print "\n\n", <<END, ":";
#Do you wish to enable the tracking of a second, separate shipping/service
#address?
#END
#my $ship = &_yesno;
#
#sub getvalue {
#  my($x)=scalar(<STDIN>);
#  chop $x;
#  $x;
#}
#
#sub _yesno {
#  print " [y/N]:";
#  my $x = scalar(<STDIN>);
#  $x =~ /^y/i;
#}

my @check_attributes = (); #add later
my @attributes = (); #add later
my $ship = $opt_s;

###

my($char_d) = 80; #default maxlength for text fields

#my(@date_type)  = ( 'timestamp', '', ''     );
my(@date_type)  = ( 'int', 'NULL', ''     );
my(@perl_type) = ( 'text', 'NULL', ''  ); 
my @money_type = ( 'decimal',   '', '10,2' );

###
# create a dbdef object from the old data structure
###

my(%tables)=&tables_hash_hack;

#turn it into objects
my($dbdef) = new DBIx::DBSchema ( map {  
  my(@columns);
  while (@{$tables{$_}{'columns'}}) {
    my($name,$type,$null,$length)=splice @{$tables{$_}{'columns'}}, 0, 4;
    push @columns, new DBIx::DBSchema::Column ( $name,$type,$null,$length );
  }
  DBIx::DBSchema::Table->new(
    $_,
    $tables{$_}{'primary_key'},
    DBIx::DBSchema::ColGroup::Unique->new($tables{$_}{'unique'}),
    DBIx::DBSchema::ColGroup::Index->new($tables{$_}{'index'}),
    @columns,
  );
} (keys %tables) );

my $cust_main = $dbdef->table('cust_main');
unless ($ship) { #remove ship_ from cust_main
  $cust_main->delcolumn($_) foreach ( grep /^ship_/, $cust_main->columns );
} else { #add indices
  push @{$cust_main->index->lol_ref},
    map { [ "ship_$_" ] } qw( last company daytime night fax );
}

#add radius attributes to svc_acct

my($svc_acct)=$dbdef->table('svc_acct');

my($attribute);
foreach $attribute (@attributes) {
  $svc_acct->addcolumn ( new DBIx::DBSchema::Column (
    'radius_'. $attribute,
    'varchar',
    'NULL',
    $char_d,
  ));
}

foreach $attribute (@check_attributes) {
  $svc_acct->addcolumn( new DBIx::DBSchema::Column (
    'rc_'. $attribute,
    'varchar',
    'NULL',
    $char_d,
  ));
}

#create history tables (false laziness w/create-history-tables)
foreach my $table ( grep { ! /^h_/ } $dbdef->tables ) {
  my $tableobj = $dbdef->table($table)
    or die "unknown table $table";

  die "unique->lol_ref undefined for $table"
    unless defined $tableobj->unique->lol_ref;
  die "index->lol_ref undefined for $table"
    unless defined $tableobj->index->lol_ref;

  my $h_tableobj = DBIx::DBSchema::Table->new( {
    name        => "h_$table",
    primary_key => 'historynum',
    unique      => DBIx::DBSchema::ColGroup::Unique->new( [] ),
    'index'     => DBIx::DBSchema::ColGroup::Index->new( [
                     @{$tableobj->unique->lol_ref},
                     @{$tableobj->index->lol_ref}
                   ] ),
    columns     => [
                     DBIx::DBSchema::Column->new( {
                       'name'    => 'historynum',
                       'type'    => 'serial',
                       'null'    => 'NOT NULL',
                       'length'  => '',
                       'default' => '',
                       'local'   => '',
                     } ),
                     DBIx::DBSchema::Column->new( {
                       'name'    => 'history_date',
                       'type'    => 'int',
                       'null'    => 'NULL',
                       'length'  => '',
                       'default' => '',
                       'local'   => '',
                     } ),
                     DBIx::DBSchema::Column->new( {
                       'name'    => 'history_user',
                       'type'    => 'varchar',
                       'null'    => 'NOT NULL',
                       'length'  => '80',
                       'default' => '',
                       'local'   => '',
                     } ),
                     DBIx::DBSchema::Column->new( {
                       'name'    => 'history_action',
                       'type'    => 'varchar',
                       'null'    => 'NOT NULL',
                       'length'  => '80',
                       'default' => '',
                       'local'   => '',
                     } ),
                     map {
                       my $column = $tableobj->column($_);

                       #clone so as to not disturb the original
                       $column = DBIx::DBSchema::Column->new( {
                         map { $_ => $column->$_() }
                           qw( name type null length default local )
                       } );

                       $column->type('int')
                         if $column->type eq 'serial';
                       #$column->default('')
                       #  if $column->default =~ /^nextval\(/i;
                       #( my $local = $column->local ) =~ s/AUTO_INCREMENT//i;
                       #$column->local($local);
                       $column;
                     } $tableobj->columns
                   ],
  } );
  $dbdef->addtable($h_tableobj);
}

#important
$dbdef->save($dbdef_file);
&FS::Record::reload_dbdef($dbdef_file);

###
# create 'em
###

my($dbh)=adminsuidsetup $user;

#create tables
$|=1;

foreach my $statement ( $dbdef->sql($dbh) ) {
  $dbh->do( $statement )
    or die "CREATE error: ". $dbh->errstr. "\ndoing statement: $statement";
}

#not really sample data (and shouldn't default to US)

#cust_main_county

#USPS state codes
foreach ( qw(
AL AK AS AZ AR CA CO CT DC DE FM FL GA GU HI ID IL IN IA KS KY LA
ME MH MD MA MI MN MS MO MT NC ND NE NH NJ NM NV NY MP OH OK OR PA PW PR RI 
SC SD TN TX UT VT VI VA WA WV WI WY AE AA AP
) ) {
  my($cust_main_county)=new FS::cust_main_county({
    'state' => $_,
    'tax'   => 0,
    'country' => 'US',
  });  
  my($error);
  $error=$cust_main_county->insert;
  die $error if $error;
}

#AU "offical" state codes ala mark.williamson@ebbs.com.au (Mark Williamson)
foreach ( qw(
VIC NSW NT QLD TAS ACT WA SA
) ) {
  my($cust_main_county)=new FS::cust_main_county({
    'state' => $_,
    'tax'   => 0,
    'country' => 'AU',
  });  
  my($error);
  $error=$cust_main_county->insert;
  die $error if $error;
}

#ISO 2-letter country codes (same as country TLDs) except US and AU
foreach ( qw(
AF AL DZ AS AD AO AI AQ AG AR AM AW AT AZ BS BH BD BB BY BE BZ BJ BM BT BO
BA BW BV BR IO BN BG BF BI KH CM CA CV KY CF TD CL CN CX CC CO KM CG CK CR CI
HR CU CY CZ DK DJ DM DO TP EC EG SV GQ ER EE ET FK FO FJ FI FR FX GF PF TF GA
GM GE DE GH GI GR GL GD GP GU GT GN GW GY HT HM HN HK HU IS IN ID IR IQ IE IL
IT JM JP JO KZ KE KI KP KR KW KG LA LV LB LS LR LY LI LT LU MO MK MG MW MY MV
ML MT MH MQ MR MU YT MX FM MD MC MN MS MA MZ MM NA NR NP NL AN NC NZ NI NE NG
NU NF MP NO OM PK PW PA PG PY PE PH PN PL PT PR QA RE RO RU RW KN LC VC WS SM
ST SA SN SC SL SG SK SI SB SO ZA GS ES LK SH PM SD SR SJ SZ SE CH SY TW TJ TZ
TH TG TK TO TT TN TR TM TC TV UG UA AE GB UM UY UZ VU VA VE VN VG VI WF EH
YE YU ZR ZM ZW
) ) {
  my($cust_main_county)=new FS::cust_main_county({
    'tax'   => 0,
    'country' => $_,
  });  
  my($error);
  $error=$cust_main_county->insert;
  die $error if $error;
}

#billing events
foreach my $aref ( 
  [ 'COMP', 'Comp invoice', '$cust_bill->comp();', 30, 'comp' ],
  [ 'CARD', 'Batch card', '$cust_bill->batch_card();', 40, 'batch-card' ],
  [ 'BILL', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
  [ 'DCRD', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
  [ 'DCHK', 'Send invoice', '$cust_bill->send();', 50, 'send' ],
) {

  my $part_bill_event = new FS::part_bill_event({
    'payby' => $aref->[0],
    'event' => $aref->[1],
    'eventcode' => $aref->[2],
    'seconds' => 0,
    'weight' => $aref->[3],
    'plan' => $aref->[4],
  });
  my($error);
  $error=$part_bill_event->insert;
  die $error if $error;

}

$dbh->commit or die $dbh->errstr;
$dbh->disconnect or die $dbh->errstr;

#print "Freeside database initialized sucessfully\n";

sub usage {
  die "Usage:\n  freeside-setup [ -s ] user\n"; 
}

###
# Now it becomes an object.  much better.
###
sub tables_hash_hack {

  #note that s/(date|change)/_$1/; to avoid keyword conflict.
  #put a kludge in FS::Record to catch this or? (pry need some date-handling
  #stuff anyway also)

  my(%tables)=( #yech.}

    'agent' => {
      'columns' => [
        'agentnum', 'serial',            '',     '',
        'agent',    'varchar',           '',     $char_d,
        'typenum',  'int',            '',     '',
        'freq',     'int',       'NULL', '',
        'prog',     @perl_type,
        'disabled',     'char', 'NULL', 1,
        'username', 'varchar',       'NULL',     $char_d,
        '_password','varchar',       'NULL',     $char_d,
      ],
      'primary_key' => 'agentnum',
      'unique' => [],
      'index' => [ ['typenum'], ['diabled'] ],
    },

    'agent_type' => {
      'columns' => [
        'typenum',   'serial',  '', '',
        'atype',     'varchar', '', $char_d,
      ],
      'primary_key' => 'typenum',
      'unique' => [],
      'index' => [],
    },

    'type_pkgs' => {
      'columns' => [
        'typenum',   'int',  '', '',
        'pkgpart',   'int',  '', '',
      ],
      'primary_key' => '',
      'unique' => [ ['typenum', 'pkgpart'] ],
      'index' => [ ['typenum'] ],
    },

    'cust_bill' => {
      'columns' => [
        'invnum',    'serial',  '', '',
        'custnum',   'int',  '', '',
        '_date',     @date_type,
        'charged',   @money_type,
        'printed',   'int',  '', '',
        'closed',    'char', 'NULL', 1,
      ],
      'primary_key' => 'invnum',
      'unique' => [],
      'index' => [ ['custnum'], ['_date'] ],
    },

    'cust_bill_event' => {
      'columns' => [
        'eventnum',    'serial',  '', '',
        'invnum',   'int',  '', '',
        'eventpart',   'int',  '', '',
        '_date',     @date_type,
        'status', 'varchar', '', $char_d,
        'statustext', 'text', 'NULL', '',
      ],
      'primary_key' => 'eventnum',
      #no... there are retries now #'unique' => [ [ 'eventpart', 'invnum' ] ],
      'unique' => [],
      'index' => [ ['invnum'], ['status'] ],
    },

    'part_bill_event' => {
      'columns' => [
        'eventpart',    'serial',  '', '',
        'payby',       'char',  '', 4,
        'event',       'varchar',           '',     $char_d,
        'eventcode',    @perl_type,
        'seconds',     'int', 'NULL', '',
        'weight',      'int', '', '',
        'plan',       'varchar', 'NULL', $char_d,
        'plandata',   'text', 'NULL', '',
        'disabled',     'char', 'NULL', 1,
      ],
      'primary_key' => 'eventpart',
      'unique' => [],
      'index' => [ ['payby'], ['disabled'], ],
    },

    'cust_bill_pkg' => {
      'columns' => [
        'pkgnum',  'int', '', '',
        'invnum',  'int', '', '',
        'setup',   @money_type,
        'recur',   @money_type,
        'sdate',   @date_type,
        'edate',   @date_type,
        'itemdesc', 'varchar', 'NULL', $char_d,
      ],
      'primary_key' => '',
      'unique' => [],
      'index' => [ ['invnum'] ],
    },

    'cust_bill_pkg_detail' => {
      'columns' => [
        'detailnum', 'serial', '', '',
        'pkgnum',  'int', '', '',
        'invnum',  'int', '', '',
        'detail',  'varchar', '', $char_d,
      ],
      'primary_key' => 'detailnum',
      'unique' => [],
      'index' => [ [ 'pkgnum', 'invnum' ] ],
    },

    'cust_credit' => {
      'columns' => [
        'crednum',  'serial', '', '',
        'custnum',  'int', '', '',
        '_date',    @date_type,
        'amount',   @money_type,
        'otaker',   'varchar', '', 32,
        'reason',   'text', 'NULL', '',
        'closed',    'char', 'NULL', 1,
      ],
      'primary_key' => 'crednum',
      'unique' => [],
      'index' => [ ['custnum'] ],
    },

    'cust_credit_bill' => {
      'columns' => [
        'creditbillnum', 'serial', '', '',
        'crednum',  'int', '', '',
        'invnum',  'int', '', '',
        '_date',    @date_type,
        'amount',   @money_type,
      ],
      'primary_key' => 'creditbillnum',
      'unique' => [],
      'index' => [ ['crednum'], ['invnum'] ],
    },

    'cust_main' => {
      'columns' => [
        'custnum',  'serial',  '',     '',
        'agentnum', 'int',  '',     '',
#        'titlenum', 'int',  'NULL',   '',
        'last',     'varchar', '',     $char_d,
#        'middle',   'varchar', 'NULL', $char_d,
        'first',    'varchar', '',     $char_d,
        'ss',       'varchar', 'NULL', 11,
        'company',  'varchar', 'NULL', $char_d,
        'address1', 'varchar', '',     $char_d,
        'address2', 'varchar', 'NULL', $char_d,
        'city',     'varchar', '',     $char_d,
        'county',   'varchar', 'NULL', $char_d,
        'state',    'varchar', 'NULL', $char_d,
        'zip',      'varchar', '',     10,
        'country',  'char', '',     2,
        'daytime',  'varchar', 'NULL', 20,
        'night',    'varchar', 'NULL', 20,
        'fax',      'varchar', 'NULL', 12,
        'ship_last',     'varchar', 'NULL', $char_d,
#        'ship_middle',   'varchar', 'NULL', $char_d,
        'ship_first',    'varchar', 'NULL', $char_d,
        'ship_company',  'varchar', 'NULL', $char_d,
        'ship_address1', 'varchar', 'NULL', $char_d,
        'ship_address2', 'varchar', 'NULL', $char_d,
        'ship_city',     'varchar', 'NULL', $char_d,
        'ship_county',   'varchar', 'NULL', $char_d,
        'ship_state',    'varchar', 'NULL', $char_d,
        'ship_zip',      'varchar', 'NULL', 10,
        'ship_country',  'char', 'NULL', 2,
        'ship_daytime',  'varchar', 'NULL', 20,
        'ship_night',    'varchar', 'NULL', 20,
        'ship_fax',      'varchar', 'NULL', 12,
        'payby',    'char', '',     4,
        'payinfo',  'varchar', 'NULL', $char_d,
        'paycvv',   'varchar', 'NULL', 4,
        #'paydate',  @date_type,
        'paydate',  'varchar', 'NULL', 10,
        'payname',  'varchar', 'NULL', $char_d,
        'tax',      'char', 'NULL', 1,
        'otaker',   'varchar', '',    32,
        'refnum',   'int',  '',     '',
        'referral_custnum', 'int',  'NULL', '',
        'comments', 'text', 'NULL', '',
      ],
      'primary_key' => 'custnum',
      'unique' => [],
      #'index' => [ ['last'], ['company'] ],
      'index' => [ ['last'], [ 'company' ], [ 'referral_custnum' ],
                   [ 'daytime' ], [ 'night' ], [ 'fax' ],
                 ],
    },

    'cust_main_invoice' => {
      'columns' => [
        'destnum',  'serial',  '',     '',
        'custnum',  'int',  '',     '',
        'dest',     'varchar', '',  $char_d,
      ],
      'primary_key' => 'destnum',
      'unique' => [],
      'index' => [ ['custnum'], ],
    },

    'cust_main_county' => { #county+state+country are checked off the
                            #cust_main_county for validation and to provide
                            # a tax rate.
      'columns' => [
        'taxnum',   'serial',   '',    '',
        'state',    'varchar',  'NULL',    $char_d,
        'county',   'varchar',  'NULL',    $char_d,
        'country',  'char',  '', 2, 
        'taxclass',   'varchar', 'NULL', $char_d,
        'exempt_amount', @money_type,
        'tax',      'real',  '',    '', #tax %
        'taxname',  'varchar',  'NULL',    $char_d,
        'setuptax',  'char', 'NULL', 1, # Y = setup tax exempt
        'recurtax',  'char', 'NULL', 1, # Y = recur tax exempt
      ],
      'primary_key' => 'taxnum',
      'unique' => [],
  #    'unique' => [ ['taxnum'], ['state', 'county'] ],
      'index' => [],
    },

    'cust_pay' => {
      'columns' => [
        'paynum',   'serial',    '',   '',
        #now cust_bill_pay #'invnum',   'int',    '',   '',
        'custnum',  'int',    '',   '',
        'paid',     @money_type,
        '_date',    @date_type,
        'payby',    'char',   '',     4, # CARD/BILL/COMP, should be index into
                                         # payment type table.
        'payinfo',  'varchar',   'NULL', $char_d,  #see cust_main above
        'paybatch', 'varchar',   'NULL', $char_d, #for auditing purposes.
        'closed',    'char', 'NULL', 1,
      ],
      'primary_key' => 'paynum',
      'unique' => [],
      'index' => [ [ 'custnum' ], [ 'paybatch' ], [ 'payby' ], [ '_date' ] ],
    },

    'cust_bill_pay' => {
      'columns' => [
        'billpaynum', 'serial',     '',   '',
        'invnum',  'int',     '',   '',
        'paynum',  'int',     '',   '',
        'amount',  @money_type,
        '_date',   @date_type
      ],
      'primary_key' => 'billpaynum',
      'unique' => [],
      'index' => [ [ 'paynum' ], [ 'invnum' ] ],
    },

    'cust_pay_batch' => { #what's this used for again?  list of customers
                          #in current CARD batch? (necessarily CARD?)
      'columns' => [
        'paybatchnum',   'serial',    '',   '',
        'invnum',   'int',    '',   '',
        'custnum',   'int',    '',   '',
        'last',     'varchar', '',     $char_d,
        'first',    'varchar', '',     $char_d,
        'address1', 'varchar', '',     $char_d,
        'address2', 'varchar', 'NULL', $char_d,
        'city',     'varchar', '',     $char_d,
        'state',    'varchar', 'NULL', $char_d,
        'zip',      'varchar', '',     10,
        'country',  'char', '',     2,
#        'trancode', 'int', '', '',
        'cardnum',  'varchar', '',     16,
        #'exp',      @date_type,
        'exp',      'varchar', '',     11,
        'payname',  'varchar', 'NULL', $char_d,
        'amount',   @money_type,
      ],
      'primary_key' => 'paybatchnum',
      'unique' => [],
      'index' => [ ['invnum'], ['custnum'] ],
    },

    'cust_pkg' => {
      'columns' => [
        'pkgnum',    'serial',    '',   '',
        'custnum',   'int',    '',   '',
        'pkgpart',   'int',    '',   '',
        'otaker',    'varchar', '', 32,
        'setup',     @date_type,
        'bill',      @date_type,
        'last_bill', @date_type,
        'susp',      @date_type,
        'cancel',    @date_type,
        'expire',    @date_type,
        'manual_flag', 'char', 'NULL', 1,
      ],
      'primary_key' => 'pkgnum',
      'unique' => [],
      'index' => [ ['custnum'] ],
    },

    'cust_refund' => {
      'columns' => [
        'refundnum',    'serial',    '',   '',
        #now cust_credit_refund #'crednum',      'int',    '',   '',
        'custnum',  'int',    '',   '',
        '_date',        @date_type,
        'refund',       @money_type,
        'otaker',       'varchar',   '',   32,
        'reason',       'varchar',   '',   $char_d,
        'payby',        'char',   '',     4, # CARD/BILL/COMP, should be index
                                             # into payment type table.
        'payinfo',      'varchar',   'NULL', $char_d,  #see cust_main above
        'paybatch',     'varchar',   'NULL', $char_d,
        'closed',    'char', 'NULL', 1,
      ],
      'primary_key' => 'refundnum',
      'unique' => [],
      'index' => [],
    },

    'cust_credit_refund' => {
      'columns' => [
        'creditrefundnum', 'serial',     '',   '',
        'crednum',  'int',     '',   '',
        'refundnum',  'int',     '',   '',
        'amount',  @money_type,
        '_date',   @date_type
      ],
      'primary_key' => 'creditrefundnum',
      'unique' => [],
      'index' => [ [ 'crednum', 'refundnum' ] ],
    },


    'cust_svc' => {
      'columns' => [
        'svcnum',    'serial',    '',   '',
        'pkgnum',    'int',    'NULL',   '',
        'svcpart',   'int',    '',   '',
      ],
      'primary_key' => 'svcnum',
      'unique' => [],
      'index' => [ ['svcnum'], ['pkgnum'], ['svcpart'] ],
    },

    'part_pkg' => {
      'columns' => [
        'pkgpart',    'serial',    '',   '',
        'pkg',        'varchar',   '',   $char_d,
        'comment',    'varchar',   '',   $char_d,
        'setup',      @perl_type,
        'freq',       'varchar',   '',   $char_d,  #billing frequency
        'recur',      @perl_type,
        'setuptax',  'char', 'NULL', 1,
        'recurtax',  'char', 'NULL', 1,
        'plan',       'varchar', 'NULL', $char_d,
        'plandata',   'text', 'NULL', '',
        'disabled',   'char', 'NULL', 1,
        'taxclass',   'varchar', 'NULL', $char_d,
      ],
      'primary_key' => 'pkgpart',
      'unique' => [],
      'index' => [ [ 'disabled' ], ],
    },

#    'part_title' => {
#      'columns' => [
#        'titlenum',   'int',    '',   '',
#        'title',      'varchar',   '',   $char_d,
#      ],
#      'primary_key' => 'titlenum',
#      'unique' => [ [] ],
#      'index' => [ [] ],
#    },

    'pkg_svc' => {
      'columns' => [
        'pkgpart',    'int',    '',   '',
        'svcpart',    'int',    '',   '',
        'quantity',   'int',    '',   '',
      ],
      'primary_key' => '',
      'unique' => [ ['pkgpart', 'svcpart'] ],
      'index' => [ ['pkgpart'] ],
    },

    'part_referral' => {
      'columns' => [
        'refnum',   'serial',    '',   '',
        'referral', 'varchar',   '',   $char_d,
        'disabled',     'char', 'NULL', 1,
      ],
      'primary_key' => 'refnum',
      'unique' => [],
      'index' => [ ['disabled'] ],
    },

    'part_svc' => {
      'columns' => [
        'svcpart',    'serial',    '',   '',
        'svc',        'varchar',   '',   $char_d,
        'svcdb',      'varchar',   '',   $char_d,
        'disabled',   'char',  'NULL',   1,
      ],
      'primary_key' => 'svcpart',
      'unique' => [],
      'index' => [ [ 'disabled' ] ],
    },

    'part_svc_column' => {
      'columns' => [
        'columnnum',   'serial',         '', '',
        'svcpart',     'int',         '', '',
        'columnname',  'varchar',     '', 64,
        'columnvalue', 'varchar', 'NULL', $char_d,
        'columnflag',  'char',    'NULL', 1, 
      ],
      'primary_key' => 'columnnum',
      'unique' => [ [ 'svcpart', 'columnname' ] ],
      'index' => [ [ 'svcpart' ] ],
    },

    #(this should be renamed to part_pop)
    'svc_acct_pop' => {
      'columns' => [
        'popnum',    'serial',    '',   '',
        'city',      'varchar',   '',   $char_d,
        'state',     'varchar',   '',   $char_d,
        'ac',        'char',   '',   3,
        'exch',      'char',   '',   3,
        'loc',       'char',   'NULL',   4, #NULL for legacy purposes
      ],
      'primary_key' => 'popnum',
      'unique' => [],
      'index' => [ [ 'state' ] ],
    },

    'part_pop_local' => {
      'columns' => [
        'localnum',  'serial',     '',     '',
        'popnum',    'int',     '',     '',
        'city',      'varchar', 'NULL', $char_d,
        'state',     'char',    'NULL', 2,
        'npa',       'char',    '',     3,
        'nxx',       'char',    '',     3,
      ],
      'primary_key' => 'localnum',
      'unique' => [],
      'index' => [ [ 'npa', 'nxx' ], [ 'popnum' ] ],
    },

    'svc_acct' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'username',  'varchar',   '',   $username_len, #unique (& remove dup code)
        '_password', 'varchar',   '',   72, #13 for encryped pw's plus ' *SUSPENDED* (md5 passwords can be 34, blowfish 60)
        'sec_phrase', 'varchar',  'NULL',   $char_d,
        'popnum',    'int',    'NULL',   '',
        'uid',       'int', 'NULL',   '',
        'gid',       'int', 'NULL',   '',
        'finger',    'varchar',   'NULL',   $char_d,
        'dir',       'varchar',   'NULL',   $char_d,
        'shell',     'varchar',   'NULL',   $char_d,
        'quota',     'varchar',   'NULL',   $char_d,
        'slipip',    'varchar',   'NULL',   15, #four TINYINTs, bah.
        'seconds',   'int', 'NULL',   '', #uhhhh
        'domsvc',    'int', '',   '',
      ],
      'primary_key' => 'svcnum',
      #'unique' => [ [ 'username', 'domsvc' ] ],
      'unique' => [],
      'index' => [ ['username'], ['domsvc'] ],
    },

    #'svc_charge' => {
    #  'columns' => [
    #    'svcnum',    'int',    '',   '',
    #    'amount',    @money_type,
    #  ],
    #  'primary_key' => 'svcnum',
    #  'unique' => [ [] ],
    #  'index' => [ [] ],
    #},

    'svc_domain' => {
      'columns' => [
        'svcnum',    'int',    '',   '',
        'domain',    'varchar',    '',   $char_d,
        'catchall',  'int', 'NULL',    '',
      ],
      'primary_key' => 'svcnum',
      'unique' => [ ['domain'] ],
      'index' => [],
    },

    'domain_record' => {
      'columns' => [
        'recnum',    'serial',     '',  '',
        'svcnum',    'int',     '',  '',
        #'reczone',   'varchar', '',  $char_d,
        'reczone',   'varchar', '',  255,
        'recaf',     'char',    '',  2,
        'rectype',   'varchar',    '',  5,
        #'recdata',   'varchar', '',  $char_d,
        'recdata',   'varchar', '',  255,
      ],
      'primary_key' => 'recnum',
      'unique'      => [],
      'index'       => [ ['svcnum'] ],
    },

    'svc_forward' => {
      'columns' => [
        'svcnum',   'int',    '',  '',
        'srcsvc',   'int',    '',  '',
        'dstsvc',   'int',    '',  '',
        'dst',      'varchar',    'NULL',  $char_d,
      ],
      'primary_key' => 'svcnum',
      'unique'      => [],
      'index'       => [ ['srcsvc'], ['dstsvc'] ],
    },

    'svc_www' => {
      'columns' => [
        'svcnum',   'int',    '',  '',
        'recnum',   'int',    '',  '',
        'usersvc',  'int',    '',  '',
      ],
      'primary_key' => 'svcnum',
      'unique'      => [],
      'index'       => [],
    },

    #'svc_wo' => {
    #  'columns' => [
    #    'svcnum',    'int',    '',   '',
    #    'svcnum',    'int',    '',   '',
    #    'svcnum',    'int',    '',   '',
    #    'worker',    'varchar',   '',   $char_d,
    #    '_date',     @date_type,
    #  ],
    #  'primary_key' => 'svcnum',
    #  'unique' => [ [] ],
    #  'index' => [ [] ],
    #},

    'prepay_credit' => {
      'columns' => [
        'prepaynum',   'serial',     '',   '',
        'identifier',  'varchar', '', $char_d,
        'amount',      @money_type,
        'seconds',     'int',     'NULL', '',
      ],
      'primary_key' => 'prepaynum',
      'unique'      => [ ['identifier'] ],
      'index'       => [],
    },

    'port' => {
      'columns' => [
        'portnum',  'serial',     '',   '',
        'ip',       'varchar', 'NULL', 15,
        'nasport',  'int',     'NULL', '',
        'nasnum',   'int',     '',   '',
      ],
      'primary_key' => 'portnum',
      'unique'      => [],
      'index'       => [],
    },

    'nas' => {
      'columns' => [
        'nasnum',   'serial',     '',    '',
        'nas',      'varchar', '',    $char_d,
        'nasip',    'varchar', '',    15,
        'nasfqdn',  'varchar', '',    $char_d,
        'last',     'int',     '',    '',
      ],
      'primary_key' => 'nasnum',
      'unique'      => [ [ 'nas' ], [ 'nasip' ] ],
      'index'       => [ [ 'last' ] ],
    },

    'session' => {
      'columns' => [
        'sessionnum', 'serial',       '',   '',
        'portnum',    'int',       '',   '',
        'svcnum',     'int',       '',   '',
        'login',      @date_type,
        'logout',     @date_type,
      ],
      'primary_key' => 'sessionnum',
      'unique'      => [],
      'index'       => [ [ 'portnum' ] ],
    },

    'queue' => {
      'columns' => [
        'jobnum', 'serial', '', '',
        'job', 'text', '', '',
        '_date', 'int', '', '',
        'status', 'varchar', '', $char_d,
        'statustext', 'text', 'NULL', '',
        'svcnum', 'int', 'NULL', '',
      ],
      'primary_key' => 'jobnum',
      'unique'      => [],
      'index'       => [ [ 'svcnum' ], [ 'status' ] ],
    },

    'queue_arg' => {
      'columns' => [
        'argnum', 'serial', '', '',
        'jobnum', 'int', '', '',
        'arg', 'text', 'NULL', '',
      ],
      'primary_key' => 'argnum',
      'unique'      => [],
      'index'       => [ [ 'jobnum' ] ],
    },

    'queue_depend' => {
      'columns' => [
        'dependnum', 'serial', '', '',
        'jobnum', 'int', '', '',
        'depend_jobnum', 'int', '', '',
      ],
      'primary_key' => 'dependnum',
      'unique'      => [],
      'index'       => [ [ 'jobnum' ], [ 'depend_jobnum' ] ],
    },

    'export_svc' => {
      'columns' => [
        'exportsvcnum' => 'serial', '', '',
        'exportnum'    => 'int', '', '',
        'svcpart'      => 'int', '', '',
      ],
      'primary_key' => 'exportsvcnum',
      'unique'      => [ [ 'exportnum', 'svcpart' ] ],
      'index'       => [ [ 'exportnum' ], [ 'svcpart' ] ],
    },

    'part_export' => {
      'columns' => [
        'exportnum', 'serial', '', '',
        #'svcpart',   'int', '', '',
        'machine', 'varchar', '', $char_d,
        'exporttype', 'varchar', '', $char_d,
        'nodomain',     'char', 'NULL', 1,
      ],
      'primary_key' => 'exportnum',
      'unique'      => [],
      'index'       => [ [ 'machine' ], [ 'exporttype' ] ],
    },

    'part_export_option' => {
      'columns' => [
        'optionnum', 'serial', '', '',
        'exportnum', 'int', '', '',
        'optionname', 'varchar', '', $char_d,
        'optionvalue', 'text', 'NULL', '',
      ],
      'primary_key' => 'optionnum',
      'unique'      => [],
      'index'       => [ [ 'exportnum' ], [ 'optionname' ] ],
    },

    'radius_usergroup' => {
      'columns' => [
        'usergroupnum', 'serial', '', '',
        'svcnum',       'int', '', '',
        'groupname',    'varchar', '', $char_d,
      ],
      'primary_key' => 'usergroupnum',
      'unique'      => [],
      'index'       => [ [ 'svcnum' ], [ 'groupname' ] ],
    },

    'msgcat' => {
      'columns' => [
        'msgnum', 'serial', '', '',
        'msgcode', 'varchar', '', $char_d,
        'locale', 'varchar', '', 16,
        'msg', 'text', '', '',
      ],
      'primary_key' => 'msgnum',
      'unique'      => [ [ 'msgcode', 'locale' ] ],
      'index'       => [],
    },

    'cust_tax_exempt' => {
      'columns' => [
        'exemptnum', 'serial', '', '',
        'custnum',   'int', '', '',
        'taxnum',    'int', '', '',
        'year',      'int', '', '',
        'month',     'int', '', '',
        'amount',   @money_type,
      ],
      'primary_key' => 'exemptnum',
      'unique'      => [ [ 'custnum', 'taxnum', 'year', 'month' ] ],
      'index'       => [],
    },

    'router' => {
      'columns' => [
        'routernum', 'serial', '', '',
        'routername', 'varchar', '', $char_d,
        'svcnum', 'int', 'NULL', '',
      ],
      'primary_key' => 'routernum',
      'unique'      => [],
      'index'       => [],
    },

    'part_svc_router' => {
      'columns' => [
        'svcpart', 'int', '', '',
	'routernum', 'int', '', '',
      ],
      'primary_key' => '',
      'unique'      => [],
      'index'       => [],
    },

    'addr_block' => {
      'columns' => [
        'blocknum', 'serial', '', '',
	'routernum', 'int', '', '',
        'ip_gateway', 'varchar', '', 15,
        'ip_netmask', 'int', '', '',
      ],
      'primary_key' => 'blocknum',
      'unique'      => [ [ 'blocknum', 'routernum' ] ],
      'index'       => [],
    },

    'svc_broadband' => {
      'columns' => [
        'svcnum', 'int', '', '',
        'blocknum', 'int', '', '',
        'speed_up', 'int', '', '',
        'speed_down', 'int', '', '',
        'ip_addr', 'varchar', '', 15,
      ],
      'primary_key' => 'svcnum',
      'unique'      => [],
      'index'       => [],
    },

    'part_virtual_field' => {
      'columns' => [
        'vfieldpart', 'int', '', '',
        'dbtable', 'varchar', '', 32,
        'name', 'varchar', '', 32,
        'check_block', 'text', 'NULL', '',
        'length', 'int', 'NULL', '',
        'list_source', 'text', 'NULL', '',
        'label', 'varchar', 'NULL', 80,
      ],
      'primary_key' => 'vfieldpart',
      'unique' => [],
      'index' => [],
    },

    'virtual_field' => {
      'columns' => [
        'recnum', 'int', '', '',
        'vfieldpart', 'int', '', '',
        'value', 'varchar', '', 128,
      ],
      'primary_key' => '',
      'unique' => [ [ 'vfieldpart', 'recnum' ] ],
      'index' => [],
    },

    'acct_snarf' => {
      'columns' => [
        'snarfnum',  'int', '', '',
        'svcnum',    'int', '', '',
        'machine',   'varchar', '', 255,
        'protocol',  'varchar', '', $char_d,
        'username',  'varchar', '', $char_d,
        '_password', 'varchar', '', $char_d,
      ],
      'primary_key' => 'snarfnum',
      'unique' => [],
      'index'  => [ [ 'svcnum' ] ],
    },

    'svc_external' => {
      'columns' => [
        'svcnum', 'int', '', '',
        'id',     'int', '', '',
        'title',  'varchar', 'NULL', $char_d,
      ],
      'primary_key' => 'svcnum',
      'unique'      => [],
      'index'       => [],
    },

  );

  %tables;

}