#!/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".
        "-s   <path to PSPS schema>\n".
        "-t   <init|detection|diff|stack|object>\n".
        -exitval => 3
        ) unless
defined $schemaPath and
defined $type;

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

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

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

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

# tables file
my $tablesOutput = new IO::File(">tables.xml");
my $tablesWriter = new XML::Writer(OUTPUT => $tablesOutput, DATA_MODE => 1, DATA_INDENT=>2);
$tablesWriter->xmlDecl('UTF-8');
$tablesWriter->startTag('tableDescriptions', "type" => "$type");

# map file
my $mapOutput = new IO::File(">map.xml");
my $mapWriter = new XML::Writer(OUTPUT => $mapOutput, DATA_MODE => 1, DATA_INDENT=>2);
$mapWriter->xmlDecl('UTF-8');
$mapWriter->startTag('tabledata', "type" => "$type");

# VOTable file
my $votOutput = new IO::File(">tables.vot");
my $votWriter = new XML::Writer(OUTPUT => $votOutput, DATA_MODE => 1, DATA_INDENT=>2);
$votWriter->xmlDecl('UTF-8');
$votWriter->startTag('VOTABLE', "version" => "1.1");
$votWriter->startTag('RESOURCE');

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

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

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

# finish up XML
$votWriter->endTag(); # end RESOURCE tag
$votWriter->endTag(); # end of TABLE tag
$votWriter->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");
    parseTable("ImageFlags");
    parseTable("DetectionFlags");
}

#######################################################################################
#
# 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("StackApFlx");
    parseTable("StackModelFit");
    parseTable("StackToImage");
}

#######################################################################################
#
# Creates object batch tables
#
#######################################################################################
sub createObjects {

    parseTable("Object");
}

#######################################################################################
#
# 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";
    $tablesWriter->startTag('table', "name" => $tableNameOut);
    $mapWriter->startTag('table', 
            "name" => $tableNameOut,
            "ippfitsextension" => "");
    $votWriter->startTag('TABLE', 
            "name" => "$tableNameOut");
    $votWriter->dataElement('DESCRIPTION', "VOTable description of PSPS table $tableNameOut");
    $votWriter->startTag('PARAM',
            "arraysize" => "1",
            "datatype" => "char",
            "ucd" => "meta.bib.author",
            "name" => "Author",
            "value" => "PSPS");
    $votWriter->endTag();

    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";}
    $tablesWriter->endTag();
    $mapWriter->endTag();

    $votWriter->startTag('DATA');
    $votWriter->startTag('TABLEDATA');
    $votWriter->endTag(); # end TABLEDDATA tag
        $votWriter->endTag(); # end DATA tag
        $votWriter->endTag(); # end TABLE tag
        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";

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

    # get VOTable type
    my $votType = "undef";
    my $arraySize = 1;
    if ($type eq "TBYTE") {$votType = "unsignedByte";}
    elsif ($type eq "TSHORT") {$votType = "short";}
    elsif ($type eq "TLONG") {$votType = "int";}
    elsif ($type eq "TLONGLONG") {$votType = "long";}
    elsif ($type eq "TFLOAT") {$votType = "float";}
    elsif ($type eq "TDOUBLE") {$votType = "double";}
    elsif ($type eq "TSTRING") {$votType = "char"; $arraySize = 100;}

    $votWriter->startTag('FIELD',
            "name" => $name,
            "arraysize" => $arraySize,
            "datatype" => $votType);
    $votWriter->endTag();

    $mapWriter->comment(" **MISSING** <map pspsName=\"$name\" ippType=\"$type\" ippName=\"\" comment=\"$comment\"/>");

#    $mapWriter->endTag();

    return $colNum;
}
