#!/usr/bin/perl
## setup -- interactively create a message database and configuration file
## Copyright (c) 2000 Christopher League; see LICENSE for details

## I require these packages:
##  - DBI-1.11 (Generic database interface)
use DBI;
use Getopt::Long;
use strict;

## Descriptive text
my $version = "0.1";

my $version_text =
"setup (Meba) $version
Copyright (c) 2000 Christopher League
Meba comes with ABSOLUTELY NO WARRANTY.
You may redistribute and/or modify Meba under the terms of the 
GNU General Public License;  see the file LICENSE for details.
";

my $usage_text = 
"Usage: setup [OPTIONS]
Interactively create a message database and configuration file.

  -h, --host=HOST      host running the database server 
      --port=NUM       port number to use for connection, if non-standard
  -u, --user=NAME      login to server as this user 
  -p, --password=TEXT  password to use when connecting to server
  -d, --database=NAME  name of database to use 
  -t, --prefix=TEXT    prefix table names with `TEXT' 

  -o, --output=FILE    write configuration to FILE (default meba.conf)
  -n, --dry-run        output, but do not execute, the CREATE statements
  -v, --verbose        describe what is happening in gross detail
      --help           display this help and exit
      --version        output version information and exit

Report bugs to meba-bugs\@contrapunctus.net.
";

## Default values for most of the options.  None of these will be
## used without interactive user confirmation.
my %default;
$default{host} = "localhost";
$default{user} = getpwuid $<;   # Q: do I want real or effective UID here?
$default{database} = "meba_$default{user}";
$default{output} = "meba.conf";

## Read command line options into this hash.
my %cmd_line_options;
my @cmd_line_specs =
    (\%cmd_line_options,
     "host|h=s", "port:s", "user|u=s", "password|p=s", 
     "database|d=s", "prefix|t:s", "output|o=s", "dry-run|n",
     "verbose|v", "help", "version");
my $getopt_result = GetOptions (@cmd_line_specs);

## If the user wanted the usage or version blurbs, then we can 
## output them and stop now.
print STDERR $usage_text and exit 1
    if $cmd_line_options{help};

print STDERR $version_text and exit 2
    if $cmd_line_options{version};

## If there was an error in the command line options, we can
## also stop now.
unless ($getopt_result) {
    print STDERR "Try `$0 --help' for more information.\n";
    exit 3;
}

## Query user for any options that are not given on command line.
## Put final results in this hash.
my %options;

sub ask_user {
    my ($key, $blurb) = @_;
    if (defined $cmd_line_options{$key}) {
        $options{$key} = $cmd_line_options{$key};
    } 
    else {
        print STDOUT "Please enter $blurb\n";
        print STDOUT "$key = [$default{$key}] ";
        my $w = <STDIN>; chop $w;
        if ($w) {
            $options{$key} = $w;
        }
        else {
            $options{$key} = $default{$key};
        }
    }
}

&ask_user("host", "host name (or IP address) of the database server");
&ask_user("port", "port number to use for connection, if non-standard");
&ask_user("user", "database server username");
system("stty -echo");
&ask_user("password", "database server password for $options{user}");
print "\n";
system("stty echo");
&ask_user("database", "the name of the database to use");
&ask_user("prefix", "an optional prefix for table names");
&ask_user("output", "a file name in which I can write the configuration");

## Merge anything else from cmd_line_options into options...
my @cmd_line_keys = keys %cmd_line_options;
my $key;
while ($key = shift @cmd_line_keys) {
    $options{$key} = $cmd_line_options{$key} unless $options{$key};
}

##### END OF OPTION PROCESSING, 
## but before moving on, let's make sure we can connect to the database
## using the given arguments.
## Determine arguments to connect()
my $data_source = "DBI:mysql:$options{database}";
if ($options{host}) {
    $data_source .= ":$options{host}";
    $data_source .= ":$options{port}" if $options{port};
}
print "Connect ($data_source, $options{user}, $options{password})\n"
    if $options{verbose};

## Go ahead and connect, unless this is a dry run
my $db;
unless ($options{"dry-run"}) {
    $db = DBI->connect ($data_source, $options{user}, $options{password})
        || exit 6;
}

## Write the configuration file.  If this is a dry run, write
## it to STDOUT only.
unless ($options{"dry-run"}) {
    print "Opening $options{output} for output\n" if $options{verbose};
    open (CONF, ">$options{output}");
}
print "Configuration:\n"
    if $options{"dry-run"} or $options{verbose};

sub write_to_conf {
    my ($s) = @_;
    print CONF   $s unless $options{"dry-run"};
    print STDOUT $s if $options{"dry-run"} or $options{verbose};
}
    
## keys in %options that should be written to conf file.
## mostly these pertain to the connection itself.
my @conf_keys = qw(host port user password database prefix);
my $k;
&write_to_conf("# Connection parameters\n");
while ($k = shift @conf_keys) {
    &write_to_conf("$k = $options{$k}\n");
}

## configuration defaults for other scripts
&write_to_conf("
show.headers = From, Subject, To, Cc, Date 
show.pager = less
");

unless ($options{"dry-run"}) {
    close CONF; 
    print "Configuration written to $options{output}.\n";
}

##### DONE CREATING CONFIGURATION FILE
######################################################################

print "\nTables:\n"
    if $options{"dry-run"} or $options{verbose};

#### Now, we create the tables.
sub execute {
    my ($q) = @_;
    print $q if $options{"dry-run"} or $options{verbose};
    unless ($options{"dry-run"}) {
        my $s = $db->prepare($q);
        $s->execute or exit 8;
    }
}

## Here are a few data type constants.
my $HeaderType =     "varchar(200)";  # All other headers 
my $AddressType =    "varchar(200)";  # Email address (From/To/Cc)
my $NameType =       "varchar(200)";  # Full name (From/To/Cc)
my $MesgIdType =     "varchar(200)";  # Message-Id: type
my $DateType =       "datetime";      # Date and time (Date)
my $TagType =        "varchar(200)";  # Tags/keywords/folder names
my $HeaderTextType = "text";          # 64k for full headers
my $BodyTextType =   "mediumtext";    # 16M for message body
my $IdType =         "int unsigned";  # Type of primary key

# The "message" table stores the message body itself and all
# single-valued headers.
&execute(<<END );
CREATE TABLE $options{prefix}message (
   msgno   $IdType         NOT NULL AUTO_INCREMENT
  ,flags   SET('seen','deleted','flagged','answered','old','draft') NOT NULL
  ,entered $DateType       NOT NULL
  ,head    $HeaderTextType NOT NULL
  ,body    $BodyTextType   NOT NULL 
  ,subject $HeaderType    
  ,msgid   $MesgIdType    
  ,sent    $DateType      
  ,PRIMARY KEY (msgno)
  ,INDEX (subject)
  ,INDEX (entered)
  ,INDEX (sent)
  ,INDEX (msgid)
)
END

# The "recipient" table is for the multi-valued To/Cc/Bcc headers.
&execute(<<END );
CREATE TABLE $options{prefix}address (
   msgno   $IdType         NOT NULL REFERENCES message
  ,kind    ENUM("From", "Sender", "To", "Cc", "Bcc") NOT NULL
  ,addr    $AddressType    NOT NULL
  ,name    $NameType
  ,PRIMARY KEY (msgno, kind, addr)
  ,INDEX (addr)
  ,INDEX (kind)
)
END

# The "reference" table is for "message_id"s of other messages
# to which this mesage refers (from References: or In-Reply-To:
# headers).
&execute(<<END );
CREATE TABLE $options{prefix}reference (
   msgno   $IdType         NOT NULL REFERENCES message
  ,msgid   $MesgIdType     NOT NULL
  ,PRIMARY KEY (msgno, msgid)
)
END

# The "tag" table is for assigning keywords (representing folders,
# message status, etc.) to messages.
&execute(<<END );
CREATE TABLE $options{prefix}tag (
   msgno   $IdType         NOT NULL REFERENCES message
  ,tag     $TagType        NOT NULL
  ,PRIMARY KEY (msgno, tag)
)
END

## All done -- disconnect and exit
unless ($options{"dry-run"}) {
    $db->disconnect || die "Error disconnecting: " . $db->errstr;
}
exit 0;

