!pip install git+https://github.com/google/starthinker


"""
Explanation: 1. Install Dependencies
First install the libraries needed to execute recipes, this only needs to be done once, then click play.
End of explanation
"""


CLOUD_PROJECT = 'PASTE PROJECT ID HERE'

print("Cloud Project Set To: %s" % CLOUD_PROJECT)


"""
Explanation: 2. Get Cloud Project ID
To run this recipe requires a Google Cloud Project, this only needs to be done once, then click play.
End of explanation
"""


CLIENT_CREDENTIALS = 'PASTE CREDENTIALS HERE'

print("Client Credentials Set To: %s" % CLIENT_CREDENTIALS)


"""
Explanation: 3. Get Client Credentials
To read and write to various endpoints requires downloading client credentials, this only needs to be done once, then click play.
End of explanation
"""


FIELDS = {
  'recipe_slug': '',  # Place where tables will be created in BigQuery.
  'dv360_advertisers': '',  # Comma seperated.
  'cm_account': '',  # Comma seperated.
  'cm_advertiser': '',  # Comma seperated.
  'dv360_advertiser': '',  # Comma seperated.
  'dv360_campaign': '',  # Comma seperated.
}

print("Parameters Set To: %s" % FIELDS)


"""
Explanation: 4. Enter CM To BigQuery IO Synch Parameters
Migrate CM Placements to DV360 IO.
 1. Fill in the CM accont information.
 1. Fill in the DV360 accont information.
 1. Click the 'Save' button to save configuration.
 1. Click the 'Run Now' button to create the workflow.
 1. Visit <b>BigQuery->UNDEFINED->UNDEFINED->DV360_IO_REVIEW</b> to check for updates.
 1. Visit <b>BigQuery->UNDEFINED->UNDEFINED->DV360_IO_INSERT</b> to approve the updates.
Modify the values below for your use case, can be done multiple times, then click play.
End of explanation
"""


from starthinker.util.project import project
from starthinker.script.parse import json_set_fields

USER_CREDENTIALS = '/content/user.json'

TASKS = [
  {
    'dataset': {
      'auth': 'user',
      'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}}
    }
  },
  {
    'sheets': {
      'auth': 'user',
      'sheet': 'BB Demo',
      'tab': 'Rules',
      'range': 'A1:C',
      'header': True,
      'out': {
        'auth': 'user',
        'bigquery': {
          'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}},
          'table': 'Rules'
        }
      }
    }
  },
  {
    'dv360_api': {
      'auth': 'user',
      'endpoints': [
        'advertisers.insertionOrders'
      ],
      'advertisers': {
        'single_cell': True,
        'values': {'field': {'name': 'dv360_advertisers','kind': 'integer_list','description': 'Comma seperated.'}}
      },
      'out': {
        'auth': 'user',
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}}
      }
    }
  },
  {
    'dcm_api': {
      'auth': 'user',
      'endpoints': [
        'campaigns',
        'placements',
        'placementGroups'
      ],
      'accounts': {
        'values': {'field': {'name': 'cm_account','kind': 'integer_list','description': 'Comma seperated.'}}
      },
      'advertisers': {
        'values': {'field': {'name': 'cm_advertiser','kind': 'integer_list','description': 'Comma seperated.'}}
      },
      'out': {
        'auth': 'user',
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}}
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'legacy': False,
        'parameters': [
          {'field': {'name': 'dv360_advertiser','kind': 'integer_list','description': 'Comma seperated.'}},
          {'field': {'name': 'dv360_campaign','kind': 'integer_list','description': 'Comma seperated.'}},
          {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be written in BigQuery.'}},
          {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be written in BigQuery.'}},
          {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be written in BigQuery.'}}
        ],
        'query': "SELECT   [PARAMETER] AS advertiserId,   [PARAMETER] AS campaignId,   REGEXP_REPLACE(CM_P.name, '_1X1.*', '') AS displayName,   'ENTITY_STATUS_DRAFT' AS entityStatus,   STRUCT(     'PACING_PERIOD_FLIGHT' AS pacingPeriod,     'PACING_TYPE_AHEAD' AS pacingType   ) AS pacing,   STRUCT(     false AS unlimited,     'TIME_UNIT_DAYS' AS timeUnit,     1 AS timeUnitCount,     CASE       WHEN CM_P.name LIKE '%FF%' AND CM_P.name LIKE '%PZN%'THEN 6       WHEN CM_P.name LIKE '%FF%' THEN 3       WHEN CM_P.name LIKE '%HOL%' THEN 6       WHEN CM_P.name LIKE '%BTC%' THEN 3     END AS maxImpressions   ) AS frequencyCap,   STRUCT(     'PERFORMANCE_GOAL_TYPE_CPM' AS performanceGoalType,     '10' AS performanceGoalAmountMicros   ) AS performanceGoal,   STRUCT(     'BUDGET_UNIT_CURRENCY' AS budgetUnit,     'INSERTION_ORDER_AUTOMATION_TYPE_BUDGET' AS automationType,     [       STRUCT(        '10000' AS budgetAmountMicros,        '' AS description,        STRUCT (          STRUCT (            EXTRACT(YEAR FROM GREATEST(CM_C.startDate, CURRENT_DATE())) AS year,            EXTRACT(MONTH FROM GREATEST(CM_C.startDate, CURRENT_DATE())) AS month,            EXTRACT(DAY FROM GREATEST(CM_C.startDate, CURRENT_DATE())) AS day          ) AS startDate,          STRUCT (            EXTRACT(YEAR FROM CM_C.endDate) AS year,            EXTRACT(MONTH FROM CM_C.endDate) AS month,            EXTRACT(DAY FROM CM_C.endDate) AS day          ) AS endDate        ) AS dateRange       )     ] AS budgetSegments   ) AS budget,   STRUCT(     STRUCT(       '10' AS bidAmountMicros     ) AS fixedBid   ) AS bidStrategy FROM `[PARAMETER].CM_placements` As CM_P LEFT JOIN `[PARAMETER].CM_campaigns` As CM_C ON CM_P.campaignId=CM_C.id LEFT JOIN `[PARAMETER].CM_placementGroups` As CM_G ON CM_P.placementGroupId=CM_G.id WHERE CM_P.advertiserID=4461789 AND CM_P.name LIKE 'PKG%' /*AND CM_G.placementGroupType = 'PACKAGE' */ ORDER BY displayName ;"
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}},
        'view': 'DV360_IO_LOGIC'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'legacy': False,
        'parameters': [
          {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be written in BigQuery.'}}
        ],
        'query': 'SELECT STRUCT(   ROW_NUMBER() OVER() AS NUMBER,   displayName IN (SELECT displayName FROM `[PARAMETER].DV360_advertisers_insertionOrders`) AS DUPLICATE ) AS PREVIEW, * FROM `[PARAMETER].DV360_IO_LOGIC` ORDER BY displayName ;'
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}},
        'view': 'DV360_IO_PREVIEW'
      }
    }
  },
  {
    'bigquery': {
      'auth': 'user',
      'from': {
        'legacy': False,
        'parameters': [
          {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be written in BigQuery.'}}
        ],
        'query': ' SELECT * EXCEPT(PREVIEW) FROM `[PARAMETER].DV360_IO_PREVIEW` WHERE PREVIEW.NUMBER IN(0,0,0,0,0) AND PREVIEW.DUPLICATE=False LIMIT 1 ;'
      },
      'to': {
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}},
        'view': 'DV360_IO_INSERT'
      }
    }
  },
  {
    'dv360_api': {
      'auth': 'user',
      'bigquery': {
        'auth': 'user',
        'dataset': {'field': {'name': 'recipe_slug','kind': 'string','description': 'Place where tables will be created in BigQuery.'}},
        'as_object': True,
        'table': 'DV360_IO_INSERT'
      },
      'insert': 'advertisers.insertionOrders'
    }
  }
]

json_set_fields(TASKS, FIELDS)

project.initialize(_recipe={ 'tasks':TASKS }, _project=CLOUD_PROJECT, _user=USER_CREDENTIALS, _client=CLIENT_CREDENTIALS, _verbose=True, _force=True)
project.execute(_force=True)


"""
Explanation: 5. Execute CM To BigQuery IO Synch
This does NOT need to be modified unles you are changing the recipe, click play.
End of explanation
"""


