-- ==================================12=29-2023====================================
ALTER TABLE `tbl_product`
ADD COLUMN `brand_id` INT NULL AFTER `subcategory_id`,
ADD COLUMN `thumbnail` VARCHAR(150) NULL AFTER `product_name_bn`;


ALTER TABLE `tbl_product`
CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

ALTER TABLE `tbl_product_to_image` RENAME TO  `tbl_product_image` ;
ALTER TABLE `tbl_product_image` CHANGE COLUMN `image` `file_path` VARCHAR(255) NOT NULL ;
ALTER TABLE `tbl_product_image` CHANGE COLUMN `product_id` `basic_info_id` BIGINT UNSIGNED NOT NULL ;


ALTER TABLE `tbl_product`
ADD COLUMN `total_rating` FLOAT NULL DEFAULT '0' AFTER `total_rating`,
ADD COLUMN `total_review` FLOAT NULL DEFAULT '0' AFTER `total_rating_1`;



CREATE TABLE `tbl_product_reviews` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  `rating` float DEFAULT NULL,
  `review` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
)

CREATE TABLE `tbl_product_basic_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_name_en` varchar(255) NOT NULL,
  `product_name_bn` varchar(255) NOT NULL,
  `category_id` int NOT NULL,
  `subcategory_id` int NOT NULL,
  `brand_id` int NOT NULL,
  `thumbnail` varchar(150) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE TABLE `tbl_brand` (
  `id` int NOT NULL AUTO_INCREMENT,
  `brand_name_en` varchar(255) DEFAULT NULL,
  `brand_name_bn` varchar(255) DEFAULT NULL,
  `status` int DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

customer_discount
agent_discount
dropshipper_discount
spot_comission
team_comission
dropshipper_pre_payment

ALTER TABLE `e-zone`.`tbl_product`
ADD COLUMN `customer_discount` FLOAT NULL AFTER `sale_price`;
ADD COLUMN `dropshipper_discount` FLOAT NULL AFTER `customer_discount`;
ADD COLUMN `spot_comission` FLOAT NULL AFTER `dropshipper_discount`;
ADD COLUMN `team_comission` FLOAT NULL AFTER `spot_comission`;
ADD COLUMN `dropshipper_pre_payment` FLOAT NULL AFTER `team_comission`;

ALTER TABLE `e-zone`.`tbl_orders`
CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;


ALTER TABLE `e-zone`.`tbl_order_items`
ADD COLUMN `discount` FLOAT NULL DEFAULT 0 AFTER `price`;

ALTER TABLE `e-zone`.`tbl_order_items_history`
ADD COLUMN `product_id` INT NULL AFTER `order_item_id`,
ADD COLUMN `status` INT NULL AFTER `note`,
CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;


ALTER TABLE `e-zone`.`tbl_order_items_history`
ADD COLUMN `order_id` INT NOT NULL AFTER `id`;


ALTER TABLE `e-zone`.`tbl_order_status_history`
ADD COLUMN `note` TEXT NULL AFTER `status_id`;

ALTER TABLE `e-zone`.`tbl_order_status_history`
CHANGE COLUMN `created_at` `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ;



ALTER TABLE `e-zone`.`tbl_delivery_hub`
ADD COLUMN `propiter_name` VARCHAR(255) NULL AFTER `business_title`,
ADD COLUMN `business_location` TEXT NULL AFTER `propiter_name`,
ADD COLUMN `business_division` INT NULL AFTER `business_location`,
ADD COLUMN `business_district` INT NULL AFTER `business_division`,
ADD COLUMN `business_police_station` INT NULL AFTER `business_district`,
ADD COLUMN `business_phone` VARCHAR(45) NULL AFTER `business_police_station`,
ADD COLUMN `trade_license` VARCHAR(150) NULL AFTER `business_phone`,
ADD COLUMN `nid` VARCHAR(150) NULL AFTER `trade_license`,
ADD COLUMN `profile_photo` VARCHAR(150) NULL AFTER `nid`,
ADD COLUMN `name` VARCHAR(150) NULL AFTER `profile_photo`,
ADD COLUMN `division` INT NULL AFTER `name`,
ADD COLUMN `district` INT NULL AFTER `division`,
ADD COLUMN `police_station` INT NULL AFTER `district`,
ADD COLUMN `address` TEXT NULL AFTER `police_station`,
ADD COLUMN `phone` VARCHAR(45) NULL AFTER `address`,
CHANGE COLUMN `hub_password` `password` VARCHAR(255) NOT NULL AFTER `username`,
CHANGE COLUMN `hub_name` `username` VARCHAR(255) NOT NULL ,
CHANGE COLUMN `hub_image` `logo` VARCHAR(255) NOT NULL ,
CHANGE COLUMN `hub_email` `business_title` VARCHAR(255) NOT NULL ;







ALTER TABLE `e-zone`.`tbl_users`
CHANGE COLUMN `common_wallet` `comission_wallet` DECIMAL(10,2) NOT NULL DEFAULT '0.00' ;
ALTER TABLE `e-zone`.`tbl_cash_wallet_history`
ADD COLUMN `payment_method` VARCHAR(20) NULL AFTER `message`;
ALTER TABLE `e-zone`.`tbl_cash_wallet_history`
CHANGE COLUMN `order_id` `order_id` BIGINT UNSIGNED NULL ;


CREATE TABLE `e-zone`.`tbl_charges` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `tax` FLOAT NULL,
  `withdrawal_charge` FLOAT NULL,
  `comission_level` INT NULL,
  `comission_limit` INT NULL,
  PRIMARY KEY (`id`));

  ALTER TABLE `e-zone`.`tbl_charges`
DROP COLUMN `comission_limit`,
DROP COLUMN `comission_level`,
CHANGE COLUMN `tax` `name` VARCHAR(255) NULL DEFAULT NULL ,
CHANGE COLUMN `withdrawal_charge` `value` VARCHAR(255) NULL DEFAULT NULL ;




ALTER TABLE `tbl_cash_wallet_history`
ADD COLUMN `current_balance` FLOAT NULL AFTER `transaction_id`;
ALTER TABLE `tbl_comission_wallet_history`
ADD COLUMN `current_balance` FLOAT NULL;


ALTER TABLE `tbl_returned_items`
ADD COLUMN `product_id` INT NULL DEFAULT NULL AFTER `order_item_id`,
ADD COLUMN `shop_id` INT NULL DEFAULT NULL AFTER `product_id`,
ADD COLUMN `seller_id` INT NULL DEFAULT NULL AFTER `shop_id`;
ALTER TABLE `tbl_shop`
ADD COLUMN `phone` VARCHAR
(11) NULL DEFAULT NULL AFTER `lon`;
