DROP TABLE IF EXISTS `tally_user`;
CREATE TABLE `tally_user` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `username` varchar(40) NOT NULL,
  `password` varchar(255) NOT NULL,
  `tally_wait_before_next_update` int(10) unsigned DEFAULT 0,
  `tally_min_run_every_n_seconds` int(10) unsigned DEFAULT 0,
  `status` char(1) NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `store_id` int(11) DEFAULT NULL,
  `store_status` enum('Active','Inactive','Uninstalled') DEFAULT 'Active',
  `tally_company_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username_unique` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



DROP TABLE IF EXISTS `tally_log`;
CREATE TABLE `tally_log` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tally_ticket_id` int(10) unsigned DEFAULT NULL,
  `tally_user_id` int(10) NOT NULL,
  `log_type` varchar(255) NOT NULL,
  `msg` text NOT NULL,
  `created_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `tally_ticket_id_index` (`tally_ticket_id`),
  KEY `log_type_index` (`log_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `tally_ticket`;
CREATE TABLE `tally_ticket` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tally_user_id` int(10) NOT NULL,
  `ticket` char(36) NOT NULL,
  `processed` int(10) unsigned DEFAULT 0,
  `lasterror_num` varchar(32) DEFAULT NULL,
  `lasterror_msg` varchar(255) DEFAULT NULL,
  `ipaddr` char(15) NOT NULL,
  `created_at` timestamp NOT NULL,
  `updated_at` timestamp NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ticket_index` (`ticket`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


DROP TABLE IF EXISTS `tally_queue`;
CREATE TABLE `tally_queue` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `tally_ticket_id` int(10) unsigned DEFAULT NULL,
  `tally_user_id` int(10) NOT NULL,
  `action` varchar(32) NOT NULL,
  `ident` varchar(40) NOT NULL,
  `extra_data` longtext DEFAULT NULL,
  `request_data` longtext DEFAULT NULL,
  `response_data` longtext DEFAULT NULL,
  `parent_queue` int(10) unsigned DEFAULT NULL,
  `queue_status` char(1) NOT NULL,
  `msg` text DEFAULT NULL,
  `enqueue_datetime` datetime NOT NULL,
  `dequeue_datetime` datetime DEFAULT NULL,

  PRIMARY KEY (`id`),
  KEY `tally_ticket_id_index` (`tally_ticket_id`),
  KEY `username_index` (`username`,`action`,`ident`,`queue_status`),
  KEY `queue_status_index` (`queue_status`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


