-- Milestone 4D.0: optional member email, consent metadata, and verification.
-- Apply once after 20260725_mobile_member_auth.sql.

USE `kiosk_db`;

UPDATE `loyalty_members`
SET `email` = NULL
WHERE `email` IS NOT NULL
  AND TRIM(`email`) = '';

UPDATE `loyalty_members`
SET `email` = LOWER(TRIM(`email`))
WHERE `email` IS NOT NULL;

ALTER TABLE `loyalty_members`
    ADD COLUMN `email_verified_at` datetime NULL AFTER `email`,
    ADD COLUMN `email_updated_at` datetime NULL AFTER `email_verified_at`,
    ADD COLUMN `terms_accepted_at` datetime NULL AFTER `email_updated_at`,
    ADD COLUMN `terms_version` varchar(32) NULL AFTER `terms_accepted_at`,
    ADD COLUMN `privacy_version` varchar(32) NULL AFTER `terms_version`,
    ADD COLUMN `marketing_email_consent` tinyint(1) NOT NULL DEFAULT 0
        AFTER `privacy_version`,
    ADD COLUMN `marketing_email_consent_updated_at` datetime NULL
        AFTER `marketing_email_consent`,
    ADD UNIQUE KEY `uq_loyalty_members_email` (`email`);

CREATE TABLE `mobile_member_email_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,
    `loyalty_member_id` int NOT NULL,
    `email` varchar(255) NOT NULL,
    `purpose` varchar(32)
        CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'verify_email',
    `code_hash` char(64)
        CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    `status` enum(
        'sending',
        'pending',
        'delivery_failed',
        'verified',
        'expired',
        'locked',
        'superseded'
    ) 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 NULL,
    `sent_at` datetime NULL,
    `expires_at` datetime NOT NULL,
    `verified_at` datetime NULL,
    `consumed_at` datetime 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_email_public_id` (`public_id`),
    KEY `idx_mobile_member_email_address_rate` (`email`, `created_at`),
    KEY `idx_mobile_member_email_member_rate`
        (`loyalty_member_id`, `created_at`),
    KEY `idx_mobile_member_email_installation_rate`
        (`installation_id`, `created_at`),
    KEY `idx_mobile_member_email_pending` (`status`, `expires_at`),
    CONSTRAINT `fk_mobile_member_email_installation`
        FOREIGN KEY (`installation_id`)
        REFERENCES `mobile_installations` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_mobile_member_email_member`
        FOREIGN KEY (`loyalty_member_id`)
        REFERENCES `loyalty_members` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

