--------------------------------------- users ---------------------------------------
CREATE TABLE users (
    id BIGSERIAL NOT NULL PRIMARY KEY,
    login_name varchar(64) NOT NULL UNIQUE,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20) UNIQUE,
    password_hash VARCHAR(255),
    totp_secret VARCHAR(40),
    enable_2fa boolean NOT NULL,
    status SMALLINT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT users_email_phone_pwd_check CHECK (
        email IS NOT NULL
        OR phone IS NOT NULL
        OR password_hash IS NOT NULL
    )
);

COMMENT ON TABLE users IS 'Table to manage user information';

COMMENT ON COLUMN users.id IS 'User ID, auto-increment primary key';

COMMENT ON COLUMN users.login_name IS 'The login username, must be unique';

COMMENT ON COLUMN users.email IS 'User email, must be unique, can be null';

COMMENT ON COLUMN users.phone IS 'User phone, must be unique, can be null';

COMMENT ON COLUMN users.password_hash IS 'The secret for the TOTP, can be null';

COMMENT ON COLUMN users.totp_secret IS 'The password for the user, can be null';

COMMENT ON COLUMN users.enable_2fa IS 'When enable_2fa is enabled, use TOTP to perform double verification for password login';

COMMENT ON COLUMN users.status IS 'User status';

COMMENT ON COLUMN users.created_at IS 'The date when the user was created';