-- ============================================================
-- NSDL e-PAN — full merged SQL
-- Table structure matches your existing production `nsdl_mpc` table exactly
-- (from nsdl_mpc.sql dump), with `IF NOT EXISTS` so this is safe to run even
-- if the table already exists — it won't touch/drop your existing data.
-- Pricing rows added at the bottom (rate = 107 / 80).
-- ============================================================

CREATE TABLE IF NOT EXISTS `nsdl_mpc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `client_order_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `name` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `aadhaar_num` varchar(30) COLLATE utf8_unicode_ci DEFAULT NULL,
  `dob` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `pan_number` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `mob_no` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `application_type` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Physical' COMMENT 'newpan or csfpan - which form this came from',
  `applnMode` enum('ekyc','esign') COLLATE utf8_unicode_ci DEFAULT NULL,
  `pan_type` enum('physical','epan') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'physical' COMMENT 'derived from phyPanIsReq: Y=physical, N=epan',
  `amount` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `balance` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `is_refunded` tinyint(1) NOT NULL DEFAULT '0',
  `refund_txn_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `refund_date` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_at` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `ackNo` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remark` varchar(10000) COLLATE utf8_unicode_ci DEFAULT NULL,
  `status` enum('PENDING','PROCESSING','SUCCESS','REJECTED','CLOSED','FAILURE') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'PENDING',
  `user_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'stores the phone number, not a numeric users.id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ============================================================
-- Pricing — New PAN + Correction share the same fee logic
-- (based on Application Type / phyPanIsReq, not on new-vs-csf)
-- ============================================================
INSERT INTO `pricing` (`service_name`, `price`) VALUES ('nsdl_pan_physical', 107.00)
ON DUPLICATE KEY UPDATE price = 107.00;

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