#!/usr/bin/perl

use constant 'CLASSDIR' => '/app/tools/common/lib/Common/DB/Schema/RSCOMMON';

use strict;
use warnings;

use FileHandle;
use Getopt::Long::Descriptive;
use Text::CSV::Easy(qw/csv_parse/);

use lib '/app/tools/common/lib';
use Common::NameCase;
use Common::DB::Schema;

my ( $opt, $usage ) = describe_options(
    '%c %o <some-arg>',
    [ 'table|t=s'   => 'Table to model' ],
    [ 'schema|s=s'  => 'Schema for table', { 'default' => 'RSCOMMON' } ],
    [ 'dbhost|d=s'  => 'Host for the schema', { 'default' => 'rpsdb01' } ],
    [ 'relfile|r=s' => 'Relationship file' ],
    [ 'file|f=s'    => 'Input file with list of tables' ],
    [ 'help|?|h'    => "print usage message and exit" ]
);

if ( $opt->help ) {
    print $usage->text . "\n";
    exit;
}

my $dbx = Common::DB::Schema->getdbx( $opt->dbhost );

## pick
my @tabs;
if ( defined $opt->table ) {
    push @tabs, get_table( $dbx, $opt->schema, $opt->table );
} elsif ( $opt->file ) {
    my $fh = FileHandle->new( $opt->file, 'r' );
    while (<$fh>) {
        chomp;
        next if m/^#/;
        push @tabs, get_table( $dbx, $opt->schema, $_ );
    }
    $fh->close;
} else {
    my $rset = $dbx->resultset('InformationSchema::Tables')->search( { 'table_schema' => $opt->schema } );
    while ( my $row = $rset->next ) {
        push @tabs, $row;
    }
}

foreach my $tab (@tabs) {

    ## schema & table camel
    my $c_table = Common::NameCase->camel( $tab->table_name );

    ## head of the file
    my $text = get_top( $tab, $c_table );

    ## column text
    my $primary;
    my @colnames;
    foreach my $col ( $tab->columns ) {
        if ( $col->column_key =~ m/pri/i ) {
            push @{$primary}, $col->column_name;
        }
        push @colnames, "    " . $col->column_name;
    }
    $text .= join "\n", @colnames;
    $text .= "\n/);\n\n";
    if ( defined $primary ) {
        if ( scalar @{$primary} > 1 ) {
            my (@keys) = map { "'$_'" } @{$primary};
            my $ktext = join ",", @keys;
            $text .= "## primary key\n__PACKAGE__->set_primary_key(($ktext));\n\n";
        } else {
            my $pkey = $primary->[0];
            $text .= "## primary key\n__PACKAGE__->set_primary_key('$pkey');\n\n";
        }
    }

    my $uniq = get_unique_cons( $tab->table_schema, $tab->table_name );
    if ( defined $uniq ) {
        $text .= "$uniq\n\n";
    }

    ## file to create or update
    my $filename = join '/', ( CLASSDIR, "$c_table.pm" );

    ## get relationships from existing file unless
    ## option relationship file is passed on CLI
    if ( defined $opt->relfile ) {
        my $tname = $tab->table_name;
        $text .= "\n## relationships\n";

        my $href = YAML::Syck::LoadFile( $opt->relfile );
        if ( exists $href->{$tname} ) {
            foreach my $rel ( @{ $href->{$tname} } ) {
                $text .= get_relationship($rel);
            }
        }

        #$rfh->close;
        $text .= "\n\n1;\n";
    } else {
        if ( -e $filename ) {
            my $fh = FileHandle->new( $filename, 'r' );
            my $is_rel;
            while (<$fh>) {
                if ($is_rel) {
                    $text .= "$_";
                } else {
                    if ( $_ =~ m/^## relationships/ ) {
                        $is_rel = 1;
                        $text .= "$_";
                    }
                }
            }
            $fh->close;
            $text .= "1;\n" unless $is_rel;
        } else {
            $text .= "1;\n";
        }
    }

    print "Creating object file $filename\n";
    my $ofh = FileHandle->new( $filename, 'w' );
    print $ofh "$text";
    $ofh->close;

}

sub get_table {
    my $dbx  = shift;
    my $scma = shift;
    my $tabl = shift;
    my $rset = $dbx->resultset('InformationSchema::Tables')->search( {
            'table_schema' => $scma,
            'table_name'   => $tabl
        }
    )->first;
    $rset;
}

sub get_top {
    my $tab  = shift;
    my $tabl = shift;
    my $pkg  = join '::', ( 'Common::DB::Schema::RSCOMMON', $tabl );
    my $conn = "'" . $tab->table_name . "'";
    my $text = <<"!";
package $pkg;

use strict;
use warnings;

use lib '/app/tools/common/lib';
use base 'Common::DB::DBIxCore';

## table
__PACKAGE__->table($conn);

## columns
__PACKAGE__->add_columns(qw/
!
    $text;
}

sub get_relationship {
    my $text = shift;

    my ( $rel, $meth, $forinfo, $sinfo ) = csv_parse($text);

    my @sinfo = split ':', $sinfo if defined $sinfo;

    my $reltext;
    if ( $rel =~ m/many_to_many/ ) {
        $reltext = "\n## FK $rel => $forinfo\n__PACKAGE__->$rel(\n\t'$meth','$forinfo','$sinfo'\n);\n";
    } elsif ( $rel =~ m/^file/ ) {
        open F, "complex_relationships/${meth}.pl";
        read F, $reltext, -s F;
        close F;
        $reltext = "\n$reltext";
    } else {
        my ( $scma, $tabl, $col ) = split( '\|', $forinfo );
        $scma = ( $scma eq 'cwr' ) ? 'CWR' : Common::NameCase->camel($scma);
        $tabl = Common::NameCase->camel($tabl);

        my @col = split ':', $col;

        $reltext = "\n## FK $tabl\n__PACKAGE__->$rel(\n\t'$meth',\n";
        $reltext .= "\t'Common::DB::Schema::${scma}::${tabl}',\n";

        $reltext .= "\t{\n";
        for ( my $i = 0 ; $i < scalar @sinfo ; $i++ ) {
            $reltext .= "\t\t'foreign.$col[$i]' => 'self.$sinfo[$i]',\n";
        }
        $reltext .= "\t},\n";
        $reltext .= "\t{'cascade_delete' => 0, 'cascade_update' => 0, 'cascade_copy' => 0}\n";
        $reltext .= ");\n";

    }
    $reltext;
}

sub get_unique_cons {
    ## need FKs set for this method
    ## until DB engine is Innodb return undef
    return undef;
    my $schema = shift;
    my $table  = shift;
    my $dbh    = $dbx->getdbh;
    my $row    = $dbh->selectcol_arrayref(
        qq{
SELECT
	CONCAT(
		'## unique key (', GROUP_CONCAT(DISTINCT k.column_name SEPARATOR ', '), ')', "\n",
		'__PACKAGE__->add_unique_constraint(',"'",k.constraint_name,"'", ' => [qw/',
		GROUP_CONCAT(DISTINCT k.column_name SEPARATOR ' '), "/]);"
	) var
FROM
	information_schema.table_constraints tc
JOIN
	information_schema.key_column_usage k ON k.constraint_name=tc.constraint_name
	AND k.table_schema=tc.table_schema
	AND k.table_name=tc.table_name
WHERE 1
	AND tc.constraint_type='UNIQUE'
	AND tc.table_schema=?
	AND tc.table_name=?
GROUP BY k.constraint_name
	}, undef, ( $schema, $table )
    );
    my $text;
    if ( defined $row ) {
        $text = $row->[0];
    }
    $text;
}

__END__
example
./dbix_object.pl -t f_royalty_journal -r warehouse.yml -d reporting -s warehouse