#!/usr/bin/ocamlrun /usr/bin/ocaml

#directory "+pcre";;
#directory "+postgres";;
#directory "+dbi";;
#directory "+extlib";;

#load "unix.cma";;
#load "pcre.cma";;
#load "postgres.cma";;
#load "dbi.cma";;
#load "dbi_postgres.cmo";;
#load "extLib.cma";;

open Printf

module DB = Dbi_postgres

let hostid = 7

let () =
  print_endline ("*** warning *** This script deletes all mail from wiki.merjis.com (hostid=" ^ string_of_int hostid ^ ")");
  print_endline "to continue type 'yes'";
  let line = read_line () in
  if line <> "yes" then exit 1

let dbh = new DB.connection "cocanwiki"

let () =
  let sth = dbh#prepare_cached "select id, subject from messages
                                 where hostid = ?" in
  sth#execute [`Int hostid];

  let msgs = sth#map (function [`Int id; `String subject] -> id, subject
			| _ -> assert false) in

  (* Delete the pages. *)
  List.iter
    (fun (msgid, subject) ->
       let title = sprintf "Mail/%s (%d)" subject msgid in
       let sth = dbh#prepare_cached "select id, url from pages
                                      where hostid = ? and title = ?
                                        and url is not null" in
       sth#execute [`Int hostid; `String title];
       let pageid, url =
	 match sth#fetch1 () with
	     [ `Int id; `String url ] -> id, url
	   | _ -> assert false in

       (* This URL might appear in a few other tables.  Look at the
	* possible constraints:
	*
	* pages_redirect_cn (redirect to this page) unlikely
	* sitemenu_url_cn (in the site menu) no
	* page_emails_url_cn (email notify) possible, but not likely
	* links_from_cn (links from) YES
	* recently_visited_url_cn (recently_visited) VERY LIKELY
	*)
       let sth = dbh#prepare_cached "delete from links
                                      where hostid = ? and from_url = ?" in
       sth#execute [`Int hostid; `String url];
       let sth = dbh#prepare_cached "delete from recently_visited
                                      where hostid = ? and url = ?" in
       sth#execute [`Int hostid; `String url];

       (* Mark the URL as deleted.  What effect this has on the database
	* consistency is not really clear, but I think it should be ok.
	*)
       let sth = dbh#prepare_cached "update pages set url_deleted = url,
                                                      url = null
                                      where hostid = ? and id = ?" in
       sth#execute [`Int hostid; `Int pageid]
    ) msgs;

  (* Delete the messages. *)
  List.iter
    (fun (msgid, _) ->
       let sth = dbh#prepare_cached "delete from msg_references
                                      where message_id = ?" in
       sth#execute [`Int msgid];

       let sth = dbh#prepare_cached "delete from messages where id = ?" in
       sth#execute [`Int msgid]
    ) msgs;

  dbh#commit ()