= Database Design
Here will keep the design document and (pseudo) code for the database itself.

== Deprecated
This document was written quite a while ago and the current program at noachide.ca/bible doesn't work this way. I'll leave this document here though as a reference.

== Database Structure
There will be three databases; one for customer data and one for usage data and one of course, for the product data (the NSB). In the following sections we will discuss each database

== Customer Database
We will need a few tables. First is the Customer ID table servicing login.

* row_id
* Customer ID (not Row ID so it does not change if we need to re-row the DB)
* Customer Name (or handle)
* Customer e-Mail (used to login)
* Customer password
* Customer level of access (standard or "pro" user, for example)

With just this information we may allow users to log in and provide a user-friendly experience by calling them by their chosen name. Also we may track their access. Other desirable data would include:

* Alternate Login Credentials (yubikey, face-id, whatever they need, app-based credentials; if they buy app version, they get access via the app as well for a seamless login experience).

=== SQL Code
<code:SQL>
CREATE TABLE clogin (
    id INT NOT NULL AUTO_INCREMENT,  # row_id
    cid INT,                         # customer ID
    email VARCHAR(64),
    rname VARCHAR(64),               # real name (or display name)
    bcrypt CHAR(60),                 # a 60 character password hash using PHP bcrypt.

    memlvl tinyint,                  # membership level (noaccess, guest/pub, user, pro)
    seclvl tinyint,                  # security level (noaccess, user, admin)
    rd DATETIME,                     # registration date
    ll TIMESTAMP,                    # last login
    lli CHAR(50),    # last login from which ip? (ipv6 takes up to 45 chars)
    lk CHAR(60),     # login key, a cookie, for permanent logins. (a bcrypt hash)

    PRIMARY KEY (id)
);
</code>

== Customer Service Issue Tracker
We will need a second table to record customer purchase data and changes made to customer access, so that we have a history and can help customers who have issues with their accounts. This will be a separate table acting like a "user issue tracker" containing at least:

* Customer ID
* CSR ID (who handled the issue)
* Issue Type
* Issue Description
* Resolution Description
* Resolution status codes
* Other notes

This will be designed as a messaging service. So the iid will be like a conversation ID.

<code:SQL>
CREATE TABLE issuetracker (
    id INT NOT NULL AUTO_INCREMENT,  // row_id
    iid INT,                         // issue ID (will be the same acroass multiple messages)
    fid INT,                         // User ID of who wrote the message
    tid INT,                         // User ID of the primary recipient
    msg TEXT,                        // the message, in TEXT format.
    read_flag bit,                   // Has the recipient read the message?
    replied_flag bit,                // Has the message been responded to?
    open_flag bit,                   // Is the issue open?

    PRIMARY KEY (id)
);
</code>

== Usage Data Database
For verse heatmap data we can include a counter in the bible DB for each verse. So other than a generic IP tracking database (left as an exercise for the reader) this will not be a useful table.

== Product Text Database
Note: Instead of a heatmap above we could include this information directly in the product text database and record each time a verse is loaded. We may try this way first since it seems a more viable and streamlined approach.

We're going to need, for every verse, the book, chapter, verse and text string.

However, how will this text string be represented? For example if there are notes, should the notes be marked directly in the text or should notes be kept in a separate table and then marked programmatically?

The simple way seems to have the annotations marked directly in the bible text itself, then have a separate column for commentary.

<code:SQL>
CREATE TABLE bible (
    id INT NOT NULL AUTO_INCREMENT,  // row_id
    trans VARCHAR(8),                // 3 or 4 characters up to 8 (ex NSB or NASB) for the translation
    book VARCHAR(32),                // Book name
    chapter INT,                     // chapter number in book
    verse INT,                       // verse number in chapter
    comment TEXT,                    // verse-by-verse commentary for this verse
    counter INT,                     // verse heatmap counter

    PRIMARY KEY (id)
);
</code>

We don't need to define what the trans column actually means here, we can do that later since there are going to be a limited number of such acronymns. Also this is a prime candidate for normalization later on.

== Usage in PHP
One paradigm I have come to use in PHP is "class per table" which is useful since the table rows themselves represent discrete units of data which are used in the program; therefore "class per table" is helpful from a programming standpoint outside of just using it to access the data. Here's an example, of course, using MeekroDB:

<code:PHP|User.php>
<?php
//
// Member.php
// v0.3
// Manage Members and Aliases for Members.
//
// (C) 2014, 2020 noachide.ca
//

require_once $_SERVER['DOCUMENT_ROOT'] . '/php/cregistry.php';
require_once $_SERVER['DOCUMENT_ROOT'] . '/db/mdb.php';

// Global Function to get user ID of current user (see: security.php)
function getUserID()
{
	if (isset($_SESSION['user']))
	{
		$user = $_SESSION['user'];
		return $user->uid;
	}

	// fallback: not logged in.
	return 0;
}


// From above (also see createTable() below):
// CREATE TABLE members (
//     id INT NOT NULL AUTO_INCREMENT,  // row_id
//     uid INT,                         // user ID
//     email VARCHAR(64),
//     rname VARCHAR(64),               // real name (or display name)
//     bcrypt CHAR(60),                 // a 60 character password hash using PHP bcrypt.
//     memlvl tinyint,                  # membership level (noaccess, guest/pub, user, pro)
//     seclvl tinyint,                  # security level (noaccess, user, admin)
//     rd DATETIME,                     # registration date
//     ll TIMESTAMP,                    # last login
//     lli CHAR(50),    # last login from which ip? (ipv6 takes up to 45 chars)
//     lk CHAR(60),     # login key, a cookie, for permanent logins. (a bcrypt hash)
//
//     PRIMARY KEY (id)
// );

// Class used to encapsulate user.
class User
{
    // property declaration
    public $uid = 0;
    public $email = '';
    public $rname = '';
    public $bcrypt = '';
    public $seclvl = 0;
    public $memlvl = 0;
    public $rd = '';
    public $ll = '';
    public $lli = '';
    public $lk = '';


    // constructor
    function __construct() {
        $this->uid = 0;
        $this->email = '';
        $this->rname = '';
        $this->bcrypt = 0;

        $this->seclvl = 0;
        $this->memlvl = 0;
        $this->rd = '';
        $this->ll = '';
        $this->lli = '';
        $this->lk = '';
    }

    //
    // Check, Create, Drop, Ensure.
    //
    
    // Check: Check if table exists
    public function checkTable() {
        $c = DB::queryFirstField("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema = 'ndb' AND table_name = 'users';");
        return ($c > 0);
    }
    
    // Create table if necesary
    public function createTable() {
		DB::query("CREATE TABLE members ( uid INT NOT NULL AUTO_INCREMENT, uid INT, email VARCHAR(64), rname VARCHAR(64), bcrypt CHAR(60), seclvl TINYINT, memlvl TINYINT, rd DATETIME, ll TIMESTAMP, lli CHAR(45), PRIMARY KEY (uid) );");
    }
    
    // Delete the table when asked
    public function dropTable() {
        DB::query("DROP TABLE members;");
    }
    
    // Make sure the table exists.
    public function ensureTable() {
        if ($this->CheckTable() == false) {
            $this->CreateTable();
        }
    }
    
    // get a blank user object with all fields defined to a default value.
    public function getBlankUser() {
        $a = array();
        $a['uid'] = 0;
        $a['email'] = '';
        $a['rname'] = '';
        $a['bcrypt'] = '';
        $a['seclvl'] = 0;
        $a['memlvl'] = 0;
        $a['rd'] = '';
        $a['ll'] = '';
        $a['lli'] = '';

        return $a;
    }


    // Write user data into database.
    // NOTE: Will auto-assign and return a username if called with TRUE.
    public function writeOut($newuser = false) {
        $a = array();
        if ($newuser == false) {
            $a['uid'] = $this->uid;
        } else {
            unset($a['uid']);	// unset it.

            // check if e-mail exists.
            $exists = DB::queryFirstField("SELECT count(*) FROM members WHERE email=%s", $this->email);
            if ($exists > 0) {
                $this->uid = 0;
                return 0;
            }

            // new user setup:
            $this->seclvl = 1;
            $this->memlvl = 1;
        }
        $a['email'] = $this->email;
        $a['rname'] = $this->rname;
        $a['bcrypt'] = $this->bcrypt;
        $a['seclvl'] = $this->seclvl;
        $a['memlvl'] = $this->memlvl;
        $a['rd'] = $this->rd;
        $a['ll'] = $this->ll;
        $a['lli'] = $this->lli;
        $a['lk'] = $this->lk;

		
        $r = DB::insertUpdate("members", $a);
        if ($newuser) {
            $this->uid = DB::insertId();
        }
		
        return $this->uid;
    }
	
    public function loadByUID($uid = 0) {
        if ($uid == 0) {
            $user = new User();
            return $user;
        }

        $account = DB::queryFirstRow("SELECT * FROM members WHERE uid=%i", $uid);

        $this->uid = $account['uid'];
        $this->rname = $account['username'];
	$this->bcrypt = $account['password'];
	$this->email = $account['email'];
        $this->seclvl = $account['seclvl'];
        $this->memlvl = $account['memlvl'];
        $this->rd = $account['rd'];
        $this->ll = $account['ll'];
        $this->lli = $account['lli'];
        $this->lk = $account['lk'];

        return $account;
    }

    // Pull user data by rname
    public function loadByRname($rn = "") {
        if (strlen($rn) == 0) {
            return false;
        }
    
        $uid = DB::queryFirstField("SELECT uid FROM members WHERE rname=%s", $rn);
        if ($uid <= 0) {
            return false;
        }
		
    return $this->loadByUID($uid);
    }

    public function loadFromSession() {
        if (isset($_SESSION['user'])) {
            $user = $_SESSION['user'];
			
            if ($user->uid > 0) {
                $this->loadByUID($user->uid);
            }
        }

        return $this->uid;
    }

    // test to see if the member can access content with a specific security level.
    // default is security level 0 meaning that they have at least registered.
    // Success: Returns TRUE
    // Failure: Returns FALSE
    public function securityCheck($security_level = 0) {
        if ($this->seclvl >= $security_level) {
            return TRUE;
        }
        return false;
    }

    public function membershipCheck($membership_level = 0) {
        if ($this->memlvl >= $membership_level) {
            return TRUE;
        }
        return false;
    }

    // Redirect them to the homepage if they're not logged in.
    function loginCheck($address="http://noachide.ca") {
        if ($this->uid == 0) {
            eredirect($address);
        }
    }


    public function getSLinfo($sl = 0) {
        $rdata = "";

        if ($sl == 0) {
            $sl = $this->seclvl;
        }

        switch ($sl) {
        case 0:
            $rdata = "noaccess";
            break;
        case 1:
            $rdata = "public access";
            break;
        case 2:
            $rdata = "security access";
            break;
        case 3:
            $rdata = "admin access";
            break;
        default:
           $rdata = "unknown (" . $sl . ")";
            break;
        }
        
        return $rdata;
    }



    public function getMLinfo($ml = 0) {
        $rdata = "";

        if ($ml == 0) {
            $ml = $this->memlvl;
        }
        
        switch ($ml) {
        case 0:
            $rdata = "noaccess";
            break;
        case 1:
           $rdata = "public access";
           break;
        case 2:
           $rdata = "special access";
           break;
        case 3:
           $rdata = "membership access";
           break;
        default:
           $rdata = "unknown (" . $ml . ")";
           break;
        }

        return $rdata;
    }

    function touchLastLog() {
      if ($this->uid == 0) {
          return FALSE;
      }

      $datetime = DB::queryFirstField("select now()");
      $rdata = $this->setData("ll", $datetime);
      return($rdata);
    }

    function touchLastLogi() {
        $ip = $this->getIP();
        $rdata = $this->setData("lli", $ip);
        return($rdata);
    }

    // Set something in the user data
    function setData($item, $newdata) {
        if ($this->uid == 0) {
            return 0;
        }

        DB::update('members', array(
            $item => $newdata
        ), "uid=%i", $this->uid);

	return(DB::affectedRows());
    }

    // Retrieve some part of user data.
    function getData($item) {
        if ($this->uid == 0) {
            return 0;
        }

        $row = DB::queryFirstRow('SELECT * from members where uid=%i', $this->uid);

        $r = NULL;
        if (isset($row[$item])) {
            $r = $row[$item];
        }

        return $r;
    }

    function getIP() {
        $this->lli = $_SERVER['REMOTE_ADDR'];
        
        /*
        if (getenv('HTTP_CLIENT_IP')) {
            $this->lli =getenv('HTTP_CLIENT_IP');
        } elseif (getenv('HTTP_X_FORWARDED_FOR')) {
            $this->lli =getenv('HTTP_X_FORWARDED_FOR');
        } elseif (getenv('HTTP_X_FORWARDED')) {
            $this->lli =getenv('HTTP_X_FORWARDED');
        } elseif (getenv('HTTP_FORWARDED_FOR')) {
            $this->lli =getenv('HTTP_FORWARDED_FOR');
        } elseif (getenv('HTTP_FORWARDED')) {
            $this->lli = getenv('HTTP_FORWARDED');
        } else {
            $this->lli = $_SERVER['REMOTE_ADDR'];
        }
        */

        return $this->lli;
    }

    // Call with a plaintext password. This will encrypt the plaintext and set it in memory
    // but will not commit the data to the db.
    function setpw($p) {
        $this->password = password_hash($p, PASSWORD_DEFAULT, ['cost' => 16 ]);
    }

    // returns true if password matches $p.
    function checkpw($p) {
        // reload user info to make sure we have the right password.
        if ($this->uid > 0) {
            $this->password = DB::queryFirstField("SELECT password FROM members WHERE uid=%d", $this->uid);
        }

        $hashtest = password_hash($p, PASSWORD_DEFAULT, ['cost' => 16 ] ); 

        $test = strcmp($this->password, $hashtest);
            if ($test == 0) {
                return TRUE;
            }

        return FALSE;
    }

    // create and set a login key.
    // returns: the key which was set.
    function set_lk() {
        $m = "logged in as uid " . $this->uid . " at " . time();
        $m = password_hash($m, PASSWORD_DEFAULT, ['cost' => 16 ]);
        DB::update("members", array("lk" => $m), "uid=%i", $this->uid);
            return $m;
        }
        
    function clear_lk() {
        DB::update("members", array("lk" => "logged out"), "uid=%i", $this->uid);
    }

    function is_logged_in() {
        if($this->uid > 0) {
            return true;
        }
        return false;
    }
}
?>
</code>

The above has not been tested but has been taken from an existing web app I wrote and scaled down/repurposed a bit for use on a new app (this app). The implementation may change as it is written.

As an example I think it serves the purpose well. All the basic interactivity for the user data is contained in the object itself. In much the same way, a bible verse will have functions that save and load themselves in the dabatabse, can search for verses in various ways, and so forth.

After that, the planned minimal UI may be written and the database may be populated.