import os, sqlite3

con = sqlite3.connect("table_compare.sqlite")
csr = con.cursor()
csr.executescript('''
    DROP TABLE IF EXISTS DPOS;
    DROP TABLE IF EXISTS PDIM;
    DROP TABLE IF EXISTS TABLE_COMPARE;

    CREATE TABLE IF NOT EXISTS DPOS
    ( TABLE_NAME TEXT PRIMARY KEY
    , COMMENTS TEXT);

    CREATE TABLE IF NOT EXISTS PDIM
    ( TABLE_NAME TEXT PRIMARY KEY
    , COMMENTS TEXT);

    DELETE FROM DPOS;
    DELETE FROM PDIM;
''')

fh = open("DPOS.txt", encoding="utf-8")
print("DPOS start")
for line in fh :
	# print(line.split("\t"))
    _line = line.split("\t")
    _table_name = _line[0]
    _comments = _line[1].replace("\n", "")
    csr.execute("INSERT OR IGNORE INTO DPOS (TABLE_NAME, COMMENTS) VALUES (?, ?)", (_table_name, _comments))
    con.commit()
fh.close()

fh = open("PDIM.txt", encoding="utf-8")
print("PDIM start")
for line in fh :
	# print(line.split("\t"))
    _line = line.split("\t")
    _table_name = _line[0]
    _comments = _line[1].replace("\n", "")
    csr.execute("INSERT OR IGNORE INTO PDIM (TABLE_NAME, COMMENTS) VALUES (?, ?)", (_table_name, _comments))
    con.commit()
fh.close()

csr.executescript('''
    CREATE TABLE TABLE_COMPARE
    AS
    SELECT  MIN(T.DPOS_COMMENTS)	AS DPOS_COMMENTS
    ,		T.TABLE_NAME
    ,		MIN(T.PDIM_COMMENTS)	AS PDIM_COMMENTS
    FROM (
    		SELECT 	D.TABLE_NAME
    		,		CASE WHEN D.COMMENTS = ''
    					THEN 'No comments'
    					ELSE D.COMMENTS
    				END				AS DPOS_COMMENTS
    		,		NULL			AS PDIM_COMMENTS
    		FROM DPOS D

    		UNION ALL

    		SELECT 	P.TABLE_NAME
    		,		NULL			AS DPOS_COMMENTS
    		,		CASE WHEN P.COMMENTS = ''
    					THEN 'No comments'
    					ELSE P.COMMENTS
    				END				AS PDIM_COMMENTS
    		FROM PDIM P) T

    GROUP BY T.TABLE_NAME
''')
con.commit()

csr.close()
con.close()

print("Finished")