-- add coupon_for column to bf_loyalty_coupon
alter table bf_loyalty_coupon add column coupon_for varchar(255) default 'pts-trigger' after coupon_type;
alter table bf_loyalty_coupon add column deno_id int(11) default null after coupon_for;

-- create table bf_coupon_denomination
CREATE TABLE `bf_coupon_denomination` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `denomination` varchar(255) DEFAULT NULL,
  `title` VARCHAR(1024) NOT NULL,
  `desc` text DEFAULT NULL,
  `disc_type` enum('a', 'p') NOT NULL DEFAULT 'A',
  `disc_value` int(11) NOT NULL,
  `min_order_value` int(11) DEFAULT NULL,
  `max_order_value` int(11) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- drop column coupon_for from bf_loyalty_coupon table
alter table bf_loyalty_coupon drop column coupon_for;

-- 'fd': Flat Discount ,'ua': User Acquisition Discount,'ptt' : pts Trigger,'pc': profile Completion
alter table bf_loyalty_coupon modify coupon_type enum('ua','ptt','pc') NOT NULL;
alter table bf_loyalty_coupon change coupon_type purpose enum('ua','ptt','pc') NOT NULL;

-- add column assigned_val_date to bf_loyalty_coupon
alter table bf_loyalty_coupon add column issue_val_date datetime DEFAULT NULL after `validity_date`;
alter table bf_loyalty_coupon change deno_id c_deno_id int(11) default null;

-- create table bf_coupon_config
CREATE TABLE `bf_coupon_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `purpose` VARCHAR(1024) NOT NULL,
  `c_deno_id` int(11) NOT NULL,
  `c_val_duration` int(11) NOT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add dynamic_coupon column to bf_merchantinfo table
alter table bf_merchantinfo add column dynamic_coupon enum('y','n') default null after cust_profile;

-- add event_date column to bf_coupon_config table
alter table bf_coupon_config add column event_date datetime default null after c_val_duration;
alter table bf_coupon_config modify column c_deno_id int(11) default null;
alter table bf_coupon_config modify column c_val_duration int(11) default null;

-- create table bf_coupon_schedule
CREATE TABLE `bf_coupon_schedule` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `conf_id` int(11) NOT NULL,
    `schedule_type` enum('p','a','o') NOT NULL,
    `days_val` VARCHAR(255) default NULL,
    `time_frame` enum('m','a','e','n') default null,
    `send_card` enum('y','n') default null,
    `card_id` int(11) default null,
    `status` enum('a','i') NOT NULL DEFAULT 'a',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table bf_coupon_schedule
add column template_id int(11) default null after card_id,
add column template_var text default null after template_id,
add column sms_text text default null after template_var;


-- change purpose from enum to varchar
alter table bf_loyalty_coupon modify column purpose varchar(50) DEFAULT NULL;

-- rename dynamic_coupon to billfree_coupons column to bf_merchantinfo table
alter table bf_merchantinfo change dynamic_coupon billfree_coupons enum('y','n') default null after cust_profile;

-- drop profile_coupon column from bf_merchantinfo
alter table bf_merchantinfo drop column profile_coupon;

-- add is_used and used_date column to bf_loyalty_coupon
alter table bf_loyalty_coupon add column is_used enum('y','n') default 'n' after is_shown, add column used_date datetime default null after start_date;

-- add c_deno_id column to bf_loyalty
alter table bf_loyalty add column c_deno_id int(11) default null after trigger_pt;

-- add profile_popup_heading, profile_popup_message column to bf_merchantinfo table
alter table bf_merchantinfo add column profile_popup_heading VARCHAR(255) default null after cust_profile, add column profile_popup_message VARCHAR(1024) default null after profile_popup_heading;
alter table bf_merchantinfo add column profile_page_heading VARCHAR(255) default null after profile_popup_message, add column profile_page_message VARCHAR(1024) default null after profile_page_heading;

-- drop column slug & sch_id from bf_loyalty_coupon table
alter table bf_loyalty_coupon drop column slug;
alter table bf_loyalty_coupon drop column sch_id;

-- create table bf_coupon_slug
CREATE TABLE `bf_coupon_slug` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(20) UNIQUE DEFAULT null,
  `merchant_id` int(11) NOT null,
  `c_id` int(11) DEFAULT NULL,
  `sch_id` int(11) DEFAULT NULL,
  `cust_phone` varchar(20) DEFAULT null,
  `purpose` VARCHAR(1024) NOT NULL,
  `date_added` datetime default CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add whatsapp_query column to bf_coupon_config table
alter table bf_coupon_config add column whatsapp_query varchar(255) default null after purpose;
alter table bf_coupon_config add column give_loyalty_pt enum('n','y') default null after whatsapp_query;
alter table bf_coupon_config add column point_val int(11) default null after give_loyalty_pt;

-- rename billfree_coupons to coupon_type column to bf_merchantinfo table
-- m -> merchant provided, b -> Billfree Coupon, n -> No
alter table bf_merchantinfo change billfree_coupons coupon_type enum('m','b','n') default null after cust_profile;

-- add max_disc_amt column to bf_loyalty_coupon
alter table bf_loyalty_coupon add column max_disc_amt int(11) default null after disc_type;

-- add is_open, and batch_id column to bf_coupon_slug table
alter table bf_coupon_slug add column is_open enum('y', 'n') default 'n' after purpose, add column batch_id varchar(255) default null after id;

-- create table bf_sms_campaign_ledger
CREATE TABLE `bf_sms_campaign_ledger` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `batch_id` varchar(255) UNIQUE NOT null,
  `merchant_id` int(11) NOT null,
  `purpose` varchar(255) DEFAULT NULL,
  `sent` int(11) DEFAULT NULL,
  `coupon_assigned` int(11) DEFAULT NULL,
  `date_added` datetime default CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- drop whatsapp_query column to bf_coupon_config table
alter table bf_coupon_config drop column whatsapp_query;

-- add card_id column to bf_coupon_slug; Entry by Amarjeet 090322
ALTER TABLE bf_coupon_slug add column card_id int(11) default null after sch_id;

-- Add channel_type column in bf_coupon_config; Entry by Amarjeet 160322
ALTER TABLE `bf_coupon_config` ADD column channel_type enum('s','w') default 's' after purpose;

-- Add wa_payload, wa_template_id column in bf_coupon_schedule; Entry by Amarjeet 160322
ALTER TABLE `bf_coupon_schedule` ADD column wa_payload longtext default null after sms_text;
ALTER TABLE `bf_coupon_schedule` ADD column wa_template_id int(11) default null after wa_payload;

-- Add wa_sent column in bf_sms_campaign_ledger; Entry by Amarjeet 230322
ALTER TABLE `bf_sms_campaign_ledger` ADD column wa_sent int(11) DEFAULT NULL after sent;