-- Copyright 2019 Michel Petit / Cogito Ergo Dev
-- 
-- Permission is hereby granted, free of charge, to any person obtaining a copy
-- of this software and associated documentation files (the "Software"), to deal
-- in the Software without restriction, including without limitation the rights
-- to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
-- copies of the Software, and to permit persons to whom the Software is
-- furnished to do so, subject to the following conditions:
--
-- The above copyright notice and this permission notice shall be included in
-- all copies or substantial portions of the Software.
--
-- THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
-- IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
-- FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
-- AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
-- LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
-- OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
-- SOFTWARE.

CREATE TABLE languages (
    code TEXT PRIMARY KEY UNIQUE,
    name TEXT
);

INSERT INTO languages (code, name)
VALUES      ('en', 'English'),
            ('fr', 'Français');

CREATE TABLE articles (
    id INTEGER PRIMARY KEY UNIQUE,
    created_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
    deleted_at DATETIME
);

CREATE TABLE translated_articles (
    id              INTEGER     PRIMARY KEY UNIQUE,
    article_id      INTEGER     REFERENCES articles (id),
    language_code   TEXT        REFERENCES languages (code),
    title           TEXT        NOT NULL,
    content         TEXT        NOT NULL,
    is_published    BOOLEAN     NOT NULL    DEFAULT(0) CHECK (is_published IN (0, 1) ),
    published_at    DATETIME,
    modified_at     DATETIME    DEFAULT (datetime('now') ) NOT NULL,
    UNIQUE (
        article_id,
        language_code
    )
);

CREATE TRIGGER on_insert_article
         AFTER INSERT
            ON translated_articles
      FOR EACH ROW
          WHEN NEW.is_published = 1 AND 
               NEW.published_at IS NULL
BEGIN
    UPDATE translated_articles
       SET published_at = datetime('now') 
     WHERE id = NEW.id;
END;


CREATE TRIGGER on_update_article_publication_status
         AFTER UPDATE OF is_published
            ON translated_articles
      FOR EACH ROW
          WHEN OLD.is_published = 0 AND 
               NEW.is_published = 1 AND 
               OLD.published_at IS NULL
BEGIN
    UPDATE translated_articles
       SET published_at = datetime('now') 
     WHERE id = OLD.id;
END;

CREATE TRIGGER on_insert_article_without_parent
         AFTER INSERT
            ON translated_articles
      FOR EACH ROW
          WHEN NEW.article_id IS NULL
BEGIN
    INSERT INTO articles (
                             id,
                             created_at
                         )
                         VALUES (
                             NULL,
                             NEW.modified_at
                         );
    UPDATE translated_articles
       SET article_id = last_insert_rowid() 
     WHERE id = NEW.id;
END;


CREATE TABLE tags (
    id         INTEGER     PRIMARY KEY UNIQUE,
    created_at DATETIME DEFAULT (datetime('now') ) NOT NULL,
    deleted_at DATETIME
);

CREATE TABLE translated_tags (
    id              INTEGER     PRIMARY KEY UNIQUE,
    tag_id          INTEGER     REFERENCES tags (id),
    language_code   TEXT        REFERENCES languages (code),
    title           TEXT        NOT NULL,
    modified_at     DATETIME    DEFAULT (datetime('now') ) NOT NULL,
    UNIQUE (
        tag_id,
        language_code
    )
);

CREATE TRIGGER on_insert_tag_without_parent
         AFTER INSERT
            ON translated_tags
      FOR EACH ROW
          WHEN NEW.tag_id IS NULL
BEGIN
    INSERT INTO tags (
                         id,
                         created_at
                     )
                     VALUES (
                         NULL,
                         NEW.modified_at
                     );
    UPDATE translated_tags
       SET tag_id = last_insert_rowid() 
     WHERE id = NEW.id;
END;

CREATE TABLE articles_tags (
    id              INTEGER     PRIMARY KEY UNIQUE,
    article_id      INTEGER     REFERENCES articles (id),
    tag_id          INTEGER     REFERENCES tags (id),
    UNIQUE (
        tag_id,
        article_id
    )
);

CREATE VIEW v_public_articles_listing AS
    SELECT a.id AS id,
           ta.language_code AS language_code,
           l.name AS language_name,
           ta.title AS title,
           ta.content AS content,
           ta.published_at AS published_at,
           GROUP_CONCAT(tt.title, ', ') AS tag_title,
           ta.modified_at AS modified_at
      FROM translated_articles AS ta
           LEFT JOIN
           articles AS a ON ta.article_id = a.id AND 
                            a.deleted_at IS NULL
           LEFT JOIN
           languages AS l ON ta.language_code = l.code
           LEFT JOIN
           articles_tags AS at ON a.id = at.article_id
           LEFT JOIN
           tags AS t ON t.id = at.tag_id AND 
                        t.deleted_at IS NULL
           LEFT JOIN
           translated_tags AS tt ON tt.tag_id = t.id AND 
                                    tt.language_code = ta.language_code
     WHERE ta.is_published = 1
     GROUP BY ta.id
     ORDER BY ta.published_at DESC;

