#!/usr/bin/env python3
import re
import sqlite3

con = sqlite3.connect("be.db")
cur = con.cursor()

# ID;;Pony?;;Title;;Length;;Channel Title;;Channel ID;;Publish Date;;Views;;18+?;;Livestream?

lines = []
with open("csv_spreadsheet_23_07_21_0006AM.csv", "r") as csv:
    for x in csv:
        l = re.sub("\n", "", x)
        l = re.split("(?<=\");(?=\")", l)
        l = [re.sub("^\"|\"$", "", x) for x in l]
        del l[0]
        # Ignore non-pony
        if l[1] != "N":
            lines.append(l)

pony = list(filter(lambda x: x[1] == "Y", lines))
kinda_pony = list(filter(lambda x: x[1] == "K", lines))

# We need the remaining videos still
uncategorized = []
for a in lines:
    if a[1] != "Y" or a[1] != "K":
        uncategorized.append(a)

print("Pony videos: " + str(len(pony)))
print("Kinda pony videos: " + str(len(kinda_pony)))
print("Uncategorized: " + str(len(uncategorized)))

with open("uncategorized_videos.txt", "w+") as uv:
    for x in uncategorized:
        uv.write(";;".join(x))
        uv.write("\n")
uv.close()

for x in pony:
    # id
    # title
    # approx_length
    # channel
    # published_on
    # views
    # adult_content
    # livestream
    # saved
    # Some videos have missing views
    if len(x) > 7:
        cur.execute("""
                    INSERT INTO pony_videos (id, title, approx_length, channel, channel_id, published_on, views)
                    VALUES
                    (?,?,?,?,?,?,?)
                    """, (x[0], x[2], x[3], x[4], x[5], x[6], x[7]))
        con.commit()

    elif len(x) < 7:
        cur.execute("""
                    INSERT INTO pony_videos (id, title, approx_length, channel, channel_id, published_on)
                    VALUES
                    (?,?,?,?,?,?)
                    """, (x[0], x[2], x[3], x[4], x[5], x[6]))
        con.commit()

        
for x in kinda_pony:
    if len(x) > 7:
        cur.execute("""
                    INSERT INTO kinda_pony_videos (id, title, approx_length, channel, channel_id, published_on, views)
                    VALUES
                    (?,?,?,?,?,?,?)
                    """, (x[0], x[2], x[3], x[4], x[5], x[6], x[7]))
        con.commit()

    elif len(x) < 7:
        cur.execute("""
                    INSERT INTO kinda_pony_videos (id, title, approx_length, channel, channel_id, published_on)
                    VALUES
                    (?,?,?,?,?,?)
                    """, (x[0], x[2], x[3], x[4], x[5], x[6]))
        con.commit()

    
csv.close()
con.close()