@ -- 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
@ --     k - ** Special: Unsubscribed using /oneclickunsub
@ --     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

@ --
@ CREATE TABLE repository.pending_alert(
@   eventid TEXT PRIMARY KEY,         -- Object that changed
@   sentSep BOOLEAN DEFAULT false,    -- individual alert sent
@   sentDigest BOOLEAN DEFAULT false, -- digest alert sent
@   sentMod BOOLEAN DEFAULT false     -- pending moderation alert sent
@ ) WITHOUT ROWID;
@
@ 
@ -- Obsolete table.  No longer used.
@ DROP TABLE IF EXISTS repository.alert_bounce;
;

/*
** Return true if the email notification tables exist.
*/

*/
void email_header_to(Blob *pMsg, int *pnTo, char ***pazTo){
  int nTo = 0;
  char **azTo = 0;
  Blob v;
  char *z, *zAddr;
  int i;

  
  email_header_value(pMsg, "to", &v);
  z = blob_str(&v);
  for(i=0; z[i]; i++){
    if( z[i]=='<' && (zAddr = email_copy_addr(&z[i+1],'>'))!=0 ){
      azTo = fossil_realloc(azTo, sizeof(azTo[0])*(nTo+1) );
      azTo[nTo++] = zAddr;
    }
  }
  *pnTo = nTo;
  *pazTo = azTo;
}

/*
** Free a list of To addresses obtained from a prior call to
** Free a list of To addresses obtained from a prior call to 
** email_header_to()
*/
void email_header_to_free(int nTo, char **azTo){
  int i;
  for(i=0; i<nTo; i++) fossil_free(azTo[i]);
  fossil_free(azTo);
}

**     From:
**     Date:
**     Message-Id:
**     Content-Type:
**     Content-Transfer-Encoding:
**     MIME-Version:
**     Sender:
**
**     
** The caller maintains ownership of the input Blobs.  This routine will
** read the Blobs and send them onward to the email system, but it will
** not free them.
**
** The Message-Id: field is added if there is not already a Message-Id
** in the pHdr parameter.
**
** If the zFromName argument is not NULL, then it should be a human-readable
** name or handle for the sender.  In that case, "From:" becomes a made-up
** email address based on a hash of zFromName and the domain of email-self,
** and an additional "Sender:" field is inserted with the email-self
** address.  Downstream software might use the Sender header to set
** the envelope-from address of the email.  If zFromName is a NULL pointer,
** the envelope-from address of the email.  If zFromName is a NULL pointer, 
** then the "From:" is set to the email-self value and Sender is
** omitted.
*/
void alert_send(
  AlertSender *p,           /* Emailer context */
  Blob *pHdr,               /* Email header (incomplete) */
  Blob *pBody,              /* Email body */

** the basename for hyperlinks included in email alert text.
** Omit the trailing "/".  If the repository is not intended to be
** a long-running server and will not be sending email notifications,
** then leave this setting blank.
*/
/*
** SETTING: email-admin               width=40
** This is the email address for the human administrator for the system.
** This is the email address for the human administrator for the system. 
** Abuse and trouble reports and password reset requests are send here.
*/
/*
** SETTING: email-subname             width=16
** This is a short name used to identifies the repository in the Subject:
** line of email alerts. Traditionally this name is included in square
** brackets. Examples: "[fossil-src]", "[sqlite-src]".

** a subscription is less than email-renew-cutoff, then now new emails
** are sent to the subscriber.
**
** email-renew-warning is the time (in days since 1970-01-01) when the
** last batch of "your subscription is about to expire" emails were
** sent out.
**
** email-renew-cutoff is normally 7 days behind email-renew-warning.
** email-renew-cutoff is normally 7 days behind email-renew-warning.  
*/
/*
** SETTING: email-send-method         width=5 default=off sensitive
** Determine the method used to send email.  Allowed values are
** "off", "relay", "pipe", "dir", "db", and "stdout".  The "off" value
** means no email is ever sent.  The "relay" value means emails are sent
** to an Mail Sending Agent using SMTP located at email-send-relayhost.
** The "pipe" value means email messages are piped into a command
** The "pipe" value means email messages are piped into a command 
** determined by the email-send-command setting. The "dir" value means
** emails are written to individual files in a directory determined
** by the email-send-dir setting.  The "db" value means that emails
** are added to an SQLite database named by the* email-send-db setting.
** The "stdout" value writes email text to standard output, for debugging.
*/
/*

** SMTP server configured as a Mail Submission Agent listening on the
** designated host and port and all times.
*/


/*
** COMMAND: alerts*
**
** 
** Usage: %fossil alerts SUBCOMMAND ARGS...
**
** Subcommands:
**
**    pending                 Show all pending alerts.  Useful for debugging.
**
**    reset                   Hard reset of all email notification tables

}

/*
** Either shutdown or completely delete a subscription entry given
** by the hex value zName.  Then paint a webpage that explains that
** the entry has been removed.
*/
static void alert_unsubscribe(int sid){
static void alert_unsubscribe(int sid, int bTotal){
  const char *zEmail = 0;
  const char *zLogin = 0;
  int uid = 0;
  Stmt q;
  db_prepare(&q, "SELECT semail, suname FROM subscriber"
                 " WHERE subscriberId=%d", sid);
  if( db_step(&q)==SQLITE_ROW ){
    zEmail = db_column_text(&q, 0);
    zLogin = db_column_text(&q, 1);
    uid = db_int(0, "SELECT uid FROM user WHERE login=%Q", zLogin);
  }
  style_set_current_feature("alerts");
  if( zEmail==0 ){
    style_header("Unsubscribe Fail");
    @ <p>Unable to locate a subscriber with the requested key</p>
  }else{
    db_unprotect(PROTECT_READONLY);
    if( bTotal ){
      /* Completely delete the subscriber */
    db_multi_exec(
      "DELETE FROM subscriber WHERE subscriberId=%d", sid
    );
      db_multi_exec(
        "DELETE FROM subscriber WHERE subscriberId=%d", sid
      );
    }else{
      /* Keep the subscriber, but turn off all notifications */
      db_multi_exec(
        "UPDATE subscriber SET ssub='k', mtime=now() WHERE subscriberId=%d",
        sid
      );
    }
    db_protect_pop();
    style_header("Unsubscribed");
    @ <p>The "%h(zEmail)" email address has been unsubscribed from all
    @ notifications.  All subscription records for "%h(zEmail)" have
    @ been purged.  No further emails will be sent to "%h(zEmail)".</p>
    if( uid && g.perm.Admin ){
       @ <p>You may also want to
       @ <a href="%R/setup_uedit?id=%d(uid)">edit or delete

**         email and clicks on the link in the email.  When a
**         compilete subscriberCode is seen on the name= query parameter,
**         that constitutes verification of the email address.
**
**    *    The sid= query parameter contains an integer subscriberId.
**         This only works for the administrator.  It allows the
**         administrator to edit any subscription.
**
**         
**    *    The user is logged into an account other than "nobody" or
**         "anonymous".  In that case the notification settings
**         associated with that account can be edited without needing
**         to know the subscriber code.
**
**    *    The name= query parameter contains a 32-digit prefix of
**         subscriber code.  (Subscriber codes are normally 64 hex digits

  }
  if( P("delete")!=0 && cgi_csrf_safe(2) ){
    if( !PB("dodelete") ){
      eErr = 9;
      zErr = mprintf("Select this checkbox and press \"Unsubscribe\" again to"
                     " unsubscribe");
    }else{
      alert_unsubscribe(sid);
      alert_unsubscribe(sid, 1);
      db_commit_transaction();
      return;
      return; 
    }
  }
  style_set_current_feature("alerts");
  style_header("Update Subscription");
  db_prepare(&q,
    "SELECT"
    "  semail,"                       /* 0 */

    @  Ticket changes</label><br>
  }
  if( g.perm.RdWiki ){
    @  <label><input type="checkbox" name="sw" %s(sw?"checked":"")>\
    @  Wiki</label>
  }
  @ </td></tr>
  if( strchr(ssub,'k')!=0 ){
    @ <tr><td></td><td>&nbsp;&uarr;&nbsp;
    @ Note: User did a one-click unsubscribe</td></tr>
  }
  @ <tr>
  @  <td class="form_label">Delivery:</td>
  @  <td><select size="1" name="sdigest">
  @     <option value="0" %s(sdigest?"":"selected")>Individual Emails</option>
  @     <option value="1" %s(sdigest?"selected":"")>Daily Digest</option>
  @     </select></td>
  @ </tr>

  style_finish_page();
}


/* This is the message that gets sent to describe how to change
** or modify a subscription
*/
static const char zUnsubMsg[] =
static const char zUnsubMsg[] = 
@ To changes your subscription settings at %s visit this link:
@
@    %s/alerts/%s
@
@ To completely unsubscribe from %s, visit the following link:
@
@    %s/unsubscribe/%s
;

/*
** WEBPAGE: unsubscribe
** WEBPAGE: oneclickunsub
**
** Users visit this page to be delisted from email alerts.
**
** If a valid subscriber code is supplied in the name= query parameter,
** then that subscriber is delisted.
**
** Otherwise, If the users is logged in, then they are redirected
** to the /alerts page where they have an unsubscribe button.
**
** Non-logged-in users with no name= query parameter are invited to enter
** an email address to which will be sent the unsubscribe link that
** contains the correct subscriber code.
**
** The /unsubscribe page requires comfirmation.  The /oneclickunsub
** page unsubscribes immediately without any need to confirm.
*/
void unsubscribe_page(void){
  const char *zName = P("name");
  char *zErr = 0;
  int eErr = 0;
  unsigned int uSeed = 0;
  const char *zDecoded;
  char *zCaptcha = 0;
  int dx;
  int bSubmit;
  const char *zEAddr;
  char *zCode = 0;
  int sid = 0;

  if( zName==0 ) zName = P("scode");

  /* If a valid subscriber code is supplied, then either present the user
  ** with a confirmation, or if already confirmed, unsubscribe immediately.
  ** with a comformation, or if already confirmed, unsubscribe immediately.
  */
  if( zName
  if( zName 
   && (sid = db_int(0, "SELECT subscriberId FROM subscriber"
                       " WHERE subscriberCode=hextoblob(%Q)", zName))!=0
  ){
    char *zUnsubName = mprintf("confirm%04x", sid);
    if( P(zUnsubName)!=0 ){
      alert_unsubscribe(sid);
      alert_unsubscribe(sid, 1);
    }else if( sqlite3_strglob("*oneclick*",g.zPath)==0 ){
      alert_unsubscribe(sid, 0);
    }else if( P("manage")!=0 ){
      cgi_redirectf("%R/alerts/%s", zName);
    }else{
      style_header("Unsubscribe");
      form_begin(0, "%R/unsubscribe");
      @ <input type="hidden" name="scode" value="%h(zName)">
      @ <table border="0" cellpadding="10" width="100%%">

    }else{
      @ <p>An email has been sent to "%h(zEAddr)" that explains how to
      @ unsubscribe and/or modify your subscription settings</p>
    }
    alert_sender_free(pSender);
    style_finish_page();
    return;
  }
  }  

  /* Non-logged-in users have to enter an email address to which is
  ** sent a message containing the unsubscribe link.
  */
  style_header("Unsubscribe Request");
  @ <p>Fill out the form below to request an email message that will
  @ explain how to unsubscribe and/or change your subscription settings.</p>

    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);
    }else{
      blob_appendf(&p->hdr, "Subject: %s %s\r\n", zSub, zTitle);
      blob_appendf(&p->hdr, "Message-Id: <%.32s@%s>\r\n",
      blob_appendf(&p->hdr, "Message-Id: <%.32s@%s>\r\n", 
                   zUuid, alert_hostname(zFrom));
      zIrt = db_column_text(&q, 4);
      if( zIrt && zIrt[0] ){
        blob_appendf(&p->hdr, "In-Reply-To: <%.32s@%s>\r\n",
                     zIrt, alert_hostname(zFrom));
      }
    }

      if( blob_size(&p->hdr)>0 ){
        /* This alert should be sent as a separate email */
        Blob fhdr, fbody;
        blob_init(&fhdr, 0, 0);
        blob_appendf(&fhdr, "To: <%s>\r\n", zEmail);
        blob_append(&fhdr, blob_buffer(&p->hdr), blob_size(&p->hdr));
        blob_init(&fbody, blob_buffer(&p->txt), blob_size(&p->txt));
        blob_appendf(&fhdr, "List-Unsubscribe: <%s/unsubscribe/%s>\r\n",
        blob_appendf(&fhdr, "List-Unsubscribe: <%s/oneclickunsub/%s>\r\n",
                     zUrl, zCode);
        blob_appendf(&fhdr,
                   "List-Unsubscribe-Post: List-Unsubscribe=One-Click\r\n");
        blob_appendf(&fbody, "\n-- \nUnsubscribe: %s/unsubscribe/%s\n",
           zUrl, zCode);
        /* blob_appendf(&fbody, "Subscription settings: %s/alerts/%s\n",
        **   zUrl, zCode); */

        }
        nHit++;
        blob_append(&body, "\n", 1);
        blob_append(&body, blob_buffer(&p->txt), blob_size(&p->txt));
      }
    }
    if( nHit==0 ) continue;
    blob_appendf(&hdr, "List-Unsubscribe: <%s/unsubscribe/%s>\r\n",
    blob_appendf(&hdr, "List-Unsubscribe: <%s/oneclickunsub/%s>\r\n",
         zUrl, zCode);
    blob_appendf(&hdr, "List-Unsubscribe-Post: List-Unsubscribe=One-Click\r\n");
    blob_appendf(&body,"\n-- \nSubscription info: %s/alerts/%s\n",
         zUrl, zCode);
    alert_send(pSender,&hdr,&body,0);
    nSent++;
    blob_truncate(&hdr, 0);

         "   AND length(sdigest)>0",
         iNewWarn, iOldWarn
      );
      while( db_step(&q)==SQLITE_ROW ){
        Blob hdr, body;
        blob_init(&hdr, 0, 0);
        blob_init(&body, 0, 0);
        alert_renewal_msg(&hdr, &body,
        alert_renewal_msg(&hdr, &body, 
           db_column_text(&q,0),
           db_column_int(&q,1),
           db_column_text(&q,2),
           db_column_text(&q,3),
           zRepoName, zUrl);
        alert_send(pSender,&hdr,&body,0);
        blob_reset(&hdr);

  style_set_current_feature("alerts");
  if( zAdminEmail==0 || zAdminEmail[0]==0 ){
    style_header("Outbound Email Disabled");
    @ <p>Outbound email is disabled on this repository
    style_finish_page();
    return;
  }
  if( P("submit")!=0
  if( P("submit")!=0 
   && P("subject")!=0
   && P("msg")!=0
   && P("from")!=0
   && cgi_csrf_safe(2)
   && captcha_is_correct(0)
  ){
    Blob hdr, body;