-- This file will have all the changes done to existing tables after the time the dbback was created, so that whenever a db change is made on say my local computer, then I shall write the change down here and push it so that say avinash takes a pull and makes the changes from this file to his db so as to avoid any unexpected db error.

-- Note: The contents of changes.sql need to be cleared if a new dbback is created on any day and stored in the sql folder and pushed. Whenever such a thing is done, it means that the db changes were massive and it is better to take a fresh dump.

*****************************************************************************************************************************************************************************************************************************************************************************


-- Latest dump is available in the sql/ folder


-- Ad. Banner
CREATE TABLE `bf_ad_banner` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `title` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `url` longtext COLLATE utf8_unicode_ci,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` enum('a','i') COLLATE utf8_unicode_ci DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


-- deactivation queue
CREATE TABLE `bf_deact_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  `added_by` int(11) NOT NULL,
  `status` enum('a','i') COLLATE utf8_unicode_ci DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;



-- Issue #188 sql changes
CREATE TABLE `bf_merchant_credits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `dr` int(11) DEFAULT NULL,
  `cr` int(11) DEFAULT NULL,
  `purpose` varchar(50) DEFAULT NULL,
  `purpose_rcpt_id` int(11) DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `added_by` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Issue #197 sql changes

CREATE TABLE `bf_sms_delivery` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `request_id` varchar(24) DEFAULT NULL,
  `date` datetime DEFAULT NULL,
  `number` varchar(12) DEFAULT NULL,
  `descr` varchar(10) DEFAULT NULL,
  `raw_data` longtext,
  `date_added` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- issue #199 sql changes

 alter table bf_merchantinfo add column show_reviews enum('y','n') default 'n';



-- #208 changes
alter table bf_merchantinfo add column custom_sms_text text default null after custom_sender_flag;


-- #211 changes
alter table bf_voucher_payment add column cred_amt_paid varchar(255) default null after wire_ref_no;
alter table bf_voucher_payment add column cred_amt_outstanding varchar(255) default null after cred_amt_paid;
alter table bf_users add column gen_bill_dyn_fields text default null after gen_bill_access;
alter table bf_vouchers add column dyn_fields longtext default null after party_email;



-- #212 changes
alter table bf_merchantinfo add column google_display_name varachar(2048);


-- sql for commentary flag on feedback popup


-- #213
CREATE TABLE `bf_archive_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `status` enum('a','i') CHARACTER SET latin1 DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `bf_bills_archive` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) DEFAULT NULL,
  `bill_id` int(11) DEFAULT NULL,
  `bill_date` datetime DEFAULT NULL,
  `bill_type` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `bill_amount` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `date_added` datetime DEFAULT NULL,
  `file_path` varchar(2048) CHARACTER SET latin1 DEFAULT NULL,
  `fb_type` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `rating` int(11) DEFAULT NULL,
  `comment` longtext CHARACTER SET latin1,
  `low_rating_title` varchar(2048) CHARACTER SET latin1 DEFAULT NULL,
  `mid_rating_title` varchar(2048) CHARACTER SET latin1 DEFAULT NULL,
  `high_rating_title` varchar(2048) CHARACTER SET latin1 DEFAULT NULL,
  `user_phone` varchar(50) CHARACTER SET latin1 DEFAULT NULL,
  `fb_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;




-- #215
alter table bf_bills_feedback add column remarks longtext default null after comment;
alter table bf_bills_archive add column fb_id int(11) default null after fb_date;
alter table bf_bills_archive add column fb_remarks longtext default null after fb_id;
alter table bf_bills_archive add column fb_remarks longtext default null after fb_date;
alter table bf_merchantinfo add column followup_sms_text text default null after custom_sms_text


--Repudation Defender
alter table bf_merchantinfo add column repu_def enum('y','n') default 'n' after show_reviews;


--#217 feedback-comment-heirarchy

CREATE TABLE `bf_feedback_autofill` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` longtext COLLATE utf8_unicode_ci NOT NULL,
  `parent_id` int(11) NOT NULL,
  `status` enum('i','a') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 CREATE TABLE `bf_fb_merchant_mapping` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `fb_autofill_id` varchar(2048) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` enum('i','a') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;





-- New layout for bill with purchase history
alter table bf_merchantinfo add column repu_def enum('y','n') default 'n' after show_reviews;



-- Universal pos type


alter table bf_merchantinfo add column config_updated enum('y','n') default 'n' after system_used;


-- Whatsapp query number in merchant offer

alter table bf_merchant_offers add column whatsapp_query_no varchar(50) after merchant_id;

-- KOT configuration updated flag
alter table bf_merchantinfo add column kot_config_updated enum('y','n') default 'n' after system_used;


-- offer type with/without link
alter table bf_merchant_offers add column offer_link_type varchar(100) default  'with link' after sms_text;

-- API changes for Feedback mobile application

CREATE TABLE `bf_feedback_ques` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(12) NOT NULL,
  `type` enum('heading','question') COLLATE utf8_unicode_ci NOT NULL,
  `text` longtext COLLATE utf8_unicode_ci NOT NULL,
  `icon_link` varchar(500) COLLATE utf8_unicode_ci DEFAULT NULL,
  `parent_id` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE `bf_feedback_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mobile` varchar(15) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` datetime DEFAULT NULL,
  `note` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`)
);



CREATE TABLE `bf_feedbacks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `rating` int(11) NOT NULL,
  `comment` longtext COLLATE utf8_unicode_ci,
  `quesId` int(11) NOT NULL,
  `feedUserId` int(11) NOT NULL,
  PRIMARY KEY (`id`)
);


-- Adding extra fields to bf_bills_extra

alter table bf_bills_extra add column cust_bday datetime,add column cust_anniv datetime,add column shipped_by varchar(255),add column quantity varchar(20) after dist_code;


-- Company name match type

 alter table bf_merchantinfo add column comp_exact_match enum('y','n') default 'n' after comp_name_keyword;


-- Custom Admin Privileges

alter table bf_users add column custom_admin_access varchar(2048) after admin_access;


-- loyalty system---
CREATE TABLE `bf_points` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `merchant_id` int(11) NOT NULL,
	  `user_phone` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
	  `bill_id` int(11) DEFAULT NULL,
	  `bill_type` enum('t','r') COLLATE utf8_unicode_ci DEFAULT NULL,
	  `bill_amount` int(11) DEFAULT NULL,
	  `dr` int(11) DEFAULT '0',
	  `cr` int(11) DEFAULT '0',
	  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
	  `date_updated` datetime DEFAULT CURRENT_TIMESTAMP,
	  `status` enum('a','i') COLLATE utf8_unicode_ci DEFAULT 'a',
	  PRIMARY KEY (`id`)
);

-- loyalty system updated----
alter table bf_vouchers add column loyalty_points_redeemed int default 0;
-- callback option--
alter table bf_alt_feedback add column show_req_callback enum('y','n') default 'n' after show_commentary;

-- loyalty system updated
alter table bf_merchantinfo drop column enable_loyalty,drop column rupee_in_points,drop column loyalty_conversion_factor;

-- New stuff

CREATE TABLE `bf_loyalty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `setup` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `inr_to_pts` varchar(50) COLLATE utf8_bin DEFAULT '1',
  `pt_to_inr` varchar(50) COLLATE utf8_bin DEFAULT '1',
  `redeem_same_day` enum('y','n') COLLATE utf8_bin DEFAULT 'y',
  `min_pts_redeemable` int(11) DEFAULT '1',
  `max_pts_redeemable_as_prcnt_of_amt` int(11) DEFAULT '100',
  `otp_verification` enum('y','n') COLLATE utf8_bin DEFAULT 'n',
  `status` enum('a','i') COLLATE utf8_bin DEFAULT 'a',
  PRIMARY KEY (`id`),
  UNIQUE KEY `merchant_id` (`merchant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Store invoice number in points table too, could be useful!
alter table bf_points add column inv_no varchar(50) after bill_amount;


-- Added payment link transactions table
CREATE TABLE `bf_transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `user_phone` varchar(20) NOT NULL,
  `amount` int(11) DEFAULT NULL,
  `txn_id` varchar(50) NOT NULL,
  `pay_link` text,
  `payu_ref_id` varchar(128) DEFAULT NULL,
  `mode` varchar(50) DEFAULT NULL,
  `bank_ref_num` varchar(50) DEFAULT NULL,
  `PG_TYPE` varchar(50) DEFAULT NULL,
  `card_no` varchar(50) DEFAULT NULL,
  `card_type` varchar(20) DEFAULT NULL,
  `name_on_card` varchar(255) DEFAULT NULL,
  `bill_id` int(30) DEFAULT NULL,
  `bill_type` enum('r','t') DEFAULT NULL,
  `status` enum('s','f','p','c') DEFAULT 'p',
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL,
  `date_paid` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `txn_id` (`txn_id`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8;

-- Added table bf_paymentlink
CREATE TABLE `bf_paymentlink` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `cc` varchar(10) DEFAULT NULL,
  `dc_s` varchar(10) DEFAULT NULL,
  `dc_l` varchar(10) DEFAULT NULL,
  `nb_hdfc_icici` varchar(10) DEFAULT NULL,
  `nb_others` varchar(10) DEFAULT NULL,
  `phonepe` varchar(10) DEFAULT NULL,
  `wallet` varchar(10) DEFAULT NULL,
  `upi` varchar(10) DEFAULT NULL,
  `amex` varchar(10) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`),
  UNIQUE KEY `merchant_id` (`merchant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

alter table bf_loyalty add column referrer_pts varchar(50) default null;
alter table bf_loyalty add column referee_pts varchar(50) default null;

alter table bf_transactions add column inv_no varchar(50) after bill_type;
alter table bf_points add column is_referral enum('y','n') default 'n' not null after cr;
alter table bf_points add column referrer_mob varchar(50) default null after is_referral;
alter table bf_points add column referee_mob varchar(50) default null after referrer_mob;

-- Added remarks column in bf_transactions
alter table bf_transactions add column remarks longtext after amount;



-- Added lconfig_updated
alter table bf_merchantinfo add column lconfig_updated enum ('y','n') default 'n' after kot_config_updated;


-- Adding prev_sender_id field to try to fix DLT stuff
alter table bf_merchantinfo add column prev_sender_id varchar(10) default null after sms_sender_id;
update bf_merchantinfo set prev_sender_id = sms_sender_id;
update bf_merchantinfo set sms_sender_id = 'BILLFR';


-- added payu_mid for settlement and stuff
alter table bf_paymentlink add column payu_mid varchar(50) default null after merchant_id;

alter table bf_transactions add column bankcode varchar(50) default null after mode;

-- Create settlement table
CREATE TABLE `bf_settlement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `bf_txn_id` int(11) NOT NULL,
  `total_amount` varchar(50) DEFAULT NULL,
  `sub_txn_id` varchar(50) DEFAULT NULL,
  `agg_charges` varchar(20) DEFAULT NULL,
  `split_details` varchar(50) DEFAULT NULL,
  `settlement_amt` varchar(20) DEFAULT NULL,
  `split_payment_id` varchar(50) DEFAULT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_updated` datetime DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- Custom zoom level for bill redir preview
alter table bf_merchantinfo add column bill_preview_zoom int default null after zoom;

-- Adding new field to send or skip bill without mobile number
ALTER TABLE bf_merchantinfo ADD column send_wo_mobile_bill enum('y','n') default 'y' AFTER system_used;

-- Show static tab checkbox
alter table bf_merchantinfo add column show_static_tab enum('y','n') default 'n' after show_purchase_history;
alter table bf_merchantinfo add column static_tab_title varchar(255) default null after show_static_tab;
alter table bf_merchantinfo add column static_tab_content text default null after static_tab_title;

-- Multiple Feedback Questions

alter table bf_alt_feedback add column parent_id int not null default '0' after status;


-- Disabling custom sms_text for all merchants temporarily
alter table bf_merchantinfo add column custom_sms_text_flag enum('y','n') default 'n' after custom_sender_flag;
update bf_merchantinfo set custom_sms_text_flag = 'y' where custom_sms_text is not null and custom_sms_text != "";

-- setup for 4 smiley pattern
alter table bf_alt_feedback add column 4_rating_title varchar(2048) null default null after mid_rating_title;


-- Queries backend
CREATE TABLE `bf_queries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) DEFAULT NULL,
  `user_phone` varchar(20) DEFAULT NULL,
  `merchant_city` varchar(200) DEFAULT NULL,
  `merchant_name` varchar(200) DEFAULT NULL,
  `bill_link` varchar(500) DEFAULT NULL,
  `ticket_id` varchar(20) DEFAULT NULL,
  `date_added` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- Add custom sms api column
alter table bf_merchantinfo add column custom_sms_api text default null after purpose_text;


-- add date_added/ date_updated
alter table qf_product_data add column date_updated datetime default null after date_added;

-- add is_printed column to bf_temp_bills table
alter table bf_temp_bills add column is_printed BOOLEAN default null after status;

-- add is_printed to bf_bills_archive table
alter table bf_bills_archive add column is_printed tinyint default null;

-- #219, daily report queue frequency option for multiple reports in same day
alter table bf_daily_report_queue add column frequency int default 1;

-- Add bill_amount field in temp table
alter table bf_temp_bills add column bill_amount varchar(50) default null after file_name;

-- #221 msg91 flow api changes
 CREATE TABLE `bf_msg91_flow` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `sender_id` varchar(10) NOT NULL,
  `flow_name` varchar(255) DEFAULT NULL,
  `flow_id` varchar(255) DEFAULT NULL,
  `sms_text` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add src column in bf_bills & bf_temp_bills
alter table bf_bills add column src enum('s', 'z', 'm', 'd', 't', 'o') default null;
alter table bf_temp_bills add column src enum('s', 'z', 'm', 'd', 't', 'o') default null after vou_id;

-- add popup_priority column to bf_merchantinfo table
alter table bf_merchantinfo add column popup_priority text default null after alt_popups;

-- add trigger_pt column to bf_loyalty table
alter table bf_loyalty add column trigger_pt varchar(50) default null after setup;

-- add lcoupon_id column to bf_points
alter table bf_points add column lcoupon_id int(11) default null after referee_mob;

-- change bill_type column to bf_points
alter table bf_points modify column bill_type enum('t', 'r', 'c') default null;

-- bf_loyalty_coupon table
 CREATE TABLE `bf_loyalty_coupon` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `user_phone` VARCHAR(255) DEFAULT NULL,
  `title` VARCHAR(1024) NOT NULL,
  `coupon_code` VARCHAR(255) NOT NULL,
  `desc` text DEFAULT NULL ,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add more coupon fields column to bf_loyalty_coupon
alter table bf_loyalty_coupon
add column bulk_csv_file varchar(255) default NULL after `desc`,
add column `coupon_type` enum('fd', 'ua') NOT NULL after `desc`,
add column `disc_type` enum('a', 'p') NOT NULL DEFAULT 'A' after `desc`,
add column `disc_value` int(11) NOT NULL after `desc`,
add column `min_order_value` int(11) DEFAULT NULL after `desc`,
add column `start_date` datetime DEFAULT NULL after `desc`,
add column `validity_date` datetime DEFAULT NULL after `desc`;

-- bf_offer_sms_var table
 CREATE TABLE `bf_offer_sms_var` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_id` int(11) NOT NULL,
  `var_name` VARCHAR(255) NOT NULL,
  `var_value` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- add is_shown column to bf_loyalty_coupon table
alter table bf_loyalty_coupon add column is_shown enum('n','y') default 'n' after status;
alter table bf_sms_queue add column flow_id varchar(255) default null after campaign_id, add column offer_url varchar(255) default null after sms_text;

-- bf_items_master table

-- bf_items_master table
 CREATE TABLE `bf_items_master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `title` VARCHAR(1024) NOT NULL,
  `hsn` VARCHAR(255) DEFAULT 0,
  `gst` VARCHAR(255) DEFAULT 0,
  `mrp` VARCHAR(255) DEFAULT 0,
  `discount` VARCHAR(255),
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add is_tested column to bf_campaign table
alter table bf_campaign add column is_tested enum('n', 'y') default null after validity_date;

-- add issue_date column to bf_loyalty_coupon
alter table bf_loyalty_coupon add column issue_date datetime DEFAULT NULL after user_phone;

-- ad_campaign table
CREATE TABLE `ad_campaign` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_name` VARCHAR(1024) NOT NULL,
  `user_phone` VARCHAR(255) NOT NULL,
  `user_email` VARCHAR(255) DEFAULT NULL,
  `lead_from` VARCHAR(1024) NOT NULL,
  `date_added` datetime default CURRENT_TIMESTAMP,
  `executed` enum('y','n') default 'n',
  `remarks` longtext default null,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- bf_items_tagging table
 CREATE TABLE `bf_items_tagging` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `item_id` int(11) NOT NULL,
  `vou_id` int(11) DEFAULT NULL,
  `tag_name` VARCHAR(255) NOT NULL,
  `tag_value` VARCHAR(255) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- bf_items_stock_ledger Table---
CREATE TABLE `bf_items_stock_ledger` (
	  `id` int(11) NOT NULL AUTO_INCREMENT,
	  `item_id` int(11) NOT NULL,
	  `dr` int(11) DEFAULT '0',
	  `cr` int(11) DEFAULT '0',
	  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
	  `status` enum('a','i') DEFAULT 'a',
	  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add row_id, stock_in_id, column to bf_items_tagging
alter table bf_items_tagging add column row_id int(11) NOT NULL after item_id, add column stock_id int(11) NOT NULL after id;

-- add comment column to ad_campaign table
alter table ad_campaign add column comment longtext default null after lead_from;
-- add channel column to ad_campaign table
alter table ad_campaign add column channel varchar(255) default null after lead_from;

-- add show_popups to bf_merchantinfo
alter table bf_merchantinfo add column show_popups enum('y', 'n') default 'n' after show_reviews;

-- make google review default priority where no priority is set
update bf_merchantinfo set popup_priority='google' where popup_priority is null or popup_priority = '';

-- default value of popup_priority
alter table bf_merchantinfo modify popup_priority text default 'google';

-- add is_shown column to bf_points table
alter table bf_points add column is_shown enum('n','y') default 'n' after lcoupon_id;
update bf_points set is_shown='y' where is_shown='n';

-- add message_type and message column to bf_loyalty
alter table bf_loyalty add column message_type enum('d','c') default 'd' after otp_verification, add column message longtext default null after message_type;

-- add table bf_dash_summary for faster working of corp dashboard
CREATE TABLE `bf_dash_summary` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `bill_date` date NOT NULL,
  `total_bills` int(11) NOT NULL,
  `bills_w_mob` int(11) NOT NULL,
  `total_amt` varchar(50) NOT NULL,
  `total_fb_1` int(11) NOT NULL,
  `total_fb_2` int(11) NOT NULL,
  `total_fb_3` int(11) NOT NULL,
  `total_fb_4` int(11) NOT NULL,
  `total_fb_5` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- rename stock_id column to stock_in_id to bf_items_tagging
alter table bf_items_tagging change stock_id stock_in_id int(11) not null;

-- add stock_out_id column to bf_items_tagging
alter table bf_items_tagging add column stock_out_id int(11) default null after stock_in_id;

-- add ean_no to bf_items_master table
alter table bf_items_master add column ean_no varchar(255) default null after hsn;

-- add merchant_id to bf_items_tagging table
alter table bf_items_tagging add column merchant_id int(11) not null after stock_out_id;

-- bf_items_tag_names table
CREATE TABLE `bf_items_tag_names` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `tag_name` VARCHAR(255) NOT NULL,
  `show_on_inv_head` enum('y','n') DEFAULT 'n',
  `status` enum('a','i') DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add gen_bill_extra_fields column in bf_users table
alter table bf_users add column gen_bill_extra_fields text default null after gen_bill_access;

-- add stock_tag column in bf_vouchers_particulars
alter table bf_vouchers_particulars add column stock_tag varchar(1024) default null after particulars;

-- add inv_phone, inv_email column to bf_merchantinfo for faster working of corp dashboard
alter table bf_merchantinfo add column inv_phone varchar(255) default null after display_email, add column inv_email varchar(255) default null after inv_phone;
-- add whitelabel table

CREATE TABLE `bf_whitelabel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `header_bg_color` varchar(50) DEFAULT NULL,
  `logo_size` int(11) DEFAULT NULL,
  `logo_position` enum('l','r','c') DEFAULT NULL,
  `footer_copy_text` varchar(255) DEFAULT NULL,
  `website_url` varchar(255) DEFAULT NULL,
  `fb` varchar(255) DEFAULT NULL,
  `insta` varchar(255) DEFAULT NULL,
  `twitter` varchar(255) DEFAULT NULL,
  `yt` varchar(255) DEFAULT NULL,
  `pintrest` varchar(255) DEFAULT NULL,
  `linkedin` varchar(255) DEFAULT NULL,
  `whatsapp` varchar(255) DEFAULT NULL,
  `show_powered_by` enum('y','n') NOT NULL DEFAULT 'y',
  `body_bg_color` varchar(50) DEFAULT NULL,
  `link_color` varchar(50) DEFAULT NULL,
  `shop_online_url` varchar(255) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add fb_id column to whitelabel table
alter table bf_whitelabel add column fb_id varchar(255) default null after fb;

-- positioning logo default left of whitelabel table
alter table bf_whitelabel modify column logo_position  enum('l','r','c') DEFAULT 'l';

-- add merchant_id column to bf_stats table
alter table bf_stats add column merchant_id int(11) default null after user_identifier;

-- add cust_profile and banner_as_login column to vendor table
alter table bf_merchantinfo add column cust_profile enum('y','n') default null after show_popups;
alter table bf_merchantinfo add column banner_as_login enum('y','n') default null after cust_profile;
alter table bf_merchantinfo drop column profile_popup;

-- customer profile table
CREATE TABLE `bf_customer_profile` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `gender` enum('m','f','o') DEFAULT NULL,
  `dob` datetime NOT NULL,
  `marital_status` varchar(10) DEFAULT NULL,
  `has_child` varchar(10) DEFAULT NULL,
  `doa` datetime DEFAULT NULL,
  `parent_id` varchar(255) NOT NULL DEFAULT '0',
  `type` enum('s','c') DEFAULT NULL,
  `p_complete` enum('y','n') DEFAULT 'n',
  `c_id` int(11) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

alter table bf_customer_profile modify dob datetime default null;

-- bf_lp_offers table
CREATE TABLE `bf_store_offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `desc` text NOT NULL,
  `offer_day` enum('s', 'm','tu','w','t','f','sa') DEFAULT NULL,
  `start_date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `end_date` datetime default NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- bf_auto_config table
CREATE TABLE `bf_auto_config` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `feature_access` varchar(255) NOT NULL,
  `priority` varchar(255) NOT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add mobile_num_masking table
CREATE TABLE `bf_mask_num` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) NOT NULL,
  `mask_num` varchar(50) DEFAULT NULL,
  `status` enum('a','i') NOT NULL DEFAULT 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- add domain_name column to bf_merchantinfo table
alter table bf_merchantinfo add column domain_name varchar(1024) default null after show_popups;

-- drop banner_as_login and add store_eng column to bf_merchantinfo table
alter table bf_merchantinfo drop banner_as_login;
alter table bf_merchantinfo add column store_eng enum('y','n') default null after show_popups;

-- add se_features column to bf_merchantinfo tabel
alter table bf_merchantinfo add column se_features varchar(2048) default null after store_eng;

-- add merchant_id column to bf_store_offers
alter table bf_store_offers add column merchant_id int(11) not null after id;

-- remove se_features column from bf_merchantinfo table
alter table bf_merchantinfo drop column se_features;

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

-- add bills_printed to bf_dash_summary table; Added by Kushagra on 29 Nov 21
ALTER TABLE bf_dash_summary ADD COLUMN bills_printed int(11) DEFAULT NULL AFTER bills_w_mob;

-- add profile_form_type column to bf_merchantinfo
alter table bf_merchantinfo add column profile_form_type varchar(255) default null after cust_profile;

-- add google_review_link column to bf_merchantinfo table
alter table bf_merchantinfo add column google_review_link varchar(255) default null after google_place_id;

-- active status default active for bf_daily_report_queue
alter table bf_daily_report_queue modify status enum('a','i') default 'a' after frequency;

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

-- eng_qr_title,eng_qr_subtitle column to bf_merchantinfo table;
alter table bf_merchantinfo add column eng_qr_title varchar(1024) default null after google_redirect, add column eng_qr_subtitle varchar(1024) default null after eng_qr_title;

-- add date_added, date_updated columns to bf_customer_profile
alter table bf_customer_profile add column date_added datetime default CURRENT_TIMESTAMP after c_id, add column date_updated datetime default null after date_added;

-- add custom_domain column to bf_merchantinfo table
alter table bf_merchantinfo add column custom_domain varchar(255) default null after custom_sms_text;

-- voucher bulk upload queue table
CREATE TABLE `bf_voucher_upload_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `merchant_id` int(11) not null,
  `party_merchant_id` int(11) default null,
  `party_name` varchar(255) default null,
  `party_mobile` varchar(255) default null,
  `party_gst` varchar(255) default null,
  `party_addr` varchar(255) default null,
  `party_state` int(11) default null,
  `party_email` varchar(255) default null,
  `dyn_fields` longtext default null,
  `ewb_no` varchar(50) default null,
  `ewb_mode` varchar(255) default null,
  `ewb_veh_no` varchar(50) default null,
  `ewb_supply_date` datetime default null,
  `ewb_supply_place` varchar(255) default null,
  `vou_prefix` varchar(50) default null,
  `vou_no` int(11) default null,
  `vou_date` datetime default CURRENT_TIMESTAMP,
  `vou_type` varchar(50) default null,
  `narration` text default null,
  `narration_flag` enum('y', 'n') default null,
  `text` longtext default null,
  `paper_size` varchar(2048) default null,
  `send_bf` enum('y', 'n') default 'y',
  `date_added` datetime default CURRENT_TIMESTAMP,
  `payment_date` date default CURRENT_TIMESTAMP,
  `validity_date` date default CURRENT_TIMESTAMP,
  `remarks` longtext default null,
  `loyalty_points_redeemed` int(11) default 0,
  `status` enum('a', 'i') default 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- vouchers particulars bulk upload queue table
CREATE TABLE `bf_vouchers_particulars_upload_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vou_id` int(11) not null,
  `particulars` longtext default null,
  `stock_tag` varchar(1024) default null,
  `hsn` varchar(255) default null,
  `gst` varchar(255) default 0,
  `rate` varchar(255) default 0,
  `qty` varchar(255) default 0,
  `dis_type` enum('percent', 'rupee') default 'percent',
  `dis_value` varchar(255) default null,
  `total` varchar(255) default null,
  `status` enum('a', 'i') default 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- vouchers payments bulk upload queue table
CREATE TABLE `bf_vouchers_payments_upload_queue` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vou_id` int(11) default null,
  `mode` varchar(50) default null,
  `amount` varchar(255) default null,
  `amt_words` varchar(255) default null,
  `chq_no` varchar(255) default null,
  `chq_bank` varchar(255) default null,
  `wire_ref_no` varchar(255) default null,
  `cred_amt_paid` varchar(255) default null,
  `cred_amt_outstanding` varchar(255) default null,
  `fin_company_name` varchar(255) default null,
  `fin_loan_no` varchar(255) default null,
  `fin_amt` varchar(255) default null,
  `fin_paid_amt` varchar(255) default null,
  `paid_date` datetime default CURRENT_TIMESTAMP,
  `status` enum('a', 'i') default 'a',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- making purchase history tab show default
ALTER table `bf_merchantinfo` drop column show_purchase_history;
ALTER TABLE `bf_merchantinfo` add column hide_purchase_history enum('y', 'n') default 'n' after remarks;

-- checkbox_options system for feedback
alter table `bf_alt_feedback` add column checkbox_options varchar(2048) default null after high_rating_title;
alter table `bf_bills_feedback` add column checkbox_options varchar(2048) default null after rating;

-- add wa_credit_balance column in bf_merchantinfo; Entry by Amarjeet 280222
alter table `bf_merchantinfo` add column wa_credit_balance int(11) default null after sms_credit_balance;

-- Add channel_type column in bf_merchant_offers; Entry by Amarjeet 010322
ALTER TABLE `bf_merchant_offers` ADD column channel_type enum('s','w') default 's' after description;

-- change sms_text column default null in bf_merchant_offers; Entry by Amarjeet 030322
ALTER TABLE `bf_merchant_offers` MODIFY sms_text text DEFAULT NULL;