url:
  formhandler-flags:
    pattern: /$YAMLURL/flags
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      formats:
        text:
          format: template
          template: $YAMLPATH/text-template.txt
          headers:
            Content-Type: text/plain
        pptx-table:
          format: pptx
          source: $YAMLPATH/input.pptx
          change-table:
            Table:
              table:
                data: data['data']
          headers:
            Content-Type: application/vnd.openxmlformats-officedocument.presentationml.presentation
        cors-json:
          format: json
          headers:
            Content-Type: application/json
            Access-Control-Allow-Origin: "*"

  formhandler-continent:
    pattern: /$YAMLURL/continent
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      function: data.groupby('Continent').sum().reset_index()

  formhandler-totals:
    pattern: /$YAMLURL/totals
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      modify: data.sum(numeric_only=True).to_frame().T

  formhandler-replace:
    pattern: /$YAMLURL/replace
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      prepare: args.update(Cross=args.pop('c', []))

  formhandler-db:
    pattern: /$YAMLURL/db
    handler: FormHandler
    kwargs:
      url: sqlite:///$YAMLPATH/database.sqlite3
      table: flags

  formhandler-query:
    pattern: /$YAMLURL/query
    handler: FormHandler
    kwargs:
      url: sqlite:///$YAMLPATH/database.sqlite3
      table: flags
      query: "SELECT Continent, COUNT(*) AS num, SUM(c1) FROM flags GROUP BY Continent"

  formhandler-queryfile:
    pattern: /$YAMLURL/queryfile
    handler: FormHandler
    kwargs:
      url: sqlite:///$YAMLPATH/database.sqlite3
      table: flags
      queryfile: $YAMLPATH/continents.sql

  formhandler-multidata:
    pattern: /$YAMLURL/multidata
    handler: FormHandler
    kwargs:
      continents:
        url: $YAMLPATH/flags.csv
        function: data.groupby('Continent').sum().reset_index()
      stripes:
        url: $YAMLPATH/flags.csv
        function: data.groupby('Stripes').sum().reset_index()

  formhandler-modify-multi:
    pattern: /$YAMLURL/modify-multi
    handler: FormHandler
    kwargs:
      symbols:
        url: sqlite:///$YAMLPATH/database.sqlite3
        table: flags
        query: "SELECT Continent, COUNT(DISTINCT Symbols) AS dsymbols FROM flags GROUP BY Continent"
        modify: data.assign(rank=data['dsymbols'].rank())
      colors:
        url: $YAMLPATH/flags.csv
        function: data.groupby('Continent').sum().reset_index()
      modify: data['colors'].merge(data['symbols'], on='Continent')

  formhandler-chart:
    pattern: /$YAMLURL/chart
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      function: data.groupby('Continent').sum().reset_index()
      formats:
        barchart:
          format: sns
          chart: barplot
          ext: png
          dpi: 48
          width: 400
          height: 300
          x: Continent
          y: c1
          headers:
            Content-Type: image/png
        barchart-svg:
          format: sns
          chart: barplot
          ext: svg
          dpi: 48
          width: 400
          height: 300
          x: Continent
          y: c1
          headers:
            Content-Type: image/svg+xml
        barchart-custom:
          format: sns
          chart: barplot
          ext: png
          dpi: 48
          width: 300
          height: 200
          x: "{xcol}"
          y: "{ycol}"
          headers:
            Content-Type: image/png
        barchart-custom-size:
          format: sns
          chart: barplot
          ext: png
          x: "{xcol}"
          y: "{ycol}"
          width: "{width}"
          height: 200
          headers:
            Content-Type: image/png
    cache:
      expiry: { duration: 3600 }

  formhandler-categorical:
    pattern: /$YAMLURL/categorical
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      default:
        _format: custom
      formats:
        custom:
          format: sns
          chart: "{chart}"
          ext: png
          x: "{xcol}"
          y: "{ycol}"
          dpi: 72
          width: "{width}"
          height: "{height}"
          headers:
            Content-Type: image/png
        facet:
          format: sns
          chart: "{chart}"
          ext: png
          x: "{xcol}"
          y: "{ycol}"
          hue: "{hue}"
          dpi: 72
          width: "{width}"
          height: "{height}"
          headers:
            Content-Type: image/png
    cache:
      expiry: { duration: 3600 }

  formhandler-numerical:
    pattern: /$YAMLURL/numerical
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/flags.csv
      function: data.select_dtypes(include=[numpy.number])
      formats:
        matrix:
          format: sns
          chart: "{chart}"
          ext: png
          dpi: 72
          width: "{width}"
          height: "{height}"
          headers:
            Content-Type: image/png
    cache:
      expiry: { duration: 3600 }

  formhandler-dir:
    pattern: /$YAMLURL/dir
    handler: FormHandler
    kwargs:
      url: $YAMLPATH/

  formhandler-schema:
    pattern: /$YAMLURL/schema
    handler: FormHandler
    kwargs:
      url: sqlite:///$YAMLPATH/database.sqlite3
      table: profile
      id: id
      columns:
        user: TEXT # Use any SQL type allowed by DB
        password: VARCHAR(40) # including customzations
        age:
          type: INTEGER # You can also specify as a dict
          nullable: true # Allows NULL values for this field
          default: 0 # that default to zero
        id:
          type: INTEGER # Define an integer ID column
          primary_key: true # as a primary key
          autoincrement: true # that auto-increments

schedule:
  data-setup-flags-db:
    function: pandas.read_csv(r'$YAMLPATH/flags.csv').to_sql(
      'flags',
      sqlalchemy.create_engine('sqlite:///$YAMLURL/database.sqlite3'),
      if_exists='replace', index=False)
    startup: true