/// <reference path="../pb_data/types.d.ts" />
migrate((app) => {
  const collection = app.findCollectionByNameOrId("pbc_135889471")

  // update collection data
  unmarshal({
    "viewQuery": "SELECT\n      (p.id || '_' || t.id || '_' || tour.id) as id,\n      p.id as player_id,\n      (p.first_name || ' ' || p.last_name) as player_name,\n      t.id as team_id,\n      t.name as team_name,\n      tour.id as tournament_id,\n      tour.name as tournament_name,\n      COUNT(m.id) as matches,\n      SUM(CASE\n          WHEN (m.home = t.id AND m.home_cups > m.away_cups) OR\n               (m.away = t.id AND m.away_cups > m.home_cups)\n          THEN 1 ELSE 0\n      END) as wins,\n      SUM(CASE\n          WHEN (m.home = t.id AND m.home_cups < m.away_cups) OR\n               (m.away = t.id AND m.away_cups < m.home_cups)\n          THEN 1 ELSE 0\n      END) as losses,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.home_cups\n          WHEN m.away = t.id THEN m.away_cups\n          ELSE 0\n      END) as total_cups_made,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.away_cups\n          WHEN m.away = t.id THEN m.home_cups\n          ELSE 0\n      END) as total_cups_against\n  FROM players p, teams t, matches m, tournaments tour\n  WHERE\n      t.players LIKE '%\"' || p.id || '\"%' AND\n      (m.home = t.id OR m.away = t.id) AND\n      m.tournament = tour.id AND\n      m.status = 'ended'\n  GROUP BY p.id, t.id, tour.id"
  }, collection)

  // remove field
  collection.fields.removeById("_clone_976G")

  // remove field
  collection.fields.removeById("_clone_njLe")

  // remove field
  collection.fields.removeById("_clone_ZNMy")

  // remove field
  collection.fields.removeById("_clone_nxTv")

  // remove field
  collection.fields.removeById("json3041953980")

  // remove field
  collection.fields.removeById("json1531431708")

  // remove field
  collection.fields.removeById("json1062535948")

  // remove field
  collection.fields.removeById("json4249694556")

  // add field
  collection.fields.addAt(4, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_YqC8",
    "max": 0,
    "min": 0,
    "name": "team_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": true,
    "system": false,
    "type": "text"
  }))

  // add field
  collection.fields.addAt(6, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_jZTo",
    "max": 0,
    "min": 0,
    "name": "tournament_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": true,
    "system": false,
    "type": "text"
  }))

  return app.save(collection)
}, (app) => {
  const collection = app.findCollectionByNameOrId("pbc_135889471")

  // update collection data
  unmarshal({
    "viewQuery": "\n  SELECT\n      (p.id || '_' || t.id || '_' || tour.id) as id,\n      p.id as player_id,\n      (p.first_name || ' ' || p.last_name) as player_name,\n      p.first_name,\n      p.last_name,\n      t.id as team_id,\n      t.name as team_name,\n      tour.id as tournament_id,\n      tour.name as tournament_name,\n      COUNT(m.id) as matches,\n      SUM(CASE\n          WHEN (m.home = t.id AND m.home_cups > m.away_cups) OR\n               (m.away = t.id AND m.away_cups > m.home_cups)\n          THEN 1 ELSE 0\n      END) as wins,\n      SUM(CASE\n          WHEN (m.home = t.id AND m.home_cups < m.away_cups) OR\n               (m.away = t.id AND m.away_cups < m.home_cups)\n          THEN 1 ELSE 0\n      END) as losses,\n      AVG(CASE\n          WHEN m.home = t.id AND m.home_cups > m.away_cups\n          THEN m.home_cups - m.away_cups\n          WHEN m.away = t.id AND m.away_cups > m.home_cups\n          THEN m.away_cups - m.home_cups\n          ELSE NULL\n      END) as margin_of_victory,\n      AVG(CASE\n          WHEN m.home = t.id AND m.home_cups < m.away_cups\n          THEN m.away_cups - m.home_cups\n          WHEN m.away = t.id AND m.away_cups < m.home_cups\n          THEN m.home_cups - m.away_cups\n          ELSE NULL\n      END) as margin_of_loss,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.home_cups\n          WHEN m.away = t.id THEN m.away_cups\n          ELSE 0\n      END) as total_cups_won_by,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.away_cups\n          WHEN m.away = t.id THEN m.home_cups\n          ELSE 0\n      END) as total_cups_lost_by,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.home_cups\n          WHEN m.away = t.id THEN m.away_cups\n          ELSE 0\n      END) as total_cups_made,\n      SUM(CASE\n          WHEN m.home = t.id THEN m.away_cups\n          WHEN m.away = t.id THEN m.home_cups\n          ELSE 0\n      END) as total_cups_against\n  FROM players p\n  JOIN teams t ON json_extract(t.players, '$[*]') LIKE '%' || p.id || '%'\n  JOIN matches m ON (m.home = t.id OR m.away = t.id)\n  JOIN tournaments tour ON m.tournament = tour.id\n  WHERE m.status = 'ended'\n  GROUP BY p.id, t.id, tour.id"
  }, collection)

  // add field
  collection.fields.addAt(3, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_976G",
    "max": 0,
    "min": 0,
    "name": "first_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": true,
    "system": false,
    "type": "text"
  }))

  // add field
  collection.fields.addAt(4, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_njLe",
    "max": 0,
    "min": 0,
    "name": "last_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": false,
    "system": false,
    "type": "text"
  }))

  // add field
  collection.fields.addAt(6, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_ZNMy",
    "max": 0,
    "min": 0,
    "name": "team_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": true,
    "system": false,
    "type": "text"
  }))

  // add field
  collection.fields.addAt(8, new Field({
    "autogeneratePattern": "",
    "hidden": false,
    "id": "_clone_nxTv",
    "max": 0,
    "min": 0,
    "name": "tournament_name",
    "pattern": "",
    "presentable": false,
    "primaryKey": false,
    "required": true,
    "system": false,
    "type": "text"
  }))

  // add field
  collection.fields.addAt(12, new Field({
    "hidden": false,
    "id": "json3041953980",
    "maxSize": 1,
    "name": "margin_of_victory",
    "presentable": false,
    "required": false,
    "system": false,
    "type": "json"
  }))

  // add field
  collection.fields.addAt(13, new Field({
    "hidden": false,
    "id": "json1531431708",
    "maxSize": 1,
    "name": "margin_of_loss",
    "presentable": false,
    "required": false,
    "system": false,
    "type": "json"
  }))

  // add field
  collection.fields.addAt(14, new Field({
    "hidden": false,
    "id": "json1062535948",
    "maxSize": 1,
    "name": "total_cups_won_by",
    "presentable": false,
    "required": false,
    "system": false,
    "type": "json"
  }))

  // add field
  collection.fields.addAt(15, new Field({
    "hidden": false,
    "id": "json4249694556",
    "maxSize": 1,
    "name": "total_cups_lost_by",
    "presentable": false,
    "required": false,
    "system": false,
    "type": "json"
  }))

  // remove field
  collection.fields.removeById("_clone_YqC8")

  // remove field
  collection.fields.removeById("_clone_jZTo")

  return app.save(collection)
})