tables.sql

CREATE TABLE `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `phone` VARCHAR(20) UNIQUE,
    `telegram_id` VARCHAR(64) UNIQUE,
    `firstname` VARCHAR(59) NOT NULL,
    `lastname` VARCHAR(59),
    `description` VARCHAR(400),
    `avatar_id` VARCHAR(16),
    `updatetime` VARCHAR(24),
    `lastseen` VARCHAR(24),
    `profileoptions` JSON NOT NULL,
    `options` JSON NOT NULL,
    `accountstatus` VARCHAR(16) NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `username` VARCHAR(60) UNIQUE
);

CREATE TABLE `tokens` (
    `phone` VARCHAR(20) NOT NULL,
    `token_hash` VARCHAR(64) NOT NULL,
    `device_type` VARCHAR(256) NOT NULL,
    `device_name` VARCHAR(256) NOT NULL,
    `location` VARCHAR(256) NOT NULL,
    `time` VARCHAR(16) NOT NULL
);

CREATE TABLE `auth_tokens` (
    `phone` VARCHAR(20) NOT NULL,
    `token_hash` VARCHAR(64) NOT NULL,
    `code_hash` VARCHAR(64) NOT NULL,
    `expires` VARCHAR(16) NOT NULL,
    `state` VARCHAR(16)
);

CREATE TABLE `user_data` (
    `phone` VARCHAR(20) NOT NULL UNIQUE,
    `chats` JSON NOT NULL,
    `contacts` JSON NOT NULL,
    `folders` JSON NOT NULL,
    `user_config` JSON NOT NULL,
    `chat_config` JSON NOT NULL
);

CREATE TABLE `chats` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `owner` INT NOT NULL,
    `type` VARCHAR(16) NOT NULL,
    `participants` JSON NOT NULL
);

CREATE TABLE `messages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `chat_id` INT NOT NULL,
    `sender` INT NOT NULL,
    `time` VARCHAR(32) NOT NULL,
    `text` VARCHAR(4000) NOT NULL,
    `attaches` JSON NOT NULL,
    `cid` VARCHAR(32) NOT NULL,
    `elements` JSON NOT NULL,
    `type` VARCHAR(16) NOT NULL
);