-- PhpWA durable state for MySQL/MariaDB. The default prefix is `phpwa_`.
-- Applications using another prefix should install through PdoSchema::install().
CREATE TABLE IF NOT EXISTS phpwa_accounts (
    account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    revision BIGINT UNSIGNED NOT NULL DEFAULT 0,
    updated_at BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (account_hash)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS phpwa_state (
    account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    collection VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    state_key_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    state_key VARBINARY(1024) NOT NULL,
    encrypted_value MEDIUMBLOB NOT NULL,
    revision BIGINT UNSIGNED NOT NULL,
    updated_at BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (account_hash, collection, state_key_hash),
    KEY phpwa_state_collection (account_hash, collection),
    CONSTRAINT phpwa_state_account_fk FOREIGN KEY (account_hash) REFERENCES phpwa_accounts(account_hash) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS phpwa_dedupe (
    account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    message_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    received_at BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (account_hash, message_hash),
    KEY phpwa_dedupe_age (account_hash, received_at),
    CONSTRAINT phpwa_dedupe_account_fk FOREIGN KEY (account_hash) REFERENCES phpwa_accounts(account_hash) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS phpwa_outbox (
    event_id CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    event_type VARCHAR(96) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    encrypted_payload MEDIUMBLOB NOT NULL,
    occurred_at BIGINT UNSIGNED NOT NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 0,
    available_at BIGINT UNSIGNED NOT NULL,
    lease_owner VARCHAR(128) CHARACTER SET ascii COLLATE ascii_bin NULL,
    lease_until BIGINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (event_id),
    KEY phpwa_outbox_claim (account_hash, available_at, lease_until),
    CONSTRAINT phpwa_outbox_account_fk FOREIGN KEY (account_hash) REFERENCES phpwa_accounts(account_hash) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;

CREATE TABLE IF NOT EXISTS phpwa_retries (
    account_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    message_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
    attempts INT UNSIGNED NOT NULL,
    next_allowed_at BIGINT UNSIGNED NOT NULL,
    updated_at BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (account_hash, message_hash),
    KEY phpwa_retries_age (account_hash, updated_at),
    CONSTRAINT phpwa_retries_account_fk FOREIGN KEY (account_hash) REFERENCES phpwa_accounts(account_hash) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
