-- SecureChat schema. Server stores encrypted blobs only.

CREATE TABLE IF NOT EXISTS users (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone         VARCHAR(20) UNIQUE NOT NULL,
    display_name  VARCHAR(80) DEFAULT NULL,
    public_key    BLOB NOT NULL,               -- user's public key (X25519/Ed25519)
    fcm_token     VARCHAR(300) DEFAULT NULL,   -- for push notifications
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS otps (
    phone         VARCHAR(20) PRIMARY KEY,
    code_hash     CHAR(64) NOT NULL,
    expires_at    TIMESTAMP NOT NULL,
    attempts      INT DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Encrypted message blobs. Server never decrypts these.
CREATE TABLE IF NOT EXISTS messages (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    client_msg_id  CHAR(36) UNIQUE NOT NULL,   -- UUID from sender (idempotent)
    sender_id      BIGINT UNSIGNED NOT NULL,
    recipient_id   BIGINT UNSIGNED NOT NULL,
    ciphertext     LONGBLOB NOT NULL,          -- opaque to server
    msg_type       VARCHAR(20) DEFAULT 'text',
    delivered      TINYINT(1) DEFAULT 0,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX (recipient_id, delivered),
    INDEX (sender_id),
    FOREIGN KEY (sender_id)    REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tombstones for delete-for-all so offline recipients also apply the delete on next fetch.
CREATE TABLE IF NOT EXISTS message_deletes (
    client_msg_id  CHAR(36) PRIMARY KEY,
    deleted_by     BIGINT UNSIGNED NOT NULL,
    deleted_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ws_sessions (
    user_id       BIGINT UNSIGNED PRIMARY KEY,
    conn_id       VARCHAR(64) NOT NULL,
    connected_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;