structure Remind :> REMIND =
struct

open Config

structure C = PgClient

fun printReal n =
    if n < 0.0 then
	"-" ^ Real.fmt (StringCvt.FIX (SOME 2)) (~n)
    else
	Real.fmt (StringCvt.FIX (SOME 2)) n

fun main _ =
    let
	val db = C.conn dbstring

	val totalShares = case C.oneRow db "SELECT SUM(shares) FROM WebUserPaying" of
			      [total] => C.intFromSql total
			    | _ => raise Fail "Bad SUM(shares) row"

	fun doOne [user, members, shares, amount] =
	    let
		val user = C.stringFromSql user
		val members = C.intFromSql members
		val shares = C.intFromSql shares
		val amount = C.realFromSql amount

		val minimum = 900.0 * real shares / real totalShares * 2.0
	    in
		if amount >= minimum then
		    ()
		else
		    let
			val m = Mail.mopen ()
			fun write msg = Mail.mwrite (m, msg)
		    in
			if amount < 0.0 then
			    write "Subject: Your NEGATIVE HCoop balance\n"
			else
			    write "Subject: Your low HCoop balance\n";
			write "From: HCoop Payment Reminder Robot <payment";
			write emailSuffix;
			write ">\n";
			write "To: ";
			write user;
			write emailSuffix;
			write "\nCc: payment@hcoop.net";
			write "\n\n";

			if amount < 0.0 then
			    (write "Your HCoop balance is negative.  This means that you've paid less than you've\n";
			     write "been charged to date.  Our bylaws allow our board of directors to vote you out\n";
			     write "of the co-op, without any obligation to contact you first, when your balance\n";
			     write "stays negative for three months.  Please make a payment as soon as possible, so\n";
			     write "that we don't need to do this!\n\n")
			else
			    (write "Your HCoop balance has dropped below your requested minimum, based on your\n";
			     write "sliding scale pledge amount.  Please make a payment as soon as you can.\n\n");

			write "Your balance: US$";
			write (printReal amount);
			write "\nTotal number of members linked to your balance: ";
			write (Int.toString members);
			write "\nTotal pledge amount: ";
			write (Int.toString shares);
			write "\nRequested minimum: US$";
			write (printReal (900.0 * real shares / real totalShares * 2.0));

			write "\n\nYour minimum was calculated by dividing our total monthly expenses ($900) by the\n";
			write "sum of all members' pledge amounts, multiplying by your pledge amount, and then\n";
			write "multiplying by 2.  That is, the amount covers your share of two months' expenses.\n\n";

			write "To make a payment, visit:\n";
			write "     https://members2.hcoop.net/\n";
			write "and use the PayPal or Google Checkout link.\n";

			write "\nIf you don't know how to get a username and password for members2.hcoop.net, visit:\n";
			write "     http://wiki2.hcoop.net/MemberManual/MigrationGuide\n";
			write "for a reminder.  (\"Step 1\" is all you need to read.)\n";

			write "\nIf for whatever reason you don't plan to pay the amount suggested in this e-mail,\n";
			write "_please_ don't stay silent.  Reply to this message explaining your circumstances.\n";
			write "We are doing limited-time monetary grants on request, due to the extra costs\n";
			write "associated with setting up our new servers.\n";

			ignore (Mail.mclose m)
		    end
	    end
	  | doOne _ = raise Fail "Bad SQL row"
    in
	C.app db doOne "SELECT Balance.name, COUNT(*), SUM(WebUserPaying.shares) AS shrs, Balance.amount FROM WebUserPaying JOIN Balance ON WebUserPaying.bal = Balance.id GROUP BY Balance.name, Balance.amount HAVING amount < 10";
	C.close db;
	OS.Process.success
    end handle C.Sql s => (print ("SQL failure: " ^ s ^ "\n");
			   OS.Process.failure)

end