phpengine/phengine.sql

-- Adminer 4.7.1 MySQL dump

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

SET NAMES utf8mb4;

DROP DATABASE IF EXISTS `phengine`;
CREATE DATABASE `phengine` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `phengine`;

DROP TABLE IF EXISTS `label_bindings`;
CREATE TABLE `label_bindings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `label_id` varchar(255) NOT NULL,
  `barcode` varchar(255) NOT NULL,
  `bat_good` int(11) DEFAULT NULL,
  `bat_low` timestamp NULL DEFAULT NULL,
  `bat_bad` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `label_bindings` (`id`, `label_id`, `barcode`, `bat_good`, `bat_low`, `bat_bad`) VALUES
(1,	'0108C4567',	'aaaaaaaa',	1554380794,	NULL,	NULL);

DROP TABLE IF EXISTS `notifications`;
CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `code` int(11) NOT NULL DEFAULT 0,
  `caption` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `link` varchar(255) NOT NULL DEFAULT '#',
  `toClient` int(11) DEFAULT NULL,
  `toStaff` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `queuesystem`;
CREATE TABLE `queuesystem` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `servicename` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
  `command` varchar(5000) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `roles`;
CREATE TABLE `roles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` varchar(1000) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_roles_name` (`name`),
  CONSTRAINT `chk_roles_name_non_empty` CHECK (char_length(trim(`name`)) > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `roles` (`id`, `name`, `description`) VALUES
(1, 'Administrator', 'Full administrative access'),
(2, 'Inventory operator', 'Inventory management access');

DROP TABLE IF EXISTS `inventory_items`;
CREATE TABLE `inventory_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `quantity` int(11) UNSIGNED NOT NULL DEFAULT 0,
  `reorder_level` int(11) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_inventory_items_sku` (`sku`),
  CONSTRAINT `chk_inventory_items_sku_non_empty` CHECK (char_length(trim(`sku`)) > 0),
  CONSTRAINT `chk_inventory_items_name_non_empty` CHECK (char_length(trim(`name`)) > 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `saltedpassword` varchar(255) NOT NULL COMMENT 'password_hash("username|password", PASS',
  `roles` varchar(4096) NOT NULL DEFAULT '{}' COMMENT 'PHP Serialized Array',
  `avatar` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_users_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `users` (`id`, `username`, `saltedpassword`, `roles`, `avatar`) VALUES
(1,	'admin',	'$2y$10$cJiNAGj.XSELKuVuQ9gSwOlExVSDGj6IsuIsSsK.fV0KEJX56ncWK',	'a:2:{i:0;i:1;i:1;i:2;}',	''),
(2,	'operatore1',	'$2y$10$9uRKekk0i9Z08fXQFSjW6.PK6l/fk956bNvMjmBMmaJNqo9KJ5Txu',	'a:1:{i:0;i:2;}',	'');

-- 2019-04-22 22:27:01