



RENAME TABLE `lara_users` TO `admin`;


UPDATE `model_has_roles` 
    SET `model_type` = 'App\\Models\\Admin' 
        WHERE `model_has_roles`.`role_id` = 1 
            AND `model_has_roles`.`model_type` = 'App\\Models\\User' 
            AND `model_has_roles`.`model_id` = 1; 

UPDATE `model_has_roles` 
    SET `model_type` = 'App\\Models\\Admin' 
        WHERE `model_has_roles`.`role_id` = 2 
            AND `model_has_roles`.`model_type` = 'App\\Models\\User' 
            AND `model_has_roles`.`model_id` = 4; 
        
UPDATE `model_has_roles` 
    SET `model_type` = 'App\\Models\\Admin' 
        WHERE `model_has_roles`.`role_id` = 4 
            AND `model_has_roles`.`model_type` = 'App\\Models\\User'
            AND `model_has_roles`.`model_id` = 3;


ALTER TABLE `admin` 
    ADD `deleted_at` TIMESTAMP NOT NULL DEFAULT NULL AFTER `remember_token`;


ALTER TABLE `admin` ADD INDEX(`deleted_at`);


ALTER TABLE `admin` ADD `status` INT(1) NOT NULL DEFAULT '1' AFTER `remember_token`;


ALTER TABLE `main_category` CHANGE `updated_date` `updated_date` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP;


ALTER TABLE `city` 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`, 
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`;



ALTER TABLE `admin` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '1';

ALTER TABLE `admin` ADD `last_login` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `status`;

ALTER TABLE `main_category` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `rank`;

ALTER TABLE `main_category` CHANGE `status` `status` TINYINT(4) NOT NULL DEFAULT '1';



ALTER TABLE `city` ADD `deleted_at` INT NULL DEFAULT NULL AFTER `status`;
ALTER TABLE `city` ADD INDEX(`deleted_at`);
ALTER TABLE `city` ADD INDEX(`status`);
ALTER TABLE `city` ADD INDEX(`created_at`);
ALTER TABLE `city` ADD INDEX(`updated_at`);


ALTER TABLE `main_category` ADD INDEX(`status`);
ALTER TABLE `main_category` ADD INDEX(`deleted_at`);
ALTER TABLE `main_category` ADD INDEX(`created_date`);
ALTER TABLE `main_category` ADD INDEX(`updated_date`);


ALTER TABLE `admin` ADD INDEX(`status`);
ALTER TABLE `admin` ADD INDEX(`created_at`);
ALTER TABLE `admin` ADD INDEX(`updated_at`);


ALTER TABLE `city` CHANGE `status` `status` TINYINT(4) NULL DEFAULT '1' COMMENT '1 -active';


ALTER TABLE `restaurant` ADD FOREIGN KEY (`city_id`) REFERENCES `city`(`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE `restaurant` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `device_last_update`;

ALTER TABLE `restaurant` ADD UNIQUE(`email`);

ALTER TABLE `store_timing` 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `off`, 
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`;



ALTER TABLE `store_collect_schedule` 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `collect_to`,
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`;


ALTER TABLE `store_tags` 
    ADD `updated_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_date`;

ALTER TABLE `store_tags` ADD `updated_by` INT NOT NULL AFTER `created_by`;

ALTER TABLE `store_tags` CHANGE `created_by` `created_by` INT(11) NOT NULL;


ALTER TABLE `store_labels` 
    CHANGE `start_date` `start_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, 
    CHANGE `end_date` `end_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;


ALTER TABLE `store_labels` 
    CHANGE `start_offer_date` `start_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `store_offer`, 
    CHANGE `end_offer_date` `end_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP AFTER `start_offer_date`;



ALTER TABLE `store_labels` 
    ADD `start_delivery_offer_date` DATETIME NULL DEFAULT NULL AFTER `delivery_offer`, 
    ADD `end_delivery_offer_date` DATETIME NULL DEFAULT NULL AFTER `start_delivery_offer_date`;


ALTER TABLE `store_labels` 
    CHANGE `start_delivery_offer_date` `start_delivery_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, 
    CHANGE `end_delivery_offer_date` `end_delivery_offer_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

ALTER TABLE `store_labels` 
    ADD `created_by` INT NOT NULL AFTER `rank_factor`;


ALTER TABLE `store_labels` 
    ADD `created_date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `updated_by`;


ALTER TABLE `store_labels` 
    CHANGE `updated_date` `updated_date` DATETIME NULL DEFAULT CURRENT_TIMESTAMP;


ALTER TABLE `store_labels` CHANGE `updated_by` `updated_by` INT(11) NOT NULL;

ALTER TABLE `store_labels` 
    CHANGE `created_by` `created_by` BIGINT(20) UNSIGNED NOT NULL, 
    CHANGE `updated_by` `updated_by` BIGINT(20) UNSIGNED NOT NULL;


ALTER TABLE `store_labels` ADD INDEX(`start_offer_date`);

ALTER TABLE `store_labels` ADD INDEX(`end_offer_date`);

ALTER TABLE `store_labels` ADD INDEX(`start_delivery_offer_date`);

ALTER TABLE `store_labels` ADD INDEX(`end_delivery_offer_date`);


ALTER TABLE `restaurant` ADD `busy_until` DATETIME NULL AFTER `available`;

ALTER TABLE `restaurant` ADD `rank` INT NOT NULL DEFAULT '0' AFTER `status`;

ALTER TABLE `restaurant` ADD `tag_names` VARCHAR(255) NULL AFTER `name`;

ALTER TABLE `store_category` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `rank`;

ALTER TABLE `store_category` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '0';

ALTER TABLE `category` ADD `deleted_at` TIMESTAMP NULL AFTER `rank`;

ALTER TABLE `category` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '0';

ALTER TABLE `restaurant_menu_item` ADD `deleted_at` TIMESTAMP NULL AFTER `deleted`;

ALTER TABLE `restaurant_menu_item` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '1';



CREATE TABLE `store_label` (
 `entity_id` int(11) NOT NULL AUTO_INCREMENT,
 `store_id` int(11) DEFAULT NULL,
 `store_group_id` bigint(11) UNSIGNED DEFAULT NULL,
 `label` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `icon_color` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `icon` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
 `start_date` datetime DEFAULT current_timestamp(),
 `end_date` datetime DEFAULT current_timestamp(),
 `status` tinyint(1) DEFAULT 1,
 `type` tinyint(1) NOT NULL DEFAULT 0 COMMENT '0-one restaurant\r\n1-all\r\n2-group',
 `deleted_at` timestamp NULL DEFAULT NULL,
 `created_by` bigint(20) unsigned NOT NULL,
 `updated_by` bigint(20) unsigned NOT NULL,
 `created_date` timestamp NULL DEFAULT current_timestamp(),
 `updated_date` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`entity_id`),
 KEY `store_label_fk1` (`store_id`),
 KEY `store_group_id` (`store_group_id`),
 KEY `created_by` (`created_by`),
 KEY `updated_by` (`updated_by`),
 CONSTRAINT `store_label_fk1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `store_label_ibfk_1` FOREIGN KEY (`store_group_id`) REFERENCES `store_group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `store_label_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `admin` (`id`) ON UPDATE CASCADE,
 CONSTRAINT `store_label_ibfk_3` FOREIGN KEY (`updated_by`) REFERENCES `admin` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci




ALTER TABLE `menu_item_group` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`;



ALTER TABLE `menu_item_group` 
    ADD `created_by` BIGINT UNSIGNED NOT NULL AFTER `updated_at`, 
    ADD `updated_by` BIGINT UNSIGNED NOT NULL AFTER `created_by`;


ALTER TABLE `menu_item_group` 
    ADD FOREIGN KEY (`created_by`) REFERENCES `admin`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;


ALTER TABLE `menu_item_group` 
    ADD FOREIGN KEY (`updated_by`) REFERENCES `admin`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;






CREATE TABLE `area` (
  `entity_id` int(11) NOT NULL AUTO_INCREMENT,
  `city_id` int(11) NOT NULL,
  `name` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name_en` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `polygon` geometry DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `area_fk4` FOREIGN KEY (`city_id`) REFERENCES `city` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `area_polygon_points` (
  `entity_id` int(11) NOT NULL AUTO_INCREMENT,
  `area_id` int(11) NOT NULL,
  `lat` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  `lng` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `area_points_fk` FOREIGN KEY (`area_id`) REFERENCES `area` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `app_banner_popup` (
  `entity_id` int(11) NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) Not NULL DEFAULT 0 COMMENT '0: POPUP, 1: BANNER',
  `popup_type` enum('STORE_LIST','CHECKOUT')  DEFAULT 'STORE_LIST',
  `geo_type` enum('STORE_DISTANCE','AREA','CITY','ALL')  DEFAULT NULL,
  `distance_km` decimal(10,2) NULL DEFAULT NULL,
  `store_id` int(11) NULL DEFAULT NULL,
  `area_id` int(11) NULL DEFAULT NULL,
  `city_id` int(11) NOT NULL,
  `message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `repeat` tinyint(1) NULL DEFAULT NULL COMMENT '0:SUN,1:MON,....6:SAT,7:DAILY,8:FIXED_DATETIME',
  `start_date` datetime NULL DEFAULT NULL,
  `end_date` datetime NULL DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `app_popups_store_fk1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `app_popups_area_fk2` FOREIGN KEY (`area_id`) REFERENCES `area` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `app_popups_city_fk3` FOREIGN KEY (`city_id`) REFERENCES `city` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;



ALTER TABLE `store_label` ADD `repeat` TINYINT(1) NOT NULL 
    COMMENT '0:SUN\r\n1:MON\r\n...\r\n6:SAT\r\n7:DAILY\r\n8:FIXED_DATETIME' AFTER `icon`;


ALTER TABLE `app_banner_popup` CHANGE `city_id` `city_id` INT(11) NULL;


ALTER TABLE `restaurant_menu_item` CHANGE `menu_item_group_id` `menu_item_group_id` VARCHAR(255) NULL DEFAULT NULL;

ALTER TABLE `app_banner_popup` 
    ADD `fixed_date` TINYINT(1) NOT NULL DEFAULT '0' AFTER `image`, 
    ADD `next_day` TINYINT(1) NOT NULL DEFAULT '0' AFTER `fixed_date`;


/* to avoid error of no repeat = 8 */
UPDATE `app_banner_popup` SET `repeat`=NULL WHERE `repeat` = 8;


CREATE TABLE `delivery_coupon` (
  `entity_id` int(11) NOT NULL AUTO_INCREMENT,
  `coupon_type` enum('STORE','STORE_GROUP','ALL')  DEFAULT 'STORE',
  `charge_type` enum('APP','STORE')  DEFAULT 'STORE',
  `geo_type` enum('STORE_DISTANCE','AREA','CITY','ALL') DEFAULT 'STORE_DISTANCE',
  `store_id` int(11) DEFAULT NULL,
  `store_group_id` int(11) DEFAULT NULL,
  `city_id` int(11) DEFAULT NULL,
  `distance_km` decimal(10,2) NULL DEFAULT NULL,
  `area_id` int(11) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `description` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `coupon_discount_type` enum('PERCENT','FIXED_DISCOUNT','FIXED_AMOUNT')  DEFAULT 'PERCENT',
  `coupon_discount` decimal(10,2) DEFAULT NULL,
  `max_discount` decimal(10,2) DEFAULT NULL,
  `min_order_amount` decimal(10,2) DEFAULT '0.00',
  `delivery_flag` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `delivery_flag_note` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `repeat` VARCHAR(255) NULL DEFAULT NULL,
  `next_day` tinyint(1) DEFAULT 0 AFTER repeat,
  `fixed_date` tinyint(1) DEFAULT 0,
  `start_date` datetime NULL DEFAULT NULL,
  `end_date` datetime NULL DEFAULT NULL,
  `status` tinyint(1) DEFAULT 1,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  UNIQUE KEY `name` (`name`),
  CONSTRAINT `delivery_coupon_store_fk1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `delivery_coupon_city_fk3` FOREIGN KEY (`city_id`) REFERENCES `city` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `delivery_coupon_area_fk4` FOREIGN KEY (`area_id`) REFERENCES `area` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `delivery_coupon` CHANGE `store_group_id` `store_group_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL;
ALTER TABLE `delivery_coupon` ADD FOREIGN KEY (`store_group_id`) REFERENCES `store_group`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;


CREATE TABLE `app_notification` (
  `entity_id` int(11) NOT NULL AUTO_INCREMENT,
  `geo_type` enum('STORE_DISTANCE','AREA','CITY','ALL') DEFAULT NULL,
  `distance_km` decimal(10,2) NULL DEFAULT NULL,
  `store_id` int(11) NULL DEFAULT NULL,
  `area_id` int(11) NULL DEFAULT NULL,
  `city_id` int(11) NULL DEFAULT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `customer_last_active_time` datetime NULL DEFAULT NULL,
  `repeat` tinyint(1) NULL DEFAULT NULL COMMENT '0:SUN,1:MON,....6:SAT,7:DAILY',
  `fixed_date` tinyint(1) DEFAULT 0,
  `start_date` datetime NULL DEFAULT NULL,
  `sent` tinyint(1) DEFAULT 0,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `notification_store_fk1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE,
  CONSTRAINT `notification_area_fk2` FOREIGN KEY (`area_id`) REFERENCES `area` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `notification_city_fk3` FOREIGN KEY (`city_id`) REFERENCES `city` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `app_notification` ADD `end_date` DATETIME NULL AFTER `start_date`;

ALTER TABLE `app_notification` ADD `next_day` TINYINT(1) NOT NULL AFTER `end_date`;

ALTER TABLE `app_notification` CHANGE `repeat` `repeat` VARCHAR(255) NULL DEFAULT NULL COMMENT '0:SUN,1:MON,....6:SAT,7:DAILY';


ALTER TABLE `area` ADD `image` VARCHAR(255) NULL AFTER `polygon`;


CREATE TABLE `store_users` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_type` tinyint(1) DEFAULT '1' COMMENT '1- ADMIN, 2- NORMAL',
  `name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '1- active',
  `device_os` tinyint NOT NULL DEFAULT '1' COMMENT '1- ANDROID, 2- IOS',
  `app_version` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_active_date` datetime DEFAULT NULL,
  `active` tinyint NOT NULL DEFAULT '0' COMMENT '1- active',
  `device_id` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `created_by` int DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `store_users_fk` (`store_id`),
  CONSTRAINT `store_users_fk` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE SET NULL ON UPDATE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE city ADD COLUMN `image` varchar(255) NULL DEFAULT NULL AFTER polygon;

ALTER TABLE `store_users` ADD `deleted_at` TIMESTAMP NULL AFTER `device_id`;



ALTER TABLE app_banner_popup ADD COLUMN `banner_image` varchar(255) NULL DEFAULT NULL AFTER image;
ALTER TABLE app_banner_popup ADD COLUMN `popup_image` varchar(255) NULL DEFAULT NULL AFTER image;
ALTER TABLE app_banner_popup DROP COLUMN type;
ALTER TABLE app_banner_popup DROP COLUMN image;


CREATE TABLE `geo_timing` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `geo_type` enum('STORE_DISTANCE','AREA','CITY','ALL') DEFAULT NULL,
  `distance_km` decimal(10,2) NULL DEFAULT NULL,
  `area_id` int(11) NULL DEFAULT NULL,
  `city_id` int(11) NULL DEFAULT NULL,
  `fixed_date` tinyint(1) DEFAULT '1',
  `repeat` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `next_day` tinyint(1) DEFAULT '0',
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  CONSTRAINT `geo_timing_fk1` FOREIGN KEY (`city_id`) REFERENCES `city` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `geo_timing_fk2` FOREIGN KEY (`area_id`) REFERENCES `area` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;




ALTER TABLE `geo_timing` 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `end_date`, 
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`, 
    ADD `created_by` INT NOT NULL AFTER `updated_at`, 
    ADD `updated_by` INT NOT NULL AFTER `created_by`;



ALTER TABLE `app_banner_popup` ADD `geo_timing_id` INT NULL AFTER `store_id`;

ALTER TABLE `app_banner_popup` 
    ADD FOREIGN KEY (`geo_timing_id`) REFERENCES `geo_timing`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;


/* do the following after migration */

ALTER TABLE maestro.app_banner_popup DROP FOREIGN KEY app_popups_area_fk2;

ALTER TABLE maestro.app_banner_popup DROP FOREIGN KEY app_popups_city_fk3;

ALTER TABLE `app_banner_popup`
  DROP `geo_type`,
  DROP `distance_km`,
  DROP `area_id`,
  DROP `city_id`,
  DROP `fixed_date`,
  DROP `next_day`,
  DROP `repeat`,
  DROP `start_date`,
  DROP `end_date`;






ALTER TABLE `delivery_coupon` ADD `geo_timing_id` INT NULL AFTER `charge_type`;

ALTER TABLE `delivery_coupon` 
    ADD FOREIGN KEY (`geo_timing_id`) REFERENCES `geo_timing`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;



/* do the following after migration */

ALTER TABLE `delivery_coupon`
  DROP `geo_type`,
  DROP `city_id`,
  DROP `distance_km`,
  DROP `area_id`,
  DROP `repeat`,
  DROP `next_day`,
  DROP `fixed_date`,
  DROP `start_date`,
  DROP `end_date`;



ALTER TABLE restaurant_menu_item ADD COLUMN min_options Tinyint(1) default 1 AFTER max_options;



ALTER TABLE store_label ADD COLUMN  `label_type` enum('STORE','STORE_GROUP','ALL') NULL DEFAULT 'STORE' AFTER type;
ALTER TABLE store_label ADD COLUMN  `delivery_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER label;
ALTER TABLE store_label ADD COLUMN  `delivery_note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER delivery_flag;
ALTER TABLE store_label ADD COLUMN  `menu_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER delivery_note;
ALTER TABLE store_label ADD COLUMN  `menu_note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL AFTER menu_flag;
ALTER TABLE store_label ADD COLUMN  `delivery_coupon_id` NULL int(11)  DEFAULT NULL AFTER store_group_id;
ALTER TABLE `store_label` ADD `geo_timing_id` INT NULL DEFAULT NULL AFTER `store_group_id`;


ALTER TABLE `store_label` 
    ADD FOREIGN KEY (`delivery_coupon_id`) REFERENCES `delivery_coupon`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `store_label` 
    ADD FOREIGN KEY (`geo_timing_id`) REFERENCES `geo_timing`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `store_label` CHANGE `repeat` `repeat` TINYINT(1) NULL COMMENT '0:SUN\r\n1:MON\r\n...\r\n6:SAT\r\n7:DAILY\r\n8:FIXED_DATETIME';


/* do the following after migration */

ALTER TABLE `store_label`
  DROP `repeat`,
  DROP `start_date`,
  DROP `end_date`;


ALTER TABLE `store_label` DROP `type`; /* I see we use label_type instead of it */


ALTER TABLE `app_banner_popup` ADD `delivery_coupon_id` INT NULL AFTER `popup_type`;

ALTER TABLE `app_banner_popup` 
    ADD FOREIGN KEY (`delivery_coupon_id`) REFERENCES `delivery_coupon`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;



ALTER TABLE `delivery_coupon`
  DROP `delivery_flag`,
  DROP `delivery_flag_note`;


ALTER TABLE delivery_coupon ADD COLUMN `charge_store` int(1) DEFAULT 100 AFTER charge_type;


ALTER TABLE `app_notification` ADD `geo_timing_id` INT NULL DEFAULT NULL AFTER `store_id`;

ALTER TABLE `app_notification` 
    ADD FOREIGN KEY (`geo_timing_id`) REFERENCES `geo_timing`(`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE;


/* do the following after migration */
ALTER TABLE app_notification DROP FOREIGN KEY notification_city_fk3;
ALTER TABLE app_notification DROP FOREIGN KEY notification_area_fk2;

ALTER TABLE `app_notification` DROP INDEX `notification_city_fk3`;
ALTER TABLE `app_notification` DROP INDEX `notification_area_fk2`;

ALTER TABLE `app_notification`
  DROP `geo_type`,
  DROP `distance_km`,
  DROP `area_id`,
  DROP `city_id`,
  DROP `repeat`,
  DROP `fixed_date`,
  DROP `start_date`,
  DROP `end_date`,
  DROP `next_day`;



ALTER TABLE `delivery_coupon` CHANGE `status` `status` TINYINT(1) NULL DEFAULT '0';

ALTER TABLE `delivery_coupon` ADD `is_expired` TINYINT(1) NOT NULL DEFAULT '0' AFTER `status`;



ALTER TABLE restaurant 
    ADD COLUMN `collect_type` enum('PRODUCTS','CASH_DEBIT','CASH_CREDIT')  DEFAULT 'PRODUCTS' AFTER app_rate;


ALTER TABLE restaurant ADD COLUMN `bank_name` varchar(255) NULL DEFAULT NULL AFTER collect_type;
ALTER TABLE restaurant ADD COLUMN `iban` varchar(30) NULL DEFAULT NULL AFTER bank_name;
ALTER TABLE restaurant ADD COLUMN `wallet_name` varchar(255) NULL DEFAULT NULL AFTER iban;
ALTER TABLE restaurant ADD COLUMN `wallet_number` varchar(10) NULL DEFAULT NULL AFTER wallet_name;



ALTER TABLE store_collect_schedule ADD COLUMN  `auto_collect` tinyint(1) DEFAULT 0 AFTER collect_type;
ALTER TABLE store_collect_schedule ADD COLUMN  `skip_first_order` tinyint(1) DEFAULT 0 AFTER auto_collect;
ALTER TABLE store_collect_schedule ADD COLUMN  `min_collect_amount` tinyint(1) DEFAULT 0 AFTER skip_first_order;
ALTER TABLE store_collect_schedule ADD COLUMN  `days` varchar(255) NULL DEFAULT NULL AFTER max_collect_amount;



ALTER TABLE `store_collect_schedule` ADD `collect_next_day` TINYINT(1) NOT NULL DEFAULT '0' AFTER `collect_to`;



ALTER TABLE `delivery_coupon` 
    CHANGE `is_expired` `delivery_current_status` ENUM('STOPPED','RUNNING','EXPIRED') NOT NULL DEFAULT 'STOPPED';


ALTER TABLE delivery_coupon 
    ADD COLUMN `delivery_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER min_order_amount;
ALTER TABLE delivery_coupon 
    ADD COLUMN `delivery_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL AFTER min_order_amount;


CREATE TABLE `messages` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

alter table messages drop column remark;

alter table messages 
    add column message_type enum( 'GENERAL', 'DELIVERY_FLAG', 'DELIVERY_LABEL') default 'GENERAL' after entity_id;


CREATE TABLE `menu_discount` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `store_id` int DEFAULT NULL,
  `geo_timing_id` int DEFAULT NULL,
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `max_discount` decimal(10,2) DEFAULT NULL,
  `min_order_amount` decimal(10,2) DEFAULT '0.00',
  `discount_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `discount_label` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `discount_current_status` enum('STOPPED','RUNNING','WAITING','EXPIRED') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'STOPPED',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `menu_discount_store_fk1` (`store_id`),
  KEY `geo_timing_id` (`geo_timing_id`),
  CONSTRAINT `menu_discount_fk1` FOREIGN KEY (`geo_timing_id`) REFERENCES `geo_timing` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `menu_discount_fk2` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `menu_discount_item` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `menu_discount_id` int DEFAULT NULL,
  `menu_type` enum('CATEGORY','SUBCATEGORY','ITEM') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'CATEGORY',
  `menu_id`  int DEFAULT NULL,
  `discount_type` enum('PERCENT','FIXED_DISCOUNT','FIXED_AMOUNT') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'PERCENT',
  `discount_value` decimal(10,2) DEFAULT NULL,
  `app_rate` decimal(10,2) DEFAULT NULL,
  `discount_flag` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(1) DEFAULT '0',
  `deleted_at` timestamp NULL DEFAULT NULL,
  `updated_by` int DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `menu_discount_item_fk1` (`menu_discount_id`),
  CONSTRAINT `menu_discount_item_fk1` FOREIGN KEY (`menu_discount_id`) REFERENCES `menu_discount` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

ALTER TABLE `menu_discount_item` CHANGE `menu_id` `menu_ids` VARCHAR(255) NULL DEFAULT NULL;


ALTER TABLE `messages` 
    CHANGE `message_type` `message_type` 
    ENUM('GENERAL','DELIVERY_FLAG','DELIVERY_LABEL','MENU_DISCOUNT_FLAG','MENU_DISCOUNT_LABEL') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT 'GENERAL';


ALTER TABLE `store_label` ADD `menu_discount_id` INT NULL AFTER `delivery_coupon_id`;

ALTER TABLE `store_label` 
    ADD FOREIGN KEY (`menu_discount_id`) REFERENCES `menu_discount`(`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;


UPDATE `store_group` SET `id` = '3' WHERE `store_group`.`id` = 1;

INSERT INTO `store_group` 
    (`id`, `name`, `created_by`, `updated_by`, `deleted_at`, `created_at`, `updated_at`) 
    VALUES 
    ('1', 'all', '1', '1', NULL, '2023-08-04 14:59:41', '2023-08-04 14:59:41');

ALTER TABLE menu_discount_item ADD COLUMN menu_id int(11) NULL DEFAULT NULL AFTER menu_ids;

ALTER TABLE `messages` MODIFY COLUMN `message_type`
   ENUM('GENERAL','DELIVERY_FLAG','DELIVERY_LABEL','MENU_DISCOUNT_FLAG',
        'OD_CUSTOMER_PN','OD_DRIVER_PN','MENU_DISCOUNT_LABEL',
        'MENU_DISCOUNT_ITEM_FLAG');

ALTER TABLE `menu_discount_item` CHANGE `status` `status` TINYINT(1) NULL DEFAULT '1';

ALTER TABLE delivery_coupon ADD COLUMN budget_amount decimal(10,2) NULL DEFAULT NULL AFTER charge_store;


ALTER TABLE `store_collect_schedule` 
    CHANGE `collect_type` `type` 
    ENUM('ASAP','DAILY','WEEKLY','MONTHLY') 
    CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'ASAP';


ALTER TABLE `store_collect_schedule` 
    ADD `collect_type` 
    ENUM('PRODUCTS','CASH_DEBIT','CASH_CREDIT','') 
    NULL AFTER `store_id`;



CREATE TABLE `notify_users` (
  `entity_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `whats_app` varchar(255) DEFAULT NULL,
  `type_of_period` enum('DAILY','WEEKLY','MONTHLY') NOT NULL DEFAULT 'MONTHLY',
  `days` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;



ALTER TABLE `notify_users`
  ADD PRIMARY KEY (`entity_id`),
  ADD KEY `store_id` (`store_id`);


ALTER TABLE `notify_users`
  MODIFY `entity_id` int(11) NOT NULL AUTO_INCREMENT;


ALTER TABLE `notify_users`
  ADD CONSTRAINT `notify_users_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON UPDATE CASCADE;


ALTER TABLE `notify_users` 
    ADD INDEX(`type_of_period`);

ALTER TABLE `notify_users` 
    ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `days`, 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `deleted_at`, 
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`, 
    ADD `created_by` INT NOT NULL AFTER `updated_at`, 
    ADD `updated_by` INT NOT NULL AFTER `created_by`;



CREATE TABLE `notify_users_export` (
  `entity_id` int(11) NOT NULL,
  `notify_id` int(11) NOT NULL,
  `start_date` datetime NOT NULL,
  `end_date` datetime NOT NULL,
  `pdf_file` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `notify_users_export`
  ADD PRIMARY KEY (`entity_id`),
  ADD KEY `notify_id` (`notify_id`);

ALTER TABLE `notify_users_export`
  MODIFY `entity_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notify_users_export`
  ADD CONSTRAINT `notify_users_export_ibfk_1` FOREIGN KEY (`notify_id`) REFERENCES `notify_users` (`entity_id`) ON UPDATE CASCADE;


ALTER TABLE `notify_users_export` 
    ADD `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `pdf_file`, 
    ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `created_at`;


ALTER TABLE `notify_users` 
    CHANGE `days` `days_of_month` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;

ALTER TABLE `notify_users` ADD `days_of_week` VARCHAR(255) NULL AFTER `type_of_period`;

ALTER TABLE `notify_users` ADD `status` TINYINT(1) NOT NULL DEFAULT '1' AFTER `days_of_month`;

RENAME TABLE `maestro`.`notify_users` TO `maestro`.`notify_contacts`;

RENAME TABLE `maestro`.`notify_users_export` TO `maestro`.`notify_contacts_export`;

ALTER TABLE `notify_contacts` CHANGE `status` `status` TINYINT(1) NOT NULL DEFAULT '0';


CREATE TABLE `notify_month_days` (
  `entity_id` int(11) NOT NULL,
  `notify_id` int(11) NOT NULL,
  `day` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `notify_month_days`
  ADD PRIMARY KEY (`entity_id`),
  ADD UNIQUE KEY `notify_id` (`notify_id`,`day`);

ALTER TABLE `notify_month_days`
  MODIFY `entity_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notify_month_days`
  ADD CONSTRAINT `notify_month_days_ibfk_1` FOREIGN KEY (`notify_id`) 
  REFERENCES `notify_contacts` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;


CREATE TABLE `notify_week_days` (
  `entity_id` int(11) NOT NULL,
  `notify_id` int(11) NOT NULL,
  `week_day` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


ALTER TABLE `notify_week_days`
  ADD PRIMARY KEY (`entity_id`),
  ADD UNIQUE KEY `notify_id` (`notify_id`,`week_day`);

ALTER TABLE `notify_week_days`
  MODIFY `entity_id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `notify_week_days`
  ADD CONSTRAINT `notify_week_days_ibfk_1` FOREIGN KEY (`notify_id`) 
  REFERENCES `notify_contacts` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE;


ALTER TABLE `notify_contacts`
  DROP `days_of_week`,
  DROP `days_of_month`;


ALTER TABLE `notify_month_days` CHANGE `day` `day` TINYINT(2) NOT NULL;

ALTER TABLE `notify_week_days` CHANGE `week_day` `week_day` 
  ENUM('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday')
  CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL;



CREATE TABLE `points_reward` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `points` int NOT NULL,
  `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE `points_earn` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `points` int NOT NULL DEFAULT 0,
  `icon` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `message` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `action` enum('INFO', 'ROUTE', 'API') DEFAULT 'INFO',
  `action_target` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` tinyint(1) NOT NULL DEFAULT 1,
  `created_by` int NOT NULL,
  `updated_by` int NOT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL
  PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


ALTER TABLE `points_reward` 
  ADD `deleted_at` TIMESTAMP NULL AFTER `created_date`, 
  ADD `updated_by` INT NOT NULL AFTER `deleted_at`, 
  ADD `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP AFTER `updated_by`;



  CREATE TABLE `external_orders` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `store_id` int NOT NULL,
  `driver_company_id` int DEFAULT NULL,
  `driver_group_id` int DEFAULT NULL,
  `driver_id` int DEFAULT NULL,
  `candidate_driver_id` int NOT NULL DEFAULT '0',
  `transport_type` tinyint DEFAULT '1',
  `neighborhood_id` int DEFAULT NULL,
  `order_status` tinyint DEFAULT '10' COMMENT '10 NEW, 30 ONWAY, 35 DELIVERED',
  `manual_dispatch` tinyint DEFAULT '0',
  `distance` decimal(10,2) DEFAULT '0.00',
  `duration` int DEFAULT '0',
  `delivery_charge` decimal(10,2) DEFAULT NULL,
  `maestro_percentage` decimal(10,2) DEFAULT '15.00',
  `discount` tinyint DEFAULT '0',
  `coupon_id` int DEFAULT NULL,
  `coupon_discount` decimal(10,2) DEFAULT '0.00',
  `order_amount` decimal(10,2) NOT NULL DEFAULT '0.00',
  `prepare_duration` int DEFAULT NULL,
  `customer_address` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `customer_phone` varchar(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `customer_lat` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `customer_lng` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `extra_note` varchar(500) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `support_user_id` int DEFAULT NULL,
  `delivery_date` datetime DEFAULT NULL,
  `dispatch_type` enum('FAIR','SWAPPED','SINGLE_NEAR','DOUBLE_NEAR_30','DOUBLE_NEAR_32','MULTIPLE_NEAR') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dispatch_date` datetime DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `updated_by` int DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`entity_id`),
  KEY `external_orders_fk` (`store_id`),
  KEY `external_orders_fk1` (`driver_id`),
  KEY `external_orders_fk2` (`driver_group_id`),
  KEY `external_orders_fk3` (`driver_company_id`),
  CONSTRAINT `external_orders_fk` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=92574 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci





CREATE TABLE `customer_points_transaction` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `customer_id` int NOT NULL,
  `order_id` int DEFAULT NULL,
  `reward_id` int DEFAULT NULL,
  `points` int DEFAULT '0',
  `points_type` enum('ORDER_PLACED','ORDER_RATED','SIGNUP','WALLET_CHARGE','DAILY_POINTS','REFERRAL','SHAKE','ORDER_COUNT','BIRTHDAY','RESERVED_POINTS','REDEEM_REWARD') COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `message` varchar(511) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `action_source` enum('ADMIN','CUSTOMER','DRIVER','STORE') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT 'ADMIN',
  PRIMARY KEY (`entity_id`),
  KEY `customer_points_transaction_fk` (`customer_id`),
  KEY `customer_points_transaction_fk1` (`order_id`),
  CONSTRAINT `customer_points_log_fk` FOREIGN KEY (`customer_id`) REFERENCES `users` (`entity_id`),
  CONSTRAINT `customer_points_log_fk1` FOREIGN KEY (`order_id`) REFERENCES `order_master` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci


CREATE TABLE `customer_points` (
  `customer_id` int NOT NULL,
  `points` int DEFAULT '0',
  `reserved` int DEFAULT '0',
  `last_transaction_id` int DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`customer_id`),
  KEY `customer_balance_fk2` (`last_transaction_id`),
  CONSTRAINT `customer_points_fk2` FOREIGN KEY (`last_transaction_id`) REFERENCES `customer_points_transaction` (`entity_id`),
  CONSTRAINT `customer_pointse_fk` FOREIGN KEY (`customer_id`) REFERENCES `users` (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci



CREATE TABLE `voucher` (
 `entity_id` int(11) NOT NULL AUTO_INCREMENT,
 `status` tinyint(1) NOT NULL DEFAULT 1,
 `discount_type` enum('PERCENT','AMOUNT') NOT NULL,
 `voucher_type` enum('STORE','WALLET','DELIVERY','POINT') NOT NULL,
 `voucher_code` varchar(6) NOT NULL,
 `discount_value` decimal(12,2) NOT NULL,
 `qr_code` varchar(255) DEFAULT NULL,
 `store_id` int(11) DEFAULT NULL,
 `max_count` tinyint(4) NOT NULL DEFAULT 1,
 `valid_until` datetime NOT NULL,
 `deleted_at` timestamp NULL DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `created_by` int(11) NOT NULL,
 `updated_by` int(11) NOT NULL,
 PRIMARY KEY (`entity_id`),
 KEY `store_id` (`store_id`),
 CONSTRAINT `voucher_ibfk_1` FOREIGN KEY (`store_id`) REFERENCES `restaurant` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


CREATE TABLE `allowed_customer_voucher` (
 `entity_id` int(11) NOT NULL AUTO_INCREMENT,
 `voucher_id` int(11) NOT NULL,
 `customer_id` int(11) NOT NULL,
 PRIMARY KEY (`entity_id`),
 KEY `voucher_id` (`voucher_id`),
 KEY `customer_id` (`customer_id`),
 CONSTRAINT `allowed_customer_voucher_ibfk_1` FOREIGN KEY (`voucher_id`) REFERENCES `voucher` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `allowed_customer_voucher_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `users` (`entity_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


CREATE TABLE `voucher_usage` (
 `entity_id` int(11) NOT NULL AUTO_INCREMENT,
 `voucher_id` int(11) NOT NULL,
 `customer_id` int(11) NOT NULL,
 `used_at` timestamp NOT NULL DEFAULT current_timestamp(),
 PRIMARY KEY (`entity_id`),
 KEY `voucher_id` (`voucher_id`),
 KEY `customer_id` (`customer_id`),
 CONSTRAINT `voucher_usage_ibfk_1` FOREIGN KEY (`voucher_id`) REFERENCES `voucher` (`entity_id`) ON UPDATE CASCADE,
 CONSTRAINT `voucher_usage_ibfk_2` FOREIGN KEY (`customer_id`) REFERENCES `users` (`entity_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


RENAME TABLE allowed_customer_voucher TO voucher_allowed_customers;



ALTER TABLE `user_address` 
  CHANGE `address` `address` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL, 
  CHANGE `landmark` `landmark` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;


ALTER TABLE `neighborhood` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`;

ALTER TABLE `landmarks` ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`;


ALTER TABLE `customer_points_transaction` 
  ADD `old_balance` DECIMAL(10,3) NULL AFTER `points_type`, 
  ADD `new_balance` DECIMAL(10,3) NULL AFTER `old_balance`;


INSERT INTO `system_option` 
  (`OptionName`, `OptionSlug`, `OptionValue`, `CreatedBy`, `CreadedDate`, `UpdatedBy`, `UpdatedDate`) 
VALUES 
  ('Max Call Center Order Balance Amount', 'max_call_center_order_balance_amount', '2', NULL, current_timestamp(), NULL, NULL);



INSERT INTO `system_option` 
  (`OptionName`, `OptionSlug`, `OptionValue`, `CreatedBy`, `CreadedDate`, `UpdatedBy`, `UpdatedDate`) 
VALUES 
  ('Max Call Center Days To Edit Balance', 'max_call_center_days_to_edit_balance', '2', NULL, current_timestamp(), NULL, NULL);

INSERT INTO `system_option` 
  (`OptionName`, `OptionSlug`, `OptionValue`, `CreatedBy`, `CreadedDate`, `UpdatedBy`, `UpdatedDate`) 
VALUES 
  ('Max Call Center Reset Amount Balance', 'max_call_center_reset_amount_balance', '-5', NULL, current_timestamp(), NULL, NULL);



ALTER TABLE `external_neighborhood` 
  ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`;



CREATE TABLE `external_neighborhood_store_group_price` (
 `entity_id` int(11) NOT NULL AUTO_INCREMENT,
 `external_neighborhood_id` int(11) NOT NULL,
 `store_group_id` bigint(20) unsigned NOT NULL,
 `price` decimal(10,2) NOT NULL,
 `status` tinyint(1) NOT NULL DEFAULT 0,
 `deledted_at` timestamp NULL DEFAULT NULL,
 `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
 `created_by` int(11) NOT NULL,
 `updated_by` int(11) NOT NULL,
 PRIMARY KEY (`entity_id`),
 KEY `external_neighborhood_id` (`external_neighborhood_id`),
 KEY `store_group_id` (`store_group_id`),
 KEY `status` (`status`),
 CONSTRAINT `external_neighborhood_store_group_price_ibfk_1` FOREIGN KEY (`external_neighborhood_id`) REFERENCES `external_neighborhood` (`entity_id`) ON UPDATE CASCADE,
 CONSTRAINT `external_neighborhood_store_group_price_ibfk_2` FOREIGN KEY (`store_group_id`) REFERENCES `store_group` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4


ALTER TABLE `external_neighborhood_store_group_price` 
  CHANGE `price` `delivery_price` DECIMAL(10,2) NOT NULL;

ALTER TABLE `external_neighborhood_store_group_price` 
  CHANGE `deledted_at` `deleted_at` TIMESTAMP NULL DEFAULT NULL;

RENAME TABLE `external_neighborhood_store_group_price` TO `external_neighborhood_store_group`;


ALTER TABLE `external_neighborhood_store_group` ADD UNIQUE(`external_neighborhood_id`, `store_group_id`)


ALTER TABLE `driver_users` 
  ADD `deleted_at` TIMESTAMP NULL DEFAULT NULL AFTER `device_id`;



ALTER TABLE `delivery_coupon` 
  CHANGE `delivery_current_status` `delivery_current_status` 
  ENUM('STOPPED','RUNNING','EXPIRED','WAITING','DUPLICATE') 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'STOPPED';


INSERT INTO `system_option` 
  (`OptionName`, `OptionSlug`, `OptionValue`, `CreatedBy`, `CreadedDate`, `UpdatedBy`, `UpdatedDate`) 
VALUES 
  ('Accountant Numbers', 'accountant_numbers', '+201125567102,+201006933901', NULL, current_timestamp(), NULL, NULL);



ALTER TABLE `messages` 
  CHANGE `message_type` `message_type` 
  ENUM(
        'GENERAL',
        'DELIVERY_FLAG',
        'DELIVERY_LABEL',
        'MENU_DISCOUNT_FLAG',
        'MENU_DISCOUNT_LABEL',
        'MENU_DISCOUNT_ITEM_FLAG',
        'NOTIFICATION_BALANCE_CHANGED_MESSAGE',
        'NOTIFICATION_BALANCE_TO_ZERO_MESSAGE'
      ) 
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL;


  ALTER TABLE `driver_users` 
    CHANGE `bag` `bag` TINYINT(1) NULL DEFAULT '0', 
    CHANGE `tshirt` `tshirt` TINYINT(1) NULL DEFAULT '0';

  ALTER TABLE menu_discount_item ADD max_quantity TINYINT NULL DEFAULT 0 AFTER app_rate;

ALTER TABLE `admin` 
  ADD `otp_code` VARCHAR(6) NULL AFTER `remember_token`, 
  ADD `otp_expired_at` DATETIME NULL AFTER `otp_code`;

ALTER TABLE `admin` 
  ADD `otp_whats_app` VARCHAR(25) NULL AFTER `remember_token`;
