import sqlite3
import pandas as pd
import json
from datetime import datetime

timeframe = 'data\\databaseChatbot'
database = "data\\RC_2013-06"
sql_transaction=[]
connection=sqlite3.connect('{}.db'.format(timeframe))
c=connection.cursor()

def create_table():
    c.execute("""CREATE TABLE IF NOT EXISTS parent_reply(parent_id TEXT PRIMARY KEY,comment_id TEXT UNIQUE,parent TEXT,
    comment TEXT,subreddit TEXT,unix INT,score INT)""")


def format_data(data):
    data=data.replace("\n","").replace("\r","").replace('"',"'")
    return data

def find_parent(pid):
    try:
        sql="SELECT comment FROM parent_reply WHERE comment_id= '{}' LIMIT 1".format(pid);
        c.execute(sql)
        result=c.fetchone()
        if result!=None:
            return result[0]
        else:
            return False
    except Exception as e:
        #print("find_parent",e)
        return False


def find_existing_score(pid):
    try:
        sql = "SELECT score FROM parent_reply WHERE parent_id= '{}' LIMIT 1".format(pid);
        c.execute(sql)
        result = c.fetchone()
        if result != None:
            return result[0]
        else:
            return False
    except Exception as e:
        # print("find_parent",e)
        return False

def acceptable(data):
    if len(data.split(' ')) > 50 or len(data)<1:
        return False
    elif len(data)>1000:
        return False
    elif data=='[deleted]' or data=='[removed]':
        return False
    else:
        return True

def transaction_bldr(sql):
    #global sql_transaction
    #sql_transaction.append(sql)
    # if len(sql_transaction)>100:
    #     c.execute('BEGIN TRANSACTION')
    #     for s in sql_transaction:
    #         try:
    c.execute(sql)
            # except:
            #     print("NOOOO")
    connection.commit()
    #sql_transaction=[]

def sql_insert_replace_comment(comment_id,parent_id,parent_data,body,subreddit,created_utc,score):
    try:
        sql="""UPDATE parent_reply SET parent_id="{}",comment_id="{}",parent="{}",comment="{}",subreddit="{}",unix={},score={} WHERE parent_id="{}";""".format(parent_id,comment_id,parent_data,body,subreddit,created_utc,score,parent_id)
        transaction_bldr(sql)
    except Exception as e:
        print('replace comment',str(e))
def sql_insert_has_parent(comment_id,parent_id,parent_data,body,subreddit,created_utc,score):
    try:
        sql="""INSERT INTO parent_reply(parent_id,comment_id,parent,comment,subreddit,unix,score) VALUES ("{}","{}","{}","{}","{}",{},{}); """.format(parent_id,comment_id,parent_data,body,subreddit,created_utc,score)
        transaction_bldr(sql)
    except Exception as e:
        print('s-parent-insertion',str(e))
def sql_insert_no_parent(comment_id,parent_id,body,subreddit,created_utc,score):
    try:
        sql="""INSERT INTO parent_reply(parent_id,comment_id,comment,subreddit,unix,score) VALUES ("{}","{}","{}","{}",{},{}); """.format(parent_id,comment_id,body,subreddit,created_utc,score)
        transaction_bldr(sql)
    except Exception as e:
        print('s-no-parent-insertion',str(e))

if __name__=='__main__':
    create_table()
    row_counter=0
    paired_rows=0
    with open(database,buffering=10) as f:
    #with open("RC_2006-03", buffering=10) as f:
        for row in f:
            row_counter+=1;
            row=json.loads(row)
            parent_id=row['parent_id']
            body=format_data(row['body'])
            created_utc=row['created_utc']
            score=row['score']
            subreddit=row['subreddit']
            comment_id=row['name'] #za stare verzije ovde ide id, a kod parent_id se splituje po donjoj crti i uzima od 1
            parent_data=find_parent(parent_id)

            if score>=2:
                if acceptable(body):
                    existing_comment_score=find_existing_score(parent_id)  #is there a comment in parent id that already has the score greater than current score if not we dont care
                    if existing_comment_score:
                        if score>existing_comment_score:
                            sql_insert_replace_comment(comment_id,parent_id,parent_data,body,subreddit,created_utc,score)
                    else:
                        if parent_data:
                            sql_insert_has_parent(comment_id,parent_id,parent_data,body,subreddit,created_utc,score)
                            paired_rows+=1
                        else:
                            sql_insert_no_parent(comment_id,parent_id,body,subreddit,created_utc,score)
            if row_counter==2608420: #260842
                print("Total rows read: {}, Paired rows: {},Time:{}".format(row_counter,paired_rows,str(datetime.now())))
                break
    print("Row couter",row_counter)