SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `ifm_colitems` (
  `cli_collection` int NOT NULL,
  `cli_library` int NOT NULL,
  `cli_moved` tinyint UNSIGNED NOT NULL DEFAULT '0',
  `cli_user` int DEFAULT NULL,
  `cli_created` datetime DEFAULT NULL,
  `cli_is_model` tinyint DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci;

CREATE TABLE `ifm_families` (
  `fam_id` int NOT NULL,
  `fam_metric` tinyint DEFAULT '0',
  `fam_type` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `fam_name` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `fam_overname` tinyint DEFAULT '0',
  `fam_subtype` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `fam_brand_id` int NOT NULL DEFAULT '0',
  `fam_descr1` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `fam_descr2` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `fam_category` varchar(64) CHARACTER SET utf8 DEFAULT NULL,
  `fam_status` tinyint DEFAULT '0',
  `fam_lastmod` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fam_updated` timestamp NULL DEFAULT NULL,
  `fam_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `fam_modeller` int DEFAULT NULL,
  `fam_library` int NOT NULL DEFAULT '0',
  `fam_account` int DEFAULT NULL,
  `fam_png` varchar(16) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `fam_pngext` tinyint DEFAULT '0',
  `fam_description` text CHARACTER SET utf8,
  `fam_percent` float DEFAULT '0',
  `fam_url` varchar(255) DEFAULT NULL,
  `fam_hours` float DEFAULT '0',
  `fam_shortdesc` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `fam_omniclass` int DEFAULT '0',
  `fam_uniformat` int DEFAULT NULL,
  `fam_revitcat` int NOT NULL DEFAULT '-1',
  `fam_revitype` enum('CableTray','Ceiling','Conduit','DraftingView','Duct','Family','Floor','Group','HandRail','Pipe','ProjectInfo','Railing','RevitLink','Roof','Schedule','Stairs','TopRail','Wall','Macro','DrawingSheet','LinkedCAD','ImportedCAD','ImportedImage','FillPattern','FillRegion','ViewTemplates','Legend','InPlace','Phase','PhaseFilter','Level','Grid','Workset','View','DesignOption','FlexPipe','FlexDuct') CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT 'Family',
  `fam_source` int DEFAULT '0',
  `fam_parent` int DEFAULT '0',
  `fam_archived` tinyint DEFAULT '0',
  `fam_control` varchar(32) DEFAULT NULL,
  `fam_hosting` enum('Non hosted','Face','Line','Ceiling','Wall','Roof','Floor','Profile') CHARACTER SET ascii DEFAULT NULL,
  `fam_btypes` int DEFAULT '0',
  `fam_lookup` varchar(255) DEFAULT NULL,
  `fam_revit` year DEFAULT NULL,
  `fam_guid` varchar(45) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `fam_thash` varchar(24) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `fam_khash` binary(16) DEFAULT NULL,
  `fam_empty` tinyint DEFAULT '7',
  `fam_rfasize` int DEFAULT '0',
  `fam_addedby` int DEFAULT '0',
  `fam_lastby` int DEFAULT '0',
  `fam_warn` text CHARACTER SET utf8,
  `fam_kwarn` bit(40) NOT NULL DEFAULT b'0',
  `fam_xmlver` int DEFAULT '0' COMMENT 'Version of XML data',
  `fam_rfaver` int DEFAULT '0' COMMENT 'RFA/RVT version',
  `fam_pngver` int DEFAULT '0' COMMENT 'Thumbanil version',
  `fam_seti` tinyint NOT NULL DEFAULT '0',
  `fam_check` tinyint(1) NOT NULL DEFAULT '-1',
  `fam_lpath` varchar(330) CHARACTER SET utf8 DEFAULT NULL,
  `fam_instances` float DEFAULT '0',
  `fam_changes` int DEFAULT '0',
  `fam_index` tinyint NOT NULL DEFAULT '0',
  `fam_nested` smallint UNSIGNED NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `ifm_libaccess` (
  `lua_lib` int NOT NULL,
  `lua_user` int NOT NULL,
  `lua_collection` int UNSIGNED DEFAULT NULL,
  `lua_last` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `ifm_libraries` (
  `lib_id` int UNSIGNED NOT NULL,
  `lib_link` int DEFAULT NULL,
  `lib_name` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
  `lib_account` int DEFAULT '10',
  `lib_number` int DEFAULT '0',
  `lib_type` enum('Order','Model','Library','Collection') CHARACTER SET ascii DEFAULT NULL,
  `lib_collated` tinyint(1) NOT NULL DEFAULT '0',
  `lib_customer` int DEFAULT '0',
  `lib_short` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_chprice` float DEFAULT NULL,
  `lib_chwhen` int DEFAULT NULL,
  `lib_chpercen` int DEFAULT NULL,
  `lib_revit` year DEFAULT NULL,
  `lib_families` int DEFAULT '0',
  `lib_rfasize` int NOT NULL DEFAULT '0',
  `lib_lastmod` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lib_created` datetime DEFAULT CURRENT_TIMESTAMP,
  `lib_archived` tinyint DEFAULT '0',
  `lib_status` tinyint DEFAULT '0',
  `lib_nrule` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `lib_control` varchar(32) CHARACTER SET latin1 DEFAULT NULL,
  `lib_rorgname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rorgdesc` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rbname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rauthor` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_ridate` varchar(255) CHARACTER SET utf16 COLLATE utf16_unicode_ci DEFAULT NULL,
  `lib_rstatus` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rcname` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_raddress` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rcentral` varchar(330) CHARACTER SET utf8 DEFAULT NULL,
  `lib_runc` varchar(330) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rserver_type` tinyint(1) DEFAULT NULL,
  `lib_rserver` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_rsize` int UNSIGNED DEFAULT '0',
  `lib_rproject_guid` binary(16) DEFAULT NULL,
  `lib_rmodel_guid` binary(16) DEFAULT NULL,
  `lib_rregion` enum('US','EMEA') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'US',
  `lib_reid` char(60) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_dirty` tinyint DEFAULT '0',
  `lib_cost` float DEFAULT '0',
  `lib_metric` tinyint DEFAULT '0',
  `lib_rnumber` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_private` tinyint DEFAULT '0',
  `lib_lsync` int DEFAULT '0',
  `lib_sview` tinyint(1) DEFAULT NULL,
  `lib_instances` int DEFAULT NULL,
  `lib_users` int DEFAULT NULL,
  `lib_comments` int DEFAULT NULL,
  `lib_warnings` int DEFAULT '0',
  `lib_readonly` tinyint(1) NOT NULL DEFAULT '0',
  `lib_shareable` tinyint(1) DEFAULT NULL,
  `lib_shared` tinyint(1) DEFAULT NULL,
  `lib_account_orig` int UNSIGNED DEFAULT NULL,
  `lib_owner` int UNSIGNED DEFAULT '0',
  `lib_is_user` tinyint UNSIGNED DEFAULT '0',
  `lib_coords_ns` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_coords_ew` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_coords_elev` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lib_famused` int NOT NULL DEFAULT '0',
  `lib_searchable` tinyint(1) NOT NULL DEFAULT '1',
  `lib_user_modify` tinyint(1) NOT NULL DEFAULT '0',
  `lib_is_local` tinyint NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC;

CREATE TABLE `ifm_shares` (
  `shr_id` int UNSIGNED NOT NULL,
  `shr_library_orig` int UNSIGNED NOT NULL,
  `shr_library_dest` int UNSIGNED DEFAULT NULL,
  `shr_user_orig` int UNSIGNED NOT NULL,
  `shr_user_dest` int UNSIGNED DEFAULT NULL,
  `shr_email` varchar(255) DEFAULT NULL,
  `shr_readonly` tinyint(1) DEFAULT NULL,
  `shr_created` datetime DEFAULT NULL,
  `shr_key` varchar(32) DEFAULT NULL,
  `shr_message` text,
  `shr_accepted` datetime DEFAULT NULL,
  `shr_subdomain` varchar(32) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


ALTER TABLE `ifm_colitems`
  ADD PRIMARY KEY (`cli_collection`,`cli_library`) USING BTREE,
  ADD UNIQUE KEY `single_model` (`cli_library`,`cli_is_model`);

ALTER TABLE `ifm_families`
  ADD PRIMARY KEY (`fam_id`),
  ADD KEY `xmllist` (`fam_library`,`fam_status`),
  ADD KEY `fam_guid` (`fam_guid`,`fam_account`,`fam_empty`) USING BTREE,
  ADD KEY `fam_index` (`fam_library`,`fam_index`,`fam_updated`),
  ADD KEY `fam_updated` (`fam_updated`),
  ADD KEY `library_revitype` (`fam_revitype`,`fam_library`,`fam_archived`,`fam_status`) USING BTREE,
  ADD KEY `lpath` (`fam_account`,`fam_lpath`),
  ADD KEY `parent` (`fam_parent`,`fam_account`),
  ADD KEY `thash` (`fam_thash`),
  ADD KEY `fam_png` (`fam_png`),
  ADD KEY `fam_check` (`fam_check`),
  ADD KEY `suggested` (`fam_name`,`fam_index`,`fam_library`) USING BTREE,
  ADD KEY `brand` (`fam_brand_id`);
ALTER TABLE `ifm_families` ADD FULLTEXT KEY `fam_name` (`fam_name`);

ALTER TABLE `ifm_libaccess`
  ADD PRIMARY KEY (`lua_user`,`lua_lib`),
  ADD KEY `lua_collection` (`lua_collection`);

ALTER TABLE `ifm_libraries`
  ADD PRIMARY KEY (`lib_id`),
  ADD UNIQUE KEY `central` (`lib_account`,`lib_rcentral`),
  ADD UNIQUE KEY `unc_account` (`lib_account`,`lib_runc`),
  ADD UNIQUE KEY `acount_number` (`lib_account`,`lib_number`),
  ADD KEY `lib_link` (`lib_link`),
  ADD KEY `instances` (`lib_type`,`lib_account`,`lib_archived`) USING BTREE;

ALTER TABLE `ifm_shares`
  ADD PRIMARY KEY (`shr_id`),
  ADD UNIQUE KEY `shr_key` (`shr_key`),
  ADD KEY `shr_user_dest` (`shr_user_dest`,`shr_readonly`);


ALTER TABLE `ifm_families`
  MODIFY `fam_id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `ifm_libraries`
  MODIFY `lib_id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `ifm_shares`
  MODIFY `shr_id` int UNSIGNED NOT NULL AUTO_INCREMENT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;