#!/usr/bin/perl -w
# ex: set tabstop=4 ai expandtab softtabstop=4 shiftwidth=4:
# -*- mode: c-basic-indent: 4; tab-width: 4; indent-tabs-mode: nil -*-

use strict;
use warnings;

our $VERSION = 3.1;

=head1 NAME

initialize_circuit_agent_db - Create the circuit gui database

=head1 SYNOPSIS

=head1 DESCRIPTION

=head1 OPTIONS

=head1 EXAMPLES

=cut

my $reset_sql = <<EOQ
DROP TABLE circuit_gui_circuits;
DROP TABLE circuit_gui_segments;
DROP TABLE circuit_gui_segment_hops;
DROP TABLE circuit_gui_nodes;
DROP TABLE circuit_gui_ports;
DROP TABLE circuit_gui_vlans;
DROP TABLE circuit_gui_segment_status;
DROP TABLE circuit_gui_segment_utilization;
DROP TABLE circuit_gui_segment_hop_status;
DROP TABLE circuit_gui_interface_utilization;
DROP TABLE circuit_gui_segment_hop_utilization;
EOQ
;

my $initialize_sql = <<EOQ
CREATE TABLE IF NOT EXISTS circuit_gui_circuits
(
    ID                   INTEGER PRIMARY KEY AUTOINCREMENT,

    CircuitName          VARCHAR(255),
    URN                  VARCHAR(255),
    StartTime            DATETIME,
    EndTime              DATETIME,
    Status               VARCHAR(32),
    Bandwidth            INTEGER,
    FinishedMeasurements BOOLEAN,
    TopologyLoaded       BOOLEAN,

    UNIQUE(CircuitName)
);

CREATE TABLE IF NOT EXISTS circuit_gui_segments
(
    ID                      INTEGER PRIMARY KEY AUTOINCREMENT,

    URN                     VARCHAR(255),
    Name                    VARCHAR(255),
    DomainName              VARCHAR(255),
    CircuitID               INTEGER NOT NULL REFERENCES circuit_gui_circuits (ID),
    HopNumber               INTEGER NOT NULL,
    SegmentNumber           INTEGER NOT NULL,
    FinishedMeasurements    BOOLEAN,
    TopologyLoaded          BOOLEAN,

    UNIQUE(HopNumber, SegmentNumber, CircuitID)
);

CREATE TABLE IF NOT EXISTS circuit_gui_segment_status
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    SegmentID          INTEGER NOT NULL REFERENCES circuit_gui_segments (ID),
    Time               DATETIME,
    OperStatus         VARCHAR(32),
    AdminStatus        VARCHAR(32),

    UNIQUE(SegmentID)
);

CREATE TABLE IF NOT EXISTS circuit_gui_segment_utilization
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    SegmentID          INTEGER NOT NULL REFERENCES circuit_gui_segments (ID),
    Time               DATETIME,
    Utilization        INTEGER,

    UNIQUE(SegmentID, Time)
);

CREATE TABLE IF NOT EXISTS circuit_gui_segment_hop_status
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    SegmentHopID       INTEGER NOT NULL REFERENCES circuit_gui_segment_hops (ID),
    Time               DATETIME,
    OperStatus         VARCHAR(32),
    AdminStatus        VARCHAR(32),

    UNIQUE(SegmentHopID, Time)
);

CREATE TABLE IF NOT EXISTS circuit_gui_interface_utilization
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    NodeID             INTEGER NOT NULL REFERENCES circuit_gui_nodes (ID),
    PortID             INTEGER REFERENCES circuit_gui_ports (ID),
    VLANID             INTEGER REFERENCES circuit_gui_vlans (ID),
    InDirection        BOOLEAN,
    Time               DATETIME,
    Utilization        INTEGER, 

    UNIQUE(NodeID, PortID, VLANID, InDirection, Time)
);

CREATE TABLE IF NOT EXISTS circuit_gui_segment_hops
(
    ID                      INTEGER PRIMARY KEY AUTOINCREMENT,

    SegmentID               INTEGER NOT NULL REFERENCES circuit_gui_segments (ID),
    HopNumber               INTEGER NOT NULL,
    VLANID                  INTEGER REFERENCES circuit_gui_vlans (ID),
    PortID                  INTEGER REFERENCES circuit_gui_ports (ID),
    NodeID                  INTEGER REFERENCES circuit_gui_nodes (ID),
    FinishedMeasurements    BOOLEAN,

    UNIQUE (SegmentID, HopNumber)
);


CREATE TABLE IF NOT EXISTS circuit_gui_nodes
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    Name               VARCHAR(255),
    City               VARCHAR(128),
    State              VARCHAR(128),
    Country            VARCHAR(128),
    Latitude           FLOAT,
    Longitude          FLOAT,

    UNIQUE(Name)
);

CREATE TABLE IF NOT EXISTS circuit_gui_ports
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    Name               VARCHAR(255),
    NodeID             INTEGER NOT NULL REFERENCES circuit_gui_nodes (ID),

    UNIQUE(Name, NodeID)
);

CREATE TABLE IF NOT EXISTS circuit_gui_vlans
(
    ID                 INTEGER PRIMARY KEY AUTOINCREMENT,

    Name               VARCHAR(255),
    VLAN               INTEGER,
    PortID             INTEGER NOT NULL REFERENCES circuit_gui_ports (ID),
    NodeID             INTEGER NOT NULL REFERENCES circuit_gui_nodes (ID),

    UNIQUE(Name, PortID)
);


EOQ
;


use FindBin;
use lib "$FindBin::Bin/../lib";

use Getopt::Long;
use DBI;

use perfSONAR_PS::Utils::Config;
use perfSONAR_PS::Utils::DB qw(init_db);

my $CONFIG_FILE;
my $RESET;
my $HELP;
my $ADMIN_NAME;
my $ADMIN_PASS;

my ( $status, $res );

$status = GetOptions(
    'reset'         => \$RESET,
    'config=s'      => \$CONFIG_FILE,
    'admin_user=s'  => \$ADMIN_NAME,
    'admin_pass=s'  => \$ADMIN_PASS,
    'help'          => \$HELP
);

my $config_file;
if ($CONFIG_FILE) {
    $config_file = $CONFIG_FILE;
}
else {
    $config_file = "$FindBin::Bin/../etc/circuit_gui.conf";
}

my $config = perfSONAR_PS::Utils::Config->new();

($status, $res) = $config->init({ file => $config_file });
if ($status != 0) {
    print "Couldn't read configuration file: $config_file\n";
    exit -1;
}

my $reservations_db_type = $config->lookup({ path => "CircuitsDBType" });
unless ($reservations_db_type) {
    print "Need to specify the Circuits Configuration Database: CircuitsDBType\n";
    exit -1;
}

unless ($reservations_db_type eq "sqlite" or
        $reservations_db_type eq "mysql") {
    print "'sqlite' and 'mysql' are the only supported database type\n";
    exit -1;
}

if ($reservations_db_type eq "sqlite") {
    my $reservations_db_file = $config->lookup({ path => "CircuitsDBFile" });
    unless ($reservations_db_file) {
        print "Need to specify the Circuits Configuration file: CircuitsDBType\n";
        exit -1;
    }

    ($status, $res) = init_db({ db_type => $reservations_db_type, db_name => $reservations_db_file, reset_database => $RESET, initialize_sql => $initialize_sql, reset_sql => $reset_sql });

    if ($status != 0) {
        print "Error: couldn't initialize database: $res\n";
        exit -1;
    }
}
elsif ($reservations_db_type eq "mysql") {
    my $reservations_db_host = $config->lookup({ path => "CircuitsDBHost" });
    my $reservations_db_port = $config->lookup({ path => "CircuitsDBPort" });
    my $reservations_db_name = $config->lookup({ path => "CircuitsDBName" });
    my $reservations_db_user = $config->lookup({ path => "CircuitsDBUser" });
    my $reservations_db_pass = $config->lookup({ path => "CircuitsDBPass" });

    unless ($reservations_db_name) {
        print "Need to specify the Circuits database name: CircuitsDBName\n";
        exit -1;
    }

    unless ($reservations_db_user) {
        print "Need to specify the Circuits database user: CircuitsDBUser\n";
        exit -1;
    }

    unless ($reservations_db_pass) {
        print "Need to specify the Circuits database user password: CircuitsDBPass\n";
        exit -1;
    }

    $reservations_db_host = "localhost" unless ($reservations_db_name);

    # Correct differences in SQL syntax
    $initialize_sql =~ s/AUTOINCREMENT/AUTO_INCREMENT/gm;
    $reset_sql =~ s/AUTOINCREMENT/AUTO_INCREMENT/gm;

    ($status, $res) = init_db({
                                    db_type => $reservations_db_type,
                                    db_name => $reservations_db_name,
                                    db_host => $reservations_db_host,
                                    db_port => $reservations_db_port,
                                    db_user => $reservations_db_user,
                                    db_pass => $reservations_db_pass,
                                    admin_user => $ADMIN_NAME,
                                    admin_pass => $ADMIN_PASS,
                                    reset_database => $RESET,
                                    initialize_sql => $initialize_sql,
                                    reset_sql => $reset_sql
                            });

    if ($status != 0) {
        print "Error: couldn't initialize database: $res\n";
        exit -1;
    }
}

exit 0;

__END__

=head1 SEE ALSO

L<FindBin>, L<Getopt::Long>, L<Carp>, L<DBI>

To join the 'perfSONAR Users' mailing list, please visit:

  https://mail.internet2.edu/wws/info/perfsonar-ps-users

The perfSONAR-PS subversion repository is located at:

  http://anonsvn.internet2.edu/svn/perfSONAR-PS/trunk

Questions and comments can be directed to the author, or the mailing list.
Bugs, feature requests, and improvements can be directed here:

  http://code.google.com/p/perfsonar-ps/issues/list

=head1 VERSION

$Id: bwdb.pl 3670 2009-09-02 13:55:50Z zurawski $

=head1 AUTHOR

Jeff W. Boote <boote@internet2.edu>

=head1 LICENSE

You should have received a copy of the Internet2 Intellectual Property Framework
along with this software.  If not, see
<http://www.internet2.edu/membership/ip.html>

=head1 COPYRIGHT

Copyright (c) 2004-2009, Internet2

All rights reserved.

=cut
