name: grant-expenses
title: Grant Expenses
category: personal
databases:
  default:
    databaseId: default
    tables:
      expenses: null
      expense-categories: null
datasources:
  data-expenses:
    type: datasource.sqlite
    datasourceId: data-expenses
    instanceId: data-expenses
    options:
      provider: DATA_PROVIDER_DYNAMIC
      entities:
        - expenses
      query: |2-
      
                SELECT 
                  id,
                  json_extract(data, '$.title') as title,
                  json_extract(data, '$.amount') as amount,
                  json_extract(data, '$.category') as category,
                  json_extract(data, '$.date') as date,
                  json_extract(data, '$.description') as description,
                  timestamp,
                  data
                FROM [expenses] 
                ORDER BY json_extract(data, '$.date') DESC
      jsonProperties:
        - data
  data-expense-categories:
    type: datasource.static
    datasourceId: data-expense-categories
    instanceId: data-expense-categories
    options:
      data:
        - id: food
          name: Food & Dining
          icon: restaurant
        - id: transport
          name: Transportation
          icon: car
        - id: accommodation
          name: Accommodation
          icon: hotel
        - id: office
          name: Office Supplies
          icon: briefcase
        - id: technology
          name: Technology
          icon: laptop
        - id: entertainment
          name: Entertainment
          icon: ticket
        - id: health
          name: Health & Medical
          icon: medical-bag
        - id: education
          name: Education & Training
          icon: academic-cap
        - id: other
          name: Other
          icon: dots-horizontal
jigs:
  home:
    type: jig.default
    jigId: home
    title: Grant's Expenses
    datasources:
      expense-stats:
        type: datasource.sqlite
        datasourceId: expense-stats
        instanceId: expense-stats
        options:
          provider: DATA_PROVIDER_DYNAMIC
          entities:
            - expenses
          query: "

            \      SELECT\ 

            \        COUNT(*) as total_expenses,

            \        SUM(json_extract(data, '$.amount')) as total_amount,

            \        AVG(json_extract(data, '$.amount')) as avg_amount,

            \        MAX(json_extract(data, '$.amount')) as max_amount

            \      FROM [expenses]

            \    "
      recent-expenses:
        type: datasource.sqlite
        datasourceId: recent-expenses
        instanceId: recent-expenses
        options:
          provider: DATA_PROVIDER_DYNAMIC
          entities:
            - expenses
          query: "

            \      SELECT\ 

            \        id,

            \        json_extract(data, '$.title') as title,

            \        json_extract(data, '$.amount') as amount,

            \        json_extract(data, '$.category') as category,

            \        json_extract(data, '$.date') as date

            \      FROM [expenses]

            \      ORDER BY json_extract(data, '$.date') DESC

            \      LIMIT 5

            \    "
    actions:
      - children:
          - type: action.go-to
            instanceId: add-expense
            options:
              title: Add Expense
              linkTo: jig-add-expense
          - type: action.go-to
            instanceId: view-all
            options:
              title: View All
              linkTo: jig-expense-list
    children:
      - instanceId: overview-card
        type: component.card
        options:
          children:
            - instanceId: total-count
              type: component.text-field
              options:
                label: Total Expenses
                value: "@ctx.datasources.expense-stats.total_expenses"
            - instanceId: total-amount
              type: component.text-field
              options:
                label: Total Amount
                value: =$formatCurrency(@ctx.datasources.expense-stats.total_amount)
      - instanceId: recent-list
        type: component.list
        options:
          isHorizontal: false
          isHorizontalScrollIndicatorHidden: true
          isVerticalScrollIndicatorHidden: true
          maximumItemsToRender: 10
          item:
            instanceId: recent-expense-item
            type: component.list-item
            options:
              title: =@ctx.current.item.title
              subtitle: =@ctx.current.item.category & " • " & @ctx.current.item.date
              onPress:
                type: action.go-to
                instanceId: edit-expense
                options:
                  linkTo: jig-edit-expense
                  parameters:
                    $expenseId: =@ctx.current.item.id
          data: =@ctx.datasources.recent-expenses
  jig-expense-list:
    type: jig.default
    jigId: jig-expense-list
    title: My Expenses
    datasources:
      expenses-summary:
        type: datasource.sqlite
        datasourceId: expenses-summary
        instanceId: expenses-summary
        options:
          provider: DATA_PROVIDER_DYNAMIC
          entities:
            - expenses
          query: "

            \      SELECT\ 

            \        json_extract(data, '$.category') as category,

            \        SUM(json_extract(data, '$.amount')) as total_amount,

            \        COUNT(*) as count

            \      FROM [expenses]

            \      GROUP BY json_extract(data, '$.category')

            \      ORDER BY total_amount DESC

            \    "
    actions:
      - children:
          - type: action.go-to
            instanceId: add-expense
            options:
              title: Add New
              linkTo: jig-add-expense
    children:
      - instanceId: expense-list
        type: component.list
        options:
          isHorizontal: false
          isHorizontalScrollIndicatorHidden: true
          isVerticalScrollIndicatorHidden: true
          maximumItemsToRender: 10
          item:
            instanceId: expense-item
            type: component.list-item
            options:
              title: =@ctx.current.item.title
              subtitle: =@ctx.current.item.category & " • " & @ctx.current.item.date
              description: =@ctx.current.item.description
              onPress:
                type: action.go-to
                instanceId: edit-expense
                options:
                  linkTo: jig-edit-expense
                  parameters:
                    $expenseId: =@ctx.current.item.id
          data: =@ctx.datasources.data-expenses
      - instanceId: summary-card
        type: component.card
        options:
          children:
            - instanceId: summary-title
              type: component.text-field
              options:
                label: Summary by Category
                value: Summary by Category
            - instanceId: summary-list
              type: component.list
              options:
                isHorizontal: false
                isHorizontalScrollIndicatorHidden: true
                isVerticalScrollIndicatorHidden: true
                maximumItemsToRender: 10
                item:
                  instanceId: summary-item
                  type: component.list-item
                  options:
                    title: =@ctx.current.item.category
                    subtitle: =@ctx.current.item.count & " expenses"
                data: =@ctx.datasources.expenses-summary
  jig-add-expense:
    type: jig.default
    jigId: jig-add-expense
    title: Add New Expense
    actions:
      - children:
          - type: action.action-list
            instanceId: save-expense
            options:
              title: Save Expense
              actions:
                - type: action.execute-entity
                  instanceId: insert-expense
                  options:
                    provider: DATA_PROVIDER_DYNAMIC
                    entity: expenses
                    method: save
                    data: |-
                      ={
                            "title": @ctx.form.title,
                            "amount": @ctx.form.amount,
                            "category": @ctx.form.category,
                            "date": @ctx.form.date,
                            "description": @ctx.form.description,
                            "timestamp": @ctx.now
                          }
                - type: action.go-back
                  instanceId: go-back
              isSequential: true
    children:
      - instanceId: expense-form
        type: component.form
        options:
          children:
            - instanceId: title
              type: component.text-field
              options:
                label: Title
                isRequired: true
            - instanceId: amount
              type: component.number-field
              options:
                label: Amount
                value: 0
                isRequired: true
            - instanceId: category
              type: component.dropdown
              options:
                label: Category
                data: =@ctx.datasources.data-expense-categories
                isMultiple: false
                item:
                  instanceId: category-item
                  type: component.dropdown-item
                  options:
                    title: =@ctx.current.item.name
                    value: =@ctx.current.item.id
                isRequired: true
            - instanceId: date
              type: component.date-picker
              options:
                label: Date
                mode: date
                isRequired: true
                value: =@ctx.now
            - instanceId: description
              type: component.text-field
              options:
                label: Description
                isMultiline: true
          isDiscardChangesAlertEnabled: true
  jig-edit-expense:
    type: jig.default
    jigId: jig-edit-expense
    title: Edit Expense
    datasources:
      current-expense:
        type: datasource.sqlite
        datasourceId: current-expense
        instanceId: current-expense
        options:
          provider: DATA_PROVIDER_LOCAL
          entities:
            - expenses
          query: "

            \      SELECT id, data, timestamp

            \      FROM [expenses]

            \      WHERE id = @expenseId

            \      LIMIT 1

            \    "
          queryParameters:
            expenseId: =@ctx.jig.parameters.expenseId
          jsonProperties:
            - data
    actions:
      - children:
          - type: action.action-list
            instanceId: update-expense
            options:
              title: Update Expense
              actions:
                - type: action.execute-entity
                  instanceId: update-expense
                  options:
                    provider: DATA_PROVIDER_DYNAMIC
                    entity: expenses
                    method: update
                    data: |-
                      ={
                            "id": @ctx.datasources.current-expense.id,
                            "title": @ctx.form.title,
                            "amount": @ctx.form.amount,
                            "category": @ctx.form.category,
                            "date": @ctx.form.date,
                            "description": @ctx.form.description,
                            "timestamp": @ctx.now
                          }
                - type: action.go-back
                  instanceId: go-back
              isSequential: true
          - type: action.action-list
            instanceId: delete-expense
            options:
              title: Delete Expense
              actions:
                - type: action.execute-entity
                  instanceId: delete-expense
                  options:
                    provider: DATA_PROVIDER_DYNAMIC
                    entity: expenses
                    method: delete
                    data: |-
                      ={
                            "id": @ctx.datasources.current-expense.id
                          }
                - type: action.go-back
                  instanceId: go-back
              isSequential: true
    children:
      - instanceId: edit-expense-form
        type: component.form
        options:
          children:
            - instanceId: title
              type: component.text-field
              options:
                label: Title
                isRequired: true
                value: "@ctx.datasources.current-expense.title"
            - instanceId: amount
              type: component.number-field
              options:
                label: Amount
                value: "@ctx.datasources.current-expense.amount"
                isRequired: true
            - instanceId: category
              type: component.dropdown
              options:
                label: Category
                data: =@ctx.datasources.data-expense-categories
                isMultiple: false
                item:
                  instanceId: category-item
                  type: component.dropdown-item
                  options:
                    title: =@ctx.current.item.name
                    value: =@ctx.current.item.id
                isRequired: true
                value: "@ctx.datasources.current-expense.category"
            - instanceId: date
              type: component.date-picker
              options:
                label: Date
                mode: date
                isRequired: true
                value: "@ctx.datasources.current-expense.date"
            - instanceId: description
              type: component.text-field
              options:
                label: Description
                isMultiline: true
                value: "@ctx.datasources.current-expense.description"
          isDiscardChangesAlertEnabled: true
functions: { }
expressions:
  formatCurrency: |-
    =function($amount) {
        '$' & $formatNumber($amount, '#,##0.00')
      }
widgets: [ ]
actions: { }
tabs:
  home:
    jigId: home
    icon: credit-card
    label: Expenses