import csv
import logging
import os

import requests
from simple_salesforce import Salesforce
from snowflake.connector.converter import SnowflakeConverter
import dotenv

dotenv.load_dotenv('.env')

converter = SnowflakeConverter()

SOAP_TO_SQL_TYPE_MAP = {
    'xsd:double': 'float',
    'xsd:boolean': 'boolean',
    'xsd:string': 'string',
    'xsd:date': 'date',
    'xsd:dateTime': 'datetime',
    'xsd:int': 'number',
    'tns:ID': 'string',
    'urn:address': 'string',
}
DEBUG_LIMIT = 10
logger = logging.getLogger(__name__)

logging.basicConfig(level=logging.DEBUG)

sf_domain = 'login'
sf_consumer_key = os.environ['SF_CONSUMER_KEY']
sf_consumer_secret = os.environ['SF_CONSUMER_SECRET']
sf_refresh_token = os.environ['SF_REFRESH_TOKEN']


# requests_session = requests.Session(verify=False)

def connect() -> Salesforce:
    SALESFORCE_VERSION = os.environ.get('SALESFORCE_VERSION', '55.0')
    oauth_url = f'https://{sf_domain}.salesforce.com/services/oauth2/token'
    payload = '&'.join([
        f'client_id={sf_consumer_key}',
        f'client_secret={sf_consumer_secret}',
        'grant_type=refresh_token',
        f'refresh_token={sf_refresh_token}'
    ])
    headers = {'content-type': 'application/x-www-form-urlencoded'}
    response = requests.request(
        'POST', oauth_url, data=payload, headers=headers, verify=False)
    credentials = response.json()

    sf = Salesforce(
        instance_url=credentials['instance_url'],
        session_id=credentials['access_token'],
        version=SALESFORCE_VERSION)
    return sf


def all_data_for_object_generator(sf, desc):
    fields = [field['name'] for field in desc['fields']]
    fields_joined = ', '.join(fields)
    table_name = desc['name']
    query = f'select {fields_joined} from {table_name}'
    if DEBUG_LIMIT:
        query = f'{query} limit {DEBUG_LIMIT}'
    rows = sf.query_all_iter(query)
    for row in rows:
        yield row


def describe_salesforce_object(sf: Salesforce, salesforce_object_name: str):
    """
    :param sf:
    :param salesforce_object_name: like Opportunity
    :return:
    """
    return getattr(sf, salesforce_object_name).describe()


def escape1(value: str):
    return converter.escape(value)


def gen_create_table_dds(desc):
    fields_dds = []
    for field in desc['fields']:
        field_name = field['name']
        sql_comment = escape1(field['label'])
        soap_type = field['soapType']
        sql_type = SOAP_TO_SQL_TYPE_MAP[soap_type]
        field_dds = f"{field_name} {sql_type} COMMENT '{sql_comment}'"
        fields_dds.append(field_dds)
    # print(field_dds)
    table_name = desc['name'].lower()
    columns = ',\n'.join(fields_dds)
    create_table_sql = f'CREATE OR REPLACE TABLE {table_name} ({columns});'
    return create_table_sql


def gen_csv_values_for_row(desc, row):
    fields_dms = []
    for field in desc['fields']:
        field_name = field['name']
        value = row[field_name]
        soap_type = field['soapType']
        if soap_type == 'xsd:dateTime' and value:
            value = value.replace('T', ' ')  # make it Snowflake default compatible
        fields_dms.append(value)
    return fields_dms


def generate_export_files(sf: Salesforce, salesforce_object_name: str):
    desc = describe_salesforce_object(sf, salesforce_object_name)
    dds_stmt = gen_create_table_dds(desc)
    object_type = salesforce_object_name.lower()
    create_ddl_filename = f'{object_type}_create.sql'
    with open(create_ddl_filename, 'w') as file:
        file.write(dds_stmt)
    data_generator = all_data_for_object_generator(sf, desc)
    csv_filename = f'{object_type}_data.csv'
    with open(csv_filename, 'w') as file:
        csv_writer = csv.writer(file)
        header = [field['name'] for field in desc['fields']]
        csv_writer.writerow(header)
        for row in data_generator:
            row_values = gen_csv_values_for_row(desc, row)
            csv_writer.writerow(row_values)
    s3_path = f's3://dev-feed-drop/AWAL/salesforce/{csv_filename}'
    publish_data = f'aws s3 cp {csv_filename} {s3_path} --profile dev'
    crate_table_stmt = f"Copy and paste SQL statement from {create_ddl_filename}"
    copy_into_table_stmt = f"""
    COPY INTO {object_type}
FROM {s3_path}
FILE_FORMAT = (
    SKIP_HEADER=1
    FIELD_DELIMITER=','
    RECORD_DELIMITER='\n'
    COMPRESSION='NONE'
    TRIM_SPACE=False
    EMPTY_FIELD_AS_NULL=True
    FIELD_OPTIONALLY_ENCLOSED_BY='"'
)
CREDENTIALS=(
  AWS_KEY_ID='**YOUR_VALUE**'
  AWS_SECRET_KEY='**YOUR_VALUE**'
);
    """
    command = f"""
    Now you can import data to snowflake:
    1. {crate_table_stmt}
    
    2. {publish_data}
    
    3. {copy_into_table_stmt}
    """
    print(command)


if __name__ == '__main__':
    sf = connect()
    for entity in ['Account', 'Opportunity', 'Contact']:
        generate_export_files(sf, entity)