<?php

/**
 * THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED
 * WARRANTIES, INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF
 * MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
 *
 * @package fusionLib
 * @copyright Copyright (c) 2008 - 2016 fusionLib. All rights reserved.
 * @link http://fusionlib.com
 */

/**
 * flUserDbInstaller Class
 *
 * Class to provide the functionality to install, upgrade and remove the database
 * tables required for user management, authentication and access control.
 *
 * @package fusionLib
 */
class flUserDbInstaller extends flDbInstallerInterface {

	/**
	 * Install or upgrade the database.
	 *
	 * @param flDb $db The database object through which to access the database.
	 */
	function install($db) {

		// Table to hold users
		$this->createIfNotExists($db, 'fl_users', "
CREATE TABLE IF NOT EXISTS `fl_users` (
  `uid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `displayname` VARCHAR(32) NOT NULL,
  `passwd` VARCHAR(32) NOT NULL,
  `email` VARCHAR(200) NOT NULL,
  `tz` VARCHAR(32) NOT NULL,
  `authkey` VARCHAR(16) NOT NULL,
  `bio` TEXT NOT NULL,
  `state` ENUM('active','disabled','pending', 'blocked') NOT NULL,
  `added` DATETIME NOT NULL,
  `lastaccess` DATETIME,
  `lastslaveupd` DATETIME,
  `salt` INT NOT NULL,
  `tokensalt` INT NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE INDEX `username` (`username` ASC),
  UNIQUE INDEX `email` (`email` ASC))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");
		
		// Table to hold external auth information
		$this->createIfNotExists($db, 'fl_users_ext', "
CREATE TABLE IF NOT EXISTS `fl_users_ext` (
  `uid` INT UNSIGNED NOT NULL,
  `service` VARCHAR(32) NOT NULL,
  `authcode` VARCHAR(128) NOT NULL,
  PRIMARY KEY (`uid`, `service`),
  KEY `authcode` (`service` ASC, `authcode` ASC))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
");

		// Table to track failed login attempts by IP
		$this->createIfNotExists($db, 'fl_usersipblocks', "
CREATE  TABLE IF NOT EXISTS `fl_usersipblocks` (
  `ip` CHAR(32) NOT NULL ,
  `hitcount` INT NOT NULL ,
  `lasthit` DATETIME NOT NULL ,
  PRIMARY KEY (`ip`) ,
  INDEX `lasthit` (`lasthit` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Table to track blocked users
		$this->createIfNotExists($db, 'fl_usersblocks', "
CREATE  TABLE IF NOT EXISTS `fl_usersblocks` (
  `uid` INT UNSIGNED NOT NULL ,
  `blockedby` INT UNSIGNED NOT NULL ,
  `blockedat` DATETIME NOT NULL ,
  `blockliftat` DATETIME ,
  `blockreason` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`uid`) ,
  INDEX `liftat` (`blockliftat` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Create table to hold permission groups
		$this->createIfNotExists($db, 'fl_permgroup', "
CREATE  TABLE IF NOT EXISTS `fl_permgroup` (
  `grpname` VARCHAR(32) NOT NULL ,
  `displayname` VARCHAR(255) NOT NULL ,
  `textdomain` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`grpname`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Create table to hold a permission
		$this->createIfNotExists($db, 'fl_permissions', "
CREATE  TABLE IF NOT EXISTS `fl_permissions` (
  `permission` VARCHAR(32) NOT NULL ,
  `displayname` VARCHAR(255) NOT NULL ,
  `textdomain` VARCHAR(255) NOT NULL ,
  `disporder` INT NOT NULL ,
  PRIMARY KEY (`permission`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Create table to hold roles
		$this->createIfNotExists($db, 'fl_roles', "
CREATE  TABLE IF NOT EXISTS `fl_roles` (
  `roleid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `rolename` VARCHAR(32) NOT NULL ,
  `displayname` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`roleid`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Create the admin role
		$db->insert('fl_roles', array('roleid' => 1, 'rolename' => 'administrator', 'displayname' => 'Administrator'));

		// Update to 1.2.0
		$db->query("ALTER TABLE fl_permgroup ADD COLUMN textdomain VARCHAR(255) NOT NULL AFTER displayname");
		$db->query("ALTER TABLE fl_permissions ADD COLUMN textdomain VARCHAR(255) NOT NULL AFTER displayname");

		// Create table to hold mapping of permissions to roles
		$this->createIfNotExists($db, 'fl_roleperms', "
CREATE  TABLE IF NOT EXISTS `fl_roleperms` (
  `roleid` INT UNSIGNED NOT NULL ,
  `permission` VARCHAR(32) NOT NULL ,
  PRIMARY KEY (`roleid`, `permission`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Create table to hold user to role mappings
		$this->createIfNotExists($db, 'fl_usersroles', "
CREATE  TABLE IF NOT EXISTS `fl_usersroles` (
  `uid` INT UNSIGNED NOT NULL ,
  `roleid` INT UNSIGNED NOT NULL ,
  PRIMARY KEY (`uid`, `roleid`) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci
");

		// Update to 1.2.1
		$db->query('DROP TABLE IF EXISTS `fl_usersprofile`');
		$db->query("ALTER TABLE fl_users ADD COLUMN lastslaveupd DATETIME NOT NULL AFTER lastaccess");
		$db->query("ALTER TABLE fl_roles ADD COLUMN rolename VARCHAR(32) NOT NULL AFTER roleid");
		$db->query("UPDATE fl_roles SET rolename='administrator' WHERE roleid=1 && rolename=''");
		$db->query("UPDATE fl_roles SET rolename=CONCAT('role', roleid) WHERE rolename=''");
		$db->query('ALTER TABLE fl_roles ADD UNIQUE INDEX `rolename` (`rolename` ASC)');
		$db->query("ALTER TABLE fl_users CHANGE state state ENUM('active','disabled','pending', 'blocked') NOT NULL");

		// Update to 1.3.1
		$db->query("ALTER TABLE fl_users ADD COLUMN displayname VARCHAR(32) NOT NULL AFTER username");

		// Update to 1.7.0
		$db->query("ALTER TABLE fl_users ADD COLUMN `authkey` VARCHAR(16) NOT NULL AFTER tz");

		// Update to 1.8.0
		$db->query("ALTER TABLE fl_users ADD COLUMN `bio` TEXT NOT NULL AFTER authkey");

		// Update to 1.9.0
		$db->query("ALTER TABLE fl_users MODIFY lastaccess DATETIME NULL");
		$db->query("ALTER TABLE fl_users MODIFY lastslaveupd DATETIME NULL");
		$db->query("ALTER TABLE fl_usersblocks MODIFY blockliftat DATETIME NULL");

		$db->query("UPDATE fl_users SET lastaccess=NULL WHERE lastaccess='0000-00-00 00:00:00'");
		$db->query("UPDATE fl_users SET lastslaveupd=NULL WHERE lastslaveupd='0000-00-00 00:00:00'");
		$db->query("UPDATE fl_usersblocks SET blockliftat=NULL WHERE blockliftat='0000-00-00 00:00:00'");
	}

	/**
	 * Uninstall the database, all data is lost.
	 *
	 * @param flDb $db The database object through which to access the database.
	 */
	function uninstall($db) {
		$db->query('DROP TABLE IF EXISTS `fl_users`');
		$db->query('DROP TABLE IF EXISTS `fl_usersipblocks`');
		$db->query('DROP TABLE IF EXISTS `fl_usersblocks`');
		$db->query('DROP TABLE IF EXISTS `fl_usersprofile`');
		$db->query('DROP TABLE IF EXISTS `fl_permgroup`');
		$db->query('DROP TABLE IF EXISTS `fl_permissions`');
		$db->query('DROP TABLE IF EXISTS `fl_roles`');
		$db->query('DROP TABLE IF EXISTS `fl_roleperms`');
		$db->query('DROP TABLE IF EXISTS `fl_usersroles`');
	}
}
