const tableName = 'vk_tasks'

function buildTable(t) {
  t.string('id', 64).primary()
  t.string('guild_id', 64).notNullable()
  t.string('publisher_agent_id', 64).notNullable()
  t.string('template', 16).notNullable()
  t.string('title', 200).notNullable()
  t.text('spec').notNullable()
  t.bigInteger('reward').notNullable()
  t.tinyint('rarity').unsigned().notNullable()
  t.datetime('deadline_at', { precision: 3 }).notNullable()
  t.string('status', 24).notNullable()
  t.string('client_request_key', 191).notNullable()
  t.string('holder_agent_id', 64).nullable()
  t.datetime('next_checkpoint_due_at', { precision: 3 }).nullable()
  t.tinyint('missed_checkpoints').unsigned().notNullable()
  t.tinyint('rework_count').unsigned().notNullable()
  t.datetime('review_due_at', { precision: 3 }).nullable()
  t.datetime('settled_at', { precision: 3 }).nullable()
  t.datetime('created_at', { precision: 3 }).notNullable()
  t.datetime('updated_at', { precision: 3 }).nullable()
  t.unique(['publisher_agent_id', 'client_request_key'], { indexName: 'uq_vk_tasks_publisher_request' })
  t.index(['guild_id', 'status', 'deadline_at'], 'idx_vk_tasks_board')
  t.index(['publisher_agent_id', 'status'], 'idx_vk_tasks_publisher_status')
  t.index(['holder_agent_id', 'status'], 'idx_vk_tasks_holder_status')
  t.check("template in ('piecework','contest')", [], 'ck_vk_tasks_template')
  t.check("status in ('pending_approval','open','claimed','in_contest','submitted','reviewing','rework','disputed','settled','cancelled','refunded')", [], 'ck_vk_tasks_status')
  t.check('reward > 0', [], 'ck_vk_tasks_reward')
  t.check('rarity between 1 and 10', [], 'ck_vk_tasks_rarity')
  t.check('rework_count <= 2', [], 'ck_vk_tasks_rework_count')
  t.check("status not in ('claimed','submitted','rework') or template = 'piecework'", [], 'ck_vk_tasks_piecework_status')
  t.check("status not in ('in_contest','reviewing') or template = 'contest'", [], 'ck_vk_tasks_contest_status')
  t.check("template <> 'contest' or holder_agent_id is null", [], 'ck_vk_tasks_contest_holder')
  t.check("status <> 'claimed' or (holder_agent_id is not null and next_checkpoint_due_at is not null)", [], 'ck_vk_tasks_claimed')
  t.check("status not in ('submitted','reviewing') or review_due_at is not null", [], 'ck_vk_tasks_review_due')
  t.check("status <> 'settled' or settled_at is not null", [], 'ck_vk_tasks_settled')
}

module.exports = { tableName, buildTable }