
DROP TABLE IF EXISTS `0_pos_stock_exclude`;
CREATE TABLE IF NOT EXISTS `0_pos_stock_exclude` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`stock_id` varchar(20) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	UNIQUE KEY (`stock_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_returnable_exclude`;
CREATE TABLE IF NOT EXISTS `0_pos_returnable_exclude` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`stock_id` varchar(20) NOT NULL DEFAULT '',
	PRIMARY KEY (`id`),
	UNIQUE KEY (`stock_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_trans`;
CREATE TABLE IF NOT EXISTS `0_pos_trans` (
	`trans_no` int(11) NULL,
	`cash_amount` double NOT NULL DEFAULT '0',
	`discount` double NOT NULL DEFAULT '0',
	`order_type` int(5) DEFAULT NULL,
	`table_id` smallint(5) DEFAULT NULL,
	`trans_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`pos_id` smallint(6) UNSIGNED NOT NULL,
	UNIQUE KEY (`trans_no`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_salesman`;
CREATE TABLE IF NOT EXISTS `0_pos_salesman` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`phone` char(30) NOT NULL DEFAULT '',
	`email` varchar(100) NOT NULL DEFAULT '',
	`inactive` tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_commission`;
CREATE TABLE IF NOT EXISTS `0_pos_commission` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`name` varchar(100) NOT NULL,
	`commission` double NOT NULL DEFAULT '0',
	`inactive` tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_salesman_trans`;
CREATE TABLE IF NOT EXISTS `0_pos_salesman_trans` (
	`salesman_id` int(11) DEFAULT NULL ,
	`debtor_trans_no` int(11) DEFAULT NULL,
	`stock_id` varchar(20) NOT NULL DEFAULT '',
	`quantity` double NOT NULL DEFAULT '0',
	`unit_price` double NOT NULL DEFAULT '0',
	`commission` double NOT NULL DEFAULT '0',
	UNIQUE KEY (`salesman_id`,`debtor_trans_no`,`stock_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_credit_account`;
CREATE TABLE IF NOT EXISTS `0_pos_credit_account` (
	`pos_id` smallint(6) UNSIGNED NOT NULL,
	`account` smallint(6) UNSIGNED NOT NULL,
	PRIMARY KEY (`pos_id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_orders`;
CREATE TABLE IF NOT EXISTS `0_pos_orders` (
	`pos_id` smallint(6) UNSIGNED NOT NULL,
	`table_id` smallint(5) DEFAULT NULL,
	`user_id` smallint(6) NOT NULL,
	`item_code` varchar(20) NOT NULL DEFAULT '',
	`item_description`  varchar(200) NOT NULL DEFAULT '',
	`units` varchar(20) NOT NULL DEFAULT 'each',
	`quantity` double NOT NULL DEFAULT '0',
	`price` double NOT NULL DEFAULT '0',
	`discount_percent` double NOT NULL DEFAULT '0',
	`created_time` datetime NOT NULL,
	`ordered` tinyint(1) NOT NULL DEFAULT '0',
	UNIQUE (`pos_id`,`table_id`,`item_code`,`price`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_shift`;
CREATE TABLE IF NOT EXISTS `0_pos_shift` (
	`id` int(11) NOT NULL AUTO_INCREMENT,
	`shift_name` varchar(100) NOT NULL,
	`start_time` time,
	`end_time` time,
	`inactive` tinyint(1) NOT NULL DEFAULT '0',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB;

DROP TABLE IF EXISTS `0_pos_bank_trans`;
CREATE TABLE IF NOT EXISTS `0_pos_bank_trans` (
	`bank_trans_no` int(11) DEFAULT NULL,
	`pos_trans_no` int(11) DEFAULT NULL,
	`trans_type` smallint(6) unsigned NOT NULL DEFAULT '0',
	UNIQUE (`bank_trans_no`, `pos_trans_no`, `trans_type`)
) ENGINE=InnoDB;

DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_def_customer';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_def_category';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_def_sales_type';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_commission';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_amt_barcode';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_restaurant_mode';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_kitchen_print_silent';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_receipt_print_silent';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_def_restaurant_order_type';
DELETE FROM `0_sys_prefs` WHERE `0_sys_prefs`.`name` = 'pos_table_number';

INSERT INTO `0_sys_prefs` VALUES ('pos_def_customer', NULL, 'int', NULL, 1);
INSERT INTO `0_sys_prefs` VALUES ('pos_def_category', NULL, 'int', NULL, -1);
INSERT INTO `0_sys_prefs` VALUES ('pos_def_sales_type', NULL, 'int', '11', 1);
INSERT INTO `0_sys_prefs` VALUES ('pos_commission', NULL, 'int', '1', 0);
INSERT INTO `0_sys_prefs` VALUES ('pos_amt_barcode', NULL, 'int', '1', 0);
INSERT INTO `0_sys_prefs` VALUES ('pos_restaurant_mode', NULL, 'int', '1', 0);
INSERT INTO `0_sys_prefs` VALUES ('pos_kitchen_print_silent', NULL, 'int', '1', 0);
INSERT INTO `0_sys_prefs` VALUES ('pos_receipt_print_silent', NULL, 'int', '1', 0);
INSERT INTO `0_sys_prefs` VALUES ('pos_def_restaurant_order_type', NULL, 'tinyint', '1', 1);
INSERT INTO `0_sys_prefs` VALUES ('pos_table_number', NULL, 'int', '5', 10);
