Fossil

@   suname TEXT,                      -- corresponding USER entry
@   sverified BOOLEAN DEFAULT true,   -- email address verified
@   sdonotcall BOOLEAN,               -- true for Do Not Call 
@   sdigest BOOLEAN,                  -- true for daily digests only
@   ssub TEXT,                        -- baseline subscriptions
@   sctime INTDATE,                   -- When this entry was created. unixtime
@   mtime INTDATE,                    -- Last change.  unixtime
@   smip TEXT,                        -- IP address of last change
@   lastContact INT                   -- Last contact. days since 1970
@ );
@ CREATE INDEX repository.subscriberUname
@   ON subscriber(suname) WHERE suname IS NOT NULL;
@ 
@ DROP TABLE IF EXISTS repository.pending_alert;
@ -- Email notifications that need to be sent.
@ --
@ -- The first character of the eventid determines the event type.
@ -- Remaining characters determine the specific event.  For example,
@ -- 'c4413' means check-in with rid=4413.
@ --
@ 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.
*/
int alert_tables_exist(void){
  return db_table_exists("repository", "subscriber");
}

/*
** Record the fact that user zUser has made contact with the repository.
** This resets the subscription timeout on that user.
*/
void alert_user_contact(const char *zUser){
  if( db_table_has_column("repository","subscriber","lastContact") ){
    db_multi_exec(
      "UPDATE subscriber SET lastContact=now()/86400 WHERE suname=%Q",
      zUser
    );
  }
}

/*
** Make sure the table needed for email notification exist in the repository.
**
** If the bOnlyIfEnabled option is true, then tables are only created
** if the email-send-method is something other than "off".
*/
void alert_schema(int bOnlyIfEnabled){
  if( !alert_tables_exist() ){
    if( bOnlyIfEnabled
     && fossil_strcmp(db_get("email-send-method",0),"off")==0
    ){
      return;  /* Don't create table for disabled email */
    }
    db_exec_sql(zAlertInit);
    return;
  }
  if( db_table_has_column("repository","subscriber","lastContact") ){
    return;
  }
  db_multi_exec(
    "DROP TABLE IF EXISTS repository.alert_bounde;\n"
    "ALTER TABLE repository.subscriber ADD COLUMN lastContact INT;\n"
    "UPDATE subscriber SET lastContact=mtime/86400;"
  );
  if( db_table_has_column("repository","pending_alert","sentMod") ){
    return;
  }
  db_multi_exec(
    "ALTER TABLE repository.pending_alert"
    " ADD COLUMN sentMod BOOLEAN DEFAULT false;"
  );

}

/*
** Enable triggers that automatically populate the pending_alert
** table.
*/
void alert_create_trigger(void){

    if( g.perm.RdForum && PB("sf") ) ssub[nsub++] = 'f';
    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)"
      "VALUES(%Q,%Q,%d,0,%d,%Q,now(),now(),%Q,now()/86400)"
      "RETURNING hex(subscriberCode);",
      /* semail */    zEAddr,
      /* suname */    suname,
      /* sverified */ needCaptcha==0,
      /* sdigest */   PB("di"),
      /* ssub */      ssub,
      /* smip */      g.zIpAddr

  const char *mtime;            /* */
  const char *sctime;           /* Time subscription created */
  int eErr = 0;                 /* Type of error */
  char *zErr = 0;               /* Error message text */
  int sid = 0;                  /* Subscriber ID */
  int nName;                    /* Length of zName in bytes */
  char *zHalfCode;              /* prefix of subscriberCode */
  int keepAlive = 0;            /* True to update the last contact time */

  db_begin_transaction();
  if( alert_webpages_disabled() ){
    db_commit_transaction();
    return;
  }
  login_check_credentials();

  }
  if( sid==0 && nName>=32 ){
    sid = db_int(0,
       "SELECT CASE WHEN hex(subscriberCode) LIKE (%Q||'%%')"
       "            THEN subscriberId ELSE 0 END"
       "  FROM subscriber WHERE subscriberCode>=hextoblob(%Q)"
       " LIMIT 1", zName, zName);
    if( sid ) keepAlive = 1;
  }
  if( sid==0 && isLogin ){
    sid = db_int(0, "SELECT subscriberId FROM subscriber"
                    " WHERE suname=%Q", g.zLogin);
  }
  if( sid==0 ){
    db_commit_transaction();

    newSsub[nsub] = 0;
    ssub = newSsub;
    blob_init(&update, "UPDATE subscriber SET", -1);
    blob_append_sql(&update,
        " sdonotcall=%d,"
        " sdigest=%d,"
        " ssub=%Q,"
        " mtime=now(),"
        " lastContact=now()/86400,"
        " smip=%Q",
        sdonotcall,
        sdigest,
        ssub,
        g.zIpAddr
    );
    if( g.perm.Admin ){

    }
    blob_append_sql(&update," WHERE subscriberId=%d", sid);
    if( eErr==0 ){
      db_exec_sql(blob_str(&update));
      ssub = 0;
    }
    blob_reset(&update);
  }else if( keepAlive ){
    db_multi_exec(
      "UPDATE subscriber SET lastContact=now()/86400"
      " WHERE subscriberId=%d", sid
    );
  }
  if( P("delete")!=0 && cgi_csrf_safe(1) ){
    if( !PB("dodelete") ){
      eErr = 9;
      zErr = mprintf("Select this checkbox and press \"Unsubscribe\" again to"
                     " unsubscribe");
    }else{

  style_header("Subscriber List");
  nTotal = db_int(0, "SELECT count(*) FROM subscriber");
  nPending = db_int(0, "SELECT count(*) FROM subscriber WHERE NOT sverified");
  if( nPending>0 && P("purge") && cgi_csrf_safe(0) ){
    int nNewPending;
    db_multi_exec(
       "DELETE FROM subscriber"
       " WHERE NOT sverified AND mtime<now()-86400"
    );
    nNewPending = db_int(0, "SELECT count(*) FROM subscriber"
                            " WHERE NOT sverified");
    nDel = nPending - nNewPending;
    nPending = nNewPending;
    nTotal -= nDel;
  }
  if( nPending>0 ){
    @ <h1>%,d(nTotal) Subscribers, %,d(nPending) Pending</h1>
    if( nDel==0 && 0<db_int(0,"SELECT count(*) FROM subscriber"
            " WHERE NOT sverified AND mtime<now()-86400")
    ){
      style_submenu_element("Purge Pending","subscribers?purge");
    }
  }else{
    @ <h1>%,d(nTotal) Subscribers</h1>
  }
  if( nDel>0 ){
    @ <p>*** %d(nDel) pending subscriptions deleted ***</p>
  }
  blob_init(&sql, 0, 0);
  blob_append_sql(&sql,
    "SELECT subscriberId,"                 /* 0 */
    "       semail,"                       /* 1 */
    "       ssub,"                         /* 2 */
    "       suname,"                       /* 3 */
    "       sverified,"                    /* 4 */
    "       sdigest,"                      /* 5 */
    "       mtime,"                        /* 6 */
    "       date(sctime,'unixepoch'),"     /* 7 */
    "       (SELECT uid FROM user WHERE login=subscriber.suname)," /* 8 */
    "       coalesce(lastContact,mtime/86400)"                     /* 9 */
    " FROM subscriber"
  );
  if( P("only")!=0 ){
    blob_append_sql(&sql, " WHERE ssub LIKE '%%%q%%'", P("only"));
    style_submenu_element("Show All","%R/subscribers");
  }
  blob_append_sql(&sql," ORDER BY mtime DESC");
  db_prepare_blob(&q, &sql);
  iNow = time(0);
  @ <table border='1' class='sortable' \
  @ data-init-sort='6' data-column-types='tttttKKt'>
  @ <thead>
  @ <tr>
  @ <th>Email
  @ <th>Events
  @ <th>Digest-Only?
  @ <th>User
  @ <th>Verified?
  @ <th>Last change
  @ <th>Last contact
  @ <th>Created
  @ </tr>
  @ </thead><tbody>
  while( db_step(&q)==SQLITE_ROW ){
    sqlite3_int64 iMtime = db_column_int64(&q, 6);
    double rAge = (iNow - iMtime)/86400.0;
    int uid = db_column_int(&q, 8);
    const char *zUname = db_column_text(&q, 3);
    sqlite3_int64 iContact = db_column_int64(&q, 9);
    double rContact = (iNow/86400) - iContact;
    @ <tr>
    @ <td><a href='%R/alerts?sid=%d(db_column_int(&q,0))'>\
    @ %h(db_column_text(&q,1))</a></td>
    @ <td>%h(db_column_text(&q,2))</td>
    @ <td>%s(db_column_int(&q,5)?"digest":"")</td>
    if( uid ){
      @ <td><a href='%R/setup_uedit?id=%d(uid)'>%h(zUname)</a>
    }else{
      @ <td>%h(zUname)</td>
    }
    @ <td>%s(db_column_int(&q,4)?"yes":"pending")</td>
    @ <td data-sortkey='%010llx(iMtime)'>%z(human_readable_age(rAge))</td>
    @ <td data-sortkey='%010llx(iContact)'>%z(human_readable_age(rContact))</td>
    @ <td>%h(db_column_text(&q,7))</td>
    @ </tr>
  }
  @ </tbody></table>
  db_finalize(&q);
  style_table_sorter();
  style_finish_page();