#!/usr/bin/perl -w

#######################################################################################
#
# Script that searches a dir containing PSPS schema files, finds those that comtain the 
# tables on interest then parses them into an XML format. Also generates C-header files 
# containing enums that detail table column names and numbers.
#
#######################################################################################

use warnings;
use strict;
use IPC::Cmd 0.36 qw( can_run run );
use XML::Writer;
use Getopt::Long qw( GetOptions :config auto_help auto_version gnu_getopt );
use Pod::Usage qw( pod2usage );

my $schemaPath = undef;
my $type = undef;

# get user args
GetOptions(
        'schema|s=s' => \$schemaPath,
        'type|t=s' => \$type,
        ) or pod2usage( 2 );

pod2usage( -msg => "Unknown option: @ARGV", -exitval => 2 ) if @ARGV;

# tell off user for not providing proper args
pod2usage(
        -msg => "\n   Required options:\n\n".
        "--schema <path to PSPS schema>\n".
        "--type <init|det|diff|stack>\n".
        -exitval => 3
        ) unless
defined $schemaPath and
defined $type;

if ($type ne "init" && $type ne "det" && $type ne "diff" && $type ne "stack") {

    print "Don't understand type '$type'\n"; 
    die;
}

my $enumsHeader = "ippToPsps".ucfirst($type)."Enums";
open(OUT, ">".$enumsHeader.".h") or die("Error");

print OUT "#ifndef ".uc($enumsHeader)."_H\n";
print OUT "#define ".uc($enumsHeader)."_H\n\n";


my $output = new IO::File(">tables.xml");
my $writer = new XML::Writer(OUTPUT => $output, DATA_MODE => 1, DATA_INDENT=>2);
$writer->xmlDecl('UTF-8');
#    $writer->doctype('manifest', "", "psps-manifest.dtd");
$writer->startTag('tableDescriptions', "type" => "$type");

if ($type eq "init") {createInit();}
elsif ($type eq "det") {createDetections();}
elsif ($type eq "diff") {createDiffs();}
elsif ($type eq "stack") {createStacks();}

# finish up XML
$writer->endTag();
$writer->end();

print OUT "\n#endif";
close OUT;


#######################################################################################
#
# Finds the schema file containing the table name passed in
#
#######################################################################################
sub findSchemaFile {
    my ($tableName) = @_;

    opendir(DIR, $schemaPath) or print "Cannot open '$schemaPath'\n" and return "";
    my @files= readdir(DIR);
    closedir(DIR);

    foreach my $f (@files) {

        if ($f =~ m/.*\.sql$/) {
            #print "....$f\n";

            open FILE, "<$schemaPath/$f" or next;

            while (<FILE>) {
                if ($_ =~ m/.*CREATE TABLE\s+dbo\.$tableName\s*\(/i) {

                    close (FILE);
                    return "$schemaPath/$f";
                }
            }

            close (FILE);

        }
    }

    print "Could not find table '$tableName'\n";
    return "";
}

#######################################################################################
#
# Creates detection batch tables
#
#######################################################################################
sub createInit {

    parseTable("Filter");
    parseTable("FitModel");
    parseTable("PhotozRecipe");
    parseTable("Survey");
    parseTable("CameraConfig");
    parseTable("PhotoCal");
    parseTable("SkyCell");
    parseTable("ProjectionCell");
    parseTable("Region");
    parseTable("StackType");
}

#######################################################################################
#
# Creates initialisation batch tables
#
#######################################################################################
sub createDetections {

    parseTable("FrameMeta");
    parseTable("ImageMeta");
    parseTable("Detection");
    parseTable("SkinnyObject");
    parseTable("ObjectCalColor");
}

#######################################################################################
#
# Creates difference batch tables
#
#######################################################################################
sub createDiffs {

    parseTable("StackMeta");
    parseTable("StackToImage");
    parseTable("StackLowSigDelta");
    parseTable("StackHighSigDelta");
    parseTable("ObjectCalColor");
}

#######################################################################################
#
# Creates stack batch tables
#
#######################################################################################
sub createStacks {

    parseTable("StackMeta");
    parseTable("StackDetection");
    parseTable("SkinnyObject");
    parseTable("StackOrphan");
    parseTable("ObjectCalColor");
}

#######################################################################################
#
# Parses a particular table from the SQL file and converts it to an XML description 
#
#######################################################################################
sub parseTable {
    my ($tableName, $newName) = @_;

    my $path =  findSchemaFile($tableName);

    if ($path eq "") {return;}

    # sort out table name, either same as in schema or as defined
    my $tableNameOut; 
    if ($newName) { $tableNameOut = $newName;}
    else {$tableNameOut = $tableName}

    print OUT "\ntypedef enum {\n";
    $writer->startTag('table', "name" => $tableNameOut);

    open (SCHEMA, $path);

    my $reading = 0;
    my $found = 0;
    my $colNum = 0;
    my $table;

    while (<SCHEMA>) {
        chomp;

        if ($_ =~ m/.*CREATE TABLE\s+dbo\.([a-zA-Z0-9.]+)\s*\(/i) {

            $table = $1;

            if ($table eq $tableName) {

                $reading = 1;
                $found = 1;
                $colNum = 0;
                next;
            }
        }

        if($reading && $_ =~ m/^\s*\)\s*/) {$reading = 0;}

        if(!$reading) {next;}

        my $line = $_;
        $line =~ s/[\s]*$//;

        if (!$line) {next;}
        if (length($line) < 5) {next;}
        if ($line =~ m/^\s*--/) {next;}
        if ($line =~ m/\/\*/) {next;}
        if ($line =~ m/\*\//) {next;}

        $colNum = processLine($line, $tableName, $colNum);

    }

    if (!$found) {print "Could not find table '$tableName'\n";}
    $writer->endTag();
    print OUT "} ".$tableNameOut.";\n";

    close SCHEMA;
}

#######################################################################################
#
# Processes a line from the PSPS schema table description and converts to XML
#
#######################################################################################
sub processLine {
    my ($line, $tableName, $colNum) = @_;

    my $name;
    my $typeStr;
    my $type;
    my $comment;
    my $default;

    # parse line
    if ($line =~ m/\s*([a-zA-Z0-9()-\[\]]+)\s+(.*)\s*--\/(.*)/) {

        $name = $1;
        $typeStr = $2;
        $comment = $3;

        # neaten up the comment
        $comment =~ s/<[\/]{0,1}column>//g;
        $comment =~ s/^[\s]*//;
        $comment =~ s/[\s]*$//;
        if ($comment =~ m/<column unit="(.*)">(.*)/ ) {$comment = "$2 (unit = $1)"}
    }
    # no comment case
    elsif ($line =~ m/\s*([a-zA-Z0-9()-\[\]]+)\s+(.*)/) {

        $name = $1;
        $typeStr = $2;
        $comment = "No comment";
    }
    else {

        print "In '$tableName', can't process: '$line'\n";
        return $colNum
    }

    my $ISBINARY = 0;

    # remove [] from name
    $name =~ s/[\[\]]//g;

    # sort out type
    if ($typeStr =~ m/([a-zA-Z0-9()-]*)[ \t]+.*/) {

        $type = $1; 
        $type =~ s/BIGINT/TLONGLONG/;
        $type =~ s/SMALLINT/TSHORT/;
        $type =~ s/TINYINT/TBYTE/;
        $type =~ s/INT/TLONG/;
        $type =~ s/FLOAT/TDOUBLE/;
        $type =~ s/REAL/TFLOAT/;
        $type =~ s/DATE/TSTRING/;
        if ($type =~ m/^VARCHAR.*/) {$type = "TSTRING";}
        if ($type =~ m/^VARBINARY.*/) {$type = "TSTRING"; $ISBINARY = 1;}
    }

    # get default value
    if ($typeStr =~ m/.*DEFAULT[ \t]*(.*)/i) {

        $default = $1;
        $default =~ s/,//;
            $default =~ s/[\(\)]//g;
        $default =~ s/[\s]*$//;
        $default =~ s/'//g;
        if ($type ne "TSTRING" && !$default) {$default = "0";}
        if ($ISBINARY && $default eq "0x") {$default = "";}
    }
    elsif ($type eq "TSTRING") {$default = " ";}
    else {$default = 0;}

    if ($type eq "TSTRING" && $default eq "") {$default = " ";}

    $colNum++;

    print OUT "  ".uc($tableName)."_".uc($name)." = ".$colNum.",\n";

    $writer->startTag('column',
            "name" => $name,
            "type" => $type,
            "default" => $default,
            "comment" => $comment);

    $writer->endTag();

    return $colNum;
}
