#!/bin/bash

set -e
DATAFILE=$(mktemp)

sqlite3 -separator ' ' reviews.db <<-EOF > $DATAFILE
	with recent as (
	  select "Reviewer Full Name",
	         "Review Creation Date",
	         t1.date,
	         "Review Title"
	    from reviews t1
	    join (select "ID", max(date) as date
	            from reviews
	        group by "ID") t2
	   using("ID")
	   where t1.date=t2.date
	     and t1."Author Full Name" in ("Nolan Prescott",
					   "Coworker A",
					   "Coworker B",
					   "Coworker C")
	     and t1."Reviewer Full Name" is not null
	   order by t1.date desc
	) select
	       '"' || recent."Reviewer Full Name" || '"',
	        max(strftime("%Y-%m-%d", date('now', '-14 days')), 
	            strftime("%Y-%m-%d", substr(recent."Review Creation Date", 1, 
	                                        instr(recent."Review Creation Date", ' ')))),
	   strftime('%Y-%m-%d', recent.date),
	       case 
	           when(length(recent."Review Title") > 40) 
	           then '"' || substr(recent."Review Title", 1, 39) || '…"'
	           else '"' || recent."Review Title" || '"'
	        end
	    from recent
	   where recent.date > date('now', '-14 days');
EOF

gnuplot -p <<-EOF
	timeformat = "%Y-%m-%d"
	T(N) = timecolumn(N,timeformat)
	
	unset title
	unset grid
	unset key
	
	set terminal png 1100, 400
	set output 'basic-reviews.png'
	set border 3
	set xtics nomirror
	set ytics nomirror
	set format x "%m/%d" timedate
	set yrange [ -1.00000 : * ]
	
	plot "$DATAFILE" using (T(2)) : (column(0)) : (T(3)-T(2)) : (0.0) : yticlabel(4) \
	         with vector nohead linewidth 2 linecolor rgb "black", \
	     "$DATAFILE" using (T(2)) : (column(0) - 0.25) : 1 \
	         with labels left offset 1
EOF

rm $DATAFILE