-- MySQL dump 10.13  Distrib 8.0.45, for Linux (x86_64)
--
-- Host: localhost    Database: tcpay
-- ------------------------------------------------------
-- Server version	8.0.45-0ubuntu0.24.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `acquirer`
--

DROP TABLE IF EXISTS `acquirer`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `acquirer` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `description` varchar(45) DEFAULT NULL,
  `nii` varchar(5) DEFAULT NULL,
  `acquirerInstitutionCode` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `acquirer_connection`
--

DROP TABLE IF EXISTS `acquirer_connection`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `acquirer_connection` (
  `id` int NOT NULL AUTO_INCREMENT,
  `acquirer_id` int DEFAULT NULL,
  `ip_address` varchar(45) NOT NULL,
  `port` int NOT NULL,
  `is_ssl` tinyint DEFAULT NULL,
  `keystore_path` varchar(255) DEFAULT NULL,
  `keystore_password` varchar(45) DEFAULT NULL,
  `truststore_path` varchar(255) DEFAULT NULL,
  `truststore_password` varchar(45) DEFAULT NULL,
  `tpdu` varchar(45) DEFAULT NULL,
  `length_mode` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_idx` (`acquirer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `acquirer_merchant`
--

DROP TABLE IF EXISTS `acquirer_merchant`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `acquirer_merchant` (
  `id` int NOT NULL AUTO_INCREMENT,
  `acquirer_id` int NOT NULL,
  `merchantid` varchar(45) NOT NULL,
  `mcc` varchar(5) DEFAULT NULL,
  `address_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `acquirer_id_idx` (`acquirer_id`),
  KEY `address_id_idx` (`address_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `acquirer_terminal`
--

DROP TABLE IF EXISTS `acquirer_terminal`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `acquirer_terminal` (
  `id` int NOT NULL AUTO_INCREMENT,
  `terminalid` varchar(45) NOT NULL,
  `acquirer_id` int DEFAULT NULL,
  `acquirer_merchant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `acquirer_id_fk_idx` (`acquirer_id`),
  KEY `acquirer_merchant_id_fk_idx` (`acquirer_merchant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `acquirer_terminal_state`
--

DROP TABLE IF EXISTS `acquirer_terminal_state`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `acquirer_terminal_state` (
  `id` bigint NOT NULL,
  `acquirer_terminal_id` bigint NOT NULL,
  `is_busy` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `address`
--

DROP TABLE IF EXISTS `address`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `address` (
  `id` int NOT NULL AUTO_INCREMENT,
  `line1` varchar(45) DEFAULT NULL,
  `line2` varchar(45) DEFAULT NULL,
  `line3` varchar(45) DEFAULT NULL,
  `line4` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `state` varchar(45) DEFAULT NULL,
  `country` varchar(45) DEFAULT NULL,
  `postalcode` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_failed_transaction`
--

DROP TABLE IF EXISTS `pos_failed_transaction`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_failed_transaction` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `s_txn_type` varchar(25) DEFAULT NULL COMMENT 'Source Txn Type',
  `s_tid` varchar(8) DEFAULT NULL COMMENT 'Source Terminal ID (POS)',
  `s_mid` varchar(15) DEFAULT NULL COMMENT 'Source Merchant ID (POS)',
  `s_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Source Terminal STAN',
  `s_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Invoice Number',
  `s_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Batch Number',
  `b_tid` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal ID (Acquirer)',
  `b_mid` varchar(15) DEFAULT NULL COMMENT 'Bank Merchant ID (Acquirer)',
  `acquirer_id` bigint DEFAULT NULL COMMENT 'Bank/Acquirer ID (Acquirer)',
  `b_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal STAN',
  `b_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Invoice Number',
  `b_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Batch Number',
  `b_tid_date` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal Date',
  `b_tid_time` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Time',
  `entry_mode` varchar(3) DEFAULT NULL COMMENT 'Card Entry Mode',
  `condition_code` varchar(2) DEFAULT NULL COMMENT 'Condition Code',
  `currency_code` varchar(3) DEFAULT NULL COMMENT 'Currency Code',
  `mti` varchar(4) DEFAULT NULL COMMENT 'Message Type Indicator',
  `proc_code` varchar(6) DEFAULT NULL COMMENT 'Processing Code',
  `total_amount` decimal(12,2) DEFAULT NULL COMMENT 'Total Amount',
  `auth_amount` decimal(12,2) DEFAULT NULL COMMENT 'Authorization Amount',
  `cash_amount` decimal(12,2) DEFAULT NULL COMMENT 'Cash Amount',
  `tip_amount` decimal(12,2) DEFAULT NULL COMMENT 'Tip Amount',
  `approval_code` varchar(6) DEFAULT NULL COMMENT 'Authorization Approval Code',
  `reference_no` varchar(12) DEFAULT NULL COMMENT 'Reference Number',
  `response_code` varchar(2) DEFAULT NULL COMMENT 'Response Code',
  `response_message` varchar(100) DEFAULT NULL COMMENT 'Response Message',
  `mcc_code` varchar(4) DEFAULT NULL COMMENT 'Merchant Category Code',
  `encrypted_track2` varchar(256) DEFAULT NULL COMMENT 'Encrypted Track 2 Data',
  `encrypted_pan` varchar(256) DEFAULT NULL COMMENT 'Encrypted PAN',
  `masked_card_no` varchar(19) DEFAULT NULL COMMENT 'Masked Card Number',
  `encrypted_expiry` varchar(64) DEFAULT NULL,
  `pan_seq` varchar(3) DEFAULT NULL COMMENT 'PAN Sequence Number',
  `emv_data` text COMMENT 'EMV Data',
  `acquirer_reference_no` varchar(23) DEFAULT NULL COMMENT 'Acquirer Reference Number',
  `scheme_reference_no` varchar(23) DEFAULT NULL COMMENT 'Scheme Reference Number',
  `created_dateTime` datetime DEFAULT NULL COMMENT 'Creation Timestamp',
  `updated_dateTime` datetime DEFAULT NULL COMMENT 'Last Update Timestamp',
  `metadata` text COMMENT 'Additional Metadata',
  `order_number` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_failed_transaction_btid_bmid` (`b_tid`,`b_mid`),
  KEY `idx_failed_transaction_stid` (`s_tid`),
  KEY `idx_failed_transaction_created` (`created_dateTime`),
  KEY `idx_failed_transaction_response_code` (`response_code`),
  KEY `idx_failed_transaction_mti` (`mti`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_merchant`
--

DROP TABLE IF EXISTS `pos_merchant`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_merchant` (
  `id` int NOT NULL AUTO_INCREMENT,
  `merchantid` varchar(45) NOT NULL,
  `merchant_name` varchar(255) DEFAULT NULL,
  `address_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `address_id_fk_idx` (`address_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_temp_transaction`
--

DROP TABLE IF EXISTS `pos_temp_transaction`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_temp_transaction` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `s_txn_type` varchar(25) DEFAULT NULL COMMENT 'Source Txn Type',
  `s_tid` varchar(8) DEFAULT NULL COMMENT 'Source Terminal ID (POS)',
  `s_mid` varchar(15) DEFAULT NULL COMMENT 'Source Merchant ID (POS)',
  `s_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Source Terminal STAN',
  `s_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Invoice Number',
  `s_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Batch Number',
  `b_tid` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal ID (Acquirer)',
  `b_mid` varchar(15) DEFAULT NULL COMMENT 'Bank Merchant ID (Acquirer)',
  `acquirer_id` bigint DEFAULT NULL COMMENT 'Bank/Acquirer ID (Acquirer)',
  `b_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal STAN',
  `b_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Invoice Number',
  `b_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Batch Number',
  `b_tid_date` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal Date',
  `b_tid_time` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Time',
  `entry_mode` varchar(3) DEFAULT NULL COMMENT 'Card Entry Mode',
  `condition_code` varchar(2) DEFAULT NULL COMMENT 'Condition Code',
  `currency_code` varchar(3) DEFAULT NULL COMMENT 'Currency Code',
  `mti` varchar(4) DEFAULT NULL COMMENT 'Message Type Indicator',
  `proc_code` varchar(6) DEFAULT NULL COMMENT 'Processing Code',
  `total_amount` decimal(12,2) DEFAULT NULL COMMENT 'Total Amount',
  `auth_amount` decimal(12,2) DEFAULT NULL COMMENT 'Authorization Amount',
  `cash_amount` decimal(12,2) DEFAULT NULL COMMENT 'Cash Amount',
  `tip_amount` decimal(12,2) DEFAULT NULL COMMENT 'Tip Amount',
  `approval_code` varchar(6) DEFAULT NULL COMMENT 'Authorization Approval Code',
  `reference_no` varchar(12) DEFAULT NULL COMMENT 'Reference Number',
  `response_code` varchar(2) DEFAULT NULL COMMENT 'Response Code',
  `response_message` varchar(100) DEFAULT NULL COMMENT 'Response Message',
  `mcc_code` varchar(4) DEFAULT NULL COMMENT 'Merchant Category Code',
  `encrypted_track2` varchar(256) DEFAULT NULL COMMENT 'Encrypted Track 2 Data',
  `encrypted_pan` varchar(256) DEFAULT NULL COMMENT 'Encrypted PAN',
  `masked_card_no` varchar(19) DEFAULT NULL COMMENT 'Masked Card Number',
  `encrypted_expiry` varchar(64) DEFAULT NULL,
  `pan_seq` varchar(3) DEFAULT NULL COMMENT 'PAN Sequence Number',
  `emv_data` text COMMENT 'EMV Data',
  `acquirer_reference_no` varchar(23) DEFAULT NULL COMMENT 'Acquirer Reference Number',
  `scheme_reference_no` varchar(23) DEFAULT NULL COMMENT 'Scheme Reference Number',
  `created_dateTime` datetime DEFAULT NULL COMMENT 'Creation Timestamp',
  `updated_dateTime` datetime DEFAULT NULL COMMENT 'Last Update Timestamp',
  `status` varchar(45) DEFAULT NULL COMMENT 'status of the transaction e.g. IN_PROGRESS, COMPLETED, FAILED',
  `metadata` text COMMENT 'Additional Metadata',
  `order_number` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_temp_transaction_btid_bmid` (`b_tid`,`b_mid`),
  KEY `idx_temp_transaction_stid` (`s_tid`),
  KEY `idx_temp_transaction_created` (`created_dateTime`)
) ENGINE=InnoDB AUTO_INCREMENT=756 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_terminal`
--

DROP TABLE IF EXISTS `pos_terminal`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_terminal` (
  `id` int NOT NULL AUTO_INCREMENT,
  `terminalid` varchar(15) NOT NULL,
  `serial_number` varchar(45) DEFAULT NULL,
  `pos_merchant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pos_merchant_id_fk_idx` (`pos_merchant_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_terminal_acquirer_terminal`
--

DROP TABLE IF EXISTS `pos_terminal_acquirer_terminal`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_terminal_acquirer_terminal` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pos_terminal_id` int NOT NULL,
  `acquirer_terminal_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pos_terminal_id_fk_idx` (`pos_terminal_id`),
  KEY `acquirer_terminal_id_fk_idx` (`acquirer_terminal_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_terminal_data`
--

DROP TABLE IF EXISTS `pos_terminal_data`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_terminal_data` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `pos_terminalId` varchar(8) NOT NULL,
  `stan` varchar(6) NOT NULL COMMENT 'System Trace Audit Number from DE-11',
  `batch_number` varchar(6) NOT NULL COMMENT 'Batch Number from DE-62',
  `transaction_date` varchar(4) NOT NULL COMMENT 'Transaction Date from DE-13 (MMDD)',
  `transaction_time` varchar(6) NOT NULL COMMENT 'Transaction Time from DE-12 (HHMMSS)',
  `transaction_id` bigint DEFAULT NULL COMMENT 'Reference to pos_transaction.id',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_pos_terminal_data_terminal_id` (`pos_terminalId`),
  KEY `idx_pos_terminal_data_stan` (`stan`),
  KEY `idx_pos_terminal_data_batch` (`batch_number`),
  KEY `idx_pos_terminal_data_transaction_id` (`transaction_id`),
  KEY `idx_pos_terminal_data_composite` (`pos_terminalId`,`stan`,`batch_number`),
  KEY `idx_pos_terminal_data_date_time` (`transaction_date`,`transaction_time`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='Stores POS terminal transaction data extracted from ISO messages';
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_transaction`
--

DROP TABLE IF EXISTS `pos_transaction`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_transaction` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `s_txn_type` varchar(25) DEFAULT NULL COMMENT 'Source Txn Type',
  `s_tid` varchar(8) DEFAULT NULL COMMENT 'Source Terminal ID (POS)',
  `s_mid` varchar(15) DEFAULT NULL COMMENT 'Source Merchant ID (POS)',
  `s_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Source Terminal STAN',
  `s_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Invoice Number',
  `s_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Source Terminal Batch Number',
  `b_tid` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal ID (Acquirer)',
  `b_mid` varchar(15) DEFAULT NULL COMMENT 'Bank Merchant ID (Acquirer)',
  `acquirer_id` bigint DEFAULT NULL COMMENT 'Bank/Acquirer ID (Acquirer)',
  `b_tid_stan` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal STAN',
  `b_tid_invoiceno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Invoice Number',
  `b_tid_batchno` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Batch Number',
  `b_tid_date` varchar(8) DEFAULT NULL COMMENT 'Bank Terminal Date',
  `b_tid_time` varchar(6) DEFAULT NULL COMMENT 'Bank Terminal Time',
  `entry_mode` varchar(3) DEFAULT NULL COMMENT 'Card Entry Mode',
  `condition_code` varchar(2) DEFAULT NULL COMMENT 'Condition Code',
  `currency_code` varchar(3) DEFAULT NULL COMMENT 'Currency Code',
  `mti` varchar(4) DEFAULT NULL COMMENT 'Message Type Indicator',
  `proc_code` varchar(6) DEFAULT NULL COMMENT 'Processing Code',
  `total_amount` decimal(12,2) DEFAULT NULL COMMENT 'Total Amount',
  `auth_amount` decimal(12,2) DEFAULT NULL COMMENT 'Authorization Amount',
  `cash_amount` decimal(12,2) DEFAULT NULL COMMENT 'Cash Amount',
  `tip_amount` decimal(12,2) DEFAULT NULL COMMENT 'Tip Amount',
  `approval_code` varchar(6) DEFAULT NULL COMMENT 'Authorization Approval Code',
  `reference_no` varchar(12) DEFAULT NULL COMMENT 'Reference Number',
  `response_code` varchar(2) DEFAULT NULL COMMENT 'Response Code',
  `response_message` varchar(100) DEFAULT NULL COMMENT 'Response Message',
  `mcc_code` varchar(4) DEFAULT NULL COMMENT 'Merchant Category Code',
  `encrypted_track2` varchar(256) DEFAULT NULL COMMENT 'Encrypted Track 2 Data',
  `encrypted_pan` varchar(256) DEFAULT NULL COMMENT 'Encrypted PAN',
  `hash_pan` char(64) DEFAULT NULL,
  `masked_card_no` varchar(19) DEFAULT NULL COMMENT 'Masked Card Number',
  `encrypted_expiry` varchar(64) DEFAULT NULL,
  `pan_seq` varchar(3) DEFAULT NULL COMMENT 'PAN Sequence Number',
  `emv_data` text COMMENT 'EMV Data',
  `acquirer_reference_no` varchar(23) DEFAULT NULL COMMENT 'Acquirer Reference Number',
  `scheme_reference_no` varchar(23) DEFAULT NULL COMMENT 'Scheme Reference Number',
  `created_dateTime` datetime DEFAULT NULL COMMENT 'Creation Timestamp',
  `updated_dateTime` datetime DEFAULT NULL COMMENT 'Last Update Timestamp',
  `metadata` text COMMENT 'Additional Metadata',
  `order_number` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_transaction_btid_bmid` (`b_tid`,`b_mid`),
  KEY `idx_transaction_stid` (`s_tid`),
  KEY `idx_transaction_created` (`created_dateTime`),
  KEY `idx_transaction_response_code` (`response_code`),
  KEY `idx_transaction_approval_code` (`approval_code`),
  KEY `idx_transaction_amount` (`total_amount`),
  KEY `idx_transaction_mti` (`mti`)
) ENGINE=InnoDB AUTO_INCREMENT=746 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `pos_transaction_reversal`
--

DROP TABLE IF EXISTS `pos_transaction_reversal`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `pos_transaction_reversal` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `s_txn_type` varchar(25) DEFAULT NULL COMMENT 'Source Txn Type',
  `original_temp_txn_id` bigint NOT NULL,
  `reversal_reason` varchar(50) NOT NULL,
  `reversal_status` varchar(20) NOT NULL DEFAULT 'PENDING',
  `s_tid` varchar(8) DEFAULT NULL,
  `s_mid` varchar(15) DEFAULT NULL,
  `s_tid_stan` varchar(6) DEFAULT NULL,
  `b_tid` varchar(8) DEFAULT NULL,
  `b_mid` varchar(15) DEFAULT NULL,
  `acquirer_id` bigint DEFAULT NULL,
  `b_tid_stan` varchar(6) DEFAULT NULL,
  `reversal_mti` varchar(4) DEFAULT NULL,
  `original_mti` varchar(4) DEFAULT NULL,
  `original_encrypted_pan` varchar(128) DEFAULT NULL,
  `original_encrypted_expiry_date` varchar(64) DEFAULT NULL,
  `original_proc_code` varchar(6) DEFAULT NULL,
  `original_amount` decimal(12,2) DEFAULT NULL,
  `original_stan` varchar(6) DEFAULT NULL,
  `original_time` varchar(6) DEFAULT NULL,
  `original_date` varchar(8) DEFAULT NULL,
  `original_entry_mode` varchar(3) DEFAULT NULL,
  `original_pan_seq` varchar(3) DEFAULT NULL,
  `original_reference_no` varchar(12) DEFAULT NULL,
  `original_currency_code` varchar(3) DEFAULT NULL,
  `original_batch_no` varchar(12) DEFAULT NULL,
  `reversal_request` text,
  `reversal_response` text,
  `reversal_response_code` varchar(2) DEFAULT NULL,
  `reversal_reference_no` varchar(12) DEFAULT NULL,
  `retry_count` int NOT NULL DEFAULT '0',
  `max_retry_attempts` int NOT NULL DEFAULT '3',
  `next_retry_time` datetime DEFAULT NULL,
  `created_dateTime` datetime NOT NULL,
  `initiated_dateTime` datetime DEFAULT NULL,
  `completed_dateTime` datetime DEFAULT NULL,
  `updated_dateTime` datetime DEFAULT NULL,
  `error_message` varchar(255) DEFAULT NULL,
  `metadata` text,
  PRIMARY KEY (`id`),
  KEY `idx_reversal_btid_bmid` (`b_tid`,`b_mid`),
  KEY `idx_reversal_original_txn` (`original_temp_txn_id`),
  KEY `idx_reversal_status` (`reversal_status`),
  KEY `idx_reversal_created` (`created_dateTime`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `transaction`
--

DROP TABLE IF EXISTS `transaction`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `transaction` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pan` varchar(45) DEFAULT NULL,
  `proc_code` varchar(45) DEFAULT NULL,
  `amount` varchar(45) DEFAULT NULL,
  `stan` varchar(45) DEFAULT NULL,
  `date_time` varchar(45) DEFAULT NULL,
  `settlement_date` varchar(45) DEFAULT NULL,
  `mcc` varchar(45) DEFAULT NULL,
  `terminal_id` varchar(45) DEFAULT NULL,
  `merchant_id` varchar(45) DEFAULT NULL,
  `reference_id` varchar(45) DEFAULT NULL,
  `auth_code` varchar(45) DEFAULT NULL,
  `response_code` varchar(45) DEFAULT NULL,
  `upi_url` varchar(511) DEFAULT NULL,
  `upi_info` varchar(1023) DEFAULT NULL,
  `meta_data` varchar(45) DEFAULT NULL,
  `date_added` datetime DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=880 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2026-03-11  6:27:03
