-- Self-Service Now Milestone 4A: mobile member OTP authentication.
-- Target: MySQL 8 / kiosk_db.
-- Run once from phpMyAdmin after backing up kiosk_db.
--
-- This migration does not award points, redeem points, alter payments, or
-- attach historical guest orders to a member.

USE `kiosk_db`;

ALTER TABLE `mobile_installations`
  ADD CONSTRAINT `fk_mobile_installation_loyalty_member`
    FOREIGN KEY (`loyalty_member_id`)
    REFERENCES `loyalty_members` (`id`)
    ON DELETE SET NULL
    ON UPDATE CASCADE;

CREATE TABLE `mobile_member_otp_challenges` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `public_id` char(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `installation_id` bigint UNSIGNED NOT NULL,
  `phone_e164` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `purpose` varchar(32) CHARACTER SET ascii COLLATE ascii_bin
    NOT NULL DEFAULT 'sign_in',
  `code_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `status` enum(
    'sending',
    'pending',
    'delivery_failed',
    'verified',
    'expired',
    'locked',
    'superseded'
  ) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'sending',
  `attempt_count` tinyint UNSIGNED NOT NULL DEFAULT 0,
  `max_attempts` tinyint UNSIGNED NOT NULL DEFAULT 5,
  `request_ip_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
  `sent_at` datetime DEFAULT NULL,
  `expires_at` datetime NOT NULL,
  `verified_at` datetime DEFAULT NULL,
  `consumed_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_mobile_member_otp_public_id` (`public_id`),
  KEY `idx_mobile_member_otp_phone_rate` (`phone_e164`, `created_at`),
  KEY `idx_mobile_member_otp_installation_rate`
    (`installation_id`, `created_at`),
  KEY `idx_mobile_member_otp_pending` (`status`, `expires_at`),
  CONSTRAINT `fk_mobile_member_otp_installation`
    FOREIGN KEY (`installation_id`)
    REFERENCES `mobile_installations` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `chk_mobile_member_otp_public_id`
    CHECK (
      `public_id` REGEXP
      '^[0-9a-f]{8}-[0-9a-f]{4}-4[0-9a-f]{3}-[89ab][0-9a-f]{3}-[0-9a-f]{12}$'
    ),
  CONSTRAINT `chk_mobile_member_otp_code_hash`
    CHECK (`code_hash` REGEXP '^[0-9a-f]{64}$'),
  CONSTRAINT `chk_mobile_member_otp_ip_hash`
    CHECK (
      `request_ip_hash` IS NULL
      OR `request_ip_hash` REGEXP '^[0-9a-f]{64}$'
    ),
  CONSTRAINT `chk_mobile_member_otp_attempts`
    CHECK (`attempt_count` <= `max_attempts`),
  CONSTRAINT `chk_mobile_member_otp_expiry`
    CHECK (`expires_at` > `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `mobile_member_sessions` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `installation_id` bigint UNSIGNED NOT NULL,
  `loyalty_member_id` int NOT NULL,
  `token_hash` char(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `last_seen_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` datetime NOT NULL,
  `revoked_at` datetime DEFAULT NULL,
  `revoke_reason` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_mobile_member_session_token_hash` (`token_hash`),
  KEY `idx_mobile_member_session_installation`
    (`installation_id`, `revoked_at`, `expires_at`),
  KEY `idx_mobile_member_session_member`
    (`loyalty_member_id`, `revoked_at`, `expires_at`),
  KEY `idx_mobile_member_session_expiry` (`expires_at`),
  CONSTRAINT `fk_mobile_member_session_installation`
    FOREIGN KEY (`installation_id`)
    REFERENCES `mobile_installations` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_mobile_member_session_member`
    FOREIGN KEY (`loyalty_member_id`)
    REFERENCES `loyalty_members` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `chk_mobile_member_session_token_hash`
    CHECK (`token_hash` REGEXP '^[0-9a-f]{64}$'),
  CONSTRAINT `chk_mobile_member_session_expiry`
    CHECK (`expires_at` > `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

