do

#!/bin/gawk -f

# A webapp for managing Taxon × Character data with a MySQL DB
# (c) Cam Webb <cw@camwebb.info>, 2019; License: GPL v3

# INSTALLATION
#
# 1. If using Apache httpd, install this file in a cgi executable
#    location and create a .htaccess file, minimally: 
#        Options +ExecCGI -Indexes
#        <Files "do">
#          SetHandler cgi-script
#        </Files>
#        DirectoryIndex do
#    Optionally add:
#        <FilesMatch "^\.pw$">
#          Deny from all
#        </FilesMatch>
#        AuthType Basic
#        AuthName "Password Protected"
#        AuthUserFile /full/path/to/.pw
#        Require valid-user
#
# 2. Install function libraries from https://github.com/camwebb/awk-lib
#    and include, using full path

@include "/usr/local/share/awk/db_sql.awk"
@include "/usr/local/share/awk/util_http.awk"
@include "/usr/local/share/awk/db_secrets.awk"

# 3. On and available MySQL or MariaDB DB, install the empty DB at
#    http://alaskaflora.org/chars/chars.sql and modify as needed. Make sure
#    the access info is in the included db_secrets.awk


BEGIN{
  PROCINFO["sorted_in"] = "@ind_str_asc"
  
  db_secrets()
 
  # READ QUERY STRING
  split(ENVIRON["QUERY_STRING"], qs, "&");
  for (i in qs) {
    split(qs[i], qp, "=");
    f[qp[1]] = substr(urldecode(qp[2]),1,2000); 
  }
  
  # DETERMINE AND EXECUTE METHOD
  if           (f["page"] == "matrix")  matrix()
  else if      (f["page"] == "cell")    cell()
  else if      (f["page"] == "tnote")   taxonnote()
  else if      (f["page"] == "copy")    copy()
  else defaultpage() ;
  
}


function matrix(   ntaxa, charID, taxaID, haschar, charx, name,  \
                   i, j, s, out, gen, warningtext, guid) {

  header("Data matrix")

  print "<p style=\"text-align:right;font-weight:bold\"><a href=\"do\">Home</a></p>"

  
  # Table header
  db_query("akchars", "SELECT `char`, `state`, `id` FROM chars \
            ORDER BY `char`, `state`;")
  for (i = 1; i <= DBQr; i++) {
    charID[i] = DBQ[i, "id"]
    char[i] = DBQ[i, "char"]
    state[i] = DBQ[i, "state"]
    charx[char[i]][state[i]] = DBQ[i, "id"]
  }

  print "<table>"
  print "<tr>"
  print "<td>Taxon</td>"
  print "<td>Syn-<br/>onyms</td>"
  print "<td>ID</td>"
  for (i in charx) 
    print "<td><b>" i "</b></td>"
  print "<td>taxon<br/>notes</td>"
  print "<td>fill</td>"
  print "</tr>"
  db_clear()

  # Taxon names
  db_query("akchars", "SELECT `name`, `guid`, `id`, `genID`, `syn`, `tnote` FROM \
            `taxa` WHERE `name` LIKE '" f["genus"] "%'ORDER BY `name`;")
  ntaxa = DBQr
  for (i = 1; i <= DBQr; i++) {
    name[i] = DBQ[i, "name"]
    guid[i] = DBQ[i, "guid"]
    taxaID[i] = DBQ[i, "id"]
    syn[i] = DBQ[i, "syn"]
    tnote[i] = DBQ[i, "tnote"]
    if (DBQ[i, "genID"]) gen[taxaID[i]] = DBQ[i, "genID"]
  }
  db_clear()

  # Table data
  db_query("akchars", "SELECT `taxaID`, `charID` FROM `TxC`;")
  for (i = 1 ; i <= DBQr; i++)
    haschar[DBQ[i, "taxaID"], DBQ[i, "charID"]] = 1
  db_clear()
  
  for (i = 1 ; i <= ntaxa; i++) {
    print "<tr>"
    print "<td><b>" name[i] "</b></td>"
    print "<td class=\"syn1\"><div class=\"syn2\">"gensub(/.+/,"...","G",syn[i])"</div><div class=\"syn3\">" syn[i] "</div></td>"
    print "<td>" guid[i] "</td>"
    for (j in charx) {
      print "<td onclick=\"location.href='do?page=cell&amp;char=" j \
                  "&amp;taxaID=" taxaID[i]"&amp;genus=" f["genus"] "';\">"
      out = ""
      sn = 0
      for (s in charx[j]) {
        # test for char at genus level
        if (haschar[gen[taxaID[i]], charx[j][s]]) out = out \
               "<span style=\"color:blue;\">" s "</span>, "
        if (haschar[taxaID[i], charx[j][s]]) {
          out = out s ", "
          sn++
        }
        if (!gen[taxaID[i]] && (sn > 1))
          warningtext = warningtext "<p style=\"color:red;\">WARNING: genus " name[i] " has >1 states for '" j "'</p>"
      }
      gsub(/,\ $/,"",out)
      if (!out) out = "&#160;"
      print out
      print "</td>"
    }
    print "<td onclick=\"location.href='do?page=tnote&amp;taxaID="   \
      taxaID[i] "&amp;genus=" f["genus"] "';\" class=\"syn1\"><div class=\"syn2\">"gensub(/.+/,"...","G",tnote[i])"</div><div class=\"syn3\">" tnote[i] "</div></td>"
    print "<td onclick=\"location.href='do?page=copy&amp;taxaID=" \
      taxaID[i]"&amp;genus=" f["genus"] "';\"/>"
    print "</tr>"
  }

  print "</table>"

  if (warningtext) print warningtext

  footer()

  exit 0
}

# The Cell editing page
function cell(   name, actiontext, ES, i,s ,srcfail) {

  # If reentering into this page after an action
  if (f["preaction"] == "delete") {
    ES = db_sql("akchars", "DELETE FROM `TxC` WHERE `id` = " f["charID"] ";")
    if (ES) actiontext = "<p style=\"color:red;\">UPDATE FAILURE</p>"
    # else actiontext = "<p>Status: character state datum " f["charID"] \
    #       " deleted</p>"
    if (!actiontext) matrix()
  }
  else if (f["preaction"] == "new") {
    # first test for accepable sources
    split(gensub(/\ /,"","G",f["sources"]),s,",")
    for (i in s)
      if (s[i] !~ /^(and|cody|dall|fna|hult|jep|judd|welsh|walt|inat|ASC|oth|apw)$/) srcfail = 1
    if (srcfail) actiontext =                                       \
      "<p style=\"color:red;\">INCORRECT SOURCE ABBREVIATION</p>"
    else if (!f["sources"]) actiontext =                                     \
        "<p style=\"color:red;\">AT LEAST ONE SOURCE NEEDED</p>"
    else if ((f["sources"] ~ /(oth|inat)/) && !f["notes"])  actiontext =  \
        "<p style=\"color:red;\">IF 'oth' or 'inat' THEN SOURCE INFO NEEDED</p>"
    else {
      gsub(/'/,"\\'",f["notes"])
      gsub(/"/,"\\\"",f["notes"])
      ES = db_sql("akchars", "INSERT INTO `TxC` \
                  (`taxaID`, `charID`, `person`, `sources`, `notes`) \
                 VALUES (" f["taxaID"] "," f["charID"]", '" \
                  f["person"] "','" f["sources"] "','" f["notes"] "');")
      if (ES) actiontext = "<p style=\"color:red;\">INSERT FAILURE</p>"
      # else actiontext = "<p>Status: character state datum " f["charID"]
      # " added</p>"

      if (!actiontext) matrix()

    }
  }

  header("Data cell")

  print "<p style=\"position:absolute;right:30px;top:15px;font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" f["genus"] \
    "\">Matrix</a></p>" 
  
  # Taxon names
  db_query("akchars", "SELECT `name`, `guid`, `genID` FROM \
            `taxa` WHERE `id` = " f["taxaID"] ";")
  name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
  db_clear()

  # top-level table
  print "<table style=\"border:none;\"><tr><td style=\"border:none;vertical-align:top;width:400px;\">"
  print "<table><tr><td><i>Taxon</i></td><td>" name "</td></tr>"
  print "<tr><td><i>Character</i></td><td><b>" f["char"] \
    "</b></td></tr></table>"

  db_query("akchars", "SELECT `state`, `timestamp`, `person`, `sources`, \
           `TxC`.`id`, `TxC`.`notes` FROM `TxC`, \
            `chars` WHERE `taxaID` = " f["taxaID"] " AND `chars`.`id` = \
            `TxC`.`charID` AND `chars`.`char` = '" f["char"] "';")

  print "<div style=\"padding:15px;\">"

  for (i = 1; i <= DBQr; i++) {
    print "<b>" DBQ[i, "state"] "</b>"
    print "&#160;&#160;&#160;<span style=\"font-size: 80%;\">(<a href=\"do?page=cell&amp;char=" urlencode(f["char"]) "&amp;taxaID=" f["taxaID"] "&amp;genus=" f["genus"] "&amp;preaction=delete&amp;charID=" DBQ[i,"id"] "\">DELETE</a>)</span>"

    print "<div style=\"padding:15px;\">"
    print "<table>"
    print "<tr><td><i>Sources</i></td><td style=\"width:400px;\">" DBQ[i, "sources"] "</td></tr>"
    print "<tr><td><i>Notes</i></td><td>" DBQ[i, "notes"] "</td></tr>"
    print "<tr><td><i>By</i></td><td>" DBQ[i, "person"] "</td></tr>"
    print "<tr><td><i>Date</i></td><td>" gensub(/^([^ ]+).*/,"\\1","G", \
             DBQ[i, "timestamp"]) "</td></tr>"
    print "</table>"
    print "</div>"
  }
  print "</div>"
  db_clear()

  print "</td><td style=\"border:none;vertical-align:top;\">"
  
  print "<form action=\"do\"><p>"
  print "<input type=\"hidden\" name=\"page\" value=\"cell\"/>"
  print "<input type=\"hidden\" name=\"preaction\" value=\"new\"/>"
  print "<input type=\"hidden\" name=\"char\" value=\"" f["char"] "\"/>"
  print "<input type=\"hidden\" name=\"taxaID\" value=\"" f["taxaID"] "\"/>"
  print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"
  print "Add new character state:</p>"
  print "<div style=\"padding:15px;\">"
  print "<select name=\"charID\" style=\"font-weight:bold;font-size:1em;\">"
  db_query("akchars", "SELECT `state`, `id` FROM `chars` WHERE `char` = \
                       '" f["char"] "';")
  for (i = 1; i <= DBQr; i++)
    print "<option value=\"" DBQ[i, "id"] "\">" DBQ[i, "state"] "</option>"
  db_clear()
  print "</select>"
  print "&#160;&#160;&#160;(<input type=\"submit\" value=\"GO\"/>)"

  print "<div style=\"padding:15px;\">"
  print "<table>"
  print "<tr><td><i>Sources*</i></td>"
  print "<td><input type=\"text\" name=\"sources\" style=\"width:300px;\"/></td></tr>"
  print "<tr><td><i>Notes</i></td>"
  print "<td><textarea name=\"notes\" cols=\"40\" rows=\"4\"></textarea></td></tr>"
  print "<tr><td><i>By</i></td>"
  print "<td><select name=\"person\">"
  print "<option value=\"Brian\">Brian</option>"
  print "<option value=\"Cam\">Cam</option>"
  print "<option value=\"Steffi\">Steffi</option>"
  print "</select></td></tr>"
  print "</table><br/>(* Allowed: <code>and, apw, cody, dall, fna, hult, jep, judd,<br/>walt, welsh, <i>inat</i>, <i>oth</i>, <b>ASC</b></code>;  <i>inat</i> and <i>oth</i> require notes)</div>"

  print "</div></form></td></tr></table>"
  

  if (actiontext) print actiontext
  
  footer()

  exit 0
}

function copy(   name, i, actiontext, sql, ES){

  header("Data copy")

  print "<p style=\"position:absolute; right:30px; top:15px; \
            font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" \
            f["genus"] "\">Matrix</a></p>" 

  if (f["preaction"] == "copy") {
    ES = db_sql("akchars", "DELETE FROM `TxC` WHERE `taxaID` = "    \
                f["totaxaID"] ";")
    if (ES) actiontext = "<p style=\"color:red;\">DELETE FAILURE</p>"
    db_clear()
    
    db_query("akchars", "SELECT `charID`,`sources`,`person`, `taxa`.`name`,\
             `taxa`.`guid` FROM `TxC`,`taxa` WHERE `taxa`.`id` = \
             `TxC`.`taxaID` AND `taxaID` = " f["fromtaxaID"] ";")
    for (i = 1; i <= DBQr; i++) {
      sql = "INSERT INTO `TxC` (`taxaID`, `charID`, `person`, `sources`, \
             `notes`) VALUES (" f["totaxaID"] "," DBQ[i, "charID"] ", '" \
        DBQ[i, "person"] "','" DBQ[i, "sources"] \
        "', 'Data copied from " DBQ[i,"name"] " (" DBQ[i,"guid"] ")'); "
      ES = db_sql("akchars", sql)
      if (ES) actiontext = actiontext "<p style=\"color:red;\">FAILED: '" \
                sql "'</p>"
    }
    db_clear()
    
    if (!actiontext) print "<p>Successfully copied</p>"
  }
  else {

    # Taxon names
    db_query("akchars", "SELECT `name`, `guid`, `genID` FROM \
            `taxa` WHERE `id` = " f["taxaID"] ";")
    name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
    db_clear()

    print "<form action=\"do\"><p>"
    print "<input type=\"hidden\" name=\"page\" value=\"copy\"/>"
    print "<input type=\"hidden\" name=\"preaction\" value=\"copy\"/>"
    print "<input type=\"hidden\" name=\"totaxaID\" value=\"" f["taxaID"] "\"/>"
    print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"

    print "Copy data <i>into</i> " name " from "
    print "<select name=\"fromtaxaID\">"
    db_query("akchars", "SELECT `name`, `guid`, `id` FROM \
            `taxa` WHERE `genID` IS NOT NULL AND `id` != " f["taxaID"] \
             " AND `name` LIKE '" f["genus"] "%' ORDER BY `name`;")
    for (i = 1; i <= DBQr; i++)
      print "<option value=\"" DBQ[i, "id"] "\">" DBQ[i, "name"] "</option>"
    db_clear()
    print "</select>"
    print "<input type=\"submit\" value=\"GO\"/></p>"
    print "</form>"
    print "<p>(<i style=\"color:red;\">All exiting data will be "\
      "overwritten</i>)</p>"
  }

  if (actiontext) print actiontext
  
  footer()

  exit 0
}

function taxonnote(   name, i, actiontext, ES){

  header("Data copy")

  print "<p style=\"position:absolute; right:30px; top:15px; \
            font-weight:bold;\"><a href=\"do?page=matrix&amp;genus=" \
            f["genus"] "\">Matrix</a></p>" 

  if (f["preaction"] == "tnote") {
    gsub(/'/,"\\'",f["tnote"])
    gsub(/"/,"\\\"",f["tnote"])
    ES = db_sql("akchars", "UPDATE `taxa` SET `tnote` = '" f["tnote"] "' , `tnoteauth` = '" f["tnoteauth"] "' WHERE id = " f["taxaID"] ";")
    if (ES) actiontext = actiontext "<p style=\"color:red;\">FAILED</p>"
    else actiontext = actiontext "<p style=\"color:green;\">Success</p>"
  }

  # Taxon names
  db_query("akchars", "SELECT `name`, `guid`, `tnote`, `tnoteauth` FROM \
            `taxa` WHERE `id` = " f["taxaID"] ";")
  name = "<b>" DBQ[1,"name"] "</b> (" DBQ[1,"guid"] ")"
  
  print "<form action=\"do\"><p>"
  print "<input type=\"hidden\" name=\"page\" value=\"tnote\"/>"
  print "<input type=\"hidden\" name=\"preaction\" value=\"tnote\"/>"
  print "<input type=\"hidden\" name=\"taxaID\" value=\"" f["taxaID"] "\"/>"
  print "<input type=\"hidden\" name=\"genus\" value=\"" f["genus"] "\"/>"
    
  print "Make a note about " name "</p>"
  # print "<p>(Currently: '" DBQ[1, "tnote"] "')</p>"
  print "<p><textarea name=\"tnote\" cols=\"60\" rows=\"10\">"
  print DBQ[1, "tnote"]
  print "</textarea> (" DBQ[1, "tnoteauth"] ")</p>"
  print "<p>By: "
  print "<select name=\"tnoteauth\">"
  print "<option value=\"Brian\">Brian</option>"
  print "<option value=\"Cam\">Cam</option>"
  print "<option value=\"Steffi\">Steffi</option>"
  print "</select></p>"
  print "<p><input type=\"submit\" value=\"GO\"/></p>"
  print "</form>"
  print "<p>(<i style=\"color:red;\">Note: exiting entry will be "  \
    "overwritten</i>)</p>"
  db_clear()

  if (actiontext) print actiontext
  
  footer()

  exit 0
}


function header(title) {
  
  # Use html5
  print "Content-type: text/html\n"
  print "<!DOCTYPE html>"
  print "<html xmlns=\"http://www.w3.org/1999/xhtml\">"
  print "<head><title>" title "</title>"
  print "<meta http-equiv=\"Content-Type\" content=\"text/html; \
           charset=utf-8\" />"
  print "<link href=\"https://fonts.googleapis.com/css?family=Montserrat\" \
           rel=\"stylesheet\"/>"
  print "<link href=\"../img/akflora.ico\" rel=\"shortcut icon\" \
           type=\"image/x-icon\"/>"
  print "<style>"
  print "body { font-size: 14px; width: 1200px;	font-family: 'Montserrat', \
         Verdana, Arial, Helvetica, sans-serif; padding: 20px; \
         position: absolute }"
  print "select , option { font-size: 14px }"
  print "table { max-width: 1200px; border-collapse: collapse }"
  print "td, th { border: 1px solid black; padding: 3px }"
  print "a { color:#15358d; text-decoration:none; border-bottom-style:none }"
  print "a:visited { color:#9f1dbc }"
  print "a:hover {color:#15358d; border-bottom-style:solid; \
	     border-bottom-width:thin }"
  print ".syn1:hover .syn2 {display: none;}"
  print ".syn1:hover .syn3 {display: block;}"
  print ".syn3 {display: none;}"
  print "</style>"
  print "</head>\n<body>"
}

function defaultpage(   i)
{
  header("Character entry webapp");

  print "<h1>Character entry webapp</h1>"

  print "<table style=\"border:none;\"><tr><td style=\"border:none;"\
    "vertical-align:top;padding:20px;width:500px\">"

  print "<p>Select genus:</p>"
  print "<table>"
  print "<tr><th>Family</th><th>Genus</th><th>N taxa</th><th>% done</th></tr>"

  # get taxa
  db_query("akchars", "SELECT * FROM taxa ORDER BY `fam`, `name`;")
  for (i = 1; i <= DBQr; i++) {
    if (!DBQ[i,"genID"]) {
      fam[++g] = DBQ[i,"fam"]
      name[g] = DBQ[i,"name"]
      gid[g] = DBQ[i,"id"]
    }
    else
      glist[DBQ[i,"genID"]][DBQ[i,"id"]] = 1
  }
  db_clear()

  # get states
  db_query("akchars", "SELECT * FROM `chars`;")
  for (i = 1; i <= DBQr; i++) {
    char[DBQ[i,"id"]] = DBQ[i,"char"]
    charx[DBQ[i,"char"]] = 1
  }    
  db_clear()
  
  # count the total number of chars (not states)
  for (i in charx) nchars++
  # fill a taxa x chars matrix
  db_query("akchars", "SELECT * FROM TxC;")
  for (i = 1; i <= DBQr; i++) {
    chard[DBQ[i,"taxaID"], char[DBQ[i,"charID"]]] = 1
  }
  db_clear()

  # now, for each genus
  for (n = 1; n <= g; n++) {
    filled = ntaxa = pcdone = 0
    # for each taxon in genus
    if (isarray(glist[gid[n]])) {
      for (i in glist[gid[n]]) {
        ntaxa++
        # for each char
        for (j in charx)
          if (chard[i, j] || chard[gid[n], j]) filled++
      }
      pcdone = sprintf("%.2f", filled / (ntaxa * nchars))
    }
    
    if (pcdone == "0.00") pcdone = ""
    else if (pcdone == "1.00") pcdone = "<span style=\"color:green;\">" \
                                 pcdone "</span>"

    if (ntaxa && fam[n] !~ /^(Cyperaceae|Lycopodiaceae|Pinaceae|Poaceae|\
          Woodsiaceae|Dryopteridaceae|Cupressaceae|Aspleniaceae)$/) 
      print "<tr><td>" fam[n] "</td><td><a href=\"do?page=matrix&amp;genus=" \
        name[n] "\">" name[n] "</a></td><td>" ntaxa "</td><td>" pcdone \
        "</td></tr>"
  }
  
  # print "<tr><th>Family</th><th>Genus</th><th>N taxa<br/>scored</th><th>N char<br/>scored</th></tr>"
  # db_query("akchars", "SELECT `fam`, `taxa`.`name`, COUNT(`b`.`genID`) \
  #   AS `ct`, SUM(`c`) AS `sc` FROM `taxa` LEFT JOIN (SELECT `genID`, \
  #   COUNT(`charID`) AS `c` \
  #   FROM `taxa`, (SELECT `taxaID`, `charID` FROM `TxC`) AS `a` WHERE \
  #   `a`.`taxaID` = `taxa`.`id` AND `genID` IS NOT NULL GROUP BY `name`) \
  #   AS `b` ON `taxa`.`id` = `b`.`genID` WHERE `taxa`.`genID` IS NULL \
  #   GROUP BY `taxa`.`id` ORDER BY `fam`, `name`;")
  # for (i = 1; i <= DBQr; i++)
  #   print "<tr><td>" DBQ[i,"fam"] "</td><td><a href=\"do?page=matrix&amp;genus=" DBQ[i, "name"] "\">" \
  #     DBQ[i, "name"] "</a></td><td>" gensub(/0/,"","G", DBQ[i,"ct"]) "</td><td>" DBQ[i,"sc"] "</td></tr>"
  # db_clear()

  
  print "</table>"

  print "</td><td style=\"border:none;vertical-align:top;padding:20px;\">"
  
  print "<p>Recent activity:</p>"
  print "<table>"
  print "<tr><th>Family</th><th>Taxon</th><th>Char</th><th>State</th><th>Date</th></tr>"
  db_query("akchars", "select fam, taxa.name, `char`, `state`, \
           date(`timestamp`) as dt from TxC, taxa, chars where TxC.charID = \
           `chars`.id and TxC.taxaID = taxa.id order by `timestamp` \
           desc limit 10;")
  for (i = 1; i <= DBQr; i++)
    print "<tr><td>" DBQ[i,"fam"] "</td><td>" DBQ[i, "name"] "</td><td>" DBQ[i,"char"] "</td><td>" DBQ[i,"state"]  "</td><td>" DBQ[i,"dt"] "</td></tr>"
  db_clear()
  print "</table>"

  print "</td></tr></table>"
  
  footer()
}

function footer()
{
  print "</body>\n</html>";
}