-- Phase 3b: Aadhaar Secure QR prefill, UPI consultation fees
ALTER TABLE departments ADD COLUMN fee_paise INT UNSIGNED NULL AFTER room;
ALTER TABLE tenants ADD COLUMN payment_config TEXT NULL AFTER notify_config;
ALTER TABLE registrations
  ADD COLUMN id_source ENUM('manual','aadhaar_qr','abha') NOT NULL DEFAULT 'manual' AFTER source,
  ADD COLUMN id_verified TINYINT(1) NOT NULL DEFAULT 0 AFTER id_source,
  ADD COLUMN pay_status ENUM('na','pending','paid','counter','claimed') NOT NULL DEFAULT 'na' AFTER his_error;
CREATE TABLE IF NOT EXISTS payments (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  registration_id BIGINT UNSIGNED NOT NULL,
  provider        ENUM('razorpay','upi','counter') NOT NULL,
  amount_paise    INT UNSIGNED NOT NULL,
  status          ENUM('pending','paid','claimed','counter','expired','failed') NOT NULL DEFAULT 'pending',
  provider_ref    VARCHAR(80) NULL,
  qr_payload      TEXT NULL,
  payment_ref     VARCHAR(80) NULL,
  method          VARCHAR(30) NULL,
  collected_by    INT UNSIGNED NULL,
  expires_at      DATETIME NULL,
  checked_at      DATETIME NULL,
  paid_at         DATETIME NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_pay_ref (provider, provider_ref),
  KEY ix_pay_reg (registration_id),
  KEY ix_pay_tenant (tenant_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;