#!/usr/bin/perl -w
#
# Zymonic Business Process and Information Management System
# Copyright Zednax Limited 2008 -
# For Authors and Changelog see the subversion history
use strict;

# Modules
use Zymonic::Config;
use Zymonic::DB;
use XML::Simple;

package MyXMLSimple;    # my XML::Simple subclass
use base 'XML::Simple';

# Overriding the method here
sub sorted_keys
{
    my ( $self, $name, $hashref ) = @_;
    if ( $name eq 'Table' )
    {
        return grep { defined $hashref->{$_} } (
            'ZName',          'DisplayName',
            'Field',          'AutoCreateMaintenanceProcess',
            'AutoCreateForm', 'AutoCreateFilter',
            'Key',            'RelationshipPermissions',
            'RevisionHistoryLimit'
        );
    }
    elsif ( $name eq 'FieldGroup' )
    {
        return grep { defined $hashref->{$_} } ( 'ZName', 'DisplayName', 'ShortDescription', 'LongDescription' );
    }
    elsif ( $name eq 'Field' )
    {
        return grep { defined $hashref->{$_} } (
            'ZName',         'SQLFieldType', 'DisplayName',      'SQLName',
            'MaximumLength', 'FieldGroup',   'ShortDescription', 'LongDescription'
        );
    }
    elsif ( $name eq 'Key' )
    {
        return grep { defined $hashref->{$_} } ( 'ZName', 'UniqueKey', 'Autoincrement', 'FieldName' );
    }
    return $self->SUPER::sorted_keys( $name, $hashref );
}

package main;

# Library path
BEGIN
{
    use vars qw($location $script_name $script_location $utils);
    use Zymonic::Utils qw(clean debug death_handler);
    $main::SIG{__DIE__} = \&death_handler;
}

# Clean path
$ENV{PATH} = "/bin:/sbin:/usr/bin:/usr/sbin:/usr/local/sbin:/bin:/usr/bin:/sbin:/usr/sbin";

my $system      = clean( $ARGV[0], "_" );
my $config_file = clean( $ARGV[1], "_" );
my %config      = ();
our %zname_list = ();

open( CONF, "<" . $config_file ) or die "Unable to open config file!";
while (<CONF>)
{
    chomp;
    next if /^#/;
    next unless /=/;
    my ( $option, $value ) = m/(.*?)\s*=\s*(.*)/;
    $config{$option} = clean( $value, "_\/{}=;" );
}
close(CONF);

debug("Zymonic starting... system: $system");
my $config = Zymonic::Config->new(
    system_name => $system,
    config_dir  => "/etc/zymonic",
    ip_address  => '127.0.0.1',
    protocol    => 'http'
);

# Load an auth object for securing records
my $auth = Zymonic::Auth->new(
    config => $config,
    DB     => $config->DB,
);

my $tof = Zymonic::Table->new(
    table  => 'table_of_fields',
    zname  => 'table_of_fields',
    config => $config,
    auth   => $auth,
    DB     => $config->DB,
);

my $output_defs       = { Field => [] };
my $output_table_defs = { Table => [], };
my %grouped_fields    = ();

# Load the fields from the database
my $base_db = Zymonic::DB->new( config => $config );

my $field_desc_rows = $base_db->run_query(
    { string => 'SELECT * FROM table_of_fields WHERE deleted <> "Y" OR deleted IS NULL', params => [], } );

foreach my $field_desc ( @{$field_desc_rows} )
{
    my $field_def = {
        ZName            => { content => $field_desc->{zobject_zname}       || '' },
        DisplayName      => { content => $field_desc->{zobject_displayname} || '' },
        ShortDescription => { content => $field_desc->{zobject_short_desc}  || '' },
        LongDescription  => { content => $field_desc->{zobject_long_desc}   || '' },
        SQLFieldType     => { content => $field_desc->{field_sql_type}      || '' },
        SQLName          => { content => $field_desc->{field_sql_name}      || '' },
        defined( $field_desc->{field_max_length} )
        ? ( MaximumLength => { content => $field_desc->{field_max_length} } )
        : (),
        defined( $field_desc->{field_group} ) ? (
            FieldGroup => { ZName => { content => $field_desc->{field_group} } }
          ) : (),
    };
    if ( $field_def->{FieldGroup} )
    {
        $grouped_fields{ $field_def->{FieldGroup}->{ZName}->{content} } = { Field => [], FieldGroup => [] }
          unless defined $grouped_fields{ $field_def->{FieldGroup}->{ZName}->{content} };

        push( @{ $grouped_fields{ $field_def->{FieldGroup}->{ZName}->{content} }->{Field} }, $field_def );
    }
    else
    {
        push( @{ $output_defs->{Field} }, $field_def );
    }
    zname_check( $field_desc->{zobject_zname}, 'Field' );
}

my $field_group_desc_rows = $base_db->run_query(
    { string => 'SELECT * FROM table_of_field_groups WHERE deleted <> "Y" OR deleted IS NULL', params => [], } );

foreach my $field_group_desc ( @{$field_group_desc_rows} )
{
    my $field_group_def = {
        ZName            => { content => $field_group_desc->{zobject_zname} },
        DisplayName      => { content => $field_group_desc->{zobject_displayname} },
        ShortDescription => { content => $field_group_desc->{zobject_short_desc} },
        LongDescription  => { content => $field_group_desc->{zobject_long_desc} },
    };

    $grouped_fields{ $field_group_def->{ZName}->{content} } = { Field => [], FieldGroup => [] }
      unless defined $grouped_fields{ $field_group_def->{ZName}->{content} };

    push( @{ $grouped_fields{ $field_group_def->{ZName}->{content} }->{FieldGroup} }, $field_group_def );
}

if ( $config{database} )
{

    $config->dbname( $config{database} );
    $config->dbdriver( $config{dbdriver} || 'mysql' );
    $config->{odbcconnectstring} = $config{'odbcconnectstring'} || '';
    $config->{dbusername}        = $config{'dbusername'};
    $config->{dbpassword}        = $config{'dbpassword'};

    my $db = Zymonic::DB->new( config => $config );

    my %table_defs = $db->get_table_definitions;

    foreach my $key ( keys(%table_defs) )
    {

        my $primary_flag = 0;

        # Strip illegal chars
        my $display_table_name = $key;
        $key =~ s/[^\w]/_/g;

        # Skip revision history tables
        next if $key =~ /_rh$/;

        # Check ZName duplicate
        if ( zname_check( $key, 'Table' ) )
        {
            warn "Duplicate ZName for table $key found - Type: " . zname_check( $key, 'Table' );
        }

        my $table = {
            ZName       => { content => $key },
            DisplayName => { content => $display_table_name },
            Field       => [],
            Key         => [],
        };

        # Add each field
        foreach my $field ( values( %{ $table_defs{$key}->{field} } ) )
        {

            # Skip auto added fields
            next if $field->{Field} =~ /parent_process_id|deleted|sec_id/;

            # Convert the Field name to a legal mysql field name/zname
            my $stripped_name = $field->{Field};
            $stripped_name =~ s/[^\w]/_/g;

            my $ofield   = { ZName => { content => 'f_' . $stripped_name }, };
            my $fieldref = { ZName => { content => 'f_' . $stripped_name }, };
            push( @{ $table->{Field} }, $fieldref );
            if ( !zname_check( 'f_' . $stripped_name, 'Field' ) )
            {
                $ofield->{DisplayName} = { content => $field->{Field} };
                $ofield->{SQLName}     = { content => $stripped_name };

                if ( $field->{Type} =~ /(.*)\((.*)\)/ )
                {
                    $ofield->{SQLFieldType}  = { content => $1 };
                    $ofield->{MaximumLength} = { content => $2 };
                }
                else
                {
                    $ofield->{SQLFieldType} = { content => $field->{Type} };
                }
                push( @{ $output_defs->{Field} }, $ofield );

                # Write to DB
                $base_db->run_statement(
                    {
                        string => 'INSERT INTO table_of_fields '
                          . '(zobject_zname, zobject_displayname, field_sql_name, field_sql_type, field_max_length, field_table)'
                          . ' VALUES (?, ?, ?, ?, ?, ?)',
                        params => [
                            $ofield->{ZName}->{content},
                            $field->{Field}, $stripped_name,
                            $ofield->{SQLFieldType}->{content},
                            ref( $ofield->{MaximumLength} ) ? $ofield->{MaximumLength}->{content} : '', $key,
                        ],
                    }
                );

                # Secure the record
                $auth->secure_record( $tof, { zobject_zname => $ofield->{ZName}->{content} } );

                # Create a process
                $base_db->run_statement(
                    {
                        string => 'INSERT INTO zz_process (current_state, process) ' . 'VALUES (?, ?)',
                        params => [ 'table_of_fields_autoprocess_live', 'table_of_fields_autoprocess' ],
                    }
                );
                my $lid = $base_db->run_query(
                    {
                        string => 'SELECT last_insert_id() AS lid',
                        params => [],
                    }
                );
                $base_db->run_statement(
                    {
                        string => 'UPDATE '
                          . 'table_of_fields'
                          . ' SET parent_process_id = ? WHERE '
                          . ' zobject_zname = ? ',
                        params => [ $lid->[0]->{lid}, $ofield->{ZName}->{content} ],
                    }
                );
            }

            if ( $field->{Key} )
            {
                my $key = { ZName => { content => 'pk_' . $key . '_' . $stripped_name }, };
                if ( !zname_check( 'pk_' . $key . '_' . $stripped_name, 'Key' ) )
                {
                    $key->{FieldName} = { content => $stripped_name };

                    if ( $field->{Extra} =~ /auto_increment/ )
                    {
                        $key->{Autoincrement} = { content => 'true' };
                    }

                    if ( $field->{Key} eq 'PRI' )
                    {
                        $key->{UniqueKey} = { content => 'true' };
                        $primary_flag = 1;
                    }
                }

                push( @{ $table->{Key} }, $key );
            }

        }

        # Add autocreate flags
        if ( ( defined $config{autocreatemaintenanceprocess} ) and ( $config{autocreatemaintenanceprocess} eq 'true' ) )
        {
            $table->{AutoCreateMaintenanceProcess}->{content} = 'true';
        }
        if ( ( defined $config{autocreateform} ) and ( $config{autocreateform} eq 'true' ) )
        {
            $table->{AutoCreateForm}->{content} = 'true';
        }
        if ( ( defined $config{autocreatefilter} ) and ( $config{autocreatefilter} eq 'true' ) )
        {
            $table->{AutoCreateFilter}->{content} = 'true';
        }

        if ( defined $config{permissions} )
        {
            $table->{RelationshipPermissions} = [];
            foreach my $rp ( split( /\s*,\s*/, $config{permissions} ) )
            {
                push( @{ $table->{RelationshipPermissions} }, { ZName => { content => $rp } } );
            }
        }

        # Add revision history flag (guess at number of revisions...)
        if ( defined $table_defs{ $key . '_rh' } )
        {
            $table->{RevisionHistoryLimit} = { content => 100 };
        }

        if (    ( !$primary_flag )
            and ( defined $config{addmissingprimary} )
            and ( $config{addmissingprimary} eq 'true' ) )
        {
            push(
                @{ $table->{Field} },
                {
                    ZName         => { content => 'zz_' . $key . '_pk' },
                    SQLFieldType  => { content => 'int' },
                    MaximumLength => { content => '18' },
                    DisplayName   => { content => 'Primary Key' },
                }
            );
            push(
                @{ $table->{Key} },
                {
                    ZName         => { content => 'pk_zz_' . $key . '_pk' },
                    FieldName     => { content => 'zz_' . $key . '_pk' },
                    Autoincrement => { content => 'true' },
                    UniqueKey     => { content => 'true' },
                }
            );
            $primary_flag = 1;
        }

        warn "Table $key does not have a primary key." unless ($primary_flag);

        push( @{ $output_table_defs->{Table} }, $table );

    }

}

my $parser = MyXMLSimple->new( KeyAttr => [], RootName => 'Zymonic' );

open( FD, ">" . $config{outputprefix} . "fields.xml" );
print FD $parser->XMLout($output_defs);
close(FD);

if ( $config{database} )
{
    open( TD, ">" . $config{outputprefix} . "tables.xml" );
    print TD $parser->XMLout($output_table_defs);
    close(TD);
}

foreach my $key ( keys(%grouped_fields) )
{
    open( FGD, ">" . $config{outputprefix} . "fieldgroup_" . $key . ".xml" );
    print FGD $parser->XMLout( $grouped_fields{$key} );
    close(FGD);
}

sub zname_check
{
    my $zname = shift;
    my $type  = shift;

    if ( $main::zname_list{$zname} )
    {
        return $main::zname_list{$zname};
    }
    else
    {
        $main::zname_list{$zname} = $type;
        return 0;
    }

}

