-- Table to store IFSC Details lookup requests
CREATE TABLE IF NOT EXISTS `ifsc_details` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `application_no` VARCHAR(100) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  `ifsc` VARCHAR(11) NOT NULL,
  `bank` VARCHAR(150) DEFAULT NULL,
  `bankcode` VARCHAR(20) DEFAULT NULL,
  `branch` VARCHAR(200) DEFAULT NULL,
  `centre` VARCHAR(100) DEFAULT NULL,
  `district` VARCHAR(100) DEFAULT NULL,
  `state` VARCHAR(100) DEFAULT NULL,
  `city` VARCHAR(100) DEFAULT NULL,
  `address` VARCHAR(255) DEFAULT NULL,
  `contact` VARCHAR(50) DEFAULT NULL,
  `imps` TINYINT(1) DEFAULT 0,
  `upi` TINYINT(1) DEFAULT 0,
  `neft` TINYINT(1) DEFAULT 0,
  `rtgs` TINYINT(1) DEFAULT 0,
  `micr` VARCHAR(20) DEFAULT NULL,
  `swift` VARCHAR(20) DEFAULT NULL,
  `iso3166` VARCHAR(10) DEFAULT NULL,
  `data_mode` VARCHAR(20) DEFAULT NULL,
  `source` VARCHAR(20) DEFAULT NULL,
  `billable` VARCHAR(1) DEFAULT 'Y' COMMENT 'Y = charged, N = free (cached hit)',
  `status` TINYINT(1) NOT NULL DEFAULT 0,
  `status_code` TEXT,
  `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'actual amount charged (0 if billable=N)',
  `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_ifsc` (`ifsc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pricing: rate = 2 as specified (this is the MAX charge; actual charge waived when billable=N)
INSERT INTO `pricing` (`service_name`, `price`) VALUES ('IFSC_Details', 2.00)
ON DUPLICATE KEY UPDATE price = 2.00;