pc-live/sql/install.sql

-- ─────────────────────────────────────────────────────────────────────────────
--  pc-live  |  Database Schema
--  Run this once against your game database.
-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_users` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `identifier`    VARCHAR(120)    NOT NULL,
    `display_name`  VARCHAR(100)    NOT NULL DEFAULT '',
    `settings_json` TEXT            NOT NULL DEFAULT '{}',
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_installs` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `user_id`       INT UNSIGNED    NOT NULL,
    `app_id`        VARCHAR(80)     NOT NULL,
    `version`       VARCHAR(20)     NOT NULL DEFAULT '1.0.0',
    `installed_at`  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_user_app` (`user_id`, `app_id`),
    CONSTRAINT `fk_installs_user` FOREIGN KEY (`user_id`)
        REFERENCES `pc_live_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_mail` (
    `id`               INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `to_identifier`    VARCHAR(120)    NOT NULL,
    `from_identifier`  VARCHAR(120)    NOT NULL,
    `subject`          VARCHAR(80)     NOT NULL,
    `body`             TEXT            NOT NULL,
    `is_read`          TINYINT(1)      NOT NULL DEFAULT 0,
    `folder_id`        INT UNSIGNED    NULL DEFAULT NULL,
    `sent_at`          TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_to_identifier` (`to_identifier`),
    KEY `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_mail_folders` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `identifier`    VARCHAR(120)    NOT NULL,
    `name`          VARCHAR(80)     NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_calendar` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `identifier`    VARCHAR(120)    NOT NULL,
    `title`         VARCHAR(120)    NOT NULL,
    `description`   VARCHAR(500)    NOT NULL DEFAULT '',
    `start_at`      DATETIME        NOT NULL,
    `end_at`        DATETIME        NULL DEFAULT NULL,
    `color`         VARCHAR(20)     NOT NULL DEFAULT '#00aaff',
    PRIMARY KEY (`id`),
    KEY `idx_identifier` (`identifier`),
    KEY `idx_start_at` (`start_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS `pc_live_contacts` (
    `id`            INT UNSIGNED    NOT NULL AUTO_INCREMENT,
    `identifier`    VARCHAR(120)    NOT NULL,
    `name`          VARCHAR(100)    NOT NULL,
    `email`         VARCHAR(120)    NOT NULL DEFAULT '',
    `phone`         VARCHAR(30)     NOT NULL DEFAULT '',
    `notes`         VARCHAR(500)    NOT NULL DEFAULT '',
    `created_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_identifier` (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────────────
-- Migration: add folder_id to pc_live_mail if it doesn't exist yet
-- ─────────────────────────────────────────────────────────────────────────────
ALTER TABLE `pc_live_mail`
    ADD COLUMN IF NOT EXISTS `folder_id`  INT UNSIGNED NULL DEFAULT NULL;

ALTER TABLE `pc_live_mail`
    ADD COLUMN IF NOT EXISTS `sender_id`  VARCHAR(120) NULL DEFAULT NULL,
    ADD COLUMN IF NOT EXISTS `to_address` VARCHAR(120) NULL DEFAULT NULL;