-- ============================================================
-- uti_pan — final consolidated schema
-- Covers: New PAN applications + PAN Correction/Update (CSF)
-- Both share this one table, distinguished by `pan_type`.
-- ============================================================

-- ============================================================
-- MIGRATION — if `uti_pan` already exists from an earlier stage of this build,
-- run whichever of these you haven't already applied (skip ones that error
-- with "duplicate column").
-- ============================================================
-- ALTER TABLE `uti_pan` ADD COLUMN `external_order_id` VARCHAR(50) DEFAULT NULL AFTER `application_no`;
-- ALTER TABLE `uti_pan` CHANGE COLUMN `pan_type` `applicant_type` VARCHAR(10) DEFAULT 'Normal';
-- ALTER TABLE `uti_pan` ADD COLUMN `pan_type` VARCHAR(10) NOT NULL DEFAULT 'new_pan' AFTER `external_order_id`;
-- ALTER TABLE `uti_pan` ADD COLUMN `pan_number` VARCHAR(10) DEFAULT NULL AFTER `applicant_type`;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_full_name` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_father_name` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_dob` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_gender` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_address` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_photo` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `correction_signature` VARCHAR(1) DEFAULT NULL;
-- ALTER TABLE `uti_pan` ADD COLUMN `doc4_path` VARCHAR(255) DEFAULT NULL AFTER `doc3_path`;
-- ALTER TABLE `uti_pan` MODIFY COLUMN `status` VARCHAR(20) NOT NULL DEFAULT 'process';
-- ALTER TABLE `uti_pan` ADD COLUMN `remark` VARCHAR(255) DEFAULT NULL AFTER `status`;
-- ALTER TABLE `uti_pan` ADD COLUMN `ack_no` VARCHAR(50) DEFAULT NULL AFTER `remark`;
-- ALTER TABLE `uti_pan` ADD COLUMN `is_refunded` TINYINT(1) NOT NULL DEFAULT 0 AFTER `ack_no`;
-- ALTER TABLE `uti_pan` ADD COLUMN `refund_txn_id` VARCHAR(100) DEFAULT NULL AFTER `is_refunded`;
-- ALTER TABLE `uti_pan` ADD COLUMN `refund_date` DATE DEFAULT NULL AFTER `refund_txn_id`;
-- ALTER TABLE `uti_pan` ADD COLUMN `last_checked_at` DATETIME DEFAULT NULL AFTER `refund_date`;
-- ALTER TABLE `uti_pan` DROP COLUMN `gender`; -- if you ran the short-lived gender-column version
-- UPDATE `uti_pan` SET `status` = 'process' WHERE `status` = '1'; -- backfill old boolean status
-- UPDATE `uti_pan` SET `pan_type` = 'new_pan' WHERE `pan_type` IS NULL OR `pan_type` = '';

CREATE TABLE IF NOT EXISTS `uti_pan` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  `application_no` VARCHAR(100) NOT NULL COMMENT 'our own locally-generated order id',
  `external_order_id` VARCHAR(50) DEFAULT NULL COMMENT 'order_id returned by the external submit API - used for status-check calls, NOT application_no',
  `username` VARCHAR(20) NOT NULL,

  `pan_type` VARCHAR(10) NOT NULL DEFAULT 'new_pan' COMMENT 'new_pan or csf_pan - which service/form this row came from',
  `pan_number` VARCHAR(10) DEFAULT NULL COMMENT 'existing PAN being corrected - only for pan_type=csf_pan',

  `title` VARCHAR(5) DEFAULT NULL,
  `name_card` VARCHAR(150) DEFAULT NULL,
  `dob` VARCHAR(15) DEFAULT NULL,
  `single_parent` VARCHAR(1) DEFAULT 'n',
  `father_name` VARCHAR(150) DEFAULT NULL,
  `mother_name` VARCHAR(150) DEFAULT NULL,
  `parent_name_on_card` VARCHAR(1) DEFAULT 'f',

  `applicant_type` VARCHAR(10) DEFAULT 'Normal' COMMENT 'Normal or Minor - only meaningful for pan_type=new_pan',
  `parent_aadhaar_no` VARCHAR(15) DEFAULT NULL COMMENT 'only set when applicant_type=Minor',

  -- CSF (correction) specific: which fields the applicant is asking to change, Y/NULL
  `correction_full_name` VARCHAR(1) DEFAULT NULL,
  `correction_father_name` VARCHAR(1) DEFAULT NULL,
  `correction_dob` VARCHAR(1) DEFAULT NULL,
  `correction_gender` VARCHAR(1) DEFAULT NULL,
  `correction_address` VARCHAR(1) DEFAULT NULL,
  `correction_photo` VARCHAR(1) DEFAULT NULL,
  `correction_signature` VARCHAR(1) DEFAULT NULL,

  `aadhaar_num` VARCHAR(15) NOT NULL,
  `mobile_no` VARCHAR(15) NOT NULL,
  `email_id` VARCHAR(150) DEFAULT NULL,

  `address1` VARCHAR(30) DEFAULT NULL,
  `address2` VARCHAR(30) DEFAULT NULL,
  `address3` VARCHAR(30) DEFAULT NULL,
  `address4` VARCHAR(30) DEFAULT NULL,
  `address5` VARCHAR(100) DEFAULT NULL COMMENT 'Town/City/District',
  `pincode` VARCHAR(10) DEFAULT NULL,
  `user_state` VARCHAR(10) DEFAULT NULL COMMENT 'state code, only set if pincode lookup failed',

  `photo_path` VARCHAR(255) DEFAULT NULL,
  `sign_path` VARCHAR(255) DEFAULT NULL,
  `doc1_path` VARCHAR(255) DEFAULT NULL COMMENT 'Aadhaar JPG',
  `doc2_path` VARCHAR(255) DEFAULT NULL COMMENT 'DOB Proof JPG (new_pan) or PAN JPG (csf_pan)',
  `doc3_path` VARCHAR(255) DEFAULT NULL COMMENT 'Parent Aadhaar JPG (new_pan/Minor) or Document 1 (csf_pan)',
  `doc4_path` VARCHAR(255) DEFAULT NULL COMMENT 'Document 2, only for pan_type=csf_pan',

  `status` VARCHAR(20) NOT NULL DEFAULT 'process' COMMENT 'process = submitted/pending, success = PAN issued, rejected = rejected by UTI',
  `remark` VARCHAR(255) DEFAULT NULL COMMENT 'latest remark from status-check API',
  `ack_no` VARCHAR(50) DEFAULT NULL COMMENT 'UTI acknowledgement number, only set once status=success',
  `is_refunded` TINYINT(1) NOT NULL DEFAULT 0,
  `refund_txn_id` VARCHAR(100) DEFAULT NULL,
  `refund_date` DATE DEFAULT NULL,
  `last_checked_at` DATETIME DEFAULT NULL COMMENT 'when the cron last polled the status-check API for this order',

  `status_code` TEXT COMMENT 'raw API response from the submit call',
  `fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'actual amount charged',
  `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_external_order_id` (`external_order_id`),
  KEY `idx_aadhaar_num` (`aadhaar_num`),
  KEY `idx_status` (`status`),
  KEY `idx_pan_type` (`pan_type`),
  KEY `idx_date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pricing entries (adjust prices as needed)
INSERT INTO `pricing` (`service_name`, `price`) VALUES ('uti_new_pan', 120.00)
ON DUPLICATE KEY UPDATE price = price;

INSERT INTO `pricing` (`service_name`, `price`) VALUES ('uti_csf_pan', 120.00)
ON DUPLICATE KEY UPDATE price = price;