-- Adminer 4.7.1 MySQL dump

-----
-- replace {PREFIX}
-----

SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';

CREATE TABLE `{PREFIX}brands` (
  `brand_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `brand_name` varchar(100) NOT NULL,
  `brand_slug` varchar(100) NOT NULL,
  `brand_group` varchar(3) DEFAULT NULL,
  `brand_parent` bigint(20) DEFAULT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`brand_id`),
  UNIQUE KEY `brand_uniq` (`brand_name`,`brand_slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}count` (
  `count_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `count_key` varchar(50) NOT NULL,
  `count_total` int(11) NOT NULL,
  PRIMARY KEY (`count_id`),
  UNIQUE KEY `count_key` (`count_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `reference_id` varchar(35) NOT NULL,
  `campaign_id` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `gtin` varchar(14) NOT NULL,
  `mpn` varchar(20) DEFAULT NULL,
  `price_currency` varchar(3) NOT NULL,
  `price_amount` decimal(6,2) NOT NULL,
  `price_amount_sale` decimal(6,2) DEFAULT NULL,
  `url` varchar(255) NOT NULL,
  `quantity` int(4) DEFAULT NULL,
  `availability` varchar(50) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `weight` varchar(50) DEFAULT NULL,
  `views` int(10) NOT NULL DEFAULT 0,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_uniq` (`reference_id`,`campaign_id`),
  KEY `name` (`name`),
  KEY `gtin` (`gtin`),
  KEY `mpn` (`mpn`),
  KEY `views` (`views`),
  KEY `reference_id` (`reference_id`),
  FULLTEXT KEY `name_description_ft` (`name`,`description`),
  FULLTEXT KEY `name_ft` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


SET NAMES utf8mb4;

CREATE TABLE `{PREFIX}products_code` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `gtin` varchar(14) NOT NULL,
  `mpn` varchar(20) NOT NULL,
  `counter` bigint(20) NOT NULL,
  `sources` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`sources`)),
  PRIMARY KEY (`id`),
  UNIQUE KEY `gtin_mpn` (`gtin`,`mpn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}products_mostviewed` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}products_random` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}product_images` (
  `image_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `product_id` bigint(20) NOT NULL,
  `image_url` varchar(255) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL ON UPDATE current_timestamp(),
  `date_checked` datetime DEFAULT NULL,
  PRIMARY KEY (`image_id`),
  UNIQUE KEY `product_image_uniq` (`product_id`,`image_url`),
  KEY `product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}rel_product_brand` (
  `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `brand_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`rel_id`),
  UNIQUE KEY `product_term_uniq` (`product_id`,`brand_id`),
  KEY `brand_id` (`brand_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}rel_product_shipping` (
  `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `shipping_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`rel_id`),
  UNIQUE KEY `product_shipping_uniq` (`product_id`,`shipping_id`),
  KEY `shipping_id` (`shipping_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `{PREFIX}rel_product_term` (
  `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `term_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`rel_id`),
  UNIQUE KEY `product_term_uniq` (`product_id`,`term_id`),
  KEY `term_id` (`term_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}rel_source_product` (
  `rel_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `source_id` bigint(20) NOT NULL,
  `product_id` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`rel_id`),
  UNIQUE KEY `source_product` (`source_id`,`product_id`),
  KEY `product_id` (`product_id`),
  KEY `source_id` (`source_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}settings` (`id` int(9), `class` varchar(255), `key` varchar(255), `value` text, `type` varchar(31), `context` varchar(255), `created_at` datetime, `updated_at` datetime);


CREATE TABLE `{PREFIX}shippings` (
  `shipping_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `status` varchar(1) NOT NULL DEFAULT 'A',
  `shipping_country` varchar(3) NOT NULL DEFAULT '',
  `shipping_cost` decimal(6,2) DEFAULT NULL,
  `shipping_service` varchar(255) NOT NULL DEFAULT '',
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`shipping_id`),
  UNIQUE KEY `shipping_uniq` (`shipping_country`,`shipping_cost`,`shipping_service`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;


CREATE TABLE `{PREFIX}sources` (`source_id` bigint(20), `status` varchar(1), `source_campaign` varchar(75), `source_slug` varchar(75), `import` tinyint(1), `date_last_import` datetime, `date_created` datetime, `source_cart_freeshipping` decimal(6,2), `source_cart_shipping` decimal(6,2), `source_cart_minorder` decimal(6,2), `source_commission` decimal(4,2), `data_minauthonline` varchar(200));


CREATE TABLE `{PREFIX}sources_data` (`rel_id` bigint(20), `source_id` bigint(20), `key` varchar(25), `value` varchar(200));


CREATE TABLE `{PREFIX}terms` (
  `term_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `term_name` varchar(100) NOT NULL,
  `term_slug` varchar(100) NOT NULL,
  `term_parent` bigint(20) NOT NULL,
  `date_created` datetime DEFAULT NULL,
  `date_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`term_id`),
  UNIQUE KEY `term_uniq` (`term_name`,`term_slug`,`term_parent`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}terms_alias` (
  `alias_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `term_id` bigint(20) NOT NULL,
  `term_alias_id` bigint(20) NOT NULL,
  PRIMARY KEY (`alias_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci;


CREATE TABLE `{PREFIX}vouchers` (`voucher_id` bigint(20), `source_id` bigint(20), `voucher_reference_id` varchar(35), `voucher_code` varchar(100), `is_active` int(1), `voucher_date_from` datetime, `voucher_date_to` datetime, `voucher_name` varchar(75), `voucher_description` tinytext, `voucher_url` varchar(255), `voucher_condition` varchar(255), `voucher_discount_fixed` float(5,2), `voucher_discount_variable` float(5,2));

DROP TABLE IF EXISTS `{PREFIX}settings`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `{PREFIX}settings` AS select `settings`.`id` AS `id`,`settings`.`class` AS `class`,`settings`.`key` AS `key`,`settings`.`value` AS `value`,`settings`.`type` AS `type`,`settings`.`context` AS `context`,`settings`.`created_at` AS `created_at`,`settings`.`updated_at` AS `updated_at` from `settings`;

DROP TABLE IF EXISTS `{PREFIX}sources`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `{PREFIX}sources` AS select `s`.`source_id` AS `source_id`,`s`.`status` AS `status`,`s`.`source_campaign` AS `source_campaign`,`s`.`source_slug` AS `source_slug`,`r`.`import` AS `import`,`r`.`date_last_import` AS `date_last_import`,`s`.`date_created` AS `date_created`,`s`.`source_cart_freeshipping` AS `source_cart_freeshipping`,`s`.`source_cart_shipping` AS `source_cart_shipping`,`s`.`source_cart_minorder` AS `source_cart_minorder`,`s`.`source_commission` AS `source_commission`,max(if(`sd`.`key` = 'minauthonline',`sd`.`value`,NULL)) AS `data_minauthonline` from ((`sources` `s` left join `rel_site_source` `r` on(`r`.`source_id` = `s`.`source_id`)) left join `sources_data` `sd` on(`sd`.`source_id` = `s`.`source_id`)) where `r`.`site_id` = {SITEID} group by `s`.`source_id`;

DROP TABLE IF EXISTS `{PREFIX}sources_data`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `{PREFIX}sources_data` AS select `sd`.`rel_id` AS `rel_id`,`sd`.`source_id` AS `source_id`,`sd`.`key` AS `key`,`sd`.`value` AS `value` from ((`sources` `s` left join `rel_site_source` `r` on(`r`.`source_id` = `s`.`source_id`)) left join `sources_data` `sd` on(`sd`.`source_id` = `s`.`source_id`)) where `r`.`site_id` = {SITEID} and `sd`.`source_id` is not null order by `sd`.`source_id`;

DROP TABLE IF EXISTS `{PREFIX}vouchers`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `{PREFIX}vouchers` AS select `v`.`voucher_id` AS `voucher_id`,`v`.`source_id` AS `source_id`,`v`.`voucher_reference_id` AS `voucher_reference_id`,`v`.`voucher_code` AS `voucher_code`,if(`v`.`voucher_date_from` <= curdate() and `v`.`voucher_date_to` >= curdate(),1,0) AS `is_active`,`v`.`voucher_date_from` AS `voucher_date_from`,`v`.`voucher_date_to` AS `voucher_date_to`,`v`.`voucher_name` AS `voucher_name`,`v`.`voucher_description` AS `voucher_description`,`v`.`voucher_url` AS `voucher_url`,`v`.`voucher_condition` AS `voucher_condition`,`v`.`voucher_discount_fixed` AS `voucher_discount_fixed`,`v`.`voucher_discount_variable` AS `voucher_discount_variable` from (`vouchers` `v` left join `rel_site_source` `r` on(`r`.`source_id` = `v`.`source_id`)) where `r`.`site_id` = {SITEID};

-- 2025-01-31 10:42:53