

-- Captcha permission in preference table
INSERT INTO `preferences` (`id`, `category`, `field`, `value`) VALUES (NULL, 'preference', 'captcha', 'disable');

-- brands table
CREATE TABLE `brands`
(
    `id`          int(11) unsigned NOT NULL AUTO_INCREMENT,
    `vendor_id`   bigint(20) DEFAULT NULL,
    `name`        varchar(50) NOT NULL,
    `description` text,
    `status`      varchar(10) NOT NULL DEFAULT 'Active',
    `created_at`  timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `brands_name_unique` (`name`),
    KEY           `brands_name_index` (`name`),
    KEY           `brands_status_index` (`status`),
    KEY           `brands_vendor_id_foreign_idx` (`vendor_id`),
    CONSTRAINT `brands_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- categories table
CREATE TABLE `categories`
(
    `id`            int(11) NOT NULL AUTO_INCREMENT,
    `name`          varchar(100) NOT NULL,
    `slug`          varchar(120) NOT NULL,
    `parent_id`     int(11) DEFAULT NULL,
    `order_by`      int(8) unsigned NOT NULL,
    `is_searchable` tinyint(1) NOT NULL DEFAULT '0',
    `item_counts`   int(8) unsigned DEFAULT '0',
    `status`        varchar(10)  NOT NULL DEFAULT 'Active',
    `created_at`    timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY             `categories_name_index` (`name`),
    KEY             `categories_slug_index` (`slug`),
    KEY             `categories_order_by_index` (`order_by`),
    KEY             `categories_status_index` (`status`),
    KEY             `categories_is_searchable_index` (`is_searchable`),
    KEY             `categories_parent_id_foreign_idx` (`parent_id`),
    CONSTRAINT `categories_parent_id_foreign` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- attribute_groups table
CREATE TABLE `attribute_groups`
(
    `id`         int(11) NOT NULL AUTO_INCREMENT,
    `vendor_id`  bigint(20) DEFAULT NULL,
    `name`       varchar(50) NOT NULL,
    `summary`    varchar(255) DEFAULT NULL,
    `status`     varchar(10) NOT NULL DEFAULT 'Active',
    `created_at` timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `attribute_groups_name_unique` (`name`),
    KEY          `attribute_groups_name_index` (`name`),
    KEY          `attribute_groups_status_index` (`status`),
    KEY          `attribute_groups_vendor_id_foreign_idx` (`vendor_id`),
    CONSTRAINT `attribute_groups_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- attribute_values table
CREATE TABLE `attribute_values`
(
    `id`           bigint(20) NOT NULL AUTO_INCREMENT,
    `attribute_id` bigint(20) NOT NULL,
    `value`        varchar(50) NOT NULL DEFAULT '',
    `order_by`     int(8) NOT NULL,
    `status`       varchar(8)  NOT NULL DEFAULT 'Active',
    `created_at`   timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY            `attribute_values_value_index` (`value`),
    KEY            `attribute_values_status_index` (`status`),
    KEY            `attribute_values_attribute_id_foreign_idx` (`attribute_id`),
    CONSTRAINT `attribute_values_attribute_id_foreign` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- attributes table
CREATE TABLE `attributes`
(
    `id`                 bigint(20) NOT NULL AUTO_INCREMENT,
    `attribute_group_id` int(11) NOT NULL,
    `category_id`        int(11) NOT NULL,
    `name`               varchar(100) NOT NULL,
    `type`               varchar(30)  NOT NULL,
    `status`             varchar(8)   NOT NULL DEFAULT 'Active',
    `is_filterable`      tinyint(1) NOT NULL DEFAULT '0',
    `is_required`        tinyint(1) NOT NULL DEFAULT '0',
    `created_at`         timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY                  `attributes_name_index` (`name`),
    KEY                  `attributes_status_index` (`status`),
    KEY                  `attributes_attribute_group_id_foreign_idx` (`attribute_group_id`),
    KEY                  `attributes_category_id_foreign_idx` (`category_id`),
    KEY                  `attributes_type_index` (`type`),
    CONSTRAINT `attributes_attribute_group_id_foreign` FOREIGN KEY (`attribute_group_id`) REFERENCES `attribute_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `attributes_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- category_attributes table
CREATE TABLE `category_attributes`
(
    `category_id`  int(11) NOT NULL,
    `attribute_id` bigint(20) NOT NULL,
    KEY            `category_attributes_category_id_foreign_idx` (`category_id`),
    KEY            `category_attributes_attribute_id_foreign_idx` (`attribute_id`),
    CONSTRAINT `category_attributes_attribute_id_foreign` FOREIGN KEY (`attribute_id`) REFERENCES `attributes` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `category_attributes_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- items table
CREATE TABLE `items`
(
    `id`                         bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `code`                  varchar(120)   NOT NULL,
    `name`                       varchar(255)   NOT NULL,
    `description`                text,
    `summary`                    varchar(255)            DEFAULT NULL,
    `video_url`                  varchar(255)            DEFAULT NULL,
    `review_count`               smallint(5) unsigned DEFAULT '0',
    `review_average`             decimal(6, 2)           DEFAULT NULL,
    `availablity_from`           varchar(50)             DEFAULT NULL,
    `availablity_to`             varchar(50)             DEFAULT NULL,
    `vendor_id`                  bigint(20) DEFAULT NULL,
    `brand_id`                   int(11) unsigned DEFAULT NULL,
    `status`                     varchar(8)     NOT NULL DEFAULT 'Active',
    `is_virtual`                 tinyint(1) NOT NULL DEFAULT '0',
    `is_shippable`               tinyint(1) NOT NULL DEFAULT '0',
    `is_shareable`               tinyint(1) NOT NULL DEFAULT '0',
    `favourite_count`            int(5) DEFAULT NULL,
    `total_wish`                 int(5) DEFAULT NULL,
    `price`                      decimal(16, 8)          DEFAULT NULL,
    `discount_amount`            decimal(16, 8)          DEFAULT NULL,
    `discount_type`              varchar(20)             DEFAULT NULL,
    `discounted_price`           decimal(16, 8)          DEFAULT NULL,
    `discount_from`              varchar(50)             DEFAULT NULL,
    `discount_to`                varchar(50)             DEFAULT NULL,
    `maximum_discount_amount`    decimal(16, 8) NOT NULL,
    `minimum_order_for_discount` varchar(45)    NOT NULL,
    `sku`                        varchar(45)    NOT NULL,
    `is_inventory_enabled`       tinyint(1) NOT NULL DEFAULT '0',
    `item_quantity`              decimal(16, 8) NOT NULL,
    `stock_availability`         decimal(16, 8) NOT NULL,
    `created_at`                 timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`                 timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY                          `items_code_index` (`code`),
    KEY                          `items_name_index` (`name`),
    KEY                          `items_availablity_from_index` (`availablity_from`),
    KEY                          `items_availablity_to_index` (`availablity_to`),
    KEY                          `items_status_index` (`status`),
    KEY                          `items_price_index` (`price`),
    KEY                          `items_discount_amount_index` (`discount_amount`),
    KEY                          `items_discount_type_index` (`discount_type`),
    KEY                          `items_discounted_price_index` (`discounted_price`),
    KEY                          `items_discount_from_index` (`discount_from`),
    KEY                          `items_discount_to_index` (`discount_to`),
    KEY                          `items_vendor_id_foreign_idx` (`vendor_id`),
    KEY                          `items_brand_id_foreign_idx` (`brand_id`),
    CONSTRAINT `items_brand_id_foreign` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `items_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- option_groups table
CREATE TABLE `option_groups`
(
    `id`          bigint(20) NOT NULL AUTO_INCREMENT,
    `vendor_id`   bigint(20) DEFAULT NULL,
    `category_id` int(11) NOT NULL,
    `name`        varchar(50) NOT NULL,
    `type`        varchar(30) NOT NULL,
    `is_required` tinyint(1) NOT NULL DEFAULT '0',
    `created_at`  timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY           `option_groups_name_index` (`name`),
    KEY           `option_groups_type_index` (`type`),
    KEY           `option_groups_vendor_id_foreign_idx` (`vendor_id`),
    KEY           `option_groups_category_id_idx` (`category_id`),
    CONSTRAINT `option_groups_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `option_groups_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- options table
CREATE TABLE `options`
(
    `id`              bigint(20) NOT NULL AUTO_INCREMENT,
    `option_group_id` bigint(20) NOT NULL,
    `option`          varchar(191)            DEFAULT NULL,
    `is_default`      tinyint(1) NOT NULL DEFAULT '0',
    `price`           decimal(16, 8) NOT NULL,
    `price_type`      varchar(10)    NOT NULL,
    `status`          varchar(8)     NOT NULL DEFAULT 'Active',
    `order_by`        int(8) DEFAULT NULL,
    `created_at`      timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY               `options_option_index` (`option`),
    KEY               `options_price_index` (`price`),
    KEY               `options_price_type_index` (`price_type`),
    KEY               `options_status_index` (`status`),
    KEY               `options_option_group_id_foreign_idx` (`option_group_id`),
    CONSTRAINT `options_option_group_id_foreign` FOREIGN KEY (`option_group_id`) REFERENCES `option_groups` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- tags table
CREATE TABLE `tags`
(
    `id`         bigint(20) NOT NULL AUTO_INCREMENT,
    `name`       varchar(50) NOT NULL,
    `vendor_id`   bigint(20) DEFAULT NULL,
    `item_counts` int(8) unsigned DEFAULT '0',
    `status`     varchar(8)  NOT NULL DEFAULT 'Active',
    `created_at` timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `tags_name_unique` (`name`),
    KEY          `tags_status_index` (`status`),
    KEY          `tags_vendor_id_foreign_idx` (`vendor_id`),
    CONSTRAINT `tags_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- coupons table
CREATE TABLE `coupons`
(
    `id`                      bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name`                    varchar(120)   NOT NULL,
    `code`                    varchar(100)   NOT NULL,
    `discount_type`           varchar(15)    NOT NULL,
    `discount_amount`         decimal(16, 8) NOT NULL,
    `maximum_discount_amount` decimal(16, 8)          DEFAULT NULL,
    `start_date`              date           NOT NULL,
    `end_date`                date           NOT NULL,
    `status`                  varchar(10)    NOT NULL DEFAULT 'Inactive',
    `created_at`              timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`              timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY                       `coupons_name_index` (`name`),
    KEY                       `coupons_code_index` (`code`),
    KEY                       `coupons_discount_type_index` (`discount_type`),
    KEY                       `coupons_discount_amount_index` (`discount_amount`),
    KEY                       `coupons_start_date_index` (`start_date`),
    KEY                       `coupons_end_date_index` (`end_date`),
    KEY                       `coupons_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- favourites table
CREATE TABLE `favourites`
(
    `id`            int(11) unsigned NOT NULL AUTO_INCREMENT,
    `item_id`       bigint(20) unsigned NOT NULL,
    `user_id`       bigint(20) NOT NULL,
    `ip_address`    varchar(16) NOT NULL,
    `browser_agent` varchar(12)          DEFAULT NULL,
    `created_at`    timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY             `favourites_ip_address_index` (`ip_address`),
    KEY             `favourites_browser_agent_index` (`browser_agent`),
    KEY             `favourites_item_id_foreign_idx` (`item_id`),
    KEY             `favourites_user_id_foreign_idx` (`user_id`),
    CONSTRAINT `favourites_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `favourites_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- flash_sales table
CREATE TABLE `flash_sales`
(
    `id`              bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `campaign name`   varchar(255)   NOT NULL,
    `item_id`         bigint(20) unsigned NOT NULL,
    `start_date_time` datetime(5) NOT NULL,
    `end_date_time`   datetime(5) NOT NULL,
    `price`           decimal(16, 8) NOT NULL,
    `quantity`        decimal(16, 8) NOT NULL,
    `vendor_id`       bigint(20) DEFAULT NULL,
    `created_at`      timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY               `flash_sales_campaign name_index` (`campaign name`),
    KEY               `flash_sales_price_index` (`price`),
    KEY               `flash_sales_quantity_index` (`quantity`),
    KEY               `flash_sales_start_date_time_index` (`start_date_time`),
    KEY               `flash_sales_end_date_time_index` (`end_date_time`),
    KEY               `flash_sales_item_id_foreign_idx` (`item_id`),
    KEY               `flash_sales_vendor_id_foreign_idx` (`vendor_id`),
    CONSTRAINT `flash_sales_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `flash_sales_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- item_categories table
CREATE TABLE `item_categories`
(
    `item_id`     bigint(20) unsigned NOT NULL,
    `category_id` int(11) NOT NULL,
    KEY           `item_categories_item_id_foreign_idx` (`item_id`),
    KEY           `item_categories_category_id_foreign_idx` (`category_id`),
    CONSTRAINT `item_categories_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `item_categories_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- item_options table
CREATE TABLE `item_options`
(
    `id`          int(11) unsigned NOT NULL AUTO_INCREMENT,
    `item_id`     bigint(20) unsigned NOT NULL,
    `name`        varchar(100) NOT NULL,
    `type`        varchar(15)  NOT NULL,
    `payloads`    text         NOT NULL,
    `is_required` tinyint(1) NOT NULL DEFAULT '0',
    `created_at`  timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY           `item_options_name_index` (`name`),
    KEY           `item_options_type_index` (`type`),
    KEY           `item_options_item_id_foreign_idx` (`item_id`),
    CONSTRAINT `item_options_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- item_tags table
CREATE TABLE `item_tags`
(
    `item_id`    bigint(20) unsigned NOT NULL,
    `tag_id`     bigint(20) NOT NULL,
    KEY          `item_tags_item_id_foreign_idx` (`item_id`),
    KEY          `item_tags_tag_id_foreign_idx` (`tag_id`),
    CONSTRAINT `item_tags_id_foreign` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `item_tags_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- reviews table
CREATE TABLE `reviews`
(
    `id`          bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `comments`    text,
    `rating`      tinyint(1) DEFAULT NULL,
    `reviewed_by` varchar(50)            DEFAULT NULL,
    `user_id`     bigint(20) DEFAULT NULL,
    `item_id`     bigint(20) unsigned NOT NULL,
    `review_time` decimal(5, 3) NOT NULL,
    `is_public`   tinyint(1) NOT NULL DEFAULT '0',
    `status`      varchar(10)   NOT NULL DEFAULT 'Inactive',
    `attachment`  varchar(255)           DEFAULT NULL,
    `created_at`  timestamp     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY           `reviews_status_index` (`status`),
    KEY           `reviews_item_id_foreign_idx` (`item_id`),
    KEY           `reviews_user_id_foreign_idx` (`user_id`),
    CONSTRAINT `reviews_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `reviews_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- wishlist table
CREATE TABLE `wishlists`
(
    `id`            int(11) unsigned NOT NULL AUTO_INCREMENT,
    `item_id`       bigint(20) unsigned NOT NULL,
    `user_id`       bigint(20) NOT NULL,
    `ip_address`    varchar(16) NOT NULL,
    `browser_agent` varchar(12)          DEFAULT NULL,
    `created_at`    timestamp   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY             `wishlist_ip_address_index` (`ip_address`),
    KEY             `wishlist_browser_agent_index` (`browser_agent`),
    KEY             `wishlis_item_id_foreign_idx` (`item_id`),
    KEY             `wishlist_user_id_foreign_idx` (`user_id`),
    CONSTRAINT `wishlis_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `wishlist_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- locations table
CREATE TABLE `locations`
(
    `id`               int(11) unsigned NOT NULL AUTO_INCREMENT,
    `code`             varchar(10)  NOT NULL,
    `name`             varchar(60)  NOT NULL,
    `delivery_address` varchar(100) NOT NULL,
    `email`            varchar(25)           DEFAULT NULL,
    `phone`            varchar
        (18)           DEFAULT NULL,
    `fax`              varchar(25)           DEFAULT NULL,
    `contact`          varchar(60)           DEFAULT NULL,
    `is_active`        tinyint(1) NOT NULL DEFAULT '0',
    `is_default`       tinyint(1) NOT NULL DEFAULT '0',
    `created_at`       timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `locations_code_unique` (`code`),
    KEY                `locations_name_index` (`name`),
    KEY                `locations_email_index` (`email`),
    KEY                `locations_phone_index` (`phone`),
    KEY                `locations_code_index` (`code`),
    KEY                `locations_is_active_index` (`is_active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- stock_management_details table
CREATE TABLE `stock_management_details`
(
    `id`                  bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `stock_management_id` bigint(20) unsigned NOT NULL,
    `item_id`             bigint(20) unsigned NOT NULL,
    `description`         text,
    `quantity`            decimal(16, 8) NOT NULL,
    PRIMARY KEY (`id`),
    KEY                   `stock_management_details_quantity_index` (`quantity`),
    KEY                   `stock_management_details_stock_management_id_foreign_idx` (`stock_management_id`),
    KEY                   `stock_management_details_item_id_foreign_idx` (`item_id`),
    CONSTRAINT `stock_management_details_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `stock_management_details_stock_management_id_foreign` FOREIGN KEY (`stock_management_id`) REFERENCES `stock_managements` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- stock_managements table
CREATE TABLE `stock_managements`
(
    `id`               bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `vendor_id`        bigint(20) DEFAULT NULL,
    `user_id`          bigint(20) NOT NULL,
    `location_id`      int(11) unsigned NOT NULL,
    `transaction_type` varchar(12)             DEFAULT NULL,
    `total_quantity`   decimal(16, 8) NOT NULL,
    `note`             text,
    `created_at`       timestamp      NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY                `stock_managements_transaction_type_index` (`transaction_type`),
    KEY                `stock_managements_total_quantity_index` (`total_quantity`),
    KEY                `stock_managements_user_id_foreign_idx` (`user_id`),
    KEY                `stock_managements_location_id_foreign_idx` (`location_id`),
    KEY                `stock_managements_vendor_id_foreign_idx` (`vendor_id`),
    CONSTRAINT `stock_managements_location_id_foreign` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `stock_managements_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `stock_managements_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- database schema for create table subscription_invoices
CREATE TABLE `subscription_invoices` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `invoice_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `object_type` varchar(45) DEFAULT NULL,
  `object_id` bigint(20) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `transcation_reference` varchar(100) DEFAULT NULL,
  `billing_name` varchar(100) DEFAULT NULL,
  `billing_email` varchar(100) DEFAULT NULL,
  `billing_address` varchar(100) DEFAULT NULL,
  `billing_address_2` varchar(100) DEFAULT NULL,
  `billing_city` varchar(100) DEFAULT NULL,
  `billing_zip` varchar(45) DEFAULT NULL,
  `billing_state` varchar(100) DEFAULT NULL,
  `billing_country` varchar(45) DEFAULT NULL,
  `billing_phone` varchar(45) DEFAULT NULL,
  `billed_amount` decimal(16,8) DEFAULT NULL,
  `received_amount` decimal(16,8) DEFAULT NULL,
  `discount_amount` decimal(16,8) DEFAULT NULL,
  `tax_amount` decimal(16,8) DEFAULT NULL,
  `balance_amount` decimal(16,8) DEFAULT NULL COMMENT 'Balance has to be zero after payment. (Received + discount) - Billied',
  `currency` varchar(45) NOT NULL DEFAULT 'USD',
  `vendor_id` bigint(11) DEFAULT NULL,
  `user_id` bigint(20) DEFAULT NULL,
  `notes` text,
  `is_resolved` int(1) DEFAULT '0',
  `status` varchar(20) DEFAULT 'Draft',
  `charged_customer_name` varchar(100) DEFAULT NULL,
  `charged_amount` decimal(16,8) DEFAULT NULL,
  `charged_billing_method` varchar(20) DEFAULT NULL,
  `charged_email` varchar(100) DEFAULT NULL,
  `charged_card` varchar(40) DEFAULT NULL,
  `charged_at` datetime DEFAULT NULL,
  `created_at` timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `subscription_invoices_invoice_date_index` (`invoice_date`),
  KEY `subscription_invoices_object_type_index` (`object_type`),
  KEY `subscription_invoices_object_id_index` (`object_id`),
  KEY `subscription_invoices_status_index` (`status`),
  KEY `subscription_invoices_is_resolved_index` (`is_resolved`),
  KEY `subscription_invoices_vendor_id_index` (`vendor_id`),
  KEY `subscription_invoices_user_id_index` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- database schema for create table subscription_invoice_items
CREATE TABLE `subscription_invoice_items` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `invoice_id` bigint(20) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `period` varchar(255) DEFAULT NULL,
  `object_type` varchar(45) DEFAULT NULL,
  `object_id` bigint(20) DEFAULT NULL,
  `price` decimal(18,8) DEFAULT NULL,
  `quantity` tinyint(4) DEFAULT NULL,
  `amount` decimal(16,8) DEFAULT NULL,
  `discount` decimal(16,8) DEFAULT NULL,
  `tax_amount` decimal(16,8) DEFAULT NULL,
  `total` decimal(16,8) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `subscription_invoice_items_object_type_index` (`object_type`),
  KEY `subscription_invoice_items_object_id_index` (`object_id`),
  KEY `subscription_invoice_items_invoice_id_index` (`invoice_id`),
  CONSTRAINT `subscription_invoice_items_invoice_id_foreign` FOREIGN KEY (`invoice_id`) REFERENCES `subscription_invoices` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- database schema for create table payment_terms
CREATE TABLE IF NOT EXISTS `payment_terms` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `days_before_due` smallint(6) NOT NULL,
  `is_default` tinyint(4) NOT NULL DEFAULT '0' COMMENT '1 for default;0 for otherwise;',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `payment_terms_name_index` (`name`),
  KEY `payment_terms_days_before_due_index` (`days_before_due`),
  KEY `payment_terms_is_default_index` (`is_default`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



ALTER TABLE `permissions` ADD `controller_path` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL AFTER `name`;

ALTER TABLE `permissions` CHANGE `controller_path` `controller_path` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;

ALTER TABLE `permissions` CHANGE `name` `name` VARCHAR(250) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;


ALTER TABLE `permissions`
  DROP `description`;


  ALTER TABLE `permission_roles` CHANGE `pernission_id` `permission_id` BIGINT(20) NOT NULL;

-- database schema for create table vendors
CREATE TABLE `vendors`
(
    `id`          bigint(20) NOT NULL AUTO_INCREMENT,
    `name`        varchar(100) NOT NULL,
    `email`       varchar(100)          DEFAULT NULL,
    `phone`       varchar(45) NOT NULL,
    `formal_name` varchar(100)          DEFAULT NULL,
    `alias`       varchar(50)  NOT NULL,
    `status`      varchar(15)  NOT NULL DEFAULT 'Pending',
    `website`     varchar(255)          DEFAULT NULL,
    `created_at`  timestamp    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY           `vendors_name_index` (`name`),
    KEY           `vendors_formal_name` (`formal_name`),
    KEY           `vendors_alias_index` (`alias`),
    KEY           `vendors_status_index` (`status`),
    KEY           `vendors_email_index` (`email`)
    KEY           `vendors_phone_index` (`phone`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- --------------------------------------------------------

--
-- Table structure for table `item_attributes`
--

CREATE TABLE IF NOT EXISTS `item_attributes` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `attribute_id` bigint(20) UNSIGNED NOT NULL,
  `payloads` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `item_attributes_item_id_foreign_idx` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--------------------------------------------------------

--
-- Table structure for table `translations`
--

CREATE TABLE IF NOT EXISTS `translations` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `locale` varchar(32) NOT NULL
  `field_name` varchar(255) NOT NULL
  `value` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Constraints for dumped tables
--


-- --------------------------------------------------------

--
-- Table structure for table `item_cross_sales`
--

CREATE TABLE IF NOT EXISTS `item_cross_sales` (
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `cross_sale_item_id` int(11) NOT NULL,
  KEY `item_cross_sales_item_id_idx` (`item_id`),
  KEY `item_cross_sales_cross_sale_item_id_foreign_idx` (`cross_sale_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;


ALTER TABLE `item_cross_sales` ADD CONSTRAINT `item_cross_sales` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;


-- --------------------------------------------------------

--
-- Table structure for table `item_relates`
--

CREATE TABLE IF NOT EXISTS `item_relates` (
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `related_item_id` int(11) NOT NULL,
  KEY `item_relates_related_item_id_foreign_idx` (`related_item_id`),
  KEY `item_relates_item_id_idx` (`item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;


ALTER TABLE `item_relates` ADD CONSTRAINT `item_relates` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;


-- --------------------------------------------------------

--
-- Table structure for table `item_upsales`
--

CREATE TABLE IF NOT EXISTS `item_upsales` (
  `item_id` bigint(20) UNSIGNED NOT NULL,
  `upsale_item_id` int(11) NOT NULL,
  KEY `item_upsales_item_id_idx` (`item_id`) USING BTREE,
  KEY `item_upsales_upsale_item_id_foreign_idx` (`upsale_item_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;

ALTER TABLE `item_upsales` ADD CONSTRAINT `item_upsales` FOREIGN KEY (`item_id`) REFERENCES `items`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;




ALTER TABLE `items` DROP `attribute_ids_with_values`, DROP `option_ids_with_names`, DROP `related_product_ids`, DROP `upsale_sale_item_ids`, DROP `cross_sale_item_ids`;
ALTER TABLE `items` DROP `seo_meta_title`, DROP `seo_meta_description`;



ALTER TABLE `items` ADD `video_url` VARCHAR(255) NULL DEFAULT NULL AFTER `summary`;

-- database schema for create table taxes
CREATE TABLE `taxes`
(
    `id`         int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name`       varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `tax_rate`   decimal(12, 8)                         NOT NULL,
    `is_default` tinyint(1) NOT NULL DEFAULT '0' COMMENT '1 for defaults;\\n0 for otherwise',
    PRIMARY KEY (`id`),
    KEY          `taxes_name_index` (`name`),
    KEY          `taxes_tax_rate_index` (`tax_rate`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

-- --------------------------------------------------------

--
-- Table structure for table `shops`
--

CREATE TABLE IF NOT EXISTS `shops` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `vendor_id` bigint(20) NOT NULL,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fax` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` varchar(1000) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Active' COMMENT 'Active/Inactive;',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `shops_vendor_id_foreign_idx` (`vendor_id`),
  KEY `shops_name_index` (`name`),
  KEY `shops_email_index` (`email`),
  KEY `shops_phone_index` (`phone`),
  KEY `shops_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `shops`
  ADD CONSTRAINT `shops_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--change attribute value
ALTER TABLE `attribute_values`
    ADD UNIQUE INDEX `attribute_values_composit_unique` (`attribute_id` ASC, `value` ASC);

-- change item table
ALTER TABLE `items`
    CHANGE COLUMN `availablity_from` `availablity_from` DATE NULL DEFAULT NULL ,
    CHANGE COLUMN `availablity_to` `availablity_to` DATE NULL DEFAULT NULL ,
    CHANGE COLUMN `discount_from` `discount_from` DATE NULL DEFAULT NULL ,
    CHANGE COLUMN `discount_to` `discount_to` DATE NULL DEFAULT NULL ;

ALTER TABLE `items`
    CHANGE COLUMN `maximum_discount_amount` `maximum_discount_amount` DECIMAL(16,8) NULL ,
    CHANGE COLUMN `minimum_order_for_discount` `minimum_order_for_discount` VARCHAR(45) NULL ,
    CHANGE COLUMN `sku` `sku` VARCHAR(45) NULL ,
    CHANGE COLUMN `item_quantity` `item_quantity` DECIMAL(16,8) NULL ,
    CHANGE COLUMN `stock_availability` `stock_availability` DECIMAL(16,8) NULL ;

ALTER TABLE `items`
    CHANGE COLUMN `availablity_from` `available_from` DATE NULL DEFAULT NULL ,
    CHANGE COLUMN `availablity_to` `available_to` DATE NULL DEFAULT NULL ;

--item attributes changes issue
ALTER TABLE `item_attributes`
    ADD COLUMN `order_by` INT(8) NOT NULL AFTER `attribute_id`,
CHANGE COLUMN `id` `id` BIGINT(20) UNSIGNED NOT NULL ,
CHANGE COLUMN `attribute_id` `attribute_id` BIGINT(20) NOT NULL ,
ADD INDEX `item_attributes_attribute_id_idx` (`attribute_id` ASC);

ALTER TABLE `item_attributes`
    ADD CONSTRAINT `item_attributes_item_id`
        FOREIGN KEY (`item_id`)
            REFERENCES `items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
ADD CONSTRAINT `item_attributes_attribute_id`
  FOREIGN KEY (`attribute_id`)
  REFERENCES `attributes` (`id`)
  ON DELETE CASCADE
  ON UPDATE CASCADE;

ALTER TABLE `item_attributes`
    CHANGE COLUMN `id` `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT ;

ALTER TABLE `item_attributes`
DROP COLUMN `order_by`;

-- item option
ALTER TABLE `item_options`
    ADD COLUMN `order_by` INT(8) NOT NULL AFTER `type`;

--attributes table changes issue
ALTER TABLE `attributes`
    ADD COLUMN `is_required` TINYINT(1) NOT NULL DEFAULT '0' AFTER `is_filterable`;

--items changes
ALTER TABLE `items`
    CHANGE COLUMN `price` `price` DECIMAL(16,8) NULL ;

--item brand id nullable
ALTER TABLE `items`
DROP FOREIGN KEY `items_brand_id_foreign`;
ALTER TABLE `items`
    CHANGE COLUMN `brand_id` `brand_id` INT(11) UNSIGNED NULL ;
ALTER TABLE `items`
    ADD CONSTRAINT `items_brand_id_foreign`
        FOREIGN KEY (`brand_id`)
            REFERENCES `laravel`.`brands` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

-- attribute table add column
ALTER TABLE `attributes`
    ADD COLUMN `description` TEXT NULL AFTER `name`;

-- add column in users table
ALTER TABLE `users`
    ADD COLUMN `sso_service` VARCHAR(45) NULL AFTER `password`,
ADD COLUMN `sso_account_id` VARCHAR(191) NULL AFTER `sso_service`;


-- preference new data
INSERT INTO `preferences` (`id`, `category`, `field`, `value`) VALUES ('55', 'preference', 'sso_service', '["Google"]');

-- reviews table changes issue
ALTER TABLE `reviews`
DROP COLUMN `attachment`,
DROP COLUMN `review_time`;

-- category changes issue
ALTER TABLE `categories`
    ADD COLUMN `is_featured` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_searchable`,
ADD INDEX `categories_is_featureable_index` (`is_featureable` ASC);
;


---create table orders
CREATE TABLE `orders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_type` varchar(15) NOT NULL,
  `invoice_type` varchar(15) NOT NULL,
  `order_type` varchar(15) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `address_id` int(10) unsigned NOT NULL,
  `tax_type` varchar(15) NOT NULL,
  `reference` varchar(45) NOT NULL,
  `order_reference_id` bigint(20) unsigned DEFAULT NULL,
  `note` text,
  `order_date` date NOT NULL,
  `due_date` date DEFAULT NULL,
  `payment_method_id` int(10) unsigned DEFAULT NULL,
  `discount_on` varchar(15) NOT NULL,
  `currency_id` int(10) unsigned NOT NULL,
  `exchange_rate` decimal(16,8) NOT NULL,
  `has_tax` tinyint(1) NOT NULL DEFAULT '0',
  `has_description` tinyint(1) NOT NULL DEFAULT '0',
  `has_item_discount` tinyint(1) NOT NULL DEFAULT '0',
  `has_hsn` tinyint(1) NOT NULL DEFAULT '0',
  `has_other_discount` tinyint(1) NOT NULL DEFAULT '0',
  `has_shipping_charge` tinyint(1) NOT NULL DEFAULT '0',
  `leave_door` tinyint(1) DEFAULT NULL,
  `has_custom_charge` varchar(45) NOT NULL DEFAULT '0',
  `other_discount_amount` decimal(16,8) DEFAULT '0.00000000',
  `other_discount_type` varchar(45) DEFAULT NULL,
  `shipping_charge` decimal(16,8) DEFAULT NULL,
  `custom_charge_title` varchar(200) DEFAULT NULL,
  `custom_charge_amount` decimal(16,8) NULL,
  `total` decimal(16,8) NOT NULL,
  `paid` decimal(16,8) NOT NULL,
  `amount_received` decimal(16,8) DEFAULT NULL,
  `payment_term_id` int(10) unsigned DEFAULT NULL,
  `status` varchar(12) NOT NULL DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `orders_user_id_foreign_idx` (`user_id`),
  KEY `orders_payment_method_id_foreign_idx` (`payment_method_id`),
  KEY `orders_currency_id_foreign_idx` (`currency_id`),
  KEY `orders_payment_term_id_foreign_idx` (`payment_term_id`),
  KEY `orders_transaction_type_index` (`transaction_type`),
  KEY `orders_order_type_index` (`order_type`),
  KEY `orders_tax_type_index` (`tax_type`),
  KEY `orders_reference_index` (`reference`),
  KEY `orders_order_reference_id_index` (`order_reference_id`),
  KEY `orders_order_date_index` (`order_date`),
  KEY `orders_due_date_index` (`due_date`),
  KEY `orders_total_index` (`total`),
  KEY `orders_paid_index` (`paid`),
  KEY `orders_amount_received_index` (`amount_received`),
  KEY `orders_exchange_rate_index` (`exchange_rate`),
  KEY `orders_status_index` (`status`),
  KEY `orders_address_id_foreign_idx` (`address_id`),
  CONSTRAINT `orders_address_id_foreign` FOREIGN KEY (`address_id`) REFERENCES `addresses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_currency_id_foreign` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_payment_method_id_foreign` FOREIGN KEY (`payment_method_id`) REFERENCES `payment_methods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_payment_term_id_foreign` FOREIGN KEY (`payment_term_id`) REFERENCES `payment_terms` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `orders_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- create table order_details
CREATE TABLE `order_details` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `item_id` bigint(20) unsigned NOT NULL,
 `order_id` bigint(20) unsigned NOT NULL,
 `vendor_id` bigint(20) DEFAULT NULL,
 `shop_id` bigint(20) DEFAULT NULL,
 `description` text,
 `item_name` varchar(255) NOT NULL,
 `price` decimal(16,8) NOT NULL,
 `quantity_sent` decimal(16,8) NOT NULL,
 `quantity` decimal(16,8) NOT NULL,
 `discount_amount` decimal(16,8) unsigned DEFAULT NULL,
 `discount_type` varchar(12) DEFAULT NULL,
 `hsn` varchar(255) DEFAULT NULL,
 `payloads` text,
 `order_by` int(3) NOT NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `order_details_item_id_foreign_idx` (`item_id`),
 KEY `order_details_item_name_index` (`item_name`),
 KEY `order_details_price_index` (`price`),
 KEY `order_details_quantity_sent_index` (`quantity_sent`),
 KEY `order_details_quantity_index` (`quantity`),
 KEY `order_details_discount_amount_index` (`discount_amount`),
 KEY `order_details_discount_index` (`discount`),
 KEY `order_details_discount_type_index` (`discount_type`),
 KEY `order_details_hsn_index` (`hsn`),
 KEY `order_details_order_by_index` (`order_by`),
 KEY `order_details_vendor_id_foreign_idx` (`vendor_id`),
 KEY `order_details_shop_id_foreign_idx` (`shop_id`),
 KEY `order_details_order_id_foreign_idx` (`order_id`),
 CONSTRAINT `order_details_item_id_foreign` FOREIGN KEY (`item_id`) REFERENCES `items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `order_details_order_id_foreign` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `order_details_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `order_details_vendor_id_foreign` FOREIGN KEY (`vendor_id`) REFERENCES `vendors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--create table transaction_references
CREATE TABLE `transaction_references` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`object_id` INT(10) NULL,
`reference_type` VARCHAR(50) NOT NULL,
`code` VARCHAR(100) NOT NULL,
PRIMARY KEY (`id`),
INDEX `transaction_references_reference_type_index` (`reference_type` ASC),
INDEX `transaction_references_object_id_index` (`object_id` ASC))
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

--create table transactions
CREATE TABLE `transactions` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `currency_id` INT(10) UNSIGNED NOT NULL,
    `amount` DECIMAL(16,8) NOT NULL,
    `transaction_type` VARCHAR(100) NOT NULL,
    `account_id` BIGINT(20) NULL,
    `transaction_date` DATE NOT NULL,
    `user_id` BIGINT(20) NULL,
    `transaction_reference_id` BIGINT(20) UNSIGNED NOT NULL,
    `transaction_method` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    `payment_method_id` INT(10) UNSIGNED NOT NULL,
    `params` TEXT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `transactions_currency_id_foreign_idx` (`currency_id` ASC),
    INDEX `transactions_user_id_foreign_idx` (`user_id` ASC),
    INDEX `transactions_transaction_reference_id_foreign_idx` (`transaction_reference_id` ASC),
    INDEX `transactions_payment_method_id_foreign_idx` (`payment_method_id` ASC),
    INDEX `transactions_amount_index` (`amount` ASC),
    INDEX `transactions_transaction_type_index` (`transaction_type` ASC),
    INDEX `transactions_transaction_date_index` (`transaction_date` ASC),
    INDEX `transactions_transaction_method_index` (`transaction_method` ASC),
    CONSTRAINT `transactions_currency_id_foreign`
        FOREIGN KEY (`currency_id`)
            REFERENCES `currencies` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `transactions_user_id_foreign`
        FOREIGN KEY (`user_id`)
            REFERENCES `users` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `transactions_transaction_reference_id_foreign`
        FOREIGN KEY (`transaction_reference_id`)
            REFERENCES `transaction_references` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE,
    CONSTRAINT `transactions_payment_method_id_foreign`
        FOREIGN KEY (`payment_method_id`)
            REFERENCES `payment_methods` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE)
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

--create table item_details
CREATE TABLE `item_details` (
    `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_id` BIGINT(20) UNSIGNED NULL,
    `warranty_type` VARCHAR(25) NOT NULL,
    `warranty_period` VARCHAR(12) NULL,
    `warranty_policy` TEXT NULL,
    `is_free_shipping` TINYINT(1) NULL DEFAULT 0,
    `is_flat_rate` TINYINT(1) NULL DEFAULT 0,
    `is_show_stock_quantity` TINYINT(1) NULL DEFAULT 0,
    `is_hide_stock` TINYINT(1) NULL DEFAULT 0,
    `is_featured` TINYINT(1) NULL DEFAULT 0,
    `is_cash_on_delivery` TINYINT(1) NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    INDEX `item_details_item_id_foeign_idx` (`item_id` ASC),
    INDEX `item_details_warranty_type_index` (`warranty_type` ASC),
    INDEX `item_details_warranty_period_index` (`warranty_period` ASC),
    INDEX `item_details_is_free_shipping_index` (`is_free_shipping` ASC),
    INDEX `item_details_is_featured_index` (`is_featured` ASC),
    INDEX `item_details_is_cash_on_delivery_index` (`is_cash_on_delivery` ASC),
    CONSTRAINT `item_details_item_id_foeign`
        FOREIGN KEY (`item_id`)
            REFERENCES `items` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE)
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4;

--order status table
CREATE TABLE `status_orders` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `is_default` tinyint(1) NOT NULL DEFAULT '0',
 `order_by` int(2) NULL,
 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 KEY `status_orders_name_index` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--add order_status_id in order table
ALTER TABLE `orders`
DROP FOREIGN KEY `orders_status_order_id_foreign`;
ALTER TABLE `orders`
DROP COLUMN `status_order_id`,
ADD COLUMN `order_status_id` INT(10) UNSIGNED NULL AFTER `coupon_code`,
ADD INDEX `orders_order_status_id_foreign_idx` (`order_status_id` ASC),
DROP INDEX `orders_status_index` ;
;
ALTER TABLE `orders`
    ADD CONSTRAINT `orders_order_status_id_foreign`
        FOREIGN KEY (`order_status_id`)
            REFERENCES `order_statuses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

--create order_status_roles table
CREATE TABLE `order_status_roles` (
   `order_status_id` INT(10) UNSIGNED NOT NULL,
   `role_id` INT(11) NOT NULL,
   INDEX `order_status_roles_role_id_foreign_idx` (`role_id` ASC),
   INDEX `order_status_roles_order_status_id_foreign_idx` (`order_status_id` ASC),
   CONSTRAINT `order_status_roles_order_status_id_foreign`
       FOREIGN KEY (`order_status_id`)
           REFERENCES `order_statuses` (`id`)
           ON DELETE CASCADE
           ON UPDATE CASCADE,
   CONSTRAINT `order_status_roles_role_id_foreign`
       FOREIGN KEY (`role_id`)
           REFERENCES `roles` (`id`)
           ON DELETE CASCADE
           ON UPDATE CASCADE)
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

--create order_status_histories table
CREATE TABLE `order_status_histories` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`item_id` BIGINT(20) UNSIGNED NOT NULL,
`order_id` BIGINT(20) UNSIGNED NOT NULL,
`user_id` BIGINT(20) NOT NULL,
`order_status_id` INT(10) UNSIGNED NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `order_status_histories_item_id_foreign_idx` (`item_id` ASC),
INDEX `order_status_histories_order_id_foreign_idx` (`order_id` ASC),
INDEX `order_status_histories_user_id_foreign_idx` (`user_id` ASC),
INDEX `order_status_histories_order_status_id_foreign_idx` (`order_status_id` ASC),
CONSTRAINT `order_status_histories_order_id_foreign`
   FOREIGN KEY (`order_id`)
       REFERENCES `orders` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
CONSTRAINT `order_status_histories_user_id_foreign`
   FOREIGN KEY (`user_id`)
       REFERENCES `users` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
CONSTRAINT `order_status_histories_order_status_id_foreign`
   FOREIGN KEY (`order_status_id`)
       REFERENCES `order_statuses` (`id`)
       ON DELETE CASCADE
       ON UPDATE CASCADE,
CONSTRAINT `order_status_histories_item_id_foreign`
    FOREIGN KEY (`item_id`)
        REFERENCES `items` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE)
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

--add order_status_id in order_details table
ALTER TABLE `order_details`
    ADD COLUMN `order_status_id` INT(10) UNSIGNED NULL AFTER `order_by`,
ADD INDEX `order_details_order_status_id_foreign_idx` (`order_status_id` ASC);
;
ALTER TABLE `order_details`
    ADD CONSTRAINT `order_details_order_status_id_foreign`
        FOREIGN KEY (`order_status_id`)
            REFERENCES `order_statuses` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

--add shipping_id in order details table
ALTER TABLE `order_details`
    ADD COLUMN `shipping_id` BIGINT(20) UNSIGNED NULL AFTER `order_status_id`,
ADD INDEX `order_details_shipping_id_foreign_idx` (`shipping_id` ASC);
;
ALTER TABLE `order_details`
    ADD CONSTRAINT `order_details_shipping_id_foreign`
        FOREIGN KEY (`shipping_id`)
            REFERENCES `shippings` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

--create inventories table
CREATE TABLE `inventories` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`item_option_id` INT(10) UNSIGNED NULL,
`label` VARCHAR(45) NULL,
`vendor_id` BIGINT(20) NULL,
`quantity` DECIMAL(16,8) NOT NULL,
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
INDEX `inventories_label_index` (`label` ASC),
INDEX `inventories_item_option_id_foreign_idx` (`item_option_id` ASC),
INDEX `inventories_vendor_id_foreign_idx` (`vendor_id` ASC),
INDEX `inventories_quantity_index` (`quantity` ASC),
CONSTRAINT `inventories_item_option_id_foreign`
    FOREIGN KEY (`item_option_id`)
        REFERENCES `item_options` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
CONSTRAINT `inventories_vendor_id_foreign`
    FOREIGN KEY (`vendor_id`)
        REFERENCES `vendors` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

--create table commission
CREATE TABLE `commissions` (
   `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
   `is_active` TINYINT(1) NULL DEFAULT 0,
   `is_category_based` TINYINT(1) NULL DEFAULT 0,
   `amount` FLOAT(16,8) NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

--create table option_group_categories
CREATE TABLE `category_option_groups` (
`option_group_id` BIGINT(20) NOT NULL,
`category_id` INT(11) NOT NULL,
INDEX `option_group_categories_option_group_id_foreign_idx` (`option_group_id` ASC),
INDEX `option_group_categories_category_id_foreign_idx` (`category_id` ASC),
CONSTRAINT `option_group_categories_option_group_id_foreign`
    FOREIGN KEY (`option_group_id`)
        REFERENCES `option_groups` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
CONSTRAINT `option_group_categories_category_id_foreign`
    FOREIGN KEY (`category_id`)
        REFERENCES `categories` (`id`)
        ON DELETE CASCADE
        ON UPDATE CASCADE);

-- create order commission tble
CREATE TABLE `order_commissions` (
  `id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `order_id` BIGINT(20) UNSIGNED NULL,
  `vendor_id` BIGINT(20) NULL,
  `amount` DECIMAL(16,8) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `order_commissions_order_id_foreign_idx` (`order_id` ASC),
  INDEX `order_commissions_vendor_id_foreign_idx` (`vendor_id` ASC),
  CONSTRAINT `order_commissions_order_id_foreign`
      FOREIGN KEY (`order_id`)
          REFERENCES `orders` (`id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE,
  CONSTRAINT `order_commissions_vendor_id_foreign`
      FOREIGN KEY (`vendor_id`)
          REFERENCES `vendors` (`id`)
          ON DELETE CASCADE
          ON UPDATE CASCADE)
    ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;

--drop category_id in attributes table
ALTER TABLE `attributes`
DROP FOREIGN KEY `attributes_category_id_foreign`;
ALTER TABLE `attributes`
DROP COLUMN `category_id`,
DROP INDEX `attributes_category_id_foreign_idx` ;
;

-- add sell_commission column in vendors table
ALTER TABLE `vendors`
    ADD COLUMN `sell_commissions` DECIMAL(16,8) NULL AFTER `website`,
ADD INDEX `vendors_sell_commissions_index` (`sell_commissions` ASC);
;

--added sell_commissions column in categories table
ALTER TABLE `categories`
    ADD COLUMN `sell_commissions` DECIMAL(16,8) NULL AFTER `item_counts`,
ADD INDEX `categories_sell_commissions_index` (`sell_commissions` ASC);
;

--order commission tble update
ALTER TABLE `order_commissions`
DROP FOREIGN KEY `order_commissions_order_id_foreign`;
ALTER TABLE `order_commissions`
DROP COLUMN `order_id`,
ADD COLUMN `order_details_id` BIGINT(20) UNSIGNED NOT NULL AFTER `id`,
ADD INDEX `order_commissions_order_detail_id_foreign_idx` (`order_details_id` ASC),
DROP INDEX `order_commissions_order_id_foreign_idx` ;
;
ALTER TABLE `order_commissions`
    ADD CONSTRAINT `order_commissions_order_detail_id_foreign`
        FOREIGN KEY (`order_details_id`)
            REFERENCES `order_details` (`id`)
            ON DELETE CASCADE
            ON UPDATE CASCADE;

ALTER TABLE `order_commissions`
    ADD COLUMN `approval_time` DATETIME(5) NULL AFTER `amount`,
ADD COLUMN `status` VARCHAR(12) NULL DEFAULT 'Pending' AFTER `approval_time`,
ADD INDEX `order_commissions_status_index` (`status` ASC),
ADD INDEX `order_commissions_approval_time_index` (`approval_time` ASC);
;

-- drop category_id column from option_groups table
ALTER TABLE `option_groups`
DROP FOREIGN KEY `option_groups_category_id`;
ALTER TABLE `option_groups`
DROP COLUMN `category_id`,
DROP INDEX `option_groups_category_id_idx` ;
;

