#!/usr/bin/perl

use strict;
use warnings;
use List::Util 'any';

use Cwd 'abs_path';
our $ROOT;
BEGIN { ($ROOT = abs_path $0) =~ s{/util/sqleditfunc\.pl$}{}; }

my %tabletypes; # table_name => dbentry_type
my %tables; # table_name => [ column_names ]
my %items; # item_name => { tables_without_hist => [ data_column_names ] }


# Fills %tables
sub readschema {
  open my $F, '<', "$ROOT/util/sql/schema.sql" or die $!;
  my $table = '';
  while(<$F>) {
    chomp;
    if(/^\s*CREATE\s+TABLE\s+([^ ]+)/) {
      $table = $1;
      $tables{$table} = [];
      $tabletypes{$table} = $1 if /--.*\s+dbentry_type=(.)/;
    } elsif($table && /^\s+("?[^\( ]+"?)\s/ && !/^\s+PRIMARY\s+KEY/) {
      push @{$tables{$table}}, $1;
    }
  }
}


sub gensql {
  my($template, $item) = @_;

  # table_name_without_hist => [ column_names_without_chid ]
  my %ts = map +($_, [ grep !/^chid$/, @{$tables{"${_}_hist"}} ]), map /^${item}_/ && /^(.+)_hist$/ ? $1 : (), keys %tables;

  my %replace = ( item => $item, itemtype => $tabletypes{$item} );
  $replace{createtemptables} = join "\n", map sprintf(
    "    CREATE TEMPORARY TABLE edit_%s (LIKE %s INCLUDING DEFAULTS INCLUDING CONSTRAINTS);\n".
    "    ALTER TABLE edit_%1\$s DROP COLUMN %s;",
    $_, $_ eq 'staff_alias' ? ($_, 'id') : ("${_}_hist", 'chid') # staff_alias copies from the non-_hist table, because it needs the sequence
  ), sort keys %ts;
  $replace{temptablenames} = join ', ', map "edit_$_", sort keys %ts;
  $replace{loadtemptables} = join "\n", map sprintf(
    "    INSERT INTO edit_%s (%s) SELECT %2\$s FROM %1\$s_hist WHERE chid = xchid;",
    $_, join ', ', @{$ts{$_}}), sort keys %ts;
  $replace{copyfromtemp} = join "\n", map sprintf(
    "  DELETE FROM %1\$s WHERE id = r.itemid;\n".
    "  INSERT INTO %1\$s (id, %2\$s) SELECT r.itemid, %2\$s FROM edit_%1\$s;\n".
    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;",
    $_, join ', ', @{$ts{$_}}), grep $_ ne $item, sort keys %ts;
  $replace{copymainfromtemp} = sprintf
    "  INSERT INTO %1\$s_hist (chid, %2\$s) SELECT r.chid, %2\$s FROM edit_%1\$s;\n".
    "  UPDATE %1\$s SET locked = (SELECT ilock FROM edit_revision), hidden = (SELECT ihid FROM edit_revision),\n".
    "    %3\$s FROM edit_%1\$s x WHERE id = r.itemid;",
    $item, join(', ', @{$ts{$item}}), join(', ', map "$_ = x.$_", @{$ts{$item}});

  $template =~ s/{([a-z]+)}/$replace{$1}/g;
  $template;
}


readschema;
my $template = join '', <DATA>;

open my $F, '>', "$ROOT/util/sql/editfunc.sql" or die $!;
print $F "-- Automatically generated by util/sqleditfunc.pl. DO NOT EDIT.\n";
print $F gensql $template, $_ for sort keys %tabletypes;


__DATA__

CREATE OR REPLACE FUNCTION edit_{itemtype}_init(xid integer, xrev integer) RETURNS void AS $$
DECLARE
  xchid integer;
BEGIN
  -- create tables, based on existing tables
  BEGIN
{createtemptables}
  EXCEPTION WHEN duplicate_table THEN
    TRUNCATE {temptablenames};
  END;
  -- Create edit_revision table and get relevant change ID.
  SELECT edit_revtable('{itemtype}', xid, xrev) INTO xchid;
  -- new entry, load defaults
  IF xchid IS NULL THEN
    INSERT INTO edit_{item} DEFAULT VALUES;
  -- otherwise, load revision
  ELSE
{loadtemptables}
  END IF;
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION edit_{itemtype}_commit() RETURNS edit_rettype AS $$
DECLARE
  r edit_rettype;
BEGIN
  IF (SELECT COUNT(*) FROM edit_{item}) <> 1 THEN
    RAISE 'edit_{item} must have exactly one row!';
  END IF;
  SELECT INTO r * FROM edit_commit();
{copyfromtemp}
{copymainfromtemp}
  PERFORM edit_committed('{itemtype}', r);
  RETURN r;
END;
$$ LANGUAGE plpgsql;