-- =====================================================
-- Financial Modules Database Schema
-- Includes: Manual Adjustment, House Account Deal, Internal Transfer
-- And updates to companyAgentTransaction and sequenceMaster
-- =====================================================

-- =====================================================
-- 1. CREATE NEW TABLES
-- =====================================================

-- Manual Adjustment Table
CREATE TABLE IF NOT EXISTS `manualAdjustment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` varchar(50) NOT NULL,
  `agentId` int(11) DEFAULT NULL,
  `currency` CHAR(5) DEFAULT 'AUD',
  `pmntType` ENUM('CASH','NCASH') DEFAULT 'CASH',
  `companyAccountId` MEDIUMINT NOT NULL DEFAULT '0',
  `transType` ENUM('DR','CR') DEFAULT 'CR',  
  `amount` DECIMAL(18,6) DEFAULT '0.000000',
  `description` VARCHAR(200) DEFAULT NULL,
  `status` enum('A','I','D') NOT NULL DEFAULT 'A',
  `createdBy` int(11) DEFAULT NULL,
  `createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,  
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_serial` (`serialNo`),
  KEY `idx_created` (`createdBy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- House Account Deal Table
CREATE TABLE IF NOT EXISTS `houseAccountDeal` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` varchar(50) NOT NULL,
  `counterpartyId` int(11) DEFAULT NULL,
  `sellCurrency` CHAR(5) DEFAULT 'AUD',
  `buyCurrency` CHAR(5) DEFAULT 'USD',
  `sellCompanyAccountId` MEDIUMINT NOT NULL DEFAULT '0',
  `buyCompanyAccountId` MEDIUMINT NOT NULL DEFAULT '0',
  `sellAmount` DECIMAL(18,6) DEFAULT '0.000000',
  `buyAmount` DECIMAL(18,6) DEFAULT '0.000000',
  `rate` DECIMAL(18,6) DEFAULT '1.000000',
  `description` VARCHAR(200) DEFAULT NULL,
  `status` enum('A','I','D') NOT NULL DEFAULT 'A',
  `createdBy` int(11) DEFAULT NULL,
  `createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_serial` (`serialNo`),
  KEY `idx_created` (`createdBy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- Internal Transfer Table
CREATE TABLE IF NOT EXISTS `internalTransfer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `serialNo` varchar(50) NOT NULL,
  `debitAgentId` int(11) DEFAULT NULL,  
  `debitCompanyAccountId` MEDIUMINT NOT NULL DEFAULT '0',
  `creditAgentId` int(11) DEFAULT NULL,
  `creditCompanyAccountId` MEDIUMINT NOT NULL DEFAULT '0',
  `currency` CHAR(5) DEFAULT 'AUD',
  `amount` DECIMAL(18,6) DEFAULT '0.000000',
  `description` VARCHAR(200) DEFAULT NULL,
  `status` enum('A','I','D') NOT NULL DEFAULT 'A',
  `createdBy` int(11) DEFAULT NULL,
  `createdOn` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_serial` (`serialNo`),
  KEY `idx_created` (`createdBy`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- =====================================================
-- 2. UPDATE SEQUENCE MASTER TABLE
-- =====================================================

-- Add new sequence columns to sequenceMaster table
ALTER TABLE `sequenceMaster` 
ADD COLUMN `manualAdjSeq` int(11) DEFAULT 1,
ADD COLUMN `houseAccountDealSeq` int(11) DEFAULT 1,
ADD COLUMN `internalTransferSeq` int(11) DEFAULT 1;

-- =====================================================
-- 3. UPDATE COMPANY AGENT TRANSACTION TABLE
-- =====================================================

-- Add reference columns to companyAgentTransaction table for the three new transaction types
ALTER TABLE `companyAgentTransaction` 
ADD COLUMN `manualAdjustmentId` int(11) DEFAULT NULL COMMENT 'Reference to manualAdjustment.id',
ADD COLUMN `houseAccountDealId` int(11) DEFAULT NULL COMMENT 'Reference to houseAccountDeal.id',
ADD COLUMN `internalTransferId` int(11) DEFAULT NULL COMMENT 'Reference to internalTransfer.id';

-- Add indexes for the new reference columns
ALTER TABLE `companyAgentTransaction`
ADD INDEX `idx_manual_adjustment` (`manualAdjustmentId`),
ADD INDEX `idx_house_account_deal` (`houseAccountDealId`), 
ADD INDEX `idx_internal_transfer` (`internalTransferId`);

-- Add foreign key constraints (optional - uncomment if you want referential integrity)
-- ALTER TABLE `companyAgentTransaction`
-- ADD CONSTRAINT `fk_cat_manual_adjustment` FOREIGN KEY (`manualAdjustmentId`) REFERENCES `manualAdjustment`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
-- ADD CONSTRAINT `fk_cat_house_account_deal` FOREIGN KEY (`houseAccountDealId`) REFERENCES `houseAccountDeal`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,
-- ADD CONSTRAINT `fk_cat_internal_transfer` FOREIGN KEY (`internalTransferId`) REFERENCES `internalTransfer`(`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- =====================================================
-- 4. SAMPLE DATA (Optional - for testing)
-- =====================================================

-- Sample counterparty data (if counterparty table exists)
-- INSERT INTO `counterparty` (`counterpartyCode`, `companyName`, `status`, `createdBy`, `createdOn`) 
-- VALUES ('CP001', 'Sample Counterparty Ltd', 'A', 1, NOW())
-- ON DUPLICATE KEY UPDATE `counterpartyCode` = VALUES(`counterpartyCode`);

-- =====================================================
-- INSTALLATION COMPLETE
-- =====================================================
-- After running this script:
-- 1. Three new financial transaction tables will be created
-- 2. sequenceMaster will have new sequence columns
-- 3. companyAgentTransaction will have reference columns
-- 4. All necessary indexes will be created
-- 5. Models and mappers are ready to use
-- =====================================================

CREATE TABLE `counterparty` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `counterpartyCode` varchar(20) NOT NULL,
  `companyName` varchar(200) NOT NULL,
  `companyInfo` text DEFAULT NULL,
  `businessType` varchar(100) DEFAULT NULL,
  `status` enum('A','I','D') NOT NULL DEFAULT 'A' COMMENT 'A: Active, I: Inactive, D: Deleted',
  `createdBy` int(11) NOT NULL,
  `createdOn` datetime NOT NULL,
  `modifiedBy` int(11) DEFAULT NULL,
  `modifiedOn` datetime DEFAULT NULL,
  `servCDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_counterparty_code` (`counterpartyCode`),
  KEY `idx_counterparty_status` (`status`),
  KEY `idx_counterparty_created` (`createdOn`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


