+use JSON::MaybeXS qw/decode_json/;
+use HTTP::Tiny;
+use PerlX::Maybe qw/maybe/;
+
+use DBI;
+use DBIx::Simple;
+use List::Util qw/sum/;
+use SQL::Abstract;
+
+use constant PROBLEM_PUBLIC_COLUMNS => [qw/id author writer level name owner private timeout olimit value/];
+use constant JOBS_PER_PAGE => 50;
+
+my %statements = (
+ user_list_sth => 'SELECT * FROM user_list LIMIT 200',
+ user_entry_sth => 'SELECT * FROM user_data WHERE id = ?',
+
+ problem_status_sth => 'SELECT problem,solved FROM problem_status WHERE owner = ?',
+ contest_status_sth => 'SELECT contest,score,rank FROM contest_status WHERE owner = ?',
+
+ contest_list_sth => 'SELECT * FROM contest_entry',
+ contest_entry_sth => 'SELECT * FROM contest_entry WHERE id = ?',
+ contest_has_problem_sth => 'SELECT EXISTS(SELECT 1 FROM contest_problems WHERE contest = ? AND problem = ?)',
+ opens_sth => 'SELECT problem,owner,time FROM opens WHERE contest = ?',
+
+ problem_entry_sth => 'SELECT ' . (join ',', @{PROBLEM_PUBLIC_COLUMNS()}, 'statement', 'solution') . ' FROM problems WHERE id = ?',
+ limits_sth => 'SELECT format,timeout FROM limits WHERE problem = ?',
+
+ job_entry_sth => 'SELECT * FROM job_entry WHERE id = ?',
+
+ rerun_problem_sth => 'UPDATE jobs SET daemon=NULL,result=-2,result_text=NULL,results=NULL,errors=NULL WHERE problem = ?',
+ rerun_job_sth => 'UPDATE jobs SET daemon=NULL,result=-2,result_text=NULL,results=NULL,errors=NULL WHERE id = ?',
+ take_job_sth => 'UPDATE jobs SET daemon=? WHERE id = (SELECT id FROM jobs WHERE daemon IS NULL LIMIT 1 FOR UPDATE) RETURNING id',
+);
+
+our $db;
+sub db () { $db }
+
+sub dbinit {
+ $db = DBIx::Simple->new(@_);
+ $db->keep_statements = 100;
+ $db->dbh->do('SET search_path TO gruntmaster, public');
+};
+
+sub purge;
+
+sub _query {
+ my ($stat, @extra) = @_;
+ $db->query($statements{$stat}, @extra)
+}
+
+my (%name_cache, %name_cache_time);
+use constant NAME_CACHE_MAX_AGE => 5;
+
+sub _object_name {
+ my ($table, $id) = @_;
+ $name_cache_time{$table} //= 0;
+ if (time - $name_cache_time{$table} > NAME_CACHE_MAX_AGE) {
+ $name_cache_time{$table} = time;
+ $name_cache{$table} = {};
+ $name_cache{$table} = $db->select($table, 'id,name')->map;
+ }
+
+ $name_cache{$table}{$id}
+}
+
+
+sub _add_names ($) { ## no critic (ProhibitSubroutinePrototypes)
+ my ($el) = @_;
+ return unless defined $el;
+ if (ref $el eq 'ARRAY') {
+ &_add_names ($_) for @$el ## no critic (ProhibitAmpersandSigils)
+ } else {
+ for my $object (qw/contest owner problem/) {
+ my $table = $object eq 'owner' ? 'users' : "${object}s";
+ $el->{"${object}_name"} = _object_name $table, $el->{$object} if defined $el->{$object}
+ }
+ }
+
+ $el
+}
+
+sub user_list { scalar _query('user_list_sth')->hashes }
+
+sub user_entry {
+ my ($id) = @_;
+ my $ret = _query('user_entry_sth', $id)->hash;
+ $ret->{problems} = _add_names _query('problem_status_sth', $id)->hashes;
+ $ret->{contests} = _add_names _query('contest_status_sth', $id)->hashes;
+
+ $ret;
+}
+
+sub problem_list {
+ my (%args) = @_;
+ my @columns = @{PROBLEM_PUBLIC_COLUMNS()};
+ push @columns, 'solution' if $args{solution};
+ my %where;
+ $where{private} = 0 unless $args{contest} || $args{private};
+ $where{'cp.contest'} = $args{contest} if $args{contest};
+ $where{owner} = $args{owner} if $args{owner};
+
+ my $table = $args{contest} ? 'problems JOIN contest_problems cp ON cp.problem = id' : 'problems';
+ _add_names $db->select(\$table, \@columns, \%where, 'name')->hashes
+}
+
+sub problem_entry {
+ my ($id, $contest) = @_;
+ $contest = contest_entry ($contest) if $contest;
+ my $ret = _add_names _query(problem_entry_sth => $id)->hash;
+ my $limits = _query(limits_sth => $id)->hashes;
+ $ret->{limits} = $limits if @$limits;
+
+ if ($contest) {
+ $ret->{contest_start} = $contest->{start};
+ $ret->{contest_stop} = $contest->{stop};
+ delete $ret->{solution}
+ }
+
+ $ret
+}