-- Whatsapp config table
CREATE TABLE `bf_whatsapp_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `also_send_sms` enum('y','n') DEFAULT 'n',
  `msg_template` longtext,
  `msg_payload` longtext,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- date_added column added in bf_whatsapp_config table
ALTER TABLE `bf_whatsapp_config` ADD COLUMN date_added datetime default CURRENT_TIMESTAMP after msg_payload;

-- is_demo, demo_type coulumn in bf_whatsapp_config table
ALTER TABLE `bf_whatsapp_config` ADD COLUMN is_demo enum('n','y') default 'n' after also_send_sms, ADD COLUMN demo_type enum('link','file') default 'link' after is_demo;

-- title coulumn in bf_whatsapp_config table
ALTER TABLE `bf_whatsapp_config` ADD COLUMN title varchar(255) default null after also_send_sms;


-- bf_whatsapp_queue table
CREATE TABLE `bf_whatsapp_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payload` longtext default null,
  `user_phone` text default null,
  `auth_token` VARCHAR(1024) default null,
  `wa_config_id` int(11) default null,
  `response` text DEFAULT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT null,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add bf_bulk_whatsapp table ; Entry by Kushagra on 170222
CREATE TABLE `bf_bulk_whatsapp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `wa_template_id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- Add bf_whatsapp_chatbot; Entry by Kushagra on 220222
CREATE TABLE `bf_whatsapp_chatbot` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `keyword` varchar(50) NOT NULL,
  `resp_payload` mediumtext,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add bf_whatsapp_incoming; Entry by Kushagra 220222
CREATE TABLE `bf_whatsapp_incoming` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `user_phone` varchar(20) DEFAULT NULL,
  `resp_text` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add commentary_mandatory column to bf_alt_feedback; Entry by Amarjeet 220222
ALTER TABLE `bf_alt_feedback` ADD COLUMN commentary_mandatory enum('n', 'y') default 'n' after show_commentary;

-- Add template_var column to bf_whatsapp_chatbot; Entry by Amarjeet 220222
ALTER TABLE `bf_whatsapp_chatbot` ADD COLUMN template_var text default NULL after resp_payload;

-- Add bf_whatsapp_auth_token; Entry by Amarjeet 220222
CREATE TABLE `bf_whatsapp_auth_token` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `auth_token` varchar(1024) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Add purpose column in bf_whatsapp_auth_token; Entry by Amarjeet 230222
ALTER TABLE `bf_whatsapp_auth_token` ADD column purpose enum('b','o') default 'b' after merchant_id;

-- Add date_added column in bf_whatsapp_incoming; Entry by Amarjeet 230222
ALTER TABLE `bf_whatsapp_incoming` ADD COLUMN date_added datetime default CURRENT_TIMESTAMP;

-- Added by Kushagra on 240222
 alter table bf_whatsapp_queue modify column status enum ('a','i','e') NOT null default 'a';

-- Add purpose column in bf_whatsapp_config; Entry by Amarjeet 240222
ALTER TABLE `bf_whatsapp_config` ADD column purpose enum('b','o') default 'b' after merchant_id;

-- Add purpose, date_added column in bf_whatsapp_chatbot; Entry by Amarjeet 240222
ALTER TABLE `bf_whatsapp_chatbot` ADD column purpose enum('b','o') default 'b' after merchant_id, ADD COLUMN date_added datetime default CURRENT_TIMESTAMP;

-- Add credit_type column in bf_sms_credits; Entry by Amarjeet 280222
ALTER TABLE `bf_sms_credits` ADD column credit_type enum('s','w') default 's' after payment_mode;

-- Add bf_waoffers_payload; Entry by Amarjeet 030322
CREATE TABLE `bf_waoffers_payload` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `wa_media_id` varchar(255) default null,
  `payload` longtext DEFAULT null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- bf_whatsapp_queue table; Entry by Amarjeet 070322
CREATE TABLE `bf_whatsapp_offers_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payload` longtext default null,
  `user_phones` longtext default null,
  `auth_token` VARCHAR(1024) default null,
  `offer_url` varchar(1024) default null,
  `campaign_id` int(11) DEFAULT NULL,
  `response` text DEFAULT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT null,
  `status` enum('a','i','e') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Emoji supported Whatsapp Tables; Entry by Amarjeet 230322
ALTER TABLE `bf_whatsapp_chatbot` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `bf_whatsapp_config` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `bf_whatsapp_offers_queue` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `bf_whatsapp_queue` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `bf_coupon_schedule` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;
ALTER TABLE `bf_waoffers_payload` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;