/*
SQLyog Professional v12.09 (64 bit)
MySQL - 10.4.22-MariaDB : Database - ftc_retail_temp
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`ftc_retail_temp` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;

USE `ftc_retail_temp`;

/*Table structure for table `alias` */

DROP TABLE IF EXISTS `alias`;

CREATE TABLE `alias` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `article` varchar(50) NOT NULL,
  `upc` varchar(50) DEFAULT NULL,
  `original_price` varchar(45) DEFAULT NULL,
  `current_price` varchar(45) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`,`article`),
  UNIQUE KEY `article` (`article`),
  UNIQUE KEY `idx_alias_upc` (`upc`)
) ENGINE=InnoDB AUTO_INCREMENT=7649 DEFAULT CHARSET=latin1;

/*Table structure for table `api_requests` */

DROP TABLE IF EXISTS `api_requests`;

CREATE TABLE `api_requests` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `module` varchar(10) DEFAULT NULL,
  `method` varchar(10) DEFAULT NULL,
  `url` varchar(20) DEFAULT NULL,
  `headers` varchar(4) DEFAULT NULL,
  `body` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `failed_jobs` */

DROP TABLE IF EXISTS `failed_jobs`;

CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `invoice_items` */

DROP TABLE IF EXISTS `invoice_items`;

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `order_number` varchar(50) DEFAULT NULL,
  `customer_number` varchar(50) NOT NULL,
  `item_number` varchar(50) DEFAULT NULL,
  `item_name` varchar(255) NOT NULL,
  `commodity_code` varchar(50) DEFAULT NULL,
  `number_of_cartons` int(11) DEFAULT 0,
  `pcs_per_carton` int(11) DEFAULT 0,
  `total_pcs` int(11) DEFAULT 0,
  `unit` varchar(20) DEFAULT NULL,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `country_of_origin` varchar(50) DEFAULT NULL,
  `discount_percentage` decimal(5,2) DEFAULT 0.00,
  `amount` decimal(15,2) NOT NULL,
  `currency` varchar(10) NOT NULL,
  `net_weight` decimal(10,2) DEFAULT NULL,
  `gross_weight` decimal(10,2) DEFAULT NULL,
  `volume` varchar(50) DEFAULT NULL,
  `kc_certificate` varchar(50) DEFAULT NULL,
  `test_status` varchar(50) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1756 DEFAULT CHARSET=latin1;

/*Table structure for table `invoices` */

DROP TABLE IF EXISTS `invoices`;

CREATE TABLE `invoices` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) DEFAULT NULL,
  `inv_date` varchar(10) DEFAULT NULL,
  `load_date` varchar(10) DEFAULT NULL,
  `load_user_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=latin1;

/*Table structure for table `jobs` */

DROP TABLE IF EXISTS `jobs`;

CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_reserved_at_index` (`queue`(191),`reserved_at`)
) ENGINE=InnoDB AUTO_INCREMENT=853 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `migrations` */

DROP TABLE IF EXISTS `migrations`;

CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `product_packages` */

DROP TABLE IF EXISTS `product_packages`;

CREATE TABLE `product_packages` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `product_item_code` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `scan_code` varchar(18) COLLATE utf8mb4_unicode_ci NOT NULL,
  `barcode_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `format` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `pack_size` int(11) NOT NULL,
  `net_weight` int(11) NOT NULL,
  `gross_weight` int(11) NOT NULL,
  `width` int(11) NOT NULL,
  `height` int(11) NOT NULL,
  `length` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ITEM_CODE` (`product_item_code`,`scan_code`),
  KEY `idx_product_packages_product_item_code` (`product_item_code`),
  KEY `idx_product_packages_scan_code` (`scan_code`),
  KEY `idx_product_packages_barcode_type` (`barcode_type`)
) ENGINE=InnoDB AUTO_INCREMENT=26042 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `products` */

DROP TABLE IF EXISTS `products`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `item_code` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_description` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `main_category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sub_category` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `uom` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `img` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_fragile` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_perishable` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_seasonal` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `fixed_item` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_fifo` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_heavy` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_dangerous` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_food` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `storage_advice` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `legal_entity_Code` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country_code` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `language_code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `item_status` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age_restricted` varchar(1) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sales_tax_code` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sales_tax_percent` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bottle_deposit_required` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ts_last_update` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL,
  `currency_code` varchar(5) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price_inc_tax` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price_exc_tax` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cost` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `price` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sap_article` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `to_sync` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `ITEM_CODE` (`item_code`),
  KEY `idx_products_main_category` (`main_category`),
  KEY `idx_products_item_code` (`item_code`)
) ENGINE=InnoDB AUTO_INCREMENT=12397 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `retail_logs` */

DROP TABLE IF EXISTS `retail_logs`;

CREATE TABLE `retail_logs` (
  `id` bigint(100) NOT NULL AUTO_INCREMENT,
  `store_code` varchar(50) DEFAULT NULL,
  `date` varchar(12) DEFAULT NULL,
  `logs` longtext DEFAULT NULL,
  `module` varchar(15) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=376 DEFAULT CHARSET=latin1;

/*Table structure for table `retail_sales_log` */

DROP TABLE IF EXISTS `retail_sales_log`;

CREATE TABLE `retail_sales_log` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `receipt_id` varchar(55) DEFAULT NULL,
  `partner_code` varchar(15) DEFAULT NULL,
  `gross` varchar(11) DEFAULT NULL,
  `net` varchar(11) DEFAULT NULL,
  `tax` varchar(11) DEFAULT NULL,
  `line_item_count` varchar(11) DEFAULT NULL,
  `payload` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2189 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `retail_sales_raw` */

DROP TABLE IF EXISTS `retail_sales_raw`;

CREATE TABLE `retail_sales_raw` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `transaction_day` varchar(15) DEFAULT NULL,
  `transaction_time` varchar(15) DEFAULT NULL,
  `trading_day` varchar(15) DEFAULT NULL,
  `currency` varchar(11) DEFAULT NULL,
  `partner_code` varchar(10) DEFAULT NULL,
  `pos_terminal_id` varchar(20) DEFAULT NULL,
  `receipt_id` varchar(20) DEFAULT NULL,
  `channel_id` varchar(10) DEFAULT NULL,
  `store_code` varchar(10) DEFAULT NULL,
  `line_number` varchar(15) DEFAULT NULL,
  `line_id` varchar(15) DEFAULT NULL,
  `article` varchar(20) DEFAULT NULL,
  `is_returned` varchar(1) DEFAULT NULL,
  `is_scanned` varchar(1) DEFAULT NULL,
  `is_cancelled` varchar(1) DEFAULT NULL,
  `return_code` varchar(15) DEFAULT NULL,
  `qty` varchar(11) DEFAULT NULL,
  `gross` varchar(11) DEFAULT NULL,
  `tax` varchar(11) DEFAULT NULL,
  `net` varchar(11) DEFAULT NULL,
  `ftc_item_code` varchar(10) DEFAULT NULL,
  `ftc_wh_code` varchar(10) DEFAULT NULL,
  `is_sent` int(1) DEFAULT 0,
  `for_update` int(1) DEFAULT 0,
  `discountJson` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17324 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `retail_stock_log` */

DROP TABLE IF EXISTS `retail_stock_log`;

CREATE TABLE `retail_stock_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ftc_item_code` varchar(15) DEFAULT NULL,
  `ftc_wh_code` varchar(15) DEFAULT NULL,
  `partner_code` varchar(10) DEFAULT NULL,
  `stocktake_date` varchar(12) DEFAULT NULL,
  `payload` longtext DEFAULT NULL,
  `qty` varchar(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35450 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `retail_stock_raw` */

DROP TABLE IF EXISTS `retail_stock_raw`;

CREATE TABLE `retail_stock_raw` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `store_code` varchar(15) DEFAULT NULL,
  `partner_code` varchar(15) DEFAULT NULL,
  `article` varchar(15) DEFAULT NULL,
  `qty` varchar(15) DEFAULT NULL,
  `ftc_wh_code` varchar(15) DEFAULT NULL,
  `ftc_item_code` varchar(15) DEFAULT NULL,
  `stocktake_date` varchar(15) DEFAULT NULL,
  `is_sent` int(1) DEFAULT 0,
  `for_update` int(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54788 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `retail_traffic_data` */

DROP TABLE IF EXISTS `retail_traffic_data`;

CREATE TABLE `retail_traffic_data` (
  `id` bigint(211) NOT NULL AUTO_INCREMENT,
  `ftc_wh_code` varchar(15) DEFAULT NULL,
  `store_code` varchar(15) DEFAULT NULL,
  `store_date` varchar(15) DEFAULT NULL,
  `time_in` varchar(15) DEFAULT NULL,
  `entrance_desc` varchar(50) DEFAULT NULL,
  `aisle` varchar(15) DEFAULT NULL,
  `incoming` varchar(12) DEFAULT NULL,
  `outgoing` varchar(12) DEFAULT NULL,
  `is_sent` int(1) DEFAULT 0,
  `for_update` int(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2191 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `retail_traffic_logs` */

DROP TABLE IF EXISTS `retail_traffic_logs`;

CREATE TABLE `retail_traffic_logs` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `store_code` varchar(10) DEFAULT NULL,
  `partner_code` varchar(10) DEFAULT NULL,
  `traffic` int(11) DEFAULT NULL,
  `store_date` varchar(10) DEFAULT NULL,
  `payload` longtext DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4;

/*Table structure for table `sessions` */

DROP TABLE IF EXISTS `sessions`;

CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payload` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL,
  UNIQUE KEY `sessions_id_unique` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `users` */

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `username` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `store_access` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `abilities` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `has_logged_in` tinyint(1) DEFAULT 0,
  `remember_token` varchar(200) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=468 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `warehouse` */

DROP TABLE IF EXISTS `warehouse`;

CREATE TABLE `warehouse` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sap_wh_code` varchar(11) DEFAULT NULL,
  `ftc_wh_code` varchar(11) DEFAULT NULL,
  `store_name` varchar(20) DEFAULT NULL,
  `status` int(11) DEFAULT NULL,
  `join_date_val` int(11) DEFAULT NULL,
  `join_date` varchar(12) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
