ALTER TABLE `category`
  ADD COLUMN `status` TINYINT(2) DEFAULT 1  NOT NULL AFTER `code`;


create table `sub_category` (`id` bigint unsigned not null auto_increment primary key, `category_id` bigint not null, `name` varchar(191) not null, `code` varchar(191) null, `created_at`
 timestamp null, `updated_at` timestamp null) default character set utf8 collate 'utf8_general_ci';




DROP TABLE IF EXISTS `sliders`;

CREATE TABLE `sliders` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(191) DEFAULT NULL,
  `image` varchar(191) NOT NULL,
  `click_url` varchar(191) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


insert  into `sliders`(`id`,`title`,`image`,`click_url`,`created_at`,`updated_at`) values (4,'Gadget Bank','gadget-bank-1-210721112922.jpg','#','2021-07-21 23:26:26','2021-07-21 23:29:22'),(5,'Flash Sale','flash-sale-1-210721112808.jpg','https://demo.hasthemes.com/ee-preview/ee/index-5-dark.html','2021-07-21 23:27:33','2021-07-21 23:28:08');



DROP TABLE IF EXISTS `ads`;

CREATE TABLE `ads` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(191) DEFAULT NULL,
  `image` varchar(191) NOT NULL,
  `ads_location` varchar(191) DEFAULT NULL,
  `click_url` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;


insert  into `ads`(`id`,`title`,`image`,`ads_location`,`click_url`,`created_at`,`updated_at`) values (1,'Slider 1','slider-1-1-210815120547.jpg','slider1','#','2021-07-19 23:24:17','2021-08-15 00:07:50'),(2,'Slider 2','slider-2-1-210815120602.jpg','slider2','#','2021-07-19 23:25:21','2021-08-15 00:07:42'),(3,'Featured Item ads','-1-210719112551.jpg','featured_item','#','2021-07-19 23:25:51','2021-07-19 23:27:32'),(4,'best seller one','-1-210815120656.jpg','best_seller1','#','2021-08-15 00:06:56','2021-08-15 00:06:56'),(5,'best seller two','-1-210815120721.jpg','best_seller2','#','2021-08-15 00:07:21','2021-08-15 00:07:21');


ALTER TABLE `products`
  ADD COLUMN `sub_category_id` INT(11) NULL AFTER `category_id`,
  ADD COLUMN `retail_price_after_discount` VARBINARY(191) DEFAULT '0'  NULL AFTER `retail_price`,
  ADD COLUMN `is_new_arrival` TINYINT(2) DEFAULT 1  NOT NULL AFTER `alert_qty`,
  ADD COLUMN `is_best_seller` TINYINT(2) DEFAULT 0  NOT NULL AFTER `is_new_arrival`,
  ADD COLUMN `is_featured_item` TINYINT(2) DEFAULT 0  NOT NULL AFTER `is_best_seller`,
  ADD COLUMN `short_description` TEXT NULL AFTER `is_featured_item`,
  ADD COLUMN `specification` TEXT NULL AFTER `description`,
  CHANGE `image` `image_one` TEXT CHARSET utf8 COLLATE utf8_general_ci NULL,
  ADD COLUMN `image_two` TEXT NULL AFTER `image_one`,
  ADD COLUMN `image_three` TEXT NULL AFTER `image_two`,
  ADD COLUMN `is_ecom_item` TINYINT(2) DEFAULT 1  NOT NULL AFTER `is_featured_item`,
  ADD COLUMN `thumb_image` TEXT NULL AFTER `specification`,
  ADD COLUMN `variant` TEXT NULL AFTER `specification`,
  ADD COLUMN `is_smartphone` TINYINT(2) DEFAULT 0  NOT NULL AFTER `is_ecom_item`,
  ADD COLUMN `is_audio` TINYINT(2) DEFAULT 0  NOT NULL AFTER `is_smartphone`,
  ADD COLUMN `status` TINYINT(2) DEFAULT 1  NOT NULL AFTER `image_three`;





--CreateReviewsTable:
DROP TABLE IF EXISTS `reviews`;
CREATE TABLE `reviews` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) NOT NULL,
  `name` varchar(191) NOT NULL,
  `email` varchar(191) NOT NULL,
  `review` text DEFAULT NULL,
  `rating` varchar(191) DEFAULT NULL,
  `status` tinyint(2) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;





  ALTER TABLE `sales`
  ADD COLUMN `billing_full_name` VARCHAR(191) NULL AFTER `sale_type`,
  ADD COLUMN `billing_phone_number` VARCHAR(191) NULL AFTER `billing_full_name`,
  ADD COLUMN `billing_full_address` VARCHAR(191) NULL AFTER `billing_phone_number`,
  ADD COLUMN `shipping_full_name` VARCHAR(191) NULL AFTER `billing_full_address`,
  ADD COLUMN `shipping_phone_number` VARCHAR(191) NULL AFTER `shipping_full_name`,
  ADD COLUMN `shipping_full_address` VARCHAR(191) NULL AFTER `shipping_phone_number`,
  ADD COLUMN `payment_method` VARCHAR(191) NULL AFTER `payment_status`,
  ADD COLUMN `delivery_status` INT(2) DEFAULT 0  NOT NULL AFTER `shipping_full_address`;



  ALTER TABLE `settings`
  CHANGE `site_name` `site_name` VARCHAR(191) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
  ADD COLUMN `default_theme` VARCHAR(191) NOT NULL AFTER `site_name`,
  CHANGE `logo` `logo` VARCHAR(191) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;


  ALTER TABLE `settings`
  CHANGE `logo` `site_logo` VARCHAR(191) CHARSET utf8 COLLATE utf8_general_ci NOT NULL;

  ALTER TABLE `settings`
  ADD COLUMN `vat_percentage` VARCHAR (191) DEFAULT '0.0' NULL AFTER `default_language`;

  ALTER TABLE `settings`
  ADD COLUMN `printer_type` VARCHAR (191) DEFAULT 'pos' NULL AFTER `vat_percentage`;

  ALTER TABLE `settings`
  ADD COLUMN `vat_reg_no` VARCHAR (191) NULL AFTER `default_language`;

  ALTER TABLE `sale_details`
  ADD COLUMN `discount_percentage` INT (11) DEFAULT 0 NULL AFTER `product_price`;





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

ALTER TABLE `customers`
  ADD COLUMN `status` TINYINT(2) DEFAULT 1  NOT NULL AFTER `mobile`;

ALTER TABLE `customers`
  ADD COLUMN `password` VARCHAR(191) NULL AFTER `mobile`;

  ALTER TABLE `customers`
  ADD COLUMN `image` VARCHAR(191) NULL AFTER `password`;

ALTER TABLE `customers`
  ADD COLUMN `otp` INT(10) NULL AFTER `image`,
  CHANGE `status` `status` TINYINT(2) DEFAULT 3  NOT NULL;

  ALTER TABLE `customers`
  ADD COLUMN `occupation` VARCHAR(191) NULL AFTER `mobile`,
  ADD COLUMN `date_of_birth` VARCHAR(191) NULL AFTER `occupation`;

ALTER TABLE customers CHANGE mobile mobile VARCHAR(191) CHARACTER SET utf8 DEFAULT NULL UNIQUE COLLATE `utf8_general_ci`
alter table `customers` add unique `customers_mobile_unique`(`mobile`)



 ALTER TABLE brands
  ADD COLUMN `image` TEXT NULL AFTER `title`,
  ADD COLUMN `is_ecom_show` TINYINT(2) DEFAULT 0  NOT NULL AFTER `image`,
  ADD COLUMN `status` TINYINT(2) DEFAULT 1  NOT NULL AFTER `is_ecom_show`;



CREATE TABLE `variants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `group_name` varchar(191) NOT NULL,
  `variant_name` varchar(191) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;


insert  into `variants`(`id`,`group_name`,`variant_name`,`created_at`,`updated_at`) values (1,'Color','Red','2021-09-14 22:37:16','2021-09-14 22:37:16'),(2,'Color','Blue','2021-09-14 22:37:16','2021-09-14 22:37:16'),(3,'Color','Black','2021-09-14 22:37:16','2021-09-14 22:37:16'),(4,'Size','S','2021-09-14 22:37:16','2021-09-14 22:37:16'),(5,'Size','M','2021-09-14 22:37:16','2021-09-14 22:37:16'),(6,'Size','L','2021-09-14 22:37:16','2021-09-14 22:37:16'),(7,'Size','XL','2021-09-14 22:37:16','2021-09-14 22:37:16'),(8,'Size','XXL','2021-09-14 22:37:16','2021-09-14 22:37:16');

ALTER TABLE `balances`
  ADD COLUMN `remarks` VARCHAR (150) NOT NULL AFTER `date`;

ALTER TABLE purchase_payments
  ADD COLUMN `account_id` BIGINT (20) NOT NULL AFTER `purchase_id`;


create table `account_fund_transfers` (`id` bigint unsigned not null auto_increment primary key, `from_account` bigint not null, `to_account` bigint not null, `amount` varchar(191) not null, `date` varchar(191) not null, `note` varchar(191) null, `created_at` timestamp null, `updated_at` timestamp null) default character set utf8 collate 'utf8_general_ci'


ALTER TABLE `user`
  ADD COLUMN `full_name` VARCHAR (111) NOT NULL AFTER `user_id`,
  ADD COLUMN `warehouse_id` INT (10) NOT NULL AFTER `role_id`,
  CHANGE `user_name` `email` VARCHAR (50) CHARSET utf8 COLLATE utf8_general_ci NOT NULL,
  CHANGE `status` `status` TINYINT (1) DEFAULT 1 NOT NULL;

CREATE TABLE `wishlists` (
                             `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
                             `customer_id` bigint(20) NOT NULL,
                             `product_id` bigint(20) NOT NULL,
                             `created_at` timestamp NULL DEFAULT NULL,
                             `updated_at` timestamp NULL DEFAULT NULL,
                             PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


ALTER TABLE `products`
    ADD COLUMN `product_position_no` VARCHAR(20) NULL AFTER `image_three`;


ALTER TABLE `expenses`
    ADD COLUMN `warehouse_id` INT(11) NOT NULL AFTER `id`;

ALTER TABLE `sale_payments`
    ADD COLUMN `account_id` BIGINT (20) NULL AFTER `sale_id`;

ALTER TABLE `sales`
    CHANGE `discount` `discount` VARCHAR (11) DEFAULT '0' NOT NULL,
    CHANGE `shipping_cost` `shipping_cost` VARCHAR (11) DEFAULT '0' NOT NULL,
    CHANGE `tax` `tax` VARCHAR (11) DEFAULT '0' NOT NULL;

ALTER TABLE `sale_details`
    CHANGE `product_price` `product_price` VARCHAR (11) NOT NULL,
    CHANGE `total_item_price` `total_item_price` VARCHAR (11) NOT NULL,
    CHANGE `discount_percentage` `discount_percentage` VARCHAR (11) DEFAULT '0' NULL;

--
ALTER TABLE `customers`
    ADD COLUMN `credit_amount_limit` VARCHAR (191) NULL AFTER `image`,
  ADD COLUMN `previous_due` VARCHAR (191) NULL COMMENT 'opening due before software start' AFTER `credit_amount_limit`;

ALTER TABLE `sale_details`
    ADD COLUMN `product_cost` VARCHAR (11) NULL AFTER `serial_no`;

CREATE TABLE `product_variants` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `product_id` int(20) NOT NULL,
    `variant_id` int(20) NOT NULL,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

ALTER TABLE `product_variants`
    ADD COLUMN `product_id` INT (20) NOT NULL AFTER `id`;

ALTER TABLE `product_stock`
    ADD COLUMN `product_variant_id` VARCHAR (20) NULL AFTER `product_id`;

ALTER TABLE `sale_details`
    ADD COLUMN `product_variant_id` BIGINT (20) NULL AFTER `product_id`;

ALTER TABLE `stock_transfer_details`
    ADD COLUMN `product_variant_id` INT (11) NULL AFTER `product_id`;