ALTER TABLE `floortables` ADD `tabletype` INT NOT NULL DEFAULT '4' AFTER `tablenumber`;

CREATE TABLE `tabletypes` (
  `tabletypeid` int(11) NOT NULL,
  `tabletypename` text NOT NULL,
  `numberchairs` int(11) NOT NULL,
  `tabletypeclass` varchar(132) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `tabletypes` (`tabletypeid`, `tabletypename`, `numberchairs`, `tabletypeclass`) VALUES
(1, 'Chair', 1, 'chair'),
(2, 'Square 2-Seats', 2, 'sq-2'),
(3, 'Square 3-Seats', 3, 'sq-3'),
(4, 'Square 4-Seats', 4, 'sq-4'),
(5, 'Square 5-Seats', 5, 'sq-5'),
(6, 'Square 6-Seats', 6, 'sq-6'),
(7, 'Square 7-Seats', 7, 'sq-7'),
(8, 'Square 8-Seats', 8, 'sq-8'),
(9, 'Square 1-Seat', 1, 'sq-1'),
(10, 'Round 1-Seat', 1, 'rd-1'),
(11, 'Round 2-Seats', 2, 'rd-2'),
(12, 'Round 3-Seats', 3, 'rd-3'),
(13, 'Round 4-Seats', 4, 'rd-4'),
(14, 'Round 5-Seats', 5, 'rd-5'),
(15, 'Round 6-Seats', 6, 'rd-6'),
(16, 'Round 7-Seats', 7, 'rd-7'),
(17, 'Round 8-Seats', 8, 'rd-8');


ALTER TABLE `tabletypes`
  ADD PRIMARY KEY (`tabletypeid`);

ALTER TABLE `tabletypes`
  MODIFY `tabletypeid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;COMMIT;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'floortables', 'Table Type', 'tabletype', 'dropdown', 'What Type of Table is this?', '0', '1', '1', '4', 'select tabletypeid as id, tabletypename as val from tabletypes order by tabletypename asc', '', 'select tabletypeid as id, tabletypename as val from tabletypes where tabletypeid =\'%id%\'', '');

ALTER TABLE `floortables` ADD `posx` INT NOT NULL DEFAULT '0' AFTER `tabletype`, ADD `posy` INT NOT NULL DEFAULT '0' AFTER `posx`;

ALTER TABLE `floortables` ADD `placed` BOOLEAN NOT NULL DEFAULT FALSE AFTER `posy`;

INSERT INTO `tabletypes` (`tabletypeid`, `tabletypename`, `numberchairs`, `tabletypeclass`) VALUES (NULL, 'Booth (4 Seats)', '4', 'booth');

ALTER TABLE `floortables` ADD `rotation` DOUBLE NOT NULL DEFAULT '0' AFTER `placed`, ADD `sizew` INT NOT NULL DEFAULT '100' AFTER `rotation`, ADD `sizeh` INT NOT NULL DEFAULT '100' AFTER `sizew`;

CREATE TABLE `floorshapes` (
  
  `jid` int(11) NOT NULL DEFAULT '0',
  `posx` int(11) NOT NULL,
  `posy` int(11) NOT NULL,
  `rotation` double NOT NULL,
  `sizew` int(11) NOT NULL,
  `sizeh` int(11) NOT NULL,
  `shapetype` varchar(132) NOT NULL,
  `roomid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `users` ADD `lastroomid` INT NOT NULL DEFAULT '-1' AFTER `usertype`;

ALTER TABLE `orderitems` ADD `seatno` INT NOT NULL DEFAULT '-1' AFTER `archived`;

CREATE TABLE `seats` (
  `orderid` int(11) NOT NULL,
  `seatid` int(11) NOT NULL,
  `seatname` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `orders` ADD `seatcount` INT NOT NULL DEFAULT '1' AFTER `archived`;

ALTER TABLE `seats` ADD `archived` BOOLEAN NOT NULL DEFAULT FALSE AFTER `seatname`;

ALTER TABLE `pos`.`seats` ADD INDEX `archived` (`archived`);

ALTER TABLE `seats` ADD `subtotal` DECIMAL(10,2) NOT NULL AFTER `archived`, ADD `discounts` DECIMAL(10,2) NOT NULL AFTER `subtotal`, ADD `tax` DECIMAL(10,2) NOT NULL AFTER `discounts`, ADD `grandtotal` DECIMAL(10,2) NOT NULL AFTER `tax`, ADD `tendered` DECIMAL(10,2) NOT NULL AFTER `grandtotal`, ADD `changedue` DECIMAL(10,2) NOT NULL AFTER `tendered`, ADD `amtdue` DECIMAL(10,2) NOT NULL AFTER `changedue`, ADD `amtpaid` DECIMAL(10,2) NOT NULL AFTER `amtdue`;

ALTER TABLE `printerqueue` ADD `seatno` INT NOT NULL DEFAULT '-1' AFTER `orderitemid`;

ALTER TABLE `users` ADD `lasthome` INT NOT NULL DEFAULT '1' AFTER `lastroomid`;

ALTER TABLE `orderitems` ADD `voidby` INT NOT NULL AFTER `isvoided`;

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('breakfastcutoff', '11:30 AM', NULL, 'Breakfast Cutoff Time', 'This is the time at which breakfast ends and lunch begins.', '1', '6', '2', 'time');

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('lunchcutoff', '4:30 PM', NULL, 'Lunch Cutoff Time', 'This is the time at which lunch ends and dinner begins.', '1', '6', '3', 'time');

ALTER TABLE `orders` ADD `ordertime` TIME NOT NULL AFTER `orderdate`;

ALTER TABLE `orders` ADD INDEX `ordertimeindex` (`ordertime`);

update orders set ordertime=FROM_UNIXTIME(orderdate, '%H:%i:%s');

INSERT INTO `assetmgr` (`assetid`, `assettype`, `pagename`, `mainicon`, `singlename`, `tablename`, `returnurl`, `returnname`, `sqlonsave`) VALUES (NULL, 'Menus', 'Manage Menus', '<i class=\"fas fa-clipboard-list\"></i>', 'Menu', 'menus', 'menueditor.php', 'Menu Editor', '');

CREATE TABLE `menus` (
  `menuid` int(11) NOT NULL,
  `menuname` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `menus`
--
ALTER TABLE `menus`
  ADD PRIMARY KEY (`menuid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `menus`
--
ALTER TABLE `menus`
  MODIFY `menuid` int(11) NOT NULL AUTO_INCREMENT;COMMIT;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menus', 'Menu Name', 'menuname', 'text', 'Name Your Menu (ie Breakfast, Lunch, Dinner, Specials)', '0', '1', '1', '0', '', '', '', '');

CREATE TABLE `menutimes` (
  `ownerid` int(11) NOT NULL,
  `valueid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
COMMIT;

CREATE TABLE `timesofday` (
  `timeid` int(11) NOT NULL,
  `timename` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `timesofday`
--

INSERT INTO `timesofday` (`timeid`, `timename`) VALUES
(1, 'Breakfast'),
(2, 'Lunch'),
(3, 'Dinner');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `timesofday`
--
ALTER TABLE `timesofday`
  ADD PRIMARY KEY (`timeid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `timesofday`
--
ALTER TABLE `timesofday`
  MODIFY `timeid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menus', 'Default Times', '', 'checkboxes', 'Which times of day should this menu be default?', '0', '0', '1', '2', 'select timeid as id, timename as val from timesofday order by timeid asc', 'menutimes', 'select timeid as id, timename as val from timesofday where timeid =\'%id%\'', '');


INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menus', 'menuid', 'menuid', 'int', '', '1', '0', '0', '0', '', '', '', '');

ALTER TABLE `menus` ADD `availableothertimes` BOOLEAN NOT NULL DEFAULT TRUE AFTER `menuname`;


INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menus', 'Display During Other Times?', 'availableothertimes', 'boolean', 'Do you want this menu available during times it is not the default menu?', '0', '1', '1', '3', '', '', '', '');

CREATE TABLE `relcatmenus` (
  `ownerid` int(11) NOT NULL,
  `valueid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
COMMIT;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'categories', 'Parent Menu', '', 'checkboxes', 'Which menus should this category appear?', '0', '0', '1', '3', 'select menuid as id, menuname as val from menus order by menuname asc', 'relcatmenus', 'select menuid as id, menuname as val from menus where menuid =\'%id%\'', '');

UPDATE `tabledefs` SET `settingname` = 'Display All Day?', `fielddescription` = 'Do you want this menu available all day?' WHERE `tabledefs`.`defid` = 73;

INSERT INTO `assetmgr` (`assetid`, `assettype`, `pagename`, `mainicon`, `singlename`, `tablename`, `returnurl`, `returnname`, `sqlonsave`) VALUES (NULL, 'Menu Period', 'Manage Menu Periods', '<i class=\"far fa-clock\"></i>', 'Menu Period', 'menuperiods', 'menueditor.php', 'Menu Editor', '');

CREATE TABLE `menuperiods` (
  `periodid` int(11) NOT NULL,
  `starttime` time NOT NULL,
  `endtime` time NOT NULL,
  `dayofweek` int(11) NOT NULL,
  `periodname` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `menuperiods`
  ADD PRIMARY KEY (`periodid`);


ALTER TABLE `menuperiods`
  MODIFY `periodid` int(11) NOT NULL AUTO_INCREMENT;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menuperiods', 'periodid', 'periodid', 'int', '', '1', '0', '0', '0', '', '', '', '');

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menuperiods', 'Menu Period Name', 'periodname', 'text', 'Name of Menu Period (Breakfast, Lunch, Happy Hour, Sunday Brunch)', '0', '1', '1', '0', '', '', '', '');

CREATE TABLE `daysofweek` (
  `dayid` int(11) NOT NULL,
  `dayofweek` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `daysofweek`
--

INSERT INTO `daysofweek` (`dayid`, `dayofweek`) VALUES
(1, 'Sunday'),
(2, 'Monday'),
(3, 'Tuesday'),
(4, 'Wednesday'),
(5, 'Thursday'),
(6, 'Friday'),
(7, 'Saturday'),
(8, 'Every Day'),
(9, 'Weekdays'),
(10, 'Weekends');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `daysofweek`
--
ALTER TABLE `daysofweek`
  ADD PRIMARY KEY (`dayid`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `daysofweek`
--
ALTER TABLE `daysofweek`
  MODIFY `dayid` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=11;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menuperiods', 'Day of Week', 'dayofweek', 'dropdown', 'What day does this period apply to?', '0', '1', '1', '2', 'select dayid as id, dayofweek as val from daysofweek order by dayid asc', '', 'select dayid as id, dayofweek as val from daysofweek where dayid =\'%id%\'', '');


INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menuperiods', 'Period Start Time', 'starttime', 'time', 'Period Start', '0', '1', '1', '3', '', '', '', '');

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menuperiods', 'Period End Time', 'endtime', 'time', 'Period End', '0', '1', '1', '4', '', '', '', '');

UPDATE `tabledefs` SET `ordinality` = '4', `sqlquery` = 'select periodid as id, periodname as val from menuperiods order by periodname asc', `relationshiptable` = 'menuperiods', `displaysql` = 'select periodid as id, periodname as val from menuperiods where periodid =\'%id%\'' WHERE `tabledefs`.`defid` = 71;

UPDATE `tabledefs` SET `ordinality` = '2' WHERE `tabledefs`.`defid` = 73;

UPDATE `tabledefs` SET `relationshiptable` = 'menudeftimes' WHERE `tabledefs`.`defid` = 71;

CREATE TABLE `menudeftimes` (
  `ownerid` int(11) NOT NULL,
  `valueid` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `tabledefs` (`defid`, `tablename`, `settingname`, `actualfield`, `datatype`, `fielddescription`, `isid`, `showintable`, `editable`, `ordinality`, `sqlquery`, `relationshiptable`, `displaysql`, `defaultvalue`) VALUES (NULL, 'menus', 'Display Times', '', 'checkboxes', 'Which times of day should this menu be available?', '0', '0', '1', '3', 'select periodid as id, periodname as val from menuperiods order by periodname asc', 'menutimes', 'select periodid as id, periodname as val from menuperiods where periodid =\'%id%\'', '');

UPDATE `settings` SET `friendlyname` = 'Lunch Cutoff Time (Reporting)', `description` = 'This is the time at which lunch ends and dinner begins in your reports.' WHERE `settings`.`settingint` = 31; UPDATE `settings` SET `friendlyname` = 'Breakfast Cutoff Time (Reporting)', `description` = 'This is the time at which breakfast ends and lunch begins in your reports.' WHERE `settings`.`settingint` = 30;

UPDATE `assetmgr` SET `returnurl` = 'rsetup.php', `returnname` = 'Setup' WHERE `assetmgr`.`assetid` = 2; UPDATE `assetmgr` SET `returnurl` = 'rsetup.php', `returnname` = 'Setup' WHERE `assetmgr`.`assetid` = 3; UPDATE `assetmgr` SET `returnurl` = 'rsetup.php', `returnname` = 'Setup' WHERE `assetmgr`.`assetid` = 4; UPDATE `assetmgr` SET `returnurl` = 'rsetup.php', `returnname` = 'Setup' WHERE `assetmgr`.`assetid` = 5;

UPDATE `assetmgr` SET `returnurl` = 'rsetup.php', `returnname` = 'Setup' WHERE `assetmgr`.`assetid` = 1;

ALTER TABLE `daemonlog` ADD `techdebug` INT NOT NULL DEFAULT '0' AFTER `timestamp`;

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('closebutton', 'Done For Day', NULL, 'Ticket Close Button Text', 'The text appearing on the close ticket button when an order is paid and complete', '1', '0', '4', 'text');

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('showsend', '1', NULL, 'Show Send Without Print', 'Allow staff to send orders to kitchen without printing a receipt simultaneously.', '1', '0', '5', 'number');

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('ticketmargintop', '1', NULL, 'Kitchen Ticket Print Top Margin', 'Margin in Inches', '1', '4', '1', 'number');

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('ticketmarginbottom', '1', NULL, 'Kitchen Ticket Print Bottom Margin', 'Margin in Inches', '1', '4', '2', 'number');

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('printvoids', '1', NULL, 'Send voided items to kitchen printers?', 'Any time an item is voided, would you like the designated kitchen printer to receive a void ticket?', '1', '4', '5', 'number');

INSERT INTO `permissions` (`id`, `permission`) VALUES (NULL, 'Restrict User To Timeclock');

ALTER TABLE `printerqueue` ADD `reportdate` VARCHAR(50) NOT NULL AFTER `seatno`, ADD `employeeid` INT NOT NULL AFTER `reportdate`;

INSERT INTO `settings` (`settingid`, `settingvalue`, `settingint`, `friendlyname`, `description`, `changeable`, `grouping`, `ordinality`, `datatype`) VALUES ('printfont', '12', NULL, 'Kitchen Ticket Font Size', 'Default is 12 ', '1', '4', '4', 'number');