import pytest
from src import buildsqlitedb
import os
import fnmatch

@pytest.fixture()
def test_db():
    return buildsqlitedb.BuildDB("testname")

def test_db_file(test_db):
    connection = test_db.opendb()
    fileexists = False
    for file in os.listdir("../db/"):
        if fnmatch.fnmatch(file, test_db.name + '*.db'):
            fileexists = True
    assert fileexists
    os.remove('../db/' + test_db.filename)

def test_db_connection(test_db):
    connection = test_db.opendb()
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE test_table (test1 text, test2 text)")
    connection.commit()
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'test_table'")
    connection.commit()
    tablestructure = cursor.fetchone()
    assert tablestructure[0] == "CREATE TABLE test_table (test1 text, test2 text)"
    os.remove('../db/' + test_db.filename)

def test_db_structure(test_db):
    connection = test_db.opendb()
    test_db.createschema(connection)
    cursor = connection.cursor()
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Video';")
    connection.commit()
    videoresponse = cursor.fetchone()
    assert videoresponse[0] == '''CREATE TABLE Video(
            number INTEGER PRIMARY KEY ASC, 
            id TEXT UNIQUE, 
            title TEXT,
            channelid TEXT,
            FOREIGN KEY (channelid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Channel';")
    connection.commit()
    channelresponse = cursor.fetchone()
    assert channelresponse[0] == '''CREATE TABLE Channel(
            number INTEGER PRIMARY KEY ASC,
            id TEXT UNIQUE,
            name TEXT)'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Watch_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Watch_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            vidid TEXT, 
            channelid TEXT,
            userid TEXT,
            FOREIGN KEY(vidid) REFERENCES Video(id),
            FOREIGN KEY (channelid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Playlist_Add_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Playlist_Add_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            vidid TEXT,
            userid TEXT,
            playlistid TEXT,
            FOREIGN KEY(vidid) REFERENCES Video(id),
            FOREIGN KEY(userid) REFERENCES Channel(id),
            FOREIGN KEY (playlistid) REFERENCES Playlist(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Subscription_Add_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Subscription_Add_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            channelid TEXT,
            userid TEXT,
            FOREIGN KEY(channelid) REFERENCES Channel(id),
            FOREIGN KEY (userid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Playlist';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Playlist(
            number INTEGER PRIMARY KEY ASC,
            id TEXT UNIQUE,
            name TEXT, 
            userid TEXT,
            FOREIGN KEY (userid) REFERENCES Channel(id))'''
    os.remove('../db/' + test_db.filename)

def test_get_vidid(test_db):
    assert test_db.getvidid("https://www.youtube.com/watch?v\u003dtestvidid1") == "testvidid1"

def test_get_chanid(test_db):
    assert test_db.getchanid("https://www.youtube.com/channel/testchannelid1") == "testchannelid1"

def test_vidinsertvalues(test_db):
    assert test_db.videoinsertvalues(
        { "header": "YouTube",
          "title": "Watched Test title 1",
          "titleUrl": "https://www.youtube.com/watch?v\u003dtestvidid1",
          "subtitles": [{
            "name": "Test channel 1",
            "url": "https://www.youtube.com/channel/testchannelid1"
          }],
          "time": "2019-07-14T16:35:31.366Z",
          "products": ["YouTube"]
        }) == ("testvidid1", "Test title 1")

def test_channelinsertvalues(test_db):
    assert test_db.channelinsertvalues({ "header": "YouTube",
      "title": "Watched Test title 1",
      "titleUrl": "https://www.youtube.com/watch?v\u003dtestvidid1",
      "subtitles": [{
        "name": "Test channel 1",
        "url": "https://www.youtube.com/channel/testchannelid1"
      }],
      "time": "2019-07-14T16:35:31.366Z",
      "products": ["YouTube"]
    }) == ("testchannelid1", "Test channel 1")



import pytest
from src import buildsqlitedb
import os
import fnmatch

@pytest.fixture()
def test_db():
    return buildsqlitedb.BuildDB("testname")

def test_db_file(test_db):
    connection = test_db.opendb()
    fileexists = False
    for file in os.listdir("../db/"):
        if fnmatch.fnmatch(file, test_db.name + '*.db'):
            fileexists = True
    assert fileexists
    os.remove('../db/' + test_db.filename)

def test_db_connection(test_db):
    connection = test_db.opendb()
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE test_table (test1 text, test2 text)")
    connection.commit()
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'test_table'")
    connection.commit()
    tablestructure = cursor.fetchone()
    assert tablestructure[0] == "CREATE TABLE test_table (test1 text, test2 text)"
    os.remove('../db/' + test_db.filename)

def test_db_structure(test_db):
    connection = test_db.opendb()
    test_db.createschema(connection)
    cursor = connection.cursor()
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Video';")
    connection.commit()
    videoresponse = cursor.fetchone()
    assert videoresponse[0] == '''CREATE TABLE Video(
            number INTEGER PRIMARY KEY ASC, 
            id TEXT UNIQUE, 
            title TEXT,
            channelid TEXT,
            FOREIGN KEY (channelid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Channel';")
    connection.commit()
    channelresponse = cursor.fetchone()
    assert channelresponse[0] == '''CREATE TABLE Channel(
            number INTEGER PRIMARY KEY ASC,
            id TEXT UNIQUE,
            name TEXT)'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Watch_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Watch_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            vidid TEXT, 
            channelid TEXT,
            userid TEXT,
            FOREIGN KEY(vidid) REFERENCES Video(id),
            FOREIGN KEY (channelid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Playlist_Add_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Playlist_Add_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            vidid TEXT,
            userid TEXT,
            playlistid TEXT,
            FOREIGN KEY(vidid) REFERENCES Video(id),
            FOREIGN KEY(userid) REFERENCES Channel(id),
            FOREIGN KEY (playlistid) REFERENCES Playlist(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Subscription_Add_Event';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Subscription_Add_Event(
            number INTEGER PRIMARY KEY ASC,
            timestamp TEXT,
            channelid TEXT,
            userid TEXT,
            FOREIGN KEY(channelid) REFERENCES Channel(id),
            FOREIGN KEY (userid) REFERENCES Channel(id))'''
    cursor.execute("SELECT sql FROM sqlite_master WHERE name = 'Playlist';")
    connection.commit()
    watcheventresponse = cursor.fetchone()
    assert watcheventresponse[0] == '''CREATE TABLE Playlist(
            number INTEGER PRIMARY KEY ASC,
            id TEXT UNIQUE,
            name TEXT, 
            userid TEXT,
            FOREIGN KEY (userid) REFERENCES Channel(id))'''
    os.remove('../db/' + test_db.filename)

def test_get_vidid(test_db):
    assert test_db.getvidid("https://www.youtube.com/watch?v\u003dtestvidid1") == "testvidid1"

def test_get_chanid(test_db):
    assert test_db.getchanid("https://www.youtube.com/channel/testchannelid1") == "testchannelid1"

def test_vidinsertvalues(test_db):
    assert test_db.videoinsertvalues(
        { "header": "YouTube",
          "title": "Watched Test title 1",
          "titleUrl": "https://www.youtube.com/watch?v\u003dtestvidid1",
          "subtitles": [{
            "name": "Test channel 1",
            "url": "https://www.youtube.com/channel/testchannelid1"
          }],
          "time": "2019-07-14T16:35:31.366Z",
          "products": ["YouTube"]
        }) == ("testvidid1", "Test title 1")

def test_channelinsertvalues(test_db):
    assert test_db.channelinsertvalues({ "header": "YouTube",
      "title": "Watched Test title 1",
      "titleUrl": "https://www.youtube.com/watch?v\u003dtestvidid1",
      "subtitles": [{
        "name": "Test channel 1",
        "url": "https://www.youtube.com/channel/testchannelid1"
      }],
      "time": "2019-07-14T16:35:31.366Z",
      "products": ["YouTube"]
    }) == ("testchannelid1", "Test channel 1")



CREATE TABLE Video(             number INTEGER PRIMARY KEY ASC,              id TEXT UNIQUE,              title TEXT,             channelid TEXT,             FOREIGN KEY (channelid) REFERENCES Channel(id)) CREATE TABLE Channel(             number INTEGER PRIMARY KEY ASC,             id TEXT UNIQUE,             name TEXT) CREATE TABLE Watch_Event(             number INTEGER PRIMARY KEY ASC,             timestamp TEXT,             vidid TEXT,              channelid TEXT,             userid TEXT,             FOREIGN KEY(vidid) REFERENCES Video(id),             FOREIGN KEY (channelid) REFERENCES Channel(id)) CREATE TABLE Playlist_Add_Event(             number INTEGER PRIMARY KEY ASC,             timestamp TEXT,             vidid TEXT,             userid TEXT,             playlistid TEXT,             FOREIGN KEY(vidid) REFERENCES Video(id),             FOREIGN KEY(userid) REFERENCES Channel(id),             FOREIGN KEY (playlistid) REFERENCES Playlist(id)) CREATE TABLE Subscription_Add_Event(             number INTEGER PRIMARY KEY ASC,             timestamp TEXT,             channelid TEXT,             userid TEXT,             FOREIGN KEY(channelid) REFERENCES Channel(id),             FOREIGN KEY (userid) REFERENCES Channel(id)) CREATE TABLE Playlist(             number INTEGER PRIMARY KEY ASC,             id TEXT UNIQUE,             name TEXT,              userid TEXT,             FOREIGN KEY (userid) REFERENCES Channel(id))