#!/usr/bin/env python
# coding: utf-8

# In[1]:


import cloudinary
import cloudinary.uploader
import cloudinary.api
import json
import pandas as pd
from google.oauth2 import service_account
import pandas_gbq

config = cloudinary.config(
        cloud_name = "###",
        api_key = "###",
        api_secret = "###"
    )


# In[2]:


credentials = service_account.Credentials.from_service_account_file('data/credentials.json')


# In[3]:


# Update the in-memory credentials cache (added in pandas-gbq 0.7.0).
pandas_gbq.context.credentials = credentials

sql = """
SELECT Date, Ad_Creative_Image_URL, Ad_Creative_Video_Picture_URL, Ad_Name, Data_Source_Name
FROM `idr-insights.all_cloudinary.all_cloudinary_view`
WHERE DATE(Date) BETWEEN CURRENT_DATE()-14 and CURRENT_DATE()
AND (Ad_Creative_Image_URL IS NOT NULL OR Ad_Creative_Video_Picture_URL IS NOT NULL);
"""
df = pandas_gbq.read_gbq(sql, project_id="idr-insights", progress_bar_type=None)


# In[4]:


img_df = df.drop(['Ad_Creative_Video_Picture_URL'], axis=1)
vid_df = df.drop(['Ad_Creative_Image_URL'], axis=1)


# In[5]:


img_df = img_df.dropna()
vid_df = vid_df.dropna()


# In[6]:


img_df['Media_Type'] = "Image"
vid_df['Media_Type'] = "Video"


# In[7]:


df = pd.concat([img_df, vid_df.rename(columns={'Ad_Creative_Video_Picture_URL':'Ad_Creative_Image_URL'})], ignore_index=True)


# In[8]:


df = df.sort_values(['Date','Media_Type'], ascending=([False, True])).reset_index(drop=True)
df = df.drop_duplicates(subset='Ad_Name', keep='first').reset_index(drop=True)


# In[9]:


df.loc[df['Data_Source_Name'].str.contains('ASO'), 'Client'] = 'aso'
df.loc[df['Data_Source_Name'].str.contains('Adaptive Sports'), 'Client'] = 'asc'
df.loc[df['Data_Source_Name'].str.contains('BOG'), 'Client'] = 'bog'
df.loc[df['Data_Source_Name'].str.contains('B&C'), 'Client'] = 'bcc'
df.loc[df['Data_Source_Name'].str.contains('CHRISTUS'), 'Client'] = 'cts'
df.loc[df['Data_Source_Name'].str.contains('Fidelity'), 'Client'] = 'fid'
df.loc[df['Data_Source_Name'].str.contains('Folds'), 'Client'] = 'fhf'
df.loc[df['Data_Source_Name'].str.contains('GOSAFE'), 'Client'] = 'gos'
df.loc[df['Data_Source_Name'].str.contains('Idea Ranch'), 'Client'] = 'idr'
df.loc[df['Data_Source_Name'].str.contains('MEAT'), 'Client'] = 'meat'
df.loc[df['Data_Source_Name'].str.contains('Meprolight'), 'Client'] = 'mep'
df.loc[df['Data_Source_Name'].str.contains('Moultrie'), 'Client'] = 'pob'
df.loc[df['Data_Source_Name'].str.contains('REDARC'), 'Client'] = 'red'
df.loc[df['Data_Source_Name'].str.contains('Splatrball'), 'Client'] = 'gam'
df.loc[df['Data_Source_Name'].str.contains('UA - Fish'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('UA - Freedom'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('UA - Hunt'), 'Client'] = 'ua'
df.loc[df['Data_Source_Name'].str.contains('Utica'), 'Client'] = 'usp'
df.loc[df['Data_Source_Name'].str.contains('Bushnell - Standard'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Bushnell Golf'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Blackhawk'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Hoppe'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('RCBS'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Primos'), 'Client'] = 'bsh'
df.loc[df['Data_Source_Name'].str.contains('Wild Sheep'), 'Client'] = 'wsf'


# In[10]:


df["Ad_Name"] = df["Ad_Name"].str.replace(" ", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace(".", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("_", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("[^a-zA-Z0-9 -]", '', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("---", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("--", '-', regex=True)
df["Ad_Name"] = df["Ad_Name"].str.replace("-$", "", regex=True)


# In[11]:


image_url = df['Ad_Creative_Image_URL'].to_list()
ad_name = df['Ad_Name'].to_list()
client = df['Client'].to_list()


# In[12]:


for img, client, path in zip(image_url, client, ad_name):
    try:
        cloudinary.uploader.upload("%s" % img, public_id="%s/" % client + "%s" % path, unique_filename = False, overwrite = False)
    except:
        pass