-- -- 1. Users table
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    username   TEXT    NOT NULL UNIQUE,            -- required, unique, lowercase only
    password   TEXT    NOT NULL,                   -- required (stored in cleartext)
    disabled   INTEGER NOT NULL DEFAULT 0,         -- boolean flag (0 = false, 1 = true)
    type       TEXT    NOT NULL DEFAULT 'user',    -- user type: 'user' or 'admin'
    apikey     TEXT,                               -- optional API key, must be NULL or exactly 32 chars
    CHECK(length(username) BETWEEN 1 AND 100),     -- username >= 1 and <= 100 chars
    CHECK(username NOT GLOB '*[^a-z]*'),           -- username only contains a-z (no other chars)
    CHECK(length(password) >= 8),                  -- password min length 8
    CHECK(disabled IN (0, 1)),                     -- disabled must be 0 or 1
    CHECK(type IN ('user', 'admin')),              -- type must be 'user' or 'admin'
    CHECK(apikey IS NULL OR length(apikey) = 32)   -- apikey must be NULL or exactly 32 characters
);
PRAGMA foreign_keys = ON;

-- 1. Users table
CREATE TABLE users (
    id         INTEGER PRIMARY KEY,
    username   TEXT    NOT NULL UNIQUE,            -- required, unique, lowercase only
    password   TEXT    NOT NULL,                   -- required (stored in cleartext)
    disabled   INTEGER NOT NULL DEFAULT 0,         -- boolean flag (0 = false, 1 = true)
    type       TEXT    NOT NULL DEFAULT 'user',    -- user type: 'user' or 'admin'
    CHECK(length(username) BETWEEN 1 AND 100),     -- username >= 1 and <= 100 chars
    CHECK(username NOT GLOB '*[^a-z]*'),           -- username only contains a-z (no other chars):contentReference[oaicite:1]{index=1}
    CHECK(length(password) >= 8),                  -- password min length 8
    CHECK(disabled IN (0, 1)),                     -- disabled must be 0 or 1
    CHECK(type IN ('user', 'admin'))               -- type must be 'user' or 'admin'
);

-- 2. People table
CREATE TABLE people (
    id           INTEGER PRIMARY KEY,
    first_name   TEXT    NOT NULL,    -- required; 1-100 chars
    last_name    TEXT    NOT NULL,    -- required; 1-100 chars
    relationship TEXT,                -- optional; NULL or up to 300 chars (no empty string)
    tags         TEXT,                -- optional; comma-delimited tags or NULL (no empty string)
    added        TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),  -- when added (date/time)
    creator_id   INTEGER NOT NULL,    -- which user created this person (FK to users)
    birth_date   TEXT,                -- optional full date (YYYY-MM-DD or similar)
    birth_day    TEXT,                -- optional birthday (MM-DD format)
    birth_year   INTEGER,             -- optional birth year (YYYY)
    CHECK(length(first_name) BETWEEN 1 AND 100),   -- first_name not empty, max 100
    CHECK(length(last_name)  BETWEEN 1 AND 100),   -- last_name not empty, max 100
    CHECK(relationship IS NULL OR relationship != ''),         -- empty string => NULL
    CHECK(relationship IS NULL OR length(relationship) <= 300),-- max 300 chars
    CHECK(tags IS NULL 
          OR (tags NOT LIKE '% %'                             -- no spaces in tags
              AND tags NOT GLOB '*[^a-z0-9,-]*'               -- only a-z, 0-9, comma, dash allowed
              AND tags NOT LIKE ',%' AND tags NOT LIKE '%,'   -- cannot start or end with comma
              AND tags NOT LIKE '%,,%')),                     -- no empty tag (no ',,' sequence)
    CHECK( /* Exactly one of birth_date, birth_day, birth_year is non-null (or all NULL) */
          (birth_date IS NOT NULL AND birth_day IS NULL AND birth_year IS NULL)
       OR (birth_date IS NULL AND birth_day IS NOT NULL AND birth_year IS NULL)
       OR (birth_date IS NULL AND birth_day IS NULL AND birth_year IS NOT NULL)
       OR (birth_date IS NULL AND birth_day IS NULL AND birth_year IS NULL) ),
    FOREIGN KEY(creator_id) REFERENCES users(id) ON DELETE RESTRICT   -- prevent deleting a user if referenced:contentReference[oaicite:2]{index=2}
);

-- 3. Notes table
CREATE TABLE notes (
    id         INTEGER PRIMARY KEY,
    body       TEXT    NOT NULL,      -- required note text (cannot be empty)
    tags       TEXT,                  -- optional tags (same format rules as people.tags)
    added      TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),  -- when added (date/time)
    creator_id INTEGER NOT NULL,      -- which user created this note (FK to users)
    CHECK(length(body) > 0),          -- body must not be empty
    CHECK(tags IS NULL 
          OR (tags NOT LIKE '% %' 
              AND tags NOT GLOB '*[^a-z0-9,-]*' 
              AND tags NOT LIKE ',%' AND tags NOT LIKE '%,' 
              AND tags NOT LIKE '%,,%')),
    FOREIGN KEY(creator_id) REFERENCES users(id) ON DELETE RESTRICT   -- cannot delete user with existing notes
);

-- 4. Note-Person linking table (to link multiple people to a note)
CREATE TABLE note_person (
    note_id   INTEGER NOT NULL,
    person_id INTEGER NOT NULL,
    PRIMARY KEY (note_id, person_id),   -- composite PK to prevent duplicate links
    FOREIGN KEY(note_id)   REFERENCES notes(id)   ON DELETE CASCADE,
    FOREIGN KEY(person_id) REFERENCES people(id)  ON DELETE CASCADE
    -- ^ If a note is deleted, its links are removed; if a person is deleted, links removed (see triggers for additional rules)
);

-- 5. History Entries table (audit log for People and Notes changes)
CREATE TABLE entries (
    id        INTEGER PRIMARY KEY,
    entity    TEXT    NOT NULL,   -- 'person' or 'note' to indicate the entity type
    entity_id INTEGER NOT NULL,   -- ID of the person or note that was archived
    action    TEXT    NOT NULL,   -- 'update' or 'delete'
    date      TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),  -- timestamp of archival
    data      TEXT    NOT NULL,   -- serialized JSON of the old record data
    CHECK(entity IN ('person', 'note')),
    CHECK(action IN ('update', 'delete'))
);

-- 6. Auxiliary table for triggers (to allow note deletions without violating link rule)
CREATE TABLE note_delete_flags (
    note_id INTEGER PRIMARY KEY
);


-- Trigger: flag_note_delete 
-- Purpose: Before a note is deleted, mark it in note_delete_flags to signal a note deletion in progress.
CREATE TRIGGER flag_note_delete
BEFORE DELETE ON notes
FOR EACH ROW
BEGIN
    INSERT OR REPLACE INTO note_delete_flags(note_id) VALUES (OLD.id);
END;

-- Trigger: unflag_note_delete 
-- Purpose: After a note deletion, remove the flag.
CREATE TRIGGER unflag_note_delete
AFTER DELETE ON notes
FOR EACH ROW
BEGIN
    DELETE FROM note_delete_flags WHERE note_id = OLD.id;
END;

-- Trigger: prevent_orphan_note 
-- Purpose: Prevent removing the last person from a note *except* when the note itself is being deleted.
CREATE TRIGGER prevent_orphan_note
BEFORE DELETE ON note_person
FOR EACH ROW
BEGIN
    SELECT CASE 
        WHEN (SELECT COUNT(*) FROM note_person WHERE note_id = OLD.note_id) = 1 
          AND (SELECT COUNT(*) FROM note_delete_flags WHERE note_id = OLD.note_id) = 0 
        THEN RAISE(ABORT, 'Cannot remove last person from a note.') 
    END;
    /* This aborts the deletion if it’s the last link and no note-deletion is in progress:contentReference[oaicite:3]{index=3} */
END;

-- Trigger: archive_person_update 
-- Purpose: On person update, save the old record to history entries (action 'update').
CREATE TRIGGER archive_person_update
AFTER UPDATE ON people
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES (
        'person', OLD.id, 'update',
        json_object(
            'id',         OLD.id,
            'first_name', OLD.first_name,
            'last_name',  OLD.last_name,
            'relationship', OLD.relationship,
            'tags',       OLD.tags,
            'added',      OLD.added,
            'creator_id', OLD.creator_id,
            'birth_date', OLD.birth_date,
            'birth_day',  OLD.birth_day,
            'birth_year', OLD.birth_year
        )
    );
END;

-- Trigger: archive_person_delete 
-- Purpose: On person deletion, save the old record to history entries (action 'delete') *before* it is gone.
CREATE TRIGGER archive_person_delete
BEFORE DELETE ON people
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES (
        'person', OLD.id, 'delete',
        json_object(
            'id',         OLD.id,
            'first_name', OLD.first_name,
            'last_name',  OLD.last_name,
            'relationship', OLD.relationship,
            'tags',       OLD.tags,
            'added',      OLD.added,
            'creator_id', OLD.creator_id,
            'birth_date', OLD.birth_date,
            'birth_day',  OLD.birth_day,
            'birth_year', OLD.birth_year
        )
    );
END;

-- Trigger: archive_note_update 
-- Purpose: On note update, archive the old note record.
CREATE TRIGGER archive_note_update
AFTER UPDATE ON notes
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES (
        'note', OLD.id, 'update',
        json_object(
            'id',        OLD.id,
            'body',      OLD.body,
            'tags',      OLD.tags,
            'added',     OLD.added,
            'creator_id',OLD.creator_id
        )
    );
END;

CREATE TRIGGER archive_note_delete
BEFORE DELETE ON notes
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES (
        'note', OLD.id, 'delete',
        json_object(
            'id',        OLD.id,
            'body',      OLD.body,
            'tags',      OLD.tags,
            'added',     OLD.added,
            'creator_id',OLD.creator_id
        )
    );
END;


-- Trigger: archive_person_insert 
-- Purpose: On person insertion, log the insert action with empty data.
CREATE TRIGGER archive_person_insert
AFTER INSERT ON people
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES ('person', NEW.id, 'insert', '');
END;

-- Trigger: archive_note_insert 
-- Purpose: On note insertion, log the insert action with empty data.
CREATE TRIGGER archive_note_insert
AFTER INSERT ON notes
FOR EACH ROW
BEGIN
    INSERT INTO entries(entity, entity_id, action, data)
    VALUES ('note', NEW.id, 'insert', '');
END;