#include "migrations.hpp"

#include <array>

#include <mw/database.hpp>
#include <mw/error.hpp>
#include <mw/utils.hpp>

namespace overseer::db
{

namespace
{

// One element per SQL statement. CREATE TRIGGER bodies contain
// semicolons (between statements in their BEGIN…END block), so we keep
// each trigger as a single string here rather than splitting on ';'.
constexpr std::array<const char*, 17> SCHEMA_V1_STATEMENTS = {
    // ---- attachment ---------------------------------------------------
    R"SQL(
CREATE TABLE attachment
(
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    mime    TEXT    NOT NULL,
    sha256  TEXT    NOT NULL UNIQUE,
    bytes   BLOB    NOT NULL
);
)SQL",

    // ---- storage ------------------------------------------------------
    R"SQL(
CREATE TABLE storage
(
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id     INTEGER NULL REFERENCES storage(id) ON DELETE RESTRICT,
    name          TEXT    NOT NULL,
    description   TEXT    NULL,
    attachment_id INTEGER NULL REFERENCES attachment(id) ON DELETE SET NULL,
    UNIQUE(parent_id, name)
);
)SQL",
    R"SQL(
CREATE UNIQUE INDEX storage_root_name_unique
    ON storage(name) WHERE parent_id IS NULL;
)SQL",
    "CREATE INDEX storage_parent_idx ON storage(parent_id);",

    // ---- stuff --------------------------------------------------------
    R"SQL(
CREATE TABLE stuff
(
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    name          TEXT    NOT NULL,
    description   TEXT    NULL,
    attachment_id INTEGER NULL REFERENCES attachment(id) ON DELETE SET NULL,
    storage_id    INTEGER NOT NULL REFERENCES storage(id) ON DELETE RESTRICT
);
)SQL",
    "CREATE INDEX stuff_storage_idx ON stuff(storage_id);",

    // ---- stuff_move + trim trigger ------------------------------------
    R"SQL(
CREATE TABLE stuff_move
(
    id         INTEGER PRIMARY KEY AUTOINCREMENT,
    stuff_id   INTEGER NOT NULL REFERENCES stuff(id) ON DELETE CASCADE,
    storage_id INTEGER NOT NULL REFERENCES storage(id) ON DELETE RESTRICT,
    moved_at   INTEGER NOT NULL
);
)SQL",
    R"SQL(
CREATE INDEX stuff_move_stuff_idx ON stuff_move(stuff_id, moved_at DESC);
)SQL",
    R"SQL(
CREATE TRIGGER stuff_move_trim
AFTER INSERT ON stuff_move
BEGIN
    DELETE FROM stuff_move
    WHERE stuff_id = NEW.stuff_id
      AND id IN (
        SELECT id FROM stuff_move
         WHERE stuff_id = NEW.stuff_id
         ORDER BY moved_at DESC, id DESC
         LIMIT -1 OFFSET 10
      );
END;
)SQL",

    // ---- FTS5 virtual tables ------------------------------------------
    R"SQL(
CREATE VIRTUAL TABLE storage_fts USING fts5(
    name, description,
    content='storage', content_rowid='id',
    tokenize='unicode61 remove_diacritics 2'
);
)SQL",
    R"SQL(
CREATE VIRTUAL TABLE stuff_fts USING fts5(
    name, description,
    content='stuff', content_rowid='id',
    tokenize='unicode61 remove_diacritics 2'
);
)SQL",

    // ---- FTS sync triggers --------------------------------------------
    R"SQL(
CREATE TRIGGER storage_ai AFTER INSERT ON storage BEGIN
    INSERT INTO storage_fts(rowid, name, description)
    VALUES (NEW.id, NEW.name, NEW.description);
END;
)SQL",
    R"SQL(
CREATE TRIGGER storage_ad AFTER DELETE ON storage BEGIN
    INSERT INTO storage_fts(storage_fts, rowid, name, description)
    VALUES ('delete', OLD.id, OLD.name, OLD.description);
END;
)SQL",
    R"SQL(
CREATE TRIGGER storage_au AFTER UPDATE ON storage BEGIN
    INSERT INTO storage_fts(storage_fts, rowid, name, description)
    VALUES ('delete', OLD.id, OLD.name, OLD.description);
    INSERT INTO storage_fts(rowid, name, description)
    VALUES (NEW.id, NEW.name, NEW.description);
END;
)SQL",
    R"SQL(
CREATE TRIGGER stuff_ai AFTER INSERT ON stuff BEGIN
    INSERT INTO stuff_fts(rowid, name, description)
    VALUES (NEW.id, NEW.name, NEW.description);
END;
)SQL",
    R"SQL(
CREATE TRIGGER stuff_ad AFTER DELETE ON stuff BEGIN
    INSERT INTO stuff_fts(stuff_fts, rowid, name, description)
    VALUES ('delete', OLD.id, OLD.name, OLD.description);
END;
)SQL",
    R"SQL(
CREATE TRIGGER stuff_au AFTER UPDATE ON stuff BEGIN
    INSERT INTO stuff_fts(stuff_fts, rowid, name, description)
    VALUES ('delete', OLD.id, OLD.name, OLD.description);
    INSERT INTO stuff_fts(rowid, name, description)
    VALUES (NEW.id, NEW.name, NEW.description);
END;
)SQL",
};

} // namespace

mw::E<void> migrateDB0To1(mw::SQLite& db)
{
    for(const char* stmt : SCHEMA_V1_STATEMENTS)
    {
        DO_OR_RETURN(db.execute(stmt));
    }
    return {};
}

} // namespace overseer::db