

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) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE 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,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (study_id) REFERENCES studies(id) ON UPDATE CASCADE ON DELETE CASCADE
);

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,
FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (schedule_id) REFERENCES schedules(id) ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE 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) ON UPDATE CASCADE ON DELETE CASCADE
);


CREATE TRIGGER IF NOT EXISTS collections_updated_trigger
AFTER UPDATE ON collections
FOR EACH ROW
-- Only trigger if the new update isn't already changing the timestamp
WHEN NEW.updated <= OLD.updated 
BEGIN
    UPDATE collections
    SET updated = CURRENT_TIMESTAMP
    WHERE id = NEW.id;
END;

-- Studies Update Trigger
CREATE TRIGGER IF NOT EXISTS studies_updated_trigger AFTER UPDATE ON studies
FOR EACH ROW WHEN NEW.updated <= OLD.updated
BEGIN
    UPDATE studies SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Schedules Update Trigger
CREATE TRIGGER IF NOT EXISTS schedules_updated_trigger AFTER UPDATE ON schedules
FOR EACH ROW WHEN NEW.updated <= OLD.updated
BEGIN
    UPDATE schedules SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;

-- Notifications Update Trigger
CREATE TRIGGER IF NOT EXISTS notifications_updated_trigger AFTER UPDATE ON notifications
FOR EACH ROW WHEN NEW.updated <= OLD.updated
BEGIN
    UPDATE notifications SET updated = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;
END;


Granular Updates: If you only want the timestamp to change when specific columns are modified (e.g., ignoring login time updates in the users table), you can use the UPDATE OF column1, column2 syntax.
​
