import sqlite3
import os.path
import time

def init(path):

    is_db_new = not os.path.isfile(path)

    database = sqlite3.connect(path)

    cursor = database.cursor()

    if is_db_new:

        cursor.execute(
            " CREATE TABLE IF NOT EXISTS tasks("
            "   id INTEGER PRIMARY KEY,"
            "   created_at INTEGER NOT NULL,"
            "   updated_at INTEGER NOT NULL,"
            "   name TEXT NOT NULL,"
            "   due_date TEXT,"
            "   description TEXT,"
            "   status TEXT"
            " )")

        cursor.execute(
            " CREATE TABLE IF NOT EXISTS tags("
            "   id INTEGER PRIMARY KEY,"
            "   created_at INTEGER NOT NULL,"
            "   updated_at INTEGER NOT NULL,"
            "   name TEXT NOT NULL,"
            "   color TEXT NOT NULL"
            " )")

        cursor.execute(
            " CREATE TABLE IF NOT EXISTS task_tags("
            "   task_id INTEGER NOT NULL,"
            "   tag_id INTEGER NOT NULL,"
            "   created_at INTEGER NOT NULL,"
            "   FOREIGN KEY (task_id) REFERENCES tasks(id),"
            "   FOREIGN KEY (tag_id) REFERENCES tags(id),"
            "   PRIMARY KEY (task_id, tag_id)"
            " )")

    cursor.execute("PRAGMA foreign_keys = ON")

    database.commit()

    return database