Reservair/includes/RsvInstaller.php

<?php

use Reservair\Database\Db;
use Reservair\Logger\Logger;

class RsvInstaller {
    public static function install() : void {
        global $wpdb;

        $charset_collate = $wpdb->get_charset_collate();

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_form_definition (
            form_id        bigint unsigned NOT NULL AUTO_INCREMENT,
            name           TINYTEXT        NOT NULL,
            definition     JSON            NOT NULL,
            PRIMARY KEY (form_id)
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_form_submit (
            form_submit_id   bigint unsigned NOT NULL AUTO_INCREMENT,
            form_id          bigint unsigned NOT NULL,
            submitted_on_utc TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
            `values`         JSON            NOT NULL,
            PRIMARY KEY (form_submit_id),
            CONSTRAINT fk_form_submit_definition
                FOREIGN KEY (form_id) REFERENCES {$wpdb->prefix}rsv_form_definition (form_id)
                ON DELETE CASCADE
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_timetable (
            id               bigint unsigned NOT NULL AUTO_INCREMENT,
            name             TINYTEXT        NOT NULL,
            block_size       int unsigned    NOT NULL DEFAULT 0,
            maintainer_email TINYTEXT        NULL DEFAULT NULL,
            google_calendar_id TINYTEXT      NULL DEFAULT NULL,
            PRIMARY KEY (id)
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_reservation (
            id           bigint unsigned NOT NULL AUTO_INCREMENT,
            form_submit_id bigint unsigned NOT NULL,
            is_confirmed tinyint(1)      NULL DEFAULT NULL,
            created_at   TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            CONSTRAINT fk_reservation_form_submit
                FOREIGN KEY (form_submit_id) REFERENCES {$wpdb->prefix}rsv_form_submit (form_submit_id)
                ON DELETE CASCADE
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_timetable_capacity (
            id                    bigint unsigned   NOT NULL AUTO_INCREMENT,
            timetable_id          bigint unsigned   NOT NULL,
            capacity              int unsigned      NOT NULL DEFAULT 1,
            min_lead_time_minutes int unsigned      NOT NULL DEFAULT 0,
            date                  DATE              NOT NULL,
            start_time            smallint unsigned NOT NULL,
            end_time              smallint unsigned NOT NULL,
            repeat_period_in_days int unsigned      NOT NULL DEFAULT 0,
            repeat_times          int unsigned      NOT NULL DEFAULT 0,
            requires_confirmation tinyint(1)        NOT NULL DEFAULT 0,
            PRIMARY KEY (id),
            KEY idx_cap_timetable_date (timetable_id, date),
            CONSTRAINT fk_capacity_timetable
                FOREIGN KEY (timetable_id) REFERENCES {$wpdb->prefix}rsv_timetable (id)
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_timetable_reservation (
            id             bigint unsigned NOT NULL AUTO_INCREMENT,
            timetable_id   bigint unsigned NOT NULL,
            reservation_id bigint unsigned NOT NULL,
            start_utc          DATETIME        NOT NULL,
            end_utc            DATETIME        NOT NULL,
            is_confirmed tinyint(1)      NULL DEFAULT NULL,
            PRIMARY KEY (id),
            KEY idx_ttr_timetable_time (timetable_id, start_utc, end_utc),
            CONSTRAINT fk_timetable_reservation_timetable
                FOREIGN KEY (timetable_id) REFERENCES {$wpdb->prefix}rsv_timetable (id),
            CONSTRAINT fk_timetable_reservation_reservation
                FOREIGN KEY (reservation_id) REFERENCES {$wpdb->prefix}rsv_reservation (id)
                ON DELETE CASCADE
        ) $charset_collate;");

        self::run("CREATE TABLE IF NOT EXISTS {$wpdb->prefix}rsv_timetable_reservation_confirmation (
            reservation_id           bigint unsigned NOT NULL,
            timetable_reservation_id bigint unsigned NOT NULL,
            code                     VARCHAR(32)     NOT NULL,
            PRIMARY KEY (timetable_reservation_id),
            CONSTRAINT fk_trc_timetable_reservation
                FOREIGN KEY (timetable_reservation_id) REFERENCES {$wpdb->prefix}rsv_timetable_reservation (id)
                ON DELETE CASCADE,
            CONSTRAINT fk_trc_reservation
                FOREIGN KEY (reservation_id) REFERENCES {$wpdb->prefix}rsv_reservation (id)
                ON DELETE CASCADE
        ) $charset_collate;");

        // Grant the custom capability that gates the admin REST endpoints.
        RsvCapabilities::ensure();
    }

    private static function run(string $sql) : void {
        if (Db::query($sql) === false) {
            Logger::error('RsvInstaller error');
        }
    }

    public static function uninstall() : void {
        RsvCapabilities::revoke();
    }
}