#!/usr/bin/perl -w
use strict;
use warnings;
use File::Basename;
use Data::Dumper;
use Getopt::Long;

# SQL auto-join

# load objects (table, field, link)
# index objects
# loop:
#   read list of tables to be joined
#   output SQL with JOINs

our $prog = basename($0);
our $prog_dir = dirname($0);
our $conf_dir = "$ENV{HOME}/.$prog";

our $usage = <<End;
usage: $prog [options] meta table ...
       $prog -dump db meta

conf dir is: $conf_dir
"meta" can be the name of a file in the conf dir, or a path (with "/")

options:

-case       case sensitive
End

main();

our ($dump, $help, $case_sensitive);

sub main {
    mkdir $conf_dir;
    GetOptions(
        "dump" => \$dump,
        "case" => \$case_sensitive,
        "help"  => \$help
        ) or die $usage;
    if ($help) {
        print $usage; exit;
    }
    if ($dump) {
        @ARGV == 2 or die $usage;
        aj_firebird_dump(@ARGV);
    } else {
        @ARGV >= 2 or die $usage;
        auto_join(@ARGV);
    }
}

sub aj_firebird_dump {
    my ($db, $meta_file) = @_;
    $meta_file = meta_file_path($meta_file);
    my $firebird_meta_sql = "$prog_dir/$prog-firebird-meta.sql";
    -e $firebird_meta_sql or die "file not found: $firebird_meta_sql";
    system("isql $db -i \Q$firebird_meta_sql\E >\Q$meta_file\E") == 0
        or die "failed: aj_firebird_dump";
    mkdir "$meta_file.d";
}

sub meta_file_path {
    my ($meta_file) = @_;
    if ($meta_file !~ m{/}) {
        $meta_file = "$conf_dir/$meta_file";
    }
    return $meta_file;
}

sub auto_join {
#    my ($meta_file, $query_file) = @_;
    my ($meta_file, @tables) = @_;

    @tables = map {uc} @tables if !$case_sensitive;

    $meta_file = meta_file_path($meta_file);

    # load objects (table, field, link) from one or more meta files
    my @files = grep {-f $_} glob("$meta_file"), glob("$meta_file.d/*");
    @files or die "files not found: $meta_file, $meta_file.d/*";
    my $objects = [];
    for my $file (@files) {
        my $meta_fh = open_file_or_stdin($file);
        load_objects($meta_fh, $objects);
        close $meta_fh;
    }

    # index objects
    my $by_type = index_objects($objects, ['TYPE'], 'multi');
    my $fields_by_table = index_objects($by_type->{FIELD}, ['TABLE_NAME'], 'multi');
    my $link_by_table = index_objects($by_type->{LINK}, ['TABLE_NAME','REFERENCES_TABLE','CONSTRAINT_NAME'], 'multi');
    my $abbrev = index_objects($by_type->{ABBREV}, ['NAME']);
    for (values %$abbrev) {
        $_ = $_->{ABBREV};
        $_ = lc $_ if !$case_sensitive;
    }

#    warn Dumper $link_by_table;
#    warn Dumper $fields_by_table;

#    # loop:
#    #   read query (list of tables to be joined)
#    #   output SQL with JOINs
#    my $query_fh = open_file_or_stdin($query_file);
#    my $line;
#    while (defined ($line = <$query_fh>)) {
#        chomp $line;
#        $line =~ s/\s+\z//s;
#        my @tables = split /\s+/, $line;
        my $sql = "SELECT\n";
        for my $table (@tables) {
            my $t = $abbrev->{$table} || $table;
            my $fields = $fields_by_table->{$table}
                or die "unknown table: $table";
            for my $field (@$fields) {
                $sql .= "    $t.$field->{FIELD_NAME},\n";
            }
        }
        $sql =~ s/,\n\z/\n/;
        my $main_table = shift @tables;
        my $ab = $abbrev->{$main_table} || '';
        $sql .= "FROM $main_table $ab\n";
        my @tables_done = ($main_table);
        for my $join_to (@tables) {
            my @links;
            for my $join_from (@tables_done) {
                push @links, values %{ $link_by_table->{$join_from}{$join_to} };
                push @links, values %{ $link_by_table->{$join_to}{$join_from} };
            }
            if (@links == 0) {
                die "no foreign key from any of (@tables_done) to $join_to";
            } elsif (@links > 1) {
                die "more than one (".@links.") foreign key from any of (@tables_done) to $join_to";
            }
            $ab = $abbrev->{$join_to} || '';
            $sql .= "JOIN $join_to $ab ON ";
            my @where;
            for my $link_field (@{$links[0]}) {
                my $t1 = $link_field->{TABLE_NAME};
                my $t2 = $link_field->{REFERENCES_TABLE};
                for ($t1, $t2) { $_ = $abbrev->{$_} || $_ }
                push @where, "$t1.$link_field->{FIELD_NAME} = $t2.$link_field->{REFERENCES_FIELD}";
            }
            $sql .= join(" AND ", @where) . "\n";
            push @tables_done, $join_to;
        }
        $sql .= ';';
        print "$sql\n";
#    }
#    close $query_fh;
}

sub open_file_or_stdin {
    my ($filename) = @_;
    my $fh;
    if (!defined $filename || $filename eq '-') {
        $fh = \*STDIN;
    } else {
        open $fh, '<', $filename
            or die "open failed: $filename: $!";
    }
    return $fh;
}

sub load_objects {
    my ($fh, $objects) = @_;
    $objects ||= [];
    my $obj;
    my $line;
    while (defined ($line = <$fh>)) {
        chomp $line;
        $line =~ s/\s+\z//s;
        $line = uc $line if !$case_sensitive;
        if ($line eq "" && $obj) {
            push @$objects, $obj;
            undef $obj;
        }
        if ($line ne "") {
            my ($k, $v) = split /\s+/, $line, 2;
            $obj->{$k} = $v;
        }
    }
    if ($obj) {
        push @$objects, $obj;
        undef $obj;
    }
    return $objects;
}

sub index_objects {
    my ($objects, $keys, $multi) = @_;
    my $index = {};
    for my $obj (@$objects) {
        my $ref = \$index;
        for my $k (@$keys) {
            my $v = $obj->{$k};
            $$ref ||= {};
            ($$ref)->{$v} ||= undef;
            $ref = \(($$ref)->{$v});
        }
        if ($multi) {
            $$ref ||= [];
            push @$$ref, $obj;
        } else {
            if (defined $$ref) {
                die "index_objects: duplicate for @$keys = @$obj{@$keys}";
            }
            $$ref = $obj;
        }
    }
    return $index;
}