CREATE TABLE IF NOT EXISTS `#__djc2_users_extra_fields` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `is_core` tinyint(4) NOT NULL DEFAULT '0',
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` text COLLATE utf8mb4_unicode_ci,
  `description` text COLLATE utf8mb4_unicode_ci,
  `placeholder` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `attributes` text COLLATE utf8mb4_unicode_ci,
  `alias` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_type` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'A',
  `imagelabel` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `target_fieldset` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'djcatalog2profile',
  `ordering` int(11) NOT NULL,
  `in_register` tinyint(4) NOT NULL DEFAULT '0',
  `in_profile` tinyint(4) NOT NULL DEFAULT '1',
  `in_checkout` tinyint(4) NOT NULL DEFAULT '1',
  `in_query` tinyint(4) NOT NULL DEFAULT '1',
  `in_delivery` tinyint(4) NOT NULL DEFAULT '0',
  `params` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_type` (`type`)
) AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `#__djc2_users_extra_fields` (`id`, `is_core`, `name`, `label`, `description`, `placeholder`, `attributes`, `alias`, `client_type`, `imagelabel`, `type`, `target_fieldset`, `ordering`, `in_register`, `in_profile`, `in_checkout`, `in_query`, `in_delivery`, `params`) VALUES
(2, 1, 'User\'s ID', 'User\'s ID', '', '', '', 'user_id', 'A', NULL, 'core:djcuser', 'djcatalog2profile', 2, 0, 0, 1, 1, 0, NULL),
(3, 1, 'E-Mail', 'COM_DJCATALOG2_UP_EMAIL', '', '', '', 'email', 'A', NULL, 'core:email', 'djcatalog2profile', 3, 0, 0, 2, 2, 0, NULL),
(4, 1, 'First Name', 'COM_DJCATALOG2_UP_FIRSTNAME', '', '', '', 'firstname', 'A', NULL, 'text', 'djcatalog2profile', 4, 2, 1, 2, 2, 0, NULL),
(5, 1, 'Last Name', 'COM_DJCATALOG2_UP_LASTNAME', '', '', '', 'lastname', 'A', NULL, 'text', 'djcatalog2profile', 5, 1, 1, 2, 2, 0, NULL),
(6, 1, 'Company Name', 'COM_DJCATALOG2_UP_COMPANY', '', '', '', 'company', 'W', NULL, 'text', 'djcatalog2profile', 6, 2, 2, 2, 2, 0, NULL),
(7, 1, 'Position', 'COM_DJCATALOG2_UP_POSITION', '', '', '', 'position', 'A', NULL, 'text', 'djcatalog2profile', 7, 0, 1, 1, 1, 0, NULL),
(8, 1, 'Address', 'COM_DJCATALOG2_UP_ADDRESS', '', '', '', 'address', 'A', NULL, 'text', 'djcatalog2profile', 8, 0, 1, 1, 1, 0, NULL),
(9, 1, 'City', 'COM_DJCATALOG2_UP_CITY', '', '', '', 'city', 'A', NULL, 'text', 'djcatalog2profile', 9, 2, 1, 2, 1, 0, NULL),
(10, 1, 'Country', 'COM_DJCATALOG2_UP_COUNTRY', '', '', '', 'country_id', 'A', NULL, 'core:djccountry', 'djcatalog2profile', 10, 0, 1, 1, 1, 0, NULL),
(11, 1, 'State', 'COM_DJCATALOG2_UP_STATE', '', '', '', 'state_id', 'A', NULL, 'core:djccountrystate', 'djcatalog2profile', 11, 0, 1, 1, 1, 0, NULL),
(12, 1, 'Postcode', 'COM_DJCATALOG2_UP_POSTCODE', '', '', '', 'postcode', 'A', NULL, 'text', 'djcatalog2profile', 12, 0, 1, 1, 1, 0, NULL),
(13, 1, 'VAT ID', 'COM_DJCATALOG2_UP_VATID', '', '', '', 'vat_id', 'A', NULL, 'text', 'djcatalog2profile', 13, 0, 1, 1, 1, 0, NULL),
(14, 1, 'Phone', 'COM_DJCATALOG2_UP_PHONE', '', '', '', 'phone', 'A', NULL, 'text', 'djcatalog2profile', 14, 0, 1, 1, 1, 0, NULL),
(15, 1, 'Fax', 'COM_DJCATALOG2_UP_FAX', '', '', '', 'fax', 'A', NULL, 'text', 'djcatalog2profile', 15, 0, 0, 0, 0, 0, NULL),
(16, 1, 'Website', 'COM_DJCATALOG2_UP_WWW', '', '', 'filter=\"url\"', 'www', 'A', NULL, 'text', 'djcatalog2profile', 16, 0, 0, 0, 0, 0, NULL),
(17, 1, 'Customer Group', 'COM_DJCATALOG2_UP_CUSTOMERGROUP', '', '', '', 'customer_group_id', 'A', NULL, 'core:djccustomergroup', 'djcatalog2profile', 17, 0, 1, 0, 0, 0, NULL),
(1, 1, 'Client Type', 'COM_DJCATALOG2_UP_CLIENT_TYPE', '', '', '', 'client_type', 'A', NULL, 'core:radio', 'djcatalog2profile', 1, 1, 1, 2, 2, 0, NULL),
(18, 1, 'Terms of Service', 'COM_DJCATALOG2_TOS', '', '', '', 'tos', 'A', NULL, 'core:checkbox', 'djcatalog2statements', 18, 2, 2, 2, 2, 0, NULL),
(19, 1, 'GDPR Policy', 'COM_DJCATALOG2_UP_GDPR_POLICY_AGREE', '', '', '', 'gdpr_policy', 'A', NULL, 'core:checkbox', 'djcatalog2statements', 19, 2, 2, 2, 2, 0, NULL),
(20, 1, 'GDPR Agreement', 'COM_DJCATALOG2_UP_GDPR_AGREE', '', '', '', 'gdpr_agreement', 'A', NULL, 'core:checkbox', 'djcatalog2statements', 20, 2, 2, 2, 2, 0, NULL);

CREATE TABLE IF NOT EXISTS `#__djc2_users_extra_fields_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `field_id` int(11) NOT NULL,
  `value` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ordering` int(11) NOT NULL,
  `params` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_field_id` (`field_id`),
  KEY `idx_value` (`value`),
  KEY `idx_ordering` (`ordering`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__djc2_users_extra_fields_values_date` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `item_field` (`item_id`,`field_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__djc2_users_extra_fields_values_int` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_item_field` (`item_id`,`field_id`),
  KEY `idx_value` (`value`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `#__djc2_users_extra_fields_values_text` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `field_id` int(11) NOT NULL,
  `value` text COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_item_field` (`item_id`,`field_id`)
) DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

