---

base_config: # applied to all DAGs unless overwritten
  dwh_engine: postgres # bigquery # postgres
  dwh_conn_id: dwh # bq # dwh
  airflow_conn_id: airflow
  start_date: 2020-10-01 00:00:00+00:00
  schedule_interval: !!python/object/apply:datetime.timedelta
    - 1 # days
    - 0 # seconds
  schedule_interval_backfill: !!python/object/apply:datetime.timedelta
    - 7
  schedule_interval_future: !!python/object/apply:datetime.timedelta
    - 0
    - 28800
  # For fullcremental loads
  schedule_interval_full_refresh: !!python/object/apply:datetime.timedelta
    - 1 # days
    - 0 # seconds
  schedule_interval_incremental: !!python/object/apply:datetime.timedelta
    - 0 # days
    - 28800 # seconds
  read_right_users:
    - i_dont_exist
  additional_task_args:
    retries: 1
    retry_delay: !!python/object/apply:datetime.timedelta
      - 0
      - 30
    email_on_retry: False
    email_on_failure: False
    # email: ['me+ewahairflowerror@mail.com']

el_dags:

  EL_Aircall:
    dag_strategy: incremental
    el_operator: aircall
    target_schema_name: raw_aircall
    start_date: 2020-11-30 00:00:00+00:00
    operator_config:
      general_config:
        source_conn_id: aircall
        # All endpoints except calls are to be fully refreshed
        extract_strategy: full-refresh
        load_data_from_relative: !!python/object/apply:datetime.timedelta
          - 1  # Reload the previous 24h of data as well in case a call was created but
          # ... not finished at the time of the first loading
        reload_data_from: 2020-11-30 00:00:00+00:00
        wait_for_seconds: 120
      tables:
        users: {}
        calls:  # Only calls are to be loaded incrementally
          extract_strategy: incremental
        numbers: {}
        contacts: {}
        tags: {}
        teams: {}

  EL_Aircall_FR:
    dag_strategy: fr
    el_operator: aircall
    target_schema_name: raw_aircall_fr
    operator_config:
      general_config:
        source_conn_id: aircall
      tables:
        users: {}
        numbers: {}
        contacts: {}
        tags_table:
          resource: tags
        teams: {}

  EL_Airflow:
    dag_strategy: atomic
    el_operator: airflow
    target_schema_name: raw_airflow_metadata
    operator_config:
      tables:
        {% for conn_name in ["airflow_api", "airflow_via_ssh_vpn", "airflow_via_ssh", "airflow_via_url", "airflow_multi_hops"] %}
          {{ conn_name }}_dags:
            endpoint: dags
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_task_instance:
            endpoint: taskInstance
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_dag_runs:
            endpoint: dagRuns
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_connections:
            endpoint: connections
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_variables:
            endpoint: variables
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_health:
            endpoint: health
            source_conn_id: "{{ conn_name }}"
        {% endfor %}

  EL_Airflow_Subs:
    dag_strategy: atomic
    el_operator: airflow
    target_schema_name: raw_airflow_subs
    operator_config:
      tables:
        {% for conn_name in ["airflow_api"] %}
          {{ conn_name }}_dags:
            endpoint: dags
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_task_instance:
            endpoint: taskInstance
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_dag_runs:
            endpoint: dagRuns
            extract_strategy: subsequent
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_connections:
            endpoint: connections
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_variables:
            endpoint: variables
            source_conn_id: "{{ conn_name }}"
          {{ conn_name }}_health:
            endpoint: health
            source_conn_id: "{{ conn_name }}"
        {% endfor %}

  EL_Amazon_Reporting:
    dag_strategy: incremental
    start_date: 2022-07-23 00:00:00+00:00
    el_operator: amazon_reporting
    target_schema_name: raw_sp_api_reporting
    schedule_interval_backfill: !!python/object/apply:datetime.timedelta
      - 1
    schedule_interval_future: !!python/object/apply:datetime.timedelta
      - 0
      - 28800
    operator_config:
      general_config:
        source_conn_id: seller_central_nl
        marketplace_region: DE
        reload_data_from: 2022-07-22 00:00:00+00:00
        # load_data_from: 2022-05-27 00:00:00+00:00
      tables:
        report:
          load_data_from_relative: !!python/object/apply:datetime.timedelta
            - 7  # Reload the previous 7 days
          report_name: sales_and_traffic
          report_options:
            asinGranularity: CHILD
        orders:
          report_name: orders
          marketplace_region:
            - DE
            # - NL

  EL_BigQuery:
    dag_strategy: fr
    el_operator: bq
    target_schema_name: raw_bq_fr
    operator_config:
      general_config:
        source_conn_id: bq
        source_schema_name: ds1
        source_database_name: gemma-287313
      tables:
        weights:
          source_table_name: bijan_weight

  #EL_BigQuery_events: !yml_from_file secret_dag_bq.yml
  #EL_BigQuery_SQL: !yml_from_file secret_dag_bq_sql.yml
  # EL_BigQuery_events_sharded: !yml_from_file secret_dag_bq_sharded.yml

  EL_Braze:
    dag_strategy: atomic
    el_operator: braze
    target_schema_name: raw_braze
    operator_config:
      general_config:
        source_conn_id: braze
        extract_strategy: subsequent
      tables:
        campaigns: {}
        canvas:
          object: canvases
        cards: {}

  EL_DynamoDB:
    dag_strategy: full-refresh
    el_operator: dynamodb
    target_schema_name: raw_dynamodb
    operator_config:
      general_config:
        source_conn_id: dynamodb_conn
        region_name: eu-central-1
      tables:
        admins:
          partition_key: email
        all_users:
          partition_key: email
          source_table_name: users
        invoices:
          partition_key: invoice_id
          sort_key: status
          source_table_name: invoice-reference

  # EL_Facebook: !yml_from_file secret_dag_facebook.yml

  EL_DynamoDB_2:
    dag_strategy: full-refresh
    el_operator: dynamodb
    target_schema_name: raw_dynamodb
    operator_config:
      general_config:
        source_conn_id: dynamodb_conn_2
        region_name: eu-central-1
      tables:
        admin_accounts:
          source_table_name: prod-admin-portal-accounts-storage
          partition_key: username


  EL_FX:
    dag_strategy: full refresh
    el_operator: fx
    target_schema_name: raw_fx
    operator_config:
      general_config:
        frequency: daily
        load_data_from: 2016-12-01 00:00:00+00:00 # tbd airflow 2.0 issue
      tables:
        {% for currency in airflow_variables['currencies'].split(",") %}
          daily_fx_rates_usd_{{ currency|lower() }}:
            index_columns:
              - low
              - high
            currency_pair:
              - USD
              - {{ currency|upper() }}
        {% endfor %}

  # EL_GAds: !yml_from_file secret_dag_gads.yml
  # EL_GAds_subs: !yml_from_file secret_dag_gads_subsequent.yml

  # EL_GA: !yml_from_file secret_dag_google_analytics.yml
  # EL_GA_hourly: !yml_from_file secret_dag_google_analytics_hourly.yml
  # EL_GA_client_test: !yml_from_file secret_dag_google_analytics_client_test.yml

  # EL_GMaps: tbd

  # EL_GCS: !yml_from_file secret_dag_gcs.yml

  # EL_GSheets: !yml_from_file secret_dag_gsheets.yml

  EL_Hubspot:
    dag_strategy: fr
    el_operator: hubspot
    target_schema_name: raw_hubspot
    operator_config:
      general_config:
        source_conn_id: hubspot
      tables:
        engagements: {}
        calls: {}
        emails: {}
        meetings: {}
        notes: {}
        tasks: {}
        companies:
          associations:
            - deals
            - contacts
        contacts:
          properties:
            - new_ip_address
            - new_referer
            - new_user_agent
            - new_utm_campaign
            - new_utm_content
        deals:
          associations: line_items
          properties:
            - amount
            - dealstage
            - dealtype
            - ignoreme
          exclude_properties:
            - ignoreme
        feedback_submissions: {}
        line_items: {}
        pipelines: {}
        products: {}
        tickets: {}
        quotes_table:
          object: quotes
        properties: {}
        owners: {}

  EL_Hubspot_all_assocs:
    dag_strategy: fr
    el_operator: hubspot
    target_schema_name: raw_hubspot_assocs
    operator_config:
      general_config:
        source_conn_id: hubspot
        associations: all
      tables:
        engagements: {}
        calls: {}
        emails: {}
        meetings: {}
        notes: {}
        tasks: {}
        companies: {}
        contacts:
          properties:
            - new_ip_address
            - new_referer
            - new_user_agent
            - new_utm_campaign
            - new_utm_content
        deals:
          properties:
            - amount
            - dealstage
            - dealtype
            - ignoreme
          exclude_properties:
            - ignoreme
        # feedback_submissions: {}
        line_items: {}
        pipelines: {}
        products: {}
        tickets: {}
        quotes: {}
        # properties: {}
        owners: {}

  EL_Infigo:
    dag_strategy: atomic
    el_operator: infigo
    target_schema_name: raw_infigo
    operator_config:
      general_config:
        source_conn_id: infigo
      tables:
        orders: {}
        customers: {}
        print_locations:
          resource: printlocations

  EL_LinkedIn:
    dag_strategy: atomic
    el_operator: linkedin_ads
    target_schema_name: raw_linkedin_ads
    operator_config:
      general_config:
        reload_data_from: 2022-01-01 00:00:00+00:00
        source_conn_id: linkedin
      tables:
        ads_test:
          pivot: campaign
          fields:
            - externalWebsiteConversions
            - dateRange
            - impressions
            - landingPageClicks
            - costInLocalCurrency
            - pivot
            - pivotValue
            - pivotValues

  EL_LinkedIn_Objects:
    dag_strategy: atomic
    el_operator: linkedin
    target_schema_name: raw_linkedin_objects
    operator_config:
      general_config:
        source_conn_id: linkedin
      tables:
        Creatives: {}
        Campaigns: {}
        Accounts: {}
        campaign_groups:
          object_name: CampaignGroups

  EL_Mailchimp:
    dag_strategy: fr
    el_operator: mailchimp
    target_schema_name: raw_mailchimp
    #dwh_conn_id: ssh_dwh
    operator_config:
      general_config:
        source_conn_id: mailchimp
      tables:
        reports:
          resource: reports
        lists:
          resource: lists
        lists_hashed:
          resource: lists
          hash_columns: name
        conversations:
          resource: conversations
        campaigns:
          default_values:
            variate_settings: {"i_am": "a deault"}
          rename_columns:
            type: renamed_type
            web_id: renamed_web_id
          exclude_columns:
            - status
        campaigns_hashed:
          resource: campaigns
          hash_columns:
            - web_id
            - type
            - status
            - sed_time

  EL_Mailingwork:
    dag_strategy: fr
    el_operator: mailingwork
    target_schema_name: raw_mailingwork
    dwh_conn_id: snowflake_conn
    dwh_engine: snowflake
    operator_config:
      general_config:
        source_conn_id: mailingwork
      tables:
        fields:
          endpoint: getfields
        lists:
          endpoint: getlists
        mailings:
          endpoint: getmailings
        media:
          endpoint: getmedia
        export_recipients:
          endpoint: exportrecipients
          primary_key: id
          page_size: 2000
          normal_params:
            "advanced[compact]": 1
          iter_param:
            name: listId
            values:
              - 227
              - 3
              - 2

  # EL_MongoDB: !yml_from_file secret_dag_mongodb.yml
  # EL_MongoDB_2: !yml_from_file secret_dag_mongodb_2.yml
  # EL_MongoDB_3: !yml_from_file secret_dag_mongodb_3.yml
  # EL_MongoDB_4: !yml_from_file secret_dag_mongodb_4.yml
  # EL_MongoDB_5: !yml_from_file secret_dag_mongodb_5.yml

  # EL_MSSQL: !yml_from_file secret_dag_mssql.yml

  # EL_MySQL_Atomic: !yml_from_file secret_dag_mysql_atomic.yml
  # EL_MySQL_Symfony: !yml_from_file secret_dag_mysql.yml

  # EL_Oracle: !yml_from_file secret_dag_oracle_to_snowflake.yml
  # EL_Oracle_2: !yml_from_file secret_dag_oracle_fullcremental.yml
  # EL_Oracle_FR: !yml_from_file secret_dag_oracle_fr.yml
  # EL_Oracle_FR_compress: !yml_from_file secret_dag_oracle_fr_compress.yml

  EL_Personio:
    dag_strategy: atomic
    el_operator: personio
    target_schema_name: raw_personio
    operator_config:
      general_config:
        source_conn_id: personio
      tables:
        employees: {}
        absences: {}
        projects: {}
        attendances: {}

  EL_Pipedrive:
    dag_strategy: fr
    el_operator: pipedrive
    target_schema_name: raw_pd
    operator_config:
      general_config:
        source_conn_id: pipedrive
      tables:
        deals: {}
        activity_fields:
          pipedrive_object: activityFields
        currencies: {}
        leads: {}
        activities: {}
        notes: {}
        dealfields:
          pipedrive_object: dealFields

  EL_PlentyMarkets:
    dag_strategy: idempotent
    el_operator: plentymarkets
    start_date: 2021-05-01 00:00:00+00:00
    target_schema_name: raw_plentymarkets
    operator_config:
      general_config:
        load_data_chunking_timedelta: !!python/object/apply:datetime.timedelta
          - 0
          - 3600
        source_conn_id: plentymarkets
      tables:
        invoices:
          resource: "/rest/orders/documents/invoice"
        account_contacts:
          resource: "/rest/accounts/contacts"
        orders:
          additional_api_call_params:
            with[]:
              - addresses
              - location
              - payments
        order_status_histories:
          resource: "/rest/orders/status-history"

  EL_PlentyMarkets_FR:
    dag_strategy: atomic
    el_operator: plentymarkets
    target_schema_name: raw_plentymarkets_fr
    operator_config:
      general_config:
        source_conn_id: plentymarkets
      tables:
        categories: {}
        items: {}
        invoices:
          resource: "/rest/orders/documents/invoice"
          load_data_chunking_timedelta: !!python/object/apply:datetime.timedelta
            - 7 # days
          load_data_from: 2022-05-01 00:00:00+00:00
          load_data_until: 2022-06-01 00:00:00+00:00
        item_variations:
          resource: "items/variations"
        tickets: {}
        properties: {}
        contact_classes:
          resource: /rest/accounts/contacts/classes
        order_status_all:
          resource: "/rest/orders/statuses/all"
          primary_key: "statusId"

  EL_PlentyMarkets_Subs:
    dag_strategy: atomic
    el_operator: plentymarkets
    target_schema_name: raw_plentymarkets_subs
    operator_config:
      general_config:
        source_conn_id: plentymarkets
        extract_strategy: subsequent
        load_data_chunking_timedelta: !!python/object/apply:datetime.timedelta
          - 1
      tables:
        account_contacts:
          resource: "/rest/accounts/contacts"
          additional_api_call_params:
            createdAtAfter: "2022-01-08"

  EL_Postgres:
    dag_strategy: fr
    el_operator: postgres
    target_schema_name: raw_pg_fr
    operator_config:
      general_config:
        source_conn_id: pg_test_conn
        source_schema_name: public
      tables:
        orders: {}
        users: {}
        deliveries:
          pickling_upload_chunk_size: 1000
        delivery_providers: {}
        delivery_slots: {}
          # exclude_columns:
          #   - preferences

  #EL_Postgres_2:
  #  dag_strategy: full-refresh
  #  el_operator: postgres
  #  target_schema_name: raw_mf
  #  operator_config:
  #    general_config:
  #      source_conn_id: mf
  #      source_schema_name: public
  #    tables:
  #      product_prices:
  #        sql_select_statement: !text_from_file secret_sql_query.sql

  EL_Postgres_3:
    dag_strategy: fr
    el_operator: postgres
    target_schema_name: raw_test_dedupe
    operator_config:
      general_config:
        source_conn_id: dwh
        source_schema_name: public
      tables:
        sumting:
          primary_key: id
          deduplication_before_upload: True
          sql_select_statement: |
            select 1 as id, 123 as x
            union all select 2, 4
            union all select 1, 5
            union all select 3, 6

  EL_Rapidmail:
    dag_strategy: atomic
    el_operator: rapidmail
    target_schema_name: rapidmail
    operator_config:
      general_config:
        source_conn_id: rapidmail
      tables:
        apiusers: {}
        blacklist: {}
        forms: {}
        mailings: {}
        recipientlists: {}
        recipients: {}
        transaction_emails:
          endpoint: "trx/emails"

  EL_Recurly_inc:
    dag_strategy: incremental
    el_operator: recurly
    target_schema_name: raw_recurly_incr
    start_date: 2021-04-01 00:00:00+00:00
    operator_config:
      general_config:
        source_conn_id: recurly
      tables:
        coupons: {}
        items: {}

  EL_Recurly_fr:
    dag_strategy: atomic
    el_operator: recurly
    target_schema_name: raw_recurly_fr
    start_date: 2021-04-01 00:00:00+00:00
    operator_config:
      general_config:
        source_conn_id: recurly
      tables:
        coupons: {}
        items: {}

  # EL_S3: !yml_from_file secret_dag_s3.yml
  # EL_S3_2: !yml_from_file secret_dag_s3_2.yml
  # EL_S3_3: !yml_from_file secret_dag_s3_3.yml
  # EL_S3_4: !yml_from_file secret_dag_s3_4.yml

  EL_Salesforce:
    dag_strategy: incremental
    el_operator: sf
    target_schema_name: raw_sf
    operator_config:
      general_config:
        reload_data_from: 2010-01-01 00:00:00+00:00
        source_conn_id: salesforce
      tables:
        User: {}
        Lead: {}
        LeadHistory: {}

  EL_Salesforce_Subs:
    dag_strategy: atomic
    el_operator: sf
    target_schema_name: raw_sf_subs
    operator_config:
      general_config:
        source_conn_id: salesforce_2
        extract_strategy: subsequent
      tables:
        User: {}
        Lead: {}

  EL_Sevdesk:
    dag_strategy: atomic
    el_operator: sevdesk
    target_schema_name: raw_sevdesk
    operator_config:
      general_config:
        source_conn_id: sevdesk
      tables:
        CheckAccount: {}
        CheckAccountTransaction: {}
        CommunicationWay: {}
        Contact:
          embed: category
        Invoice: {}
        invoice_pos:
          endpoint: InvoicePos
        Order: {}
        order_pos:
          endpoint: OrderPos
        Part: {}
        Voucher: {}
        VoucherPos: {}


  EL_Sharepoint:
    dag_strategy: atomic
    el_operator: sharepoint
    target_schema_name: raw_sp
    operator_config:
      general_config:
        source_conn_id: sharepoint
        file_relative_path: "/sites/BusinessAnalytics/Shared Documents/D_Accounts.xlsx"
      tables:
        accounts:
          sheet_name: "SKR03 Accounts"

  #EL_Shopify:
  #  dag_strategy: atomic
  #  el_operator: shopify
  #  target_schema_name: raw_shopify
  #  operator_config:
  #    general_config:
  #      source_conn_id: shopify
  #      extract_strategy: subsequent
  #    tables:
  #      products: {}
  #      customers: {}
  #      payouts: {}
  #      balance_transactions:
  #        load_strategy: insert_replace
  #      disputes: {}
  #      checkouts: {}
  #      draft_orders: {}

  EL_Stripe:
    dag_strategy: fr
    el_operator: stripe
    target_schema_name: raw_stripe
    operator_config:
      general_config:
        source_conn_id: stripe
      tables:
        Account: {}
        Balance: {} # resource defaults to table name
        balance_transactions:
          resource: BalanceTransaction
        Customer:
          rename_columns:
            currency: currency_name
            delinquent: is_delinquent
        Payout: {}
        Charge:
          expand: # expand a list of strings as required
            - 'data.customer'
        products:
          resource: Product
        refunds:
          resource: Refund
        transactions:
          resource: issuing.Transaction

  # EL_Zendesk: tbd

  Update_GSheet:
    dwh_engine: gsheets
    dwh_conn_id: google_service_account_key
    dag_strategy: fr
    el_operator: postgres
    target_schema_name: 1peh2ztnhqDJ98Qmp8PK0FMmJxJMzTaLKWUq37cDfujA
    schedule_interval: !!python/object/apply:datetime.timedelta
      - 0
      - 60 # every minute
    operator_config:
      general_config:
        add_metadata: False
        source_conn_id: dwh
        source_schema_name: public
      tables:
        Upload Test:
          sql_select_statement: "select 1 as a, 2 as b, 'c' as c union all select 3, 4, 'd'"

...