{
  databases:
    {
      train:
        {
          queries:
            {
              train_overview:
                |||
                  SELECT
                     version,
                     rollbacks,
                     rollbacks_time,
                     group2_delay_days,
                     (group0_delay_days +
                     group1_delay_days +
                     group2_delay_days) AS total_delay,
                     total_time AS train_total_time,
                     (select count(*) from blocker b where b.train_id = t.id) AS blockers,
                     (select count(*) from blocker b where b.train_id = t.id and resolved = 1) AS resolved_blockers,
                     patches,
                     (select max(time_in_review) from patch p where p.train_id = t.id)/(60*60)  AS max_time_in_review,
                     (select max(comments) from patch where patch.train_id = t.id) AS max_comments_per_patch,
                     (select max(start_time - created) from patch p where p.train_id = t.id)/(60*60) AS max_cycle_time
                     FROM train t
                     ORDER BY version DESC limit 10
                |||,
            },
        },
    },
  plugins: {
    'datasette-dashboards': {
      train: {
        title: 'Train Dashboard',
        description: 'Train metrics demo',
        layout: [
          [ 'train-conductors', 'train-blockers-unblockers'],
          ['train-timing', 'train-rollbacks'],
          [ 'train-blockers-time-to-unblock', 'train-blockers-time-to-unblock'],
          ['train-patches-line', 'train-blockers-trend' ],
        ],
        filters: {
          date_start: {
            name: 'Date Start',
            type: 'date',
            default: '2021-01-01',
          },
          date_end: {
            name: 'Date End',
            type: 'date',
          },
        },
        charts: {
          local parseIntTransform = {"calculate": "parseInt(datum.count)", "as": "x"},
          'train-rollbacks': {
            title: 'Patches and rollbacks',
            db: 'train',
            query: "select version, patches, rollbacks, rollbacks_time / 3600 as rollbacks_hours, total_time / 3600 as total_hours, count(b.id) as blockers, datetime(start_time+total_time, 'unixepoch') as end_time, datetime(start_time, 'unixepoch', 'weekday 3') as mid_week, datetime(start_time, 'unixepoch') as start_time from train t, blocker b on t.id=b.train_id group by t.version order by start_time desc limit 3",
            library: 'vega',
            display: {
              local chart = self,
              local xf = { field: 'start_time', type: 'temporal' },
              transform: [

              ],
              layer: [
                {
                  local layer = self,
                  mark: { type: 'rule', tooltip: true, color: { value: 'black' } },
                  encoding: {
                    y: { field: 'version', type: 'ordinal' },
                    x: xf,
                    x2: { field: 'end_time', type: 'temporal' },
                    tooltip: { field: 'total_hours', type: 'quantitative' },
                  },
                },
                {
                  mark: { type: 'rule' },
                  encoding: {
                    x: xf,
                    y: { field: 'version', type: 'nominal', bandPosition: '0.1' },
                    y2: { field: 'version', type: 'nominal', bandPosition: '0.9' },
                  },
                },
                {
                  mark: { type: 'rule' },
                  encoding: {
                    x: { field: 'end_time', type: 'temporal' },
                    y: { field: 'version', type: 'nominal', bandPosition: '0.1' },
                    y2: { field: 'version', type: 'nominal', bandPosition: '0.9' },

                  },
                },
                {
                  mark: { type: 'point', tooltip: true, filled: true },
                  encoding: {
                    x: { field: 'mid_week', type: 'temporal', title: 'train time' },
                    y: { field: 'version', type: 'ordinal', title: 'MediaWiki Version' },
                    color: { field: 'version', type: 'nominal', title: 'MediaWiki Version' },
                    size: { field: 'patches', type: 'quantitative', legend: { orient: 'bottom' } },
                    tooltip: [
                      { field: 'version', title: 'MediaWiki Version' },
                      { field: 'patches', title: 'Patches deployed' },
                      { field: 'blockers', title: 'Train-blocking bugs' },
                      { field: 'total_hours', title: 'Total time (hours)' },
                      { field: 'rollbacks_hours', title: 'Rolled-back (hours)' },
                    ],
                  },
                },
              ],
            },
          },


          'train-conductors': {
            title: 'Number of trains by conductor',
            db: 'train',
            query: 'SELECT conductor, count(version) AS count, sum(patches) AS patches, sum(rollbacks) AS rollbacks FROM train WHERE TRUE GROUP BY conductor',
            library: 'vega',
            display: {
              transform: [ parseIntTransform ],
              mark: { type: 'bar', tooltip: true },
              encoding: {
                y: { field: 'conductor', type: 'nominal', sort: '-x' },
                color: { field: 'count', type: 'quantitative' },
                x: { field: 'x', type: 'quantitative' },
              },
            },
          },

          'train-patches-line': {
            title: 'Number of patches trend',
            db: 'train',
            query: "select id, version, patches, total_time, datetime(start_time, 'unixepoch') as start_time,  datetime(start_time+total_time, 'unixepoch') as end_time  from train order by start_time asc",
            library: 'vega',
            display: {
              local encoding = {
                      y: { field: 'patches', type: 'quantitative', title: 'Patches merged.' },
                      x: { field: 'start_time', type: 'temporal', title:'train time' },
                      //x2: { field: 'end_time', type: 'temporal', title:'' },
              },
              layer: [
                {
                    mark: { type: 'bar', tooltip: true },
                    encoding: encoding,
                },
                {
                  "transform": [
                    {
                      "regression": "patches",
                      "on": "start_time"
                    }
                  ],
                  mark: { type: 'line', color: 'firebrick', tooltip: true },
                  encoding: {
                    x: encoding.x,
                    y: encoding.y
                  }
                },
              ]
            },
          },
          'train-blockers-trend': {
            title: 'Number of Blockers trend',
            db: 'train',
            query: "select id, train_id, datetime(blocked, 'unixepoch') as start_time, datetime(unblocked, 'unixepoch') as end_time, blocker, unblocker, removed, resolved, task, url, status, group_blocked, group_unblocked, count(*) as count from blocker group by train_id order by blocked, unblocked asc",
            library: 'vega',
            display: {
              local encoding = {
                      y: { field: 'count', type: 'quantitative', title: 'Blockers reported.' },
                      x: { field: 'start_time', type: 'temporal', title:'Train time' },
                      //x2: { field: 'end_time', type: 'temporal', title:'' },
              },
              layer: [
                {
                    mark: { type: 'area', tooltip: true },
                    encoding: encoding,
                },
                {
                  "transform": [
                    {
                      "regression": "count",
                      "on": "start_time"
                    }
                  ],
                  mark: { type: 'line', color: 'firebrick', tooltip: true },
                  encoding: {
                    x: encoding.x,
                    y: encoding.y
                  }
                },
              ]
            },
          },
          'train-blockers-unblockers': {
            title: 'Train blockers reported',
            db: 'train',
            query: "with b2 as ( select unblocker, count(*) as unblock_count from blocker group by unblocker having unblock_count > 5), b1 as ( select blocker, count(*) as block_count from blocker group by blocker having block_count > 5 ) select blocker as who, block_count, 0-unblock_count as unblock_count from b1 join b2 on b1.blocker=b2.unblocker ",
            library: 'vega',
            display:
            {
                "transform": [
                  {
                    fold: ['block_count', 'unblock_count']
                  },
                ],
                mark: { type: 'bar', tooltip: true },
                encoding: {
                    y: { field: 'who', type: 'nominal', title: 'Who', "sort": "x" },
                    x: { field: 'value', type: 'quantitative', title:'number resolved | number reported', scale: { domain: [-200, 200], stack: "center" },},
                    //x2: { field: 'v2', type: 'quantitative', title:'number resolved', scale: { domain: [0, 100]},"stack": "center"},
                    color: { field: 'key',  type: "ordinal", "scale": {"range": ["#675193", "#ca8861"]} },
                },
            }
          },
          'train-blockers-time-to-unblock': {
            title: 'Time to unblock',
            db: 'train',
            query: "select blocker, unblocker,datetime(min(blocked), 'unixepoch') as start_time, datetime(max(unblocked), 'unixepoch') as end_time, avg(unblocked-blocked)/3600 as blocked_time, count(*) as count from blocker group by unblocker having block_count > 4 ",
            library: 'vega',
            display:
            {
                "transform": [parseIntTransform],
                mark: { type: 'bar', tooltip: true },
                encoding: {
                    x: {
                        field: 'start_time',
                        type: 'temporal',
                        title: 'Hours blocked (mean)',
                        axis: {"grid": true, "tickBand": "extent"}
                    },
                    x2: { field: "end_time", type: "temporal" },
                    y: { field: 'unblocker', type: 'nominal', title:'Who', sort: 'x' },
                    color: { field: 'unblocker', type: "nominal" },
                    size: { field: 'blocked_time', type: 'quantitative', title: 'Blockers resolved.' }

                    //color: { field: '', type: 'quantitative', title:'', sort: "x" },
                },
            }
          },
          'train-timing': {
            title: 'Cycle time and Lead time',
            db: 'train',
            query: "SELECT t.id, t.version, datetime(start_time, 'unixepoch') AS start_time, (start_time - created)/(60*60) AS cycle_time, (start_time - submitted)/(60*60) AS lead_time, datetime(created, 'unixepoch') AS created, datetime(submitted, 'unixepoch') submitted FROM patch p JOIN train t ON t.id = p.train_id WHERE ( lead_time > 0 AND cycle_time > 0 ) ORDER BY start_time DESC",
            library: 'vega',
            display: {
              transform: [
                { fold: ['lead_time', 'cycle_time'] },
              ],
              layer: [
                {
                  mark: { type: 'bar' },
                },
                {
                  mark: { type: 'text', dx: 10 },
                  encoding: {
                    text: {
                      field: 'value',
                      aggregate: 'mean',
                      format: 'd',
                      fontSize: 18,
                    },
                    color: {
                      value: 'white',
                    },
                  },
                },
              ],
              encoding: {
                x: {
                  aggregate: 'mean',
                  field: 'value',
                  axis: {
                    title: 'Lead, Cycle time (minutes)',
                    labelFontSize: 12,
                  },
                  type: 'quantitative',
                },
                y: {
                  field: 'version',
                  type: 'ordinal',
                  axis: {
                    labelAngle: 0,
                    labelFontSize: 17,
                  },
                },
                color: {
                  field: 'key',
                  type: 'nominal',
                },
              },
            },
          },
        },
      },
    },
  },
}