PRAGMA foreign_keys = ON;

CREATE TABLE studies (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER  NOT NULL,
    type        TEXT     NOT NULL,
    reference   TEXT     NOT NULL,
    title       TEXT,
    status      INTEGER  DEFAULT 1,
    created     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated     DATETIME DEFAULT CURRENT_TIMESTAMP,
    total       INTEGER  DEFAULT 0,
    timestamp   DATETIME DEFAULT NULL,
    position    INTEGER  DEFAULT 0,
    repetitions INTEGER  DEFAULT 1,
    FOREIGN KEY (user_id) REFERENCES users(id)
);
CREATE TABLE IF NOT EXISTS "schedules" (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    user_id     INTEGER  NOT NULL,
    type        TEXT     NOT NULL,
    status      INTEGER  DEFAULT 1,
    created     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated     DATETIME DEFAULT CURRENT_TIMESTAMP,
    schedule    TEXT     NOT NULL,
    total       INTEGER  NOT NULL,
    position    INTEGER  DEFAULT 1,
    repeat_count INTEGER DEFAULT 0,
    study_id    INTEGER  NOT NULL,
    upcoming    DATETIME
);
CREATE TABLE notifications (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT, 
    created     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated     DATETIME DEFAULT CURRENT_TIMESTAMP, 
    user_id     INTEGER  NOT NULL, type TEXT not NULL, 
    status      INTEGER  DEFAULT 1, 
    destination TEXT, 
    schedule_id INTEGER  NOT NULL, 
    due         DATETIME, 
    content     TEXT, 
    subject     TEXT
);
CREATE TABLE IF NOT EXISTS "users" (
    id          INTEGER  PRIMARY KEY AUTOINCREMENT,
    email       TEXT     UNIQUE NOT NULL,
    password    TEXT,
    salt        TEXT,
    verified    INTEGER  DEFAULT 0,
    name        TEXT,
    fname       TEXT,
    lname       TEXT,
    interface   INTEGER  DEFAULT 1,
    created     DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated     DATETIME DEFAULT CURRENT_TIMESTAMP,
    timezone    TEXT,
    login       DATETIME DEFAULT null,
    config      TEXT     DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS collections (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    owner INTEGER NOT NULL,
    global INTEGER DEFAULT 0,
    passages TEXT NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    status INTEGER DEFAULT 1,
    created DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (owner) REFERENCES users(id)
);

-- Create trigger to auto-update the updated timestamp
CREATE TRIGGER IF NOT EXISTS collections_updated_trigger
AFTER UPDATE ON collections
BEGIN
    UPDATE collections
    SET updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;

CREATE VIEW s_s_n AS
SELECT *
FROM studies
JOIN schedules
  ON schedules.study_id = studies.id
LEFT JOIN notifications
  ON notifications.schedule_id = schedules.id
/* s_s_n(id,user_id,type,reference,title,status,created,updated,total,timestamp,"current","id:1","user_id:1","type:1","status:1","created:1","updated:1",schedule,"total:1","current:1",study_id,upcoming,"id:2","created:2","updated:2","user_id:2","type:2","status:2",destination,"timestamp:1",schedule_id,due,content,subject) */;
