# By: Riasar Ullah
# This module works with integrations data in the database.

from dbqueries import db_task_instances, db_users
from psycopg2 import errorcodes
from utils import constants, errors, key_manager, times, var_names
from validations import report_validator, string_validator
import configuration as configs
import datetime
import json
import psycopg2


def get_postmortem_reports_list(conn, timestamp, with_user_id=None, with_organization_id=None,
                                row_limit=None, row_offset=None):
    '''
    Gets the list of postmortem reports. Only the basic information is retrieved in the
    :param conn: db connection
    :param timestamp: timestamp when this request s being made
    :param with_user_id: the user_id of the owner, reviewer or collaborator to filter by
    :param with_organization_id: the organization id to filter by
    :param row_limit: the number of rows to retrieve
    :param row_offset: the number of rows to offset before retrieving the results
    :return: (list) of dict of basic report details
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    query_params = {'timestamp': timestamp}

    conditions = []
    if with_user_id is not None:
        assert isinstance(with_user_id, int)
        conditions.append('''
            (
                rep.created_by = %(user_name)s
                or
                rep.report_id in (
                    select report_id from postmortem_reviewers
                    where start_timestamp <= %(timestamp)s
                        and end_timestamp > %(timestamp)s
                        and reviewer = %(user_name)s
                )
            )
        ''')
        query_params['user_name'] = with_user_id

    if with_organization_id is not None:
        assert isinstance(with_organization_id, int)
        conditions.append(" rep.organization_id = %(org_id)s ")
        query_params['org_id'] = with_organization_id

    limit_cond = ''
    if row_limit is not None:
        assert isinstance(row_limit, int)
        limit_cond += ' limit {0} '.format(str(row_limit))
    if row_offset is not None:
        assert isinstance(row_offset, int)
        limit_cond += ' offset {0} '.format(str(row_offset))

    query = '''
            select report_number, rep.start_timestamp, users.first_name || ' ' || users.last_name, report_name,
                report_status, inst.organization_instanceid, inst.instance_timestamp
            from postmortem_reports as rep
            join task_instances as inst using(instanceid)
            left join users on rep.created_by = users.user_id
            where rep.start_timestamp <= %(timestamp)s
                and rep.end_timestamp > %(timestamp)s
                and users.start_timestamp <= %(timestamp)s
                and users.end_timestamp > %(timestamp)s
                {0}
            {1};
            '''.format(' and ' + ' and '.join(conditions) if len(conditions) > 0 else '', limit_cond)

    try:
        result = conn.fetch(query, query_params)
        data = []
        for rep_num, rep_timestamp, created_by, rep_name, rep_status, org_inst_id, inst_timestamp in result:

            data.append({
                var_names.report_number: rep_num,
                var_names.report_timestamp: rep_timestamp,
                var_names.created_by: created_by,
                var_names.report_name: rep_name,
                var_names.status: rep_status,
                var_names.organization_instance_id: org_inst_id,
                var_names.instance_timestamp: inst_timestamp
            })

        return data
    except psycopg2.DatabaseError:
        raise


def get_postmortem_report_details(conn, timestamp, organization_id, report_number):
    '''
    Get the report specific details of a postmortem report.
    :param conn: db connection
    :param timestamp: timestamp when the request is being made
    :param organization_id: (int) organization ID to filter by
    :param report_number: (str) the report number (reference id) of this report
    :return: (dict) of details
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    string_validator.is_postmortem_report_number(report_number)

    query = '''
            with t1 as (
                select report_id, report_number, report_status, report_name, rep.start_timestamp,
                    users.first_name || ' ' || users.last_name, rep.instanceid, inst.organization_instanceid,
                    complete_by, overview, description, impact, resolution, positives, negatives, preventions
                from postmortem_reports as rep
                join task_instances as inst using(instanceid)
                left join users on rep.created_by = users.user_id
                where rep.start_timestamp <= %(timestamp)s
                    and rep.end_timestamp > %(timestamp)s
                    and rep.report_number = %(rep_num)s
                    and rep.organization_id = %(org_id)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
            )
            , t2 as (
                select report_id, array_agg(json_build_object(
                    'preferred_username', users.preferred_username,
                    'display_name', first_name || ' ' || last_name,
                    'user_role', reviewer_role
                )) as reviewers
                from postmortem_reviewers as rev
                join users on rev.reviewer = users.user_id
                where rev.start_timestamp <= %(timestamp)s
                    and rev.end_timestamp > %(timestamp)s
                    and users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and report_id in (select report_id from t1)
                group by report_id
            )
            , t3 as (
                select report_id, array_agg(json_build_object(
                    'session_date', session_date,
                    'session_time', session_time,
                    'duration', duration
                ) order by session_date, session_time) as review_sessions
                from postmortem_review_sessions
                where start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                    and report_id in (select report_id from t1)
                group by report_id
            )
            , t4 as (
                select report_id, array_agg(json_build_object(
                    'timestamp', comment_timestamp,
                    'display_name', first_name || ' ' || last_name,
                    'comments', comment
                ) order by comment_timestamp) as report_comments
                from postmortem_comments
                join users on postmortem_comments.comment_by = users.user_id
                where users.start_timestamp <= %(timestamp)s
                    and users.end_timestamp > %(timestamp)s
                    and report_id in (select report_id from t1)
                group by report_id
            )
            select t1.*, t2.reviewers, t3.review_sessions, t4.report_comments
            from t1
            full join t2 using (report_id)
            full join t3 using (report_id)
            full join t4 using (report_id);
            '''

    query_params = {'timestamp': timestamp, 'org_id': organization_id, 'rep_num': report_number}
    try:
        result = conn.fetch(query, query_params)
        data = dict()
        if len(result) == 0:
            raise LookupError(errors.err_unknown_resource)

        for report_id, report_number, report_status, report_name, report_timestamp, created_by,\
            instance_id, org_instance_id, complete_by, overview, description, impact, resolution,\
                positives, negatives, preventions, reviewers, review_sessions, comments in result:
            data = {
                var_names.report_number: report_number,
                var_names.status: report_status,
                var_names.report_name: report_name,
                var_names.report_timestamp: report_timestamp,
                var_names.created_by: created_by,
                var_names.instance_id: instance_id,
                var_names.organization_instance_id: org_instance_id,
                var_names.complete_by: complete_by,
                var_names.overview: overview,
                var_names.description: description,
                var_names.impact: impact,
                var_names.resolution: resolution,
                var_names.positives: positives,
                var_names.negatives: negatives,
                var_names.preventions: preventions,
                var_names.reviewers: [list(x.values()) for x in reviewers] if reviewers is not None else [],
                var_names.review_sessions: [[
                    times.get_date_from_string(x[var_names.session_date]),
                    x[var_names.session_time], x[var_names.duration]
                ] for x in review_sessions] if review_sessions is not None else [],
                var_names.comments: [[
                    times.get_timestamp_from_string(x[var_names.timestamp]),
                    x[var_names.display_name], x[var_names.comments]
                ] for x in comments] if comments is not None else []
            }
        return data
    except psycopg2.DatabaseError:
        raise


def create_postmortem_report(conn, timestamp, org_id, user_id, instance_id, report_status, report_name, complete_by,
                             overview, description, impact, resolution, positives, negatives, preventions,
                             reviewers, sessions):
    '''
    Create a new postmortem report.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param org_id: ID of the organization
    :param user_id: user_id of the user
    :param instance_id: id of the instance
    :param report_status: the status of the report
    :param report_name: the name/title of the report
    :param complete_by: date the report is expected to be completed by
    :param overview: overview of the incident and the resolution
    :param description: detailed description of the incident
    :param impact: the impact the incident had
    :param resolution: description of the way it was resolved
    :param positives: the positive aspects of the way the incident was handled
    :param negatives: the things that could have been handled better
    :param preventions: the things that can be done to prevent recurrences
    :param reviewers: (list) of lists -> [ [policy id, display name, role], ... ]
    :param sessions: (list) of lists -> [ [session date, session time, duration], ... ]
    :return: report number (8 character long string)
    :errors: AssertionError, DatabaseError, RuntimeError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert isinstance(instance_id, int)

    if reviewers is not None and len(reviewers) > 0:
        pref_names = [x[0] for x in reviewers]
        pref_maps = db_users.get_user_ids_from_preferred_usernames(conn, timestamp, org_id, pref_names, as_dict=True)
        for i in range(0, len(reviewers)):
            user_name = reviewers[i][0]
            if user_name in pref_maps:
                reviewers[i][0] = pref_maps[user_name]

    report_validator.validate_postmortem_report_data(report_status, complete_by, reviewers, sessions)

    complete_by = None if complete_by is None else times.get_date_from_string(complete_by)
    report_number = key_manager.generate_alphanumeric_key(configs.postmortem_report_reference_id_length,
                                                          capitalized=True)
    inst_org_id = db_task_instances.get_organization_id_of_instance(conn, instance_id)

    if org_id == inst_org_id:
        query = '''
                do
                $body$
                
                declare
                    rep_id postmortem_reports.report_id%%type;
                    rev json;
                    sess json;
                begin

                insert into postmortem_reports (
                    start_timestamp, end_timestamp, created_by, report_number, instanceid,
                    organization_id, report_status, report_name, complete_by, overview,
                    description, impact, resolution, positives, negatives, preventions)
                values (
                    %(timestamp)s, %(end_time)s, %(user_name)s, %(rep_num)s, %(inst_id)s,
                    %(org_id)s, %(rep_status)s, %(rep_name)s, %(complete_by)s, %(overview)s,
                    %(description)s, %(impact)s, %(resolution)s, %(positives)s, %(negatives)s, %(preventions)s
                )
                returning report_id into rep_id;

                for rev in select * from json_array_elements(%(reviewers)s)
                loop
                    insert into postmortem_reviewers values (
                        rep_id, %(timestamp)s, %(end_time)s, (rev->>'user_id')::int, rev->>'user_role'
                    );
                end loop;

                for sess in select * from json_array_elements(%(sessions)s)
                loop
                    insert into postmortem_review_sessions values (
                        rep_id, %(timestamp)s, %(end_time)s, (sess->>'session_date')::date,
                        (sess->>'session_time')::time, (sess->>'duration')::numeric
                    );
                end loop;

                end;
                $body$
                '''

        query_params = {'timestamp': timestamp, 'end_time': constants.end_timestamp,
                        'user_name': user_id, 'rep_num': report_number,
                        'inst_id': instance_id, 'org_id': org_id,
                        'rep_status': report_status, 'rep_name': report_name,
                        'complete_by': complete_by, 'overview': overview,
                        'description': description, 'impact': impact,
                        'resolution': resolution, 'positives': positives,
                        'negatives': negatives, 'preventions': preventions,
                        'reviewers': reviewer_json_values(reviewers),
                        'sessions': review_session_json_values(sessions)}
        conn.execute(query, query_params)

        return report_number
    else:
        raise LookupError(errors.err_unknown_resource)


def edit_postmortem_report(conn, timestamp, org_id, user_id, report_number, report_status, report_name, complete_by,
                           overview, description, impact, resolution, positives, negatives, preventions,
                           reviewers, sessions):
    '''
    Edit an existing postmortem report.
    :param conn: db connection
    :param timestamp: timestamp when this request was made
    :param org_id: organization ID
    :param user_id: user_id of the user
    :param report_number: the report number
    :param report_status: the status of the report
    :param report_name: the name/title of the report
    :param complete_by: date the report is expected to be completed by
    :param overview: overview of the incident and the resolution
    :param description: detailed description of the incident
    :param impact: the impact the incident had
    :param resolution: description of the way it was resolved
    :param positives: the positive aspects of the way the incident was handled
    :param negatives: the things that could have been handled better
    :param preventions: the things that can be done to prevent recurrences
    :param reviewers: (list) of lists -> [ [policy id, display name, role], ... ]
    :param sessions: (list) of lists -> [ [session date, session time, duration], ... ]
    :errors: AssertionError, DatabaseError, RuntimeError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert string_validator.is_postmortem_report_number(report_number)

    if reviewers is not None and len(reviewers) > 0:
        pref_names = [x[0] for x in reviewers]
        pref_maps = db_users.get_user_ids_from_preferred_usernames(conn, timestamp, org_id, pref_names, as_dict=True)
        for i in range(0, len(reviewers)):
            user_name = reviewers[i][0]
            if user_name in pref_maps:
                reviewers[i][0] = pref_maps[user_name]

    report_validator.validate_postmortem_report_data(report_status, complete_by, reviewers, sessions)

    complete_by = None if complete_by is None else times.get_date_from_string(complete_by)
    report_org_id = get_organization_id_of_report(conn, timestamp, report_number)

    if org_id == report_org_id:
        query = '''
                do
                $body$

                declare
                    rep_id postmortem_reports.report_id%%type;
                    rev json;
                    sess json;
                begin

                update postmortem_reports set report_status = %(rep_status)s,
                    report_name = %(rep_name)s,
                    complete_by = %(complete_by)s,
                    overview = %(overview)s,
                    description = %(description)s,
                    impact = %(impact)s,
                    resolution = %(resolution)s,
                    positives = %(positives)s,
                    negatives =  %(negatives)s,
                    preventions = %(preventions)s
                where report_number = %(rep_num)s
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s
                returning report_id into rep_id;

                update postmortem_reviewers set end_timestamp = %(timestamp)s
                where report_id = rep_id
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s;

                update postmortem_review_sessions set end_timestamp = %(timestamp)s
                where report_id = rep_id
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s;

                if rep_id is not null then

                    for rev in select * from json_array_elements(%(reviewers)s)
                    loop
                        insert into postmortem_reviewers values (
                            rep_id, %(timestamp)s, %(end_time)s, (rev->>'user_id')::int, rev->>'user_role'
                        );
                    end loop;
    
                    for sess in select * from json_array_elements(%(sessions)s)
                    loop
                        insert into postmortem_review_sessions values (
                            rep_id, %(timestamp)s, %(end_time)s, (sess->>'session_date')::date,
                            (sess->>'session_time')::time, (sess->>'duration')::numeric
                        );
                    end loop;

                end if;

                end;
                $body$
                '''

        query_params = {'timestamp': timestamp, 'end_time': constants.end_timestamp,
                        'rep_num': report_number, 'rep_status': report_status,
                        'rep_name': report_name, 'complete_by': complete_by,
                        'overview': overview, 'description': description,
                        'impact': impact, 'resolution': resolution,
                        'positives': positives, 'negatives': negatives,
                        'preventions': preventions,
                        'reviewers': reviewer_json_values(reviewers),
                        'sessions': review_session_json_values(sessions)}

        conn.execute(query, query_params)
    else:
        raise LookupError(errors.err_unknown_resource)


def save_comment(conn, timestamp, user_id, report_number, comment):
    '''
    Saves a comment made on a report.
    :param conn: db connection
    :param timestamp: timestamp when the comment was made
    :param user_id: user_id of the user
    :param report_number: the report number
    :param comment: the comment
    :errors: AssertionError, DatabaseError, PermissionError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert string_validator.is_postmortem_report_number(report_number)

    if is_owner_or_reviewer(conn, timestamp, user_id):
        query = '''
                do
                $body$
                declare
                    rep_id postmortem_reports.report_id%%type;
                begin

                select report_id into rep_id from postmortem_reports
                where report_number = %(rep_num)s
                    and start_timestamp <= %(timestamp)s
                    and end_timestamp > %(timestamp)s;

                if rep_id is not null then
                    insert into postmortem_comments values (rep_id, %(timestamp)s, %(user_name)s, %(comment)s);
                else
                    raise check_violation;
                end if;

                end;
                $body$
                '''
        query_params = {'rep_num': report_number, 'timestamp': timestamp,
                        'user_name': user_id, 'comment': comment}
        try:
            conn.execute(query, query_params)
        except psycopg2.IntegrityError as e:
            if e.pgcode == errorcodes.CHECK_VIOLATION:
                raise LookupError(errors.err_unknown_resource)
            else:
                raise
        except psycopg2.DatabaseError:
            raise
    else:
        raise PermissionError(errors.err_user_action)


def delete_postmortem_report(conn, timestamp, org_id, user_id, report_number):
    '''
    Delete/end-date a postmortem report.
    :param conn: db connection
    :param timestamp: timestamp when this request is being made
    :param org_id: organization ID
    :param user_id: user_id of the user making the request
    :param report_number: (int) the report id
    :errors: AssertionError, DatabaseError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)
    assert string_validator.is_postmortem_report_number(report_number)

    report_org_id = get_organization_id_of_report(conn, timestamp, report_number)

    if org_id == report_org_id:
        query = '''
                select close_postmortem(%s, %s);
                '''
        query_params = (report_number, timestamp,)
        try:
            conn.execute(query, query_params)
        except psycopg2.IntegrityError as e:
            if e.pgcode == errorcodes.CHECK_VIOLATION:
                raise LookupError(errors.err_unknown_resource)
            else:
                raise
        except psycopg2.DatabaseError:
            raise
    else:
        raise LookupError(errors.err_unknown_resource)


def reviewer_json_values(reviewers):
    '''
    Get json values of reviewers in the format needed for sql insertion.
    :param reviewers: (list) of reviewers
    :return: (json) values
    '''
    values = []
    for item in reviewers:
        values.append({
            var_names.user_id: item[0],
            var_names.user_role: item[2]
        })
    return json.dumps(values)


def review_session_json_values(sessions):
    '''
    Get json values of review sessions in the format needed for sql insertion.
    :param sessions: (list) of review sessions
    :return: (json) values
    '''
    values = []
    for item in sessions:
        values.append({
            var_names.session_date: item[0],
            var_names.session_time: item[1],
            var_names.duration: item[2]
        })
    return json.dumps(values)


def get_organization_id_of_report(conn, timestamp, report_number):
    '''
    Get the ID of the organization that a given report is for.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param report_number: (str) report number
    :return: (int) organization id
    :errors: AssertionError, DatabaseError, LookupError
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert string_validator.is_postmortem_report_number(report_number)
    query = '''
            select organization_id from postmortem_reports
            where start_timestamp <= %s
                and end_timestamp > %s
                and report_number = %s;
            '''
    query_params = (timestamp, timestamp, report_number,)
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 1:
            return result[0][0]
        elif len(result) == 0:
            raise LookupError(errors.err_unknown_resource)
        else:
            raise SystemError(errors.err_internal_multiple_entries_found + ' Report number - ' + report_number)
    except psycopg2.DatabaseError:
        raise


def is_owner_or_reviewer(conn, timestamp, user_id):
    '''
    Checks if a user is the owner of the report or is a reviewer or a collaborator or not.
    :param conn: db connection
    :param timestamp: timestamp when the request was made
    :param user_id: user_id of the user making the request
    :return: (boolean) True or False
    '''
    assert isinstance(timestamp, datetime.datetime)
    assert isinstance(user_id, int)

    query = '''
            select report_id from postmortem_reports
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and created_by = %(user_name)s
            
            union
            
            select report_id from postmortem_reviewers
            where start_timestamp <= %(timestamp)s
                and end_timestamp > %(timestamp)s
                and reviewer = %(user_name)s;
            '''
    query_params = {'timestamp': timestamp, 'user_name': user_id}
    try:
        result = conn.fetch(query, query_params)
        if len(result) == 0:
            return False
        return True
    except psycopg2.DatabaseError:
        raise