import sqlite3


def create_or_open_db(filename):
    """ Create (1st time) or open database
    :argument:
        filename: str           database file
    :returns:
        conn: sqlite3 database connection
        cursor: sqlite3 database cursor
    """

    conn = sqlite3.connect(filename)
    cursor = conn.cursor()

    sql = """ CREATE TABLE IF NOT EXISTS note (
        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
        bibkey TEXT UNIQUE NOT NULL,
        author TEXT NOT NULL,
        genre TEXT, 
        thesis TEXT, 
        hypothesis TEXT,
        method TEXT,
        finding TEXT, 
        comment TEXT,
        img_linkstr TEXT
    );"""
    cursor.execute(sql)
    
    sql = """ CREATE TABLE IF NOT EXISTS tags (
        bibkey TEXT NOT NULL,
        tag TEXT NOT NULL
    );"""
    cursor.execute(sql)

    # create fts5 virtual table for full text search
    sql = """ CREATE VIRTUAL TABLE IF NOT EXISTS fts USING fts5(
        author,
        thesis, 
        hypothesis,
        method, 
        finding, 
        comment,
        content="note",
        content_rowid="id"
    );
    """
    cursor.execute(sql)

    # create triggers
    cursor.execute(""" CREATE TRIGGER IF NOT EXISTS tbl_ai 
    AFTER INSERT ON note BEGIN
    INSERT INTO fts(rowid, author, thesis, hypothesis, method, 
        finding, comment) VALUES (new.id, new.author, new.thesis, new.hypothesis,
        new.method, new.finding, new.comment);
    END;""")
    cursor.execute(""" CREATE TRIGGER IF NOT EXISTS tbl_ad 
    AFTER DELETE ON note BEGIN
      INSERT INTO fts(fts, rowid, author, thesis, hypothesis, method, 
        finding, comment) VALUES ('delete', old.id, old.author, old.thesis, 
        old.hypothesis, old.method, old.finding, old.comment);
    END;""")
    cursor.execute(""" CREATE TRIGGER IF NOT EXISTS tbl_au 
    AFTER UPDATE ON note BEGIN
      INSERT INTO fts(fts, rowid, author, thesis, hypothesis, method, 
        finding, comment) VALUES ('delete', old.id, old.author, old.thesis, 
        old.hypothesis, old.method, old.finding, old.comment);
      INSERT INTO fts(rowid, author, thesis, hypothesis, method, 
        finding, comment) VALUES (new.id, new.author, new.thesis, new.hypothesis,
        new.method, new.finding, new.comment);
    END;""")

    conn.commit()

    return conn, cursor