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

val basePerMonth = 7.0
val deposit = basePerMonth * 3.0

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 perMonth = basePerMonth * real shares
		val headsUp = deposit + perMonth * 2.0
	    in
		if amount >= headsUp then
		    ()
		else
		    let
			val m = Mail.mopen ()
			fun write msg = Mail.mwrite (m, msg)
		    in
			if amount < deposit 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 < deposit then
			    (write "Your HCoop balance is negative.  This means that you've paid less than you've\n";
			     write "been charged to date, excluding your required deposit.  If your account hasn't\n";
			     write "been frozen yet, expect that to happen very soon.  Our bylaws allow our board\n";
			     write "of directors to vote you out of the co-op, without any obligation to contact\n";
			     write "you first, when your balance stays negative for three months.  Please make a\n";
			     write "payment as soon as possible, so that we don't need to do this!\n\n")
			else
			    (write "With our current dues projections, you have less than two months left until\n";
			     write "your HCoop balance becomes negative, based on your sliding scale pledge amount.\n";
			     write "Please make a payment as soon as you can.  We will freeze your account if your\n";
			     write "balance does become negative, and the board of directors will probably vote you\n";
			     write "out of the cooperative shortly thereafter if you don't make a payment.\n\n");

			write "Your balance: US$";
			write (printReal (amount - deposit));
			write "\nTotal number of members linked to your balance: ";
			write (Int.toString members);
			write "\nTotal pledge amount: ";
			write (Int.toString shares);
			write "\nDeposit: US$";
			write (printReal deposit);
			write "\nMinimum amount to pay to not see this message again for two months: US$";
			write (printReal (headsUp - amount));

			write "\n\nThe deposit requirement was calculated as three months of dues at $7/mo..\n\n";

			write "To make a payment, visit:\n";
			write "     https://members.hcoop.net/\n";
			write "and use the PayPal or Stripe link.\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";

			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 < " ^ C.realToSql deposit ^ " + " ^ C.realToSql (basePerMonth * 2.0) ^ " * SUM(WebUserPaying.shares)");
	C.close db;
	OS.Process.success
    end handle C.Sql s => (print ("SQL failure: " ^ s ^ "\n");
			   OS.Process.failure)

end