"""Update subaccount_id mapping in art_relations."""
import csv
import connection
import os
import sys
import config
import queries
import constants as const


FILENAME = os.environ.get('FILENAME')
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))


def update_subaccount(subaccount_ids, logger):
    """Update subaccount_id mapping in art_relations."""
    for subaccount_id, project_ids in subaccount_ids.items():
        projects = ','.join(map(str, project_ids))
        logger.info(
            f'subaccount: {str(subaccount_id)} projects: {projects}')
        update_query = queries.UPDATE_PROJECT_AND_PRODUCT_SUBACCOUNT.format(
            subaccount_id, projects
        )

        db_connection = connection.database_connection()
        cursor = db_connection.cursor()
        cursor.execute(update_query)
        db_connection.commit()
        db_connection.close()
        logger.info('Subaccount mapping completed')


def validate_vendor(subaccount_ids, vendor_id, logger):
    """Check if subaccount and project belong to same vendor"""
    for subaccount_id, project_ids in subaccount_ids.items():
        projects = ','.join(map(str, project_ids))
        vendor_of_subaccount_query = queries.GET_VENDOR_OF_SUBACCOUNT.format(
            subaccount_id
        )
        vendor_of_projects_query = queries.GET_VENDOR_OF_PROJECTS.format(
            projects
        )

        db_connection = connection.database_connection()
        cursor = db_connection.cursor()

        cursor.execute(vendor_of_subaccount_query)
        subaccount_vendor = cursor.fetchone()

        cursor.execute(vendor_of_projects_query)
        project_vendor = cursor.fetchall()
        db_connection.close()

        if len(project_vendor) != len(subaccount_vendor) \
                or project_vendor[0] != subaccount_vendor:
            logger.info('Vendor of subaccount and project do not match')
            return None

        if vendor_id != str(subaccount_vendor[0]):
            logger.info('Vendor of sheet and subaccount vendor do not match')
            return None

    return True


def main(correlation_id=None):
    """Read subaccount_id and project_id from CSV file."""
    logger = config.get_current_logger(correlation_id)
    logger.info('Running script to update subaccount mapping in art_relations')
    logger.info(f'Reading file: : {FILENAME}')
    subaccount_ids = {}
    try:
        with open(FILENAME) as csv_file:
            reader = csv.DictReader(csv_file)
            if const.SUBACCOUNT_ID in reader.fieldnames \
                    and const.PROJECT_ID in reader.fieldnames \
                    and const.VENDOR_ID in reader.fieldnames:
                for row in reader:
                    if row.get(const.SUBACCOUNT_ID) \
                            and row.get(const.PROJECT_ID) \
                            and row.get(const.VENDOR_ID):
                        subaccount_id = row[const.SUBACCOUNT_ID]
                        project_id = row[const.PROJECT_ID]
                        vendor_id = row[const.VENDOR_ID]

                        if not subaccount_ids.get(subaccount_id):
                            subaccount_ids[subaccount_id] = [project_id]
                        else:
                            if project_id \
                                    not in subaccount_ids.get(subaccount_id):
                                subaccount_ids[subaccount_id].append(
                                    project_id
                                )
                    else:
                        subaccount_ids = {}
                        logger.info('Data is missing in file')
                        break
                if subaccount_ids:
                    result = validate_vendor(
                        subaccount_ids, vendor_id, logger
                        )
                    if result:
                        update_subaccount(subaccount_ids, logger)
            else:
                logger.info('Required headers are missing from csv file')
    except Exception as e:
        logger.info(str(e))


if __name__ == '__main__':
    main()