path: root/common/db.php

<?php
namespace mcoop;
require_once("config_cls.php");
require_once("vendor/busybee/urljoin/src/urljoin.php");
require_once("db_classes.php");
require_once("tables/members.php");
require_once("tables/dividend_credits.php");
require_once("tables/tasks.php");
require_once("tables/task_claims.php");
require_once("tables/task_dividend_credits.php");
require_once("tables/tcc_history.php");

// TODO: move this into the config object, or auto add to it in each decl file
$table_decls = array(
	"members" => $members_table_decl,
	"dividend_credits" => $dividend_credits_table_decl,
	"tasks" => $tasks_table_decl,
	"task_claims" => $task_claims_table_decl,
	"task_dividend_credits" => $task_dividend_credits_table_decl,
	"tcc_history" => $tcc_history_table_decl
);

class UnknownMember extends \Exception {
	public $searched = null;
	public $value = null;

	function __construct($searched, $value) {
		$this->searched = $searched;
		$this->value = $value;

		$message = "Unable to find a member with $searched = $value";
		parent::__construct($message);
	}
}

class RegistrationError extends \Exception {
	public $invalid_part = null;
	public $reason = null;

	function __construct($invalid_part, $reason) {
		$this->invalid_part = $invalid_part;
		$this->reason = $reason;

		$message = "Registration Error ($invalid_part): $reason";
		parent::__construct($message);
	}
}

class LoginError extends \Exception {}

class Database {
	public $config = null;
	public $conn = null;
	public $target_tableversions = array(
		"unexpected_errors" => 1,
	);
	public $statements = array();
	public $upgraders = array();
	public $table_decls = array();

	function __construct($config) {
		$this->config = $config;
		global $table_decls;
		$this->table_decls = $table_decls;
		$this->connect();
	}

	function connect() {
		// XXX/TODO: the exceptions from this need to be caught, see the warning on http://php.net/manual/en/pdo.connections.php
		$this->conn = new \PDO(
			$this->config->pdo_connstring,
			$this->config->db_username,
			$this->config->db_password,
			array(\PDO::ATTR_PERSISTENT => true)
		);
		$this->create_tableversions_table();
		$this->create_unexpected_errors_table();
		// TODO: make table creation optional for everything except tableversions and unexpected_errors
		// (you can change the $create argument to setup_table_decls to false for that now)
		$this->setup_table_decls();
		$this->auto_upgrade_all();
		//$this->create_members_table();
		//$this->upgraders["members"] = new MembersUpgrader($this->conn);
		//$this->auto_upgrade_all();
		//var_dump($this->conn);
	}

	function setup_table_decls($create=true) {
		$conn = $this->conn;
		foreach ($this->table_decls as $tname => $td) {
			if ($create) {
				$td->create_table($this);
			}
			$td->set_statements($this);
			$clsname = $td->upgrader_classname;
			$this->upgraders[$tname] = new $clsname($conn);
		}
	}

	function get_cur_table_version($table_name) {
		$this->statements["get_tableversion"]->execute(array(":name" => $table_name));
		$x = $this->statements["get_tableversion"]->fetchAll(\PDO::FETCH_COLUMN);
		$ver = (int)$x[0];
		return $ver;
	}

	function auto_upgrade($upgrader) {
		// This shouldn't be called until at the very least after create_tableversions_table, since it adds set_tableversion to statements
		$table_name = $upgrader->table_name;
		$old_ver = $this->get_cur_table_version($table_name);
		$new_ver = $this->table_decls[$table_name]->newest_tableversion;
		if ($old_ver < $new_ver) {
			error_log("upgrading $table_name from $old_ver to $new_ver");
			// XXX/TODO: this is assuming it doesn't error, going to get really ugly if it does
			$upgraded_to = $upgrader->upgrade($old_ver, $new_ver);
			$this->statements["set_tableversion"]->execute(array(":table_name" => $table_name, ":version" => $upgraded_to));
			//print_r($upgraded_to);
			return $upgraded_to;
		}
	}

	function auto_upgrade_all() {
		foreach ($this->upgraders as $table_name => $upgrader) {
			$this->auto_upgrade($upgrader);
		}
	}

	function create_tableversions_table() {
		$conn = $this->conn;
		$conn->exec("CREATE TABLE IF NOT EXISTS `table_versions` (`name` TEXT CHARACTER SET utf8 NOT NULL, `version` INT, PRIMARY KEY (`name`(1024)));");
		$this->statements["set_tableversion"] = $conn->prepare("INSERT INTO table_versions VALUES (:table_name, :version) on duplicate key UPDATE version=:version");
		$this->statements["get_tableversion"] = $conn->prepare("SELECT version FROM table_versions WHERE name = :name");
	}

	function create_unexpected_errors_table() {
		// TODO: this table needs a datetime field so you know when the error happened
		$conn = $this->conn;
		$conn->exec("CREATE TABLE `unexpected_errors` (`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, `action` TEXT CHARACTER SET utf8 NOT NULL, `error_sql_code` CHAR(5) CHARACTER SET ascii NOT NULL, `error_server_code` INT, `error_server_description` TEXT CHARACTER SET utf8);");
		$err_info = $conn->errorInfo();
		//var_dump($err_info);
		if ($err_info[0] == "00000") {
			$this->statements["set_tableversion"]->execute(array(
				":table_name" => "unexpected_errors",
				":version" => $this->target_tableversions["unexpected_errors"]
			));
		}
		$this->statements["insert_unexpected_error"] = $conn->prepare("INSERT INTO unexpected_errors (action, error_sql_code, error_server_code, error_server_description) VALUES (:action, :sql_code, :server_code, :server_description)");
	}

	function create_members_table() {
		$conn = $this->conn;
		$conn->exec(
		"CREATE TABLE `members` (
`userid` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`username` TEXT CHARACTER SET utf8 NOT NULL,
`email` TEXT CHARACTER SET utf8 NOT NULL,
`last_updated_table_version` INT NOT NULL,
`full_name` TEXT CHARACTER SET utf8,
`validation_code` CHAR(64) CHARACTER SET ascii,
`validated` BOOL DEFAULT false,
`full_member` BOOL DEFAULT false,
`argon2_password_hash` VARCHAR(256) CHARACTER SET ascii,
`reset_password_hash` VARCHAR(256) CHARACTER SET ascii,
`reset_requested` BOOL DEFAULT false,
UNIQUE KEY (`username`(256)),
UNIQUE KEY (`email`(256))
);");
		$err_info = $conn->errorInfo();
		// the SQL errorcode for a table already existing seems to be 42S01
		// but I think we don't need to check for it specifically here,
		// just make sure there was no error
		//print_r($err_info);
		//var_dump($err_info);
		if ($err_info[0] == "00000") {
			$this->statements["set_tableversion"]->execute(array(
				":table_name" => "members",
				":version" => $this->target_tableversions["members"]
			));
		} else if ($err_info[0] != "42S01") {
			$this->statements["insert_unexpected_error"]->execute(array(
				":action" => "create_members_table",
				":sql_code" => $err_info[0],
				":server_code" => $err_info[1],
				":server_description" => $err_info[2]
			));
		}
		$this->statements["register_member"] = $conn->prepare("INSERT INTO members (username, email, last_updated_table_version, full_name, argon2_password_hash) VALUES (:username, :email, :version, :full_name, :argon2_phash)");
		$this->statements["get_member_passhash_by_username"] = $conn->prepare("SELECT argon2_password_hash FROM members WHERE username = :username");
		$this->statements["get_member_by_username"] = $conn->prepare("SELECT * FROM members WHERE username = ?");
		$this->statements["get_member_by_email"] = $conn->prepare("SELECT * FROM members WHERE email = ?");
		$this->statements["get_members_by_uname_or_email"] = $conn->prepare("SELECT * FROM members WHERE username = :username OR email = :email");
	}

	function validate_username($username) {
		if (!preg_match("/^[a-zA-Z0-9]{3,}$/", $username)) {
			throw new RegistrationError("username", "Invalid username (must be at least 3 characters long, only english alphanumeric characters are allowed");
		}
		$conn = $this->conn;
		$username_check = $conn->prepare("SELECT * FROM members WHERE username = :username");
		$username_check->execute(array(":username" => $username));
		$x = $username_check->fetchAll(\PDO::FETCH_COLUMN);
		if ($x) {
			throw new RegistrationError("username", "Username already registered");
		}
		return $username;
	}

	function validate_email($email) {
		$filt_email = filter_var($email, FILTER_VALIDATE_EMAIL);
		if ($filt_email != $email) {
			throw new RegistrationError("email", "Doesn't validate as a valid email address");
		}
		$conn = $this->conn;
		$email_check = $conn->prepare("SELECT * FROM members where email = :email");
		$email_check->execute(array(":email" => $filt_email));
		$x = $email_check->fetchAll(\PDO::FETCH_COLUMN);
		if ($x) {
			throw new RegistrationError("email", "An account with that email address already exists, you can <a href=\"/reset_password.php\">Reset your password</a> if you need a new password");
		}
		return $filt_email;
	}

	function validate_fullname($full_name) {
		$filt_fname = filter_var($full_name, FILTER_SANITIZE_STRING, array(
			"flags" => FILTER_FLAG_NO_ENCODE_QUOTES | FILTER_FLAG_STRIP_LOW | FILTER_FLAG_STRIP_HIGH | FILTER_FLAG_STRIP_BACKTICK
		));
		return $filt_fname;
	}

	// TODO: reset password functionality that sends a randomly generated password to your email address
	function register($username, $email, $password, $full_name, $validate_email=false, $email_twig_env=null) {
		$filt_email = $this->validate_email($email);
		$filt_username = $this->validate_username($username);
		$filt_fname = $this->validate_fullname($full_name);
		$statements = $this->statements;
		$conn = $this->conn;
		$pass_hash = password_hash($password, PASSWORD_ARGON2I);
		$conn->beginTransaction();
		$success = $statements["register_member"]->execute(array(
			":username" => $username,
			":email" => $filt_email,
			":version" => $this->table_decls["members"]->newest_tableversion,
			":full_name" => $filt_fname,
			":argon2_phash" => $pass_hash
		));
		// TODO: check that it was actually successful
		if (!$success) {
			$einfo = $statements["register_member"]->errorInfo();
			error_log("mcoop member registration failed inserting into the database: " . var_export($einfo, true) . " ($username, $email, $password, $pass_hash, $full_name)");
			throw new RegistrationError("database", "registration failed internally, contact the admin");
		}
		if ($validate_email) {
			$this->send_validation_email($username, $filt_email, $email_twig_env);
		}
		$conn->commit();
		// TODO: setup the login session here too, not sure if session_start should be called in the script setup code actually instead of just here (and just set the variable in $_SESSION here)
		//session_start();
		$_SESSION["logged_in"] = true;
		$_SESSION["login_username"] = $username;
		// TODO: include login time in here and other things so you can invalidate sessions/log people out if a password reset gets requested or anything else
		//session_write_close();
	}

	function send_validation_email($username, $email, $email_twig_env) {
		// generate a validation code, just use some random bytes, hash them and use the hexdigest for now
		$rbytes = random_bytes(64);
		$h = hash("sha256", $rbytes, false);
		$conn = $this->conn;
		$stmt = $conn->prepare("UPDATE members SET validation_code = :vcode WHERE username = :username AND email = :email");
		$success = $stmt->execute(array(
			":vcode" => $h,
			":username" => $username,
			":email" => $email
		));
		if (!$success) {
			$einfo = $stmt->errorInfo();
			error_log("mcoop: send_validation_email failed updating members: " . var_export($einfo, true) . " ($h, $username, $email)");
			throw new RegistrationError("database", "internal error generating validation code, contact the admin");
		}
		$headers = "From: " . $this->config->email_from_address;
		$sitename = $this->config->website_name;
		$vurl = urljoin($this->config->webapp_base_uri, "validate.php?un=$username&vcode=$h");
		$deactivate_url = urljoin($this->config->webapp_base_uri, "deactivate.php?un=$username&vcode=$h");
		$body = $email_twig_env->render("validation_email.tmpl", array(
			"email" => $email,
			"website_name" => $sitename,
			"vurl" => $vurl,
			"deactivate_url" => $deactivate_url
		));
		mail($email, "Activate your $sitename account", $body, $headers);
	}

	function login($username, $password, $session_info) {
		$m = DBMember::load_by($this, "username", $username);
		// TODO: password reset logic
		if (password_verify($password, $m->argon2_password_hash)) {
			$_SESSION["logged_in"] = true;
			$_SESSION["login_username"] = $username;
			$session_info->re_init();
			return true;
		} else {
			throw new LoginError("Invalid Password");
		}
	}

	function get_members($only_verified=true) {
		
	}
}
?>