+--
+-- Table structure for table illcomments
+--
+
+DROP TABLE IF EXISTS `illcomments`;
+CREATE TABLE illcomments (
+ illcomment_id int(11) NOT NULL AUTO_INCREMENT, -- Unique ID of the comment
+ illrequest_id bigint(20) unsigned NOT NULL, -- ILL request number
+ borrowernumber integer DEFAULT NULL, -- Link to the user who made the comment (could be librarian, patron or ILL partner library)
+ comment text DEFAULT NULL, -- The text of the comment
+ timestamp timestamp DEFAULT CURRENT_TIMESTAMP, -- Date and time when the comment was made
+ PRIMARY KEY ( illcomment_id ),
+ CONSTRAINT illcomments_bnfk
+ FOREIGN KEY ( borrowernumber )
+ REFERENCES borrowers ( borrowernumber )
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT illcomments_ifk
+ FOREIGN KEY (illrequest_id)
+ REFERENCES illrequests ( illrequest_id )
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `circulation_rules`
+--
+
+DROP TABLE IF EXISTS `circulation_rules`;
+CREATE TABLE `circulation_rules` (
+ `id` int(11) NOT NULL auto_increment,
+ `branchcode` varchar(10) NULL default NULL,
+ `categorycode` varchar(10) NULL default NULL,
+ `itemtype` varchar(10) NULL default NULL,
+ `rule_name` varchar(32) NOT NULL,
+ `rule_value` varchar(32) NOT NULL,
+ PRIMARY KEY (`id`),
+ CONSTRAINT `circ_rules_ibfk_1` FOREIGN KEY (`branchcode`) REFERENCES `branches` (`branchcode`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `circ_rules_ibfk_2` FOREIGN KEY (`categorycode`) REFERENCES `categories` (`categorycode`) ON DELETE CASCADE ON UPDATE CASCADE,
+ CONSTRAINT `circ_rules_ibfk_3` FOREIGN KEY (`itemtype`) REFERENCES `itemtypes` (`itemtype`) ON DELETE CASCADE ON UPDATE CASCADE,
+ KEY `rule_name` (`rule_name`),
+ UNIQUE (`branchcode`,`categorycode`,`itemtype`,`rule_name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `stockrotationrotas`
+--
+
+CREATE TABLE IF NOT EXISTS stockrotationrotas (
+ rota_id int(11) auto_increment, -- Stockrotation rota ID
+ title varchar(100) NOT NULL, -- Title for this rota
+ description text NOT NULL, -- Description for this rota
+ cyclical tinyint(1) NOT NULL default 0, -- Should items on this rota keep cycling?
+ active tinyint(1) NOT NULL default 0, -- Is this rota currently active?
+ PRIMARY KEY (`rota_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `stockrotationstages`
+--
+
+CREATE TABLE IF NOT EXISTS stockrotationstages (
+ stage_id int(11) auto_increment, -- Unique stage ID
+ position int(11) NOT NULL, -- The position of this stage within its rota
+ rota_id int(11) NOT NULL, -- The rota this stage belongs to
+ branchcode_id varchar(10) NOT NULL, -- Branch this stage relates to
+ duration int(11) NOT NULL default 4, -- The number of days items shoud occupy this stage
+ PRIMARY KEY (`stage_id`),
+ CONSTRAINT `stockrotationstages_rifk`
+ FOREIGN KEY (`rota_id`)
+ REFERENCES `stockrotationrotas` (`rota_id`)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT `stockrotationstages_bifk`
+ FOREIGN KEY (`branchcode_id`)
+ REFERENCES `branches` (`branchcode`)
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+
+--
+-- Table structure for table `stockrotationitems`
+--
+
+CREATE TABLE IF NOT EXISTS stockrotationitems (
+ itemnumber_id int(11) NOT NULL, -- Itemnumber to link to a stage & rota
+ stage_id int(11) NOT NULL, -- stage ID to link the item to
+ indemand tinyint(1) NOT NULL default 0, -- Should this item be skipped for rotation?
+ fresh tinyint(1) NOT NULL default 0, -- Flag showing item is only just added to rota
+ PRIMARY KEY (itemnumber_id),
+ CONSTRAINT `stockrotationitems_iifk`
+ FOREIGN KEY (`itemnumber_id`)
+ REFERENCES `items` (`itemnumber`)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ CONSTRAINT `stockrotationitems_sifk`
+ FOREIGN KEY (`stage_id`)
+ REFERENCES `stockrotationstages` (`stage_id`)
+ ON UPDATE CASCADE ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
+