-- Firebase mobile push notification device tokens and delivery logs.
-- Import after 14-locker-deposit-ledger.sql.
-- Safe to run when the tables do not already exist.

CREATE TABLE IF NOT EXISTS `client_device_tokens` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `token` TEXT NOT NULL,
    `token_hash` VARCHAR(64) NOT NULL,
    `platform` VARCHAR(20) NOT NULL DEFAULT 'android',
    `device_name` VARCHAR(255) NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_used_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `client_device_tokens_token_hash_unique` (`token_hash`),
    KEY `client_device_tokens_client_id_is_active_index` (`client_id`, `is_active`),
    CONSTRAINT `client_device_tokens_client_id_foreign`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `push_notification_logs` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `client_id` BIGINT UNSIGNED NOT NULL,
    `locker_registration_item_id` BIGINT UNSIGNED NOT NULL,
    `days_before` INT NOT NULL,
    `scheduled_for` DATE NOT NULL,
    `status` VARCHAR(20) NOT NULL,
    `sent_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `failed_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `error` TEXT NULL,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `push_expiry_once_per_stage`
        (`locker_registration_item_id`, `days_before`, `scheduled_for`),
    KEY `push_notification_logs_client_id_foreign` (`client_id`),
    CONSTRAINT `push_notification_logs_client_id_foreign`
        FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE,
    CONSTRAINT `push_notification_logs_item_id_foreign`
        FOREIGN KEY (`locker_registration_item_id`) REFERENCES `locker_registration_items` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Verification:
SHOW TABLES LIKE 'client_device_tokens';
SHOW TABLES LIKE 'push_notification_logs';