-- Phase 2: ABHA Scan & Share, WhatsApp notifications, OTP
ALTER TABLE tenants
  ADD COLUMN abdm_hip_id VARCHAR(60) NULL AFTER his_config,
  ADD COLUMN notify_config TEXT NULL AFTER abdm_hip_id,
  ADD UNIQUE KEY uq_tenant_hip (abdm_hip_id);

ALTER TABLE kiosk_devices
  ADD COLUMN abdm_counter_id VARCHAR(60) NULL AFTER location,
  ADD COLUMN abdm_qr TEXT NULL AFTER abdm_counter_id;

ALTER TABLE registrations
  MODIFY mobile_enc TEXT NULL,
  MODIFY mobile_hash CHAR(64) NULL,
  MODIFY mobile_last4 CHAR(4) NULL,
  ADD COLUMN source ENUM('kiosk','abdm') NOT NULL DEFAULT 'kiosk' AFTER device_id,
  ADD COLUMN abha_number_enc TEXT NULL AFTER pincode,
  ADD COLUMN abha_address_enc TEXT NULL AFTER abha_number_enc,
  ADD COLUMN abha_hash CHAR(64) NULL AFTER abha_address_enc,
  ADD COLUMN abdm_counter_id VARCHAR(60) NULL AFTER abha_hash,
  ADD COLUMN claimed_device_id INT UNSIGNED NULL AFTER abdm_counter_id,
  ADD COLUMN claimed_at DATETIME NULL AFTER claimed_device_id,
  ADD COLUMN called_at DATETIME NULL AFTER handled_at,
  ADD KEY ix_reg_abha (tenant_id, abha_hash),
  ADD KEY ix_reg_counter (tenant_id, source, abdm_counter_id, claimed_at);

ALTER TABLE consent_records
  MODIFY channel ENUM('kiosk','web','desk','abdm') NOT NULL DEFAULT 'kiosk';

CREATE TABLE IF NOT EXISTS abdm_requests (
  request_id      VARCHAR(80) NOT NULL PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  registration_id BIGINT UNSIGNED NOT NULL,
  token_no        VARCHAR(16) NOT NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS notifications (
  id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id       INT UNSIGNED NOT NULL,
  registration_id BIGINT UNSIGNED NULL,
  kind            ENUM('token','called','otp') NOT NULL,
  to_enc          TEXT NOT NULL,
  params          TEXT NOT NULL,
  lang            VARCHAR(5) NOT NULL DEFAULT 'en',
  status          ENUM('queued','sent','failed','skipped') NOT NULL DEFAULT 'queued',
  attempts        TINYINT UNSIGNED NOT NULL DEFAULT 0,
  provider_ref    VARCHAR(120) NULL,
  error           VARCHAR(255) NULL,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  sent_at         DATETIME NULL,
  KEY ix_notify_status (status, created_at),
  KEY ix_notify_reg (registration_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS otp_codes (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  tenant_id   INT UNSIGNED NOT NULL,
  mobile_hash CHAR(64) NOT NULL,
  code_hash   CHAR(64) NOT NULL,
  attempts    TINYINT UNSIGNED NOT NULL DEFAULT 0,
  device_id   INT UNSIGNED NULL,
  expires_at  DATETIME NOT NULL,
  used_at     DATETIME NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY ix_otp (tenant_id, mobile_hash, created_at)
) ENGINE=InnoDB;