CREATE TABLE `objects`
(
    `do_not_access_rowid_alias` INTEGER PRIMARY KEY,
    `object_type`               INT NOT NULL,
    `id`                        INT GENERATED ALWAYS AS (`do_not_access_rowid_alias` | (`object_type` << 56)) VIRTUAL,
    `name`                      TEXT NOT NULL,
    `information`               TEXT NOT NULL
) STRICT;

CREATE UNIQUE INDEX `object_id` ON `objects` (`id`);
CREATE INDEX `object_type` ON `objects` (`object_type`);

CREATE VIRTUAL TABLE `objects_fts` USING fts5
(
    `name`, `information`,
    content='objects',
    content_rowid='id',
    tokenize="unicode61 remove_diacritics 2 tokenchars '-_'"
);

CREATE TABLE `object_scopes`
(
    `object` INT,
    `scope`  ANY,

    PRIMARY KEY (`object`),
    FOREIGN KEY (`object`) REFERENCES `objects` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE INDEX `object_scopes_scope` ON `object_scopes` (`scope`);

CREATE TABLE `folders`
(
    `id` INT,

    PRIMARY KEY (`id`),
    FOREIGN KEY (`id`) REFERENCES `objects` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE TABLE `object_tree`
(
    `object`   INT,
    `parent`   INT,
    `position` INT NOT NULL,

    PRIMARY KEY (`object`),
    UNIQUE (`parent`, `position`),
    FOREIGN KEY (`object`) REFERENCES `objects` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`parent`) REFERENCES `folders` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE INDEX `object_tree_parent` ON `object_tree` (`parent`);
CREATE INDEX `object_tree_position` ON `object_tree` (`position`);

CREATE TABLE `blueprints`
(
    `id` INT,

    PRIMARY KEY (`id`),
    FOREIGN KEY (`id`) REFERENCES `objects` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE TABLE `entities`
(
    `id` INT,

    PRIMARY KEY (`id`),
    FOREIGN KEY (`id`) REFERENCES `objects` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE TABLE `entity_blueprints`
(
    `entity`    INT NOT NULL,
    `blueprint` INT NOT NULL,

    PRIMARY KEY (`entity`),
    UNIQUE (`entity`, `blueprint`),
    FOREIGN KEY (`blueprint`) REFERENCES `blueprints` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE INDEX `entity_blueprints_blueprint` ON `entity_blueprints` (`blueprint`);

CREATE TABLE `properties`
(
    `id`            INT,
    `type` INT  NOT NULL,
    `default_value` TEXT NOT NULL,
    `settings`      TEXT NOT NULL,

    PRIMARY KEY (`id`),
    FOREIGN KEY (`id`) REFERENCES `objects` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE INDEX `properties_type` ON `properties` (`type`);

CREATE VIRTUAL TABLE `property_default_value_fts` USING fts5
(
    `default_value`,
    content='properties',
    content_rowid='object_id',
    tokenize="unicode61 remove_diacritics 2 tokenchars '-_'"
);

CREATE VIRTUAL TABLE `property_settings_fts` USING fts5
(
    `settings`,
    content='properties',
    content_rowid='object_id',
    tokenize="unicode61 remove_diacritics 2 tokenchars '-_'"
);

CREATE TABLE `values`
(
    `entity`   INT  NOT NULL,
    `property` INT  NOT NULL,
    `value`    TEXT NOT NULL,

    PRIMARY KEY (`entity`, `property`),
    FOREIGN KEY (`entity`) REFERENCES `entities` (`id`) ON DELETE CASCADE,
    FOREIGN KEY (`property`) REFERENCES `properties` (`id`) ON DELETE CASCADE
) WITHOUT ROWID, STRICT;

CREATE VIRTUAL TABLE `values_fts` USING fts5
(
    `value`,
    content='values',
    tokenize="unicode61 remove_diacritics 2 tokenchars '-_'"
)
