CREATE TABLE `cashfree_emi_tracking` 
(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `loan_no` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `loan_type` varchar(45) COLLATE utf8mb4_unicode_ci NOT NULL,
  `installment_no` int(11) NOT NULL,
  `subscription_id` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cf_subscription_id` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_group` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_id` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `cf_payment_id` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `emi_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `penalty_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_amount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `due_date` date NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'CREATED',
  `payment_status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `failure_reason` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_error` text COLLATE utf8mb4_unicode_ci,
  `raise_response` text COLLATE utf8mb4_unicode_ci,
  `webhook_response` text COLLATE utf8mb4_unicode_ci,
  `retry_count` int(11) NOT NULL DEFAULT '0',
  `max_retry_count` int(11) NOT NULL DEFAULT '3',
  `last_action` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_received` tinyint(1) NOT NULL DEFAULT '0',
  `emi_approved` tinyint(1) NOT NULL DEFAULT '0',
  `emi_completed` tinyint(1) NOT NULL DEFAULT '0',
  `charge_raised_at` datetime DEFAULT NULL,
  `payment_received_at` datetime DEFAULT NULL,
  `last_retry_at` datetime DEFAULT NULL,
  `payment_completed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_loan_installment` (`loan_no`,`installment_no`),
  UNIQUE KEY `uk_payment_id` (`payment_id`),
  KEY `idx_status` (`status`),
  KEY `idx_due_date` (`due_date`),
  KEY `idx_subscription` (`subscription_id`),
  KEY `idx_cf_payment` (`cf_payment_id`),
  KEY `idx_payment_status` (`payment_status`),
  KEY `idx_recovery` (`status`,`due_date`,`retry_count`),
  KEY `idx_emi_approved` (`emi_approved`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;