ic-web/sql/ic_web.sql

-- ─────────────────────────────────────────────────────────────────────────────
--  ic-web  |  IC-Webhosting – Datenbankschema
--  Einmalig gegen die Spieldatenbank ausfuehren. Wiederholbar.
--
--  Die DOMAENE selbst steht nicht hier, sondern in ic_mail_realms: ic-mail ist
--  die einzige Domaenenverwaltung, eine Webseite ist nur ein zweiter Dienst
--  darauf. Referenziert wird ueber den Domaenennamen, nicht ueber einen
--  Fremdschluessel – Fremdschluessel ueber Resource-Grenzen hinweg koppeln zwei
--  Lebenszyklen aneinander, die unabhaengig voneinander installiert werden.
-- ─────────────────────────────────────────────────────────────────────────────

-- Eine Webseite je Domaene.
CREATE TABLE IF NOT EXISTS `ic_web_sites` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `domain`       VARCHAR(80)  NOT NULL,
    `title`        VARCHAR(120) NOT NULL DEFAULT '',
    `theme`        VARCHAR(40)  NOT NULL DEFAULT 'clean',
    `owner_type`   ENUM('job','faction') NOT NULL DEFAULT 'job',
    `owner_id`     VARCHAR(80)  NOT NULL,
    `published`    TINYINT(1)   NOT NULL DEFAULT 0,
    `blocked`      TINYINT(1)   NOT NULL DEFAULT 0,
    `created_by`   VARCHAR(120) NULL DEFAULT NULL,
    `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                                ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_domain` (`domain`),
    KEY `idx_owner` (`owner_type`, `owner_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Seiten einer Webseite. slug 'home' ist die Startseite.
CREATE TABLE IF NOT EXISTS `ic_web_pages` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `site_id`    INT UNSIGNED NOT NULL,
    `slug`       VARCHAR(60)  NOT NULL,
    `title`      VARCHAR(120) NOT NULL DEFAULT '',
    `blocks`     LONGTEXT     NOT NULL,        -- JSON-Liste, serverseitig geprueft
    `sort_order` INT          NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                              ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_site_slug` (`site_id`, `slug`),
    CONSTRAINT `fk_page_site` FOREIGN KEY (`site_id`)
        REFERENCES `ic_web_sites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Zugaenge zum Verwaltungsbereich.
--
--   superadmin  – der Anbieter selbst. Legt Domaenen an und setzt je Domaene
--                 einen Admin ein. Nicht an eine Domaene gebunden.
--   admin       – Domaenenadmin. Pflegt die Seiten seiner Domaene und legt dort
--                 weitere Zugaenge an.
--   editor      – darf nur die Seiten seiner Domaene pflegen.
--
-- Das Passwort steht nirgends im Klartext: gespeichert wird SHA2(salt||passwort).
-- Gerechnet wird das in der Datenbank, damit das Passwort den Server nur als
-- Abfrageparameter durchlaeuft und nie in einer Lua-Variable liegen bleibt.
--
-- Das ist ein IC-Passwort, keine Sicherheitsgrenze gegen Serveradmins – wer
-- Datenbankzugriff hat, kann jeden Zugang zuruecksetzen. Es soll verhindern,
-- dass Mitspieler fremde Seiten aendern, nicht Angriffe von aussen abwehren.
CREATE TABLE IF NOT EXISTS `ic_web_accounts` (
    `id`            INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username`      VARCHAR(200) NOT NULL,      -- z. B. admin@liveinvader.ls
    `domain`        VARCHAR(80)  NULL DEFAULT NULL,  -- NULL nur beim superadmin
    `role`          ENUM('superadmin','admin','editor') NOT NULL DEFAULT 'editor',
    `display_name`  VARCHAR(120) NOT NULL DEFAULT '',
    `salt`          CHAR(32)     NOT NULL,
    `password_hash` CHAR(64)     NOT NULL,
    `active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `must_change`   TINYINT(1)   NOT NULL DEFAULT 0,
    `created_by`    VARCHAR(200) NULL DEFAULT NULL,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_login`    TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_username` (`username`),
    KEY `idx_domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Fehlgeschlagene Anmeldungen. Ohne diese Bremse ist ein vierstelliges
-- IC-Passwort in Sekunden durchprobiert.
CREATE TABLE IF NOT EXISTS `ic_web_login_fails` (
    `id`         INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `username`   VARCHAR(200) NOT NULL,
    `identifier` VARCHAR(120) NULL DEFAULT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user_time` (`username`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Meldungen. Bilder werden extern verlinkt, deshalb braucht es einen Weg,
-- unpassende Inhalte zu melden, ohne dass ein Admin staendig mitliest.
CREATE TABLE IF NOT EXISTS `ic_web_reports` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `site_id`     INT UNSIGNED NOT NULL,
    `page_slug`   VARCHAR(60)  NULL DEFAULT NULL,
    `reason`      VARCHAR(500) NOT NULL DEFAULT '',
    `reported_by` VARCHAR(120) NOT NULL,
    `status`      ENUM('open','reviewed','dismissed') NOT NULL DEFAULT 'open',
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_report_site` FOREIGN KEY (`site_id`)
        REFERENCES `ic_web_sites` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;