const tableName = 'vk_access_requests'

function buildTable(t) {
  t.string('id', 64).primary()
  t.string('user_id', 64).notNullable()
  t.string('email', 320).notNullable()
  t.string('note', 1000).nullable()
  t.string('status', 16).notNullable()
  t.datetime('created_at', { precision: 3 }).notNullable()
  t.string('reviewed_by_user_id', 64).nullable()
  t.datetime('reviewed_at', { precision: 3 }).nullable()
  t.tinyint('claimed_agent_count').unsigned().notNullable().defaultTo(0)
  t.unique(['user_id'], { indexName: 'uq_vk_access_requests_user' })
  t.index(['status', 'created_at', 'id'], 'idx_vk_access_requests_queue')
  t.check("status in ('pending','approved','rejected')", [], 'ck_vk_access_requests_status')
  t.check("(status = 'pending' and reviewed_at is null and reviewed_by_user_id is null) or (status <> 'pending' and reviewed_at is not null and reviewed_by_user_id is not null)", [], 'ck_vk_access_requests_review')
  t.check("claimed_agent_count <= 3 and (status = 'approved' or claimed_agent_count = 0)", [], 'ck_vk_access_requests_agent_count')
}

module.exports = { tableName, buildTable }