const tableName = 'billing_ledger'

function buildTable(t) {
  t.string('id', 64).primary()
  t.string('user_id', 64).notNullable()
  t.string('type', 24).notNullable()
  t.bigInteger('amount').notNullable()
  t.string('reason', 96).nullable()
  t.string('source', 32).notNullable()
  t.string('credit_kind', 24).nullable()
  t.string('ref_type', 64).nullable()
  t.string('ref_id', 191).nullable()
  t.text('note').nullable()
  t.string('failure_code', 64).nullable()
  t.bigInteger('balance_after').notNullable().defaultTo(0)
  t.string('idempotency_key', 191).nullable()
  t.datetime('created_at', { precision: 3 }).notNullable()
  t.index(['user_id', 'created_at'], 'idx_billing_ledger_user_time')
  t.index(['ref_type', 'ref_id'], 'idx_billing_ledger_ref')
  t.index(['source', 'created_at', 'id'], 'idx_billing_ledger_source_time')
  t.index(['reason', 'created_at', 'id'], 'idx_billing_ledger_reason_time')
  t.unique(['idempotency_key'], { indexName: 'uq_billing_ledger_idem' })
  t.check("type in ('grant','deduct')", [], 'ck_billing_ledger_type')
  t.check("(type = 'grant' and amount > 0 and credit_kind is not null) or (type = 'deduct' and amount < 0 and credit_kind is null)", [], 'ck_billing_ledger_amount_credit_kind')
  t.check('(ref_type is null and ref_id is null) or (ref_type is not null and ref_id is not null)', [], 'ck_billing_ledger_reference_pair')
  t.check("reason <> 'admin_adjustment' or note is not null", [], 'ck_billing_ledger_admin_note')
  t.check("reason not in ('turn_play_refund','full_run_video_refund') or failure_code is not null", [], 'ck_billing_ledger_refund_failure_code')
  t.check("reason in ('admin_adjustment','turn_play_refund','full_run_video_refund') or (note is null and failure_code is null)", [], 'ck_billing_ledger_phase_one_note_failure')
}

module.exports = { tableName, buildTable }