-- Table to store NPCI/DBT Link Status check requests
CREATE TABLE IF NOT EXISTS `npci_status` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `application_no` VARCHAR(100) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  `aadhaar` VARCHAR(15) NOT NULL,
  `bank_name` VARCHAR(200) DEFAULT NULL,
  `npci_link_status` VARCHAR(5) DEFAULT NULL COMMENT 'A = Active, others = inactive/error codes',
  `last_updated_date` VARCHAR(30) DEFAULT NULL,
  `uid` VARCHAR(15) DEFAULT NULL,
  `name` VARCHAR(150) DEFAULT NULL,
  `local_name` VARCHAR(150) DEFAULT NULL,
  `mobile_masked` VARCHAR(20) DEFAULT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 0,
  `status_code` TEXT,
  `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `old_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `new_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `message` VARCHAR(255) DEFAULT NULL,
  `date` DATETIME NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_username` (`username`),
  KEY `idx_application_no` (`application_no`),
  KEY `idx_aadhaar` (`aadhaar`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add pricing entry (adjust price as needed)
INSERT INTO `pricing` (`service_name`, `price`) VALUES ('npci_status', 10.00)
ON DUPLICATE KEY UPDATE price = price;