from reddit_to_csv import sub2csv
import csv
import sqlite3

'''insert into table sub_categories first'''
'''make sure constraints are met'''
# -- for foreign_keys support (on delete cascade)
# --con.execute("PRAGMA foreign_keys = ON") for python


def csv_2_list(csv_name):
    with open(csv_name) as f:
        data = [list(line) for line in csv.reader(f)]
    return data


def list_2_database(data_list, category_id=0, sub_category_id=0):

    conn = sqlite3.connect('w2w.db')
    conn.execute("PRAGMA foreign_keys = ON")
    for link in data_list:
        cursor = conn.cursor()
        data_2_insert = [category_id, sub_category_id,
                         link[0].decode('utf-8'), link[1].decode('utf-8')]
        # print data_2_insert
        cursor.execute(
            "insert into all_links (c_fid, s_fid,link_title, link_url)values (?,?,?,?)", data_2_insert)
    conn.commit()
    conn.close()

'''standupcomedy'''

# sub_csv_name = sub2csv('standupcomedy', 450)
# sub_data = csv_2_list(sub_csv_name)
# list_2_database(sub_data, category_id=2, sub_category_id=1)

# '''Music sub categories'''

# sub_csv_name = sub2csv('musicvideos', 200)
# sub_data = csv_2_list(sub_csv_name)
# list_2_database(sub_data, category_id=4, sub_category_id=1)

# sub_csv_name = sub2csv('coversongs', 200)
# sub_data = csv_2_list(sub_csv_name)
# list_2_database(sub_data, category_id=4, sub_category_id=2)

sub_csv_name = sub2csv('AcousticCovers', 200)
sub_data = csv_2_list(sub_csv_name)
list_2_database(sub_data, category_id=4, sub_category_id=3)

