"""PROPER Class to retrieve data.

Class for generating SQL queries for the Proper feed.
"""

import datetime

import smart_open

from feed_sender.flows.proper_changed_releases import config
from feed_sender.util import mysql
from feed_sender.util import proper_common
from feed_sender.util.aws import s3


def _convert(original):
    """Convert transformed values for printing to CSV.

    Args:
        original (str): Transformed string.

    Returns:
        str: Further string conversion for CSV printing.
    """
    if original is None or original == '':
        return '""'

    transformed = str(original).replace('"', '""').strip()
    return '\"{transformed}\"'.format(transformed=transformed)


class Proper:
    """SQL generator for generating data for Proper feed."""

    def __init__(self, cutoff):
        """Initialize class info to be persisted."""
        self.art_relations_db = config.ART_RELATIONS_DB
        self.physical_delivery_db = config.PHYSICAL_DELIVERY_DB
        self.direct_delivery_db = config.DIRECT_DELIVERY_DB
        self.store_id = config.STORE_ID
        self.cutoff = cutoff
        self.db_connection = None
        self._release_ids = None
        self.total_releases = 0

    def fetch_products(self, s3path, job_ids_filename):
        """Get filtered Proper products.

        Args:
            s3path (str): S3 path to files stored.
            job_ids_filename (str): Filename for list of release_ids.
        """
        parsed_ids = proper_common.get_ids_from_s3(
            s3path, job_ids_filename)

        self._release_ids = parsed_ids.get('release_ids')
        self.total_releases = len(self._release_ids)

    def changed_products_query(self):
        """Get products that have changed.

        Get products that have changed and have yet to be delivered to Proper.

        Returns:
            list(DictCursor): List of database rows where each element
                is a product with changes to be delivered to Proper.
        """
        sql = """
          SELECT
              ppch.product_id,
              r.product_code,
              ppch.field_name,
              ppch.new_price,
              ppch.new_sale_start_date,
              ppch.new_deletion_status,
              ppch.date_changed,
              ppch.artworkpath
              FROM {art_relations_db}.product_physical_change_history AS ppch
              JOIN {art_relations_db}.releases AS r
                  ON ppch.product_id = r.release_id
              JOIN {art_relations_db}.artist_info a
                ON r.artist_id = a.artist_id
              JOIN {art_relations_db}.vw_active_vendor_contract vw
                ON a.vendor_id = vw.vendor_id
              LEFT JOIN {art_relations_db}.release_dms_master_restriction rdmr
                ON rdmr.customer_master_master_id = {store_id}
                AND rdmr.release_id = r.release_id
              LEFT JOIN {art_relations_db}.vendor_dms_master_restriction vdmr
                ON vw.vendor_contract_id = vdmr.vendor_contract_id
                AND vdmr.customer_master_master_id = {store_id}
              WHERE EXISTS (
                SELECT 0
                FROM physical_delivery.product_physical_feed_delivery_history
                AS ppfdh
                WHERE ppch.product_id = ppfdh.product_id
                AND ppfdh.supply_chain = 'PROPER'
                )
                AND ppch.date_changed < '{cutoff}'
                AND ppch.delivered = 'N'
                AND rdmr.restriction_id is NULL
                AND vdmr.restriction_id is NULL
                AND
                (
                  (
                    ppch.`store_id` = {store_id}
                    AND ppch.`field_name` = 'wholesale_price'
                  )
                  OR ppch.`store_id` IS NULL
                )
              ORDER BY ppch.id
        """.format(art_relations_db=self.art_relations_db,
                   store_id=config.STORE_ID,
                   cutoff=self.cutoff)

        return mysql.execute_query(self.db_connection, sql)

    def _changed_product_data_query(self):
        """Retrieve data about the products that have changed.

        Returns:
            list(DictCursor): List of database rows where each element
                is a product with changes to be delivered to Proper.
        """
        if self.total_releases > 0:
            sql = """
                SELECT
                  ppch.product_id,
                  r.product_code,
                  ppch.field_name,
                  ppch.new_price,
                  ppch.new_sale_start_date,
                  ppch.new_deletion_status,
                  ppch.date_changed,
                  ppch.artworkpath
                  FROM {art_relations_db}.product_physical_change_history
                    AS ppch
                  JOIN {art_relations_db}.releases AS r
                    ON ppch.product_id = r.release_id
                  WHERE ppch.delivered = 'N'
                    AND r.release_id IN({release_ids})
                    AND
                    (
                      (
                        ppch.`store_id` = {store_id}
                        AND ppch.`field_name` = 'wholesale_price'
                      )
                      OR ppch.`store_id` IS NULL
                    )
                  ORDER BY ppch.id
            """.format(
                release_ids=','.join(str(x) for x in self._release_ids),
                store_id=config.STORE_ID,
                art_relations_db=self.art_relations_db)

            return mysql.execute_query(self.db_connection, sql)
        else:
            return {}

    def squash_records(self, records):
        """Squash all the changes of a single product in one record.

        Args:
            list(DictCursor): List of database rows where row represents a
                change in a product.

        Returns:
            list: List of rows where each row has all changes associated
                with a product.
        """
        # Map field name to DB column names
        field_to_column = {
            'wholesale_price': 'new_price',
            'deletions': 'new_deletion_status',
            'sale_start_date': 'new_sale_start_date',
            'artwork': 'artworkpath'
        }

        squashed_records = {}

        for record in records:

            product_id = record.get('product_id')
            if product_id in squashed_records:

                source_column_name = field_to_column.get(
                    record.get('field_name'))

                squashed_records.get(
                    product_id)[source_column_name] = record.get(
                    source_column_name)

            else:
                squashed_records[product_id] = record

        return list(squashed_records.values())

    def save_release_ids_to_s3(self, records, s3path, release_ids_file):
        """Persist list of Proper release ids and its last updated date in S3.

        Args:
            records (list(DictCursor)): List of changed releases records.
            s3path (str): S3 bucket for file defined in settings.py.
            release_ids_file (str): Where release ids are saved.

        Returns:
            bool: True if success. False otherwise.
        """
        release_ids = [row.get('product_id') for row in records]
        full_path = '{path}/{file}'.format(
            path=s3path, file=release_ids_file)

        with smart_open.smart_open(full_path, 'wb') as fout:

            if not release_ids:
                fout.write(' ')

            for release_id in release_ids:
                if release_id:
                    fout.write(
                        '{id}\n'.format(id=str(release_id)))

        return True

    def _write_header_row(self, fout):
        """Write a header row of Proper column names.

        Args:
            fout (io): Open file handle for writing to.
        """
        header_row = ','.join(
            [i.get('proper') for i in config.CHANGED_RELEASES_MAP])
        fout.write('{}\r\n'.format(header_row))

    def convert_releases_to_csv(self, records, s3path, filename):
        """Convert field values retrieved from MySQL and write to CSV.

        Args:
            records (list(DictCursor)): List of changed releases records.
            s3path (str): S3 bucket for releases defined in config.py.
            filename (str): Name as specified by Proper defined in config.py.
        """
        full_path = '{path}/{file}'.format(path=s3path, file=filename)

        with smart_open.smart_open(full_path, 'wb') as fout:

            self._write_header_row(fout)

            for record in records:
                row_to_write = []
                for field_map in config.CHANGED_RELEASES_MAP:

                    field = field_map.get('orchard')
                    orch_val = record.get(field)

                    if 'transform' in field_map:
                        transformed = field_map.get('transform')(orch_val)
                        row_to_write.append(_convert(transformed))
                    else:
                        row_to_write.append(_convert(orch_val))

                row_str = ','.join(str(x) for x in row_to_write)
                fout.write('{}\r\n'.format(row_str))
        return

    def _update_feed_delivery_history_table(self, s3_file_contents):
        """Update product_physical_feed_delivery_history table.

        Set `date_delivered` to current time.

        Args:
            s3_file_contents (list): List of release ids we delivered.

        Returns:
            bool: True if success. False otherwise.
        """
        current_time = datetime.datetime.now().isoformat()

        parsed_ids = [line for line in s3_file_contents.split('\n')]
        new_rows = []
        for id in parsed_ids:
            new_rows.append("({id}, 'PROPER', 'CHANGE', '{date}')".format(
                id=id, date=current_time))

        sql = (
            'INSERT INTO {physical_delivery_db}.product_physical_'
            'feed_delivery_history(product_id, supply_chain, '
            'delivery_type, date_delivered) VALUES {new_values}').format(
            physical_delivery_db=self.physical_delivery_db,
            new_values=','.join(new_rows))

        written_rows = mysql.execute_write_query(self.db_connection, sql)

        if len(new_rows) != written_rows:
            return False

        return True

    def _update_delivered_flag_on_changes_table(self, s3_file_contents):
        """Update product_physical_change_history table.

        Setting `delivered` to 'Y' if it is a product changed before
        the cutoff.

        Args:
            s3_file_contents (list): List of release ids we delivered.

        Returns:
            bool: True if success. False otherwise.
        """
        parsed_ids = [line for line in s3_file_contents.split('\n')]

        sql = ("""
            UPDATE {art_relations_db}.product_physical_change_history
            SET delivered = '{status}'
            WHERE date_changed < '{cutoff}'
              AND ((store_id = '{store_id}'
                AND field_name = 'wholesale_price')
                OR store_id IS NULL)
              AND product_id IN ({release_ids})""").format(
                art_relations_db=self.art_relations_db,
                status='Y',
                store_id=config.STORE_ID,
                cutoff=self.cutoff,
                release_ids=','.join(str(i) for i in parsed_ids))

        # No need to check for row count affected as 0 may be legitimate
        mysql.execute_write_query(self.db_connection, sql)

        return True

    def update_delivery_history(self, s3path, filename):
        """Update status for releases we just delivered.

        Args:
            s3path (str): S3 bucket for file containing release info.
            filename (str): File containing release/product info.
        """
        # get list of release ids from file
        # sample file contents: release_id_1\nrelease_id_2\n
        full_path = '{path}/{file}'.format(path=s3path, file=filename)

        data_from_s3 = s3.read_s3_object(full_path).decode('utf-8').rstrip()

        if not data_from_s3:
            return True

        connection = self.connect_to_sql(autocommit=False)
        completed = self._update_feed_delivery_history_table(data_from_s3)
        completed = completed and self._update_delivered_flag_on_changes_table(
            data_from_s3)

        if not completed:
            connection.rollback()
            raise Exception('Failed to update delivery history.')

        connection.commit()
        self.close_sql()

        return True

    def connect_to_sql(self, autocommit=True):
        """Connect to MySQL through pyMySQL.

        Returns:
            pyMYSQL connection object.
        """
        self.db_connection = mysql.get_art_db_connection_pymysql(autocommit)
        return self.db_connection

    def close_sql(self):
        """Close pyMYSQL connection.

        Returns:
             bool: True after operation is completed.
        """
        if not self.db_connection:
            self.db_connection.close()

        return