/parser4confluence.py

#!/usr/bin/env python3
# -*- coding: utf-8 -*-

"""
Usage:
    parser4confluence.py -u <user> -f <filename> -d <database>
"""

"""
__author__      = "Tomasz Dudzisz <tdudzisz@atlassian.com>"
__author__      = "Rudy Slaiby <rslaiby@atlassian.com>"
__author__      = "Tomasz Kanafa <tkanafa@atlassian.com>"
__copyright__   = "Copyright 2018, Atlassian.com"
__credits__     = []
__license__     = "BSD"
__version__     = "1.0.8"
__status__      = "Development"
__created__     = 11/04/2018
__updated__     = 20/04/2018

__project__     = gdpr-framework
__file__        = parser4confluence.py
__description__ =

"""

from docopt import docopt
from schema import Schema, And

import sys, os, json, traceback


class Opts(object):

    def __init__(self, __doc__):
        self.username = None
        self.filename = None
        self.database = None

        self.valid = False

        arguments = docopt(__doc__)
        self._validate(arguments)


    def _validate(self, arguments):
        schema = Schema({
            '-u': True,
            '<user>': And(str, len),
            '-f': True,
            '<filename>': And(str, len),
            '-d': True,
            '<database>': And(str, len),
        })
        args = schema.validate(arguments)

        self.username = args['<user>']
        self.username = self.username.strip('\'')
        self.filename = args['<filename>']
        self.database = args['<database>']

        self.valid = self.username and self.filename and self.database


class SearchData(object):

    def __init__(self):
        self.username = None
        self.data = None

        self.where = "where"
        self.action = "action"
        self.origin = "origin"
        self.type = "type"
        self.description = "description"
        self.active = "active"

        self.db_mysql = "mysql"
        self.db_mssql = "mssql"
        self.db_oracle = "oracle"
        self.db_postgresql = "postgresql"

        self.user_order = True
        self.order = 1

    def execute(self):

        self.clear_output()

        for key, value in self.data.items():
            # self.show_metadata(key, value)

            self.save_query(key, value)
            self.order += 1

            continue


    def save_query(self, table, metadata):

        type = None
        comments = None
        sql = None

        origin = metadata[self.origin]
        description = metadata[self.description]

        if not metadata[self.active]:
            return

        for action in metadata[self.action]:
            type = action[self.type]

            comments = []
            comments.append("-- Type       : {}".format(type))
            comments.append("-- Origin     : {}".format(origin))
            comments.append("-- Description: {}".format(description))

            sql = self.get_sql_table(table, metadata)

            print(sql)

            self.save_to_file(self.database, self.add_comments(comments, sql), type, table)


    def add_comments(self, comment, sql):

        """
        add comments to sql
        """

        data = comment[:]
        data.append(sql)

        data = "\n".join(data)

        return data


    def get_order(self):
        if self.user_order:

            if self.order < 10:
                order = "0{}_".format(self.order)
            else:
                order = "{}_".format(self.order)
            return order

        else:
            return ""


    def save_to_file(self, database, data, type, table):
        try:

            file_name = "{}{}_{}.sql".format(self.get_order(), type, table.strip())
            path = "{}/{}/{}".format(self.output_path, database, file_name)
            f = open(path, "w")
            f.write(data)
            f.close()

        except Exception as e:
            print("Error saving file: {}".format(file_name))
            raise Exception(e)


    def show_metadata(self, key, value):

        print("\n{}".format(key))
        print(self.get_value("origin", value))
        print(self.get_value("description", value))
        print(self.get_value("action", value))

        for cname, cvalue in value[self.action].items():
            print("\t\t{0:<25}: {0:<30}".format(cname, cvalue))

        print("\t{}:".format(self.where))

        for cname, cvalue in value[self.where].items():
            print("\t\t{0:<25}: {0:<30}".format(cname, cvalue))


    def clear_output(self):

        """
        1. create output dir if doesn't exist
        2. remove a dir content in exists
        """

        path = "{}/{}".format(self.output_path, self.database)
        self.recreate_dir(path)


    def recreate_dir(self, path):

        if os.path.exists(path):
            print("Output directory found, cleaning: {}".format(path))
            # for the_file in os.listdir(path):
            #     file_path = os.path.join(path, the_file)
            #     try:
            #         if os.path.isfile(file_path):
            #             print("Removing: {}".format(file_path))
            #             os.unlink(file_path)
            #     except Exception as e:
            #         print(e)
        else:
            print("Creating output dir: {}".format(self.output_path))
            os.makedirs(path, exist_ok=True)


    def parse_args(self):
        args = Opts(__doc__)

        if not args.valid:
            sys.exit(1)

        self.username = args.username
        self.data = self.read_json(args.filename)
        self.database = args.database
        base = os.path.basename(args.filename)
        self.output_path = "{}_{}".format(base.split('.')[0], "queries")


    def read_json(self, filename):
        if os.path.isfile(filename):
            content = open(filename, 'r').read()
            data = json.loads(content)
            return data
        print("No such file: {}".format(filename))
        sys.exit(1)


    def get_value(self, keyword, entry):
        if keyword in entry:
            return "\t{0:<33}: {0:<20}".format(keyword, entry[keyword])


    def get_column(self, entry):
        return entry


    def get_sql_clause(self, type, left_column, right_column):

        def exact_match_handler(left_column, right_column):
            return "{} = '{}' ".format(left_column, right_column)

        def regex_match_handler(left_column, right_column):
            return "{} like '{}' ".format(left_column, right_column)

        def none_handler(left_column, right_column): return "1 = 0".format(left_column, right_column)

        def exact_value_handler(left_column, right_column): return "{} = {} ".format(left_column, right_column)

        def free_text_handler(left_column, right_column): return "{} {} ".format(left_column, right_column)

        ret = {
            'user_key': exact_match_handler,
            'email': exact_match_handler,
            'text': regex_match_handler,
            'url': regex_match_handler,
            'jql': regex_match_handler,
            'audit_changed_value': regex_match_handler,
            'audit_data': regex_match_handler,
            'changeitem_string': regex_match_handler,
            'changeitem_value': regex_match_handler,
            'exact_value': exact_value_handler,
            'free_text': free_text_handler,
        }.get(type, none_handler)(left_column, right_column)
        return ret


    def get_sql_where_clause(self, where_details):

        sql_where_clause = ''

        if where_details:
            for current_column, current_value in where_details.items():
                current_type = current_value['type']
                if current_type == 'nested':
                    if "db_specific" in current_value and self.should_apply_db_specific(current_value):
                        # apply the database specific query
                        sql_where_clause = self.apply_db_specific(current_column, current_value)
                    else:
                        sql_where_clause += "and {} IN (select {} from {} where {})".format(current_column, current_value['column_select'], current_value['table'], self.get_sql_where_clause(current_value['value']))
                elif current_type == 'exact_value':
                    # in case a we need to compare 2 columns
                    sql_where_clause += "and {}".format(self.get_sql_clause(current_value['type'], current_column, current_value['value']))

                elif current_type == 'function':
                    sql_where_clause += self.apply_function(current_column, current_value)

                else:
                    sql_where_clause += 'and {}'.format(self.get_sql_clause(current_value['type'], current_column, self.username))
        else:
            sql_where_clause = "1 = 1"

        # Slice up the starting and
        sql_where_clause = self.slice_string(sql_where_clause, "and ")

        return sql_where_clause


    def slice_string(self, text, text_to_slice):
        if (len(text) > len(text_to_slice)) and (text.startswith(text_to_slice)):
            text = text[len(text_to_slice):]
        return text


    def should_apply_db_specific(self, current_value):
        if current_value['db_specific'] == self.database:
            return True
        return False


    def apply_function(self, current_column, current_value):
        function_name = current_value['function_name']
        function_params_text = ''
        all_params = current_value['parameters']
        for current_parameter in all_params:
            current_param_type = current_parameter['type']

            if current_param_type == 'nested':
                function_params_text += ", (select {} from {} where {})".format(current_parameter['column_select'], current_parameter['table'], self.get_sql_where_clause(current_parameter['value']))

            else:
                function_params_text += ", {}".format(current_parameter['value'])

        # Slice up the starting comma
        function_params_text = self.slice_string(function_params_text, ", ")
        sql_where_clause = "and {} = {}({})".format(current_column, function_name, function_params_text)

        return sql_where_clause


    def apply_db_specific(self, current_column, current_value):
        sql_where_clause = ""

        # construct the inner query

        sql_where_clause += "select {} from {} where {}".format(current_value['column_select'], current_value['table'], self.get_sql_where_clause(current_value['value']))

        # wrap it with the database specific statement
        sql_where_clause = self.replace_db_specific(current_value, sql_where_clause)
        # glue the first part of the where with the wrapped inner query
        sql_where_clause = "and {} IN {}".format(current_column, sql_where_clause)

        return sql_where_clause


    def replace_db_specific(self, current_value, sql_where_clause):
        # Slice up the starting and before wrapping it
        sql_where_clause = self.slice_string(sql_where_clause, "and ")

        wrapped_query = sql_where_clause

        # apply any database specific thing here
        if current_value['db_specific'] == self.db_postgresql:
            wrapped_query = current_value['wrap'].replace("$1", sql_where_clause)
        elif current_value['db_specific'] == self.db_mysql:
            wrapped_query = current_value['wrap'].replace("$1", sql_where_clause)
        elif current_value['db_specific'] == self.db_mssql:
            wrapped_query = current_value['wrap'].replace("$1", sql_where_clause)
        elif current_value['db_specific'] == self.db_oracle:
            wrapped_query = current_value['wrap'].replace("$1", sql_where_clause)

        return wrapped_query


    def build_insert_statements(self):
        if self.database == "oracle":
            return self.build_insert_statements_for_oracle()
        elif self.database == "mssql":
            return self.build_insert_statements_for_mssql()
        elif self.database == "mysql":
            return self.build_insert_statements_for_mysql()
        elif self.database == "postgresql":
            return self.build_insert_statements_for_postgres()

    def build_insert_statements_for_mssql(self):
        insert1 = """INSERT INTO journalentry (journal_name, creationdate, type, message) VALUES
                      ('main_index', CURRENT_TIMESTAMP - 2,
                       'DELETE_CHANGE_DOCUMENTS', concat('com.atlassian.confluence.pages.Attachment-', (
                        SELECT CONTENTID
                        FROM CONTENT
                        WHERE PAGEID IN (
                          SELECT CONTENTID
                          FROM CONTENT
                          WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                            SELECT user_key
                            FROM user_mapping
                            WHERE username = '{}')))));""".format(self.username)

        insert2 = """INSERT INTO journalentry (journal_name, creationdate, type, message) VALUES
                      ('main_index', CURRENT_TIMESTAMP - 2, 'DELETE_DOCUMENT',
                       concat('com.atlassian.confluence.pages.Attachment-', (
                         SELECT CONTENTID
                         FROM CONTENT
                         WHERE PAGEID IN (
                           SELECT CONTENTID
                           FROM CONTENT
                           WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                             SELECT user_key
                             FROM user_mapping
                             WHERE username = '{}')))));""".format(self.username)

        insert3 = """INSERT INTO journalentry
                       SELECT 'main_index'                                                               AS journal_name
                          ,   CURRENT_TIMESTAMP - 2                                                      AS creationdate
                          ,   'DELETE_DOCUMENT'                                                          AS type
                          ,   concat('com.atlassian.confluence.user.PersonalInformation-', C2.CONTENTID) AS message
                       FROM (
                               SELECT CONTENTID
                               FROM CONTENT
                               WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                  SELECT user_key
                                  FROM user_mapping
                                  WHERE username = '{}')) AS C2;""".format(self.username)

        insert4 = """INSERT INTO journalentry
                       SELECT 'main_index'                                                               AS journal_name
                          ,   CURRENT_TIMESTAMP - 2                                                      AS creationdate
                          ,   'DELETE_CHANGE_DOCUMENTS'                                                  AS type
                          ,   concat('com.atlassian.confluence.user.PersonalInformation-', C2.CONTENTID) AS message
                       FROM (
                               SELECT CONTENTID
                               FROM CONTENT
                               WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                  SELECT user_key
                                  FROM user_mapping
                                  WHERE username = '{}')) AS C2;""".format(self.username)
        insert5 = """INSERT INTO journalentry
                        SELECT 'main_index'            AS journal_name
                            ,    CURRENT_TIMESTAMP - 2 AS creationdate
                            ,    type_name             AS type
                            ,    CASE
                                WHEN (C2.CONTENTTYPE = 'PAGE')
                                THEN concat('com.atlassian.confluence.pages.Page-', C2.CONTENTID)
                                WHEN (C2.CONTENTTYPE = 'BLOGPOST')
                                THEN concat('com.atlassian.confluence.pages.BlogPost-', C2.CONTENTID)
                                WHEN (C2.CONTENTTYPE = 'COMMENT')
                                THEN concat('com.atlassian.confluence.pages.Comment-', C2.CONTENTID)
                                END               AS message
                        FROM (
                                SELECT CONTENT.CONTENTID
                                , CONTENT.CONTENTTYPE
                                , BODYCONTENT.BODY
                                FROM CONTENT
                                JOIN BODYCONTENT
                                    ON CONTENT.CONTENTID = BODYCONTENT.CONTENTID
                                WHERE CONTENTTYPE IN ('PAGE', 'BLOGPOST', 'COMMENT')
                                    AND CONTENT.PREVVER IS NULL
                                    AND CONTENT.CONTENT_STATUS = 'current'
                                    AND BODYCONTENT.BODY LIKE CONCAT('%<ri:user ri:userkey="', (
                                SELECT user_key
                                FROM user_mapping
                                WHERE user_mapping.username = '{}'), '"%')
                            ) AS C2
                            JOIN (SELECT 'ADD_CHANGE_DOCUMENT' AS type_name
                                UNION SELECT 'UPDATE_DOCUMENT' AS type_name) AS tmp_type
                            ON (1 = 1);""".format(self.username)
        # return "{}\n\n{}\n\n{}\n\n{}".format(insert1, insert2, insert3, insert4)
        # remove the attachment journal entry for now, since it breaks when there are more than 1 attachment (and also breaks for 0 attachment)
        return "{}\n\n{}\n\n{}".format(insert3, insert4, insert5)

    def build_insert_statements_for_oracle(self):
        insert1 = """INSERT INTO journalentry (entry_id, journal_name, creationdate, type, message) VALUES
                              (SEQ_JOURNAL_ENTRY_ID.nextval, 'main_index', CURRENT_TIMESTAMP - 2,
                               'DELETE_CHANGE_DOCUMENTS', concat('com.atlassian.confluence.pages.Attachment-', (
                                SELECT CONTENTID
                                FROM CONTENT
                                WHERE PAGEID IN (
                                  SELECT CONTENTID
                                  FROM CONTENT
                                  WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                    SELECT user_key
                                    FROM user_mapping
                                    WHERE username = '{}')))));""".format(self.username)

        insert2 = """INSERT INTO journalentry (entry_id, journal_name, creationdate, type, message) VALUES
                              (SEQ_JOURNAL_ENTRY_ID.nextval, 'main_index', CURRENT_TIMESTAMP - 2, 'DELETE_DOCUMENT',
                               concat('com.atlassian.confluence.pages.Attachment-', (
                                 SELECT CONTENTID
                                 FROM CONTENT
                                 WHERE PAGEID IN (
                                   SELECT CONTENTID
                                   FROM CONTENT
                                   WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                     SELECT user_key
                                     FROM user_mapping
                                     WHERE username = '{}')))));""".format(self.username)

        insert3 = """INSERT INTO journalentry
                              SELECT SEQ_JOURNAL_ENTRY_ID.nextval                                            AS entry_id
                                ,    'main_index'                                                            AS journal_name
                                ,    CURRENT_TIMESTAMP - 2                                                   AS creationdate
                                ,    'DELETE_DOCUMENT'                                                       AS type
                                ,    concat('com.atlassian.confluence.user.PersonalInformation-', CONTENTID) AS message
                              FROM (
                                SELECT CONTENTID
                                FROM CONTENT
                                WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                  SELECT user_key
                                  FROM user_mapping
                                  WHERE username = '{}'));""".format(self.username)

        insert4 = """INSERT INTO journalentry
                              SELECT SEQ_JOURNAL_ENTRY_ID.nextval                                            AS entry_id
                                ,    'main_index'                                                            AS journal_name
                                ,    CURRENT_TIMESTAMP - 2                                                   AS creationdate
                                ,    'DELETE_CHANGE_DOCUMENTS'                                               AS type
                                ,    concat('com.atlassian.confluence.user.PersonalInformation-', CONTENTID) AS message
                              FROM (
                                SELECT CONTENTID
                                FROM CONTENT
                                WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                                  SELECT user_key
                                  FROM user_mapping
                                  WHERE username = '{}'));""".format(self.username)
        insert5 = """INSERT INTO journalentry          
                        SELECT SEQ_JOURNAL_ENTRY_ID.nextval AS entry_id
                        ,    'main_index'                 AS journal_name
                        ,    CURRENT_TIMESTAMP - 2        AS creationdate
                        ,    type_name                    AS type
                        ,    CASE
                            WHEN (CONTENTTYPE = 'PAGE')
                                THEN concat('com.atlassian.confluence.pages.Page-', CONTENTID)
                            WHEN (CONTENTTYPE = 'BLOGPOST')
                                THEN concat('com.atlassian.confluence.pages.BlogPost-', CONTENTID)
                            WHEN (CONTENTTYPE = 'COMMENT')
                                THEN concat('com.atlassian.confluence.pages.Comment-', CONTENTID)
                            END                          AS message
                        FROM (
                        SELECT CONTENT.CONTENTID
                            , CONTENT.CONTENTTYPE
                        FROM CONTENT
                            JOIN BODYCONTENT
                            ON CONTENT.CONTENTID = BODYCONTENT.CONTENTID
                        WHERE CONTENTTYPE IN ('PAGE', 'BLOGPOST', 'COMMENT')
                                AND CONTENT.PREVVER IS NULL
                                AND CONTENT.CONTENT_STATUS = 'current'
                                AND BODYCONTENT.BODY LIKE '%<ri:user ri:userkey="' || (
                            SELECT user_key
                            FROM user_mapping
                            WHERE user_mapping.username = '{}') || '"%'
                        )
                        JOIN (SELECT 'ADD_CHANGE_DOCUMENT' type_name FROM dual
                                UNION
                                SELECT 'UPDATE_DOCUMENT' type_name FROM dual) tmp_type
                            ON (1 = 1);""".format(self.username)
        # return "{}\n\n{}\n\n{}\n\n{}".format(insert1, insert2, insert3, insert4)
        # remove the attachment journal entry for now, since it breaks when there are more than 1 attachment (and also breaks for 0 attachment)
        return "{}\n\n{}\n\n{}".format(insert3, insert4, insert5)

    def build_insert_statements_for_mysql(self):
        insert1 = """INSERT INTO journalentry (JOURNAL_NAME, CREATIONDATE, TYPE, MESSAGE)
                        VALUES ('main_index', CURRENT_TIMESTAMP() - INTERVAL 2 DAY, 'DELETE_CHANGE_DOCUMENTS', concat('com.atlassian.confluence.pages.Attachment-', (
                          SELECT CONTENTID
                          FROM CONTENT
                          WHERE PAGEID IN (
                            SELECT CONTENTID
                            FROM CONTENT
                            WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                              SELECT user_key
                              FROM user_mapping
                              WHERE username = '{}')))));""".format(self.username)

        insert2 = """INSERT INTO journalentry (JOURNAL_NAME, CREATIONDATE, TYPE, MESSAGE)
                        VALUES ('main_index', CURRENT_TIMESTAMP() - INTERVAL 2 DAY, 'DELETE_DOCUMENT', concat('com.atlassian.confluence.pages.Attachment-', (
                          SELECT CONTENTID
                          FROM CONTENT
                          WHERE PAGEID IN (
                            SELECT CONTENTID
                            FROM CONTENT
                            WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                              SELECT user_key
                              FROM user_mapping
                              WHERE username = '{}')))));""".format(self.username)

        insert3 = """INSERT INTO journalentry
                      SELECT @n := @n + 1                                                                AS id
                        ,    'main_index'                                                                AS JOURNAL_NAME
                        ,    CURRENT_TIMESTAMP() - INTERVAL 2 DAY                                        AS CREATIONDATE
                        ,    'DELETE_DOCUMENT'                                                           AS TYPE
                        ,    concat('com.atlassian.confluence.user.PersonalInformation-', tmp.CONTENTID) AS MESSAGE
                      FROM (
                             SELECT CONTENTID
                             FROM CONTENT
                             WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                               SELECT user_key
                               FROM user_mapping
                               WHERE username =
                                     '{}')) AS tmp,
                        (
                          SELECT @n := (
                            SELECT max(ENTRY_ID)
                            FROM journalentry)) AS parameter;""".format(self.username)

        insert4 = """INSERT INTO journalentry
                      SELECT @n := @n + 1                                                                AS id
                        ,    'main_index'                                                                AS JOURNAL_NAME
                        ,    CURRENT_TIMESTAMP() - INTERVAL 2 DAY                                        AS CREATIONDATE
                        ,    'DELETE_CHANGE_DOCUMENTS'                                                   AS TYPE
                        ,    concat('com.atlassian.confluence.user.PersonalInformation-', tmp.CONTENTID) AS MESSAGE
                      FROM (
                             SELECT CONTENTID
                             FROM CONTENT
                             WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                               SELECT user_key
                               FROM user_mapping
                               WHERE username =
                                     '{}')) AS tmp,
                        (
                          SELECT @n := (
                            SELECT max(ENTRY_ID)
                            FROM journalentry)) AS parameter;""".format(self.username)
        insert5 = """INSERT INTO journalentry (
                      SELECT @n := @n + 1        AS id
                        ,    'main_index'        AS JOURNAL_NAME
                        ,    CURRENT_TIMESTAMP() - INTERVAL 2 DAY AS CREATIONDATE
                        ,    type_name           AS TYPE
                        ,    CASE tmp.CONTENTTYPE
                            WHEN 'PAGE'
                                THEN concat('com.atlassian.confluence.pages.Page-', tmp.CONTENTID)
                            WHEN 'BLOGPOST'
                                THEN concat('com.atlassian.confluence.pages.BlogPost-', tmp.CONTENTID)
                            WHEN 'COMMENT'
                                THEN concat('com.atlassian.confluence.pages.Comment-', tmp.CONTENTID)
                            END                 AS message
                        FROM (
                            SELECT CONTENT.CONTENTID
                                , CONTENT.CONTENTTYPE
                            FROM CONTENT
                                JOIN BODYCONTENT
                                ON CONTENT.CONTENTID = BODYCONTENT.CONTENTID
                            WHERE CONTENT.PREVVER IS NULL
                                    AND CONTENT.CONTENTTYPE IN ('PAGE', 'BLOGPOST', 'COMMENT')
                                    AND CONTENT.CONTENT_STATUS = 'current'
                                    AND BODYCONTENT.BODY LIKE concat('%<ri:user ri:userkey="', (
                                SELECT user_key
                                FROM user_mapping
                                WHERE user_mapping.username = '{}'), '"%')) AS tmp
                        JOIN (SELECT 'ADD_CHANGE_DOCUMENT' AS type_name
                                UNION SELECT 'UPDATE_DOCUMENT' AS type_name) AS tmp_type
                            ON (1 = 1)
                        ,
                        (
                            SELECT @n := (
                            SELECT max(ENTRY_ID)
                            FROM journalentry)) AS parameter);""".format(self.username)

        # return "{}\n\n{}\n\n{}\n\n{}".format(insert1, insert2, insert3, insert4)
        # remove the attachment journal entry for now, since it breaks when there are more than 1 attachment (and also breaks for 0 attachment)
        return "{}\n\n{}\n\n{}".format(insert3, insert4, insert5)

    def build_insert_statements_for_postgres(self):
        insert1 = """INSERT INTO journalentry (entry_id, journal_name, creationdate, type, message) VALUES
                      (nextval('seq_journal_entry_id'), 'main_index', (current_timestamp(0) - INTERVAL '2 day') :: TIMESTAMP,
                       'DELETE_CHANGE_DOCUMENTS', concat('com.atlassian.confluence.pages.Attachment-', (
                        SELECT CONTENTID
                        FROM CONTENT
                        WHERE PAGEID IN (
                          SELECT CONTENTID
                          FROM CONTENT
                          WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                            SELECT user_key
                            FROM user_mapping
                            WHERE username = '{}')))));""".format(self.username)

        insert2 = """INSERT INTO journalentry (entry_id, journal_name, creationdate, type, message) VALUES
                      (nextval('seq_journal_entry_id'), 'main_index', (current_timestamp(0) - INTERVAL '2 day') :: TIMESTAMP, 'DELETE_DOCUMENT',
                       concat('com.atlassian.confluence.pages.Attachment-', (
                         SELECT CONTENTID
                         FROM CONTENT
                         WHERE PAGEID IN (
                           SELECT CONTENTID
                           FROM CONTENT
                           WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                             SELECT user_key
                             FROM user_mapping
                             WHERE username = '{}')))));""".format(self.username)

        insert3 = """INSERT INTO journalentry
                      SELECT nextval('seq_journal_entry_id')                                            AS entry_id
                        ,    'main_index'                                                               AS journal_name
                        ,    (current_timestamp(0) - INTERVAL '2 day') :: TIMESTAMP                     AS creationdate
                        ,    'DELETE_DOCUMENT'                                                          AS type
                        ,    concat('com.atlassian.confluence.user.PersonalInformation-', C2.CONTENTID) AS message
                      FROM (
                             SELECT CONTENTID
                             FROM CONTENT
                             WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                               SELECT user_key
                               FROM user_mapping
                               WHERE username = '{}')) AS C2;""".format(self.username)

        insert4 = """INSERT INTO journalentry
                      SELECT nextval('seq_journal_entry_id')                                            AS entry_id
                        ,    'main_index'                                                               AS journal_name
                        ,    (current_timestamp(0) - INTERVAL '2 day') :: TIMESTAMP                     AS creationdate
                        ,    'DELETE_CHANGE_DOCUMENTS'                                                  AS type
                        ,    concat('com.atlassian.confluence.user.PersonalInformation-', C2.CONTENTID) AS message
                      FROM (
                             SELECT CONTENTID
                             FROM CONTENT
                             WHERE CONTENTTYPE = 'USERINFO' AND CONTENT.USERNAME IN (
                               SELECT user_key
                               FROM user_mapping
                               WHERE username = '{}')) AS C2;""".format(self.username)

        insert5 = """INSERT INTO journalentry
                      SELECT nextval('seq_journal_entry_id') AS entry_id
                        ,    'main_index'                    AS journal_name
                        ,    (current_timestamp(0) - INTERVAL '2 day') :: TIMESTAMP AS creationdate
                        ,    type_name                       AS type
                        ,    CASE
                            WHEN (C2.CONTENTTYPE = 'PAGE')
                                THEN concat('com.atlassian.confluence.pages.Page-', C2.CONTENTID)
                            WHEN (C2.CONTENTTYPE = 'BLOGPOST')
                                THEN concat('com.atlassian.confluence.pages.BlogPost-', C2.CONTENTID)
                            WHEN (C2.CONTENTTYPE = 'COMMENT')
                                THEN concat('com.atlassian.confluence.pages.Comment-', C2.CONTENTID)
                            END               AS message
                        FROM (
                            SELECT CONTENT.CONTENTID
                                , CONTENT.CONTENTTYPE
                            FROM CONTENT
                                INNER JOIN BODYCONTENT
                                ON CONTENT.CONTENTID = BODYCONTENT.CONTENTID
                            WHERE CONTENTTYPE IN ('PAGE', 'BLOGPOST', 'COMMENT')
                                    AND CONTENT.PREVVER IS NULL
                                    AND CONTENT.CONTENT_STATUS = 'current'
                                    AND BODYCONTENT.BODY LIKE CONCAT('%<ri:user ri:userkey="', (
                                SELECT user_key
                                FROM user_mapping
                                WHERE user_mapping.username = '{}'), '"%')
                            ) AS C2
                        JOIN (SELECT 'ADD_CHANGE_DOCUMENT' AS type_name
                                UNION SELECT 'UPDATE_DOCUMENT' AS type_name) AS tmp_type
                            ON (1 = 1);""".format(self.username)


        # return "{}\n\n{}\n\n{}\n\n{}".format(insert1, insert2, insert3, insert4)
        # remove the attachment journal entry for now, since it breaks when there are more than 1 attachment (and also breaks for 0 attachment)
        return "{}\n\n{}\n\n{}".format(insert3, insert4, insert5)

    def get_sql_action(self, table_name, action):

        sql_action = ""
        action_type = action['type']

        if action_type == 'select':
            action_where = action['columns']
            sql_action = "{} {} from {}".format(action_type, action_where, table_name.strip())

            if self.database == self.db_postgresql:
                sql_action = '{} {} from "{}"'.format(action_type, action_where, table_name.strip())

        elif action_type == 'update':
            action_where = action['where']
            sql_action = "{} {} set {}".format(action_type, table_name.strip(), action_where)

        elif action_type == 'delete':
            sql_action = "{} from {}".format(action_type, table_name.strip())
        return sql_action


    def get_sql_table(self, table_name, table_description):

        actions = table_description[self.action]
        result = []
        final = ""

        for action in actions:
            if action['type'] == 'insert':
                final = self.build_insert_statements()
            else:
                action = self.get_sql_action(table_name, action)
                where = self.get_sql_where_clause(table_description[self.where])
                final = "{} where {};".format(action, where)

            result.append(final)

        result = "\n".join(result)
        return result


if __name__ == '__main__':

    try:

        search = SearchData()

        search.parse_args()

        search.execute()

    except Exception as e:

        print("ERROR: {}".format(e))
        traceback.print_exc()

        sys.exit(1)