import csv
import json
import logging

import requests
from simple_salesforce import Salesforce

from flows.awal_salesforce import config

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 = None
logger = logging.getLogger(__name__)

logging.basicConfig(level=logging.DEBUG)


# requests_session = requests.Session(verify=False)

def connect() -> Salesforce:
    SALESFORCE_VERSION = '55.0'
    oauth_url = f'https://{config.SF_DOMAIN}.salesforce.com/services/oauth2/token'
    payload = '&'.join([
        f'client_id={config.SF_CONSUMER_KEY}',
        f'client_secret={config.SF_CONSUMER_SECRET}',
        'grant_type=refresh_token',
        f'refresh_token={config.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):
    res = value
    res = res.replace("\\", "\\\\")
    res = res.replace("\n", "\\n")
    res = res.replace("\r", "\\r")
    res = res.replace("\047", "\134\047")  # single quotes
    return res


def gen_fields_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)
    return fields_dds


def gen_create_table_dds(desc, table_name):
    fields_dds = gen_fields_dds(desc)
    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)

    object_type = salesforce_object_name.lower()
    # dds_stmt = gen_create_table_dds(desc, table_name)
    # with open(create_ddl_filename, 'w') as file:
    #     file.write(dds_stmt)
    create_ddl_filename = f'{object_type}_describe.json'
    with open(create_ddl_filename, 'w') as file:
        json.dump(desc, fp=file, indent=4)

    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)

    return create_ddl_filename, csv_filename


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