-- (1) SESSION LOGIN POPUP

-- Update Permission
-- https://demo.microfinancesoftware.in/backend/api/update-permission

-- SOFTWARE SUSPEND
-- https://demo.microfinancesoftware.in/backend/api/suspend/inactive
-- https://demo.microfinancesoftware.in/backend/api/suspend/active

-- SOFTWARE DISABLE TO LOGIN
-- https://demo.microfinancesoftware.in/backend/api/software/inactive
-- https://demo.microfinancesoftware.in/backend/api/software/active

-- DISABLE ADMIN LOGIN
-- https://demo.microfinancesoftware.in/backend/api/admin/inactive
-- https://demo.microfinancesoftware.in/backend/api/admin/active

-- Check Duplicate disbursement
-- SELECT id, disbursement_id, COUNT(disbursement_id) FROM `member_loan_plans` GROUP BY disbursement_id HAVING COUNT(disbursement_id) > 1

-- Check Duplicate Collection
-- select s.id, s.created_at, s.disbursement_id, t.* ,s.member_id , k.applicationNo, p.name, p.code from member_collections as s join ( select member_loan_plan_id, paymentDate, amt, count(*) as qty from member_collections group by member_loan_plan_id, paymentDate, amt having count(*) > 1 ) as t on s.member_loan_plan_id = t.member_loan_plan_id and s.paymentDate = t.paymentDate and s.amt = t.amt left join member_loan_plans as k on s.member_loan_plan_id = k.id left join users as p on s.member_id = p.id;

-- Financial Year Voucher Statement
-- https://demo.microfinancesoftware.in/backend/api/financialyearvoucherstatementServer?branch_id=1&branch_ledger_id=3

-- Manual Cron
-- https://demo.microfinancesoftware.in/backend/api/manualcron/daily    or      ?date=2024-06-29
-- https://demo.microfinancesoftware.in/backend/api/manualcron/interest

-- Update Completed or Sattlement Dtae
-- https://demo.microfinancesoftware.in/backend/api/update/completesattlementdate


-- Find MemberLoanPlan records that does not have generated MemberEMI
-- SELECT ml.* FROM member_loan_plans ml WHERE NOT EXISTS ( SELECT 1 FROM member_emis me WHERE ml.id = me.member_loan_plan_id );



-- TRUNCATE `loan_disbursements`;
-- TRUNCATE `loan_histories`;
-- TRUNCATE `member_attendances`;
-- TRUNCATE `member_collections`;
-- TRUNCATE `member_emis`;
-- TRUNCATE `member_loan_plans`;
-- TRUNCATE `vouchers`;
-- TRUNCATE `voucher_details`;


-- ALTER TABLE `member_emis`  ADD `emiNo` INT NULL  AFTER `id`;
-- ALTER TABLE `member_loan_plans`  ADD `emiStartDate` VARCHAR(255) NULL DEFAULT NULL  AFTER `branch_id`,  ADD `emiEndDate` VARCHAR(255) NULL DEFAULT NULL  AFTER `emiStartDate`;
-- ALTER TABLE `member_loan_plans` ADD `lastUpdateEmiNo` VARCHAR(255) NULL AFTER `member_id`;
-- ALTER TABLE `loan_disbursements`  ADD `emiStartDate` VARCHAR(255) NULL  AFTER `disbursementDate`;
-- ALTER TABLE `loan_disbursements` ADD `emiEndDate` VARCHAR(255) NULL AFTER `emiStartDate`;
-- ALTER TABLE `loan_disbursements` ADD `loanApplicationNo` VARCHAR(255) NULL AFTER `id`;
-- ALTER TABLE `loan_disbursements` ADD `loanAmount` INT NULL AFTER `reason_id`;
-- ALTER TABLE `loan_plans` CHANGE `rateOfInterest` `rateOfInterest` DOUBLE(4,2) UNSIGNED NOT NULL;
-- ALTER TABLE `loan_plans` CHANGE `processingFee` `processingFee` DOUBLE UNSIGNED NULL DEFAULT NULL;
-- ALTER TABLE `settings` ADD `enableMandatoryCustomerKyc` VARCHAR(255) NULL AFTER `decimalPlacesForAmtInWords`;    -- 27-05-2021
-- ALTER TABLE `settings` ADD `enableSMS` VARCHAR(255) NULL DEFAULT 'true' AFTER `enableMandatoryCustomerKyc`; --11-6-2021
-- 05-7-2021
-- ALTER TABLE `settings` ADD `emiReminderSendSMSDayBefore` VARCHAR(255) NULL DEFAULT '2' AFTER `enableSMS`;
-- ALTER TABLE `settings` ADD `createCustomerSMS` VARCHAR(255) NULL AFTER `emiReminderSendSMSDayBefore`, ADD `loanDisbursementSMS` VARCHAR(255) NULL AFTER `createCustomerSMS`, ADD `emiCollectionSMS` VARCHAR(255) NULL AFTER `loanDisbursementSMS`, ADD `passwordResetSMS` VARCHAR(255) NULL AFTER `emiCollectionSMS`, ADD `croneDueReminderSMS` VARCHAR(255) NULL AFTER `passwordResetSMS`, ADD `documentVerificationSMS` VARCHAR(255) NULL AFTER `croneDueReminderSMS`, ADD `customerCredentionSMS` VARCHAR(255) NULL AFTER `documentVerificationSMS`;
-- ALTER TABLE `settings` ADD `memberVerifyOTPSMS` VARCHAR(255) NULL AFTER `customerCredentionSMS`, ADD `guarantorVerifyOTPSMS` VARCHAR(255) NULL AFTER `memberVerifyOTPSMS`, ADD `staffCredentionSMS` VARCHAR(255) NULL AFTER `guarantorVerifyOTPSMS`, ADD `staffVerifyOTPSMS` VARCHAR(255) NULL AFTER `staffCredentionSMS`;



-- (end) update javascript and version number
-- ALTER TABLE `settings` ADD `settings_version` VARCHAR(255) NULL AFTER `decimalPlacesForAmtInWords`, ADD `settings_system_javascript_versioning` VARCHAR(255) NULL AFTER `settings_version`;

-- 10/12/2021
 ALTER TABLE `sattlements` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;
 ALTER TABLE `loan_disbursements` ADD `processingFee` INT UNSIGNED NULL DEFAULT NULL AFTER `loanAmount`, ADD `fileCharge` INT UNSIGNED NULL DEFAULT NULL AFTER `processingFee`;
 ALTER TABLE `loan_disbursements` ADD `guarantor_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `fileCharge`, ADD `checkNo` VARCHAR(255) NULL DEFAULT NULL AFTER `guarantor_id`, ADD `bank_id` BIGINT UNSIGNED NULL DEFAULT NULL AFTER `checkNo`, ADD `name` VARCHAR(255) NULL DEFAULT NULL AFTER `bank_id`, ADD `checkDate` VARCHAR(255) NULL DEFAULT NULL AFTER `name`;
 ALTER TABLE `loan_plan_details` CHANGE `emi` `emi` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `noOfPayments` `noOfPayments` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL;
 ALTER TABLE `loan_plan_details` ADD `principal` INT UNSIGNED NULL AFTER `noOfPayments`, ADD `intrest` INT UNSIGNED NULL AFTER `principal`;

-- 31-03-2022
ALTER TABLE `member_collections` ADD `transaction_id` INT NULL AFTER `branch_ledger_id`, ADD `paymentMode` VARCHAR(255) NULL AFTER `transaction_id`;

-- 24-05-2022
ALTER TABLE `loan_plans` ADD `insuranceFee` INT NULL AFTER `fileChargeIncludeAmt`, ADD `insuranceFeeType` VARCHAR(255) NULL AFTER `insuranceFee`, ADD `insuranceFeeIncludeAmt` VARCHAR(255) NULL AFTER `insuranceFeeType`;
ALTER TABLE `loan_plans` ADD `totalAmtToPay` INT NULL AFTER `holidayInclude`;
ALTER TABLE `loan_disbursements` ADD `insuranceFee` VARCHAR(255) NULL AFTER `processingFeeStatus`, ADD `insuranceFeeStatus` VARCHAR(255) NULL AFTER `insuranceFee`;
ALTER TABLE `loan_disbursements` ADD `purpose_id` VARCHAR(255) NULL AFTER `reason_id`;
UPDATE `ledgers` SET `name` = 'Insurance Fee', `created_at` = NULL, `updated_at` = NULL WHERE `ledgers`.`id` = 13;
UPDATE `loan_plans` SET `insuranceFee`=0, `insuranceFeeType`='fixed', `insuranceFeeIncludeAmt`='false' where `insuranceFee` is null;

-- 21-06-2022
ALTER TABLE `loan_disbursements` ADD `description` VARCHAR(255) NULL AFTER `insuranceFeeStatus`;
ALTER TABLE `death_members` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;
UPDATE `users` SET `status` = 'active' WHERE `users`.`id` = 1;


UPDATE `purposes` SET `purpose` = 'Agriculture' WHERE `purposes`.`id` = 2;
UPDATE `purposes` SET `purpose` = 'Air Conditioner' WHERE `purposes`.`id` = 4;

UPDATE `relations` SET `relation` = 'Daughter' WHERE `relations`.`id` = 4;
UPDATE `relations` SET `relation` = 'Grand Daughter' WHERE `relations`.`id` = 12;
UPDATE `relations` SET `relation` = 'Cousin Brother' WHERE `relations`.`id` = 19;
UPDATE `relations` SET `relation` = 'Cousin Sister' WHERE `relations`.`id` = 20;

UPDATE `identity_proofs` SET `identity` = 'Cheque' WHERE `identity_proofs`.`id` = 10;
UPDATE `identity_proofs` SET `identity` = 'College ID' WHERE `identity_proofs`.`id` = 8;

UPDATE `reasons` SET `reason` = 'Driving Licence' WHERE `reasons`.`id` = 8;
UPDATE `reasons` SET `reason` = 'Guarantor Pan' WHERE `reasons`.`id` = 12;
UPDATE `reasons` SET `reason` = 'Guarantor Aadhar' WHERE `reasons`.`id` = 13;
UPDATE `reasons` SET `reason` = 'Guarantor Phone' WHERE `reasons`.`id` = 14;
UPDATE `reasons` SET `reason` = 'Guarantor Address' WHERE `reasons`.`id` = 15;

-- 22-07-2022
ALTER TABLE `employee_transfers` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;
ALTER TABLE `members` ADD `aadharBackPhoto` VARCHAR(255) NULL AFTER `aadharPhoto`;
ALTER TABLE `members` ADD `guarantorAadharBackPhoto` VARCHAR(255) NULL AFTER `guarantorAadharPhoto`;
ALTER TABLE `members` ADD `voterCardPhoto` VARCHAR(255) NULL AFTER `rationCardStatus`, ADD `voterCardStatus` VARCHAR(255) NULL AFTER `voterCardPhoto`;
ALTER TABLE `staff` ADD `aadharBack` VARCHAR(255) NULL AFTER `aadhar`;

ALTER TABLE `settings` ADD `sattlementSMS` VARCHAR(255) NULL AFTER `staffVerifyOTPSMS`;

-- 30-07-2022
ALTER TABLE `settings` ADD `autoApproveMemberCollection` VARCHAR(255) NULL DEFAULT 'false' AFTER `sattlementSMS`;
ALTER TABLE `settings` ADD `showAllMembers` VARCHAR(255) NULL DEFAULT 'false' AFTER `sattlementSMS`;

-- 06-09-2022 For Interest Plan
ALTER TABLE `loan_disbursements` ADD `planCategory` VARCHAR(255) NULL DEFAULT NULL COMMENT '1-normal | 2-normalReduced | 3-special | 4-custom | 5-manual | 6-interest' AFTER `planType`;
ALTER TABLE `member_loan_plans` ADD `receivedPrincipal` INT NULL AFTER `receivedAmt`;
ALTER TABLE `member_collections` ADD `collectionType` VARCHAR(255) NULL DEFAULT 'collection' AFTER `amt`;
ALTER TABLE `loan_plans` CHANGE `rateOfInterest` `rateOfInterest` DOUBLE(5,2) NOT NULL;

-- 08-10-2022 For Session
ALTER TABLE `settings` ADD `currentSession` INT NULL AFTER `website`;

ALTER TABLE `financial_sessions` CHANGE `status` `status` VARCHAR(255) NULL DEFAULT NULL;
INSERT INTO `financial_sessions` (`id`, `session`, `startDate`, `endDate`, `startYear`, `endYear`, `status`, `remark`, `ipAddress`, `created_at`, `updated_at`) VALUES (NULL, '2022-2023', '2022-04-01', '2023-03-31', '2022', '2023', 'active', NULL, '127.0.0.1', NULL, NULL);
UPDATE `settings` SET `currentSession` = 1 Where `id` = 1;

ALTER TABLE `settings` ADD `lastDailyCronDate` VARCHAR(255) NULL AFTER `autoApproveMemberCollection`, ADD `lastInterestOnlyCronDate` VARCHAR(255) NULL AFTER `lastDailyCronDate`;

 UPDATE `settings` SET `settings_version` = '2.01' Where `id` = 1;
 UPDATE `settings` SET `settings_system_javascript_versioning` = now() Where `id` = 1;




-- -- version 2.03 Start ===================================================== 17-10-2022 (total 109) ==============================================




-- Penulty
UPDATE `ledgers` SET `name` = 'Penalty Charges' WHERE `ledgers`.`id` = 14;
UPDATE `groups` SET `group` = 'Unknown' WHERE `groups`.`id` = 25;
UPDATE `ledgers` SET `group_id` = '10' WHERE `ledgers`.`id` = 9;
UPDATE `branch_ledgers` SET `group_id` = '10' WHERE `ledger_id` = 9;
ALTER TABLE `member_loan_plans` ADD `receivedPanulty` INT NULL DEFAULT '0' AFTER `receivedPrincipal`;

-- Dashboard Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'dashboard-total-collection', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-total-disbursement', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-total-outstanding', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-cash', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-bank', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-interest', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-approved', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-approved-daily', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-approved-weekly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-approved-monthly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-approved-total', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-pending', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-pending-daily', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-pending-weekly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-pending-monthly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-collection-pending-total', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-approved', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-approved-daily', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-approved-weekly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-approved-monthly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-approved-total', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-pending-rows', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-pending-rows-daily', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-pending-rows-weekly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-pending-rows-monthly', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-disbursement-pending-rows-total', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-total-plan-disbursement', 'dashboard', 'dashboard permission', 'true', NULL, NULL),
        (NULL, 'dashboard-total-dynamic-disbursement', 'dashboard', 'dashboard permission', 'true', NULL, NULL);

-- AreaSurvey Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'areasurvey-view-any', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL),
        (NULL, 'areasurvey-view', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL),
        (NULL, 'areasurvey-create', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL),
        (NULL, 'areasurvey-update', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL),
        (NULL, 'areasurvey-update', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL),
        (NULL, 'areasurvey-approve', 'areasurvey', 'View Any AreaSurvey permission', 'true', NULL, NULL);

-- Member VoterCard Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'member-voter-card', 'member', 'View Any member permission', 'true', NULL, NULL);

-- Staff Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'staff-pan', 'staff', 'View Any staff permission', 'true', NULL, NULL),
        (NULL, 'staff-aadhar', 'staff', 'View Any staff permission', 'true', NULL, NULL);

-- Menu Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'menu-loan-plan', 'menu', 'menu-loan-plan permission', 'true', NULL, NULL),
        (NULL, 'menu-collection-center', 'menu', 'menu-collection-center permission', 'true', NULL, NULL), 
        (NULL, 'menu-state', 'menu', 'menu-state permission', 'true', NULL, NULL),

        (NULL, 'menu-bank', 'menu', 'menu-bank permission', 'true', NULL, NULL),
        (NULL, 'menu-leave', 'menu', 'menu-leave permission', 'true', NULL, NULL),
        (NULL, 'menu-branch', 'menu', 'menu-branch permission', 'true', NULL, NULL),
        (NULL, 'menu-series', 'menu', 'menu-series permission', 'true', NULL, NULL),
        (NULL, 'menu-designation', 'menu', 'menu-designation permission', 'true', NULL, NULL),
        (NULL, 'menu-miscellneous', 'menu', 'menu-miscellneous permission', 'true', NULL, NULL),
        (NULL, 'menu-software-timing', 'menu', 'menu-software-timing permission', 'true', NULL, NULL),
        (NULL, 'menu-setting', 'menu', 'menu-setting permission', 'true', NULL, NULL),
        (NULL, 'menu-session', 'menu', 'menu-session permission', 'true', NULL, NULL),

        -- HRM
        (NULL, 'menu-hrm', 'menu', 'menu-hrm permission', 'true', NULL, NULL),
        (NULL, 'menu-employee', 'menu', 'menu-employee permission', 'true', NULL, NULL),
        (NULL, 'menu-salary', 'menu', 'menu-salary permission', 'true', NULL, NULL),
        (NULL, 'menu-holiday', 'menu', 'menu-holiday permission', 'true', NULL, NULL),
        (NULL, 'menu-employee-transfer', 'menu', 'menu-employee-transfer permission', 'true', NULL, NULL),
        (NULL, 'menu-member-transfer', 'menu', 'menu-member-transfer permission', 'true', NULL, NULL),
        (NULL, 'menu-update-center-leader', 'menu', 'menu-update-center-leader permission', 'true', NULL, NULL),
        (NULL, 'menu-print-letter', 'menu', 'menu-print-letter permission', 'true', NULL, NULL),
        (NULL, 'menu-employee-leave', 'menu', 'menu-employee-leave permission', 'true', NULL, NULL),
        (NULL, 'menu-attendance', 'menu', 'menu-attendance permission', 'true', NULL, NULL),

        -- MEMBER
        (NULL, 'menu-member', 'menu', 'menu-member permission', 'true', NULL, NULL),
        (NULL, 'menu-member-group', 'menu', 'menu-member-group permission', 'true', NULL, NULL),

        -- Apply For Loan
        (NULL, 'menu-apply-loan', 'menu', 'menu-apply-loan permission', 'true', NULL, NULL),
        (NULL, 'menu-download-loan-form', 'menu', 'menu-download-loan-form permission', 'true', NULL, NULL),
        (NULL, 'menu-individual-loan', 'menu', 'menu-individual-loan permission', 'true', NULL, NULL),
        (NULL, 'menu-group-loan', 'menu', 'menu-group-loan permission', 'true', NULL, NULL),

        -- Disbursement
        (NULL, 'menu-disbursement', 'menu', 'menu-disbursement permission', 'true', NULL, NULL),
        (NULL, 'menu-group-disbursement', 'menu', 'menu-group-disbursement permission', 'true', NULL, NULL),
        (NULL, 'menu-loan-summery', 'menu', 'menu-loan-summery permission', 'true', NULL, NULL),

        -- EMI Records
        (NULL, 'menu-emi', 'menu', 'menu-emi permission', 'true', NULL, NULL),
        (NULL, 'menu-paid-emi', 'menu', 'menu-paid-emi permission', 'true', NULL, NULL),
        (NULL, 'menu-due-emi', 'menu', 'menu-due-emi permission', 'true', NULL, NULL),
        (NULL, 'menu-promise-to-pay', 'menu', 'menu-promise-to-pay permission', 'true', NULL, NULL),
        (NULL, 'menu-member-collection', 'menu', 'menu-member-collection permission', 'true', NULL, NULL),
        (NULL, 'menu-group-emi-collection', 'menu', 'menu-group-emi-collection permission', 'true', NULL, NULL),

        -- Approvals
        (NULL, 'menu-approval', 'menu', 'menu-approval permission', 'true', NULL, NULL),
        (NULL, 'menu-salary-approve', 'menu', 'menu-salary-approve permission', 'true', NULL, NULL),
        (NULL, 'menu-collection-approve', 'menu', 'menu-collection-approve permission', 'true', NULL, NULL),
        (NULL, 'menu-sattlement-approve', 'menu', 'menu-sattlement-approve permission', 'true', NULL, NULL),

        -- Sattlement
        (NULL, 'menu-sattlement', 'menu', 'menu-sattlement permission', 'true', NULL, NULL),
        (NULL, 'menu-death-member', 'menu', 'menu-death-member permission', 'true', NULL, NULL),
        (NULL, 'menu-cibil', 'menu', 'menu-cibil permission', 'true', NULL, NULL),

        -- Voucher
        (NULL, 'menu-voucher', 'menu', 'menu-voucher permission', 'true', NULL, NULL),
        (NULL, 'menu-day-book', 'menu', 'menu-day-book permission', 'true', NULL, NULL),
        (NULL, 'menu-voucher-entries', 'menu', 'menu-voucher-entries permission', 'true', NULL, NULL),
        (NULL, 'menu-payment', 'menu', 'menu-payment permission', 'true', NULL, NULL),
        (NULL, 'menu-receipt', 'menu', 'menu-receipt permission', 'true', NULL, NULL),
        (NULL, 'menu-contra', 'menu', 'menu-contra permission', 'true', NULL, NULL),
        (NULL, 'menu-journal', 'menu', 'menu-journal permission', 'true', NULL, NULL),
        (NULL, 'menu-trial-balance', 'menu', 'menu-trial-balance permission', 'true', NULL, NULL),
        (NULL, 'menu-fy-trial-balance', 'menu', 'menu-fy-trial-balance permission', 'true', NULL, NULL),
        (NULL, 'menu-fy-voucher-statement', 'menu', 'menu-fy-voucher-statement permission', 'true', NULL, NULL),
        (NULL, 'menu-fy-opening-balance', 'menu', 'menu-fy-opening-balance permission', 'true', NULL, NULL),
        (NULL, 'menu-groups', 'menu', 'menu-groups permission', 'true', NULL, NULL),
        (NULL, 'menu-ledger', 'menu', 'menu-ledger permission', 'true', NULL, NULL),
        (NULL, 'menu-branch-ledger', 'menu', 'menu-branch-ledger permission', 'true', NULL, NULL),

        -- Ledger
        (NULL, 'menu-ledger', 'menu', 'menu-ledger permission', 'true', NULL, NULL),
        (NULL, 'menu-due-emi-report', 'menu', 'menu-due-emi-report permission', 'true', NULL, NULL),
        (NULL, 'menu-collection-report', 'menu', 'menu-collection-report permission', 'true', NULL, NULL),
        (NULL, 'menu-disbursement-report', 'menu', 'menu-disbursement-report permission', 'true', NULL, NULL),
        (NULL, 'menu-member-report', 'menu', 'menu-member-report permission', 'true', NULL, NULL),
        (NULL, 'menu-online-payment', 'menu', 'menu-online-payment permission', 'true', NULL, NULL),

        (NULL, 'menu-role-permission', 'menu', 'menu-role-permission permission', 'true', NULL, NULL),
        (NULL, 'menu-role', 'menu', 'menu-role permission', 'true', NULL, NULL),

        (NULL, 'menu-staff-branches', 'menu', 'menu-staff-branches permission', 'true', NULL, NULL),
        (NULL, 'menu-branch-center', 'menu', 'menu-branch-center permission', 'true', NULL, NULL),
        (NULL, 'menu-profit-loss', 'menu', 'menu-profit-loss permission', 'true', NULL, NULL),
        (NULL, 'menu-portfolio', 'menu', 'menu-portfolio permission', 'true', NULL, NULL),
        (NULL, 'menu-area-survey', 'menu', 'menu-area-survey permission', 'true', NULL, NULL);


-- StaffBranch Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
	(NULL, 'staff-branch-view-any', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'staff-branch-view', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'staff-branch-create', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'staff-branch-update', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'staff-branch-delete', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'staff-branch-assign-multiple', 'staff-branch', 'View Any staff-branch permission', 'true', NULL, NULL);

-- BranchCenter Permissions
INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `created_at`, `updated_at`) VALUES 
        (NULL, 'branch-center-view-any', 'branch-center', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'branch-center-view', 'branch-center', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'branch-center-create', 'branch-center', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'branch-center-update', 'branch-center', 'View Any staff-branch permission', 'true', NULL, NULL),
        (NULL, 'branch-center-delete', 'branch-center', 'View Any staff-branch permission', 'true', NULL, NULL);



-- FinancialSession Permissions
-- INSERT INTO `financial_sessions` (`id`, `session`, `startDate`, `endDate`, `startYear`, `endYear`, `status`, `remark`, `ipAddress`, `created_at`, `updated_at`) VALUES 
--         (NULL, '2022-23', '2022-04-01', '2023-03-31', '2022', '2023', 'true', 'test', '127.0.0.1', NULL, NULL);



-- Branch Center
ALTER TABLE `series` ADD `branchCenterSeries` INT NOT NULL DEFAULT '0' AFTER `branchPrefix`, ADD `branchCenterPrefix` VARCHAR(255) NULL AFTER `branchCenterSeries`;
ALTER TABLE `loan_disbursements` ADD `branch_center_id` INT NULL AFTER `branch_id`;

-- Advance Security EMI
UPDATE `ledgers` SET `name` = 'Advance Security EMI' WHERE `ledgers`.`id` = 15;
UPDATE `ledgers` SET `group_id` = '6' WHERE `ledgers`.`id` = 15;

ALTER TABLE `vouchers` ADD `advance_emi_security_id` BIGINT NULL DEFAULT NULL AFTER `salary_id`;

-- Member Application Form
ALTER TABLE `series` ADD `memberApplicationFormNoSeries` INT NULL DEFAULT '0' AFTER `branchCenterPrefix`, ADD `memberApplicationFormNoIncludeYear` VARCHAR(255) NULL DEFAULT 'false' AFTER `memberApplicationFormNoSeries`, ADD `memberApplicationFormNoPrefix` VARCHAR(255) NULL DEFAULT 'MAPPFRM' AFTER `memberApplicationFormNoIncludeYear`;

-- Collection Center Code Auto Generate
ALTER TABLE `series` ADD `collectionCenterSeries` INT NULL DEFAULT '0' AFTER `memberApplicationFormNoPrefix`, ADD `collectionCenterIncludeYear` VARCHAR(255) NULL DEFAULT 'false' AFTER `collectionCenterSeries`, ADD `collectionCenterPrefix` VARCHAR(255) NULL AFTER `collectionCenterIncludeYear`;

-- UPI Details  Cibil Id
ALTER TABLE `settings` ADD `upiId` VARCHAR(255) NULL AFTER `lastInterestOnlyCronDate`, ADD `upiQrCode` VARCHAR(255) NULL AFTER `upiId`, ADD `cibilMemberCode` VARCHAR(255) NULL AFTER `upiQrCode`, ADD `cibilMemberShortName` VARCHAR(255) NULL AFTER `cibilMemberCode`;

-- Configuration Setting
INSERT INTO `configuration_settings` (`id`, `softwareCode`, `softwareStatus`, `smsApiUrl`, `smsApiUser`, `smsApiPassword`, `smsApiText`, `smsApiPhone`, `smsApiBalanceUrl`, `smsApiTemplateCompanyName`, `highmarkCibilExport`, `highmarkMemberCode`, `highmarkMemberShortName`, `branchLimit`, `enableGroupLoan`, `customerPanel`, `staffPanel`, `documentVerificationApi`, `SANDBOX_HOST`, `SANDBOX_CONSENT`, `SANDBOX_REASON`, `SANDBOX_API_KEY`, `SANDBOX_API_SECRET`, `SANDBOX_VERSION`, `SANDBOX_WALLET_BALANCE`, `paytmPaymentGateway`, `FRONTEND_URL`, `PAYTM_ENVIRONMENT`, `PAYTM_MERCHANT_ID`, `PAYTM_MERCHANT_KEY`, `PAYTM_MERCHANT_WEBSITE`, `PAYTM_CHANNEL`, `PAYTM_INDUSTRY_TYPE`, `adminBackDateEntry`, `staffBackDateEntry`, `created_at`, `updated_at`) VALUES 
        (NULL, 'MNM002', 'true', 'http://103.117.180.173/api/mt/SendSMS?senderid=FORIDO&channel=trans&DCS=0&flashsms=0&route=2', 'user=for123ido', 'password=123456', 'text', 'number', 'http://103.117.180.173/api/mt/GetBalance?', 'Demo Microfinance', 'false', 'memberCode', 'memberShortName', '1', 'false', 'false', 'true', 'false', 'https://api.sandbox.co.in', 'Y', 'KYC Purpose', 'key_live_0fvSFFZsJwvOH7RuUlEXDmMkeeeQuzAT', 'secret_live_3leFqqYu7lpqDtgE75uT0Qh32Gv1Dwaa', '1.0', '0', 'false', 'http://localhost:8080', 'local', 'oKLcdZ37016271046026', 'Oo9MrlG13QU_OI&1', 'WEBSTAGING', 'WEB', 'Retail', 'true', 'true', NULL, NULL);


ALTER TABLE `area_surveys` CHANGE `status` `status` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `branch_centers` CHANGE `status` `status` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `loan_plans` CHANGE `rateOfInterest` `rateOfInterest` DOUBLE(5,2) NOT NULL;

ALTER TABLE `employee_transfers` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;
ALTER TABLE `death_members` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;


 UPDATE `settings` SET `settings_version` = '3.00' Where `id` = 1;
 UPDATE `settings` SET `settings_system_javascript_versioning` = now() Where `id` = 1;

 -- -- version 3.00 End ================ MAJOR UPDATE ================ 24-01-2023 =====================================



 ALTER TABLE `loan_disbursements` ADD `topup_id` INT NULL AFTER `purpose_id`;
 ALTER TABLE `vouchers` ADD `topup_id` INT NULL AFTER `advance_emi_security_id`;
 ALTER TABLE `member_loan_plans` ADD `topup_id` INT NULL AFTER `createdBy`;

 ALTER TABLE `update_center_leaders` ADD `previous_center_leader_id` INT NULL AFTER `center_leader_id`;

  UPDATE `settings` SET `settings_version` = '3.01' Where `id` = 1;
 UPDATE `settings` SET `settings_system_javascript_versioning` = now() Where `id` = 1;

 -- -- version 3.01 End ================ MAJOR UPDATE ================ 12-05-2023 =====================================


ALTER TABLE `configuration_settings` ADD `logoutFromOtherDevice` VARCHAR(255) NULL DEFAULT 'false' AFTER `staffBackDateEntry`, ADD `loginWithOtp` VARCHAR(255) NULL DEFAULT 'false' AFTER `logoutFromOtherDevice`;
ALTER TABLE `advance_emi_securities` ADD `type` VARCHAR(255) NOT NULL DEFAULT 'emi' AFTER `paymentDate`;

-- 07-07-2023
ALTER TABLE `member_transfers` ADD `employee_id` INT NULL AFTER `new_collection_center_id`, ADD `previous_employee_id` INT NULL AFTER `employee_id`, ADD `transferType` VARCHAR(255) NOT NULL AFTER `previous_employee_id`;
ALTER TABLE `member_transfers` CHANGE `branch_id` `branch_id` BIGINT(20) UNSIGNED NULL, CHANGE `member_id` `member_id` BIGINT(20) UNSIGNED NULL, CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL, CHANGE `new_branch_id` `new_branch_id` BIGINT(20) UNSIGNED NULL, CHANGE `new_collection_center_id` `new_collection_center_id` BIGINT(20) UNSIGNED NULL;

-- 19-07-2023
ALTER TABLE `members` ADD `latitude` VARCHAR(255) NULL AFTER `ipAddress`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;
ALTER TABLE `member_collections` ADD `latitude` VARCHAR(255) NULL AFTER `ipAddress`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;
ALTER TABLE `loan_disbursements` ADD `latitude` VARCHAR(255) NULL AFTER `ipAddress`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;
ALTER TABLE `log_activity` ADD `latitude` VARCHAR(255) NULL AFTER `user_id`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;
ALTER TABLE `users` ADD `latitude` VARCHAR(255) NULL AFTER `status`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;

-- 23-07-2023
ALTER TABLE `salary_disbursements` ADD `basic` VARCHAR(255) NULL AFTER `branch_ledger_id`, ADD `houseRentAllowance` VARCHAR(255) NULL AFTER `basic`, ADD `fuelAllowance` VARCHAR(255) NULL AFTER `houseRentAllowance`, ADD `mobileAllowance` VARCHAR(255) NULL AFTER `fuelAllowance`, ADD `specialAllowance` VARCHAR(255) NULL AFTER `mobileAllowance`, ADD `commission` VARCHAR(255) NULL AFTER `specialAllowance`, ADD `otherAllowance` VARCHAR(255) NULL AFTER `commission`, ADD `pfDeductions` VARCHAR(255) NULL AFTER `otherAllowance`, ADD `advanceDeductions` VARCHAR(255) NULL AFTER `pfDeductions`, ADD `leavesDeductions` VARCHAR(255) NULL AFTER `advanceDeductions`, ADD `otherDeductions` VARCHAR(255) NULL AFTER `leavesDeductions`;
ALTER TABLE `promise_to_pays` ADD `latitude` VARCHAR(255) NULL AFTER `remark`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;

-- 18-08-2023
ALTER TABLE `settings` ADD `enableBranchDisburseFromHeadOffice` VARCHAR(255) NULL DEFAULT 'false' AFTER `cibilMemberShortName`;
ALTER TABLE `loan_disbursements` ADD `head_office_branch_ledger_id` INT NULL DEFAULT NULL AFTER `branch_ledger_id`;
ALTER TABLE `configuration_settings` ADD `otherBranchDisburseFromHeadOffice` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `loginWithOtp`;

ALTER TABLE `configuration_settings` ADD `enableAndroidApp` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `otherBranchDisburseFromHeadOffice`, ADD `appValidityStartDate` VARCHAR(255) NULL DEFAULT NULL AFTER `enableAndroidApp`, ADD `appValidityEndDate` VARCHAR(255) NULL DEFAULT NULL AFTER `appValidityStartDate`;
ALTER TABLE `settings` ADD `enableEmails` VARCHAR(255) NOT NULL DEFAULT 'true' AFTER `sattlementSMS`;
ALTER TABLE `settings` ADD `interestPlanDeletePendingEmiLoanComplete` VARCHAR(255) NULL DEFAULT 'false' AFTER `enableBranchDisburseFromHeadOffice`;
ALTER TABLE `settings` ADD `salaryDisbursementSMS` VARCHAR(255) NULL DEFAULT 'false' AFTER `loanDisbursementSMS`;
ALTER TABLE `salary_disbursements` ADD `disburseFromHeadOffice` VARCHAR(255) NULL DEFAULT 'false' AFTER `otherDeductions`;

-- 27-09-2023
ALTER TABLE `configuration_settings` ADD `AndroidAppVersion` VARCHAR(255) NULL DEFAULT '1.0' AFTER `enableAndroidApp`;
ALTER TABLE `configuration_settings` ADD `enachIciciMandate` VARCHAR(255) NULL DEFAULT 'false' AFTER `appValidityEndDate`, ADD `enachIciciMarchantId` VARCHAR(255) NULL AFTER `enachIciciMandate`, ADD `enachIciciSaltKey` VARCHAR(255) NULL AFTER `enachIciciMarchantId`;

-- 08-10-2023
ALTER TABLE `enach_mandates` ADD `clnt_txn_ref` VARCHAR(255) NULL AFTER `date`, ADD `tpsl_bank_cd` VARCHAR(255) NULL AFTER `clnt_txn_ref`, ADD `tpsl_txn_id` VARCHAR(255) NULL AFTER `tpsl_bank_cd`, ADD `txn_amt` VARCHAR(255) NULL AFTER `tpsl_txn_id`, ADD `clnt_rqst_meta` VARCHAR(255) NULL AFTER `txn_amt`, ADD `BankTransactionID` VARCHAR(255) NULL AFTER `clnt_rqst_meta`, ADD `mandate_reg_no` VARCHAR(255) NULL AFTER `BankTransactionID`, ADD `callbackResponse` VARCHAR(255) NULL AFTER `mandate_reg_no`;
ALTER TABLE `enach_transactions` ADD `member_emi_id` BIGINT NOT NULL AFTER `id`;
ALTER TABLE `enach_transactions` ADD `callbackResponse` TEXT NULL DEFAULT NULL AFTER `date`;
ALTER TABLE `enach_transactions` ADD `member_collection_id` INT NULL AFTER `callbackResponse`;
ALTER TABLE `configuration_settings` ADD `enachIciciEnvironment` VARCHAR(255) NOT NULL DEFAULT 'test' AFTER `enachIciciSaltKey`;

-- PhonePe
ALTER TABLE `configuration_settings` ADD `phonepePaymentGateway` VARCHAR(255) NULL DEFAULT 'false' AFTER `appValidityEndDate`, ADD `phonepeMerchantId` VARCHAR(255) NULL DEFAULT 'MERCHANTUAT' AFTER `phonepePaymentGateway`, ADD `phonepeMerchantUserId` VARCHAR(255) NULL DEFAULT 'MUID123' AFTER `phonepeMerchantId`, ADD `phonepeSaltKey` VARCHAR(255) NULL DEFAULT '099eb0cd-02cf-4e2a-8aca-3e6c6aff0399' AFTER `phonepeMerchantUserId`, ADD `phonepeEnvironment` VARCHAR(255) NULL DEFAULT 'test' AFTER `phonepeSaltKey`;

ALTER TABLE `staff_attendances` ADD `latitude` VARCHAR(255) NULL AFTER `attendance_marked_id`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`, ADD `location` VARCHAR(255) NULL AFTER `longitude`;
ALTER TABLE `death_members` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;

-- 11-12-2023 Enach
ALTER TABLE `enach_transactions` ADD `branch_ledger_id` INT NULL AFTER `branch_center_id`;

ALTER TABLE `area_surveys` CHANGE `polulationOfVillage` `populationOfVillage` VARCHAR(191) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NULL DEFAULT NULL;
ALTER TABLE `area_surveys` ADD `latitude` VARCHAR(255) NULL AFTER `otherCastPercentage`, ADD `longitude` VARCHAR(255) NULL AFTER `latitude`;
ALTER TABLE `staff` ADD `gender` VARCHAR(255) NULL AFTER `dob`;

-- 5-1-2024 
UPDATE `ledgers` SET `group_id` = '5' WHERE `ledgers`.`id` = 15;

-- 23-1-2024 
ALTER TABLE `settings` ADD `dailyMultipleLoanDisbursement` VARCHAR(255) NULL DEFAULT 'true' AFTER `interestPlanDeletePendingEmiLoanComplete`, ADD `dailyMultipleMemberCollection` VARCHAR(255) NULL DEFAULT 'true' AFTER `dailyMultipleLoanDisbursement`;
-- 1-2-2024 
ALTER TABLE `configuration_settings` ADD `smsApiUpdateTemplate` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `smsApiTemplateCompanyName`;
ALTER TABLE `configuration_settings` ADD `loanPlanDayshift` VARCHAR(255) NULL DEFAULT 'false' AFTER `enableHrm`;
ALTER TABLE `member_collections` ADD `remark` VARCHAR(255) NULL DEFAULT NULL AFTER `createdBy`;

-- 10-02-2024 SMS TEMPLATES
INSERT INTO `sms_templates` (`id`, `smsTemplateName`, `smsTemplate`, `sms_template_id`, `type`, `templateVariable`, `date`, `remark`, `status`, `ipAddress`, `created_at`, `updated_at`) VALUES
(1, 'LOAN DISBUREMENT', 'Dear {#USERNAME#}, Congratulations Your Loan amount Rs. {#AMT#} has been disbursed successfully today. Regards', '', 'disbursement', '{#USERNAME#},{#AMT#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(2, 'NEW MEMBER REGISTRATION', 'Dear {#USERNAME#}, You are successfully registered as member & are now eligible for taking loans. You will receive notifications related to the process. Regards', '', 'register', '{#USERNAME#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(3, 'DOCUMENT VERIFICATION', 'Dear {#USERNAME#}, Your documents have been successfully verified and are updated on our system. Regards', '', 'verification', '{#USERNAME#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(4, 'SALARY DISBURSEMENT', 'Dear {#USERNAME#}, Your salary has been disbursed successfully on {#DATE#}. Regards', '', 'salary', '{#USERNAME#},{#DATE#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(5, 'MEMBER EMI DUE DATE', 'Dear Customer, EMI of Rs. {#AMT#} is due on {#DATE#}. Please ensure to have sufficient funds. Ignore this message if already paid. Regards', '', 'due-emi', '{#AMT#},{#DATE#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(6, 'MEMBER INSTALLMENT', 'Dear Customer, Your instalment amount Rs. {#AMT#} has been successfully received at our end on {#DATE#} Regards', '', 'collection', '{#AMT#},{#DATE#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(7, 'MEMBER/ AGENT OTP', 'Dear User, {#OTP#} is your One Time Password for Mobile Verification. Please do not share it with anyone. The code will expire in 15 minutes. Regards', '', 'otp', '{#OTP#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(8, 'LOGIN CREDENTIALS', 'Dear User, Your application is registered successfully. Your login credentials are: User ID- {#USERID#} Password- {#PASSWORD#} Regards', '', 'credential', '{#USERID#},{#PASSWORD#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),

(9, 'SETTLEMENT ALERT', 'Dear Customer, Amount of Rs. {#AMT#} has been successfully received against Loan ID {#LOANID#} and your loan is now settled. Regards', '', 'sattlement', '{#AMT#},{#LOANID#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(10, 'OVERDUE REMINDER', 'Dear Customer, Your loan EMI {#AMT#} is overdue from {#DATE#}. Please make payment at the earliest to avoid penalties. Regards', '', 'overdue', '{#AMT#},{#DATE#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(11, 'AGENT COLLECTION', 'Dear Customer, Your EMI amount Rs. {#AMT#} is successfully collected by {#AGENT#} today. You will receive an update upon approval. Regards', '', 'agent-collection', '{#AMT#},{#AGENT#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL);

UPDATE `settings` SET `settings_version` = '3.02' Where `id` = 1;
UPDATE `settings` SET `settings_system_javascript_versioning` = now() Where `id` = 1;

-- -- version 3.02 End ================ UPDATE ================ 16-02-2024 =====================================

INSERT INTO `permissions` (`id`, `permission`, `for`, `description`, `status`, `systemEntry`, `created_at`, `updated_at`) VALUES (NULL, 'member-collection-back-date-entry', 'member-collection', 'back-date-entry permission', 'true', NULL, NULL, NULL);
ALTER TABLE `settings` ADD `loginWithOtpSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `sattlementSMS`;

ALTER TABLE `settings` ADD `emiOverdueReminderSendSMSDayAfter` VARCHAR(255) NULL DEFAULT '2' AFTER `emiReminderSendSMSDayBefore`;
ALTER TABLE `settings` ADD `croneOverDueReminderSMS` VARCHAR(255) NULL DEFAULT 'false' AFTER `croneDueReminderSMS`;
ALTER TABLE `member_loan_plans` ADD `closeDate` VARCHAR(255) NULL AFTER `receivedPanulty`;

ALTER TABLE `members` ADD `videoKycLink` VARCHAR(255) NULL AFTER `purpose_id`, ADD `cibilScore` VARCHAR(255) NULL AFTER `videoKycLink`, ADD `remark` VARCHAR(255) NULL AFTER `cibilScore`;
ALTER TABLE `member_collections` ADD `advance_emi_security_id` INT NULL AFTER `paymentMode`;

-- 16-04-2024
ALTER TABLE `settings` ADD `disburseAfterApproval` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `dailyMultipleMemberCollection`;
ALTER TABLE `settings` ADD `enableSalaryDisburseFromHeadOffice` VARCHAR(255) NULL DEFAULT 'false' AFTER `enableBranchDisburseFromHeadOffice`;
ALTER TABLE `settings` ADD `memberCollectionApproveSMS` VARCHAR(255) NULL DEFAULT 'false' AFTER `customerCredentionSMS`;

ALTER TABLE `loan_disbursements` ADD `hideRoi` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `description`;

-- 09-05-2024
ALTER TABLE `product_loans` CHANGE `client_id` `member_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL;

-- 15-05-2024
ALTER TABLE `loan_plans` ADD `nDays` INT NULL COMMENT 'For N no of days plan' AFTER `recoveryType`;

-- 21-05-2024
ALTER TABLE `sattlements` ADD `interestPaidWithSattlement` VARCHAR(255) NULL DEFAULT '0' AFTER `totalAmountDue`;
ALTER TABLE `member_loan_plans` ADD `head_office_branch_ledger_id` INT NULL AFTER `member_id`;

-- 22-06-2024
INSERT INTO `api_charges` (`id`, `provider`, `api`, `gstType`, `charge`, `gst`, `amt`, `status`, `remark`, `ipAddress`, `created_at`, `updated_at`) VALUES 
(1, 'SANDBOX', 'AadharGenerateOtp', 'disable', '2', '0', '2', 'true', NULL, NULL, NULL, NULL), 
(2, 'SANDBOX', 'AadharVerifyOtp', 'disable', '3', '0', '3', 'true', NULL, NULL, NULL, NULL),
(3, 'FINB', 'CibilExperianPDF', 'disable', '40', '0', '40', 'false', NULL, NULL, NULL, NULL),
(4, 'FINB', 'CibilEquifax', 'disable', '40', '0', '40', 'false', NULL, NULL, NULL, NULL),
(5, 'BIHAR_EQUIFAX', 'CibilEquifaxBihar', 'disable', '45', '8', '53', 'false', NULL, NULL, NULL, NULL),


(6, 'CASHFREE', 'DigilockerVerifyAccount', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(7, 'CASHFREE', 'DigilockerInitiate', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(8, 'CASHFREE', 'DigilockerStatus', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(9, 'CASHFREE', 'DigilockerDocument', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(10, 'CASHFREE', 'VerifyBankAccountSync', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(11, 'CASHFREE', 'VerifyBankAccountAsync', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(12, 'CASHFREE', 'VerifyBankAccountStatus', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(13, 'CASHFREE', 'VerifyBankAccountPennyDrop', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(14, 'CASHFREE', 'VerifyBankAccountPennyDropDetails', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(15, 'CASHFREE', 'VerifyIfsc', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(16, 'CASHFREE', 'VerifyGstin', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(17, 'CASHFREE', 'VerifyPanGstin', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(18, 'CASHFREE', 'VerifyCin', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(19, 'CASHFREE', 'VerifyUdyam', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(20, 'CASHFREE', 'VerifyPanUdyam', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(21, 'CASHFREE', 'VehicleRcStatus', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(22, 'CASHFREE', 'VerifyVehicleRc', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(23, 'CASHFREE', 'VerifyVoterId', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(24, 'CASHFREE', 'VerifyDrivingLicense', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(25, 'CASHFREE', 'VerifyPassport', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(26, 'CASHFREE', 'UploadEsignatureDocument', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(27, 'CASHFREE', 'InitiateEsignature', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(28, 'CASHFREE', 'EsignatureStatus', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(29, 'CASHFREE', 'SendMobile360Otp', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(30, 'CASHFREE', 'VerifyMobile360Otp', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(31, 'CASHFREE', 'VerifyAdvanceEmployment', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),

(32, 'CASHFREE', 'VerifyPan', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(33, 'CASHFREE', 'PanStatus', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(34, 'CASHFREE', 'VerifyPanLite', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL),
(35, 'CASHFREE', 'VerifyPanAdvance', 'disable', '20', '0', '20', 'false', NULL, NULL, NULL, NULL);


INSERT INTO `admin_wallets` (`id`, `admin_id`, `amount`, `status`, `remark`, `ipAddress`, `created_at`, `updated_at`) VALUES (1, '1', '0', 'false', NULL, NULL, NULL, NULL);

-- 13-07-2024
ALTER TABLE `product_loans` ADD `reg_no` VARCHAR(255) NULL AFTER `insurance_end_date`, ADD `engine_no` VARCHAR(255) NULL AFTER `reg_no`, ADD `chasis_no` VARCHAR(255) NULL AFTER `engine_no`, ADD `serial_no` VARCHAR(255) NULL AFTER `chasis_no`, ADD `expire_date` VARCHAR(255) NULL AFTER `serial_no`, ADD `mfg_date` VARCHAR(255) NULL AFTER `expire_date`, ADD `batch_no` VARCHAR(255) NULL AFTER `mfg_date`, ADD `imei_no_1` VARCHAR(255) NULL AFTER `batch_no`, ADD `imei_no_2` VARCHAR(255) NULL AFTER `imei_no_1`, ADD `warranty` VARCHAR(255) NULL AFTER `imei_no_2`, ADD `other` VARCHAR(255) NULL AFTER `warranty`;

-- 02-08-2024
ALTER TABLE `settings` ADD `cibilBranchId` VARCHAR(255) NULL AFTER `cibilMemberShortName`, ADD `cibilPassword` VARCHAR(255) NULL AFTER `cibilBranchId`;

-- 30-08-2024
ALTER TABLE `settings` ADD `emiAgentCollectionSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `emiCollectionSMS`;

-- 14-10-2024
ALTER TABLE `product_loans` ADD `isRefurbished` VARCHAR(255) NULL DEFAULT 'false' AFTER `other`;

-- 18-10-2024
ALTER TABLE `recurring_deposits` CHANGE `tenureDays` `noInstallment` INT(11) NOT NULL, CHANGE `interestPaymentFrequency` `installmentPaymentFrequency` ENUM('Daily','Weekly','Biweekly ','Monthly','Half-Monthly','Quarterly','Half-Yearly','Annually','AtMaturity') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'AtMaturity';

-- 29-10-2024
ALTER TABLE `deposit_transactions` ADD `deposit_emi_id` INT NULL AFTER `collection_center_id`;

-- 09-11-2024
INSERT INTO `ledgers` (`id`, `name`, `alias`, `group_id`, `openingBalance`, `createdBy`, `updatedBy`, `branch_id`, `type`, `status`, `systemEntry`, `ipAddress`, `created_at`, `updated_at`) VALUES (NULL, 'Deposit Loan', 'Deposit Loan', '5', '0', '1', '1', NULL, NULL, 'active', 'true', '127.0.0.1', NULL, NULL);
INSERT INTO `ledgers` (`id`, `name`, `alias`, `group_id`, `openingBalance`, `createdBy`, `updatedBy`, `branch_id`, `type`, `status`, `systemEntry`, `ipAddress`, `created_at`, `updated_at`) VALUES (NULL, 'Interest(Expense)', 'Interest(Expense)', '10', '0', '1', '1', NULL, NULL, 'active', 'true', '127.0.0.1', NULL, NULL);

-- 14-11-2024
UPDATE `configuration_settings` SET `SANDBOX_VERSION` = '2.0' WHERE `configuration_settings`.`id` = 1;

-- 15-11-2024
ALTER TABLE `members` ADD `nomineeRelation` VARCHAR(255) NULL AFTER `nomineeAadharNo`;
ALTER TABLE `settings` ADD `enableMemberAgeLimit` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true,false' AFTER `disburseAfterApproval`;
ALTER TABLE `settings` ADD `memberMinAge` INT NOT NULL DEFAULT '18' AFTER `enableMemberAgeLimit`, ADD `memberMaxAge` INT NOT NULL DEFAULT '60' AFTER `memberMinAge`;

-- 19-11-2024
ALTER TABLE `configuration_settings` ADD `enableFixedDeposit` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `enachIciciEnvironment`, ADD `enableRecurringDeposit` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `enableFixedDeposit`;

-- 27-11-2024
ALTER TABLE `vouchers` ADD `deposit_id` INT NULL AFTER `topup_id`;
ALTER TABLE `vouchers` ADD `deposit_transaction_id` INT NULL AFTER `deposit_id`;

-- 8-1-2025 Software Validity
ALTER TABLE `configuration_settings` ADD `softwareValidityStartDate` VARCHAR(255) NOT NULL AFTER `appValidityEndDate`, ADD `softwareValidityEndDate` VARCHAR(255) NOT NULL AFTER `softwareValidityStartDate`;
-- 9-1-2025
ALTER TABLE `vouchers` ADD `depositType` VARCHAR(255) NULL AFTER `deposit_id`;

-- 23-1-2025
ALTER TABLE `settings` DROP `cibilMemberCode`, DROP `cibilMemberShortName`, DROP `cibilBranchId`, DROP `cibilPassword`;
-- Highmark (CRIF)
ALTER TABLE `configuration_settings` CHANGE `highmarkMemberCode` `highmarkIndividualkMemberCode` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'memberCode', CHANGE `highmarkMemberShortName` `highmarkIndividualkMemberShortName` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'memberShortName';
ALTER TABLE `configuration_settings` ADD `highmarkGroupkMemberCode` VARCHAR(255) NULL DEFAULT 'memberCode' AFTER `highmarkIndividualkMemberShortName`, ADD `highmarkGroupMemberShortName` VARCHAR(255) NULL DEFAULT 'memberShortName' AFTER `highmarkGroupkMemberCode`, ADD `highmarkBranchId` VARCHAR(255) NULL DEFAULT '' AFTER `highmarkGroupMemberShortName`, ADD `highmarkPassword` VARCHAR(255) NULL DEFAULT '' AFTER `highmarkBranchId`;
-- Equifax
ALTER TABLE `configuration_settings` ADD `equifaxCibilExport` VARCHAR(255) NULL DEFAULT 'false' AFTER `highmarkPassword`, ADD `equifaxIndividualkMemberCode` VARCHAR(255) NULL DEFAULT 'memberCode' AFTER `equifaxCibilExport`, ADD `equifaxIndividualkMemberShortName` VARCHAR(255) NULL DEFAULT 'memberShortName' AFTER `equifaxIndividualkMemberCode`, ADD `equifaxGroupkMemberCode` VARCHAR(255) NULL DEFAULT 'memberCode' AFTER `equifaxIndividualkMemberShortName`, ADD `equifaxGroupMemberShortName` VARCHAR(255) NULL DEFAULT 'memberShortName' AFTER `equifaxGroupkMemberCode`, ADD `equifaxBranchId` VARCHAR(255) NULL DEFAULT '' AFTER `equifaxGroupMemberShortName`, ADD `equifaxPassword` VARCHAR(255) NULL DEFAULT '' AFTER `equifaxBranchId`;

-- 08-02-2025 Member detail nullable
ALTER TABLE `members` CHANGE `gender` `gender` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `dob` `dob` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `fatherName` `fatherName` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `motherName` `motherName` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `address` `address` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `city` `city` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `district` `district` VARCHAR(191) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL, CHANGE `state_id` `state_id` BIGINT(20) UNSIGNED NULL;
-- 10-02-2025
ALTER TABLE `members` ADD `monthlyFamilyExpences` VARCHAR(255) NULL AFTER `incomeTime`;
-- 10-04-2025
ALTER TABLE `loan_disbursements` ADD `penalty_scheme_id` INT NULL AFTER `planCategory`;
ALTER TABLE `loan_disbursements` ADD INDEX `loan_disbursement_penalty_scheme_id_foreign` (`penalty_scheme_id`);
-- 16-04-2025
ALTER TABLE `penalty_schemes` ADD `name` VARCHAR(255) NULL AFTER `id`;
ALTER TABLE `member_panulties` ADD `dueDate` VARCHAR(255) NULL AFTER `amt`;
ALTER TABLE `member_panulties` CHANGE `collection_center_id` `collection_center_id` BIGINT(20) UNSIGNED NULL;
ALTER TABLE `member_collections` ADD `member_penalty_id` INT NULL AFTER `collection_center_id`;
ALTER TABLE `vouchers` ADD `member_penalty_id` INT NULL AFTER `deposit_transaction_id`;
-- 08-05-2025
ALTER TABLE `hdfc_mandates` CHANGE `customer_debit_amount` `customer_debit_amount` DECIMAL(10,2) NULL;

-- 19-05-2025 SMS TEMPLATES
INSERT INTO `sms_templates` (`id`, `smsTemplateName`, `smsTemplate`, `sms_template_id`, `type`, `templateVariable`, `date`, `remark`, `status`, `ipAddress`, `created_at`, `updated_at`) VALUES
(12, 'AGENT DROPOUT', 'Dear Customer, Mr.. {#AGENT#} is no longer associated with our company. We will not be responsible for any transactions made through them. Regards', '', 'agent-dropout', '{#AGENT#},{#AMT#}', '2024-01-01', NULL, 'active', NULL, NULL, NULL),
(13, 'AGENT DROPOUT', 'प्रिय ग्राहक, श्री {#AGENT#} अब हमारे साथ कार्यरत नहीं हैं। अब से उनके साथ किए गए किसी भी लेन-देन के लिए हमारी कंपनी उत्तरदायी नहीं होगी। Regards', '', 'agent-dropout', '{#AGENT#}', '2024-01-01', NULL, 'inactive', NULL, NULL, NULL);

ALTER TABLE `member_panulties` ADD `waivedAmt` INT NULL AFTER `amt`;
ALTER TABLE `member_panulties` ADD `waivedDate` VARCHAR(255) NULL AFTER `waivedAmt`, ADD `waivedStatus` VARCHAR(255) NULL AFTER `waivedDate`;
ALTER TABLE `member_panulties` CHANGE `waivedStatus` `waivedStatus` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NULL DEFAULT 'no';

ALTER TABLE `member_panulties` ADD `ledger_id` INT NULL AFTER `paymentMode`, ADD `branch_ledger_id` INT NULL AFTER `ledger_id`;

-- 13-06-2025
ALTER TABLE `salary_disbursements` ADD `workingDays` VARCHAR(255) NULL AFTER `disburseFromHeadOffice`;
ALTER TABLE `employee_transfers` ADD `loanTransfered` VARCHAR(255) NULL AFTER `collection_center_id`, ADD `loanDisbursements` TEXT NULL AFTER `loanTransfered`;

-- 19-06-2025
ALTER TABLE `configuration_settings` ADD `penaltyModule` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `enableRecurringDeposit`;

-- 16-07-2025
ALTER TABLE `settings` 
        ADD `enableWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `enableEmails`, 
        ADD `createCustomerWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `enableWhatsappSMS`, 
        ADD `loanDisbursementWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `createCustomerWhatsappSMS`, 
        ADD `salaryDisbursementWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `loanDisbursementWhatsappSMS`, 
        ADD `emiCollectionWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `salaryDisbursementWhatsappSMS`, 
        ADD `emiAgentCollectionWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `emiCollectionWhatsappSMS`, 
        ADD `passwordResetWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `emiAgentCollectionWhatsappSMS`, 
        ADD `croneDueReminderWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `passwordResetWhatsappSMS`, 
        ADD `croneOverDueReminderWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `croneDueReminderWhatsappSMS`, 
        ADD `documentVerificationWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `croneOverDueReminderWhatsappSMS`,
        ADD `customerCredentionWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `documentVerificationWhatsappSMS`,
        ADD `memberCollectionApproveWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `customerCredentionWhatsappSMS`,
        ADD `memberVerifyOTPWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `memberCollectionApproveWhatsappSMS`,
        ADD `guarantorVerifyOTPWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `memberVerifyOTPWhatsappSMS`,
        ADD `staffCredentionWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `guarantorVerifyOTPWhatsappSMS`,
        ADD `staffVerifyOTPWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `staffCredentionWhatsappSMS`,
        ADD `sattlementWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `staffVerifyOTPWhatsappSMS`,
        ADD `loginWithOtpWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `sattlementWhatsappSMS`,
        ADD `agentDropoutWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `loginWithOtpWhatsappSMS`;

ALTER TABLE `settings` ADD `agentDropoutSMS` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `loginWithOtpSMS`;

ALTER TABLE `configuration_settings` ADD `whatsapp` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `penaltyModule`, ADD `whatsappPhoneNumberId` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `whatsapp`, ADD `whatsappToken` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `whatsappPhoneNumberId`;

-- 26-07-2025 "Insurance Fee Payable" under Current Liabilities
UPDATE `ledgers` SET `group_id` = '6' WHERE `ledgers`.`id` = 13;
UPDATE `branch_ledgers` SET `group_id` = '6' WHERE `ledger_id` = 13;

-- 02-08-2025
ALTER TABLE `settings` ADD `compoudingInterestPlan` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `memberMaxAge`;

-- Mortgage
ALTER TABLE `configuration_settings` ADD `mortgageModule` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `penaltyModule`;
-- ALTER TABLE `settings` ADD `loginWithEmail` VARCHAR(255) NOT NULL DEFAULT 'true' AFTER `compoudingInterestPlan`, ADD `loginWithMobile` VARCHAR(255) NOT NULL DEFAULT 'true' AFTER `loginWithEmail`, ADD `loginWithCode` VARCHAR(255) NOT NULL DEFAULT 'true' AFTER `loginWithMobile`;
ALTER TABLE `settings`
  ADD `loginWithEmail` ENUM('true','false') NOT NULL DEFAULT 'true' AFTER `compoudingInterestPlan`,
  ADD `loginWithMobile` ENUM('true','false') NOT NULL DEFAULT 'true' AFTER `loginWithEmail`,
  ADD `loginWithCode` ENUM('true','false') NOT NULL DEFAULT 'true' AFTER `loginWithMobile`;
-- Investor Module
ALTER TABLE `configuration_settings` ADD `investorModule` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `mortgageModule`;
ALTER TABLE `users` ADD `isDirector` ENUM('yes', 'no') NOT NULL DEFAULT 'no' AFTER `type`;
ALTER TABLE `users` ADD `ledger_id` INT NULL AFTER `isDirector`;
ALTER TABLE `vouchers` ADD `investor_transaction_id` INT NULL AFTER `advance_emi_security_id`;

ALTER TABLE `product_mortgages` CHANGE `demand_value` `demand_value` DECIMAL(12,2) NULL DEFAULT '0.00';
ALTER TABLE `vehicle_mortgages` CHANGE `demand_value` `demand_value` DECIMAL(12,2) NULL DEFAULT '0.00';
ALTER TABLE `metal_mortgages` CHANGE `demand_value` `demand_value` DECIMAL(12,2) NULL DEFAULT '0.00';

ALTER TABLE `settings` ADD `maxActiveLoansAllowed` INT NOT NULL DEFAULT '2' AFTER `loginWithCode`, ADD `repeatLoanVerification` VARCHAR(255) NOT NULL DEFAULT 'false' AFTER `maxActiveLoansAllowed`;
ALTER TABLE `configuration_settings` ADD `mobileMuneem` ENUM('true', 'false') NOT NULL DEFAULT 'false' AFTER `appValidityEndDate`;
ALTER TABLE `loan_plans` CHANGE `rateOfInterest` `rateOfInterest` DOUBLE(6,3) NOT NULL;
ALTER TABLE `loan_disbursements` ADD `mobileNo` VARCHAR(255) NULL AFTER `hideRoi`, ADD `mobileOtp` VARCHAR(255) NULL AFTER `mobileNo`, ADD `mobileOtpDate` DATE NULL AFTER `mobileOtp`, ADD `mobileOtpStatus` ENUM('true', 'false') NOT NULL DEFAULT 'false' AFTER `mobileOtpDate`, ADD `mobileOtpStaff` INT NULL AFTER `mobileOtpStatus`;
-- ALTER TABLE `settings` ADD `reverifyDisbursementSMS` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `agentDropoutSMS`;
ALTER TABLE `settings`
  ADD `reverifyDisbursementSMS` ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `agentDropoutSMS`;
-- ALTER TABLE `settings` ADD `reverifyDisbursementWhatsappSMS` VARCHAR(255) NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `agentDropoutWhatsappSMS`;
ALTER TABLE `settings` ADD `reverifyDisbursementWhatsappSMS` ENUM('true','false') NOT NULL DEFAULT 'false' COMMENT 'true/false' AFTER `agentDropoutWhatsappSMS`;
ALTER TABLE `member_panulties` ADD `remark` VARCHAR(255) NULL AFTER `branch_ledger_id`;
ALTER TABLE `members` ADD `registerFrom` VARCHAR(255) NULL AFTER `cibilScore`;
ALTER TABLE `wallet_transactions` CHANGE `response` `response` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL;
INSERT INTO `ledgers` (`id`, `name`, `alias`, `group_id`, `openingBalance`, `createdBy`, `updatedBy`, `branch_id`, `type`, `status`, `systemEntry`, `ipAddress`, `created_at`, `updated_at`) VALUES (NULL, 'Sale A/C', 'Sale A/C', '17', '0', '1', '1', NULL, NULL, 'active', 'true', '127.0.0.1', NULL, NULL);

-- 02-09-2025
ALTER TABLE `investments` ADD `last_emi_generated_at` DATE NULL AFTER `totalInterestPaid`;
ALTER TABLE `configuration_settings` ADD `multipleLedgerDisbursement` ENUM('true','false') NOT NULL DEFAULT 'false' AFTER `whatsappToken`;
ALTER TABLE `loan_disbursements` ADD `loanType` ENUM('singleLedger','multipleLedger') NOT NULL DEFAULT 'singleLedger' AFTER `mobileOtpStaff`;

-- 16-10-2025
ALTER TABLE `recurring_deposits` CHANGE `installmentPaymentFrequency` `installmentPaymentFrequency` ENUM('Daily','Weekly','Monthly','Half-Monthly','Quarterly','Half-Yearly','Annually','AtMaturity') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'AtMaturity';

-- 05-11-2025
ALTER TABLE `configuration_settings` ADD `cibilApiValidityStartDate` DATE NULL AFTER `smsApiUpdateTemplate`, ADD `cibilApiValidityEndDate` DATE NULL AFTER `cibilApiValidityStartDate`;
UPDATE `member_collections` SET `type` = 'penalty' WHERE type='panulty';
ALTER TABLE `member_collections` ADD `approveBy` INT NOT NULL AFTER `createdBy`;
ALTER TABLE `member_collections` ADD `collection_head_office` ENUM('yes','no') NOT NULL DEFAULT 'no' AFTER `receivedBy`;
ALTER TABLE `product_loans` ADD `disbursement_amount` INT NOT NULL AFTER `loan_amount`;
ALTER TABLE `members` ADD `meritalStatus` VARCHAR(255) NULL AFTER `pin`, ADD `currentAddress` VARCHAR(255) NULL AFTER `meritalStatus`, ADD `currentArea` VARCHAR(255) NULL AFTER `currentAddress`, ADD `currentCity` VARCHAR(255) NULL AFTER `currentArea`, ADD `currentDistrict` VARCHAR(255) NULL AFTER `currentCity`, ADD `current_state_id` INT NULL AFTER `currentDistrict`, ADD `currentPin` VARCHAR(255) NULL AFTER `current_state_id`, ADD `residentType` VARCHAR(255) NULL AFTER `currentPin`;
ALTER TABLE `member_collections` CHANGE `approveBy` `approveBy` BIGINT(20) UNSIGNED NULL;
-- 08-12-2025
ALTER TABLE `members` ADD `religion` VARCHAR(255) NULL AFTER `residentType`;
ALTER TABLE `avenue_bill_payments` DROP FOREIGN KEY `abp_member_fk`; 
ALTER TABLE `avenue_bill_payments` ADD CONSTRAINT `abp_member_fk` FOREIGN KEY (`member_id`) REFERENCES `users`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;
ALTER TABLE `avenue_bill_payments` ADD `bill_amount` DECIMAL(12,2) NULL AFTER `bill_number`;

-- 04-02-2026
ALTER TABLE `staff` CHANGE `bank_branch` `bank_branch` VARCHAR(255) NULL DEFAULT NULL;
ALTER TABLE `staff` ADD `pfNumber` VARCHAR(255) NULL AFTER `aadharStatus`, ADD `esiNumber` VARCHAR(255) NULL AFTER `pfNumber`;
ALTER TABLE `staff` ADD `pfDeduction` DECIMAL(10,2) NULL AFTER `esiNumber`, ADD `esiDeduction` DECIMAL(10,2) NULL AFTER `pfDeduction`;
ALTER TABLE `staff` ADD `ta` DECIMAL(10,2) NULL AFTER `esiDeduction`, ADD `da` DECIMAL(10,2) NULL AFTER `ta`, ADD `hra` DECIMAL(10,2) NULL AFTER `da`;
ALTER TABLE `staff` ADD `employeeWelfare` DECIMAL(10,2) NULL AFTER `hra`, ADD `insurancePremium` DECIMAL(10,2) NULL AFTER `employeeWelfare`;
ALTER TABLE `salary_disbursements` ADD `accountHolderName` VARCHAR(255) NULL AFTER `pfDeductions`, ADD `ifsc` VARCHAR(255) NULL AFTER `accountHolderName`, ADD `esiDeduction` DECIMAL(10,2) NULL AFTER `ifsc`, ADD `ta` DECIMAL(10,2) NULL AFTER `esiDeduction`, ADD `da` DECIMAL(10,2) NULL AFTER `ta`, ADD `employeeWelfare` DECIMAL(10,2) NULL AFTER `da`, ADD `insurancePremium` DECIMAL(10,2) NULL AFTER `employeeWelfare`;
ALTER TABLE `salary_disbursements` ADD `fixedAllowance` DECIMAL(10,2) NULL AFTER `insurancePremium`;

UPDATE `settings` SET `settings_system_javascript_versioning` = now() Where `id` = 1;

