ALTER TABLE  `sps_settlement` CHANGE  `settlement_amount`  `settlement_amount` FLOAT( 10, 2 ) NOT NULL DEFAULT  '0',
CHANGE  `foc_total_accepted`  `foc_total_accepted` FLOAT( 10, 2 ) NOT NULL DEFAULT  '0',
CHANGE  `foc_settlement_amount`  `foc_settlement_amount` FLOAT( 10, 2 ) NOT NULL DEFAULT  '0',
CHANGE  `amount_recovered`  `amount_recovered` FLOAT( 10, 2 ) NOT NULL DEFAULT  '0',
CHANGE  `foc_amount_recovered`  `foc_amount_recovered` FLOAT( 10, 2 ) NOT NULL DEFAULT  '0';
ALTER TABLE `sps_settlement` ADD `banking_amt` FLOAT(10,2) NOT NULL AFTER `f_eight_ticket_charges`;

/**************New queries 18th Oct 2019***************/
ALTER TABLE `sps_membership` ADD `balance_updated_at` TIMESTAMP NULL DEFAULT NULL AFTER `balance`;
ALTER TABLE `sps_transaction_additional_charges` ADD `cps_id` INT NULL DEFAULT '0' AFTER `transaction_id`;
ALTER TABLE `sps_transaction_discount` ADD `cps_id` INT NULL DEFAULT '0' AFTER `transaction_id`;

--
-- Table structure for table `sps_membership_bal_noti`
--

CREATE TABLE IF NOT EXISTS `sps_membership_bal_noti` (
  `id` int(11) NOT NULL,
  `transaction_id` int(11) DEFAULT NULL,
  `cps_id` int(11) DEFAULT NULL,
  `card_no` bigint(11) DEFAULT NULL,
  `transaction_at` enum('ENTRY','CPS','EXIT','') DEFAULT NULL COMMENT 'ENTRY,CPS,EXIT',
  `is_notify` tinyint(1) DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `sps_membership_bal_noti`
--
ALTER TABLE `sps_membership_bal_noti`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sps_membership_bal_noti`
--
ALTER TABLE `sps_membership_bal_noti`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

ALTER TABLE `sps_tariff_member` ADD `benefit_type` TINYINT(2) NOT NULL AFTER `administration_discount`, ADD `benefit_value` FLOAT(10,2) NOT NULL AFTER `benefit_type`;
ALTER TABLE `sps_membership_log` ADD `benefit_type` TINYINT(2) NOT NULL AFTER `is_card_used`, ADD `benefit_value` FLOAT(10,2) NOT NULL DEFAULT '0.00' AFTER `benefit_type`;


--
-- Table structure for table `sps_license`
--

CREATE TABLE `sps_license` (
  `id` bigint(20) NOT NULL,
  `license_key` varchar(513) DEFAULT NULL,
  `expire_date` timestamp NULL DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1=active,0=inactive',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/**********For Maria DB if above query won't work*************

CREATE TABLE IF NOT EXISTS `sps_license` (
  `id` bigint(20) NOT NULL,
  `license_key` varchar(513) DEFAULT NULL,
  `expire_date` timestamp NULL DEFAULT NULL,
  `status` tinyint(4) NOT NULL DEFAULT '1' COMMENT '1=active,0=inactive',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

********************************* END For Maria DB if above query won't work ***********/

--
-- Indexes for dumped tables
--

--
-- Indexes for table `sps_license`
--
ALTER TABLE `sps_license`
ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `sps_license`
--
ALTER TABLE `sps_license`
MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;
COMMIT;


ALTER TABLE `sps_reports` ADD `status_progress` VARCHAR(20) NOT NULL AFTER `status`;
ALTER TABLE `sps_reports` ADD `requested_by` INT(10) NOT NULL AFTER `status_progress`;
ALTER TABLE `sps_reports` CHANGE `status_progress` `status_progress` VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;
ALTER TABLE `sps_reports` CHANGE `requested_by` `requested_by` INT(10) NULL DEFAULT NULL;
UPDATE sps_reports SET status_progress = 'Completed'; 

/**Eod Organize Configure **/

ALTER TABLE `sps_eod_report_select` ADD `eod_reportvise_id` INT NOT NULL AFTER `location_id`;

UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '1' WHERE `sps_eod_report_select`.`eod_report_id` = 1;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '2' WHERE `sps_eod_report_select`.`eod_report_id` = 3;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '3' WHERE `sps_eod_report_select`.`eod_report_id` = 17;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '4' WHERE `sps_eod_report_select`.`eod_report_id` = 4;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '5' WHERE `sps_eod_report_select`.`eod_report_id` = 20;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '6' WHERE `sps_eod_report_select`.`eod_report_id` = 18;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '7' WHERE `sps_eod_report_select`.`eod_report_id` = 12;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '8' WHERE `sps_eod_report_select`.`eod_report_id` = 6;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '9' WHERE `sps_eod_report_select`.`eod_report_id` = 9;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '10' WHERE `sps_eod_report_select`.`eod_report_id` = 8;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '11' WHERE `sps_eod_report_select`.`eod_report_id` = 7;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '12' WHERE `sps_eod_report_select`.`eod_report_id` = 13;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '13' WHERE `sps_eod_report_select`.`eod_report_id` = 5;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '14' WHERE `sps_eod_report_select`.`eod_report_id` = 10;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '15' WHERE `sps_eod_report_select`.`eod_report_id` = 15;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '16' WHERE `sps_eod_report_select`.`eod_report_id` = 14;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '17' WHERE `sps_eod_report_select`.`eod_report_id` = 19;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '18' WHERE `sps_eod_report_select`.`eod_report_id` = 16;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '19' WHERE `sps_eod_report_select`.`eod_report_id` = 11;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '20' WHERE `sps_eod_report_select`.`eod_report_id` = 2;

UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '21' WHERE `sps_eod_report_select`.`eod_report_id` = 21;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '22' WHERE `sps_eod_report_select`.`eod_report_id` = 22;
UPDATE `sps_eod_report_select` SET `eod_reportvise_id` = '23' WHERE `sps_eod_report_select`.`eod_report_id` = 23;
/**Eod Organize Configure **/


CREATE TABLE `admin_password_reset_log` (
  `password_reset_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `admin_password_reset_log`
ADD PRIMARY KEY (`password_reset_id`);

ALTER TABLE `admin_password_reset_log`
MODIFY `password_reset_id` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;

/** Execute following url to generate licence key and replace generated key & date on following query and .env file

http://server-ip/spstest/public/genrate-licence/date/2020-12-31

*/


INSERT INTO `sps_license` (`id`, `license_key`, `expire_date`, `status`, `created_at`) VALUES(1, 'dlM0p+Q67i4ii1EIGiqcESj/VUo/VkBqjSBuzZfSX9oXTHrCG4EwrbP0Hk+eEfmKxOlHxrDQGpoohO1m3LtAM3BJHZ1WrG0MBOiMSbz4JhA=', '2019-12-31 18:30:00', 1, '2019-11-05 13:58:23');

#.env
#LICENSE=dlM0p+Q67i4ii1EIGiqcESj/VUo/VkBqjSBuzZfSX9oXTHrCG4EwrbP0Hk+eEfmKxOlHxrDQGpoohO1m3LtAM3BJHZ1WrG0MBOiMSbz4JhA=


/**************End New queries 18th Oct 2019***************/
