"""Database operations for FingerprintRule nodes."""

from connector_neo4j import get_session

from sound_recordings.constants import profiles
from sound_recordings.cypher import fingerprint_rules as cypher
from sound_recordings.utils import features
from sound_recordings.utils import snowflake as snowflake_util
from sound_recordings.utils.neo4j import by

DEFAULT_REL = 'HAS_FINGERPRINT_RULE'
DELETED_PREFIX = 'DELETED_'

# Snowflake stores the anchor type as 'SubAccount', but the app uses 'Subaccount'.
SNOWFLAKE_OBJ_TYPE = {'Subaccount': 'SubAccount'}

SQLLoader = snowflake_util.SQLLoader(__file__)


class CreateException(Exception):
    """Error during create rules."""

    pass


class DeleteException(Exception):
    """Error during delete rules."""

    pass


def get_profile_type_by_id(profile_type_id):
    """Get profile type by profile type id."""
    return profiles.PROFILE_TYPE_BY_ID.get(profile_type_id, 'UnknownProfile')


def get_rules(obj_type, obj_id):
    """Match active rules connected to single object.

    Args:
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node

    Returns:
        dict: obj_node -> rule path as single obj
    """
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.GET_RULES.format(
            obj_type=obj_type,
            rule_rel=DEFAULT_REL
        ),
        obj_id=obj_id
    )
    return [
        {
            'start': x['rel']['start'].to_native() if 'start' in x['rel'] else None,
            'end': x['rel']['end'].to_native() if 'end' in x['rel'] else None,
            'territory': x['rule']['territory'],
            'policy': x['rule']['policy'],
            'service': x['rule']['service'],
            'created_at': x['rel']['createdAt'].to_native(),
            'created_by': x['rel']['createdBy'],
            'last_modified_at': x['rel']['lastModifiedAt'].to_native(),
            'last_modified_by': x['rel']['lastModifiedBy'],
            'element_id': str(x['rel'].element_id)
        }
        for x in results
    ]


def get_bulk_rules(obj_type, obj_ids):
    """Match active rules connected to multiple objects.

    Args:
        obj_type (str): label of anchor node
        obj_ids list(int): unique identifiers of anchor nodes

    Returns:
        dict with node id as keys and corresponding list of rules
    """
    if features.is_has_fingerprint_rule_in_snowflake_enabled():
        return _get_bulk_rules_snowflake(obj_type, obj_ids)
    return _get_bulk_rules_neo4j(obj_type, obj_ids)


def _get_bulk_rules_neo4j(obj_type, obj_ids):
    neo4j_session = get_session()
    results = neo4j_session.run(
        cypher.GET_BULK_RULES.format(
            obj_type=obj_type,
            rule_rel=DEFAULT_REL
        ),
        obj_ids=obj_ids
    )

    rules = dict()
    for obj_id in obj_ids:
        rules[obj_id] = []

    for each in results:
        obj = each['obj']
        details = each['details']
        obj_id = obj['id']

        for detail in details:
            rel = detail['rel']
            rule = detail['rule']

            rules[obj_id].append({
                'start': (
                    rel['start'].to_native()
                    if 'start' in rel and rel['start']
                    else None
                ),
                'end': (
                    rel['end'].to_native()
                    if 'end' in rel and rel['end']
                    else None
                ),
                'territory': rule['territory'],
                'policy': rule['policy'],
                'service': rule['service'],
                'created_at': (
                    rel['createdAt'].to_native()
                    if 'createdAt' in rel and rel['createdAt']
                    else None
                ),
                'created_by': rel.get('createdBy'),
                'last_modified_at': (
                    rel['lastModifiedAt'].to_native()
                    if 'lastModifiedAt' in rel and rel['lastModifiedAt']
                    else None
                ),
                'last_modified_by': rel.get('lastModifiedBy'),
                '_id': rel['id']
            })

    return rules


def _get_bulk_rules_snowflake(obj_type, obj_ids):
    rules = {obj_id: [] for obj_id in obj_ids}
    if not obj_ids:
        return rules

    ids_clause = ', '.join("'{}'".format(int(obj_id)) for obj_id in obj_ids)
    sql = SQLLoader.load_query('get_bulk_rules').format(obj_ids=ids_clause)
    sf_obj_type = SNOWFLAKE_OBJ_TYPE.get(obj_type, obj_type)
    rows = snowflake_util.fetchall(sql, {'orchard_obj_type': sf_obj_type})

    for row in rows:
        obj_id = int(row[0])
        rules[obj_id].append({
            'start': row[4],
            'end': row[5],
            'territory': row[1],
            'policy': row[3],
            'service': row[2],
            'created_at': row[6],
            'created_by': row[7],
            'last_modified_at': row[8],
            'last_modified_by': row[9]
        })

    return rules


def create_rules(obj_type, obj_id, rules, profile):
    """Create rule connected to single object.

    Args:
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node
        rules (list[dict]): detailed rules to add
        profile (profile_type, profile_id): Request Profile
    """
    neo4j_session = get_session()
    created_by = by(profile, is_for_has_fingerprint_rule=True)

    for rule in rules:
        result = neo4j_session.run(
            cypher.CREATE_RULE.format(
                obj_type=obj_type,
                rule_rel=DEFAULT_REL
            ),
            obj_id=obj_id,
            start=rule.get('start'),
            end=rule.get('end'),
            service=rule['service'],
            policy=rule['policy'],
            territory=rule['territory'],
            created_by=created_by
        )
        if not result.single():
            raise CreateException()
    return None


def delete_rules(obj_type, obj_id, rel_ids, profile):
    """Soft delete rel connected to FingerprintRule node.

    Args:
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node
        rel_ids (list[str]): internal neo4j ID of relationship
        profile (profile_type, profile_id): Request Profile
    """
    neo4j_session = get_session()
    modified_by = by(profile, is_for_has_fingerprint_rule=True)

    results = neo4j_session.run(
        cypher.DELETE_RULES.format(
            obj_type=obj_type,
            rule_rel=DEFAULT_REL,
            deleted_rule_rel=DELETED_PREFIX + DEFAULT_REL
        ),
        obj_id=obj_id,
        rel_ids=rel_ids,
        modified_by=modified_by
    )
    nodes = [x for x in results]
    if len(nodes) != len(rel_ids):
        raise DeleteException()
    return None


def get_rules_snowflake(tx, obj_type, obj_id):
    """Read active rules for a single anchor from Snowflake.

    Runs inside the given transaction so the read participates in the same
    atomic unit as the subsequent create/delete.

    Args:
        tx: open Snowflake transaction handle (utils.snowflake.transaction).
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node

    Returns:
        list[dict]: rules keyed like the Neo4j path (no element_id).
    """
    sf_obj_type = SNOWFLAKE_OBJ_TYPE.get(obj_type, obj_type)
    sql = SQLLoader.load_query('get_rules')
    rows = tx.fetchall(sql, {
        'orchard_obj_type': sf_obj_type,
        'orchard_obj_id': str(obj_id)
    })
    return [
        {
            'territory': row[0],
            'service': row[1],
            'policy': row[2],
            'start': row[3],
            'end': row[4],
            'created_at': row[5],
            'created_by': row[6],
            'last_modified_at': row[7],
            'last_modified_by': row[8]
        }
        for row in rows
    ]


def create_rules_snowflake(tx, obj_type, obj_id, rules, profile, modified_at):
    """Insert rules for a single anchor into Snowflake within a transaction.

    Args:
        tx: open Snowflake transaction handle.
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node
        rules (list[dict]): rules to add
        profile (profile_type, profile_id): Request Profile
        modified_at: single transaction timestamp stamped on every row, so all
            rows changed in one upsert share created_at/last_modified_at.
    """
    if not rules:
        return None

    sf_obj_type = SNOWFLAKE_OBJ_TYPE.get(obj_type, obj_type)
    created_by = by(profile, is_for_has_fingerprint_rule=True)
    sql = SQLLoader.load_query('create_rule')

    # pass all rows as one list of param dicts so this executes as a single
    # batched INSERT instead of one round trip per rule
    params = [
        {
            'orchard_obj_type': sf_obj_type,
            'orchard_obj_id': str(obj_id),
            'territory': rule['territory'],
            'service': rule['service'],
            'policy': rule['policy'],
            'period_start': rule.get('start'),
            'period_end': rule.get('end'),
            'created_by': created_by,
            'modified_at': modified_at
        }
        for rule in rules
    ]
    result = tx.execute(sql, params)
    if result.rowcount != len(rules):
        raise CreateException()
    return None


def delete_rules_snowflake(tx, obj_type, obj_id, rules, profile, modified_at):
    """Soft delete rules for a single anchor in Snowflake within a transaction.

    The whole batch is archived and removed in two statements total (one
    multi-row INSERT, one MERGE-based delete) instead of two statements per
    rule, matched by each rule's full identity (territory, service, policy,
    period start, period end) so edits that change only the end date or
    policy target the single intended row.

    Args:
        tx: open Snowflake transaction handle.
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node
        rules (list[dict]): rules to delete, as returned by
            get_rules_snowflake (must include created_at/created_by).
        profile (profile_type, profile_id): Request Profile
        modified_at: single transaction timestamp stamped as the soft-delete
            moment, shared with the create path so one upsert groups together.
    """
    if not rules:
        return None

    sf_obj_type = SNOWFLAKE_OBJ_TYPE.get(obj_type, obj_type)
    modified_by = by(profile, is_for_has_fingerprint_rule=True)

    archive_sql = SQLLoader.load_query('delete_rule_archive')
    archive_params = [
        {
            'orchard_obj_type': sf_obj_type,
            'orchard_obj_id': str(obj_id),
            'territory': rule['territory'],
            'service': rule['service'],
            'policy': rule['policy'],
            'period_start': rule.get('start'),
            'period_end': rule.get('end'),
            'created_at': rule['created_at'],
            'created_by': rule['created_by'],
            'modified_at': modified_at,
            'modified_by': modified_by
        }
        for rule in rules
    ]
    tx.execute(archive_sql, archive_params)

    # dynamically size the MERGE's VALUES list to the batch, one uniquely
    # named tuple of bind params per rule
    delete_sql_template = SQLLoader.load_query('delete_rule')
    values_rows = []
    delete_params = {
        'orchard_obj_type': sf_obj_type,
        'orchard_obj_id': str(obj_id)
    }
    for i, rule in enumerate(rules):
        values_rows.append(
            '(:service_{i}, :territory_{i}, :policy_{i}, '
            ':period_start_{i}, :period_end_{i})'.format(i=i)
        )
        delete_params['service_{}'.format(i)] = rule['service']
        delete_params['territory_{}'.format(i)] = rule['territory']
        delete_params['policy_{}'.format(i)] = rule['policy']
        delete_params['period_start_{}'.format(i)] = rule.get('start')
        delete_params['period_end_{}'.format(i)] = rule.get('end')
    delete_sql = delete_sql_template.format(
        values_rows=',\n    '.join(values_rows))

    result = tx.execute(delete_sql, delete_params)
    if result.rowcount != len(rules):
        raise DeleteException()
    return None


def get_history(obj_type, obj_id, filters):
    """Get history of fingerprint rules.

    Args:
        obj_type (str): label of anchor node
        obj_id (int): unique identifier of anchor node
        filters (dict): query filters
    """
    params = {
        'orchard_obj_type': obj_type,
        'orchard_obj_id': obj_id
    }
    filters_sql = ''
    if filters['service']:
        filters_sql = "AND service = '{}' ".format(filters['service'])
    if filters['policy']:
        filters_sql += "AND policy = '{}'".format(filters['policy'])

    created_rule_sql = SQLLoader.load_query('created_rules_history').format(filters=filters_sql)  # noqa:E501
    deleted_rule_sql = SQLLoader.load_query('deleted_rules_history').format(filters=filters_sql)  # noqa:E501

    created_rules = snowflake_util.fetchall(created_rule_sql, params)
    deleted_rules = snowflake_util.fetchall(deleted_rule_sql, params)

    result = {
        'created': [],
        'deleted': []
    }
    for created_event in created_rules:
        result['created'].append({
            'territory': created_event[0],
            'service': created_event[1],
            'policy': created_event[2],
            'period_start': created_event[3],
            'period_end': created_event[4],
            'created_at': created_event[5],
            'created_by': created_event[6],
            'last_modified_at': created_event[7],
            'last_modified_by': created_event[8]
        })
    for deleted_event in deleted_rules:
        result['deleted'].append({
            'territory': deleted_event[0],
            'service': deleted_event[1],
            'policy': deleted_event[2],
            'period_start': deleted_event[3],
            'period_end': deleted_event[4],
            'created_at': deleted_event[5],
            'created_by': deleted_event[6],
            'last_modified_at': deleted_event[7],
            'last_modified_by': deleted_event[8]
        })

    return result