INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMSTCLR','MKTMST','MKTMSTCLR',1208,'/crm/CrmMstCallresponse',3,NULL,'Call Response','1','','Call Response','','Call Response','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmTrnMarketingManagerSummary' WHERE (`module_gid` = 'MKTCMPCMR');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTECM','MKT','MKTECM',1004,'',2,NULL,'e-Commerce','1','../images/Reports.gif','','','e-Commerce','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstCampaignsummary' WHERE (`module_gid` = 'MKTCMPCPM');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNSC','MKTECM','MKTMCNSC',1208,'/crm/Crmshopifycampaign',3,NULL,'Shopify','1','','Shopify','','Shopify','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmTrnMycampaign' WHERE (`module_gid` = 'MKTCMPCMP');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTECMWS','MKTECM','MKTECMWS',1808,'/crm/Crmwebsite',3,NULL,'Website','1','','Website','','Website','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstCallresponse' WHERE (`module_gid` = 'MKTMSTCLR');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTLEG','MKT','MKTLEG',1004,'',2,NULL,'Lead Generation','1','../images/Reports.gif','','','Lead Generation','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmShopifycustomer' WHERE (`module_gid` = 'MKTMCNSC');
  UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmWebsite' WHERE (`module_gid` = 'MKTECMWS');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNTEG','MKTLEG','MKTMCNTEG',1805,'ems_crm/crm_smm_CrmSmmTelegramaccount.aspx',3,NULL,'Telegram','1','','Telegram','','Telegram','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNFB','MKTLEG','MKTMCNFB',1808,'ems_crm/crm_smm_CrmSmmFacebookaccount.aspx',3,NULL,'Facebook','1','','Facebook','','Facebook','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmFacebookaccount' WHERE (`module_gid` = 'MKTMCNFB');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmTelegramaccount' WHERE (`module_gid` = 'MKTMCNTEG');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNLIN','MKTLEG','MKTMCNLIN',1900,'ems_crm/crm_smm_CrmSmmLinkedinaccount.aspx',3,NULL,'Linkedin','1','','Linkedin','','Linkedin','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
  UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmLinkedaccount' WHERE (`module_gid` = 'MKTMCNLIN');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNINS','MKTLEG','MKTMCNINS',1910,'ems_crm/crm_smm_CrmSmmInstagramaccount.aspx',3,NULL,'Instagram','1','','Instagram','','Instagram','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmInstagram' WHERE (`module_gid` = 'MKTMCNINS');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNWA','MKTMCN','MKTMCNWA',1802,'/crm/CrmSmmWhatsapp',3,NULL,'Instagram','1','','Whatsapp','','Whatsapp','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCN','MKT','MKTMCN',1006,'',2,NULL,'Campaign','1','../images/Reports.gif','','','Campaign','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNEC','MKTMCN','MKTMCNEC',1803,'/crm/CrmSmmEmailmanagement',3,NULL,'Email ','1','','Email ','','Email ','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
UPDATE `vcidex`.`adm_mst_tmodule` SET `module_name` = 'Whatsapp' WHERE (`module_gid` = 'MKTMCNWA');
INSERT INTO `adm_mst_tmodule` (`module_gid`,`module_gid_parent`,`module_code`,`display_order`,`module_link`,`menu_level`,`max_menulevel`,`module_name`,`status`,`image_url`,`group_type`,`modulemanager_gid`,`breadcrumb_name`,`approval_flag`,`approval_tablename`,`approval_type`,`approval_limit`,`module_flag`,`created_by`,`created_date`,`updated_by`,`updated_date`,`lw_flag`,`sref`,`icon`) VALUES
('MKTMCNSMS','MKTMCN','MKTMCNSMS',1804,'/crm/CrmSmmSms',3,NULL,'SMS ','1','','SMS ','','SMS ','','','','N','N',NULL,NULL,NULL,NULL,'Y',NULL,NULL);
  UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmMailcampaignsummary' WHERE (`module_gid` = 'MKTMCNEC');
   UPDATE `vcidex`.`adm_mst_tmodule` SET `module_name` = 'Product Master' WHERE (`module_gid` = 'MKTPDM');
   UPDATE `vcidex`.`adm_mst_tmodule` SET `module_name` = 'Leads' WHERE (`module_gid` = 'MKTLED');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmSmscampaign' WHERE (`module_gid` = 'MKTMCNSMS');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmRptActivitylogreport' WHERE (`module_gid` = 'MKTMEXALR');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmRptEnquiryrpt' WHERE (`module_gid` = 'MKTMEXENR');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmRptProductconsumptionreport' WHERE (`module_gid` = 'MKTPDTPCR');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmRptProductgroupreport' WHERE (`module_gid` = 'MKTPDTPGR');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmRptProductpreviouspricereport' WHERE (`module_gid` = 'MKTPDTPPR');
UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1005' WHERE (`module_gid` = 'MKTLEG');
UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1008' WHERE (`module_gid` = 'MKTCMP');
#-----

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1001' WHERE (`module_gid` = 'MKTMST');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1002' WHERE (`module_gid` = 'MKTPDM');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1003' WHERE (`module_gid` = 'MKTECM');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1004' WHERE (`module_gid` = 'MKTLEG');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1005' WHERE (`module_gid` = 'MKTLED');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1006' WHERE (`module_gid` = 'MKTMCN');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1007' WHERE (`module_gid` = 'MKT');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1008' WHERE (`module_gid` = 'MKTPDT');

UPDATE `vcidex`.`adm_mst_tmodule` SET `display_order` = '1009' WHERE (`module_gid` = 'MKTMEX');


#------------------------------------------------------------------------------------------ product crm--------------------------
ALTER TABLE pmr_mst_tproduct
ADD COLUMN `mrp_price` DECIMAL(13,2) DEFAULT '0.00',
ADD COLUMN `grams` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `compare_at_price` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `price` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `variant_id` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `old_inventory_quantity` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `inventory_quantity` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `vendor_name` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `shopify_productid` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `inventory_item_id` VARCHAR(256) DEFAULT NULL,
ADD COLUMN `option1` VARCHAR(256) DEFAULT NULL;
ALTER TABLE pmr_mst_tproductgroup
ADD COLUMN `businessunit_gid` VARCHAR(45) DEFAULT NULL,
ADD COLUMN `businessunit_name` VARCHAR(45) DEFAULT NULL;
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstProductunitsummary' WHERE (`module_gid` = 'MKTPDMPOU');
#-------------------------------------------leadbank-------------------------
ALTER TABLE crm_trn_tleadbank
ADD COLUMN `customer_type` VARCHAR(64) DEFAULT NULL,
ADD COLUMN `customertype_gid` VARCHAR(16) DEFAULT NULL,
ADD COLUMN `created_by1` VARCHAR(64) DEFAULT NULL,
ADD COLUMN `shopify_id` VARCHAR(1020) DEFAULT NULL,
ADD COLUMN `wh_flag` CHAR(1) DEFAULT NULL,
ADD COLUMN `wh_id` VARCHAR(256) DEFAULT NULL,
ADD COLUMN `whatsappcontact_id` VARCHAR(100) DEFAULT NULL;

ALTER TABLE crm_trn_tlead2campaign
ADD COLUMN call_count VARCHAR(45) DEFAULT '0';
#----------------------------------------customer_type--------------
CREATE TABLE `crm_trn_tcustomertypelog` (
  `customertypelog_gid` varchar(16) NOT NULL,
  `pre_customertype_gid` varchar(16) NOT NULL,
  `pre_customertype` varchar(45) DEFAULT NULL,
  `curr_customertype` varchar(45) DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`customertypelog_gid`),
  KEY `Index_2` (`pre_customertype`)
);
#------------------------------whatsapp--------------
CREATE TABLE `crm_smm_whatsapp` (
  `whatsapp_gid` int(11) NOT NULL AUTO_INCREMENT,
  `leadbank_gid` varchar(100) DEFAULT NULL,
  `leadbankcontact_gid` varchar(100) DEFAULT NULL,
  `id` varchar(256) DEFAULT NULL,
  `wkey` varchar(64) DEFAULT NULL,
  `wvalue` varchar(64) DEFAULT NULL,
  `displayName` varchar(64) DEFAULT NULL,
  `firstName` varchar(64) DEFAULT NULL,
  `lastName` varchar(64) DEFAULT NULL,
  `gender` varchar(64) DEFAULT NULL,
  `Servicewindow_flag` varchar(1) DEFAULT 'N',
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `updated_by` varchar(100) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `customer_type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`whatsapp_gid`),
  KEY `whatsapp_gid` (`whatsapp_gid`)
) ;
CREATE TABLE `crm_smm_whatsapptemplate` (
  `project_id` varchar(256) NOT NULL,
  `template_id` longtext,
  `version_id` varchar(256) DEFAULT NULL,
  `p_type` varchar(256) DEFAULT NULL,
  `p_name` varchar(256) DEFAULT NULL,
  `campaign_flag` varchar(1) DEFAULT 'Y',
  `template_body` longtext,
  `footer` longtext,
  `media_url` varchar(10000) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `last_updated` datetime DEFAULT NULL,
  `description` varchar(256) DEFAULT NULL,
  PRIMARY KEY (`project_id`),
  KEY `project_id` (`project_id`)
) ;
CREATE TABLE `crm_trn_tfiles` (
  `file_gid` varchar(32) NOT NULL,
  `message_gid` varchar(164) DEFAULT NULL,
  `contact_gid` varchar(164) DEFAULT NULL,
  `document_name` text CHARACTER SET utf8 NOT NULL,
  `document_path` varchar(512) DEFAULT NULL,
  `created_date` datetime DEFAULT CURRENT_TIMESTAMP,
  `mailmanagement_gid` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`file_gid`)
) ;
#-------------------------------------------------------email-------------------------
CREATE TABLE `temp_crm_smm_mailmanagement` (
  `temp_mail_gid` varchar(32) COLLATE utf8mb4_bin NOT NULL,
  `sub` longtext COLLATE utf8mb4_bin,
  `body` longtext COLLATE utf8mb4_bin,
  `created_by` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `template_flag` varchar(1) COLLATE utf8mb4_bin DEFAULT 'Y',
  `image_path` varchar(1128) COLLATE utf8mb4_bin DEFAULT NULL,
  `from_mail` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `bcc` longtext COLLATE utf8mb4_bin,
  `cc` longtext COLLATE utf8mb4_bin,
  `reply_to` longtext COLLATE utf8mb4_bin,
  `template_name` varchar(86) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`temp_mail_gid`),
  KEY `temp_mail_gid` (`temp_mail_gid`)
) ;
CREATE TABLE `crm_smm_mailmanagement` (
  `mailmanagement_gid` varchar(256) COLLATE utf8mb4_bin NOT NULL,
  `from_mail` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `to_mail` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `bcc` longtext COLLATE utf8mb4_bin,
  `cc` longtext COLLATE utf8mb4_bin,
  `reply_to` longtext COLLATE utf8mb4_bin,
  `sub` longtext COLLATE utf8mb4_bin,
  `body` longtext COLLATE utf8mb4_bin,
  `image_path` varchar(1128) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_by` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `sent_flag` varchar(1) COLLATE utf8mb4_bin DEFAULT 'N',
  `direction` varchar(10) COLLATE utf8mb4_bin DEFAULT 'outgoing',
  `transmission_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `status_delivery` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `status_open` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `status_click` varchar(16) COLLATE utf8mb4_bin DEFAULT NULL,
  `scheduled_time` datetime DEFAULT NULL,
  `schedule_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `temp_mail_gid` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `read_flag` char(1) COLLATE utf8mb4_bin DEFAULT 'N',
  `leadbank_gid` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`mailmanagement_gid`),
  KEY `mailmanagement_gid` (`mailmanagement_gid`)
);
#--------------------------------facebook-----------------
CREATE TABLE `crm_smm_facebookdetails` (
  `facebook_gid` int(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(256) DEFAULT NULL,
  `facebook_type` varchar(64) DEFAULT NULL,
  `page_category` varchar(256) DEFAULT NULL,
  `page_link` longtext,
  `user_name` varchar(64) DEFAULT NULL,
  `first_name` varchar(64) DEFAULT NULL,
  `last_name` varchar(64) DEFAULT NULL,
  `email` varchar(256) DEFAULT NULL,
  `birthday` varchar(64) DEFAULT NULL,
  `gender` varchar(12) DEFAULT NULL,
  `age_range` varchar(256) DEFAULT NULL,
  `profile_picture` longtext,
  `friends_count` varchar(256) DEFAULT NULL,
  `hometown_location` varchar(256) DEFAULT NULL,
  `current_location` varchar(256) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`facebook_gid`),
  KEY `facebook_gid` (`facebook_gid`)
);
CREATE TABLE `crm_smm_facebookpage` (
  `facebookpage_gid` int(20) NOT NULL AUTO_INCREMENT,
  `facebookmain_gid` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_id` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_type` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_url` longtext COLLATE utf8mb4_bin,
  `permalink_url` longtext COLLATE utf8mb4_bin,
  `caption` longtext COLLATE utf8mb4_bin,
  `views_count` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `postcreated_time` datetime DEFAULT NULL,
  PRIMARY KEY (`facebookpage_gid`),
  KEY `facebookpage_gid` (`facebookpage_gid`)
);
CREATE TABLE `crm_smm_facebookpagedtl` (
  `facebookpagedtl_gid` int(20) NOT NULL AUTO_INCREMENT,
  `facebookmain_gid` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_type` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `post_id` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `commentmessage_id` varchar(1000) COLLATE utf8mb4_bin DEFAULT NULL,
  `comment_message` longtext COLLATE utf8mb4_bin,
  `comment_time` datetime DEFAULT NULL,
  PRIMARY KEY (`facebookpagedtl_gid`),
  KEY `facebookpagedtl_gid` (`facebookpagedtl_gid`)
) ;
CREATE TABLE `crm_smm_tfacebookschedule` (
  `facebookschedule_gid` int(20) NOT NULL AUTO_INCREMENT,
  `post_id` varchar(1000) DEFAULT NULL,
  `post_type` varchar(64) DEFAULT NULL,
  `file_name` varchar(64) DEFAULT NULL,
  `published` varchar(64) DEFAULT NULL,
  `post_url` longtext,
  `caption` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `schedule_at` datetime DEFAULT NULL,
  `postcreated_time` datetime DEFAULT NULL,
  `posted_flag` char(1) DEFAULT 'N',
  PRIMARY KEY (`facebookschedule_gid`)
);
#----------------------------------------telegram----------------------
CREATE TABLE `crm_smm_telegramdetails` (
  `telegram_gid` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(256) DEFAULT NULL,
  `telegram_type` varchar(64) DEFAULT NULL,
  `page_name` varchar(256) DEFAULT NULL,
  `page_link` longtext,
  `bot_name` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`telegram_gid`),
  KEY `telegram_gid` (`telegram_gid`)
);
CREATE TABLE `crm_smm_ttelegram` (
  `id` int(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_content` longtext COLLATE utf8mb4_bin,
  `upload_path` longtext COLLATE utf8mb4_bin,
  `upload_type` varchar(1128) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id` (`id`)
);
#------------------------------------------linkedin-----------------------------
CREATE TABLE `crm_smm_tlinkedinusersetails` (
  `linkedin_gid` int(12) unsigned NOT NULL AUTO_INCREMENT,
  `upload_path` longtext,
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `first_name` varchar(500) DEFAULT NULL,
  `last_name` varchar(500) DEFAULT NULL,
  `type` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`linkedin_gid`)
);
#-------------------------------------------------google analytics------------------
CREATE TABLE `crm_smm_tgoogleanalyticsuser` (
  `firstSessionDate` varchar(255) DEFAULT NULL,
  `firstUserCampaignName` varchar(255) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `activeUsers` int(11) DEFAULT NULL,
  `newUsers` int(11) DEFAULT NULL,
  `totalUsers` int(11) DEFAULT NULL,
  `eventCount` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `crm_smm_tgoogleanalyticspage` (
  `firstUserCampaignName` varchar(255) DEFAULT NULL,
  `date` varchar(255) DEFAULT NULL,
  `hour` int(11) DEFAULT NULL,
  `minute` int(11) DEFAULT NULL,
  `deviceCategory` varchar(50) DEFAULT NULL,
  `operatingSystem` varchar(50) DEFAULT NULL,
  `pageTitle` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `country` varchar(50) DEFAULT NULL,
  `activeUsers` int(11) DEFAULT NULL,
  `newUsers` int(11) DEFAULT NULL,
  `totalUsers` int(11) DEFAULT NULL,
  `screenPageViews` int(11) DEFAULT NULL,
  `screenPageViewsPerUser` float DEFAULT NULL,
  `eventCount` int(11) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
);
#------------------------------------InlineChat----------------------------------------------------
CREATE TABLE `crm_smm_tinlinechat` (
  `inlinechat_gid` int(32) NOT NULL AUTO_INCREMENT,
  `user_id` varchar(256) DEFAULT NULL,
  `user_name` varchar(1000) DEFAULT NULL,
  `user_mail` varchar(1000) DEFAULT NULL,
  `email_verified` varchar(12) DEFAULT NULL,
  `user_type` varchar(12) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `ip_address` varchar(128) DEFAULT NULL,
  `country` varchar(256) DEFAULT NULL,
  `country_code` varchar(128) DEFAULT NULL,
  `region` varchar(256) DEFAULT NULL,
  `city` varchar(256) DEFAULT NULL,
  `timezone` varchar(1000) DEFAULT NULL,
  `latitude` varchar(256) DEFAULT NULL,
  `longitude` varchar(256) DEFAULT NULL,
  `page_openedat` datetime DEFAULT NULL,
  `page_url` varchar(1000) DEFAULT NULL,
  `title` varchar(128) DEFAULT NULL,
  `threads_count` varchar(256) DEFAULT NULL,
  `visits_count` varchar(256) DEFAULT NULL,
  `page_views_count` varchar(256) DEFAULT NULL,
  `user_agent` longtext,
  PRIMARY KEY (`inlinechat_gid`)
) ;

CREATE TABLE `crm_smm_tchatmessage` (
  `chat_id` varchar(128) NOT NULL,
  `created_date` datetime DEFAULT NULL,
  `user_id` varchar(1000) DEFAULT NULL,
  `user_name` varchar(1000) DEFAULT NULL,
  `user_mail` varchar(1000) DEFAULT NULL,
  `event_id` varchar(128) DEFAULT NULL,
  `event_type` varchar(64) DEFAULT NULL,
  `useradd_flag` varchar(12) DEFAULT 'Unassigned',
  PRIMARY KEY (`chat_id`)
);

CREATE TABLE `crm_smm_tchatmessagedtl` (
  `event_id` varchar(128) NOT NULL,
  `user_id` varchar(1000) DEFAULT NULL,
  `chat_id` varchar(128) DEFAULT NULL,
  `thread_id` varchar(128) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `event_type` varchar(64) DEFAULT NULL,
  `chatted_at` datetime DEFAULT NULL,
  `message` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image_url` longtext,
  `image_name` varchar(1000) DEFAULT NULL,
  `alternative_name` varchar(1000) DEFAULT NULL,
  `author_id` varchar(1000) DEFAULT NULL,
  `agent_id` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`event_id`)
) ;

CREATE TABLE `crm_smm_tchatmessagethread` (
  `thread_id` varchar(128) NOT NULL,
  `chat_id` varchar(128) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `nextthread_id` varchar(1000) DEFAULT NULL,
  `previousthread_id` varchar(1000) DEFAULT NULL,
  `next_page_id` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`thread_id`)
) ;

CREATE TABLE `crm_smm_tinlinechatservice` (
  `s_no` int(11) NOT NULL AUTO_INCREMENT,
  `id` varchar(64) DEFAULT NULL,
  `access_token` varchar(1000) DEFAULT NULL,
  `livechat_status` varchar(64) DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `created_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`s_no`)
);
alter table crm_smm_tchatmessage add column leadbank_gid varchar (64);

alter table crm_smm_tchatmessagedtl add column leadbank_gid varchar (64);

alter table crm_smm_tchatmessage add column leadbank_gid varchar (64);

alter table crm_smm_tchatmessagedtl add column leadbank_gid varchar (64);


#-------------------------------------------------------priyanga-------------------------------
INSERT INTO `vcidex`.`crm_smm_tinstagramservice` (`s_no`, `access_token`, `created_date`, `created_by`, `instagram_status`) VALUES ('1', 'IGQWROeXE5TkRPYXA2ZAzdoNUtsLVdoQmFaMGxaWUJlS1pZATmVNQlFNYy1SX0ZAtNzRVMm13V0RNTl9rVEZAEc09GYTdIalZAFcFdUdC1uWHltd2dUSDFfb04xSkZAJVUlLN0RPRjhBUVB2UEdHREpDVFE0YkdTUEg0aTAZD', '2024-02-14 00:00:00', 'SUSM1905280009', 'N');
CREATE TABLE `crm_smm_tinstagramservice` ( `s_no` int(11) NOT NULL AUTO_INCREMENT, 
  `access_token` varchar(1000) DEFAULT NULL,  `created_date` datetime DEFAULT NULL, 
  `created_by` varchar(64) DEFAULT NULL,  `updated_date` datetime DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,  `instagram_status` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`s_no`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
CREATE TABLE `crm_smm_tfacebookservice` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `page_id` varchar(64) DEFAULT NULL,
   `access_token` varchar(1000) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `facebook_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ;
INSERT INTO `vcidex`.`crm_smm_tfacebookservice` (`s_no`, `page_id`, `access_token`, `created_date`, `created_by`, `updated_date`, `updated_by`, `facebook_status`) VALUES ('1', '176470792209680', 'EAAFA9zGloSABO2YTL2ZCnoLZCeGv8UGOEIfNTakaR1nDHJYnpZBvyr8R4maZCKlH0j1ySKq9lPzaeo2ynNLObuTUdj83sH7x4KkJsiHZCsqdSKbBhzSdKmNe1pZCHABeYZA5LWs4xtaxd1DhIUNu9SxRqYVdIf8gRpgda8RL9i5gbaaQfaA8RsqrL1uzUgh0W0ZD', '2023-12-19 00:00:00', 'SUSM1709250001', '2023-12-29 09:45:06', 'SUSM1709250001', 'N');
INSERT INTO `vcidex`.`crm_smm_mail_service` (`s_no`, `access_token`, `base_url`, `updated_by`, `updated_date`, `sending_domain`, `receiving_domain`, `email_username`, `email_status`) VALUES ('1', '14e9f31c9e5002fb9dcf7e28b89f7d0d92759a4c', '\\nhttps://api.sparkpost.com', 'SUSM1709250002', '2024-02-16 15:29:34', 'company@reply.vcidex.com', 'company@receive.vcidex.com', 'VCIDEX', 'Y');
CREATE TABLE `crm_smm_mail_service` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `access_token` varchar(1000) DEFAULT NULL,
   `base_url` varchar(1000) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `sending_domain` varchar(64) DEFAULT NULL,
   `receiving_domain` varchar(64) DEFAULT NULL,
   `email_username` varchar(1024) DEFAULT NULL,
   `email_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ;
CREATE TABLE `crm_smm_whatsapp_service` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `workspace_id` varchar(1000) DEFAULT NULL,
   `channel_id` varchar(1000) DEFAULT NULL,
   `access_token` varchar(1000) DEFAULT NULL,
   `channel_name` varchar(200) DEFAULT NULL,
   `mobile_number` varchar(200) DEFAULT NULL,
   `channelgroup_id` varchar(200) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `whatsapp_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ;
INSERT INTO `vcidex`.`crm_smm_whatsapp_service` (`s_no`, `workspace_id`, `channel_id`, `access_token`, `channel_name`, `mobile_number`, `channelgroup_id`, `created_date`, `created_by`, `updated_by`, `updated_date`, `whatsapp_status`) VALUES ('1', '285d582b-58e9-4591-b600-db16c9b1a9cc', '1e10bea3-b9d4-4373-a1dc-c493f05e274d', 'AccessKey hbpyhmx674RFxij95Jujg1K5ebOhbEif0EGL', 'VCIDEX Solutions', '+919360389967', 'fb8c5d1d-f684-4ef8-bce6-9e7f1a610a48', '2023-12-06 00:00:00', 'SUSM1709250002', 'SUSM2010090003', '2024-02-22 15:00:48', 'Y');
 
CREATE TABLE `crm_smm_ttelegramservice` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `bot_id` varchar(1000) DEFAULT NULL,
   `chat_id` varchar(1000) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `telegram_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
);
INSERT INTO `vcidex`.`crm_smm_ttelegramservice` (`s_no`, `bot_id`, `chat_id`, `created_date`, `created_by`, `telegram_status`) VALUES ('1', 'bot6684855132:AAGm3867vW-kITkULJWPPimqdG6TdCCqt7M', '@MYSOFTWAREDEVLEOPERGROUP', '2023-12-19 00:00:00', 'SUSM1709250001', 'N');
CREATE TABLE `crm_smm_shopify_service` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `access_token` varchar(1000) DEFAULT NULL,
   `shopify_store_name` varchar(1000) DEFAULT NULL,
   `store_month_year` varchar(1000) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `shopify_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ;
INSERT INTO `vcidex`.`crm_smm_shopify_service` (`s_no`, `access_token`, `shopify_store_name`, `store_month_year`, `created_date`, `created_by`, `updated_by`, `updated_date`, `shopify_status`) VALUES ('1', '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', 'eed4dc-55', '2023-10', '2024-02-08 00:00:00', 'SUSM1709250001', 'SUSM1709250003', '2024-02-17 12:24:16', 'Y');
 
CREATE TABLE `crm_mst_tcustomertype` (
   `customertype_gid` varchar(16) NOT NULL,
   `customer_type` varchar(45) DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   PRIMARY KEY (`customertype_gid`),
   KEY `Index_2` (`customer_type`)
);
INSERT INTO `vcidex`.`crm_mst_tcustomertype`
(`customertype_gid`,
`customer_type`,
`created_by`,
`updated_by`,
`created_date`,
`updated_date`)
VALUES
('BCRT240331000', 'Retailer', 'SUSM1709250001', 'SUSM1709250001', '2024-02-06 09:51:19', '2024-02-21 16:34:17'
);
INSERT INTO `vcidex`.`crm_mst_tcustomertype`
(`customertype_gid`,
`customer_type`,
`created_by`,
`updated_by`,
`created_date`,
`updated_date`)
VALUES
('BCRT240331001', 'Distributor', 'SUSM1709250001', 'SUSM1709250001', '2024-02-06 09:51:34', '2024-02-21 15:26:18'
);
INSERT INTO `vcidex`.`crm_mst_tcustomertype`
(`customertype_gid`,
`customer_type`,
`created_by`,
`updated_by`,
`created_date`,
`updated_date`)
VALUES
('BCRT240331002', 'Corporate', 'SUSM1709250001', 'SUSM1709250001', '2024-02-06 09:51:45', '2024-02-21 16:34:26'
);
INSERT INTO `vcidex`.`crm_smm_tlinkedinservice` (`s_no`, `access_token`, `created_date`, `created_by`, `updated_date`, `updated_by`, `linkedin_status`) VALUES ('1', 'Bearer AQVtKI9QB9EugLW6WMkvADzSebCBSqO2Pf3qjm4ijHDI4_Qxr0W8gSliXL5UDXyqKFVHarGD9dHef0xXxAqsVtpBi-Q576Wk8pCfYqo8TcSj4yAlHTxkwTIl2CNk7yXwZDqDAK1-ULZXUdfCKD9sbK_Mpo-rxGqXmUarUvG-8lvYxCJvcYOb1yJQYTbtkfBIEI-ZqZ3e94SSRQqbTYg2UeRdnq9akVlHEPR5VXouITPNo4944BzyfwieDA05Tex4SD2lVy3ylJppAS7gB7RCWuy_ElM4_0tsimfal-8qNChYJol2QsKMk02cqlTxXtFWPjNlCenhpOqkSDhTyEinbHOThNRPEg', '2023-12-20 00:00:00', 'SUSM1709250001', '2024-02-12 17:26:01', 'SUSM1709250002', 'Y');
 
CREATE TABLE `crm_smm_tlinkedinservice` (
   `s_no` int(11) NOT NULL AUTO_INCREMENT,
   `access_token` varchar(1000) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL,
   `linkedin_status` varchar(64) DEFAULT NULL,
   PRIMARY KEY (`s_no`)
) ;
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstRegionsummary' WHERE (`module_gid` = 'MKTMSTRGN');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstSourcesummary' WHERE (`module_gid` = 'MKTMSTSRC');
CREATE TABLE `crm_mst_callresponse` (
   `call_response` varchar(64) DEFAULT NULL,
   `moving_stage` varchar(64) DEFAULT NULL,
   `created_by` varchar(32) DEFAULT NULL,
   `created_date` date DEFAULT NULL,
   `leadstage_gid` int(10) unsigned DEFAULT NULL,
   `callresponse_code` varchar(45) DEFAULT NULL,
   `callresponse_gid` varchar(64) DEFAULT NULL
) ;
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstProductgroupsummary' WHERE (`module_gid` = 'MKTPDMPDG');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstProductsummary' WHERE (`module_gid` = 'MKTPDMPDT');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmTrnRetailerRegisterLead' WHERE (`module_gid` = 'MKTLEDLAD');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmTrnRetailerLeadBank' WHERE (`module_gid` = 'MKTLEDLDB');
UPDATE `vcidex`.`adm_mst_tmodule` SET `sref` = '/crm/CrmSmmTelegramsummary' WHERE (`module_gid` = 'MKTMCNTEG');

UPDATE `boba_tea`.`adm_mst_tmodule` SET `sref` = '/crm/CrmMstProductunitsummary' WHERE (`module_gid` = 'MKTPDMPOU');

--------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE `crm_trn_tcustomertypelog` (
  `customertypelog_gid` varchar(16) NOT NULL,
  `pre_customertype_gid` varchar(16) NOT NULL,
  `pre_customertype` varchar(45) DEFAULT NULL,
  `curr_customertype` varchar(45) DEFAULT NULL,
  `updated_by` varchar(64) DEFAULT NULL,
  `updated_date` datetime DEFAULT NULL,
  PRIMARY KEY (`customertypelog_gid`),
  KEY `Index_2` (`pre_customertype`)
);
update crm_trn_tleadbank set customertype_gid = 'BCRT240331002';

update crm_trn_tleadbank set customer_type = 'Corporate';
create table crm_trn_tgmailtemplate 
( template_gid int auto_increment primary key,template_name varchar(1024), template_subject varchar(1024), template_body longtext,created_by varchar(64),created_date datetime
);
create table  crm_smm_gmail_service (s_no int,client_id longtext,client_secret longtext,refresh_token longtext,gmail_address varchar(1024));
insert into crm_smm_gmail_service values('1','948982844176-3tc97upso97s9touaupqcq1oirbdqgg7.apps.googleusercontent.com','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','1//0g4Q0Kc1_gtz-CgYIARAAGBASNwF-L9IrKm7-IV-LxyY-l1xDSLa0VOZ_cdsvi_-nZ-bLWFWQg2yTSApBJcRfOxCzGj4rta_ksgg','vishwalogesh.yoshops@gmail.com');
alter table crm_trn_tgmailtemplate add column template_flag varchar(1) default 'Y' after template_body;
ALTER TABLE crm_smm_whatsapptemplate CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE TABLE `crm_trn_gmail` (
  `gmail_gid` varchar(256) COLLATE utf8mb4_bin NOT NULL,
  `from_mailaddress` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `to_mailaddress` varchar(64) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_by` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `sent_flag` varchar(1) COLLATE utf8mb4_bin DEFAULT 'N',
  `direction` varchar(10) COLLATE utf8mb4_bin DEFAULT 'outgoing',
  `transmission_id` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `template_gid` varchar(32) COLLATE utf8mb4_bin DEFAULT NULL,
  `read_flag` char(1) COLLATE utf8mb4_bin DEFAULT 'N',
  `leadbank_gid` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`gmail_gid`),
  KEY `gmail_gid` (`gmail_gid`)
)
alter table crm_trn_gmail add column file_path longtext after transmission_id;

alter table crm_trn_gmail add column mail_subject longtext after to_mailaddress;

alter table crm_trn_gmail add column mail_body longtext after mail_subject;

alter table crm_smm_gmail_service add column created_by varchar(64) DEFAULT NULL;

alter table crm_smm_gmail_service add column `created_date` datetime DEFAULT NULL;

alter table crm_smm_gmail_service add column `updated_date` datetime DEFAULT NULL;

alter table crm_smm_gmail_service add column updated_by varchar(64) DEFAULT NULL;
CREATE TABLE `crm_trn_gmail` (
  `gmail_gid` int(11) NOT NULL AUTO_INCREMENT,
  `from_mailaddress` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `to_mailaddress` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `mail_subject` longtext,
  `mail_body` longtext,
  `created_by` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `created_date` datetime DEFAULT NULL,
  `sent_flag` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'N',
  `direction` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'outgoing',
  `transmission_id` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `file_path` longtext,
  `template_gid` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `read_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'N',
  `leadbank_gid` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`gmail_gid`),
  KEY `gmail_gid` (`gmail_gid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;

CREATE TABLE `crm_smm_tgoogleanalyticsservice` (
   `s_no` int(11)primary key NOT NULL AUTO_INCREMENT,
   `user_url` varchar(64) DEFAULT NULL,
   `page_url` varchar(1000) DEFAULT NULL,
   `googleanalytics_status` varchar(64) DEFAULT NULL,
   `created_date` datetime DEFAULT NULL,
   `created_by` varchar(64) DEFAULT NULL,
   `updated_date` datetime DEFAULT NULL,
   `updated_by` varchar(64) DEFAULT NULL
);

alter table crm_mst_tcustomertype add column order_by int (2);

alter table crm_mst_tcustomertype add column display_name varchar (120);

UPDATE `boba_tea`.`crm_mst_tcustomertype` SET `display_name` = 'Corporate', `order_by` = '1' WHERE (`customertype_gid` = 'BCRT240331000');
UPDATE `boba_tea`.`crm_mst_tcustomertype` SET `display_name` = 'Distributor', `order_by` = '2' WHERE (`customertype_gid` = 'BCRT240331001');
UPDATE `boba_tea`.`crm_mst_tcustomertype` SET `display_name` = 'Retailer', `order_by` = '3' WHERE (`customertype_gid` = 'BCRT240331002');

------------------------------------------------------------------------------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE `crm_trn_spleadbankcorporate`()
BEGIN
SELECT a.leadbank_gid,b.leadbankcontact_gid,a.leadbank_pin, a.leadbank_id,a.approval_flag,m.country_name,b.mobile,a.leadbank_state,b.email,b.address1,b.address2,a.leadbank_city,  a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
                     a.remarks,CONCAT(IFNULL(b.leadbankcontact_name, ''),' / ',IFNULL(b.country_code1, ''), IFNULL('-', ''),IFNULL(b.mobile, ''),' / ',IFNULL(b.email, '')) AS contact_details,a.lead_status,  
                     concat(d.region_name,' / ',a.leadbank_city,' / ',a.leadbank_state) as region_name,  
                     concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
                     date_format(a.created_date,'%d-%m-%Y') as created_date, 
                     CONCAT(CASE WHEN e.source_name IS NULL THEN '' ELSE e.source_name END,' / ',CASE WHEN l.categoryindustry_name IS NULL THEN '' ELSE l.categoryindustry_name END) AS source_name, 
                     ( g.assign_to )as assigned ,   
                     concat(j.user_firstname,' ',j.user_lastname) as assign_to from crm_trn_tleadbank a   
                     left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
                     left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
                     left join crm_mst_tsource e on a.source_gid=e.source_gid   
                     left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
                     left join hrm_mst_temployee k on a.created_by=k.employee_gid   
                     left join adm_mst_tuser c on  c.user_gid = k.user_gid   
                     left join crm_trn_tlead2campaign g on a.leadbank_gid = g.leadbank_gid  
                     left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
                     left join adm_mst_tuser j on h.user_gid = j.user_gid  
                     left join adm_mst_tcountry m on a.leadbank_country = m.country_gid  
                     where a.customertype_gid = 'BCRT240331000' 
                     group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
END$$
DELIMITER ;


DELIMITER $$
CREATE PROCEDURE `crm_trn_spleadbankdistributor`()
BEGIN
SELECT a.leadbank_gid,b.leadbankcontact_gid,a.leadbank_pin, a.leadbank_id,a.approval_flag,m.country_name,b.mobile,a.leadbank_state,b.email,b.address1,b.address2,a.leadbank_city, a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
                     a.remarks,CONCAT(IFNULL(b.leadbankcontact_name, ''),' / ',IFNULL(b.country_code1, ''), IFNULL('-', ''),IFNULL(b.mobile, ''),' / ',IFNULL(b.email, '')) AS contact_details,a.lead_status,  
                     concat(d.region_name,' / ',a.leadbank_city,' / ',a.leadbank_state) as region_name,  
                     concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
                     date_format(a.created_date,'%d-%m-%Y') as created_date, 
                     CONCAT(CASE WHEN e.source_name IS NULL THEN '' ELSE e.source_name END,' / ',CASE WHEN l.categoryindustry_name IS NULL THEN '' ELSE l.categoryindustry_name END) AS source_name, 
                     ( g.assign_to )as assigned ,   
                     concat(j.user_firstname,' ',j.user_lastname) as assign_to from crm_trn_tleadbank a   
                     left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
                     left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
                     left join crm_mst_tsource e on a.source_gid=e.source_gid   
                     left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
                     left join hrm_mst_temployee k on a.created_by=k.employee_gid   
                     left join adm_mst_tuser c on  c.user_gid = k.user_gid   
                     left join crm_trn_tlead2campaign g on a.leadbank_gid = g.leadbank_gid  
                     left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
                     left join adm_mst_tuser j on h.user_gid = j.user_gid  
                     left join adm_mst_tcountry m on a.leadbank_country = m.country_gid  
                     where a.customertype_gid = 'BCRT240331001' 
                     group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
END$$
DELIMITER ;


DELIMITER $$
CREATE PROCEDURE `crm_trn_spleadbankretailer`()
BEGIN
   SELECT a.leadbank_gid,b.leadbankcontact_gid,a.leadbank_pin, a.leadbank_id,a.approval_flag,m.country_name,b.mobile,a.leadbank_state,b.email,b.address1,b.address2,a.leadbank_city, a.leadbank_name,b.leadbankcontact_name,ifnull(a.customer_gid,' ') as customer_gid, 
       a.remarks,CONCAT(IFNULL(b.leadbankcontact_name, ''),' / ',IFNULL(b.country_code1, ''), IFNULL('-', ''),IFNULL(b.mobile, ''),' / ',IFNULL(b.email, '')) AS contact_details,a.lead_status,  
       concat(d.region_name,' / ',a.leadbank_city,' / ',a.leadbank_state) as region_name,  
       concat(b.address1,b.address2) As address_details,a.customer_type,concat_ws('-',c.user_firstname,c.user_lastname) as created_by, 
       date_format(a.created_date,'%d-%m-%Y') as created_date, 
       CONCAT(CASE WHEN e.source_name IS NULL THEN '' ELSE e.source_name END,' / ',CASE WHEN l.categoryindustry_name IS NULL THEN '' ELSE l.categoryindustry_name END) AS source_name, 
       ( g.assign_to )as assigned ,   
       concat(j.user_firstname,' ',j.user_lastname) as assign_to from crm_trn_tleadbank a   
       left join crm_trn_tleadbankcontact b on a.leadbank_gid=b.leadbank_gid   
       left join crm_mst_tregion d on a.leadbank_region=d.region_gid   
       left join crm_mst_tsource e on a.source_gid=e.source_gid   
       left join crm_mst_tcategoryindustry l on a.categoryindustry_gid = l.categoryindustry_gid   
       left join hrm_mst_temployee k on a.created_by=k.employee_gid   
       left join adm_mst_tuser c on  c.user_gid = k.user_gid   
       left join crm_trn_tlead2campaign g on a.leadbank_gid = g.leadbank_gid  
       left join hrm_mst_temployee h on  g.assign_to = h.employee_gid   
       left join adm_mst_tuser j on h.user_gid = j.user_gid  
       left join adm_mst_tcountry m on a.leadbank_country = m.country_gid  
       where a.customertype_gid = 'BCRT240331002' 
       group by a.leadbank_gid Order by date(a.created_date) desc,a.created_date asc,a.leadbank_gid desc;
END$$
DELIMITER ;

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