#!/usr/bin/perl

# This script is used to verify the consistency of the item tables (e.g. 'vn')
# with the latest revision in the corresponding history tables (e.g. 'vn_hist').
#
# The edit_* functions generated by sqleditfunc.pl should ensure this
# consistency, but bugs can happen and migration scripts sometimes bypass these
# functions.
#
# Outputs SQL statements that can be piped to 'psql'. The generated SELECT
# statements should not return any rows.

use v5.24;
use warnings;
use Cwd 'abs_path';

my $ROOT;
BEGIN { ($ROOT = abs_path $0) =~ s{/util/revision-integrity\.pl$}{}; }
use lib "$ROOT/lib";
use VNDB::Schema;

my $schema = VNDB::Schema::schema;

for my $table (sort { $a->{name} cmp $b->{name} } values %$schema) {
    next if $table->{name} !~ /^(.+)_hist$/ || $table->{name} eq 'users_username_hist';
    my($main, $type) = ($1, $1);
    $type =~ s/_[^_]+$// while !$schema->{$type}{dbentry_type};

    my ($mainlock, $histlock) = $main eq $type ? ('locked, hidden, ', 'c.ilock, c.ihid, ') : ('','');

    my $cols = join ', ', map "e.\"$_->{name}\"", grep $_->{name} ne 'chid', $table->{cols}->@*;
    print "SELECT '$main' as table, id, $mainlock $cols
           FROM $main e
         EXCEPT
         SELECT '$main', c.itemid, $histlock $cols
           FROM $table->{name} e
           JOIN changes c ON e.chid = c.id
          WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.itemid = c.itemid AND c2.rev > c.rev);\n\n"
}