return async (db: Knex) => {
   await rollbackMarketState(db, log.market, ReportingState.AWAITING_NEXT_WINDOW);
   await db.update({
     universe: log.originalUniverse,
     needsMigration: db.raw("needsMigration + 1"),
     needsDisavowal: db.raw("needsDisavowal + 1"),
   }).into("markets").where("marketId", log.market);
   return db.update({
     disavowed: db.raw("disavowed - 1"),
   }).into("crowdsourcers").where("marketId", log.market);
 };

export async function getAccountTimeRangedStats(db: Knex, augur: Augur, params: AccountTimeRangedStatsParamsType): Promise<AccountTimeRangeResult> {
  // guards
  if (params.startTime && params.endTime && params.startTime > params.endTime)
    throw new Error("startTime must be less than or equal to endTime");
  if (!params.startTime) params.startTime = 0;
  if (!params.endTime) params.endTime = 0;

  // collates stats from several joins unrelated except by blockNumbers
  const sql = db.select(["startBlock", "endBlock"])
    .countDistinct("markets.marketId as marketsCreated")
    .countDistinct("trades.transactionhash as numberOfTrades")
    .countDistinct("trades.marketId as marketsTraded")
    .countDistinct("proceeds.marketId as redeemedPositions")
    .from(db.raw(blockNumberForTimestampQuery(db, params.startTime || 0, "start").toString()).wrap("(", ")"))
    .join(db.raw(blockNumberForTimestampQuery(db, params.endTime || 0, "end").toString()).wrap("(", ")"))
    .leftJoin(db.raw("markets on universe=? and markets.marketCreator=? and markets.creationBlockNumber between startBlock and endBlock",
      [params.universe, params.account]))
    .leftJoin(db.raw("trades as trades on (creator=? or filler=?) and (trades.blockNumber between startBlock and endblock) AND trades.marketId in (select markets.marketId from markets where universe=?)",
      [params.account, params.account, params.universe]))
    .leftJoin(db.raw("trading_proceeds as proceeds on account=? and (proceeds.blockNumber between startBlock and endBlock) and proceeds.marketId in (select markets.marketId from markets where universe=?)",
      [params.account, params.universe]));

  const res = (await sql).shift();

  const startBlock = res.startBlock;
  const endBlock = res.endBlock;
  const marketsCreated = res.marketsCreated;
  const numberOfTrades = res.numberOfTrades;
  const marketsTraded = res.marketsTraded;
  const redeemedPositions = res.redeemedPositions;

  if (!startBlock || !endBlock || startBlock > endBlock)
    throw new Error("startTime/endTime error");

  // Positions and successfulDisputes must be in a separate queries because they kill sqlLite3 otherwise.
  const positionsResult = (await getPositions(db, startBlock, endBlock, params)).shift();
  const positions = positionsResult.positions;

  const successfulDisputesResult = (await getSuccessfulDisputes(db, startBlock, endBlock, params)).shift();
  const successfulDisputes = successfulDisputesResult.successfulDisputes;

  const result: AccountTimeRangeResult = {
    positions,
    marketsCreated,
    numberOfTrades,
    successfulDisputes,
    marketsTraded,
    redeemedPositions,
  };

  return result;
}

export async function getAccountTransferHistory(db: Knex, augur: {}, params: t.TypeOf<typeof AccountTransferHistoryParams>): Promise<Array<TransferRow<string>>> {
  const query = db("transfers").select([
    "transfers.transactionHash",
    "transfers.logIndex",
    "transfers.blockNumber as creationBlockNumber",
    "transfers.sender",
    "transfers.recipient",
    "transfers.token",
    "transfers.value",
    "blocks.timestamp as creationTime",
    "blocks.blockHash",
    "tokens.symbol",
    "tokens.outcome",
    "tokens.marketId",
    db.raw("CASE WHEN transfers.transactionHash IN (SELECT DISTINCT transactionHash FROM trades UNION SELECT DISTINCT transactionHash FROM orders) THEN 1 ELSE 0 END as isInternalTransfer"),
  ]).where((db: Knex): Knex.QueryBuilder => db.where("sender", params.account).orWhere("recipient", params.account));
  query.join("blocks", "blocks.blockNumber", "transfers.blockNumber");
  query.join("tokens", "tokens.contractAddress", "transfers.token");
  if (params.isInternalTransfer != null) query.where("isInternalTransfer", params.isInternalTransfer);
  if (params.token != null) query.andWhere("token", params.token);
  if (params.earliestCreationTime != null) query.where("creationTime", ">=", params.earliestCreationTime);
  if (params.latestCreationTime != null) query.where("creationTime", "<=", params.latestCreationTime);
  const results = await queryModifier<TransferRow<BigNumber>>(db, query, "transfers.blockNumber", "desc", params);
  return results.map((result) => formatBigNumberAsFixed<TransferRow<BigNumber>, TransferRow<string>>(result));
}

async function queryModifierUserland<T>(
  db: Knex,
  query: Knex.QueryBuilder,
  defaultSortBy: string,
  defaultSortOrder: string,
  sortLimitParams: Partial<SortLimit>,
): Promise<Array<T>> {
  type RowWithSort = T & { xMySorterFieldx: BigNumber };

  let sortField: string = defaultSortBy;
  let sortDescending: boolean = defaultSortOrder.toLowerCase() === "desc";

  if (sortLimitParams.sortBy != null) {
    sortField = sortLimitParams.sortBy;
    if (typeof (sortLimitParams.isSortDescending) !== "undefined" && sortLimitParams.isSortDescending !== null) {
      sortDescending = sortLimitParams.isSortDescending;
    }
  }

  const rows: Array<RowWithSort> = await query.select(db.raw(`?? as "xMySorterFieldx"`, [sortField]));
  const ascendingSorter = (left: RowWithSort, right: RowWithSort) => safeBigNumberCompare(left.xMySorterFieldx, right.xMySorterFieldx);
  const descendingSorter = (left: RowWithSort, right: RowWithSort) => safeBigNumberCompare(right.xMySorterFieldx, left.xMySorterFieldx);
  const results = rows.sort(sortDescending ? descendingSorter : ascendingSorter);
  if (sortLimitParams.limit == null && sortLimitParams.offset == null)
    return results;
  return results.slice(sortLimitParams.offset || 0, sortLimitParams.limit || results.length);
}

exports.up = async (knex: Knex): Promise<any> => {
  return knex.raw("DROP VIEW IF EXISTS balances_detail").then((): PromiseLike<any> => {
    return knex.raw(`CREATE VIEW balances_detail AS
      SELECT tokens."contractAddress" as token, symbol, "marketId", outcome, "feeWindow", owner, balance, supply FROM balances
      LEFT JOIN tokens ON balances.token = tokens."contractAddress"
      LEFT JOIN token_supply ON token_supply.token = tokens."contractAddress"`);
  });
};

exports.up = async (knex: Knex): Promise<any> => {
  return knex.raw("DROP VIEW IF EXISTS all_participants").then((): PromiseLike<any> => {
    return knex.raw(`CREATE VIEW all_participants AS
      SELECT
        markets.universe,
        markets."marketId",
        markets."feeWindow",
        'initial_report' as type,
        "initialReporter" as participantAddress,
        initial_reports.reporter,
        initial_reports."blockNumber",
        "amountStaked" as size,
        "amountStaked" as participantSupply,
        ${knex.client.config.client === "sqlite3" ?
        `CASE WHEN redeemed = 0 THEN initial_reports.amountStaked ELSE 0 END as reporterBalance,` :
        `CASE WHEN redeemed = false THEN initial_reports."amountStaked" ELSE '0' END as reporterBalance,`}
        contractBalances.token as reputationToken,
        contractBalances.balance as reputationTokenBalance,
        initial_reports."payoutId",
        1 as completed,
        "reportingState",
        markets.forking,
        ${knex.client.config.client === "sqlite3" ? "disavowed" : "cast(disavowed AS BOOLEAN) as disavowed"},
        markets."needsDisavowal"
      FROM initial_reports
        JOIN markets ON markets."marketId" = initial_reports."marketId"
        JOIN universes ON markets.universe = universes.universe
        JOIN balances as contractBalances ON (contractBalances.owner = initial_reports."initialReporter" AND contractBalances.token = universes."reputationToken")
        JOIN market_state ON markets."marketStateId" = market_state."marketStateId"
      union
      SELECT
        markets.universe,
        crowdsourcers."marketId",
        markets."feeWindow",
        'crowdsourcer' as type,
        crowdsourcers."crowdsourcerId" as participantAddress,
        accountBalances.owner as reporter,
        crowdsourcers."blockNumber",
        crowdsourcers.size,
        participantSupply.supply as participantSupply,
        accountBalances.balance as reporterBalance,
        contractBalances.token as reputationToken,
        contractBalances.balance as reputationTokenBalance,
        crowdsourcers."payoutId",
        crowdsourcers.completed,
        "reportingState",
        markets.forking,
        ${knex.client.config.client === "sqlite3" ? "crowdsourcers.disavowed" : "cast(crowdsourcers.disavowed AS BOOLEAN) as disavowed"},
        markets."needsDisavowal" from crowdsourcers
        JOIN markets ON markets."marketId" = crowdsourcers."marketId"
        JOIN universes ON markets.universe = universes.universe
        JOIN balances as accountBalances ON (accountBalances.token = crowdsourcers."crowdsourcerId")
        JOIN balances as contractBalances ON (contractBalances.owner = crowdsourcers."crowdsourcerId" AND contractBalances.token = universes."reputationToken")
        JOIN token_supply as participantSupply ON participantSupply.token = crowdsourcers."crowdsourcerId"
        JOIN market_state ON markets."marketStateId" = market_state."marketStateId"`);
  });
};

exports.up = async (knex: Knex): Promise<any> => {
  await knex.schema.dropTableIfExists("wcl_profit_loss_timeseries");

  await knex.schema.createTable("wcl_profit_loss_timeseries", (table: Knex.CreateTableBuilder): void => {
    table.string("account", 42).notNullable();
    table.string("marketId", 42).notNullable();
    table.specificType("outcome", "integer NOT NULL CONSTRAINT nonnegativeOutcome CHECK (\"outcome\" >= 0)");
    table.specificType("price", "varchar(255) NOT NULL CONSTRAINT nonnegativeAmount CHECK (ltrim(\"price\", '-') = \"price\")");
    table.string("position", 42).notNullable();
    table.string("quantityOpened", 42).notNullable();
    table.string("profit", 255).defaultTo("0");
    table.string("frozenFunds", 255).defaultTo("0");
    table.string("realizedCost", 255).defaultTo("0");
    table.string("transactionHash", 66).notNullable();
    table.specificType("timestamp", "integer NOT NULL CONSTRAINT nonnegativeTimestamp CHECK (\"timestamp\" >= 0)");
    table.specificType("logIndex", "integer NOT NULL CONSTRAINT \"nonnegativelogIndex\" CHECK (\"logIndex\" >= 0)");
    table.specificType("blockNumber", "integer NOT NULL CONSTRAINT positiveOrderBlockNumber CHECK (\"blockNumber\" > 0)");
  });

  const query = knex("trades")
    .select(knex.raw([`"marketId"`, "outcome", "amount", "price", `"orderType"`, "creator", "filler", "false as claim", `"blockNumber"`, `"logIndex"`, `"transactionHash", "numCreatorTokens", "numCreatorShares", "numFillerTokens", "numFillerShares"`]))
    .union((builder: Knex.QueryBuilder) => {
      return builder
        .from("trading_proceeds")
        .select(knex.raw([
          `"marketId"`, // TradeOrClaimRow.marketId
          "0", // TradeOrClaimRow.outcome
          "'0'", // TradeOrClaimRow.amount
          "'0'", // TradeOrClaimRow.price
          "'0'", // TradeOrClaimRow.orderType
          "''", // TradeOrClaimRow.creator
          "account", // TradeOrClaimRow.filler NB row.filler passed as `account` below
          "true as claim", // TradeOrClaimRow.claim
          `"blockNumber"`,
          `"logIndex"`,
          `"transactionHash"`,
          "'0'", // TradeOrClaimRow.numCreatorTokens
          "'0'", // TradeOrClaimRow.numCreatorShares
          "'0'", // TradeOrClaimRow.numFillerTokens
          "'0'", // TradeOrClaimRow.numFillerShares
        ]));
    });
  query.orderByRaw(`"blockNumber", "logIndex"`);

  const results: Array<TradeOrClaimRow> = await query;

  for (const row of results) {
    if (row.claim) {
      await updateProfitLossClaimProceeds(knex, row.marketId, row.filler, row.transactionHash, row.blockNumber, row.logIndex);
    } else {
      await updateProfitLoss(knex, row.marketId, row.orderType === "buy" ? row.amount : row.amount.negated(), row.creator, row.outcome, row.price, row.transactionHash, row.blockNumber, row.logIndex, row);
      await updateProfitLoss(knex, row.marketId, row.orderType === "sell" ? row.amount : row.amount.negated(), row.filler, row.outcome, row.price, row.transactionHash, row.blockNumber, row.logIndex, row);
    }
  }

  await knex.schema.dropTableIfExists("profit_loss_timeseries");
};

 await each(marketIds, async (marketIdRow) => {
   await updateMarketState(db, marketIdRow.marketId, blockNumber, ReportingState.AWAITING_FINALIZATION);
   augurEmitter.emit(SubscriptionEventNames.MarketState, {
     universe: marketIdRow.universe,
     marketId: marketIdRow.marketId,
     reportingState: ReportingState.AWAITING_FINALIZATION,
   });
   return db("payouts")
     .where({ marketId: marketIdRow.marketId })
     .update("winning", db.raw(`"tentativeWinning"`));
 });

exports.up = (knex: Knex)  => knex.raw(`
  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  CREATE TABLE ${TODO_TABLE} (
      guid uuid DEFAULT uuid_generate_v4() UNIQUE,
      ts TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
      title varchar NOT NULL,
      content text NOT NULL
    );

    CREATE INDEX id_idx ON ${TODO_TABLE} (guid);
    CREATE INDEX ts_idx ON ${TODO_TABLE} (ts);
    CREATE INDEX title_idx ON ${TODO_TABLE} (title);
`);

async function copyRunDbFixerScript(dbFileNamePath: string, backupDbPath: string): Promise<void> {
  await promisify(fs.copyFile)(dbFileNamePath, backupDbPath);
  // need to do this because cli runs migrations in .ts and augur-app runs migrations in .js
  const db: Knex = Knex({
    client: "sqlite3",
    connection: {
      filename: backupDbPath,
    },
    acquireConnectionTimeout: 5 * 60 * 1000,
    useNullAsDefault: true,
  });
  await db.raw("update knex_migrations set name = substr(name,1, length(name)-2) || 'js';");
  await db.destroy();
}

async function getParticipationTokenEthFees(db: Knex, augur: Augur, reporter: Address, universe: Address): Promise<Array<ParticipationTokenEthFee>> {
  const participationTokenQuery = db
    .select([
      "fee_windows.feeWindow",
      "participationToken.balance AS participationTokens",
      db.raw("IFNULL(feeTokenSupply.supply,0) as feeTokenSupply"),
      db.raw("IFNULL(participationTokenSupply.supply,0) as participationTokenSupply"),
      db.raw("IFNULL(cashFeeWindow.balance,0) as cashFeeWindow"),
    ])
    .from("fee_windows");
  participationTokenQuery.join("balances AS participationToken", function() {
    this.on("participationToken.token", db.raw("fee_windows.feeWindow")).andOn("participationToken.owner", db.raw("?", [reporter]));
  });
  participationTokenQuery.leftJoin("balances AS cashFeeWindow", function() {
    this.on("cashFeeWindow.owner", db.raw("fee_windows.feeWindow")).on("cashFeeWindow.token", db.raw("?", getCashAddress(augur)));
  });
  participationTokenQuery
    .leftJoin("token_supply as feeTokenSupply", "feeTokenSupply.token", "fee_windows.feeToken")
    .leftJoin("token_supply as participationTokenSupply", "participationTokenSupply.token", "fee_windows.feeWindow")
    .whereNot("participationTokens", "0")
    .where("fee_windows.state", FeeWindowState.PAST)
    .where("fee_windows.universe", universe);
  const participationTokens: Array<ParticipationTokensEthFeeRow> = await participationTokenQuery;
  return _.map(participationTokens, (participationToken) => {
    const totalFeeTokensInFeeWindow = new BigNumber(participationToken.feeTokenSupply).plus(new BigNumber(participationToken.participationTokenSupply));
    const cashInFeeWindow = new BigNumber(participationToken.cashFeeWindow);
    const participationTokens = new BigNumber(participationToken.participationTokens);
    const reporterShareOfFeeWindow = totalFeeTokensInFeeWindow.isZero() ? ZERO : participationTokens.dividedBy(totalFeeTokensInFeeWindow);
    const ethFees = reporterShareOfFeeWindow.times(cashInFeeWindow);
    return {
      feeWindow: participationToken.feeWindow,
      ethFees,
      participationTokens,
    };
  });
}

 public async migrateUp(): Promise<any> {
     await this.db.schema.table("markets", (markets) => {
       markets.specificType("searchProperties", "tsvector");
     });
     await this.db("markets").update({
       searchProperties: this.db.raw(`
             setweight(to_tsvector('english', coalesce(category, '')), 'A') ||
             setweight(to_tsvector('english', coalesce(tag1, '')), 'A') ||
             setweight(to_tsvector('english', coalesce(tag2, '')), 'A') ||
             setweight(to_tsvector('english', coalesce("shortDescription", '')), 'B') ||
             setweight(to_tsvector('english', coalesce("longDescription", '')), 'B') ||
             setweight(to_tsvector('english', coalesce("scalarDenomination", '')), 'C') ||
             setweight(to_tsvector('english', coalesce("resolutionSource", '')), 'C')
             `),
     });
     await this.db.schema.raw(`CREATE INDEX market_search_idx ON markets USING gin("searchProperties");`);
 }

async function getMarketsReportingParticipants(db: Knex, reporter: Address, universe: Address, participantEthFees: Array<ParticipantEthFee>): Promise<FormattedMarketInfo> {
  const initialReportersQuery = db("initial_reports")
    .distinct("initial_reports.initialReporter")
    .select(["initial_reports.disavowed", "initial_reports.marketId", "markets.universe", "market_state.reportingState", "markets.forking", "markets.needsMigration"])
    .join("markets", "initial_reports.marketId", "markets.marketId")
    .join("fee_windows", "markets.feeWindow", "fee_windows.feeWindow")
    .join("market_state", "market_state.marketStateId", "markets.marketStateId")
    .whereRaw("(markets.forking OR market_state.reportingState IN (?, ?, ?) OR (initial_reports.disavowed != 0 OR markets.needsDisavowal) AND (fee_windows.state = ? OR reportingState = ? ) )", [
      ReportingState.AWAITING_FINALIZATION,
      ReportingState.FINALIZED,
      ReportingState.FORKING,
      FeeWindowState.PAST,
      ReportingState.AWAITING_FORK_MIGRATION,
    ])
    .where("markets.universe", universe)
    .where("initial_reports.reporter", reporter)
    .where("initial_reports.redeemed", 0);
  const crowdsourcersQuery = db("crowdsourcers")
    .distinct("crowdsourcers.crowdsourcerId")
    .select(["crowdsourcers.disavowed", "crowdsourcers.marketId", "markets.universe", "market_state.reportingState", "markets.forking", "markets.needsMigration", "balances.balance as amountStaked"])
    .join("balances", "balances.token", "crowdsourcers.crowdsourcerId")
    .join("markets", "crowdsourcers.marketId", "markets.marketId")
    .join("fee_windows", "crowdsourcers.feeWindow", "fee_windows.feeWindow")
    .join("market_state", "market_state.marketStateId", "markets.marketStateId")
    .whereRaw("(markets.forking or (market_state.reportingState IN (?, ?, ?)) OR (crowdsourcers.disavowed != 0 OR markets.needsDisavowal) AND (fee_windows.state = ? OR reportingState = ? ) )", [
      ReportingState.AWAITING_FINALIZATION,
      ReportingState.FINALIZED,
      ReportingState.FORKING,
      FeeWindowState.PAST,
      ReportingState.AWAITING_FORK_MIGRATION,
    ])
    .andWhere("markets.universe", universe)
    .andWhere("balances.owner", reporter)
    .andWhereNot("balances.balance", "0");
  const forkedMarketQuery = db("markets")
    .first(["markets.marketId", "markets.universe", db.raw("market_state.reportingState = 'FINALIZED' as isFinalized")])
    .where("markets.forking", 1)
    .where("markets.universe", universe)
    .join("market_state", "markets.marketId", "market_state.marketId");

  const initialReporters: Array<UnclaimedInitialReporterRow> = await initialReportersQuery;
  const crowdsourcers: Array<UnclaimedCrowdsourcerRow> = await crowdsourcersQuery;
  const forkedMarket: ForkedMarket = await forkedMarketQuery;
  return formatMarketInfo(initialReporters, crowdsourcers, forkedMarket, participantEthFees);
}

export async function getUniversesInfo(db: Knex, augur: Augur, params: t.TypeOf<typeof UniverseInfoParams>): Promise<Array<UIUniverseInfoRow<string>>> {
  const parentUniverseRow: ParentUniverseRow = await db.select("parentUniverse").from("universes").where("universe", params.universe).first();
  if (parentUniverseRow === undefined) return [];
  const query = db.select([
    "universes.universe",
    "universes.parentUniverse",
    "payouts.isInvalid",
    "payouts.payout0",
    "payouts.payout1",
    "payouts.payout2",
    "payouts.payout3",
    "payouts.payout4",
    "payouts.payout5",
    "payouts.payout6",
    "payouts.payout7",
    "balances.balance",
    "token_supply.supply",
    db.raw("count(markets.marketId) as numMarkets")]).from("universes")
    .where("universes.parentUniverse", params.universe) // Children
    .orWhere("universes.parentUniverse", parentUniverseRow.parentUniverse) // Siblings
    .orWhere("universes.universe", params.universe) // Universe
    .orWhere("universes.universe", parentUniverseRow.parentUniverse) // Parent
    .leftJoin("token_supply", "token_supply.token", "universes.reputationToken")
    .leftJoin("balances", function () {
      this
        .on("balances.owner", db.raw("?", [params.account]))
        .on("balances.token", "universes.reputationToken");
    })
    .leftJoin("markets", "markets.universe", "universes.universe")
    .leftJoin("payouts", "payouts.payoutId", "universes.payoutId")
    .groupBy("universes.universe");
  const universeInfoRows: Array<UniverseInfoRow<BigNumber>> = await query;
  return universeInfoRows.map((row: UniverseInfoRow<BigNumber>) => {
    return formatBigNumberAsFixed<UIUniverseInfoRow<BigNumber>, UIUniverseInfoRow<string>>({
      universe: row.universe,
      parentUniverse: row.parentUniverse,
      balance: row.balance || "0",
      supply: row.supply || "0",
      numMarkets: row.numMarkets,
      payout: normalizedPayoutsToFixed(normalizePayouts(row)).payout,
      isInvalid: Boolean(row.isInvalid),
    });
  });
}

 .union((builder: Knex.QueryBuilder) => {
   return builder
     .from("trading_proceeds")
     .select(knex.raw([
       `"marketId"`, // TradeOrClaimRow.marketId
       "0", // TradeOrClaimRow.outcome
       "'0'", // TradeOrClaimRow.amount
       "'0'", // TradeOrClaimRow.price
       "'0'", // TradeOrClaimRow.orderType
       "''", // TradeOrClaimRow.creator
       "account", // TradeOrClaimRow.filler NB row.filler passed as `account` below
       "true as claim", // TradeOrClaimRow.claim
       `"blockNumber"`,
       `"logIndex"`,
       `"transactionHash"`,
       "'0'", // TradeOrClaimRow.numCreatorTokens
       "'0'", // TradeOrClaimRow.numCreatorShares
       "'0'", // TradeOrClaimRow.numFillerTokens
       "'0'", // TradeOrClaimRow.numFillerShares
     ]));
 });

async function getOldestTradePriceMinusMinPriceUserPaidForOpenPositionInLast24hByOutcomeByMarketId(db: Knex, now: number, params: GetProfitLossParamsType): Promise<Dictionary<Dictionary<Price>>> {
  const newerThan = (params.endTime || now) - 86400; // newerThan is a unix timestamp in seconds; 86400 is one day in seconds, ie. filter by trades that opened a user's position in the last day
  const trades: Array<
  Pick<TradesRow<BigNumber>, "marketId" | "outcome" | "price"> &
  Pick<MarketsRow<BigNumber>, "minPrice">
  > = await db.raw(`
    SELECT trades.marketId, trades.outcome, price, a.minPrice
    FROM trades
    INNER JOIN (
        SELECT wcl_profit_loss_timeseries.marketId, wcl_profit_loss_timeseries.outcome, timestamp, wcl_profit_loss_timeseries.transactionHash, wcl_profit_loss_timeseries.logIndex, markets.minPrice
        FROM wcl_profit_loss_timeseries
        INNER JOIN markets ON wcl_profit_loss_timeseries.marketId = markets.marketId
        WHERE account = :account
            AND universe = :universe
            AND CAST(quantityOpened as REAL) > 0
            AND timestamp >= :newerThan
        GROUP by wcl_profit_loss_timeseries.marketId, outcome
        HAVING min(timestamp)
    ) a ON trades.marketId = a.marketId AND trades.outcome = a.outcome AND trades.transactionHash = a.transactionHash AND trades.logIndex = a.logIndex
    GROUP BY trades.marketId, trades.outcome
    HAVING min(CAST(price as REAL))
    `, {
      account: params.account,
      universe: params.universe,
      newerThan,
    });
  const tradesByMarketId = _.groupBy(trades, "marketId");
  return _.reduce(tradesByMarketId, (result, allTradesOneMarket, marketId) => {
    const allTradesOneMarketByOutcome = _.groupBy(allTradesOneMarket, (r) => r.outcome);
    const priceByOutcome = _.mapValues(allTradesOneMarketByOutcome, (allTradesForOneOutcome) => {
      if (allTradesForOneOutcome.length !== 1) {
        throw new Error(`getOldestTradePriceMinusMinPriceUserPaidForOpenPositionInLast24hByOutcomeByMarketId: expected allTradesForOneOutcome to have lenght 1 because we select exactly one trade row per matching (marketId, outcome), allTradesForOneOutcome.length=${allTradesForOneOutcome.length}, params=${params}`);
      }
      return new Price(allTradesForOneOutcome[0].price.minus(allTradesForOneOutcome[0].minPrice));
    });
    result[marketId] = priceByOutcome;
    return result;
  }, {} as Dictionary<Dictionary<Price>>);
}

function queryMarketCreation(db: Knex, qb: Knex.QueryBuilder, params: GetAccountTransactionHistoryParamsType) {
  return qb.select(
    db.raw("? as action", Action.MARKET_CREATION),
    db.raw("'ETH' as coin"),
    db.raw("'ETH validity bond for market creation' as details"),
    "markets.marketId",
    db.raw("NULL as marketCreatorFees"),
    "markets.marketType",
    db.raw("NULL as minPrice"),
    db.raw("NULL as maxPrice"),
    db.raw("NULL as numPayoutTokens"),
    db.raw("NULL as numShares"),
    db.raw("NULL as reporterFees"),
    "markets.scalarDenomination",
    db.raw("markets.creationFee as fee"),
    "markets.shortDescription as marketDescription",
    db.raw("NULL as outcome"),
    db.raw("NULL as outcomeDescription"),
    db.raw("NULL as payout0"),
    db.raw("NULL as payout1"),
    db.raw("NULL as payout2"),
    db.raw("NULL as payout3"),
    db.raw("NULL as payout4"),
    db.raw("NULL as payout5"),
    db.raw("NULL as payout6"),
    db.raw("NULL as payout7"),
    db.raw("NULL as isInvalid"),
    db.raw("'0' as price"),
    db.raw("'0' as quantity"),
    db.raw("'0' as total"),
    "markets.transactionHash",
    db.raw("markets.creationBlockNumber as blockNumber"))
    .from("markets")
    .where({
      "markets.marketCreator": params.account,
      "markets.universe": params.universe,
    });
}

 qb.union((qb: Knex.QueryBuilder) => {
   qb.select(
     db.raw("? as action", Action.COMPLETE_SETS),
     db.raw("'ETH' as coin"),
     db.raw("'Sell complete sets' as details"),
     "markets.marketId",
     db.raw("markets.marketCreatorFeeRate as marketCreatorFees"),
     "markets.marketType",
     "markets.minPrice",
     "markets.maxPrice",
     db.raw("NULL as numPayoutTokens"),
     db.raw("NULL as numShares"),
     db.raw("NULL as reporterFees"),
     "markets.scalarDenomination",
     db.raw("fee_windows.fees as fee"),
     "markets.shortDescription as marketDescription",
     db.raw("NULL as outcome"),
     db.raw("NULL as outcomeDescription"),
     db.raw("NULL as payout0"),
     db.raw("NULL as payout1"),
     db.raw("NULL as payout2"),
     db.raw("NULL as payout3"),
     db.raw("NULL as payout4"),
     db.raw("NULL as payout5"),
     db.raw("NULL as payout6"),
     db.raw("NULL as payout7"),
     db.raw("NULL as isInvalid"),
     "markets.numTicks as price",
     "completeSets.numCompleteSets as quantity",
     db.raw("'0' as total"),
     "completeSets.transactionHash",
     "completeSets.blockNumber")
     .from("completeSets")
     .join("markets", "markets.marketId", "completeSets.marketId")
     .join("fee_windows", "fee_windows.universe", "markets.universe")
     .join("blocks", "blocks.blockNumber", "completeSets.blockNumber")
     .whereRaw("blocks.timestamp between fee_windows.startTime and fee_windows.endTime")
     .where({
       "completeSets.eventName": "CompleteSetsSold",
       "completeSets.account": params.account,
       "markets.universe": params.universe,
     });
 });

exports.down = (knex: Knex) => knex.raw(`
  DROP TABLE ${TODO_TABLE} CASCADE;
`);

 marketsQuery.leftJoin("balances AS cash", function () {
   this
     .on("cash.owner", db.raw("markets.marketCreatorMailbox"))
     .andOn("cash.token", db.raw("?", getCashAddress(augur)));
 });