-- Table to store UP Ration to UID (Aadhaar) requests
CREATE TABLE IF NOT EXISTS `upration2uid` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `application_no` VARCHAR(100) NOT NULL,
  `username` VARCHAR(20) NOT NULL,
  `ration_no` VARCHAR(20) NOT NULL,
  `member_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `family_data` LONGTEXT COMMENT 'raw JSON array of family members from API',
  `data_mode` VARCHAR(20) DEFAULT NULL,
  `source` 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_ration_no` (`ration_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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