@ -- to the USER entry.
@ --
@ -- The ssub field is a string where each character indicates a particular
@ -- type of event to subscribe to.  Choices:
@ --     a - Announcements
@ --     c - Check-ins
@ --     f - Forum posts
@ --     n - New forum threads
@ --     r - Replies to my own forum posts
@ --     t - Ticket changes
@ --     w - Wiki changes
@ --     x - Edits to forum posts
@ -- Probably different codes will be added in the future.  In the future
@ -- we might also add a separate table that allows subscribing to email
@ -- notifications for specific branches or tags or tickets.
@ --
@ CREATE TABLE repository.subscriber(
@   subscriberId INTEGER PRIMARY KEY, -- numeric subscriber ID.  Internal use
@   subscriberCode BLOB DEFAULT (randomblob(32)) UNIQUE, -- UUID for subscriber

    int nsub = 0;
    const char *suname = PT("suname");
    if( suname==0 && needCaptcha==0 && !g.perm.Admin ) suname = g.zLogin;
    if( suname && suname[0]==0 ) suname = 0;
    if( PB("sa") ) ssub[nsub++] = 'a';
    if( g.perm.Read && PB("sc") )    ssub[nsub++] = 'c';
    if( g.perm.RdForum && PB("sf") ) ssub[nsub++] = 'f';
    if( g.perm.RdForum && PB("sn") ) ssub[nsub++] = 'n';
    if( g.perm.RdForum && PB("sr") ) ssub[nsub++] = 'r';
    if( g.perm.RdTkt && PB("st") )   ssub[nsub++] = 't';
    if( g.perm.RdWiki && PB("sw") )  ssub[nsub++] = 'w';
    if( g.perm.RdForum && PB("sx") ) ssub[nsub++] = 'x';
    ssub[nsub] = 0;
    zCode = db_text(0,
      "INSERT INTO subscriber(semail,suname,"
      "  sverified,sdonotcall,sdigest,ssub,sctime,mtime,smip,lastContact)"

  if( P("submit")==0 ){
    /* If this is the first visit to this page (if this HTTP request did not
    ** come from a prior Submit of the form) then default all of the
    ** subscription options to "on" */
    cgi_set_parameter_nocopy("sa","1",1);
    if( g.perm.Read )    cgi_set_parameter_nocopy("sc","1",1);
    if( g.perm.RdForum ) cgi_set_parameter_nocopy("sf","1",1);
    if( g.perm.RdForum ) cgi_set_parameter_nocopy("sn","1",1);
    if( g.perm.RdForum ) cgi_set_parameter_nocopy("sr","1",1);
    if( g.perm.RdTkt )   cgi_set_parameter_nocopy("st","1",1);
    if( g.perm.RdWiki )  cgi_set_parameter_nocopy("sw","1",1);
  }
  @ <p>To receive email notifications for changes to this
  @ repository, fill out the form below and press the "Submit" button.</p>
  form_begin(0, "%R/subscribe");
  @ <table class="subscribe">

  @  Announcements</label><br>
  if( g.perm.Read ){
    @  <label><input type="checkbox" name="sc" %s(PCK("sc"))> \
    @  Check-ins</label><br>
  }
  if( g.perm.RdForum ){
    @  <label><input type="checkbox" name="sf" %s(PCK("sf"))> \
    @  Forum Posts</label><br>
    @  All Forum Posts</label><br>
    @  <label><input type="checkbox" name="sn" %s(PCK("sn"))> \
    @  New Forum Threads</label><br>
    @  <label><input type="checkbox" name="sr" %s(PCK("sr"))> \
    @  Replies To My Forum Posts</label><br>
    @  <label><input type="checkbox" name="sx" %s(PCK("sx"))> \
    @  Forum Edits</label><br>
    @  Edits To Forum Posts</label><br>
  }
  if( g.perm.RdTkt ){
    @  <label><input type="checkbox" name="st" %s(PCK("st"))> \
    @  Ticket changes</label><br>
  }
  if( g.perm.RdWiki ){
    @  <label><input type="checkbox" name="sw" %s(PCK("sw"))> \

**         revealing the complete subscriber code, and hence without
**         verifying the email address.
*/
void alert_page(void){
  const char *zName = 0;        /* Value of the name= query parameter */
  Stmt q;                       /* For querying the database */
  int sa, sc, sf, st, sw, sx;   /* Types of notifications requested */
  int sn, sr;
  int sdigest = 0, sdonotcall = 0, sverified = 0;  /* Other fields */
  int isLogin;                  /* True if logged in as an individual */
  const char *ssub = 0;         /* Subscription flags */
  const char *semail = 0;       /* Email address */
  const char *smip;             /* */
  const char *suname = 0;       /* Corresponding user.login value */
  const char *mtime;            /* */

    sdonotcall = PB("sdonotcall");
    sdigest = PB("sdigest");
    semail = P("semail");
    if( PB("sa") )                   newSsub[nsub++] = 'a';
    if( g.perm.Read && PB("sc") )    newSsub[nsub++] = 'c';
    if( g.perm.RdForum && PB("sf") ) newSsub[nsub++] = 'f';
    if( g.perm.RdForum && PB("sn") ) newSsub[nsub++] = 'n';
    if( g.perm.RdForum && PB("sr") ) newSsub[nsub++] = 'r';
    if( g.perm.RdTkt && PB("st") )   newSsub[nsub++] = 't';
    if( g.perm.RdWiki && PB("sw") )  newSsub[nsub++] = 'w';
    if( g.perm.RdForum && PB("sx") ) newSsub[nsub++] = 'x';
    newSsub[nsub] = 0;
    ssub = newSsub;
    blob_init(&update, "UPDATE subscriber SET", -1);
    blob_append_sql(&update,

  if( suname==0 ){
    suname = db_column_text(&q, 6);
    sverified = db_column_int(&q, 1);
  }
  sa = strchr(ssub,'a')!=0;
  sc = strchr(ssub,'c')!=0;
  sf = strchr(ssub,'f')!=0;
  sn = strchr(ssub,'n')!=0;
  sr = strchr(ssub,'r')!=0;
  st = strchr(ssub,'t')!=0;
  sw = strchr(ssub,'w')!=0;
  sx = strchr(ssub,'x')!=0;
  smip = db_column_text(&q, 5);
  mtime = db_column_text(&q, 7);
  sctime = db_column_text(&q, 8);
  if( !g.perm.Admin && !sverified ){

  @  Announcements</label><br>
  if( g.perm.Read ){
    @  <label><input type="checkbox" name="sc" %s(sc?"checked":"")>\
    @  Check-ins</label><br>
  }
  if( g.perm.RdForum ){
    @  <label><input type="checkbox" name="sf" %s(sf?"checked":"")>\
    @  Forum Posts</label><br>
    @  All Forum Posts</label><br>
    @  <label><input type="checkbox" name="sn" %s(sn?"checked":"")>\
    @  New Forum Threads</label><br>
    @  <label><input type="checkbox" name="sr" %s(sr?"checked":"")>\
    @  Replies To My Posts</label><br>
    @  <label><input type="checkbox" name="sx" %s(sx?"checked":"")>\
    @  Forum Edits</label><br>
    @  Edits To Forum Posts</label><br>
  }
  if( g.perm.RdTkt ){
    @  <label><input type="checkbox" name="st" %s(st?"checked":"")>\
    @  Ticket changes</label><br>
  }
  if( g.perm.RdWiki ){
    @  <label><input type="checkbox" name="sw" %s(sw?"checked":"")>\

** A single event that might appear in an alert is recorded as an
** instance of the following object.
**
** type values:
**
**      c       A new check-in
**      f       An original forum post
**      n       New forum threads
**      r       Replies to my forum posts
**      x       An edit to a prior forum post
**      t       A new ticket or a change to an existing ticket
**      w       A change to a wiki page
**      x       Edits to forum posts
*/
struct EmailEvent {
  int type;          /* 'c', 'f', 't', 'w', 'x' */
  int type;          /* 'c', 'f', 'n', 'r', 't', 'w', 'x' */
  int needMod;       /* Pending moderator approval */
  Blob hdr;          /* Header content, for forum entries */
  Blob txt;          /* Text description to appear in an alert */
  char *zFromName;   /* Human name of the sender */
  char *zPriors;     /* Upthread sender IDs for forum posts */
  EmailEvent *pNext; /* Next in chronological order */
};
#endif

/*
** Free a linked list of EmailEvent objects
*/
void alert_free_eventlist(EmailEvent *p){
  while( p ){
    EmailEvent *pNext = p->pNext;
    blob_reset(&p->txt);
    blob_reset(&p->hdr);
    fossil_free(p->zFromName);
    fossil_free(p->zPriors);
    fossil_free(p);
    p = pNext;
  }
}

/*
** Compute a string that is appropriate for the EmailEvent.zPriors field
** for a particular forum post.
**
** This string is an encode list of sender names and rids for all ancestors
** of the fpdi post - the post that fpid answer, the post that that parent 
** post answers, and so forth back up to the root post. Duplicates sender
** names are omitted.
**
** The EmailEvent.zPriors field is used to screen events for people who
** only want to see replies to their own posts or to specific posts.
*/
static char *alert_compute_priors(int fpid){
  return db_text(0,
    "WITH priors(rid,who) AS ("
    "  SELECT firt, coalesce(euser,user)"
    "    FROM forumpost LEFT JOIN event ON fpid=objid"
    "   WHERE fpid=%d"
    "  UNION ALL"
    "  SELECT firt, coalesce(euser,user)"
    "    FROM priors, forumpost LEFT JOIN event ON fpid=objid"
    "   WHERE fpid=rid"
    ")"
    "SELECT ','||group_concat(DISTINCT 'u'||who)||"
           "','||group_concat(rid) FROM priors;",
    fpid
  );
}

/*
** Compute and return a linked list of EmailEvent objects
** corresponding to the current content of the temp.wantalert
** table which should be defined as follows:
**
**     CREATE TEMP TABLE wantalert(eventId TEXT, needMod BOOLEAN);

    "   AND eventId GLOB 'f*'"
    "   AND forumpost.fpid=event.objid"
    " ORDER BY event.mtime"
  );
  zFrom = db_get("email-self",0);
  zSub = db_get("email-subname","");
  while( db_step(&q)==SQLITE_ROW ){
    int fpid = db_column_int(&q,0);
    Manifest *pPost = manifest_get(db_column_int(&q,0), CFTYPE_FORUM, 0);
    Manifest *pPost = manifest_get(fpid, CFTYPE_FORUM, 0);
    const char *zIrt;
    const char *zUuid;
    const char *zTitle;
    const char *z;
    if( pPost==0 ) continue;
    p = fossil_malloc( sizeof(EmailEvent) );
    pLast->pNext = p;
    pLast = p;
    p->type = db_column_int(&q,7) ? 'f' : 'x';
    p->needMod = db_column_int(&q, 5);
    z = db_column_text(&q,6);
    p->zFromName = z && z[0] ? fossil_strdup(z) : 0;
    p->zPriors = alert_compute_priors(fpid);
    p->pNext = 0;
    blob_init(&p->hdr, 0, 0);
    zUuid = db_column_text(&q, 1);
    zTitle = db_column_text(&q, 3);
    if( p->needMod ){
      blob_appendf(&p->hdr, "Subject: %s Pending Moderation: %s\r\n",
                   zSub, zTitle);

    "If you take no action, your subscription will expire and you will be\n"
    "unsubscribed in about %d days.  To make other changes or to unsubscribe\n"
    "immediately, visit the following webpage:\n\n"
    "  %s/alerts/%s\n\n",
    ALERT_RENEWAL_MSG_FREQUENCY, zUrl, zCode
  );
}

/*
** If zUser is a sender of one of the ancestors of a forum post
** (if zUser appears in zPriors) then return true.
*/
static int alert_in_priors(const char *zUser, const char *zPriors){
  int n = (int)strlen(zUser);
  char zBuf[200];
  if( n>195 ) return 0;
  if( zPriors==0 || zPriors[0]==0 ) return 0;
  zBuf[0] = ',';
  zBuf[1] = 'u';
  memcpy(zBuf+2, zUser, n+1);
  return strstr(zPriors, zBuf)!=0;
}

#if INTERFACE
/*
** Flags for alert_send_alerts()
*/
#define SENDALERT_DIGEST      0x0001    /* Send a digest */
#define SENDALERT_PRESERVE    0x0002    /* Do not mark the task as done */

  blob_init(&hdr, 0, 0);
  blob_init(&body, 0, 0);
  db_prepare(&q,
     "SELECT"
     " hex(subscriberCode),"  /* 0 */
     " semail,"               /* 1 */
     " ssub,"                 /* 2 */
     " fullcap(user.cap)"     /* 3 */
     " fullcap(user.cap),"    /* 3 */
     " suname"                /* 4 */
     " FROM subscriber LEFT JOIN user ON (login=suname)"
     " WHERE sverified"
     "   AND NOT sdonotcall"
     "   AND sdigest IS %s"
     "   AND coalesce(subscriber.lastContact,subscriber.mtime)>=%d",
     zDigest/*safe-for-%s*/,
     db_get_int("email-renew-cutoff",0)
  );
  while( db_step(&q)==SQLITE_ROW ){
    const char *zCode = db_column_text(&q, 0);
    const char *zSub = db_column_text(&q, 2);
    const char *zEmail = db_column_text(&q, 1);
    const char *zCap = db_column_text(&q, 3);
    int nHit = 0;
    for(p=pEvents; p; p=p->pNext){
      if( strchr(zSub,p->type)==0 ) continue;
      if( strchr(zSub,p->type)==0 ){
        if( p->type!='f' ) continue;
        if( strchr(zSub,'n')!=0 && (p->zPriors==0 || p->zPriors[0]==0) ){
          /* New post: accepted */
        }else if( strchr(zSub,'r')!=0
               && alert_in_priors(db_column_text(&q,4), p->zPriors) ){
          /* A follow-up to a post written by the user: accept */
        }else{
          continue;
        }
      }
      if( p->needMod ){
        /* For events that require moderator approval, only send an alert
        ** if the recipient is a moderator for that type of event.  Setup
        ** and Admin users always get notified. */
        char xType = '*';
        if( strpbrk(zCap,"as")==0 ){
          switch( p->type ){
            case 'x': case 'f':  xType = '5';  break;
            case 'x': case 'f':
            case 'n': case 'r':  xType = '5';  break;
            case 't':            xType = 'q';  break;
            case 'w':            xType = 'l';  break;
          }
          if( strchr(zCap,xType)==0 ) continue;
        }
      }else if( strchr(zCap,'s')!=0 || strchr(zCap,'a')!=0 ){
        /* Setup and admin users can get any notification that does not
        ** require moderation */
      }else{
        /* Other users only see the alert if they have sufficient
        ** privilege to view the event itself */
        char xType = '*';
        switch( p->type ){
          case 'c':            xType = 'o';  break;
          case 'x': case 'f':  xType = '2';  break;
          case 'x': case 'f':
          case 'n': case 'r':  xType = '2';  break;
          case 't':            xType = 'r';  break;
          case 'w':            xType = 'j';  break;
        }
        if( strchr(zCap,xType)==0 ) continue;
      }
      if( blob_size(&p->hdr)>0 ){
        /* This alert should be sent as a separate email */