#!/usr/bin/perl

use strict;
use warnings;

our ($tblnum, $tblname, $colnum, $colname);

# Generate the where clauses to be used on all tables
our (%wherepattern1, %wherepattern2, %wherepattern3);
my @ops = (">", "<", ">=", "<=", "=", "<>");
my @conj = ("and", "or", "and not", "or not");
for (1..100)
{
	my $op1 = $ops[int(rand(@ops))];
	my $op2 = $ops[int(rand(@ops))];
	my $op3 = $ops[int(rand(@ops))];
	my $conj1 = $conj[int(rand(@conj))];
	my $conj2 = $conj[int(rand(@conj))];

	$wherepattern1{"\%s $op1 \%s"} = 1;
	$wherepattern2{"\%s $op1 \%s $conj1 \%s $op2 \%s"} = 1;
	$wherepattern3{"\%s $op1 \%s $conj1 \%s $op2 \%s $conj2 \%s $op3 \%s"} = 1;
}

sub next_table {
	$tblnum++;
	$tblname = "table_$tblnum";
	$colnum = 0;
	$colname = "column_$colnum";
}

sub next_column {
	$colnum++;
	$colname = "column_$colnum";
}

for my $colcnt (2..10)
{
	next_table();
	print("CREATE TABLE $tblname (\n");
	for (1..$colcnt-1)
	{
		next_column();
		print("\t$colname INTEGER,\n");
	}
	next_column();
	print("\t$colname INTEGER\n);\n");
	print("INSERT INTO $tblname (SELECT ",
		  join(", ", map { "gs-$_" } (1..$colcnt)),
		  " FROM generate_series(1,100) gs);\n");
	print("VACUUM FREEZE $tblname;\n");
	for my $colmax (2..$colcnt)
	{
		print("CREATE STATISTICS ${tblname}_stats_${colmax} ON ",
			  join(", ", map { "column_$_" } (1..$colmax)),
			  " FROM $tblname;\n");
	}
	print("ANALYZE $tblname;\n");
}

# Restart the table sequence
$tblnum = 0;

for my $colcnt (2..10)
{
	next_table();

	for (1..100)
	{
		my $a = sprintf("column_%d", 1+int(rand($colcnt)));
		my $b = sprintf("column_%d", 1+int(rand($colcnt)));
		my $c = sprintf("column_%d", 1+int(rand($colcnt)));

		foreach my $where1 (keys %wherepattern1)
		{
			my $whereclause1 = sprintf($where1, $a, $b);
			print("
select actual, estimated, abs(actual - estimated) AS misestimate
	from check_estimated_rows('select * from $tblname where $whereclause1');");
		}
		foreach my $where2 (keys %wherepattern2)
		{
			my $whereclause1 = sprintf($where2, $a, $b, $a, $c);
			print("
select actual, estimated, abs(actual - estimated) AS misestimate
	from check_estimated_rows('select * from $tblname where $whereclause1');");
		}
		foreach my $where3 (keys %wherepattern3)
		{
			my $whereclause1 = sprintf($where3, $a, $b, $a, $c, $c, $a);
			print("
select actual, estimated, abs(actual - estimated) AS misestimate
	from check_estimated_rows('select * from $tblname where $whereclause1');");
		}
	}
}