CREATE TABLE `opl_inventory` (
  `invId` int(10) unsigned NOT NULL,
  `fid` int(10) unsigned NOT NULL default '0',
  `scanOrder` int(10) unsigned NOT NULL auto_increment,
  `sortOrder` int(10) NOT NULL default '0',
  `callNumber` varchar(40) NOT NULL,
  `barcode` varchar(50) NOT NULL default '',
  `status` set('active','damaged','found','missing','loan','invalid','unknown','noCallNumber','scanned','misplace') NOT NULL default 'active',
  `updated` tinyint(3) unsigned NOT NULL default '0',
  `updatedStatus` set('active','damaged','found','missing','loan','invalid','unknown','noCallNumber','scanned','misplace') NOT NULL,
  `noteDetail` varchar(100) default NULL,
  PRIMARY KEY  (`scanOrder`),
  UNIQUE KEY `bc_invId` (`barcode`,`invId`)
) ENGINE=MyISAM;

CREATE TABLE `opl_inventoryFile` (
  `invId` int(10) unsigned NOT NULL,
  `fid` int(10) unsigned NOT NULL auto_increment,
  `fname` varchar(255) NOT NULL default '',
  `uid` int(10) unsigned NOT NULL default '0',
  `mesgDigest` varchar(128) default NULL,
  `dateUpload` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`fid`),
  UNIQUE KEY `u_fileUser` (`fname`,`uid`,`invId`),
  UNIQUE KEY `u_mesgDigest` (`mesgDigest`)
) ENGINE=MyISAM;

 CREATE TABLE `opl_inventoryInfo` (
  `invId` int(10) NOT NULL auto_increment,
  `uid` int(10) unsigned NOT NULL,
  `callNoB` varchar(40) default '0',
  `callNoE` varchar(40) default '0',
  `invDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `status` set('scanning','done','waiting','processing','closed') default 'scanning',
  `orderidB` int(5) unsigned default NULL,
  `orderidE` int(5) unsigned default NULL,
  PRIMARY KEY  (`invId`)
) ENGINE=MyISAM;
