metadata.yml

---
databases:
  arc:
    tables:
      arc_export_files:
        sort_desc: file_name
        label_column: file_name
        units:
          file_size: bytes
          active_energy_burned: calorie
      places:
        sort: name
        label_column: name
        plugins:
          datasette-cluster-map:
            latitude_column: latitude
            longitude_column: longitude
      timeline_items:
        sort_desc: ends_at
        label_column: item_id
        plugins:
          datasette-cluster-map:
            latitude_column: latitude
            longitude_column: longitude
      samples:
        sort_desc: taken_at
        label_column: sample_id
        plugins:
          datasette-cluster-map:
            latitude_column: latitude
            longitude_column: longitude
    queries:
      calendar:
        title: Timeline Calendar
        sql: |
          SELECT
            timeline_items.item_id AS event_uid,
            places.name AS event_name,
            timeline_items.starts_at AS event_dtstart,
            timeline_items.ends_at AS event_dtend
          FROM
            timeline_items
            INNER JOIN places ON places.place_id = timeline_items.place_id
          ORDER BY
            timeline_items.starts_at DESC
plugins:
  datasette-dashboards:
    dashboard:
      title: Arc
      layout:
        - [steps, steps-by-day]
        - [floors-climbed, floors-climbed-by-day]
        - [places-visited, places-visited-table]
      filters:
        starts_at:
          label: Start date
          type: date
        ends_at:
          label: End date
          type: date
      charts:
        steps:
          title: Steps
          db: arc
          query: |
            SELECT sum(step_count) AS steps
            FROM timeline_items
            WHERE
              TRUE
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
          library: metric
          display:
            field: steps
            prefix:
            suffix:
        steps-by-day:
          title: Steps by day
          db: arc
          query: |
            SELECT
              date(starts_at) AS starts_at,
              sum(step_count) AS steps
            FROM
              timeline_items
            WHERE
              TRUE
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
            GROUP BY
              starts_at
            ORDER BY
              starts_at
          library: vega-lite
          display:
            mark: { type: rect, tooltip: true }
            encoding:
              y: { field: steps, aggregate: sum, title: "Steps" }
              x: { field: starts_at, title: "Date" }
        floors-climbed:
          title: Floors climbed
          db: arc
          query: |
            SELECT sum(floors_ascended) AS floors
            FROM timeline_items
            WHERE
              TRUE
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
          library: metric
          display:
            field: floors
            prefix:
            suffix:
        floors-climbed-by-day:
          title: Floors climbed by day
          db: arc
          query: |
            SELECT
              date(starts_at) AS starts_at,
              sum(floors_ascended) AS floors
            FROM
              timeline_items
            WHERE
              TRUE
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
            GROUP BY
              starts_at
            ORDER BY
              starts_at
          library: vega-lite
          display:
            mark: { type: rect, tooltip: true }
            encoding:
              y: { field: floors, aggregate: sum, title: "Floors climbed" }
              x: { field: starts_at, title: "Date" }
        places-visited:
          title: Map
          db: arc
          query: |
            SELECT
              latitude,
              longitude
            FROM
              timeline_items
            WHERE
              latitude IS NOT NULL
              AND longitude IS NOT NULL
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
            LIMIT 1000
          library: map
          display:
            latitude_column: latitude
            longitude_column: longitude
        places-visited-table:
          title: Top places visited
          db: arc
          query: |
            SELECT
              "<a href='/arc/places/" || places.place_id || "'>" || places.name || "</a>" AS "<strong>Place name</strong>",
              round(
                sum(unixepoch(ends_at) - unixepoch(starts_at)) / 60.0 / 60.0,
                2
              ) AS "<strong>Hours spent</strong>"
            FROM
              timeline_items
              INNER JOIN places ON places.place_id = timeline_items.place_id
            WHERE
              timeline_items.place_id IS NOT NULL
              [[ AND starts_at >= date(:starts_at) ]]
              [[ AND ends_at <= date(:ends_at) ]]
            GROUP BY
              places.place_id
            ORDER BY
              "<strong>Hours spent</strong>" DESC,
              places.place_id
            LIMIT
              10
          library: table