-- MySQL dump 10.13  Distrib 8.0.42, for Linux (x86_64)
--
-- Host: localhost    Database: upi_pps
-- ------------------------------------------------------
-- Server version	8.0.42-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 `followup_requests`
--

DROP TABLE IF EXISTS `followup_requests`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `followup_requests` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` bigint unsigned NOT NULL,
  `kind` varchar(255) NOT NULL,
  `status` varchar(255) NOT NULL DEFAULT 'scheduled',
  `attempt_no` int NOT NULL DEFAULT '1',
  `deadline_at` datetime NOT NULL,
  `sent_at` datetime DEFAULT NULL,
  `response_code` varchar(255) DEFAULT NULL,
  `response_payload` json DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `followup_requests_transaction_id_index` (`transaction_id`),
  KEY `followup_requests_kind_index` (`kind`),
  KEY `followup_requests_status_index` (`status`),
  KEY `followup_requests_deadline_at_index` (`deadline_at`),
  CONSTRAINT `followup_requests_transaction_id_fkey` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `fx_quotes`
--

DROP TABLE IF EXISTS `fx_quotes`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `fx_quotes` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `qr_validation_id` bigint unsigned NOT NULL,
  `base_currency` varchar(3) NOT NULL,
  `base_amount` decimal(18,6) DEFAULT NULL,
  `fx_rate` decimal(18,8) NOT NULL,
  `markup_pct` decimal(7,4) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `last_modified_ts` datetime DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fx_quotes_qr_validation_id_index` (`qr_validation_id`),
  KEY `fx_quotes_active_index` (`active`),
  CONSTRAINT `fx_quotes_qr_validation_id_fkey` FOREIGN KEY (`qr_validation_id`) REFERENCES `qr_validations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `international_fx_rates`
--

DROP TABLE IF EXISTS `international_fx_rates`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `international_fx_rates` (
  `id` binary(16) NOT NULL,
  `base_currency` varchar(255) NOT NULL,
  `target_currency` varchar(255) NOT NULL,
  `fx_rate` decimal(18,8) NOT NULL,
  `markup_rate` decimal(5,2) DEFAULT '0.00',
  `effective_from` datetime DEFAULT NULL,
  `effective_until` datetime DEFAULT NULL,
  `active` tinyint(1) DEFAULT '1',
  `source` varchar(255) DEFAULT NULL,
  `corridor` varchar(255) DEFAULT NULL,
  `last_modified_ts` datetime DEFAULT NULL,
  `created_by` varchar(255) DEFAULT NULL,
  `rate_type` varchar(255) DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `intl_fx_rates_unique_active_rate` (`base_currency`,`target_currency`,`corridor`),
  KEY `international_fx_rates_corridor_index` (`corridor`),
  KEY `international_fx_rates_effective_from_effective_until_index` (`effective_from`,`effective_until`),
  KEY `international_fx_rates_active_index` (`active`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `merchant_invoices`
--

DROP TABLE IF EXISTS `merchant_invoices`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `merchant_invoices` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `merchant_id` bigint unsigned NOT NULL,
  `qr_validation_id` bigint unsigned DEFAULT NULL,
  `invoice_name` varchar(255) DEFAULT NULL,
  `invoice_number` varchar(255) DEFAULT NULL,
  `invoice_date` datetime DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `merchant_invoices_merchant_id_index` (`merchant_id`),
  KEY `merchant_invoices_qr_validation_id_index` (`qr_validation_id`),
  CONSTRAINT `merchant_invoices_merchant_id_fkey` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE CASCADE,
  CONSTRAINT `merchant_invoices_qr_validation_id_fkey` FOREIGN KEY (`qr_validation_id`) REFERENCES `qr_validations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `merchants`
--

DROP TABLE IF EXISTS `merchants`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `merchants` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `mid` varchar(255) NOT NULL,
  `sid` varchar(255) DEFAULT NULL,
  `tid` varchar(255) DEFAULT NULL,
  `sub_code` varchar(255) DEFAULT NULL,
  `merchant_type` varchar(255) DEFAULT NULL,
  `merchant_genre` varchar(255) DEFAULT NULL,
  `onboarding_type` varchar(255) DEFAULT NULL,
  `inst_code` varchar(255) DEFAULT NULL,
  `brand_name` varchar(255) DEFAULT NULL,
  `legal_name` varchar(255) DEFAULT NULL,
  `franchise_name` varchar(255) DEFAULT NULL,
  `ownership_type` varchar(255) DEFAULT NULL,
  `country_code` varchar(2) DEFAULT NULL,
  `network_inst_id` varchar(255) DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `partner_id` binary(16) DEFAULT NULL,
  `merchant_code` varchar(255) DEFAULT NULL,
  `merchant_vpa` varchar(255) DEFAULT NULL,
  `business_type` varchar(255) DEFAULT NULL,
  `business_category` varchar(255) DEFAULT NULL,
  `static_qr_code` text,
  `qr_enabled` tinyint(1) DEFAULT '1',
  `dynamic_qr_enabled` tinyint(1) DEFAULT '0',
  `max_transaction_limit` decimal(15,2) DEFAULT NULL,
  `daily_transaction_limit` decimal(15,2) DEFAULT NULL,
  `settlement_frequency` varchar(255) DEFAULT 'T+1',
  `settlement_account_intl` varchar(255) DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `address` text,
  `city` varchar(255) DEFAULT NULL,
  `state` varchar(255) DEFAULT NULL,
  `pincode` varchar(255) DEFAULT NULL,
  `gstin` varchar(255) DEFAULT NULL,
  `pan` varchar(255) DEFAULT NULL,
  `intl_status` varchar(255) DEFAULT 'ACTIVE',
  `onboarded_at` datetime DEFAULT NULL,
  `last_transaction_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `merchants_mid_tid_index` (`mid`,`tid`),
  KEY `merchants_merchant_code_index` (`merchant_code`),
  KEY `merchants_merchant_vpa_index` (`merchant_vpa`),
  KEY `merchants_partner_id_index` (`partner_id`),
  KEY `merchants_intl_status_index` (`intl_status`),
  CONSTRAINT `merchants_partner_id_fkey` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `partners`
--

DROP TABLE IF EXISTS `partners`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `partners` (
  `id` binary(16) NOT NULL,
  `partner_code` varchar(255) NOT NULL,
  `partner_name` varchar(255) NOT NULL,
  `partner_type` varchar(255) NOT NULL,
  `api_endpoint` varchar(255) DEFAULT NULL,
  `api_key` varchar(255) DEFAULT NULL,
  `webhook_url` varchar(255) DEFAULT NULL,
  `webhook_secret` varchar(255) DEFAULT NULL,
  `settlement_account` varchar(255) DEFAULT NULL,
  `commission_rate` decimal(5,2) DEFAULT NULL,
  `status` varchar(255) DEFAULT 'ACTIVE',
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `corridor` varchar(255) DEFAULT NULL,
  `base_currency` varchar(255) DEFAULT NULL,
  `onboarded_at` datetime DEFAULT NULL,
  `last_reconciled_at` datetime DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `partners_partner_code_index` (`partner_code`),
  KEY `partners_corridor_index` (`corridor`),
  KEY `partners_status_index` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `qr_validation_events`
--

DROP TABLE IF EXISTS `qr_validation_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `qr_validation_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `qr_validation_id` bigint unsigned NOT NULL,
  `seq` int NOT NULL,
  `event_type` varchar(255) NOT NULL,
  `payload` json DEFAULT NULL,
  `prev_hash` blob,
  `hash` blob NOT NULL,
  `inserted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `qr_validation_events_qr_validation_id_seq_index` (`qr_validation_id`,`seq`),
  KEY `qr_validation_events_event_type_index` (`event_type`),
  CONSTRAINT `qr_validation_events_qr_validation_id_fkey` FOREIGN KEY (`qr_validation_id`) REFERENCES `qr_validations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `qr_validations`
--

DROP TABLE IF EXISTS `qr_validations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `qr_validations` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `txn_id` varchar(50) DEFAULT NULL,
  `msg_id` varchar(255) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  `payer_addr` varchar(255) DEFAULT NULL,
  `payer_name` varchar(255) DEFAULT NULL,
  `network_inst_id` varchar(20) DEFAULT NULL,
  `con_code` varchar(2) DEFAULT NULL,
  `qr_version` varchar(255) DEFAULT NULL,
  `qr_medium` varchar(255) DEFAULT NULL,
  `ver_token` varchar(255) DEFAULT NULL,
  `qr_expires_at` datetime DEFAULT NULL,
  `foreign_amount` decimal(18,6) DEFAULT NULL,
  `foreign_currency` varchar(3) DEFAULT NULL,
  `fx_rate` decimal(18,8) DEFAULT NULL,
  `markup_pct` decimal(7,4) DEFAULT NULL,
  `inr_amount_calc` decimal(18,2) DEFAULT NULL,
  `qr_hash` blob,
  `raw_xml` blob,
  `status` varchar(255) NOT NULL DEFAULT 'validated',
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `payee_addr` varchar(255) DEFAULT NULL,
  `payee_name` varchar(255) DEFAULT NULL,
  `base_amount` decimal(15,2) DEFAULT NULL,
  `base_currency` varchar(255) DEFAULT NULL,
  `fx_timestamp` datetime DEFAULT NULL,
  `corridor` varchar(255) DEFAULT NULL,
  `fx_provider` varchar(255) DEFAULT NULL,
  `validation_type` varchar(255) DEFAULT 'INTERNATIONAL',
  `merchant_category` varchar(255) DEFAULT NULL,
  `transaction_purpose` varchar(255) DEFAULT NULL,
  `partner_id` binary(16) DEFAULT NULL,
  `merchant_id` bigint unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `qr_validations_msg_id_org_id_index` (`msg_id`,`org_id`),
  UNIQUE KEY `qr_validations_ver_token_index` (`ver_token`),
  KEY `qr_validations_network_inst_id_index` (`network_inst_id`),
  KEY `qr_validations_status_index` (`status`),
  KEY `qr_validations_validation_type_index` (`validation_type`),
  KEY `qr_validations_corridor_index` (`corridor`),
  KEY `qr_validations_partner_id_index` (`partner_id`),
  KEY `qr_validations_merchant_id_index` (`merchant_id`),
  CONSTRAINT `qr_validations_merchant_id_fkey` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `qr_validations_partner_id_fkey` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `schema_migrations`
--

DROP TABLE IF EXISTS `schema_migrations`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `schema_migrations` (
  `version` bigint NOT NULL,
  `inserted_at` datetime DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `transaction_events`
--

DROP TABLE IF EXISTS `transaction_events`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `transaction_events` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` bigint unsigned NOT NULL,
  `seq` int NOT NULL,
  `event_type` varchar(255) NOT NULL,
  `payload` json DEFAULT NULL,
  `prev_hash` blob,
  `hash` blob NOT NULL,
  `inserted_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transaction_events_transaction_id_seq_index` (`transaction_id`,`seq`),
  KEY `transaction_events_event_type_index` (`event_type`),
  CONSTRAINT `transaction_events_transaction_id_fkey` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `transactions`
--

DROP TABLE IF EXISTS `transactions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `transactions` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `org_txn_id` varchar(255) NOT NULL,
  `parent_qr_validation_id` bigint unsigned DEFAULT NULL,
  `payer_addr` varchar(255) DEFAULT NULL,
  `payee_addr` varchar(255) DEFAULT NULL,
  `payee_mid` varchar(255) DEFAULT NULL,
  `payer_bank_ref` varchar(255) DEFAULT NULL,
  `foreign_amount` decimal(18,6) DEFAULT NULL,
  `foreign_currency` varchar(3) DEFAULT NULL,
  `fx_rate` decimal(18,8) DEFAULT NULL,
  `markup_pct` decimal(7,4) DEFAULT NULL,
  `inr_amount` decimal(18,2) DEFAULT NULL,
  `ver_token` varchar(255) DEFAULT NULL,
  `network_inst_id` varchar(255) DEFAULT NULL,
  `current_state` varchar(255) NOT NULL DEFAULT 'initiated',
  `status` varchar(255) NOT NULL DEFAULT 'pending',
  `debit_secured_at` datetime DEFAULT NULL,
  `credit_requested_at` datetime DEFAULT NULL,
  `credit_completed_at` datetime DEFAULT NULL,
  `inserted_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  `payer_name` varchar(255) DEFAULT NULL,
  `payee_name` varchar(255) DEFAULT NULL,
  `currency` varchar(255) DEFAULT 'INR',
  `markup_rate` decimal(5,2) DEFAULT NULL,
  `corridor` varchar(255) DEFAULT NULL,
  `transaction_type` varchar(255) DEFAULT 'INTERNATIONAL',
  `fx_provider` varchar(255) DEFAULT NULL,
  `fx_locked_at` datetime DEFAULT NULL,
  `req_msg_id` varchar(255) DEFAULT NULL,
  `resp_msg_id` varchar(255) DEFAULT NULL,
  `npci_received_at` datetime DEFAULT NULL,
  `partner_credited_at` datetime DEFAULT NULL,
  `completed_at` datetime DEFAULT NULL,
  `failure_reason` varchar(255) DEFAULT NULL,
  `partner_txn_id` varchar(255) DEFAULT NULL,
  `npci_reversal_ref` varchar(255) DEFAULT NULL,
  `partner_id` binary(16) DEFAULT NULL,
  `merchant_id` bigint unsigned DEFAULT NULL,
  `failure_code` varchar(255) DEFAULT NULL,
  `deemed` tinyint(1) NOT NULL DEFAULT '0',
  `chktxn_requested_at` datetime DEFAULT NULL,
  `reversal_requested_at` datetime DEFAULT NULL,
  `finalized_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `transactions_org_txn_id_index` (`org_txn_id`),
  KEY `transactions_parent_qr_validation_id_fkey` (`parent_qr_validation_id`),
  KEY `transactions_ver_token_index` (`ver_token`),
  KEY `transactions_current_state_index` (`current_state`),
  KEY `transactions_status_index` (`status`),
  KEY `transactions_corridor_index` (`corridor`),
  KEY `transactions_transaction_type_index` (`transaction_type`),
  KEY `transactions_partner_id_index` (`partner_id`),
  KEY `transactions_merchant_id_index` (`merchant_id`),
  KEY `transactions_npci_received_at_index` (`npci_received_at`),
  KEY `transactions_deemed_index` (`deemed`),
  CONSTRAINT `transactions_merchant_id_fkey` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`id`) ON DELETE RESTRICT,
  CONSTRAINT `transactions_parent_qr_validation_id_fkey` FOREIGN KEY (`parent_qr_validation_id`) REFERENCES `qr_validations` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_partner_id_fkey` FOREIGN KEY (`partner_id`) REFERENCES `partners` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping routines for database 'upi_pps'
--
/*!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 2025-08-13 18:05:45
INSERT INTO `schema_migrations` (version) VALUES (20250810090000);
INSERT INTO `schema_migrations` (version) VALUES (20250810091000);
INSERT INTO `schema_migrations` (version) VALUES (20250810092000);
INSERT INTO `schema_migrations` (version) VALUES (20250811000001);
INSERT INTO `schema_migrations` (version) VALUES (20250811093000);
