-- Manual Adjustment Category Master Table
-- This table stores categories for manual adjustments

CREATE TABLE IF NOT EXISTS `manual_adjustment_category_master` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `status` enum('A','D') DEFAULT 'A',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `title` (`title`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert some default categories
INSERT INTO `manual_adjustment_category_master` (`title`, `status`) VALUES
('Administrative Adjustment', 'A'),
('System Error Correction', 'A'),
('Fee Adjustment', 'A'),
('Exchange Rate Correction', 'A'),
('Reconciliation Adjustment', 'A'),
('Customer Service Adjustment', 'A'),
('Reversal Transaction', 'A'),
('Other', 'A');

-- Add categoryId column to existing manualAdjustment table
ALTER TABLE `manualAdjustment` 
ADD COLUMN `categoryId` int(11) DEFAULT NULL AFTER `transType`,
ADD KEY `categoryId` (`categoryId`),
ADD CONSTRAINT `fk_manual_adjustment_category` 
    FOREIGN KEY (`categoryId`) REFERENCES `manual_adjustment_category_master` (`id`) 
    ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `customerDepositReq` 
ADD COLUMN `pgType` VARCHAR(20) NULL AFTER `counterSessionId`; 
