/*
SQLyog Community v13.3.0 (64 bit)
MySQL - 10.4.24-MariaDB : Database - lgu_cashcard
*********************************************************************
*/



/*Table structure for table `accounts` */


CREATE TABLE `accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_id` bigint(20) unsigned NOT NULL,
  `account_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('active','suspended','closed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_uuid_unique` (`uuid`),
  UNIQUE KEY `accounts_account_number_unique` (`account_number`),
  KEY `accounts_client_id_foreign` (`client_id`),
  CONSTRAINT `accounts_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `accounts` */

insert  into `accounts`(`id`,`uuid`,`client_id`,`account_number`,`status`,`created_at`,`updated_at`,`deleted_at`) values 
(1,'a692419b-62ba-4537-848a-5416a8027cb2',1,'AC20260312011108681','active','2026-03-12 01:11:08','2026-03-12 01:11:08',NULL);

/*Table structure for table `acquirer_connections` */


CREATE TABLE `acquirer_connections` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `transport` enum('tcp','http') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'tcp',
  `host` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `port` int(10) unsigned DEFAULT NULL,
  `url` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `timeout_ms` int(10) unsigned NOT NULL DEFAULT 30000,
  `header_length` tinyint(3) unsigned NOT NULL DEFAULT 2,
  `nii` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `acquirer_connections_name_unique` (`name`),
  UNIQUE KEY `acquirer_connections_code_unique` (`code`),
  KEY `acquirer_connections_is_active_index` (`is_active`),
  KEY `acquirer_connections_code_index` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `acquirer_connections` */

/*Table structure for table `audit_logs` */


CREATE TABLE `audit_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `action` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `entity_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `audit_logs_user_id_foreign` (`user_id`),
  CONSTRAINT `audit_logs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `audit_logs` */

insert  into `audit_logs`(`id`,`user_id`,`action`,`entity`,`entity_id`,`ip_address`,`created_at`,`updated_at`) values 
(1,1,'CREATE_INSTITUTION','institutions','1','127.0.0.1','2026-03-12 00:59:29','2026-03-12 00:59:29'),
(2,1,'CREATE_PRODUCT','products','1','127.0.0.1','2026-03-12 01:00:39','2026-03-12 01:00:39'),
(3,1,'UPDATE_PRODUCT','products','1','127.0.0.1','2026-03-12 01:09:38','2026-03-12 01:09:38'),
(4,1,'CREATE_CARD_BATCH','card_batches','1','127.0.0.1','2026-03-12 01:10:09','2026-03-12 01:10:09'),
(5,1,'CREATE_CLIENT','clients','d85940ff-60cc-4c4a-8b93-fd1330e7eb35','127.0.0.1','2026-03-12 01:10:44','2026-03-12 01:10:44'),
(6,1,'SUBMIT_KYC','client_kyc','1','127.0.0.1','2026-03-12 01:11:06','2026-03-12 01:11:06'),
(7,1,'APPROVE_KYC','client_kyc','1','127.0.0.1','2026-03-12 01:11:08','2026-03-12 01:11:08'),
(8,1,'ASSIGN_STOCK_CARD','cards','25b1f455-a6d7-471c-9ed2-cf0447f670fd','127.0.0.1','2026-03-12 01:11:39','2026-03-12 01:11:39'),
(9,1,'ACTIVATE_CARD','cards','25b1f455-a6d7-471c-9ed2-cf0447f670fd','127.0.0.1','2026-03-12 01:11:43','2026-03-12 01:11:43'),
(10,1,'CREATE_USER','users','3ccb049e-8281-4cb4-a961-a49f2a41f3cd','127.0.0.1','2026-06-16 05:40:39','2026-06-16 05:40:39');

/*Table structure for table `auth_logs` */


CREATE TABLE `auth_logs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `rrn` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stan` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request_iso` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `response_iso` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `processing_time` int(10) unsigned DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `auth_logs_rrn_index` (`rrn`),
  KEY `auth_logs_stan_index` (`stan`),
  KEY `auth_logs_rrn_stan_index` (`rrn`,`stan`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `cache_locks` */

/*Table structure for table `card_accounts` */


CREATE TABLE `card_accounts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `is_primary` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_accounts_card_id_account_id_unique` (`card_id`,`account_id`),
  KEY `card_accounts_card_id_index` (`card_id`),
  KEY `card_accounts_account_id_index` (`account_id`),
  CONSTRAINT `card_accounts_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `card_accounts_card_account_alignment_fk` FOREIGN KEY (`card_id`, `account_id`) REFERENCES `cards` (`id`, `account_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `card_accounts_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `card_accounts` */

/*Table structure for table `card_batches` */


CREATE TABLE `card_batches` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `institution_id` bigint(20) unsigned NOT NULL,
  `product_id` bigint(20) unsigned NOT NULL,
  `total_cards` int(10) unsigned NOT NULL,
  `perso_file_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'generated',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `card_batches_institution_id_foreign` (`institution_id`),
  KEY `card_batches_product_id_foreign` (`product_id`),
  CONSTRAINT `card_batches_institution_id_foreign` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `card_batches_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `card_batches` */

insert  into `card_batches`(`id`,`institution_id`,`product_id`,`total_cards`,`perso_file_path`,`status`,`created_at`,`updated_at`) values 
(1,1,1,3,'perso-files/batches/batch-1-5e31e018-4d42-4a5b-9d01-69092e3a80a2.txt','in_stock','2026-03-12 01:10:08','2026-03-12 01:10:09');

/*Table structure for table `card_limit_usage` */


CREATE TABLE `card_limit_usage` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card_id` bigint(20) unsigned NOT NULL,
  `txn_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `period_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `amount_used` decimal(15,2) NOT NULL DEFAULT 0.00,
  `txn_count` int(10) unsigned NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `card_limit_usage_card_id_index` (`card_id`),
  KEY `card_limit_usage_period_idx` (`card_id`,`txn_type`,`period_type`,`period_start`),
  CONSTRAINT `card_limit_usage_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `card_limit_usage` */

/*Table structure for table `card_limits` */


CREATE TABLE `card_limits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card_id` bigint(20) unsigned NOT NULL,
  `txn_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `daily_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `weekly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `monthly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `quarterly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `semi_annual_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `annual_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `per_transaction_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `card_limits_card_id_txn_type_unique` (`card_id`,`txn_type`),
  KEY `card_limits_card_id_index` (`card_id`),
  CONSTRAINT `card_limits_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `card_limits` */

insert  into `card_limits`(`id`,`card_id`,`txn_type`,`daily_limit`,`weekly_limit`,`monthly_limit`,`quarterly_limit`,`semi_annual_limit`,`annual_limit`,`per_transaction_limit`,`created_at`) values 
(1,1,'PURCHASE',50000.00,100000.00,200000.00,400000.00,500000.00,1000000.00,10000.00,'2026-03-12 09:10:08'),
(2,2,'PURCHASE',50000.00,100000.00,200000.00,400000.00,500000.00,1000000.00,10000.00,'2026-03-12 09:10:08'),
(3,3,'PURCHASE',50000.00,100000.00,200000.00,400000.00,500000.00,1000000.00,10000.00,'2026-03-12 09:10:09');

/*Table structure for table `card_print_jobs` */


CREATE TABLE `card_print_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card_id` bigint(20) unsigned NOT NULL,
  `printer_id` bigint(20) unsigned NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `printed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `card_print_jobs_card_id_foreign` (`card_id`),
  KEY `card_print_jobs_printer_id_foreign` (`printer_id`),
  CONSTRAINT `card_print_jobs_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE,
  CONSTRAINT `card_print_jobs_printer_id_foreign` FOREIGN KEY (`printer_id`) REFERENCES `printers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `card_print_jobs` */

/*Table structure for table `card_print_queue` */


CREATE TABLE `card_print_queue` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card_id` bigint(20) unsigned NOT NULL,
  `printer_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('queued','printing','printed','failed') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'queued',
  `requested_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `printed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `card_print_queue_card_id_foreign` (`card_id`),
  CONSTRAINT `card_print_queue_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Table structure for table `client_kyc` */


CREATE TABLE `client_kyc` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `client_id` bigint(20) unsigned NOT NULL,
  `id_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_front_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_back_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `selfie_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` enum('pending','approved','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `reviewed_by` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reviewed_at` timestamp NULL DEFAULT NULL,
  `remarks` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `client_kyc_client_id_foreign` (`client_id`),
  CONSTRAINT `client_kyc_client_id_foreign` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `client_kyc` */

insert  into `client_kyc`(`id`,`client_id`,`id_type`,`id_number`,`id_front_path`,`id_back_path`,`selfie_path`,`status`,`reviewed_by`,`reviewed_at`,`remarks`,`created_at`,`updated_at`) values 
(1,1,'national_id','123456889','kyc/d85940ff-60cc-4c4a-8b93-fd1330e7eb35/QUTj70KrYm1bn2dDDnZhwlj64EoHakyNqO3uSKQo.png','kyc/d85940ff-60cc-4c4a-8b93-fd1330e7eb35/LjdqGU4NCvFIhWONzZbepvUVVyqI03udN7j0PY5Q.png','kyc/d85940ff-60cc-4c4a-8b93-fd1330e7eb35/YRKxwDpzjnKTsyiXLsSHTn2BzRLZIuj2kiL702AJ.png','approved','Administrator','2026-03-12 01:11:08',NULL,'2026-03-12 01:11:06','2026-03-12 01:11:08');

/*Table structure for table `clients` */


CREATE TABLE `clients` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` char(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `client_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `first_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `birth_date` date NOT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mobile` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `type` enum('individual','corporate') COLLATE utf8mb4_unicode_ci NOT NULL,
  `full_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `kyc_level` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'basic',
  `status` enum('pending','verified','rejected') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `clients_uuid_unique` (`uuid`),
  UNIQUE KEY `clients_client_code_unique` (`client_code`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `clients` */

insert  into `clients`(`id`,`uuid`,`client_code`,`first_name`,`last_name`,`birth_date`,`email`,`mobile`,`address`,`type`,`full_name`,`kyc_level`,`status`,`created_at`,`updated_at`) values 
(1,'d85940ff-60cc-4c4a-8b93-fd1330e7eb35','CL20260312011044252','Juan','Dela Cruz','1988-01-01','test@email.com','09562365588','This is the example address for now','individual','Juan Dela Cruz','basic','verified','2026-03-12 01:10:44','2026-03-12 01:11:08');

/*Table structure for table `failed_jobs` */


CREATE TABLE `failed_jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uuid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `connection` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `queue` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `exception` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `failed_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `failed_jobs_uuid_unique` (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `hsm_settings` */

/*Table structure for table `institutions` */


CREATE TABLE `institutions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `bin` varchar(12) COLLATE utf8mb4_unicode_ci NOT NULL,
  `card_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`card_settings`)),
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `institutions` */

insert  into `institutions`(`id`,`name`,`bin`,`card_settings`,`status`,`created_at`,`updated_at`) values 
(1,'DK Bank','629856','[]','active','2026-03-12 00:59:29','2026-03-12 00:59:29');

/*Table structure for table `job_batches` */


CREATE TABLE `job_batches` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `total_jobs` int(11) NOT NULL,
  `pending_jobs` int(11) NOT NULL,
  `failed_jobs` int(11) NOT NULL,
  `failed_job_ids` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `options` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cancelled_at` int(11) DEFAULT NULL,
  `created_at` int(11) NOT NULL,
  `finished_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `job_batches` */

/*Table structure for table `jobs` */


CREATE TABLE `jobs` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `queue` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `attempts` tinyint(3) unsigned NOT NULL,
  `reserved_at` int(10) unsigned DEFAULT NULL,
  `available_at` int(10) unsigned NOT NULL,
  `created_at` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `jobs_queue_index` (`queue`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `jobs` */

/*Table structure for table `ledger_entries` */


CREATE TABLE `ledger_entries` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` bigint(20) unsigned NOT NULL,
  `account_id` bigint(20) unsigned NOT NULL,
  `entry_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `balance_after` decimal(15,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `ledger_entries_account_id_index` (`account_id`),
  KEY `ledger_entries_transaction_id_index` (`transaction_id`),
  CONSTRAINT `ledger_entries_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ledger_entries_transaction_id_foreign` FOREIGN KEY (`transaction_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `ledger_entries` */

/*Table structure for table `merchants` */


CREATE TABLE `merchants` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `merchant_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `merchant_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mcc` varchar(4) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `merchants_merchant_id_unique` (`merchant_id`),
  KEY `merchants_merchant_id_index` (`merchant_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `merchants` */

/*Table structure for table `migrations` */


CREATE TABLE `migrations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `migration` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `batch` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `migrations` */

insert  into `migrations`(`id`,`migration`,`batch`) values 
(1,'0001_01_01_000000_create_users_table',1),
(2,'0001_01_01_000001_create_cache_table',1),
(3,'0001_01_01_000002_create_jobs_table',1),
(4,'2026_02_05_012016_create_roles_table',1),
(5,'2026_02_05_012020_add_role_id_to_users_table',1),
(6,'2026_02_09_000001_add_uuid_active_to_users_table',1),
(7,'2026_02_09_000002_create_role_user_table',1),
(8,'2026_02_09_000003_create_clients_table',1),
(9,'2026_02_09_000004_create_accounts_table',1),
(10,'2026_02_09_000005_create_cards_table',1),
(11,'2026_02_09_000006_create_printers_table',1),
(12,'2026_02_09_000007_create_card_print_jobs_table',1),
(13,'2026_02_09_000008_create_audit_logs_table',1),
(14,'2026_02_09_000009_create_client_kyc_table',1),
(15,'2026_02_09_000010_create_card_print_queue_table',1),
(16,'2026_02_09_120000_create_institutions_table',1),
(17,'2026_02_09_120010_create_products_table',1),
(18,'2026_02_09_120020_create_card_batches_table',1),
(19,'2026_02_09_120030_update_cards_for_issuance_types',1),
(20,'2026_02_09_120040_make_card_account_nullable',1),
(21,'2026_02_09_130000_create_pin_mailers_table',1),
(22,'2026_03_10_060247_create_issuer_transaction_system_tables',1),
(23,'2026_03_11_123801_align_card_accounts_and_move_balance_to_cards',1),
(24,'2026_03_11_124346_seed_default_admin_user_and_administrator_role',1),
(25,'2026_03_12_000100_add_active_lifecycle_statuses_to_cards',1),
(26,'2026_03_12_000200_create_product_limits_table',1),
(27,'2026_03_12_000300_add_issuer_fields_to_transactions_table',2),
(28,'2026_03_12_000400_add_direction_and_acquirer_issuer_settings',2),
(29,'2026_03_12_000500_add_hsm_session_columns',3);

/*Table structure for table `printers` */


CREATE TABLE `printers` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `model` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'online',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `printers` */

insert  into `printers`(`id`,`name`,`model`,`status`,`created_at`,`updated_at`) values 
(1,'Primary Printer','EMV-READY-01','online','2026-06-16 05:33:18','2026-06-16 05:33:18');

/*Table structure for table `product_limits` */


CREATE TABLE `product_limits` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` bigint(20) unsigned NOT NULL,
  `txn_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `daily_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `weekly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `monthly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `quarterly_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `semi_annual_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `annual_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `per_transaction_limit` decimal(15,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `product_limits_product_id_txn_type_unique` (`product_id`,`txn_type`),
  KEY `product_limits_product_id_index` (`product_id`),
  CONSTRAINT `product_limits_product_id_foreign` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `product_limits` */

insert  into `product_limits`(`id`,`product_id`,`txn_type`,`daily_limit`,`weekly_limit`,`monthly_limit`,`quarterly_limit`,`semi_annual_limit`,`annual_limit`,`per_transaction_limit`,`created_at`) values 
(1,1,'PURCHASE',50000.00,100000.00,200000.00,400000.00,500000.00,1000000.00,10000.00,'2026-03-12 09:09:38');

/*Table structure for table `products` */


CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `institution_id` bigint(20) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_code` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `product_settings` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`product_settings`)),
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `products_product_code_unique` (`product_code`),
  KEY `products_institution_id_foreign` (`institution_id`),
  CONSTRAINT `products_institution_id_foreign` FOREIGN KEY (`institution_id`) REFERENCES `institutions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `products` */

insert  into `products`(`id`,`institution_id`,`name`,`product_code`,`product_settings`,`status`,`created_at`,`updated_at`) values 
(1,1,'PANTAWID','PRD-PANTAWID','[]','active','2026-03-12 01:00:39','2026-03-12 01:00:39');

/*Table structure for table `reversals` */


CREATE TABLE `reversals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `original_txn_id` bigint(20) unsigned NOT NULL,
  `rrn` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `reversals_rrn_index` (`rrn`),
  KEY `reversals_original_txn_id_index` (`original_txn_id`),
  CONSTRAINT `reversals_original_txn_id_foreign` FOREIGN KEY (`original_txn_id`) REFERENCES `transactions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `reversals` */

/*Table structure for table `role_user` */


CREATE TABLE `role_user` (
  `user_id` bigint(20) unsigned NOT NULL,
  `role_id` bigint(20) unsigned NOT NULL,
  UNIQUE KEY `role_user_user_id_role_id_unique` (`user_id`,`role_id`),
  KEY `role_user_role_id_foreign` (`role_id`),
  CONSTRAINT `role_user_role_id_foreign` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `role_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `role_user` */

insert  into `role_user`(`user_id`,`role_id`) values 
(1,1),
(2,1),
(3,2);

/*Table structure for table `roles` */


CREATE TABLE `roles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `roles_name_unique` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `roles` */

insert  into `roles`(`id`,`name`,`created_at`,`updated_at`) values 
(1,'Administrator','2026-03-12 00:58:32','2026-03-12 00:58:32'),
(2,'SUPER ADMIN','2026-06-16 05:33:18','2026-06-16 05:33:18'),
(3,'BANK ADMIN','2026-06-16 05:33:18','2026-06-16 05:33:18'),
(4,'OPERATIONS OFFICER','2026-06-16 05:33:18','2026-06-16 05:33:18'),
(5,'CARD PERSONALIZATION OFFICER','2026-06-16 05:33:18','2026-06-16 05:33:18'),
(6,'AUDITOR','2026-06-16 05:33:18','2026-06-16 05:33:18');

/*Table structure for table `sessions` */


CREATE TABLE `sessions` (
  `id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `user_id` bigint(20) unsigned DEFAULT NULL,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_agent` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payload` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_activity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `sessions_user_id_index` (`user_id`),
  KEY `sessions_last_activity_index` (`last_activity`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `sessions` */

insert  into `sessions`(`id`,`user_id`,`ip_address`,`user_agent`,`payload`,`last_activity`) values 
('77awnP8ZzhLFaUwVlzGlHn1KO22LpWRx1JEXtI2i',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiNEh2TG1UcGVMTDlMME1wN1Fvd1g5b3U5WW1uSXVFTDF0SDVoVVlHdyI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mzg6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9yZXBvcnRzL2lzc3VhbmNlIjtzOjU6InJvdXRlIjtzOjE2OiJyZXBvcnRzLmlzc3VhbmNlIjt9czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319czo1MDoibG9naW5fd2ViXzU5YmEzNmFkZGMyYjJmOTQwMTU4MGYwMTRjN2Y1OGVhNGUzMDk4OWQiO2k6MTt9',1773279506),
('czscQcMZpq59kv8FowPCWclv2EZ8JZboXm1FnUCe',3,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoickVPaXRhTzQwcUZ3V0REM2ZmNUp2UTk2U1ZHOEFHYUIzSzNpdHdNMiI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mjc6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9sb2dpbiI7czo1OiJyb3V0ZSI7czo1OiJsb2dpbiI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjM7fQ==',1781659137),
('h5gyknajsiKqiI4PrajA6JBLLVEIXpyDVEcxQxzB',NULL,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36','YTozOntzOjY6Il90b2tlbiI7czo0MDoiVkpoSWZtdndVejdKa2RyV1BvNmh4VHgwV1RjaDd6bzY3MVEyU2daZiI7czozOiJ1cmwiO2E6MTp7czo4OiJpbnRlbmRlZCI7czo0MToiaHR0cDovLzEyNy4wLjAuMTo4MDAwL2NhcmRzL3ByZS1nZW5lcmF0ZWQiO31zOjY6Il9mbGFzaCI7YToyOntzOjM6Im9sZCI7YTowOnt9czozOiJuZXciO2E6MDp7fX19',1773320216),
('KpA7EuLNAQfYRGIhff2E6RmakIAp7nmaP7IKxgEC',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiTmZWeHQ1Tm9SQWpVa000eXl5bVR5OHlqVFZaTVp0OFdXZDNXa0Y4MyI7czozOiJ1cmwiO2E6MDp7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjE7fQ==',1773382004),
('whJAXNfP8Ww24xB8bL0P1xRr3O1LYI3rPbC5q8lq',3,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiSzhzUmUwSGxOV0tZZU1GWkFOeXptQWtzRTREQ2pHNm0zcVA3RzRidSI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6MzQ6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9zZXR0aW5ncy9oc20iO3M6NToicm91dGUiO3M6MTI6InNldHRpbmdzLmhzbSI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjM7fQ==',1781666448),
('xUqBSzRXQ893PsuVAcNfuSif7FyHOKaDHKdRaTXa',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/145.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiaEZ5eEl4SDVoQlhrUHlnUm1Kb0pyME5CZnJ5bTdkcTRPTmYzd2NabSI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mjc6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9sb2dpbiI7czo1OiJyb3V0ZSI7czo1OiJsb2dpbiI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjE7fQ==',1773368505),
('YV6FqzxSDy7chBGogbDwLurJMulWXKyvZXThhB25',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiYXZpY05BNVdBRFB6NWh6NWZEWERKdGpCWFpjVWxMcWREMnpuN1VsUiI7czozOiJ1cmwiO2E6MDp7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjE7fQ==',1781485291),
('Yvk2V2pglcXfjix46auTBEs73a2N9ZhdmcUIg6sR',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiZTFJbWVOdG9VUWpIWlJjeTlBNnJ1VTNWSEs0RUl0QklSZ2w1MGVUcSI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mjc6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9sb2dpbiI7czo1OiJyb3V0ZSI7czo1OiJsb2dpbiI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjE7fQ==',1781575675),
('ZgdQLIN8AHrHBqOn1mlPPy9C333L6iH3evVbtrEt',1,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiN2lxbFFCUkdBUFZ3bHd4YWh6cUZieUtFUFRZT1JUNDRidlBoS3BaaSI7czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mjc6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9sb2dpbiI7czo1OiJyb3V0ZSI7czo1OiJsb2dpbiI7fXM6NjoiX2ZsYXNoIjthOjI6e3M6Mzoib2xkIjthOjA6e31zOjM6Im5ldyI7YTowOnt9fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjE7fQ==',1781477599),
('zyFBBS92lKI5l8H32r9O4YSWZw6mcQKMlHHBHz4b',3,'127.0.0.1','Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/149.0.0.0 Safari/537.36','YTo0OntzOjY6Il90b2tlbiI7czo0MDoiTlQ2aDdic2MwV1F1bFp1S3p5RkRScVNiS1lzME1VVUc1WEpIWW5JaiI7czo2OiJfZmxhc2giO2E6Mjp7czozOiJvbGQiO2E6MDp7fXM6MzoibmV3IjthOjA6e319czo5OiJfcHJldmlvdXMiO2E6Mjp7czozOiJ1cmwiO3M6Mjc6Imh0dHA6Ly8xMjcuMC4wLjE6ODAwMC9sb2dpbiI7czo1OiJyb3V0ZSI7czo1OiJsb2dpbiI7fXM6NTA6ImxvZ2luX3dlYl81OWJhMzZhZGRjMmIyZjk0MDE1ODBmMDE0YzdmNThlYTRlMzA5ODlkIjtpOjM7fQ==',1781591201);

/*Table structure for table `settlements` */


CREATE TABLE `settlements` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `settlement_date` date NOT NULL,
  `total_txn` int(10) unsigned NOT NULL DEFAULT 0,
  `total_amount` decimal(15,2) NOT NULL DEFAULT 0.00,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `settlements_settlement_date_index` (`settlement_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `settlements` */

/*Table structure for table `terminals` */


CREATE TABLE `terminals` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `terminal_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `merchant_id` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `terminals_terminal_id_unique` (`terminal_id`),
  KEY `terminals_terminal_id_index` (`terminal_id`),
  KEY `terminals_merchant_id_index` (`merchant_id`),
  CONSTRAINT `terminals_merchant_id_foreign` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`merchant_id`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

/*Data for the table `terminals` */

/*Table structure for table `transactions` */


CREATE TABLE `transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `direction` enum('ISSUER','ACQUIRER') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'ISSUER',
  `rrn` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `stan` varchar(12) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `cardno` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `card_id` bigint(20) unsigned DEFAULT NULL,
  `account_id` bigint(20) unsigned DEFAULT NULL,
  `account_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `txn_type` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `txn_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `entry_mode` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` decimal(15,2) NOT NULL,
  `fee` decimal(15,2) NOT NULL DEFAULT 0.00,
  `service_charge` decimal(15,2) NOT NULL DEFAULT 0.00,
  `convenience_fee` decimal(15,2) NOT NULL DEFAULT 0.00,
  `access_fee` decimal(15,2) NOT NULL DEFAULT 0.00,
  `running_balance` decimal(15,2) DEFAULT NULL,
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'PHP',
  `status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'REJECTED',
  `response_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `acquirer_institution` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `terminal_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `merchant_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `channel` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `device_no` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `dispensed_type` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_ewallet` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `transactions_rrn_index` (`rrn`),
  KEY `transactions_stan_index` (`stan`),
  KEY `transactions_card_id_index` (`card_id`),
  KEY `transactions_account_id_index` (`account_id`),
  KEY `transactions_terminal_id_index` (`terminal_id`),
  KEY `transactions_merchant_id_index` (`merchant_id`),
  KEY `transactions_rrn_stan_index` (`rrn`,`stan`),
  KEY `transactions_cardno_index` (`cardno`),
  KEY `transactions_direction_index` (`direction`),
  CONSTRAINT `transactions_account_id_foreign` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_card_id_foreign` FOREIGN KEY (`card_id`) REFERENCES `cards` (`id`) ON DELETE SET NULL,
  CONSTRAINT `transactions_merchant_id_foreign` FOREIGN KEY (`merchant_id`) REFERENCES `merchants` (`merchant_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `transactions_terminal_id_foreign` FOREIGN KEY (`terminal_id`) REFERENCES `terminals` (`terminal_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

