-- Self-Service Now Milestone 3.1: native order push notifications.
-- Safe to run once from phpMyAdmin against kiosk_db.

CREATE TABLE IF NOT EXISTS mobile_installations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    public_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    secret_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    platform ENUM('ios') NOT NULL DEFAULT 'ios',
    loyalty_member_id INT DEFAULT NULL,
    app_version VARCHAR(32) DEFAULT NULL,
    last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    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_installation_public_id (public_id),
    KEY idx_mobile_installation_member (loyalty_member_id),
    KEY idx_mobile_installation_last_seen (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_push_tokens (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    installation_id BIGINT UNSIGNED NOT NULL,
    environment ENUM('sandbox','production') NOT NULL,
    device_token VARCHAR(200) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    last_registered_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    invalidated_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_push_installation_environment (installation_id, environment),
    UNIQUE KEY uq_mobile_push_environment_token (environment, token_hash),
    KEY idx_mobile_push_active (is_active, environment),
    CONSTRAINT fk_mobile_push_installation
        FOREIGN KEY (installation_id) REFERENCES mobile_installations (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_payment_installations (
    payment_request_id BIGINT UNSIGNED NOT NULL,
    installation_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (payment_request_id),
    KEY idx_mobile_payment_installation (installation_id),
    CONSTRAINT fk_mobile_payment_installation_payment
        FOREIGN KEY (payment_request_id) REFERENCES hitpay_payment_requests (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_mobile_payment_installation_device
        FOREIGN KEY (installation_id) REFERENCES mobile_installations (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS kitchen_order_subscribers (
    kitchen_order_id BIGINT UNSIGNED NOT NULL,
    installation_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (kitchen_order_id, installation_id),
    KEY idx_kitchen_subscriber_installation (installation_id),
    CONSTRAINT fk_kitchen_subscriber_order
        FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_kitchen_subscriber_installation
        FOREIGN KEY (installation_id) REFERENCES mobile_installations (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS mobile_notification_outbox (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    kitchen_order_id BIGINT UNSIGNED NOT NULL,
    installation_id BIGINT UNSIGNED NOT NULL,
    event_type ENUM('preparing','ready','cancelled') NOT NULL,
    status ENUM('pending','processing','retry','delivered','failed') NOT NULL DEFAULT 'pending',
    attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
    available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    locked_at DATETIME DEFAULT NULL,
    apns_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin DEFAULT NULL,
    last_http_status SMALLINT UNSIGNED DEFAULT NULL,
    last_error VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME DEFAULT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_mobile_notification_event (kitchen_order_id, installation_id, event_type),
    KEY idx_mobile_notification_dispatch (status, available_at, id),
    CONSTRAINT fk_mobile_notification_order
        FOREIGN KEY (kitchen_order_id) REFERENCES kitchen_orders (id)
        ON DELETE CASCADE,
    CONSTRAINT fk_mobile_notification_installation
        FOREIGN KEY (installation_id) REFERENCES mobile_installations (id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

