# Installation

From a fresh resource to your first NovaOS phone.

Follow these steps in order. The code blocks below contain the complete database installer, every inventory item for ox_inventory and qb-inventory, and the full resource startup block. Use **Copy all** on a long block to copy every line, including the lines below the scroll area.

## 1. Add the resource

Install the [required dependencies](#/docs/wd_phone/requirements) first: `oxmysql`, `ox_lib`, one framework (`qbx_core`, `qb-core` or `es_extended`) and one inventory (`ox_inventory`, or `qb-inventory` on QBCore). OneSync must be on: `set onesync on` in `server.cfg`, or txAdmin's OneSync setting. Your database must be MariaDB 10.3 or newer.

For the full setup below, also install `pma-voice` for call audio and `ox_target` for shops, payphones and placed items.

Extract your download and place the complete resource at `resources/[wolfden]/wd_phone`. Create the `[wolfden]` folder if it does not exist. Keep the resource folder named `wd_phone`, with `fxmanifest.lua` directly inside it.

## 2. Import the complete database

1. Open HeidiSQL, phpMyAdmin or your server's database manager.
2. Select the database used by your server's oxmysql connection.
3. Open a SQL query tab, copy the **entire block below**, paste it and execute all statements.
4. Wait for it to finish and confirm there are no SQL errors.

This is the complete `migrations/install.sql` shipped with the documented version of Wolfden Phone. It requires MariaDB 10.3 or newer. You can also [download the complete SQL file](reference/wd_phone/migrations/install.sql) and import it through your database manager.

> Back up existing data first. Use `install.sql` for installation and updates. Never use `uninstall.sql` for these steps: it deletes phone data. When installing a different release, use that release's included installer.

```sql migrations/install.sql
-- =====================================================================
-- wd_phone / NovaOS database schema
--
-- One file for every table the resource owns. Import it on install and
-- again after updating the resource. The schema statements are idempotent and
-- the guarded data corrections are safe to repeat, so re-running this file
-- brings an existing database up to date without discarding its data.
-- It never touches qbx_core or ox_inventory tables.
--
-- Requires MariaDB 10.3+ (ADD COLUMN IF NOT EXISTS).
-- To remove everything: migrations/uninstall.sql (destructive).
-- =====================================================================


-- ---------------------------------------------------------------------
-- Device core
-- ---------------------------------------------------------------------

-- Core device registry. One row per physical device (identified by device_id,
-- which also lives in the ox_inventory item metadata).
CREATE TABLE IF NOT EXISTS `phone_devices` (
    `id`               INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`        VARCHAR(32)  NOT NULL,
    `model`            VARCHAR(32)  NOT NULL,
    `color`            VARCHAR(24)  NOT NULL DEFAULT 'midnight',
    `device_name`      VARCHAR(32)  DEFAULT NULL,
    `condition_value`  TINYINT UNSIGNED NOT NULL DEFAULT 100,
    `battery_level`    TINYINT UNSIGNED NOT NULL DEFAULT 100,
    `battery_health`   TINYINT UNSIGNED NOT NULL DEFAULT 100,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_used_at`     TIMESTAMP    NULL DEFAULT NULL,
    -- First-boot setup finished. Until then every open starts in setup; an
    -- erase clears it again.
    `setup_complete`   TINYINT(1)   NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_phone_devices_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-device settings. 1:1 with phone_devices.
CREATE TABLE IF NOT EXISTS `phone_device_settings` (
    `id`                    INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`             VARCHAR(32)  NOT NULL,
    `theme`                 VARCHAR(16)  NOT NULL DEFAULT 'system',
    `wallpaper`             VARCHAR(48)  NOT NULL DEFAULT 'nova_default',
    -- 'auto' (match the phone's finish) or a Config.AccentColors key.
    `accent_color`          VARCHAR(16)  NOT NULL DEFAULT 'auto',
    `ringtone`              VARCHAR(48)  NOT NULL DEFAULT 'nova_ascent',
    `notification_sound`    VARCHAR(48)  NOT NULL DEFAULT 'nova_chime',
    `volume`                TINYINT UNSIGNED NOT NULL DEFAULT 70,
    `brightness`            TINYINT UNSIGNED NOT NULL DEFAULT 80,
    `text_size`             VARCHAR(8)   NOT NULL DEFAULT 'medium',
    `icon_size`             VARCHAR(8)   NOT NULL DEFAULT 'medium',
    `ui_scale`              DECIMAL(3,2) NOT NULL DEFAULT 1.00,
    `language`              VARCHAR(8)   NOT NULL DEFAULT 'en',
    `do_not_disturb`        TINYINT(1)   NOT NULL DEFAULT 0,
    `airplane_mode`         TINYINT(1)   NOT NULL DEFAULT 0,
    `wifi_enabled` TINYINT(1) NOT NULL DEFAULT 0,
    `mobile_data_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `bluetooth_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    -- Control Center: on the lock screen; its controls (JSON id list, NULL = default).
    `cc_lock_screen` TINYINT(1) NOT NULL DEFAULT 1,
    `cc_controls` VARCHAR(400) DEFAULT NULL,
    -- Voice changer for calls (Wolfden Phone Secure; JSON { enabled, style, strength, tone }).
    `voice_changer` VARCHAR(160) DEFAULT NULL,
    -- Settings -> Battery.
    `low_power_mode` TINYINT(1) NOT NULL DEFAULT 0,
    `optimized_charging` TINYINT(1) NOT NULL DEFAULT 0,
    `notification_previews` VARCHAR(16)  NOT NULL DEFAULT 'show_all',
    `reduce_motion`         TINYINT(1)   NOT NULL DEFAULT 0,
    `high_contrast` TINYINT(1) NOT NULL DEFAULT 0,
    `bold_text` TINYINT(1) NOT NULL DEFAULT 0,
    `reduce_transparency` TINYINT(1) NOT NULL DEFAULT 0,
    `find_my_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `lock_clock_font` VARCHAR(12) NOT NULL DEFAULT 'rounded',
    `lock_clock_color` VARCHAR(12) NOT NULL DEFAULT 'white',
    `lock_show_weather` TINYINT(1) NOT NULL DEFAULT 1,
    `lock_show_calendar` TINYINT(1) NOT NULL DEFAULT 1,
    `lock_show_alarm` TINYINT(1) NOT NULL DEFAULT 1,
    `silent_mode` TINYINT(1) NOT NULL DEFAULT 0,
    `vibration`             TINYINT(1)   NOT NULL DEFAULT 1,
    `show_app_labels`       TINYINT(1)   NOT NULL DEFAULT 1,
    `auto_lock_seconds`     SMALLINT UNSIGNED NOT NULL DEFAULT 30,
    `pin_enabled`           TINYINT(1)   NOT NULL DEFAULT 0,
    `pin_hash`              VARCHAR(255) DEFAULT NULL,
    -- Wolfden Phone Secure: a second PIN that burns the phone (Config.Secure.duressPin).
    `duress_pin_hash`       VARCHAR(255) DEFAULT NULL,
    -- Use Face Unlock (faces in phone_device_faces).
    `face_id_unlock`        TINYINT(1)   NOT NULL DEFAULT 1,
    -- "Require Attention for Face Unlock" (Config.FaceId.hold).
    `face_id_attention`     TINYINT(1)   NOT NULL DEFAULT 1,
    `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_phone_settings_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Home-screen layout (pages, dock, folders, widgets) as JSON. 1:1 with device.
CREATE TABLE IF NOT EXISTS `phone_home_layouts` (
    `id`          INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`   VARCHAR(32)  NOT NULL,
    `layout_json` LONGTEXT     NOT NULL,
    `updated_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_phone_layout_device_id` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Persistent notifications. Capped + pruned by retention config.
CREATE TABLE IF NOT EXISTS `phone_notifications` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`  VARCHAR(32)  NOT NULL,
    `app_id`     VARCHAR(32)  NOT NULL DEFAULT 'system',
    `channel_id` VARCHAR(32) NOT NULL DEFAULT 'default',
    `title`      VARCHAR(64)  NOT NULL,
    `content`    VARCHAR(256) NOT NULL DEFAULT '',
    `icon`       VARCHAR(32)  NOT NULL DEFAULT 'bell',
    `data_json`  TEXT         DEFAULT NULL,
    `is_read`    TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_phone_notif_device` (`device_id`, `created_at`),
    KEY `idx_phone_notif_expires` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Installed apps per device and their installed version. The home layout JSON
-- decides arrangement; this table decides which apps a device has.
-- position / folder_id are unused (kept for existing databases).
CREATE TABLE IF NOT EXISTS `phone_installed_apps` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`    VARCHAR(32)  NOT NULL,
    `app_id`       VARCHAR(32)  NOT NULL,
    `version` VARCHAR(16) NOT NULL DEFAULT '1.0.0',
    `source` VARCHAR(12) NOT NULL DEFAULT 'builtin',
    `position`     SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `folder_id`    VARCHAR(32)  DEFAULT NULL,
    `installed_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_phone_installed` (`device_id`, `app_id`),
    KEY `idx_phone_installed_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Phone lines, contacts, messaging and calls
-- ---------------------------------------------------------------------

-- Phone lines: the stable communication identity. A line outlives any single
-- player source and can later be moved between devices (number transfer).
CREATE TABLE IF NOT EXISTS `phone_lines` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id`          VARCHAR(40)  NOT NULL,
    `phone_number`     VARCHAR(20)  NOT NULL,
    `line_type`        VARCHAR(16)  NOT NULL DEFAULT 'personal',
    `label`            VARCHAR(40)  DEFAULT NULL,
    `registered_owner` VARCHAR(64)  DEFAULT NULL,
    `is_active`        TINYINT(1)   NOT NULL DEFAULT 1,
    `is_suspended`     TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_used_at`     TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_phone_lines_line_id` (`line_id`),
    UNIQUE KEY `uq_phone_lines_number` (`phone_number`),
    KEY `idx_phone_lines_owner` (`registered_owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Device <-> line attachment (the device's primary line; SIM slots below hold
-- additional lines).
CREATE TABLE IF NOT EXISTS `phone_device_lines` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`   VARCHAR(32)  NOT NULL,
    `line_id`     VARCHAR(40)  NOT NULL,
    `line_slot`   TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `is_primary`  TINYINT(1)   NOT NULL DEFAULT 1,
    `attached_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_device_line` (`device_id`, `line_id`),
    KEY `idx_device_lines_device` (`device_id`),
    KEY `idx_device_lines_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contacts belong to a LINE (not a player). Separate contact book per line.
CREATE TABLE IF NOT EXISTS `phone_contacts` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_id`      VARCHAR(40)  NOT NULL,
    -- Owner key: account:<NovaAccountId>, else device:<deviceId>.
    `owner_key`       VARCHAR(40)  NOT NULL,
    `display_name`    VARCHAR(40)  NOT NULL,
    `phone_number`    VARCHAR(20)  NOT NULL,
    `avatar`          VARCHAR(8)   DEFAULT NULL,
    `avatar_color`    VARCHAR(9)   DEFAULT '#0a84ff',
    `company`         VARCHAR(60)  DEFAULT NULL,
    `notes`           VARCHAR(300) DEFAULT NULL,
    `custom_ringtone` VARCHAR(48)  DEFAULT NULL,
    `is_favorite`     TINYINT(1)   NOT NULL DEFAULT 0,
    `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_contact_id` (`contact_id`),
    UNIQUE KEY `uq_owner_number` (`owner_key`, `phone_number`),
    KEY `idx_contacts_owner` (`owner_key`),
    KEY `idx_contacts_number` (`phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every number of a contact, primary first (position 0). phone_contacts.phone_number
-- mirrors the primary. A number belongs to one contact per owner.
CREATE TABLE IF NOT EXISTS `phone_contact_numbers` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_id`    VARCHAR(40) NOT NULL,
    `owner_key`     VARCHAR(40) NOT NULL,
    `label`         VARCHAR(16) NOT NULL DEFAULT 'mobile',
    `phone_number`  VARCHAR(20) NOT NULL,
    `position`      TINYINT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_contact_number_owner` (`owner_key`, `phone_number`),
    KEY `idx_contact_numbers_contact` (`contact_id`, `position`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conversations (direct + group).
CREATE TABLE IF NOT EXISTS `phone_conversations` (
    `id`                 BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`    VARCHAR(40)  NOT NULL,
    `conversation_type`  VARCHAR(16)  NOT NULL DEFAULT 'direct',
    `title`              VARCHAR(40)  DEFAULT NULL,
    `created_by_line_id` VARCHAR(40)  DEFAULT NULL,
    -- For direct chats: a deterministic key of the two line ids to prevent dupes.
    `direct_key`         VARCHAR(83)  DEFAULT NULL,
    `created_at`         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_message_at`    TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_conversation_id` (`conversation_id`),
    UNIQUE KEY `uq_direct_key` (`direct_key`),
    KEY `idx_conv_last_message` (`last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Conversation membership: the NUMBERS (lines) a conversation is addressed to,
-- with group roles. What each owner sees is in phone_conversation_views.
CREATE TABLE IF NOT EXISTS `phone_conversation_members` (
    `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`        VARCHAR(40)  NOT NULL,
    `line_id`                VARCHAR(40)  NOT NULL,
    `nickname`               VARCHAR(40)  DEFAULT NULL,
    `role`                   VARCHAR(16)  NOT NULL DEFAULT 'member',
    `joined_at`              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `left_at`                TIMESTAMP    NULL DEFAULT NULL,
    `updated_at`             TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_conv_member` (`conversation_id`, `line_id`),
    KEY `idx_member_line` (`line_id`),
    KEY `idx_member_conv` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- A conversation as one owner (Nova Account, else phone) sees it: created
-- when the owner sends or receives in it. line_id = the owner's number in it.
CREATE TABLE IF NOT EXISTS `phone_conversation_views` (
    `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_key`            VARCHAR(40)  NOT NULL,
    `conversation_id`      VARCHAR(40)  NOT NULL,
    `line_id`              VARCHAR(40)  NOT NULL,
    `is_muted`             TINYINT(1)   NOT NULL DEFAULT 0,
    `is_deleted`           TINYINT(1)   NOT NULL DEFAULT 0,
    `last_read_message_id` VARCHAR(40)  DEFAULT NULL,
    `updated_at`           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_conv_view` (`owner_key`, `conversation_id`),
    KEY `idx_conv_view_conv` (`conversation_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Messages.
CREATE TABLE IF NOT EXISTS `phone_messages` (
    `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id`           VARCHAR(40)  NOT NULL,
    `conversation_id`      VARCHAR(40)  NOT NULL,
    `sender_line_id`       VARCHAR(40)  DEFAULT NULL,
    `sender_owner_key`     VARCHAR(40)  DEFAULT NULL, -- whose history the sent copy is in
    `message_type`         VARCHAR(16)  NOT NULL DEFAULT 'text',
    `content`              TEXT         DEFAULT NULL,
    `payload_json`         TEXT         DEFAULT NULL,
    `reply_to_message_id`  VARCHAR(40)  DEFAULT NULL,
    `client_request_id`    VARCHAR(64)  DEFAULT NULL,
    `created_at`           TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `edited_at`            TIMESTAMP(3) NULL DEFAULT NULL,
    `deleted_at`           TIMESTAMP(3) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_message_id` (`message_id`),
    -- Idempotency: a sender cannot create two messages with the same request id.
    UNIQUE KEY `uq_sender_request` (`sender_line_id`, `client_request_id`),
    KEY `idx_messages_conv_created` (`conversation_id`, `created_at`),
    KEY `idx_messages_sender` (`sender_line_id`),
    KEY `idx_messages_sender_owner_created` (`sender_owner_key`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-recipient delivery + read receipts.
CREATE TABLE IF NOT EXISTS `phone_message_receipts` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id`   VARCHAR(40)  NOT NULL,
    `line_id`      VARCHAR(40)  NOT NULL,
    -- The owner of the phone holding the number when it was delivered.
    `owner_key`    VARCHAR(40)  DEFAULT NULL,
    `delivered_at` TIMESTAMP(3) NULL DEFAULT NULL,
    `read_at`      TIMESTAMP(3) NULL DEFAULT NULL,
    `expired_at`   TIMESTAMP(3) NULL DEFAULT NULL, -- not delivered in time: never shown
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_message_receipt` (`message_id`, `line_id`),
    -- The delivery queue (server/messages.lua) asks every second for the
    -- undelivered receipts of online numbers, and for every undelivered /
    -- unexpired receipt: both stay index lookups however many messages exist.
    KEY `idx_receipt_line_pending` (`line_id`, `delivered_at`),
    KEY `idx_receipt_pending` (`delivered_at`, `expired_at`),
    KEY `idx_receipt_owner` (`owner_key`, `read_at`),
    KEY `idx_receipt_owner_delivered` (`owner_key`, `delivered_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Blocked numbers (per owner).
CREATE TABLE IF NOT EXISTS `phone_blocked_numbers` (
    `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_key`            VARCHAR(40)  NOT NULL,
    `blocked_line_id`      VARCHAR(40)  DEFAULT NULL,
    `blocked_phone_number` VARCHAR(20)  NOT NULL,
    `created_at`           TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_owner_blocked` (`owner_key`, `blocked_phone_number`),
    KEY `idx_blocked_owner` (`owner_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Call records. True caller identity is preserved here for server integrity even
-- when the call is displayed as anonymous (is_anonymous = 1).
CREATE TABLE IF NOT EXISTS `phone_calls` (
    `id`                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `call_id`                 VARCHAR(40)  NOT NULL,
    -- Group calls: one row per leg; every leg of one call shares its id, and
    -- a call of a group conversation names it. NULL = a plain 1:1 call.
    `group_call_id`           VARCHAR(40)  DEFAULT NULL,
    `conversation_id`         VARCHAR(40)  DEFAULT NULL,
    `caller_line_id`          VARCHAR(40)  DEFAULT NULL,
    `receiver_line_id`        VARCHAR(40)  DEFAULT NULL,
    `caller_owner_key`        VARCHAR(40)  DEFAULT NULL,
    `receiver_owner_key`      VARCHAR(40)  DEFAULT NULL,
    `displayed_caller_number` VARCHAR(20)  DEFAULT NULL,
    `is_anonymous`            TINYINT(1)   NOT NULL DEFAULT 0,
    `is_video`                TINYINT(1)   NOT NULL DEFAULT 0,
    `state`                   VARCHAR(16)  NOT NULL DEFAULT 'ended',
    `end_reason`              VARCHAR(24)  DEFAULT NULL,
    `started_at`              TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `answered_at`             TIMESTAMP    NULL DEFAULT NULL,
    `ended_at`                TIMESTAMP    NULL DEFAULT NULL,
    `duration_seconds`        INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_call_id` (`call_id`),
    KEY `idx_calls_caller` (`caller_line_id`, `started_at`),
    KEY `idx_calls_receiver` (`receiver_line_id`, `started_at`),
    KEY `idx_calls_caller_owner` (`caller_owner_key`, `started_at`),
    KEY `idx_calls_receiver_owner` (`receiver_owner_key`, `started_at`),
    KEY `idx_calls_group` (`group_call_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-line call-history visibility (clearing history is member-specific).
CREATE TABLE IF NOT EXISTS `phone_call_visibility` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `call_id`    VARCHAR(40)  NOT NULL,
    `owner_key`  VARCHAR(40)  NOT NULL,
    `is_deleted` TINYINT(1)   NOT NULL DEFAULT 0,
    `viewed_at`  TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_call_line` (`call_id`, `owner_key`),
    KEY `idx_callvis_line` (`owner_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Line-level communication settings (identity/privacy preferences). Hardware/UI
-- preferences live in phone_device_settings.
CREATE TABLE IF NOT EXISTS `phone_communication_settings` (
    `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_key`            VARCHAR(40)  NOT NULL,
    `caller_id_mode`       VARCHAR(16)  NOT NULL DEFAULT 'visible', -- visible | hidden | next_hidden
    `read_receipts`        TINYINT(1)   NOT NULL DEFAULT 1,
    `typing_indicators`    TINYINT(1)   NOT NULL DEFAULT 1,
    `message_previews`     TINYINT(1)   NOT NULL DEFAULT 1,
    `group_previews`       TINYINT(1)   NOT NULL DEFAULT 1,
    `delivery_status`      TINYINT(1)   NOT NULL DEFAULT 1,
    `incoming_call_vibrate` TINYINT(1)  NOT NULL DEFAULT 1,
    `allow_calls_phone_closed` TINYINT(1) NOT NULL DEFAULT 1,
    `missed_call_notifications` TINYINT(1) NOT NULL DEFAULT 1,
    `my_card_display_name` VARCHAR(40) DEFAULT NULL,
    `my_card_avatar_color` VARCHAR(9) NOT NULL DEFAULT '#5e5ce6',
    `my_card_notes`       VARCHAR(300) DEFAULT NULL,
    `my_card_media_id`    VARCHAR(48) DEFAULT NULL,
    `nova_drop`           VARCHAR(10) NOT NULL DEFAULT 'everyone', -- off | contacts | everyone
    `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_commsettings_line` (`owner_key`),
    KEY `idx_commsettings_card_media` (`my_card_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_message_hidden` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id` VARCHAR(40) NOT NULL,
    `owner_key`  VARCHAR(40) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_message_hidden` (`message_id`, `owner_key`),
    KEY `idx_hidden_owner_created` (`owner_key`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- SIM, eSIM, carriers and connectivity
-- ---------------------------------------------------------------------

-- The SIM identity (physical, eSIM, or migrated legacy line). One line per SIM.
CREATE TABLE IF NOT EXISTS `phone_sims` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sim_id`           VARCHAR(40)  NOT NULL,
    `sim_type`         VARCHAR(16)  NOT NULL DEFAULT 'physical', -- physical | esim | legacy_esim
    `line_id`          VARCHAR(40)  DEFAULT NULL,
    `carrier_id`       VARCHAR(32)  DEFAULT NULL,
    `status`           VARCHAR(16)  NOT NULL DEFAULT 'active',
    `suspend_reason`   VARCHAR(32)  DEFAULT NULL, -- why it is suspended ('billing': paying the bill lifts it)
    `registered_owner` VARCHAR(64)  DEFAULT NULL,
    `purchase_shop`    VARCHAR(32)  DEFAULT NULL, -- carrier shop it was bought at (subscriber records)
    `pin_enabled`      TINYINT(1)   NOT NULL DEFAULT 0,
    `pin_hash`         VARCHAR(255) DEFAULT NULL,
    `pin_attempts`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `puk_hash`         VARCHAR(255) DEFAULT NULL,
    `puk_attempts`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `pin_locked`       TINYINT(1)   NOT NULL DEFAULT 0,
    `puk_locked`       TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `activated_at`     TIMESTAMP    NULL DEFAULT NULL,
    `expires_at`       TIMESTAMP    NULL DEFAULT NULL,
    `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_sims_sim_id` (`sim_id`),
    KEY `idx_sims_line` (`line_id`),
    KEY `idx_sims_carrier` (`carrier_id`),
    KEY `idx_sims_status` (`status`),
    KEY `idx_sims_owner` (`registered_owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- eSIM-specific profile data (activation code, installed device, slot).
CREATE TABLE IF NOT EXISTS `phone_esim_profiles` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `esim_id`               VARCHAR(40)  NOT NULL,
    `sim_id`                VARCHAR(40)  DEFAULT NULL,
    `activation_code_hash`  VARCHAR(255) DEFAULT NULL,
    `activation_expires_at` TIMESTAMP    NULL DEFAULT NULL,
    `installed_device_id`   VARCHAR(32)  DEFAULT NULL,
    `slot_name`             VARCHAR(24)  DEFAULT NULL,
    `status`                VARCHAR(16)  NOT NULL DEFAULT 'pending',
    `registered_owner`      VARCHAR(64)  DEFAULT NULL,
    `created_at`            TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `activated_at`          TIMESTAMP    NULL DEFAULT NULL,
    `expires_at`            TIMESTAMP    NULL DEFAULT NULL,
    `updated_at`            TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_esim_id` (`esim_id`),
    KEY `idx_esim_sim` (`sim_id`),
    KEY `idx_esim_device` (`installed_device_id`),
    KEY `idx_esim_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Which SIMs are in which device slot, and whether the line is enabled.
CREATE TABLE IF NOT EXISTS `phone_device_sim_slots` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`    VARCHAR(32)  NOT NULL,
    `sim_id`       VARCHAR(40)  NOT NULL,
    `slot_type`    VARCHAR(16)  NOT NULL DEFAULT 'physical', -- physical | esim
    `slot_index`   TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `is_enabled`   TINYINT(1)   NOT NULL DEFAULT 1,
    `line_label`   VARCHAR(16)  DEFAULT 'Personal',
    `item_name`    VARCHAR(32)  DEFAULT NULL, -- inventory item a physical SIM came from (returned on removal)
    `installed_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_slot_sim` (`sim_id`),                 -- a SIM is installed in ONE device
    UNIQUE KEY `uq_device_slot` (`device_id`, `slot_type`, `slot_index`),
    KEY `idx_slots_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-device dual-SIM defaults (call / message line + ask-every-time).
CREATE TABLE IF NOT EXISTS `phone_device_line_prefs` (
    `id`                     BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`              VARCHAR(32) NOT NULL,
    `default_call_line_id`   VARCHAR(40) DEFAULT NULL,
    `default_message_line_id` VARCHAR(40) DEFAULT NULL,
    -- The line mobile data uses (NULL: the line in use).
    `default_data_line_id`   VARCHAR(40) DEFAULT NULL,
    `ask_call`               TINYINT(1)  NOT NULL DEFAULT 0,
    `ask_message`            TINYINT(1)  NOT NULL DEFAULT 0,
    `updated_at`             TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_prefs_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Carrier account per SIM (plan, balance, billing).
CREATE TABLE IF NOT EXISTS `phone_carrier_accounts` (
    `id`                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sim_id`              VARCHAR(40)  NOT NULL,
    `carrier_id`          VARCHAR(32)  NOT NULL,
    `plan_id`             VARCHAR(32)  DEFAULT NULL,
    `account_type`        VARCHAR(16)  NOT NULL DEFAULT 'prepaid',
    `balance`             INT          NOT NULL DEFAULT 0,
    -- Mobile data (KB): what is left, used this cycle, and a prepaid SIM's
    -- part-used MB past its allowance (billed per whole MB).
    `data_kb`             INT          NOT NULL DEFAULT 0,
    `data_used_kb`        INT          NOT NULL DEFAULT 0,
    `data_overage_kb`     INT          NOT NULL DEFAULT 0,
    `low_data_notified`   TINYINT(1)   NOT NULL DEFAULT 0,
    `status`              VARCHAR(16)  NOT NULL DEFAULT 'active',
    `billing_cycle_start` TIMESTAMP    NULL DEFAULT NULL,
    `next_billing_at`     TIMESTAMP    NULL DEFAULT NULL,
    `grace_expires_at`    TIMESTAMP    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_account_sim` (`sim_id`),
    KEY `idx_account_carrier` (`carrier_id`),
    KEY `idx_account_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Usage / billing records (idempotent via unique reference per sim).
CREATE TABLE IF NOT EXISTS `phone_carrier_usage` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `usage_id`     VARCHAR(40)  NOT NULL,
    `sim_id`       VARCHAR(40)  NOT NULL,
    `line_id`      VARCHAR(40)  DEFAULT NULL,
    `usage_type`   VARCHAR(16)  NOT NULL, -- call | message | topup | plan | fee
    `units`        INT          NOT NULL DEFAULT 0,
    `amount`       INT          NOT NULL DEFAULT 0,
    `reference_id` VARCHAR(64)  DEFAULT NULL,
    `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_usage_id` (`usage_id`),
    UNIQUE KEY `uq_usage_ref` (`sim_id`, `reference_id`),
    KEY `idx_usage_sim` (`sim_id`, `created_at`),
    KEY `idx_usage_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Number change audit.
CREATE TABLE IF NOT EXISTS `phone_number_history` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id`    VARCHAR(40) NOT NULL,
    `old_number` VARCHAR(20) DEFAULT NULL,
    `new_number` VARCHAR(20) DEFAULT NULL,
    `reason`     VARCHAR(32) DEFAULT NULL,
    `changed_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_numhist_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SIM->SIM / device transfer audit.
CREATE TABLE IF NOT EXISTS `phone_sim_transfers` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transfer_id`   VARCHAR(40) NOT NULL,
    `line_id`       VARCHAR(40) DEFAULT NULL,
    `source_sim_id` VARCHAR(40) DEFAULT NULL,
    `target_sim_id` VARCHAR(40) DEFAULT NULL,
    `transfer_type` VARCHAR(24) DEFAULT NULL,
    `status`        VARCHAR(16) NOT NULL DEFAULT 'pending',
    `created_at`    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at`  TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_transfer_id` (`transfer_id`),
    KEY `idx_transfer_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- SIM replacement audit.
CREATE TABLE IF NOT EXISTS `phone_sim_replacements` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `replacement_id` VARCHAR(40) NOT NULL,
    `line_id`        VARCHAR(40) DEFAULT NULL,
    `old_sim_id`     VARCHAR(40) DEFAULT NULL,
    `new_sim_id`     VARCHAR(40) DEFAULT NULL,
    `fee`            INT         NOT NULL DEFAULT 0,
    `status`         VARCHAR(16) NOT NULL DEFAULT 'pending',
    `created_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at`   TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_replacement_id` (`replacement_id`),
    KEY `idx_replacement_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Released numbers awaiting cooldown before re-availability.
CREATE TABLE IF NOT EXISTS `phone_released_numbers` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `phone_number`    VARCHAR(20) NOT NULL,
    `released_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `available_after` TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_released_number` (`phone_number`),
    KEY `idx_released_after` (`available_after`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Emergency call audit (flagged, dedicated).
CREATE TABLE IF NOT EXISTS `phone_emergency_calls` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id`    VARCHAR(40) DEFAULT NULL,
    `number`     VARCHAR(20) NOT NULL,
    `identifier` VARCHAR(64) DEFAULT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_emergency_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Payphones (config/payphones.lua): a phone box's number, made the first time
-- someone uses it. payphone_id = its map position (PayphoneLogic.id).
CREATE TABLE IF NOT EXISTS `phone_payphones` (
    `payphone_id`        VARCHAR(48) NOT NULL,
    `line_id`            VARCHAR(40) NOT NULL,
    `model`              VARCHAR(48) DEFAULT NULL,
    `x`                  FLOAT       NOT NULL,
    `y`                  FLOAT       NOT NULL,
    `z`                  FLOAT       NOT NULL,
    `created_at`         TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`payphone_id`),
    UNIQUE KEY `uq_payphone_line` (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Media, gallery, maps, services and mail
-- ---------------------------------------------------------------------

-- Central media record. One row per stored image; raw image data is NEVER
-- stored in SQL — only the provider URL/reference + normalized metadata.
CREATE TABLE IF NOT EXISTS `phone_media` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `media_id`         VARCHAR(40)  NOT NULL,
    `owner_line_id`    VARCHAR(40)  NOT NULL,
    `owner_device_id`  VARCHAR(32)  DEFAULT NULL,
    `remote_url`       VARCHAR(512) NOT NULL,
    `remote_reference` VARCHAR(255) DEFAULT NULL,      -- provider deletion ref (never sent to clients)
    `thumbnail_url`    VARCHAR(512) DEFAULT NULL,
    `mime_type`        VARCHAR(32)  NOT NULL DEFAULT 'image/jpeg',
    `file_size`        INT UNSIGNED NOT NULL DEFAULT 0,
    `width`            SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `height`           SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `orientation`      VARCHAR(12)  NOT NULL DEFAULT 'portrait',  -- portrait | landscape
    `kind`             VARCHAR(8)   NOT NULL DEFAULT 'photo',     -- photo | video
    `duration_ms`      INT UNSIGNED DEFAULT NULL,                 -- videos only
    `source`           VARCHAR(24)  NOT NULL DEFAULT 'camera',
    `capture_x`        FLOAT        DEFAULT NULL,
    `capture_y`        FLOAT        DEFAULT NULL,
    `capture_z`        FLOAT        DEFAULT NULL,
    `taken_at`         TIMESTAMP    NULL DEFAULT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at`       TIMESTAMP    NULL DEFAULT NULL, -- soft delete (Recently Deleted)
    `purge_after`      TIMESTAMP    NULL DEFAULT NULL, -- hard purge deadline once soft-deleted
    `metadata_json`    TEXT         DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_media_id` (`media_id`),
    KEY `idx_media_owner` (`owner_line_id`, `deleted_at`, `created_at`),
    KEY `idx_media_purge` (`purge_after`),
    -- MediaServer.sweepTombstones: deleted, no purge deadline, by id.
    KEY `idx_media_tombstone` (`purge_after`, `deleted_at`, `id`),
    KEY `idx_media_source` (`owner_line_id`, `source`),
    -- Purge: does another record still share the remote file / poster?
    KEY `idx_media_remote_url` (`remote_url`(191)),
    KEY `idx_media_thumbnail_url` (`thumbnail_url`(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Access grants beyond the owner (message recipients, mail recipients, shares).
-- Revoke by setting revoked_at (rows are audit history, never repurposed).
CREATE TABLE IF NOT EXISTS `phone_media_access` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `media_id`    VARCHAR(40) NOT NULL,
    `line_id`     VARCHAR(40) NOT NULL,
    `access_type` VARCHAR(24) NOT NULL,   -- owner|message|mail|contact_avatar|wallpaper|shared
    `granted_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `revoked_at`  TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_media_access` (`media_id`, `line_id`, `access_type`),
    KEY `idx_access_line` (`line_id`),
    KEY `idx_access_media` (`media_id`, `revoked_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-line storage usage counters (kept incrementally; avoids recalculating
-- SUM(file_size) on every upload).
CREATE TABLE IF NOT EXISTS `phone_media_usage` (
    `line_id`       VARCHAR(40)     NOT NULL,
    `photo_count`   INT UNSIGNED    NOT NULL DEFAULT 0,
    `storage_bytes` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `uploads_today` INT UNSIGNED    NOT NULL DEFAULT 0,
    `uploads_day`   DATE            DEFAULT NULL,
    `updated_at`    TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Favorites (per owner line).
CREATE TABLE IF NOT EXISTS `phone_gallery_favorites` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `line_id`    VARCHAR(40) NOT NULL,
    `media_id`   VARCHAR(40) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_favorite` (`line_id`, `media_id`),
    KEY `idx_fav_line` (`line_id`),
    KEY `idx_fav_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Albums (per owner line; soft-deleted; deleting an album keeps its photos).
CREATE TABLE IF NOT EXISTS `phone_albums` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `album_id`       VARCHAR(40) NOT NULL,
    `owner_line_id`  VARCHAR(40) NOT NULL,
    `title`          VARCHAR(48) NOT NULL,
    `cover_media_id` VARCHAR(40) DEFAULT NULL,
    `created_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at`     TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_album_id` (`album_id`),
    KEY `idx_albums_owner` (`owner_line_id`, `deleted_at`),
    KEY `idx_albums_cover` (`cover_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Album membership (unique per album+media; position preserves manual order).
CREATE TABLE IF NOT EXISTS `phone_album_media` (
    `id`       BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `album_id` VARCHAR(40) NOT NULL,
    `media_id` VARCHAR(40) NOT NULL,
    `position` INT UNSIGNED NOT NULL DEFAULT 0,
    `added_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_album_media` (`album_id`, `media_id`),
    KEY `idx_album_media_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Message attachments: immutable snapshot rows separate from message text.
-- A gallery soft/hard delete NEVER cascades here — recipients keep access via
-- phone_media_access rows of type 'message'.
CREATE TABLE IF NOT EXISTS `phone_message_attachments` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `attachment_id`   VARCHAR(40) NOT NULL,
    `message_id`      VARCHAR(40) NOT NULL,
    `media_id`        VARCHAR(40) NOT NULL,
    `attachment_type` VARCHAR(16) NOT NULL DEFAULT 'image',
    `position`        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `caption`         VARCHAR(200) DEFAULT NULL,
    `created_at`      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_attachment_id` (`attachment_id`),
    KEY `idx_att_message` (`message_id`),
    KEY `idx_att_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Saved locations (per owner line).
CREATE TABLE IF NOT EXISTS `phone_saved_locations` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `location_id`   VARCHAR(40) NOT NULL,
    `owner_key`     VARCHAR(40) NOT NULL,
    `label`         VARCHAR(48) NOT NULL,
    `description`   VARCHAR(180) DEFAULT NULL,
    `x`             FLOAT NOT NULL,
    `y`             FLOAT NOT NULL,
    `z`             FLOAT NOT NULL DEFAULT 0,
    `heading`       FLOAT NOT NULL DEFAULT 0,
    `street`        VARCHAR(64) DEFAULT NULL,
    `zone`          VARCHAR(64) DEFAULT NULL,
    `icon`          VARCHAR(32) DEFAULT 'map-pin',
    `color`         VARCHAR(9)  DEFAULT '#0a84ff',
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `last_used_at`  TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_location_id` (`location_id`),
    KEY `idx_locations_owner` (`owner_key`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Recent locations (bounded ring per line; oldest trimmed by app code).
CREATE TABLE IF NOT EXISTS `phone_recent_locations` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `owner_key`     VARCHAR(40) NOT NULL,
    `label`         VARCHAR(48) DEFAULT NULL,
    `x`             FLOAT NOT NULL,
    `y`             FLOAT NOT NULL,
    `z`             FLOAT NOT NULL DEFAULT 0,
    `street`        VARCHAR(64) DEFAULT NULL,
    `zone`          VARCHAR(64) DEFAULT NULL,
    `source`        VARCHAR(24) NOT NULL DEFAULT 'waypoint', -- waypoint | share | request | photo
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_recent_owner` (`owner_key`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Service requests. True requester identity is ALWAYS stored (is_anonymous only
-- hides it from the employee-facing payloads).
CREATE TABLE IF NOT EXISTS `phone_service_requests` (
    `id`                    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id`            VARCHAR(40) NOT NULL,
    `service_id`            VARCHAR(40) NOT NULL,
    `requester_line_id`     VARCHAR(40) NOT NULL,
    `requester_number`      VARCHAR(20) NOT NULL,
    `assigned_identifier`   VARCHAR(64) DEFAULT NULL,   -- employee citizenid
    `assigned_number`       VARCHAR(20) DEFAULT NULL,   -- employee callback number (optional)
    `description`           VARCHAR(220) NOT NULL,
    `location_x`            FLOAT DEFAULT NULL,
    `location_y`            FLOAT DEFAULT NULL,
    `location_z`            FLOAT DEFAULT NULL,
    `street`                VARCHAR(64) DEFAULT NULL,
    `zone`                  VARCHAR(64) DEFAULT NULL,
    `is_anonymous`          TINYINT(1) NOT NULL DEFAULT 0,
    `status`                VARCHAR(16) NOT NULL DEFAULT 'pending',
    `created_at`            TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `accepted_at`           TIMESTAMP NULL DEFAULT NULL,
    `completed_at`          TIMESTAMP NULL DEFAULT NULL,
    `expires_at`            TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_request_id` (`request_id`),
    KEY `idx_req_service_status` (`service_id`, `status`, `created_at`),
    KEY `idx_req_requester` (`requester_line_id`, `created_at`),
    KEY `idx_req_assigned` (`assigned_identifier`, `status`),
    KEY `idx_req_expiry` (`status`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Immutable transition audit for service requests.
CREATE TABLE IF NOT EXISTS `phone_service_request_history` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id`  VARCHAR(40) NOT NULL,
    `from_status` VARCHAR(16) DEFAULT NULL,
    `to_status`   VARCHAR(16) NOT NULL,
    `actor_type`  VARCHAR(16) NOT NULL DEFAULT 'system', -- requester | employee | system
    `actor_ident` VARCHAR(64) DEFAULT NULL,
    `created_at`  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_hist_request` (`request_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mail message bodies (shared across recipients; recipient state is separate,
-- so "delete for me" never removes another recipient's copy).
CREATE TABLE IF NOT EXISTS `phone_mail_messages` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mail_id`        VARCHAR(40) NOT NULL,
    `sender_line_id` VARCHAR(40) DEFAULT NULL,    -- NULL for system mail
    `sender_type`    VARCHAR(12) NOT NULL DEFAULT 'line', -- line | system
    `sender_label`   VARCHAR(64) DEFAULT NULL,
    `sender_address` VARCHAR(96) DEFAULT NULL,
    `subject`        VARCHAR(96) NOT NULL,
    `body`           TEXT NOT NULL,
    `created_at`     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at`     TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_mail_id` (`mail_id`),
    KEY `idx_mail_sender` (`sender_line_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-recipient mail state (read/star/archive/trash are recipient-local).
CREATE TABLE IF NOT EXISTS `phone_mail_recipients` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `mail_id`        VARCHAR(40) NOT NULL,
    -- Number it was addressed to (NULL: a Nova Account mailbox).
    `line_id`        VARCHAR(40) DEFAULT NULL,
    -- Owner of the phone holding the number when it arrived, or the mailbox's
    -- account (NULL: the SIM was in no phone; the next phone it goes into
    -- claims it).
    `owner_key`      VARCHAR(40) DEFAULT NULL,
    `recipient_type` VARCHAR(12) NOT NULL DEFAULT 'to',  -- to | sender_copy
    `is_read`        TINYINT(1) NOT NULL DEFAULT 0,
    `is_starred`     TINYINT(1) NOT NULL DEFAULT 0,
    `is_archived`    TINYINT(1) NOT NULL DEFAULT 0,
    `is_trashed`     TINYINT(1) NOT NULL DEFAULT 0,
    `trashed_at`     TIMESTAMP NULL DEFAULT NULL,
    `deleted_at`     TIMESTAMP NULL DEFAULT NULL,        -- permanent delete FOR THIS recipient
    `read_at`        TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_mail_recipient` (`mail_id`, `line_id`),
    KEY `idx_mailrcpt_line` (`line_id`, `deleted_at`, `is_trashed`, `is_archived`),
    KEY `idx_mailrcpt_unread` (`line_id`, `is_read`, `deleted_at`),
    KEY `idx_mailrcpt_owner` (`owner_key`, `deleted_at`, `is_trashed`, `is_archived`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mail attachments (image via media_id; location/contact via payload_json).
CREATE TABLE IF NOT EXISTS `phone_mail_attachments` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `attachment_id`   VARCHAR(40) NOT NULL,
    `mail_id`         VARCHAR(40) NOT NULL,
    `attachment_type` VARCHAR(16) NOT NULL,
    `media_id`        VARCHAR(40) DEFAULT NULL,
    `payload_json`    TEXT DEFAULT NULL,
    `position`        TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_mail_attachment_id` (`attachment_id`),
    KEY `idx_mailatt_mail` (`mail_id`),
    KEY `idx_mailatt_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mail action buttons: predefined types + validated payloads + optional
-- single-use hashed tokens and expiry. Never stores executable content.
CREATE TABLE IF NOT EXISTS `phone_mail_actions` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `action_id`     VARCHAR(40) NOT NULL,
    `mail_id`       VARCHAR(40) NOT NULL,
    `action_type`   VARCHAR(32) NOT NULL,
    `label`         VARCHAR(32) NOT NULL,
    `payload_json`  TEXT DEFAULT NULL,
    `token_hash`    VARCHAR(255) DEFAULT NULL,
    `is_single_use` TINYINT(1) NOT NULL DEFAULT 0,
    `used_at`       TIMESTAMP NULL DEFAULT NULL,
    `used_by_line`  VARCHAR(40) DEFAULT NULL,
    `expires_at`    TIMESTAMP NULL DEFAULT NULL,
    `created_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_mail_action_id` (`action_id`),
    KEY `idx_mailact_mail` (`mail_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Custom wallpapers: device-level media wallpaper + display options.
-- settings.wallpaper = 'custom_media' selects it.
CREATE TABLE IF NOT EXISTS `phone_device_wallpapers` (
    `device_id`     VARCHAR(32) NOT NULL,
    `screen`        VARCHAR(8)  NOT NULL DEFAULT 'both',  -- home | lock | both
    `media_id`      VARCHAR(40) NOT NULL,
    `source_line_id` VARCHAR(40) DEFAULT NULL,            -- line that authorized the copy
    `fit`           VARCHAR(12) NOT NULL DEFAULT 'cover', -- cover | contain
    `blur`          TINYINT UNSIGNED NOT NULL DEFAULT 0,  -- px
    `dark_overlay`  TINYINT UNSIGNED NOT NULL DEFAULT 0,  -- 0-80 (%)
    `updated_at`    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `screen`),
    KEY `idx_wallpapers_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contact avatars: contact -> media mapping; initials + colour remain the fallback.
CREATE TABLE IF NOT EXISTS `phone_contact_avatars` (
    `contact_id` VARCHAR(40) NOT NULL,
    `owner_key`  VARCHAR(40) NOT NULL,
    `media_id`   VARCHAR(40) NOT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`contact_id`),
    KEY `idx_contact_avatar_line` (`owner_key`),
    KEY `idx_contact_avatar_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Device runtime, ownership audit and voicemail
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `phone_device_runtime` (
    `device_id`           VARCHAR(32) NOT NULL,
    `charging`            TINYINT(1) NOT NULL DEFAULT 0,
    -- What it charges from: car | powerbank | pad (NULL: not charging).
    `charge_source`       VARCHAR(16) DEFAULT NULL,
    -- Off: at 0% (until charged back to Config.Charging.powerOnLevel) or by the player.
    `powered_off`         TINYINT(1) NOT NULL DEFAULT 0,
    -- Turned off by the player (stays off until turned on by hand).
    `off_by_user`         TINYINT(1) NOT NULL DEFAULT 0,
    -- Minutes the screen has been on (screen wear).
    `runtime_ticks`       INT UNSIGNED NOT NULL DEFAULT 0,
    -- % ever charged (charge cycles), and % charged since health last dropped.
    `charged_total`       DOUBLE NOT NULL DEFAULT 0,
    `health_wear`         DOUBLE NOT NULL DEFAULT 0,
    `updated_at`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_voicemails` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `voicemail_id`      VARCHAR(40) NOT NULL,
    `recipient_line_id` VARCHAR(40) NOT NULL,
    `owner_key`         VARCHAR(40) DEFAULT NULL, -- claimed like mail
    `caller_line_id`    VARCHAR(40) DEFAULT NULL,
    `caller_number`     VARCHAR(24) DEFAULT NULL,
    `audio_url`         VARCHAR(512) DEFAULT NULL,
    `transcript`        VARCHAR(500) DEFAULT NULL,
    `duration_seconds`  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `is_read`           TINYINT(1) NOT NULL DEFAULT 0,
    `created_at`        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at`        TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_voicemail_id` (`voicemail_id`),
    KEY `idx_voicemail_recipient` (`recipient_line_id`, `deleted_at`, `created_at`),
    KEY `idx_voicemail_owner` (`owner_key`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Nova Wallet
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `phone_bank_accounts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_id` VARCHAR(40) NOT NULL,
    `owner_citizenid` VARCHAR(64) NOT NULL,
    `account_type` VARCHAR(16) NOT NULL DEFAULT 'personal',
    `label` VARCHAR(40) NOT NULL DEFAULT 'Nova Wallet',
    -- Only for a business account with no banking-resource account: Nova's
    -- own ledger. Personal accounts are the owner's real bank (provider).
    `balance` BIGINT NOT NULL DEFAULT 0,
    -- A business account's job / society account in the banking resource.
    `shared_account` VARCHAR(64) DEFAULT NULL,
    `card_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `daily_limit` INT UNSIGNED NOT NULL DEFAULT 25000,
    `spent_today` INT UNSIGNED NOT NULL DEFAULT 0,
    `spent_date` DATE DEFAULT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'active',
    `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_bank_account_id` (`account_id`),
    UNIQUE KEY `uq_bank_personal_owner` (`owner_citizenid`, `account_type`),
    KEY `idx_bank_owner` (`owner_citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_bank_account_members` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_id` VARCHAR(40) NOT NULL,
    `citizenid` VARCHAR(64) NOT NULL,
    `role` VARCHAR(20) NOT NULL DEFAULT 'member',
    `can_spend` TINYINT(1) NOT NULL DEFAULT 0,
    `can_manage` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_bank_account_member` (`account_id`, `citizenid`),
    KEY `idx_bank_member_citizen` (`citizenid`, `account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_bank_transactions` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `transaction_id` VARCHAR(40) NOT NULL,
    `from_account_id` VARCHAR(40) DEFAULT NULL,
    `to_account_id` VARCHAR(40) DEFAULT NULL,
    `initiator_citizenid` VARCHAR(64) NOT NULL,
    `transaction_type` VARCHAR(20) NOT NULL,
    `amount` BIGINT UNSIGNED NOT NULL,
    `fee` INT UNSIGNED NOT NULL DEFAULT 0,
    `status` VARCHAR(16) NOT NULL DEFAULT 'pending',
    `note` VARCHAR(120) DEFAULT NULL,
    `reference_id` VARCHAR(64) DEFAULT NULL,
    `idempotency_key` VARCHAR(64) NOT NULL,
    `metadata_json` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_bank_transaction_id` (`transaction_id`),
    UNIQUE KEY `uq_bank_idempotency` (`initiator_citizenid`, `idempotency_key`),
    KEY `idx_bank_tx_from` (`from_account_id`, `created_at`),
    KEY `idx_bank_tx_to` (`to_account_id`, `created_at`),
    KEY `idx_bank_tx_reference` (`reference_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_bank_recipients` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `recipient_id` VARCHAR(40) NOT NULL,
    `owner_citizenid` VARCHAR(64) NOT NULL,
    `target_account_id` VARCHAR(40) NOT NULL,
    `label` VARCHAR(40) NOT NULL,
    `phone_number` VARCHAR(24) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_bank_recipient_id` (`recipient_id`),
    UNIQUE KEY `uq_bank_recipient_target` (`owner_citizenid`, `target_account_id`),
    KEY `idx_bank_recipients_owner` (`owner_citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_payment_requests` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `request_id` VARCHAR(40) NOT NULL,
    `request_type` VARCHAR(16) NOT NULL DEFAULT 'payment',
    `requester_account_id` VARCHAR(40) NOT NULL,
    `payer_account_id` VARCHAR(40) NOT NULL,
    `requester_line_id` VARCHAR(40) DEFAULT NULL,
    `payer_line_id` VARCHAR(40) DEFAULT NULL,
    `amount` BIGINT UNSIGNED NOT NULL,
    `note` VARCHAR(120) DEFAULT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'pending',
    `transaction_id` VARCHAR(40) DEFAULT NULL,
    `processing_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NOT NULL,
    `resolved_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_payment_request_id` (`request_id`),
    KEY `idx_payment_request_payer` (`payer_account_id`, `status`, `created_at`),
    KEY `idx_payment_request_requester` (`requester_account_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_bank_audit` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_id` VARCHAR(40) DEFAULT NULL,
    `actor_citizenid` VARCHAR(64) DEFAULT NULL,
    `action` VARCHAR(32) NOT NULL,
    `reference_id` VARCHAR(64) DEFAULT NULL,
    `details_json` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_bank_audit_account` (`account_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Echo
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `phone_social_profiles` (
    `line_id` VARCHAR(40) NOT NULL,
    `handle` VARCHAR(24) NOT NULL,
    `display_name` VARCHAR(40) NOT NULL,
    `bio` VARCHAR(160) DEFAULT NULL,
    `avatar_media_id` VARCHAR(48) DEFAULT NULL,
    `privacy` VARCHAR(16) NOT NULL DEFAULT 'public',
    `discoverable` TINYINT(1) NOT NULL DEFAULT 1,
    `verified` TINYINT(1) NOT NULL DEFAULT 0,
    `verified_revoked` TINYINT(1) NOT NULL DEFAULT 0,  -- badge taken away / cleared on rename: the auto sweep skips it
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`line_id`),
    UNIQUE KEY `uq_social_handle` (`handle`),
    KEY `idx_social_discovery` (`discoverable`, `handle`),
    KEY `idx_social_profiles_avatar` (`avatar_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_follows` (
    `follower_line_id` VARCHAR(40) NOT NULL,
    `following_line_id` VARCHAR(40) NOT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'accepted',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `accepted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`follower_line_id`, `following_line_id`),
    KEY `idx_social_following` (`following_line_id`, `status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_blocks` (
    `blocker_line_id` VARCHAR(40) NOT NULL,
    `blocked_line_id` VARCHAR(40) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`blocker_line_id`, `blocked_line_id`),
    KEY `idx_social_blocked` (`blocked_line_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_posts` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id` VARCHAR(40) NOT NULL,
    `author_line_id` VARCHAR(40) NOT NULL,
    `post_type` VARCHAR(16) NOT NULL DEFAULT 'post',
    `parent_post_id` VARCHAR(40) DEFAULT NULL,
    `repost_of_post_id` VARCHAR(40) DEFAULT NULL,
    `content` VARCHAR(500) DEFAULT NULL,
    `visibility` VARCHAR(16) NOT NULL DEFAULT 'public',
    `like_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `reply_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `repost_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_social_post_id` (`post_id`),
    UNIQUE KEY `uq_social_repost` (`author_line_id`, `repost_of_post_id`),
    KEY `idx_social_feed` (`deleted_at`, `created_at`),
    KEY `idx_social_author` (`author_line_id`, `deleted_at`, `created_at`),
    KEY `idx_social_parent` (`parent_post_id`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_post_media` (
    `post_id` VARCHAR(40) NOT NULL,
    `media_id` VARCHAR(48) NOT NULL,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`post_id`, `media_id`),
    KEY `idx_social_post_media_order` (`post_id`, `position`),
    KEY `idx_social_post_media_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_likes` (
    `post_id` VARCHAR(40) NOT NULL,
    `line_id` VARCHAR(40) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`post_id`, `line_id`),
    KEY `idx_social_likes_line` (`line_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_hashtags` (
    `post_id` VARCHAR(40) NOT NULL,
    `tag` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`post_id`, `tag`),
    KEY `idx_social_tag` (`tag`, `post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_mentions` (
    `post_id` VARCHAR(40) NOT NULL,
    `mentioned_line_id` VARCHAR(40) NOT NULL,
    PRIMARY KEY (`post_id`, `mentioned_line_id`),
    KEY `idx_social_mentioned` (`mentioned_line_id`, `post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_reports` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id` VARCHAR(40) NOT NULL,
    `reporter_line_id` VARCHAR(40) NOT NULL,
    `target_type` VARCHAR(16) NOT NULL,
    `target_id` VARCHAR(40) NOT NULL,
    `reason` VARCHAR(32) NOT NULL,
    `details` VARCHAR(300) DEFAULT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'open',
    `resolved_by` VARCHAR(64) DEFAULT NULL,
    `resolution` VARCHAR(32) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_social_report_id` (`report_id`),
    UNIQUE KEY `uq_social_report_once` (`reporter_line_id`, `target_type`, `target_id`),
    KEY `idx_social_reports_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_social_moderation` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `actor` VARCHAR(64) NOT NULL,
    `action` VARCHAR(32) NOT NULL,
    `target_type` VARCHAR(16) NOT NULL,
    `target_id` VARCHAR(40) NOT NULL,
    `reason` VARCHAR(300) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_social_moderation_target` (`target_type`, `target_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Calendar, notes, alarms and timers
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `phone_calendar_events` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `event_id` VARCHAR(40) NOT NULL,
    `device_id` VARCHAR(32) NOT NULL,
    `title` VARCHAR(80) NOT NULL,
    `description` VARCHAR(500) DEFAULT NULL,
    `start_unix` BIGINT UNSIGNED NOT NULL,
    `end_unix` BIGINT UNSIGNED NOT NULL,
    `all_day` TINYINT(1) NOT NULL DEFAULT 0,
    `recurrence` VARCHAR(16) NOT NULL DEFAULT 'none',
    `reminder_minutes` SMALLINT UNSIGNED DEFAULT NULL,
    `reminder_fired_for` BIGINT UNSIGNED DEFAULT NULL,
    `location_label` VARCHAR(80) DEFAULT NULL,
    `location_x` DOUBLE DEFAULT NULL,
    `location_y` DOUBLE DEFAULT NULL,
    `location_z` DOUBLE DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_calendar_event_id` (`event_id`),
    KEY `idx_calendar_device_time` (`device_id`, `deleted_at`, `start_unix`),
    KEY `idx_calendar_reminders` (`deleted_at`, `start_unix`, `reminder_fired_for`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_notes` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `note_id` VARCHAR(40) NOT NULL,
    `device_id` VARCHAR(32) NOT NULL,
    `title` VARCHAR(80) NOT NULL,
    `body` TEXT NOT NULL,
    `folder` VARCHAR(40) NOT NULL DEFAULT 'Notes',
    `color` VARCHAR(16) NOT NULL DEFAULT 'yellow',
    `is_pinned` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_note_id` (`note_id`),
    KEY `idx_notes_device` (`device_id`, `deleted_at`, `is_pinned`, `updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_alarms` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `alarm_id` VARCHAR(40) NOT NULL,
    `device_id` VARCHAR(32) NOT NULL,
    `label` VARCHAR(60) NOT NULL DEFAULT 'Alarm',
    `hour` TINYINT UNSIGNED NOT NULL,
    `minute` TINYINT UNSIGNED NOT NULL,
    `days_json` VARCHAR(32) DEFAULT NULL,
    `enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `snooze_minutes` TINYINT UNSIGNED NOT NULL DEFAULT 9,
    `last_fired_key` VARCHAR(20) DEFAULT NULL,
    `utc_offset` SMALLINT NOT NULL DEFAULT 0,
    `utc_minute` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `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_alarm_id` (`alarm_id`),
    KEY `idx_alarms_device` (`device_id`, `enabled`),
    KEY `idx_alarms_due` (`enabled`, `utc_minute`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clock_timers` (
    `device_id` VARCHAR(32) NOT NULL,
    `label` VARCHAR(60) NOT NULL DEFAULT 'Timer',
    `end_unix` BIGINT UNSIGNED DEFAULT NULL,
    `duration_seconds` INT UNSIGNED NOT NULL DEFAULT 0,
    `running` TINYINT(1) NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`),
    KEY `idx_timers_due` (`running`, `end_unix`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Marketplace and business orders
-- ---------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS `phone_market_listings` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `listing_id` VARCHAR(40) NOT NULL,
    `seller_line_id` VARCHAR(40) NOT NULL,
    `title` VARCHAR(80) NOT NULL,
    `description` VARCHAR(800) NOT NULL,
    `category` VARCHAR(24) NOT NULL,
    `price` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `location_label` VARCHAR(80) DEFAULT NULL,
    `location_x` DOUBLE DEFAULT NULL,
    `location_y` DOUBLE DEFAULT NULL,
    `location_z` DOUBLE DEFAULT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'active',
    `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `expires_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    `closed_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_market_listing_id` (`listing_id`),
    KEY `idx_market_feed` (`status`, `expires_at`, `category`, `created_at`),
    KEY `idx_market_seller` (`seller_line_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_market_listing_media` (
    `listing_id` VARCHAR(40) NOT NULL,
    `media_id` VARCHAR(48) NOT NULL,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`listing_id`, `media_id`),
    KEY `idx_market_media_position` (`listing_id`, `position`),
    KEY `idx_market_media_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_market_reports` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id` VARCHAR(40) NOT NULL,
    `listing_id` VARCHAR(40) NOT NULL,
    `reporter_line_id` VARCHAR(40) NOT NULL,
    `reason` VARCHAR(32) NOT NULL,
    `details` VARCHAR(300) DEFAULT NULL,
    `status` VARCHAR(16) NOT NULL DEFAULT 'open',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_market_report_id` (`report_id`),
    UNIQUE KEY `uq_market_reporter_target` (`listing_id`, `reporter_line_id`),
    KEY `idx_market_reports_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_market_moderation` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `listing_id` VARCHAR(40) NOT NULL,
    `actor_identifier` VARCHAR(96) NOT NULL,
    `action` VARCHAR(20) NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_market_moderation_listing` (`listing_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_business_orders` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(40) NOT NULL,
    `business_id` VARCHAR(40) NOT NULL,
    `product_id` VARCHAR(40) NOT NULL,
    `customer_line_id` VARCHAR(40) NOT NULL,
    `customer_number` VARCHAR(20) NOT NULL,
    `customer_citizenid` VARCHAR(64) DEFAULT NULL,
    `assigned_citizenid` VARCHAR(64) DEFAULT NULL,
    `product_label` VARCHAR(80) NOT NULL,
    `unit_price` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `note` VARCHAR(400) DEFAULT NULL,
    `location_label` VARCHAR(80) DEFAULT NULL,
    `location_x` DOUBLE DEFAULT NULL,
    `location_y` DOUBLE DEFAULT NULL,
    `location_z` DOUBLE DEFAULT NULL,
    `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
    `invoice_request_id` VARCHAR(40) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `accepted_at` TIMESTAMP NULL DEFAULT NULL,
    `completed_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_business_order_id` (`order_id`),
    KEY `idx_business_orders_business` (`business_id`, `status`, `created_at`),
    KEY `idx_business_orders_customer` (`customer_line_id`, `created_at`),
    KEY `idx_business_orders_employee` (`assigned_citizenid`, `status`),
    KEY `idx_business_orders_product` (`product_id`, `status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_business_order_history` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(40) NOT NULL,
    `from_status` VARCHAR(20) DEFAULT NULL,
    `to_status` VARCHAR(20) NOT NULL,
    `actor_type` VARCHAR(20) NOT NULL,
    `actor_identifier` VARCHAR(64) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_business_order_history` (`order_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bites (server/bites.lua): a food order is a Business order (product_id
-- '@bites') plus its payment / courier row and its cart.
CREATE TABLE IF NOT EXISTS `phone_bites_orders` (
    `order_id` VARCHAR(40) NOT NULL,                     -- phone_business_orders.order_id
    `payer_citizenid` VARCHAR(64) NOT NULL,
    `subtotal` INT UNSIGNED NOT NULL DEFAULT 0,
    `delivery_fee` INT UNSIGNED NOT NULL DEFAULT 0,
    `tip` INT UNSIGNED NOT NULL DEFAULT 0,
    `payment_status` VARCHAR(12) NOT NULL DEFAULT 'held', -- charging | held | settled | refunded | refund_due
    `payout_status` VARCHAR(8) DEFAULT NULL,             -- paid | due (after delivery)
    `courier_citizenid` VARCHAR(64) DEFAULT NULL,
    `claimed_at` TIMESTAMP NULL DEFAULT NULL,
    `delivered_at` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`order_id`),
    KEY `idx_bites_courier` (`courier_citizenid`),
    KEY `idx_bites_payment` (`payment_status`),
    KEY `idx_bites_payout` (`payout_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_bites_order_items` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_id` VARCHAR(40) NOT NULL,
    `product_id` VARCHAR(40) NOT NULL,
    `label` VARCHAR(80) NOT NULL,
    `quantity` SMALLINT UNSIGNED NOT NULL,
    `unit_price` INT UNSIGNED NOT NULL,
    PRIMARY KEY (`id`),
    KEY `idx_bites_items_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- App platform and device operations
-- ---------------------------------------------------------------------

-- Per-device runtime permission grants for external (SDK) apps.
CREATE TABLE IF NOT EXISTS `phone_app_permissions` (
    `device_id`  VARCHAR(32) NOT NULL,
    `app_id`     VARCHAR(32) NOT NULL,
    `permission` VARCHAR(24) NOT NULL,
    `state`      VARCHAR(8)  NOT NULL DEFAULT 'granted',   -- granted | denied
    `updated_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `app_id`, `permission`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-device notification channel preferences. Absent rows use the channel
-- defaults declared in config/apps.lua or by the registering resource.
CREATE TABLE IF NOT EXISTS `phone_notification_channels` (
    `device_id`  VARCHAR(32) NOT NULL,
    `app_id`     VARCHAR(32) NOT NULL,
    `channel_id` VARCHAR(32) NOT NULL,
    `enabled`    TINYINT(1)  NOT NULL DEFAULT 1,
    `banner`     TINYINT(1)  NOT NULL DEFAULT 1,
    `sound`      TINYINT(1)  NOT NULL DEFAULT 1,
    `lockscreen` TINYINT(1)  NOT NULL DEFAULT 1,
    `updated_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `app_id`, `channel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_device_security` (
    `device_id`          VARCHAR(32)  NOT NULL,
    `lost_mode`          TINYINT(1)   NOT NULL DEFAULT 0,
    `reported_stolen`    TINYINT(1)   NOT NULL DEFAULT 0,
    `lost_message`       VARCHAR(160) DEFAULT NULL,
    `lost_contact`       VARCHAR(20)  DEFAULT NULL,
    `lost_since`         TIMESTAMP    NULL DEFAULT NULL,
    `lost_account_id`    VARCHAR(24)  DEFAULT NULL,
    `wipe_requested_at`  TIMESTAMP    NULL DEFAULT NULL,
    `wipe_requested_by`  VARCHAR(64)  DEFAULT NULL,
    `wiped_at`           TIMESTAMP    NULL DEFAULT NULL,
    `last_seen_x`        FLOAT        DEFAULT NULL,
    `last_seen_y`        FLOAT        DEFAULT NULL,
    `last_seen_z`        FLOAT        DEFAULT NULL,
    `last_seen_at`       TIMESTAMP    NULL DEFAULT NULL,
    `updated_at`         TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`),
    KEY `idx_device_security_flags` (`lost_mode`, `reported_stolen`),
    KEY `idx_device_security_wipe` (`wipe_requested_at`, `wiped_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Face Unlock (Config.FaceId): faces enrolled on a phone, as the characters'
-- citizen ids (server-only). Device data, but never in backups or transfers;
-- deleted when the PIN changes or is removed and on erase.
CREATE TABLE IF NOT EXISTS `phone_device_faces` (
    `device_id`   VARCHAR(32)      NOT NULL,
    `slot`        TINYINT UNSIGNED NOT NULL, -- 1 main, 2 alternate appearance
    `citizen_id`  VARCHAR(64)      NOT NULL,
    `created_at`  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `slot`),
    KEY `idx_device_faces_citizen` (`citizen_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_backups` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `backup_id`        VARCHAR(40)  NOT NULL,
    `citizen_id`       VARCHAR(64)  NOT NULL,
    `account_id`       VARCHAR(24)  DEFAULT NULL,
    `source_device_id` VARCHAR(32)  NOT NULL,
    `model`            VARCHAR(32)  NOT NULL,
    `label`            VARCHAR(48)  NOT NULL,
    `schema_version`   SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    `size_bytes`       INT UNSIGNED NOT NULL DEFAULT 0,
    `summary_json`     TEXT         DEFAULT NULL,
    `payload_json`     LONGTEXT     NOT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at`       TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_backup_id` (`backup_id`),
    KEY `idx_backups_citizen` (`citizen_id`, `deleted_at`, `created_at`),
    KEY `idx_backups_account` (`account_id`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_device_audit` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`   VARCHAR(32)  NOT NULL,
    `action`      VARCHAR(32)  NOT NULL,
    `actor`       VARCHAR(64)  DEFAULT NULL,
    `detail_json` VARCHAR(1024) DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_device_audit` (`device_id`, `created_at`),
    KEY `idx_device_audit_action` (`action`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Snapgram (photo-sharing social app). Identity columns (*_id / account_id) hold the
-- Snapgram account id (phone_accounts, service 'snapgram').
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_snap_profiles` (
    `account_id`      VARCHAR(24)  NOT NULL,
    `handle`          VARCHAR(24)  NOT NULL,
    `display_name`    VARCHAR(40)  NOT NULL,
    `bio`             VARCHAR(160) DEFAULT NULL,
    `avatar_media_id` VARCHAR(48)  DEFAULT NULL,
    `is_private`      TINYINT(1)   NOT NULL DEFAULT 0,
    `verified`        TINYINT(1)   NOT NULL DEFAULT 0,
    `verified_revoked` TINYINT(1)  NOT NULL DEFAULT 0,   -- badge taken away / cleared on rename: the auto sweep skips it
    `activity_seen_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`),
    UNIQUE KEY `uq_snap_handle` (`handle`),
    KEY `idx_snap_profiles_avatar` (`avatar_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_follows` (
    `follower_id`  VARCHAR(24) NOT NULL,
    `following_id` VARCHAR(24) NOT NULL,
    `status`       VARCHAR(12) NOT NULL DEFAULT 'accepted',
    `created_at`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`follower_id`, `following_id`),
    KEY `idx_snap_following` (`following_id`, `status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_blocks` (
    `blocker_id` VARCHAR(24) NOT NULL,
    `blocked_id` VARCHAR(24) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`blocker_id`, `blocked_id`),
    KEY `idx_snap_blocked` (`blocked_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_posts` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `post_id`       VARCHAR(32)  NOT NULL,
    `author_id`     VARCHAR(24)  NOT NULL,
    `caption`       VARCHAR(500) DEFAULT NULL,
    `like_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `comment_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at`    TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_snap_post_id` (`post_id`),
    KEY `idx_snap_posts_author` (`author_id`, `deleted_at`, `created_at`),
    KEY `idx_snap_posts_recent` (`deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_post_media` (
    `post_id`  VARCHAR(32) NOT NULL,
    `media_id` VARCHAR(48) NOT NULL,
    `position` TINYINT UNSIGNED NOT NULL DEFAULT 1,
    PRIMARY KEY (`post_id`, `media_id`),
    KEY `idx_snap_post_media_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_likes` (
    `post_id`    VARCHAR(32) NOT NULL,
    `account_id` VARCHAR(24) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`post_id`, `account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_comments` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `comment_id` VARCHAR(32)  NOT NULL,
    `post_id`    VARCHAR(32)  NOT NULL,
    `author_id`  VARCHAR(24)  NOT NULL,
    `body`       VARCHAR(300) NOT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_snap_comment_id` (`comment_id`),
    KEY `idx_snap_comments_post` (`post_id`, `deleted_at`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_saves` (
    `account_id` VARCHAR(24) NOT NULL,
    `post_id`    VARCHAR(32) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`, `post_id`),
    KEY `idx_snap_saves_recent` (`account_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_tags` (
    `post_id`    VARCHAR(32) NOT NULL,
    `account_id` VARCHAR(24) NOT NULL,
    PRIMARY KEY (`post_id`, `account_id`),
    KEY `idx_snap_tags_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_stories` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `story_id`   VARCHAR(32)  NOT NULL,
    `author_id`  VARCHAR(24)  NOT NULL,
    `media_id`   VARCHAR(48)  NOT NULL,
    `caption`    VARCHAR(120) DEFAULT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_snap_story_id` (`story_id`),
    KEY `idx_snap_stories_author` (`author_id`, `expires_at`),
    KEY `idx_snap_stories_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_story_views` (
    `story_id`  VARCHAR(32) NOT NULL,
    `viewer_id` VARCHAR(24) NOT NULL,
    `viewed_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`story_id`, `viewer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One thread per account pair (account_a < account_b); per-member read state.
CREATE TABLE IF NOT EXISTS `phone_snap_threads` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `thread_id`       VARCHAR(32) NOT NULL,
    `account_a`       VARCHAR(24) NOT NULL,
    `account_b`       VARCHAR(24) NOT NULL,
    `a_read_at`       TIMESTAMP   NULL DEFAULT NULL,
    `b_read_at`       TIMESTAMP   NULL DEFAULT NULL,
    `last_message_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at`      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_snap_thread_id` (`thread_id`),
    UNIQUE KEY `uq_snap_thread_pair` (`account_a`, `account_b`),
    KEY `idx_snap_threads_b` (`account_b`, `last_message_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_snap_messages` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id` VARCHAR(32)  NOT NULL,
    `thread_id`  VARCHAR(32)  NOT NULL,
    `sender_id`  VARCHAR(24)  NOT NULL,
    `kind`       VARCHAR(16)  NOT NULL DEFAULT 'text',
    `body`       VARCHAR(500) DEFAULT NULL,
    `ref_id`     VARCHAR(32)  DEFAULT NULL,
    `created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_snap_message_id` (`message_id`),
    KEY `idx_snap_messages_thread` (`thread_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Likes, comments, follows and tags shown in the Activity screen.
CREATE TABLE IF NOT EXISTS `phone_snap_activity` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `recipient_id` VARCHAR(24)  NOT NULL,
    `actor_id`     VARCHAR(24)  NOT NULL,
    `kind`         VARCHAR(16)  NOT NULL,
    `post_id`      VARCHAR(32)  DEFAULT NULL,
    `detail`       VARCHAR(120) DEFAULT NULL,
    `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_snap_activity_recipient` (`recipient_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Ember (dating app). Identity columns hold the Ember account id
-- (phone_accounts, service 'ember'). birthdate is 'YYYY-MM-DD' text taken
-- from the character when the profile is created.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_ember_profiles` (
    `account_id`    VARCHAR(24)  NOT NULL,
    `display_name`  VARCHAR(24)  NOT NULL,
    `birthdate`     VARCHAR(10)  NOT NULL,
    `gender`        VARCHAR(12)  NOT NULL,
    `interested_in` VARCHAR(12)  NOT NULL,
    `bio`           VARCHAR(300) DEFAULT NULL,
    `age_min`       TINYINT UNSIGNED NOT NULL DEFAULT 18,
    `age_max`       TINYINT UNSIGNED NOT NULL DEFAULT 99,
    `discoverable`  TINYINT(1)   NOT NULL DEFAULT 1,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`),
    KEY `idx_ember_discover` (`discoverable`, `gender`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_ember_photos` (
    `account_id` VARCHAR(24) NOT NULL,
    `position`   TINYINT UNSIGNED NOT NULL,
    `media_id`   VARCHAR(48) NOT NULL,
    PRIMARY KEY (`account_id`, `position`),
    KEY `idx_ember_photos_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_ember_swipes` (
    `swiper_id`  VARCHAR(24) NOT NULL,
    `target_id`  VARCHAR(24) NOT NULL,
    `action`     VARCHAR(8)  NOT NULL,
    `created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`swiper_id`, `target_id`),
    KEY `idx_ember_swipes_target` (`target_id`, `action`),
    KEY `idx_ember_swipes_recent` (`swiper_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per pair (account_a < account_b); unmatching hides it for both.
CREATE TABLE IF NOT EXISTS `phone_ember_matches` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `match_id`        VARCHAR(32) NOT NULL,
    `account_a`       VARCHAR(24) NOT NULL,
    `account_b`       VARCHAR(24) NOT NULL,
    `a_read_at`       TIMESTAMP   NULL DEFAULT NULL,
    `b_read_at`       TIMESTAMP   NULL DEFAULT NULL,
    `last_message_at` TIMESTAMP   NULL DEFAULT NULL,
    `unmatched_at`    TIMESTAMP   NULL DEFAULT NULL,
    `unmatched_by`    VARCHAR(24) DEFAULT NULL,
    `created_at`      TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_ember_match_id` (`match_id`),
    UNIQUE KEY `uq_ember_match_pair` (`account_a`, `account_b`),
    KEY `idx_ember_match_b` (`account_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_ember_messages` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id` VARCHAR(32)  NOT NULL,
    `match_id`   VARCHAR(32)  NOT NULL,
    `sender_id`  VARCHAR(24)  NOT NULL,
    `body`       VARCHAR(500) NOT NULL,
    `created_at` TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_ember_message_id` (`message_id`),
    KEY `idx_ember_messages_match` (`match_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Reports also hide the pair from each other for good.
CREATE TABLE IF NOT EXISTS `phone_ember_reports` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `reporter_id` VARCHAR(24) NOT NULL,
    `target_id`   VARCHAR(24) NOT NULL,
    `reason`      VARCHAR(16) NOT NULL,
    `created_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_ember_report_pair` (`reporter_id`, `target_id`),
    KEY `idx_ember_reports_target` (`target_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Accounts: the Nova Account and every social app's own login share this
-- table, namespaced by `service` (a login is unique per service). Passwords
-- are hashed server-side; never returned to clients.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_accounts` (
    `id`              INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `account_id`      VARCHAR(24)  NOT NULL,
    `service`         VARCHAR(16)  NOT NULL,
    `login`           VARCHAR(64)  NOT NULL,
    -- The account holder's name (sign-up asks for both; shown as "First Last").
    `first_name`      VARCHAR(30)  NOT NULL,
    `last_name`       VARCHAR(30)  NOT NULL,
    `password_hash`   VARCHAR(255) NOT NULL,
    `created_by`      VARCHAR(64)  DEFAULT NULL,
    `failed_attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `locked_until`    TIMESTAMP    NULL DEFAULT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_login_at`   TIMESTAMP    NULL DEFAULT NULL,
    `deleted_at`      TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_account_id` (`account_id`),
    UNIQUE KEY `uq_account_login` (`service`, `login`),
    KEY `idx_account_creator` (`service`, `created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Accounts signed in on a phone (device-owned; cleared by sign-out/erase).
-- `is_active` marks the account in use when a service allows several.
CREATE TABLE IF NOT EXISTS `phone_account_devices` (
    `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`    VARCHAR(32)  NOT NULL,
    `service`      VARCHAR(16)  NOT NULL,
    `account_id`   VARCHAR(24)  NOT NULL,
    `is_active`    TINYINT(1)   NOT NULL DEFAULT 1,
    `signed_in_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_account_device` (`device_id`, `service`, `account_id`),
    KEY `idx_account_device_account` (`account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Wi-Fi routers (server/wifi.lua). A router is an inventory item; placed
-- ones stay in the world across restarts. The password is shown to its
-- owner in the Routers app (a router sticker, not an account secret).
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_wifi_routers` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `router_id`       VARCHAR(24)  NOT NULL,
    `owner_citizenid` VARCHAR(64)  NOT NULL,
    `network_name`    VARCHAR(32)  NOT NULL,
    `password`        VARCHAR(32)  NOT NULL,
    `placed`          TINYINT(1)   NOT NULL DEFAULT 0,
    `x`               FLOAT        DEFAULT NULL,
    `y`               FLOAT        DEFAULT NULL,
    `z`               FLOAT        DEFAULT NULL,
    `heading`         FLOAT        NOT NULL DEFAULT 0,
    `expires_at`      TIMESTAMP    NULL DEFAULT NULL,   -- time package: unlimited until then
    `data_kb`         BIGINT       NOT NULL DEFAULT 0,  -- data package left
    `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_router_id` (`router_id`),
    KEY `idx_router_owner` (`owner_citizenid`),
    KEY `idx_router_placed` (`placed`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Networks a phone joined (device-owned; erasing the phone forgets them).
CREATE TABLE IF NOT EXISTS `phone_wifi_joined` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `device_id`  VARCHAR(32) NOT NULL,
    `router_id`  VARCHAR(24) NOT NULL,
    `joined_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_wifi_joined` (`device_id`, `router_id`),
    KEY `idx_wifi_joined_router` (`router_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Subscriber records (server/subscriber_records.lua): every police search at
-- the data center terminal, for accountability.
CREATE TABLE IF NOT EXISTS `phone_records_lookups` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `officer_citizenid` VARCHAR(64) NOT NULL,
    `query_type`        VARCHAR(16) NOT NULL, -- number | citizen | export
    `query`             VARCHAR(64) NOT NULL,
    `found`             TINYINT(1)  NOT NULL DEFAULT 0,
    `terminal_id`       VARCHAR(32) DEFAULT NULL,
    `created_at`        TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_records_officer` (`officer_citizenid`, `created_at`),
    KEY `idx_records_query` (`query`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Personal Hotspot (server/hotspot.lua): the network a phone shares from its
-- mobile data. Phones join it like a router (phone_wifi_joined.router_id =
-- hotspot_id). Device data: erasing the phone deletes it.
CREATE TABLE IF NOT EXISTS `phone_hotspots` (
    `device_id`    VARCHAR(32) NOT NULL,
    `hotspot_id`   VARCHAR(24) NOT NULL,
    `network_name` VARCHAR(32) NOT NULL,
    `password`     VARCHAR(32) NOT NULL,
    `enabled`      TINYINT(1)  NOT NULL DEFAULT 0,
    `created_at`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`),
    UNIQUE KEY `uq_hotspot_id` (`hotspot_id`),
    KEY `idx_hotspot_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NovaBuds (server/novapods.lua). The item carries `novapodsId`; the
-- battery is stored with the time it was taken (it drains while worn and
-- charges in the case, computed from battery_at). `model` is the item name
-- (Config.NovaPods.models). The first pairing on a phone signed in to a Nova
-- Account makes them that account's (`owner_account_id`): only its phones
-- can pair them, and its Locate shows them (last seen location below).
CREATE TABLE IF NOT EXISTS `phone_novapods` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `novapods_id`       VARCHAR(24)  NOT NULL,
    `model`            VARCHAR(48)  DEFAULT NULL,
    `name`             VARCHAR(48)  DEFAULT NULL,
    `owner_account_id` VARCHAR(24)  DEFAULT NULL,
    `paired_device_id` VARCHAR(32)  DEFAULT NULL,
    `battery`          FLOAT        NOT NULL DEFAULT 100,
    `battery_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `worn`             TINYINT(1)   NOT NULL DEFAULT 0,
    -- % per minute it charges at right now (car charger, power bank or pad).
    `charge_rate`      FLOAT        NOT NULL DEFAULT 0,
    `last_seen_x`      FLOAT        DEFAULT NULL,
    `last_seen_y`      FLOAT        DEFAULT NULL,
    `last_seen_z`      FLOAT        DEFAULT NULL,
    `last_seen_at`     TIMESTAMP    NULL DEFAULT NULL,
    -- Battery health wears with charging like a phone's (Config.Charging).
    `health`           FLOAT        NOT NULL DEFAULT 100,
    `charged_total`    DOUBLE       NOT NULL DEFAULT 0,
    `health_wear`      DOUBLE       NOT NULL DEFAULT 0,
    -- Headset noise control: noise | transparency | off.
    `noise_control`    VARCHAR(12)  NOT NULL DEFAULT 'transparency',
    -- Locate: notify when left behind; lost mode (shown to whoever uses them).
    `left_behind`      TINYINT(1)   NOT NULL DEFAULT 1,
    `lost_mode`        TINYINT(1)   NOT NULL DEFAULT 0,
    `lost_message`     VARCHAR(160) DEFAULT NULL,
    `lost_contact`     VARCHAR(20)  DEFAULT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_novapods_id` (`novapods_id`),
    KEY `idx_novapods_device` (`paired_device_id`),
    KEY `idx_novapods_owner` (`owner_account_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Wolfden Phone Secure rooms (server/secure_ops.lua, Config.Secure.rooms): a group
-- conversation joined with a short code instead of numbers. The code is valid
-- until `code_expires_at`; the room disappears for everyone when nobody has
-- written in it for Config.Secure.rooms.idleMinutes.
CREATE TABLE IF NOT EXISTS `phone_secure_rooms` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `conversation_id`  VARCHAR(40)  NOT NULL,
    `code`             VARCHAR(16)  DEFAULT NULL,
    `code_expires_at`  TIMESTAMP    NULL DEFAULT NULL,
    `created_by_line`  VARCHAR(40)  NOT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_secure_room_conversation` (`conversation_id`),
    UNIQUE KEY `uq_secure_room_code` (`code`),
    KEY `idx_secure_room_creator` (`created_by_line`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Bluetooth speakers (server/speakers.lua). The item carries `speakerId`;
-- `model` is the item name (Config.Speakers.models). The battery is stored
-- with when it was taken, the rate it charges at and whether it plays
-- (drains). Ownership and pairing work like NovaBuds. A speaker set down has
-- its position (x/y/z/heading), who placed it and the item's metadata (given
-- back when picked up); `allow_others` lets any phone in range play on it.
CREATE TABLE IF NOT EXISTS `phone_speakers` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `speaker_id`       VARCHAR(24)  NOT NULL,
    `model`            VARCHAR(48)  DEFAULT NULL,
    `name`             VARCHAR(48)  DEFAULT NULL,
    `owner_account_id` VARCHAR(24)  DEFAULT NULL,
    `paired_device_id` VARCHAR(32)  DEFAULT NULL,
    `battery`          FLOAT        NOT NULL DEFAULT 100,
    `battery_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `playing`          TINYINT(1)   NOT NULL DEFAULT 0,
    `charge_rate`      FLOAT        NOT NULL DEFAULT 0,
    `volume`           TINYINT UNSIGNED NOT NULL DEFAULT 80,
    `allow_others`     TINYINT(1)   NOT NULL DEFAULT 0,
    `placed_by`        VARCHAR(64)  DEFAULT NULL,
    `x`                FLOAT        DEFAULT NULL,
    `y`                FLOAT        DEFAULT NULL,
    `z`                FLOAT        DEFAULT NULL,
    `heading`          FLOAT        DEFAULT NULL,
    `item_metadata`    LONGTEXT     DEFAULT NULL,
    `last_seen_x`      FLOAT        DEFAULT NULL,
    `last_seen_y`      FLOAT        DEFAULT NULL,
    `last_seen_z`      FLOAT        DEFAULT NULL,
    `last_seen_at`     TIMESTAMP    NULL DEFAULT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_speaker_id` (`speaker_id`),
    KEY `idx_speakers_device` (`paired_device_id`),
    KEY `idx_speakers_owner` (`owner_account_id`),
    KEY `idx_speakers_placed` (`placed_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Music (server/music.lua): songs saved by link, owned like contacts
-- (owner_key: Nova Account, else the phone), and playlists of them.
CREATE TABLE IF NOT EXISTS `phone_music_tracks` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `track_id`    VARCHAR(24)  NOT NULL,
    `owner_key`   VARCHAR(40)  NOT NULL,
    `title`       VARCHAR(80)  NOT NULL,
    `artist`      VARCHAR(60)  DEFAULT NULL,
    `url`         VARCHAR(500) NOT NULL,
    `kind`        VARCHAR(10)  NOT NULL DEFAULT 'link',   -- link | youtube
    `cover_url`   VARCHAR(500) DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_music_track` (`track_id`),
    KEY `idx_music_owner` (`owner_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_music_playlists` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `playlist_id` VARCHAR(24)  NOT NULL,
    `owner_key`   VARCHAR(40)  NOT NULL,
    `title`       VARCHAR(40)  NOT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_music_playlist` (`playlist_id`),
    KEY `idx_music_playlist_owner` (`owner_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Libraries that got Config.Music.defaultSongs (once per owner).
CREATE TABLE IF NOT EXISTS `phone_music_libraries` (
    `owner_key`   VARCHAR(40)  NOT NULL,
    `seeded_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`owner_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_music_playlist_items` (
    `playlist_id` VARCHAR(24)  NOT NULL,
    `track_id`    VARCHAR(24)  NOT NULL,
    `position`    INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`playlist_id`, `track_id`),
    KEY `idx_music_item_track` (`track_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Charging pads placed in the world (Config.Charging.pad). One item sits on
-- a pad at a time: a phone, NovaBuds or a power bank, kept here (item name +
-- its ox_inventory metadata) until someone takes it.
CREATE TABLE IF NOT EXISTS `phone_charging_pads` (
    `pad_id`           VARCHAR(16)  NOT NULL,
    `owner_citizenid`  VARCHAR(64)  NOT NULL,
    `x`                FLOAT        NOT NULL,
    `y`                FLOAT        NOT NULL,
    `z`                FLOAT        NOT NULL,
    `heading`          FLOAT        NOT NULL DEFAULT 0,
    `content_item`     VARCHAR(64)  DEFAULT NULL,
    `content_metadata` MEDIUMTEXT   DEFAULT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`pad_id`),
    KEY `idx_charging_pads_owner` (`owner_citizenid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Power bank charge (server/charging.lua), by the item's signed `bankId`.
-- The item's `charge` only mirrors it, so an old copy of the item never
-- brings charge back.
CREATE TABLE IF NOT EXISTS `phone_power_banks` (
    `bank_id`    VARCHAR(16)   NOT NULL,
    `charge`     DECIMAL(6,3)  NOT NULL DEFAULT 50,
    `updated_at` TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`bank_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Live cell towers (server/towers.lua). Config.Signal.towers seeds rows once
-- (source 'config', INSERT IGNORE); after that this table is authoritative.
-- Deleted towers stay as soft-deleted rows so a config tower never comes
-- back. `carriers` is JSON { carrierId: strength 0..2 }; access_* is the
-- ground cabinet where players sabotage / repair (NULL: not serviceable).
CREATE TABLE IF NOT EXISTS `phone_signal_towers` (
    `id`              VARCHAR(64)  NOT NULL,
    `label`           VARCHAR(48)  NOT NULL,
    `x`               DOUBLE       NOT NULL,
    `y`               DOUBLE       NOT NULL,
    `z`               DOUBLE       NOT NULL,
    `radius`          FLOAT        NOT NULL,
    `carriers`        TEXT         NOT NULL,
    `enabled`         TINYINT(1)   NOT NULL DEFAULT 1,
    `status`          VARCHAR(12)  NOT NULL DEFAULT 'online',   -- online | damaged
    `access_x`        DOUBLE       DEFAULT NULL,
    `access_y`        DOUBLE       DEFAULT NULL,
    `access_z`        DOUBLE       DEFAULT NULL,
    `access_h`        FLOAT        DEFAULT NULL,
    `damaged_at`      TIMESTAMP    NULL DEFAULT NULL,
    `cooldown_until`  TIMESTAMP    NULL DEFAULT NULL,
    `source`          VARCHAR(8)   NOT NULL DEFAULT 'admin',    -- config | admin
    `deleted`         TINYINT(1)   NOT NULL DEFAULT 0,
    `created_by`      VARCHAR(80)  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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- What happened to a tower and who did it (sabotage, repair, admin edits).
-- `actor` is a citizen id, 'admin:<citizenid|console>' or 'system'; never
-- sent to clients.
CREATE TABLE IF NOT EXISTS `phone_signal_tower_events` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tower_id`    VARCHAR(64)  NOT NULL,
    `action`      VARCHAR(24)  NOT NULL,
    `actor`       VARCHAR(80)  DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_tower_events_tower` (`tower_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- News (server/news.lua): newsroom articles (Config.News.outlets). The
-- author is a citizen id (never sent to clients; author_name is the byline).
-- status: draft | published | unpublished. removed_at hides an article for
-- good (deleted by its newsroom, or removed by a moderator: removed_by).
-- cover_media_id keeps its phone_media row alive (MediaServer.purge).
CREATE TABLE IF NOT EXISTS `phone_news_articles` (
    `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `article_id`        VARCHAR(32)  NOT NULL,
    `outlet_id`         VARCHAR(32)  NOT NULL,
    `author_citizenid`  VARCHAR(64)  NOT NULL,
    `author_name`       VARCHAR(64)  NOT NULL,
    `headline`          VARCHAR(160) NOT NULL,
    `subtitle`          VARCHAR(255) DEFAULT NULL,
    `body`              TEXT         NOT NULL,
    `category`          VARCHAR(24)  NOT NULL,
    `cover_media_id`    VARCHAR(48)  DEFAULT NULL,
    `location_label`    VARCHAR(64)  DEFAULT NULL,
    `location_x`        DOUBLE       DEFAULT NULL,
    `location_y`        DOUBLE       DEFAULT NULL,
    `location_z`        DOUBLE       DEFAULT NULL,
    `status`            VARCHAR(12)  NOT NULL DEFAULT 'draft',
    `breaking`          TINYINT(1)   NOT NULL DEFAULT 0,
    `breaking_sent_at`  TIMESTAMP    NULL DEFAULT NULL,
    `view_count`        INT UNSIGNED NOT NULL DEFAULT 0,
    `published_at`      TIMESTAMP    NULL DEFAULT NULL,
    `published_by`      VARCHAR(64)  DEFAULT NULL,
    `removed_at`        TIMESTAMP    NULL DEFAULT NULL,
    `removed_by`        VARCHAR(80)  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_news_article_id` (`article_id`),
    KEY `idx_news_published` (`status`, `published_at`),
    KEY `idx_news_outlet` (`outlet_id`, `status`, `published_at`),
    KEY `idx_news_category` (`category`, `status`, `published_at`),
    KEY `idx_news_author` (`author_citizenid`, `status`),
    KEY `idx_news_breaking` (`outlet_id`, `breaking_sent_at`),
    KEY `idx_news_cover` (`cover_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per article and reader (viewer_key = 'citizen:<citizenid>'), so
-- phone_news_articles.view_count counts each character once.
CREATE TABLE IF NOT EXISTS `phone_news_views` (
    `article_id`  VARCHAR(32)  NOT NULL,
    `viewer_key`  VARCHAR(80)  NOT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`article_id`, `viewer_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Radio app (server/radio.lua): a phone's radio volume / mic clicks and the
-- last frequency it tuned, plus its saved frequencies. Device data: erasing
-- the phone deletes both. Frequencies are pma-voice radio channel numbers.
CREATE TABLE IF NOT EXISTS `phone_radio_settings` (
    `device_id`       VARCHAR(32)   NOT NULL,
    `volume`          TINYINT UNSIGNED NOT NULL DEFAULT 60,
    `mic_clicks`      TINYINT(1)    NOT NULL DEFAULT 1,
    `last_frequency`  DECIMAL(9,3)  DEFAULT NULL,
    `updated_at`      TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_radio_presets` (
    `device_id`   VARCHAR(32)   NOT NULL,
    `frequency`   DECIMAL(9,3)  NOT NULL,
    `label`       VARCHAR(32)   NOT NULL,
    `created_at`  TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `frequency`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arcade (server/arcade.lua): each phone's best per game (device data,
-- erased with the phone). board_* is what the global leaderboard shows; it
-- copies best_score when the phone is online. board_name is the Nova
-- Account name at that time (NULL: anonymous). `best_score` is points, or
-- seconds for timed games (Config.Arcade.games order = 'asc').
CREATE TABLE IF NOT EXISTS `phone_arcade_scores` (
    `device_id`   VARCHAR(32) NOT NULL,
    `game`        VARCHAR(16) NOT NULL,
    `best_score`  INT         NOT NULL,
    `best_at`     TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `board_score` INT         DEFAULT NULL,
    `board_name`  VARCHAR(64) DEFAULT NULL,
    `board_at`    TIMESTAMP   NULL DEFAULT NULL,
    PRIMARY KEY (`device_id`, `game`),
    KEY `idx_arcade_board` (`game`, `board_score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Arcade player preferences per phone (play anonymously).
CREATE TABLE IF NOT EXISTS `phone_arcade_players` (
    `device_id`   VARCHAR(32) NOT NULL,
    `anonymous`   TINYINT(1)  NOT NULL DEFAULT 0,
    `updated_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Shade (anonymous dark-web app, server/shade.lua). A phone's handle is an
-- identity row; taking a new handle retires the old one. device_id /
-- citizen_id are only for bans and are never sent to clients or exposed to
-- subscriber records. Messages (channels and DMs) are deleted after
-- Config.Shade.messageTtlMinutes and listings after listingTtlHours.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_shade_identities` (
    `identity_id` VARCHAR(24)  NOT NULL,
    `handle`      VARCHAR(40)  NOT NULL,
    `device_id`   VARCHAR(32)  NOT NULL,
    `citizen_id`  VARCHAR(64)  DEFAULT NULL,   -- last user; bans only
    `retired_at`  TIMESTAMP    NULL DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`identity_id`),
    UNIQUE KEY `uq_shade_handle` (`handle`),
    KEY `idx_shade_identity_device` (`device_id`, `retired_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Player-made channels (built-in ones live in Config.Shade.channels).
CREATE TABLE IF NOT EXISTS `phone_shade_channels` (
    `channel_id`      VARCHAR(20)  NOT NULL,
    `topic`           VARCHAR(80)  DEFAULT NULL,
    `created_by`      VARCHAR(24)  NOT NULL,
    `last_message_at` TIMESTAMP    NULL DEFAULT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`channel_id`),
    KEY `idx_shade_channel_creator` (`created_by`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One pair of handles per thread (identity_a < identity_b).
CREATE TABLE IF NOT EXISTS `phone_shade_threads` (
    `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `thread_id`       VARCHAR(32)  NOT NULL,
    `identity_a`      VARCHAR(24)  NOT NULL,
    `identity_b`      VARCHAR(24)  NOT NULL,
    `a_read_at`       TIMESTAMP(3) NULL DEFAULT NULL,
    `b_read_at`       TIMESTAMP(3) NULL DEFAULT NULL,
    `last_message_at` TIMESTAMP(3) NULL DEFAULT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_shade_thread_id` (`thread_id`),
    UNIQUE KEY `uq_shade_thread_pair` (`identity_a`, `identity_b`),
    KEY `idx_shade_thread_b` (`identity_b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Channel messages (channel_id), DMs (thread_id) and room messages
-- (room_id). sender_handle is the handle at the time, so a message keeps it
-- after the sender rotates.
CREATE TABLE IF NOT EXISTS `phone_shade_messages` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `message_id`    VARCHAR(32)  NOT NULL,
    `channel_id`    VARCHAR(20)  DEFAULT NULL,
    `thread_id`     VARCHAR(32)  DEFAULT NULL,
    `room_id`       VARCHAR(32)  DEFAULT NULL,
    `sender_id`     VARCHAR(24)  NOT NULL,
    `sender_handle` VARCHAR(40)  NOT NULL,
    `body`          VARCHAR(400) NOT NULL,
    `created_at`    TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_shade_message_id` (`message_id`),
    KEY `idx_shade_messages_channel` (`channel_id`, `id`),
    KEY `idx_shade_messages_thread` (`thread_id`, `id`),
    KEY `idx_shade_messages_room` (`room_id`, `id`),
    KEY `idx_shade_messages_sender` (`sender_id`),
    KEY `idx_shade_messages_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_shade_listings` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `listing_id`    VARCHAR(32)  NOT NULL,
    `identity_id`   VARCHAR(24)  NOT NULL,
    `handle`        VARCHAR(40)  NOT NULL,
    `title`         VARCHAR(60)  NOT NULL,
    `price`         VARCHAR(24)  DEFAULT NULL,
    `body`          VARCHAR(300) DEFAULT NULL,
    `media_id`      VARCHAR(48)  DEFAULT NULL,
    `expires_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_shade_listing_id` (`listing_id`),
    KEY `idx_shade_listings_identity` (`identity_id`),
    KEY `idx_shade_listings_expiry` (`expires_at`),
    KEY `idx_shade_listings_media` (`media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Handles a phone has blocked (device-owned, so they survive its rotation).
CREATE TABLE IF NOT EXISTS `phone_shade_blocks` (
    `device_id`   VARCHAR(32) NOT NULL,
    `identity_id` VARCHAR(24) NOT NULL,
    `handle`      VARCHAR(40) NOT NULL,
    `created_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `identity_id`),
    KEY `idx_shade_blocks_identity` (`identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Moderator bans: they hit the phone and the character, whatever handle
-- they take next. expires_at NULL = permanent.
CREATE TABLE IF NOT EXISTS `phone_shade_bans` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `identity_id` VARCHAR(24)  NOT NULL,
    `handle`      VARCHAR(40)  NOT NULL,
    `device_id`   VARCHAR(32)  NOT NULL,
    `citizen_id`  VARCHAR(64)  DEFAULT NULL,
    `reason`      VARCHAR(120) DEFAULT NULL,
    `banned_by`   VARCHAR(80)  DEFAULT NULL,
    `expires_at`  TIMESTAMP    NULL DEFAULT NULL,
    `lifted_at`   TIMESTAMP    NULL DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_shade_bans_device` (`device_id`),
    KEY `idx_shade_bans_citizen` (`citizen_id`),
    KEY `idx_shade_bans_identity` (`identity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Rooms (server/shade_rooms.lua): `private` (invite_code) or `hidden` (name +
-- pass_hash, bcrypt). Owned by a phone (owner_device), never a handle, so
-- they survive handle rotation; never sent to clients. key_id is the id on
-- the room's physical keys (signed item metadata). drop_* is a dead drop set
-- from the owner's ped; crew_* ties it to a live job / gang.
CREATE TABLE IF NOT EXISTS `phone_shade_rooms` (
    `room_id`         VARCHAR(32)  NOT NULL,
    `kind`            VARCHAR(8)   NOT NULL,
    `name`            VARCHAR(20)  NOT NULL,
    `topic`           VARCHAR(80)  DEFAULT NULL,
    `owner_device`    VARCHAR(32)  NOT NULL,
    `invite_code`     VARCHAR(9)   DEFAULT NULL,
    `pass_hash`       VARCHAR(255) DEFAULT NULL,
    `key_id`          VARCHAR(32)  DEFAULT NULL,
    `key_burn`        TINYINT(1)   NOT NULL DEFAULT 0,
    `ttl_minutes`     INT UNSIGNED NOT NULL DEFAULT 240,
    `drop_x`          DOUBLE       DEFAULT NULL,
    `drop_y`          DOUBLE       DEFAULT NULL,
    `drop_z`          DOUBLE       DEFAULT NULL,
    `drop_radius`     FLOAT        DEFAULT NULL,
    `crew_kind`       VARCHAR(4)   DEFAULT NULL,
    `crew_name`       VARCHAR(50)  DEFAULT NULL,
    `last_message_at` TIMESTAMP    NULL DEFAULT NULL,
    `created_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`room_id`),
    UNIQUE KEY `uq_shade_room_code` (`invite_code`),
    UNIQUE KEY `uq_shade_room_key` (`key_id`),
    KEY `idx_shade_room_name` (`kind`, `name`),
    KEY `idx_shade_room_owner` (`owner_device`),
    KEY `idx_shade_room_crew` (`crew_kind`, `crew_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Room members are phones (via: owner | code | pass | key). A kicked phone
-- keeps its row (kicked = 1) so it stays out.
CREATE TABLE IF NOT EXISTS `phone_shade_room_members` (
    `room_id`    VARCHAR(32) NOT NULL,
    `device_id`  VARCHAR(32) NOT NULL,
    `via`        VARCHAR(8)  NOT NULL,
    `kicked`     TINYINT(1)  NOT NULL DEFAULT 0,
    `joined_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`room_id`, `device_id`),
    KEY `idx_shade_room_member_device` (`device_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- ---------------------------------------------------------------------
-- Rides (player ride-hailing). Riders and drivers are citizen ids (the
-- framework character); names are the short "First L." shown to the other
-- side. Money columns are whole currency units; fare is what the rider paid
-- (or owes while payment_status = 'unpaid'). driver_stars are the stars the
-- rider gave the driver, rider_stars the driver's for the rider.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_rides` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ride_id`          VARCHAR(24)  NOT NULL,
    `status`           VARCHAR(16)  NOT NULL DEFAULT 'requested',
    `rider_citizenid`  VARCHAR(64)  NOT NULL,
    `rider_name`       VARCHAR(40)  DEFAULT NULL,
    `driver_citizenid` VARCHAR(64)  DEFAULT NULL,
    `driver_name`      VARCHAR(40)  DEFAULT NULL,
    `pickup_x`         DOUBLE       NOT NULL,
    `pickup_y`         DOUBLE       NOT NULL,
    `pickup_z`         DOUBLE       NOT NULL DEFAULT 0,
    `pickup_label`     VARCHAR(80)  DEFAULT NULL,
    `dest_x`           DOUBLE       NOT NULL,
    `dest_y`           DOUBLE       NOT NULL,
    `dest_label`       VARCHAR(80)  DEFAULT NULL,
    `estimate_meters`  INT UNSIGNED NOT NULL DEFAULT 0,
    `estimate_fare`    INT UNSIGNED NOT NULL DEFAULT 0,
    `travelled_meters` INT UNSIGNED DEFAULT NULL,
    `fare`             INT UNSIGNED DEFAULT NULL,
    `platform_fee`     INT UNSIGNED DEFAULT NULL,
    `driver_payout`    INT UNSIGNED DEFAULT NULL,
    `cancel_fee`       INT UNSIGNED DEFAULT NULL,
    `cancelled_by`     VARCHAR(8)   DEFAULT NULL,
    `cancel_reason`    VARCHAR(24)  DEFAULT NULL,
    `payment_status`   VARCHAR(8)   NOT NULL DEFAULT 'none',
    -- The platform fee: none | charging | taken | waived. Set to 'charging'
    -- before the charge, so a retry after a crash never takes it twice.
    `fee_status`       VARCHAR(8)   NOT NULL DEFAULT 'none',
    `transaction_id`   VARCHAR(40)  DEFAULT NULL,
    `vehicle_label`    VARCHAR(32)  DEFAULT NULL,
    `vehicle_plate`    VARCHAR(12)  DEFAULT NULL,
    `driver_stars`     TINYINT UNSIGNED DEFAULT NULL,
    `rider_stars`      TINYINT UNSIGNED DEFAULT NULL,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `accepted_at`      TIMESTAMP    NULL DEFAULT NULL,
    `arrived_at`       TIMESTAMP    NULL DEFAULT NULL,
    `started_at`       TIMESTAMP    NULL DEFAULT NULL,
    `completed_at`     TIMESTAMP    NULL DEFAULT NULL,
    `cancelled_at`     TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_rides_ride_id` (`ride_id`),
    KEY `idx_rides_rider` (`rider_citizenid`, `id`),
    KEY `idx_rides_driver` (`driver_citizenid`, `id`),
    KEY `idx_rides_status` (`status`),
    KEY `idx_rides_payment` (`payment_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Every status change of a trip and who made it ('rider', 'driver', 'system').
CREATE TABLE IF NOT EXISTS `phone_ride_events` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ride_id`     VARCHAR(24) NOT NULL,
    `from_status` VARCHAR(16) NOT NULL DEFAULT '',
    `to_status`   VARCHAR(16) NOT NULL,
    `actor_type`  VARCHAR(8)  NOT NULL,
    `actor_ident` VARCHAR(64) NOT NULL DEFAULT '',
    `created_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ride_events_ride` (`ride_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Homes: the numbers keys were given to from the phone (server/homes.lua).
-- The owner's keyholder list shows one row per number (or their contact
-- name for it), never a character name; `phone_number` '' keeps the
-- generic row of a key given outside the phone once that holder also got
-- one by number. Properties and keys stay the housing resource's; rows go
-- when their key is taken back or no longer listed.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_home_keys` (
    `owner_citizenid`  VARCHAR(64) NOT NULL,
    `home_id`          VARCHAR(64) NOT NULL,
    `holder_citizenid` VARCHAR(64) NOT NULL,
    `phone_number`     VARCHAR(32) NOT NULL,
    `created_at`       TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`owner_citizenid`, `home_id`, `holder_citizenid`, `phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Clips (short vertical video). Identity columns (*_id / account_id) hold
-- the Clips account id (phone_accounts, service 'clips'). A clip is one
-- gallery video (phone_media.kind = 'video'); counters are denormalized.
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `phone_clips_profiles` (
    `account_id`       VARCHAR(24)  NOT NULL,
    `handle`           VARCHAR(24)  NOT NULL,
    `display_name`     VARCHAR(40)  NOT NULL,
    `bio`              VARCHAR(160) DEFAULT NULL,
    `avatar_media_id`  VARCHAR(48)  DEFAULT NULL,
    `is_private`       TINYINT(1)   NOT NULL DEFAULT 0,
    `verified`         TINYINT(1)   NOT NULL DEFAULT 0,
    `verified_revoked` TINYINT(1)   NOT NULL DEFAULT 0,  -- badge taken away / cleared on rename: the auto sweep skips it
    `activity_seen_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`account_id`),
    UNIQUE KEY `uq_clips_handle` (`handle`),
    KEY `idx_clips_profiles_avatar` (`avatar_media_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_follows` (
    `follower_id`  VARCHAR(24) NOT NULL,
    `following_id` VARCHAR(24) NOT NULL,
    `status`       VARCHAR(12) NOT NULL DEFAULT 'accepted',
    `created_at`   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`follower_id`, `following_id`),
    KEY `idx_clips_following` (`following_id`, `status`, `created_at`),
    KEY `idx_clips_follows_time` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_blocks` (
    `blocker_id` VARCHAR(24) NOT NULL,
    `blocked_id` VARCHAR(24) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`blocker_id`, `blocked_id`),
    KEY `idx_clips_blocked` (`blocked_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- removed_by: the moderator (citizen id / resource) who took it down.
CREATE TABLE IF NOT EXISTS `phone_clips` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `clip_id`       VARCHAR(32)  NOT NULL,
    `author_id`     VARCHAR(24)  NOT NULL,
    `media_id`      VARCHAR(48)  NOT NULL,
    `caption`       VARCHAR(300) DEFAULT NULL,
    `like_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `comment_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `share_count`   INT UNSIGNED NOT NULL DEFAULT 0,
    `view_count`    INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at`    TIMESTAMP    NULL DEFAULT NULL,
    `removed_by`    VARCHAR(64)  DEFAULT NULL,
    `author_citizenid` VARCHAR(64) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_clips_clip_id` (`clip_id`),
    KEY `idx_clips_author` (`author_id`, `deleted_at`, `created_at`),
    KEY `idx_clips_recent` (`deleted_at`, `created_at`),
    KEY `idx_clips_media` (`media_id`),
    KEY `idx_clips_author_citizen` (`author_citizenid`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_tags` (
    `clip_id` VARCHAR(32) NOT NULL,
    `tag`     VARCHAR(30) NOT NULL,
    PRIMARY KEY (`clip_id`, `tag`),
    KEY `idx_clips_tags_tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_likes` (
    `clip_id`    VARCHAR(32) NOT NULL,
    `account_id` VARCHAR(24) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`clip_id`, `account_id`),
    KEY `idx_clips_likes_account` (`account_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_comments` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `comment_id` VARCHAR(32)  NOT NULL,
    `clip_id`    VARCHAR(32)  NOT NULL,
    `author_id`  VARCHAR(24)  NOT NULL,
    `body`       VARCHAR(300) NOT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `deleted_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_clips_comment_id` (`comment_id`),
    KEY `idx_clips_comments_clip` (`clip_id`, `deleted_at`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per viewer and clip: view_count counts each account once.
CREATE TABLE IF NOT EXISTS `phone_clips_views` (
    `clip_id`   VARCHAR(32) NOT NULL,
    `viewer_id` VARCHAR(24) NOT NULL,
    `viewed_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`clip_id`, `viewer_id`),
    KEY `idx_clips_views_viewer` (`viewer_id`),
    KEY `idx_clips_views_clip_time` (`clip_id`, `viewed_at`),
    KEY `idx_clips_views_time` (`viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Likes, comments, mentions and follows shown in the Inbox screen.
CREATE TABLE IF NOT EXISTS `phone_clips_activity` (
    `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `recipient_id` VARCHAR(24)  NOT NULL,
    `actor_id`     VARCHAR(24)  NOT NULL,
    `kind`         VARCHAR(16)  NOT NULL,
    `clip_id`      VARCHAR(32)  DEFAULT NULL,
    `detail`       VARCHAR(120) DEFAULT NULL,
    `created_at`   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_clips_activity_recipient` (`recipient_id`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One report per reporter and target; moderators resolve them.
CREATE TABLE IF NOT EXISTS `phone_clips_reports` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `report_id`   VARCHAR(32)  NOT NULL,
    `reporter_id` VARCHAR(24)  NOT NULL,
    `target_type` VARCHAR(12)  NOT NULL,
    `target_id`   VARCHAR(32)  NOT NULL,
    `reason`      VARCHAR(24)  NOT NULL,
    `details`     VARCHAR(200) DEFAULT NULL,
    `status`      VARCHAR(12)  NOT NULL DEFAULT 'open',
    `resolution`  VARCHAR(16)  DEFAULT NULL,
    `resolved_by` VARCHAR(64)  DEFAULT NULL,
    `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_clips_report` (`reporter_id`, `target_type`, `target_id`),
    KEY `idx_clips_reports_status` (`status`, `id`),
    KEY `idx_clips_reports_target` (`target_type`, `target_id`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Clips Live gifts (server/clips_live.lua): a viewer's Wallet pays the
-- broadcaster's character. The row is written ('charging') before the
-- charge; status: charging | paid | failed | refund_due | refunded. payout
-- is what the broadcaster got (amount minus Config.Creators.gifts
-- platformFeePercent). Citizen ids never reach the NUI.
CREATE TABLE IF NOT EXISTS `phone_clips_gifts` (
    `gift_id`           VARCHAR(32)  NOT NULL,
    `stream_id`         VARCHAR(32)  NOT NULL,
    `gift_kind`         VARCHAR(24)  NOT NULL,
    `host_account_id`   VARCHAR(24)  NOT NULL,
    `sender_account_id` VARCHAR(24)  NOT NULL,
    `host_citizenid`    VARCHAR(64)  NOT NULL,
    `sender_citizenid`  VARCHAR(64)  NOT NULL,
    `amount`            INT UNSIGNED NOT NULL,
    `payout`            INT UNSIGNED NOT NULL DEFAULT 0,
    `status`            VARCHAR(12)  NOT NULL DEFAULT 'charging',
    `tried_at`          TIMESTAMP    NULL DEFAULT NULL,              -- last reconcile / refund attempt (sweep order)
    `created_at`        TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`gift_id`),
    KEY `idx_clips_gifts_sender` (`sender_citizenid`, `created_at`),
    KEY `idx_clips_gifts_host` (`host_account_id`, `status`, `created_at`),
    KEY `idx_clips_gifts_retry` (`status`, `tried_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Brand deals (server/clips_deals.lua): a Business pays a Clips creator to
-- post a clip. The money leaves funding_account_id when the offer is made
-- and is held (money_status) until it is paid out or refunded; status and
-- money_status only move by conditional UPDATEs. creator_citizenid is the
-- character that accepted (paid on approval); it never reaches the NUI.
CREATE TABLE IF NOT EXISTS `phone_clips_deals` (
    `deal_id`               VARCHAR(32)  NOT NULL,
    `business_id`           VARCHAR(40)  NOT NULL,
    `business_label`        VARCHAR(60)  NOT NULL,
    `funding_account_id`    VARCHAR(40)  NOT NULL,
    `created_by_citizenid`  VARCHAR(64)  NOT NULL,
    `creator_account_id`    VARCHAR(24)  NOT NULL,
    `creator_citizenid`     VARCHAR(64)  DEFAULT NULL,
    `brief`                 VARCHAR(400) NOT NULL,
    `amount`                INT UNSIGNED NOT NULL,
    `due_days`              SMALLINT UNSIGNED NOT NULL,
    `status`                VARCHAR(12)  NOT NULL DEFAULT 'offered',  -- Clips.DealStatus
    `money_status`          VARCHAR(12)  NOT NULL DEFAULT 'funding',  -- funding | held | paid_out | refunded | payout_due | refund_due | failed
    `clip_id`               VARCHAR(32)  DEFAULT NULL,
    `feedback`              VARCHAR(200) DEFAULT NULL,
    `revisions`             TINYINT UNSIGNED NOT NULL DEFAULT 0,      -- "Request changes" used (Config.Creators.deals.maxRevisions)
    `respond_by`            TIMESTAMP    NULL DEFAULT NULL,
    `due_at`                TIMESTAMP    NULL DEFAULT NULL,
    `review_by`             TIMESTAMP    NULL DEFAULT NULL,
    `accepted_at`           TIMESTAMP    NULL DEFAULT NULL,
    `submitted_at`          TIMESTAMP    NULL DEFAULT NULL,        -- last submission; kept when sent back (NULL = never submitted)
    `resolved_at`           TIMESTAMP    NULL DEFAULT NULL,
    `tried_at`              TIMESTAMP    NULL DEFAULT NULL,        -- last payout / refund attempt (sweep order)
    `created_at`            TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`            TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`deal_id`),
    KEY `idx_clips_deals_creator` (`creator_account_id`, `status`),
    KEY `idx_clips_deals_business` (`business_id`, `status`),
    KEY `idx_clips_deals_clip` (`clip_id`),
    KEY `idx_clips_deals_sweep` (`status`, `respond_by`),
    KEY `idx_clips_deals_retry` (`money_status`, `tried_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Creator fund (server/clips_fund.lua, Config.Creators.fund): a view counts
-- once per character per clip (never the poster's own); weeks are ISO weeks
-- ('YYYY-Www', Monday 00:00 server time). A finished week makes one payout
-- row per poster (pending -> paid, or below_min) before the Wallet credit
-- (key clipsfund:<week>:<id>); phone_clips_fund_weeks marks a week whose
-- posters all have a row. Citizen ids never reach the NUI.
CREATE TABLE IF NOT EXISTS `phone_clips_fund_views` (
    `clip_id`          VARCHAR(32) NOT NULL,
    `viewer_citizenid` VARCHAR(64) NOT NULL,
    `author_citizenid` VARCHAR(64) NOT NULL,
    `viewed_at`        TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`clip_id`, `viewer_citizenid`),
    KEY `idx_clips_fund_views_author` (`author_citizenid`, `viewed_at`),
    KEY `idx_clips_fund_views_time` (`viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_fund_payouts` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `week_key`   VARCHAR(8)   NOT NULL,
    `citizenid`  VARCHAR(64)  NOT NULL,
    `views`      INT UNSIGNED NOT NULL,
    `amount`     INT UNSIGNED NOT NULL DEFAULT 0,
    `status`     VARCHAR(12)  NOT NULL DEFAULT 'pending',  -- pending | paid | below_min
    `paid_at`    TIMESTAMP    NULL DEFAULT NULL,
    `tried_at`   TIMESTAMP    NULL DEFAULT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_clips_fund_payout` (`week_key`, `citizenid`),
    KEY `idx_clips_fund_payouts_citizen` (`citizenid`, `week_key`),
    KEY `idx_clips_fund_payouts_status` (`status`, `tried_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_clips_fund_weeks` (
    `week_key`   VARCHAR(8) NOT NULL,
    `settled_at` TIMESTAMP  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`week_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Creator analytics (server/clips_analytics.lua). Follows are deleted on
-- unfollow, so follower history is a snapshot per account and day, written
-- whenever the count changes (a missing day kept the previous count). `day`
-- is 'YYYY-MM-DD' text from the database clock (never a DATE column).
CREATE TABLE IF NOT EXISTS `phone_clips_follower_days` (
    `account_id` VARCHAR(24)  NOT NULL,
    `day`        VARCHAR(10)  NOT NULL,
    `followers`  INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`account_id`, `day`),
    KEY `idx_clips_follower_days_day` (`day`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One row per share to Messages (share_count is the lifetime counter);
-- pruned after Config.Clips.analytics.retentionDays.
CREATE TABLE IF NOT EXISTS `phone_clips_shares` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `clip_id`    VARCHAR(32) NOT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_clips_shares_clip` (`clip_id`, `created_at`),
    KEY `idx_clips_shares_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================================
-- Racing (server/racing*.lua). Identity is the character: citizen ids stay
-- server-side; payloads carry only racer_id + alias. Timestamps are
-- TIMESTAMP; read them with UNIX_TIMESTAMP(col), write FROM_UNIXTIME(?).
-- =====================================================================

-- One racing profile per character. license_hash = HMAC of the player's
-- game licence (server secret 'racing_license'): bet conflicts and same-
-- person rating exclusion. Never sent to the NUI.
CREATE TABLE IF NOT EXISTS `phone_racing_profiles` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `racer_id`         VARCHAR(16)  NOT NULL,
    `citizenid`        VARCHAR(64)  NOT NULL,
    `alias`            VARCHAR(20)  NOT NULL,
    `alias_key`        VARCHAR(20)  NOT NULL,
    `license_hash`     CHAR(64)     DEFAULT NULL,
    `rating`           INT          NOT NULL DEFAULT 1000,
    `rated_races`      INT UNSIGNED NOT NULL DEFAULT 0,
    `races`            INT UNSIGNED NOT NULL DEFAULT 0,
    `wins`             INT UNSIGNED NOT NULL DEFAULT 0,
    `podiums`          INT UNSIGNED NOT NULL DEFAULT 0,
    `dnfs`             INT UNSIGNED NOT NULL DEFAULT 0,
    `earnings`         BIGINT       NOT NULL DEFAULT 0,
    `crew_id`          VARCHAR(16)  DEFAULT NULL,
    `alias_changed_at` TIMESTAMP    NULL DEFAULT NULL,
    `crew_left_at`     TIMESTAMP    NULL DEFAULT NULL,
    `flags`            INT UNSIGNED NOT NULL DEFAULT 0,
    `banned_until`     TIMESTAMP    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_racing_profiles_racer` (`racer_id`),
    UNIQUE KEY `uq_racing_profiles_citizen` (`citizenid`),
    UNIQUE KEY `uq_racing_profiles_alias` (`alias_key`),
    KEY `idx_racing_profiles_license` (`license_hash`),
    KEY `idx_racing_profiles_crew` (`crew_id`),
    KEY `idx_racing_profiles_rating` (`rating`, `rated_races`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Tracks. checkpoints_json = [{x,y,z|null,r}]; map-placed tracks start
-- verified = 0 (z unknown) until the owner's time trial stamps them.
CREATE TABLE IF NOT EXISTS `phone_racing_tracks` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `track_id`         VARCHAR(16)  NOT NULL,
    `owner_racer_id`   VARCHAR(16)  NOT NULL,
    `name`             VARCHAR(40)  NOT NULL,
    `description`      VARCHAR(160) NOT NULL DEFAULT '',
    `kind`             VARCHAR(8)   NOT NULL DEFAULT 'sprint',
    `default_laps`     TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `class`            VARCHAR(8)   NOT NULL DEFAULT 'any',
    `radius`           FLOAT        NOT NULL DEFAULT 12,
    `checkpoints_json` MEDIUMTEXT   NOT NULL,
    `checkpoint_count` SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    `length_m`         INT UNSIGNED NOT NULL DEFAULT 0,
    `verified`         TINYINT(1)   NOT NULL DEFAULT 0,
    `visibility`       VARCHAR(8)   NOT NULL DEFAULT 'private',
    `share_code`       VARCHAR(12)  NOT NULL,
    `start_x`          DOUBLE       NOT NULL DEFAULT 0,
    `start_y`          DOUBLE       NOT NULL DEFAULT 0,
    `start_street`     VARCHAR(64)  DEFAULT NULL,
    `times_raced`      INT UNSIGNED NOT NULL DEFAULT 0,
    `deleted_at`       TIMESTAMP    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_racing_tracks_track` (`track_id`),
    UNIQUE KEY `uq_racing_tracks_code` (`share_code`),
    KEY `idx_racing_tracks_owner` (`owner_racer_id`),
    KEY `idx_racing_tracks_public` (`visibility`, `deleted_at`, `times_raced`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Races (lobbies). track_json snapshots the checkpoints at creation.
CREATE TABLE IF NOT EXISTS `phone_racing_races` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `race_id`        VARCHAR(16)  NOT NULL,
    `track_id`       VARCHAR(16)  NOT NULL,
    `track_name`     VARCHAR(40)  NOT NULL DEFAULT '',
    `host_racer_id`  VARCHAR(16)  NOT NULL,
    `status`         VARCHAR(12)  NOT NULL DEFAULT 'open',
    `mode`           VARCHAR(12)  NOT NULL DEFAULT 'standard',
    `visibility`     VARCHAR(8)   NOT NULL DEFAULT 'public',
    `invite_code`    VARCHAR(8)   NOT NULL,
    `underground`    TINYINT(1)   NOT NULL DEFAULT 0,
    `crew_a`         VARCHAR(16)  DEFAULT NULL,
    `crew_b`         VARCHAR(16)  DEFAULT NULL,
    `class`          VARCHAR(8)   NOT NULL DEFAULT 'any',
    `laps`           TINYINT UNSIGNED NOT NULL DEFAULT 1,
    `buy_in`         INT UNSIGNED NOT NULL DEFAULT 0,
    `max_racers`     TINYINT UNSIGNED NOT NULL DEFAULT 8,
    `seats_taken`    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `rated`          TINYINT(1)   NOT NULL DEFAULT 0,
    `bets_enabled`   TINYINT(1)   NOT NULL DEFAULT 0,
    `pink_slips`     TINYINT(1)   NOT NULL DEFAULT 0,
    `fee_percent`    TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `pool`           INT UNSIGNED NOT NULL DEFAULT 0,
    `fee`            INT UNSIGNED NOT NULL DEFAULT 0,
    `fee_status`     VARCHAR(8)   NOT NULL DEFAULT 'none',
    `track_json`     MEDIUMTEXT   NOT NULL,
    `start_x`        DOUBLE       NOT NULL DEFAULT 0,
    `start_y`        DOUBLE       NOT NULL DEFAULT 0,
    `start_z`        DOUBLE       DEFAULT NULL,
    `start_street`   VARCHAR(64)  DEFAULT NULL,
    `area_x`         DOUBLE       DEFAULT NULL,
    `area_y`         DOUBLE       DEFAULT NULL,
    `start_at`       TIMESTAMP    NULL DEFAULT NULL,
    `started_at`     TIMESTAMP    NULL DEFAULT NULL,
    `finished_at`    TIMESTAMP    NULL DEFAULT NULL,
    `cancelled_at`   TIMESTAMP    NULL DEFAULT NULL,
    `cancel_reason`  VARCHAR(24)  DEFAULT NULL,
    `heat_alerted`   TINYINT(1)   NOT NULL DEFAULT 0,
    `created_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_races_race` (`race_id`),
    UNIQUE KEY `uq_racing_races_code` (`invite_code`),
    KEY `idx_racing_races_status` (`status`, `start_at`),
    KEY `idx_racing_races_track` (`track_id`),
    KEY `idx_racing_races_host` (`host_racer_id`, `status`),
    KEY `idx_racing_races_fee` (`fee_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Entries. id is the entryId used in money keys. live_flag is 1 while the
-- entry is active and NULL once left / kicked, so a racer can rejoin.
CREATE TABLE IF NOT EXISTS `phone_racing_entries` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `race_id`        VARCHAR(16)  NOT NULL,
    `racer_id`       VARCHAR(16)  NOT NULL,
    `citizenid`      VARCHAR(64)  NOT NULL,
    `alias`          VARCHAR(20)  NOT NULL,
    `crew_id`        VARCHAR(16)  DEFAULT NULL,
    `status`         VARCHAR(10)  NOT NULL DEFAULT 'joined',
    `live_flag`      TINYINT UNSIGNED DEFAULT 1,
    `stake`          INT UNSIGNED NOT NULL DEFAULT 0,
    `stake_status`   VARCHAR(12)  NOT NULL DEFAULT 'none',
    `kept`           INT UNSIGNED NOT NULL DEFAULT 0,
    `payout`         INT UNSIGNED NOT NULL DEFAULT 0,
    `payout_status`  VARCHAR(8)   NOT NULL DEFAULT 'none',
    `position`       TINYINT UNSIGNED DEFAULT NULL,
    `total_ms`       INT UNSIGNED DEFAULT NULL,
    `best_lap_ms`    INT UNSIGNED DEFAULT NULL,
    `laps_json`      TEXT         DEFAULT NULL,
    `penalty_ms`     INT UNSIGNED NOT NULL DEFAULT 0,
    `dnf_reason`     VARCHAR(16)  DEFAULT NULL,
    `rating_before`  INT          DEFAULT NULL,
    `rating_after`   INT          DEFAULT NULL,
    `points`         SMALLINT UNSIGNED DEFAULT NULL,
    `vehicle_model`  VARCHAR(32)  DEFAULT NULL,
    `vehicle_label`  VARCHAR(32)  DEFAULT NULL,
    `vehicle_plate`  VARCHAR(12)  DEFAULT NULL,
    `vehicle_class`  VARCHAR(8)   DEFAULT NULL,
    `joined_at`      TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `finished_at`    TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_entries_live` (`race_id`, `racer_id`, `live_flag`),
    KEY `idx_racing_entries_race` (`race_id`, `status`),
    KEY `idx_racing_entries_racer` (`racer_id`, `id`),
    KEY `idx_racing_entries_stake` (`stake_status`),
    KEY `idx_racing_entries_payout` (`payout_status`),
    KEY `idx_racing_entries_kept` (`kept`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Spectator bets (pari-mutuel). Row written 'charging' before the charge.
CREATE TABLE IF NOT EXISTS `phone_racing_bets` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `bet_id`           VARCHAR(16)  NOT NULL,
    `race_id`          VARCHAR(16)  NOT NULL,
    `entry_id`         BIGINT UNSIGNED NOT NULL,
    `bettor_citizenid` VARCHAR(64)  NOT NULL,
    `license_hash`     CHAR(64)     DEFAULT NULL,
    `amount`           INT UNSIGNED NOT NULL,
    `status`           VARCHAR(12)  NOT NULL DEFAULT 'charging',
    `payout`           INT UNSIGNED NOT NULL DEFAULT 0,
    `payout_status`    VARCHAR(8)   NOT NULL DEFAULT 'none',
    `created_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `settled_at`       TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_bets_bet` (`bet_id`),
    KEY `idx_racing_bets_race` (`race_id`, `status`),
    KEY `idx_racing_bets_bettor` (`bettor_citizenid`, `id`),
    KEY `idx_racing_bets_status` (`status`),
    KEY `idx_racing_bets_payout` (`payout_status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Personal bests per track / class / lap count (laps = 0: best single lap).
CREATE TABLE IF NOT EXISTS `phone_racing_track_bests` (
    `track_id`   VARCHAR(16)  NOT NULL,
    `class`      VARCHAR(8)   NOT NULL,
    `laps`       TINYINT UNSIGNED NOT NULL,
    `racer_id`   VARCHAR(16)  NOT NULL,
    `best_ms`    INT UNSIGNED NOT NULL,
    `race_id`    VARCHAR(16)  DEFAULT NULL,
    `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`track_id`, `class`, `laps`, `racer_id`),
    KEY `idx_racing_bests_board` (`track_id`, `class`, `laps`, `best_ms`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Weekly points (ISO week key 'YYYY-Www'; class '*' = every tier).
CREATE TABLE IF NOT EXISTS `phone_racing_weekly` (
    `week_key` CHAR(8)      NOT NULL,
    `class`    VARCHAR(8)   NOT NULL,
    `racer_id` VARCHAR(16)  NOT NULL,
    `points`   INT UNSIGNED NOT NULL DEFAULT 0,
    `races`    INT UNSIGNED NOT NULL DEFAULT 0,
    `wins`     INT UNSIGNED NOT NULL DEFAULT 0,
    `podiums`  INT UNSIGNED NOT NULL DEFAULT 0,
    PRIMARY KEY (`week_key`, `class`, `racer_id`),
    KEY `idx_racing_weekly_board` (`week_key`, `class`, `points`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_racing_crews` (
    `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `crew_id`        VARCHAR(16)  NOT NULL,
    `name`           VARCHAR(24)  NOT NULL,
    `name_key`       VARCHAR(24)  NOT NULL,
    `tag`            VARCHAR(4)   NOT NULL,
    `tag_key`        VARCHAR(4)   NOT NULL,
    `color`          CHAR(7)      NOT NULL DEFAULT '#e11d48',
    `owner_racer_id` VARCHAR(16)  NOT NULL,
    `invite_code`    VARCHAR(8)   NOT NULL,
    `rating`         INT          NOT NULL DEFAULT 1000,
    `races`          INT UNSIGNED NOT NULL DEFAULT 0,
    `wins`           INT UNSIGNED NOT NULL DEFAULT 0,
    `member_count`   INT UNSIGNED NOT NULL DEFAULT 0,
    `created_at`     TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `disbanded_at`   TIMESTAMP    NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_crews_crew` (`crew_id`),
    UNIQUE KEY `uq_racing_crews_name` (`name_key`),
    UNIQUE KEY `uq_racing_crews_tag` (`tag_key`),
    UNIQUE KEY `uq_racing_crews_code` (`invite_code`),
    KEY `idx_racing_crews_rating` (`disbanded_at`, `rating`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- One crew per racer.
CREATE TABLE IF NOT EXISTS `phone_racing_crew_members` (
    `racer_id`  VARCHAR(16) NOT NULL,
    `crew_id`   VARCHAR(16) NOT NULL,
    `role`      VARCHAR(8)  NOT NULL DEFAULT 'member',
    `joined_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`racer_id`),
    KEY `idx_racing_crew_members_crew` (`crew_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `phone_racing_crew_invites` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `crew_id`    VARCHAR(16) NOT NULL,
    `racer_id`   VARCHAR(16) NOT NULL,
    `invited_by` VARCHAR(16) NOT NULL,
    `expires_at` TIMESTAMP   NULL DEFAULT NULL,
    `created_at` TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_crew_invites` (`crew_id`, `racer_id`),
    KEY `idx_racing_crew_invites_racer` (`racer_id`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pink-slip stakes (vehicles), transferred through the garage adapter.
CREATE TABLE IF NOT EXISTS `phone_racing_pinkslips` (
    `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `race_id`          VARCHAR(16)  NOT NULL,
    `entry_id`         BIGINT UNSIGNED NOT NULL,
    `citizenid`        VARCHAR(64)  NOT NULL,
    `plate`            VARCHAR(12)  NOT NULL,
    `vehicle_ref`      VARCHAR(64)  DEFAULT NULL,
    `label`            VARCHAR(48)  DEFAULT NULL,
    `status`           VARCHAR(12)  NOT NULL DEFAULT 'staked',
    `winner_citizenid` VARCHAR(64)  DEFAULT NULL,
    `attempts`         TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `updated_at`       TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uq_racing_pinkslips_entry` (`race_id`, `entry_id`),
    KEY `idx_racing_pinkslips_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Anti-cheat flags (racingCheat).
CREATE TABLE IF NOT EXISTS `phone_racing_flags` (
    `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `race_id`    VARCHAR(16)  DEFAULT NULL,
    `racer_id`   VARCHAR(16)  NOT NULL,
    `kind`       VARCHAR(16)  NOT NULL,
    `detail`     VARCHAR(160) DEFAULT NULL,
    `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_racing_flags_racer` (`racer_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Stock performance per vehicle model (signed joaat), confirmed once two
-- different players' answers agree (Config.Racing.perf.tolerance).
CREATE TABLE IF NOT EXISTS `phone_racing_model_perf` (
    `model`      INT          NOT NULL,
    `score`      FLOAT        NOT NULL DEFAULT 0,
    `tier`       VARCHAR(4)   NOT NULL,
    `stats_json` TEXT         NOT NULL,
    `confirmed`  TINYINT(1)   NOT NULL DEFAULT 0,
    `answers`    INT UNSIGNED NOT NULL DEFAULT 0,
    `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`model`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Per-device key/value storage for external (SDK) apps: NovaSDK.getItem /
-- setItem / removeItem (for example the Browser's history and bookmarks).
-- `item_value` is JSON, capped by Config.AppPlatform.sdk.storage. Deleted
-- when the app is uninstalled or the phone is erased.
CREATE TABLE IF NOT EXISTS `phone_app_storage` (
    `device_id`   VARCHAR(32) NOT NULL,
    `app_id`      VARCHAR(32) NOT NULL,
    `item_key`    VARCHAR(64) NOT NULL,
    `item_value`  TEXT        NOT NULL,
    `updated_at`  TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`device_id`, `app_id`, `item_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Secrets only the server knows (server/server_secret.lua): `master` is made
-- once on the first start (32 random bytes, hex) unless the wd_phone:secret
-- convar gives one. Item identity signatures are derived from it, so
-- deleting the row invalidates every signed phone / SIM / accessory.
CREATE TABLE IF NOT EXISTS `phone_server_secrets` (
    `secret_name`   VARCHAR(32)  NOT NULL,
    `secret_value`  VARCHAR(128) NOT NULL,
    `created_at`    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`secret_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------------
-- Columns added to existing tables after their first release. No-ops on a
-- database created from this file; needed when it already has the tables.
-- ---------------------------------------------------------------------

ALTER TABLE `phone_device_settings`
    ADD COLUMN IF NOT EXISTS `wifi_enabled` TINYINT(1) NOT NULL DEFAULT 0 AFTER `airplane_mode`,
    ADD COLUMN IF NOT EXISTS `mobile_data_enabled` TINYINT(1) NOT NULL DEFAULT 1 AFTER `wifi_enabled`,
    ADD COLUMN IF NOT EXISTS `high_contrast` TINYINT(1) NOT NULL DEFAULT 0 AFTER `reduce_motion`,
    ADD COLUMN IF NOT EXISTS `bold_text` TINYINT(1) NOT NULL DEFAULT 0 AFTER `high_contrast`,
    ADD COLUMN IF NOT EXISTS `reduce_transparency` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bold_text`,
    ADD COLUMN IF NOT EXISTS `find_my_enabled` TINYINT(1) NOT NULL DEFAULT 1 AFTER `reduce_transparency`,
    ADD COLUMN IF NOT EXISTS `lock_clock_font` VARCHAR(12) NOT NULL DEFAULT 'rounded' AFTER `find_my_enabled`,
    ADD COLUMN IF NOT EXISTS `lock_clock_color` VARCHAR(12) NOT NULL DEFAULT 'white' AFTER `lock_clock_font`,
    ADD COLUMN IF NOT EXISTS `lock_show_weather` TINYINT(1) NOT NULL DEFAULT 1 AFTER `lock_clock_color`,
    ADD COLUMN IF NOT EXISTS `lock_show_calendar` TINYINT(1) NOT NULL DEFAULT 1 AFTER `lock_show_weather`,
    ADD COLUMN IF NOT EXISTS `lock_show_alarm` TINYINT(1) NOT NULL DEFAULT 1 AFTER `lock_show_calendar`,
    ADD COLUMN IF NOT EXISTS `silent_mode` TINYINT(1) NOT NULL DEFAULT 0 AFTER `lock_show_alarm`,
    ADD COLUMN IF NOT EXISTS `bluetooth_enabled` TINYINT(1) NOT NULL DEFAULT 1 AFTER `mobile_data_enabled`,
    ADD COLUMN IF NOT EXISTS `low_power_mode` TINYINT(1) NOT NULL DEFAULT 0 AFTER `bluetooth_enabled`,
    ADD COLUMN IF NOT EXISTS `optimized_charging` TINYINT(1) NOT NULL DEFAULT 0 AFTER `low_power_mode`,
    ADD COLUMN IF NOT EXISTS `cc_lock_screen` TINYINT(1) NOT NULL DEFAULT 1 AFTER `bluetooth_enabled`,
    ADD COLUMN IF NOT EXISTS `cc_controls` VARCHAR(400) DEFAULT NULL AFTER `cc_lock_screen`,
    ADD COLUMN IF NOT EXISTS `voice_changer` VARCHAR(160) DEFAULT NULL AFTER `cc_controls`,
    ADD COLUMN IF NOT EXISTS `accent_color` VARCHAR(16) NOT NULL DEFAULT 'auto' AFTER `wallpaper`,
    ADD COLUMN IF NOT EXISTS `duress_pin_hash` VARCHAR(255) DEFAULT NULL AFTER `pin_hash`,
    ADD COLUMN IF NOT EXISTS `face_id_unlock` TINYINT(1) NOT NULL DEFAULT 1 AFTER `duress_pin_hash`,
    ADD COLUMN IF NOT EXISTS `face_id_attention` TINYINT(1) NOT NULL DEFAULT 1 AFTER `face_id_unlock`;

-- Batteries and charging (per phone; Config.Charging).
ALTER TABLE `phone_device_runtime`
    ADD COLUMN IF NOT EXISTS `charge_source` VARCHAR(16) DEFAULT NULL AFTER `charging`,
    ADD COLUMN IF NOT EXISTS `powered_off` TINYINT(1) NOT NULL DEFAULT 0 AFTER `charge_source`,
    ADD COLUMN IF NOT EXISTS `off_by_user` TINYINT(1) NOT NULL DEFAULT 0 AFTER `powered_off`,
    ADD COLUMN IF NOT EXISTS `charged_total` DOUBLE NOT NULL DEFAULT 0 AFTER `runtime_ticks`,
    ADD COLUMN IF NOT EXISTS `health_wear` DOUBLE NOT NULL DEFAULT 0 AFTER `charged_total`;

ALTER TABLE `phone_installed_apps`
    ADD COLUMN IF NOT EXISTS `version` VARCHAR(16) NOT NULL DEFAULT '1.0.0' AFTER `app_id`,
    ADD COLUMN IF NOT EXISTS `source` VARCHAR(12) NOT NULL DEFAULT 'builtin' AFTER `version`,
    ADD COLUMN IF NOT EXISTS `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE `phone_notifications`
    ADD COLUMN IF NOT EXISTS `channel_id` VARCHAR(32) NOT NULL DEFAULT 'default' AFTER `app_id`;

ALTER TABLE `phone_message_receipts`
    ADD COLUMN IF NOT EXISTS `expired_at` TIMESTAMP(3) NULL DEFAULT NULL AFTER `read_at`;

-- The Bites sweep reads open food orders by product every minute.
ALTER TABLE `phone_business_orders`
    ADD INDEX IF NOT EXISTS `idx_business_orders_product` (`product_id`, `status`, `created_at`);

-- Business order invoices bill the character who ordered (server-only).
ALTER TABLE `phone_business_orders`
    ADD COLUMN IF NOT EXISTS `customer_citizenid` VARCHAR(64) DEFAULT NULL AFTER `customer_number`;

-- Homes keyholder rows are per number given (a holder can have several).
-- Re-runnable: the same primary key is rebuilt each time.
ALTER TABLE `phone_home_keys`
    DROP PRIMARY KEY,
    ADD PRIMARY KEY (`owner_citizenid`, `home_id`, `holder_citizenid`, `phone_number`);

-- Alarms fire in the phone's own time basis (its UTC offset when saved).
ALTER TABLE `phone_alarms`
    ADD COLUMN IF NOT EXISTS `utc_offset` SMALLINT NOT NULL DEFAULT 0 AFTER `last_fired_key`,
    ADD COLUMN IF NOT EXISTS `utc_minute` SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER `utc_offset`,
    ADD INDEX IF NOT EXISTS `idx_alarms_due` (`enabled`, `utc_minute`);

-- Delivery queue indexes.
ALTER TABLE `phone_message_receipts`
    ADD INDEX IF NOT EXISTS `idx_receipt_line_pending` (`line_id`, `delivered_at`),
    ADD INDEX IF NOT EXISTS `idx_receipt_pending` (`delivered_at`, `expired_at`);

ALTER TABLE `phone_calls`
    ADD COLUMN IF NOT EXISTS `is_video` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_anonymous`;

ALTER TABLE `phone_media`
    ADD COLUMN IF NOT EXISTS `kind` VARCHAR(8) NOT NULL DEFAULT 'photo' AFTER `orientation`,
    ADD COLUMN IF NOT EXISTS `duration_ms` INT UNSIGNED DEFAULT NULL AFTER `kind`;

ALTER TABLE `phone_contacts`
    ADD COLUMN IF NOT EXISTS `company` VARCHAR(60) DEFAULT NULL AFTER `avatar_color`;

ALTER TABLE `phone_communication_settings`
    ADD COLUMN IF NOT EXISTS `my_card_display_name` VARCHAR(40) DEFAULT NULL AFTER `missed_call_notifications`,
    ADD COLUMN IF NOT EXISTS `my_card_avatar_color` VARCHAR(9) NOT NULL DEFAULT '#5e5ce6' AFTER `my_card_display_name`,
    ADD COLUMN IF NOT EXISTS `my_card_notes` VARCHAR(300) DEFAULT NULL AFTER `my_card_avatar_color`,
    ADD COLUMN IF NOT EXISTS `my_card_media_id` VARCHAR(48) DEFAULT NULL AFTER `my_card_notes`,
    ADD COLUMN IF NOT EXISTS `nova_drop` VARCHAR(10) NOT NULL DEFAULT 'everyone' AFTER `my_card_media_id`;

ALTER TABLE `phone_device_sim_slots`
    ADD COLUMN IF NOT EXISTS `item_name` VARCHAR(32) DEFAULT NULL AFTER `line_label`;

ALTER TABLE `phone_carrier_accounts`
    ADD COLUMN IF NOT EXISTS `data_kb` INT NOT NULL DEFAULT 0 AFTER `balance`,
    ADD COLUMN IF NOT EXISTS `data_used_kb` INT NOT NULL DEFAULT 0 AFTER `data_kb`,
    ADD COLUMN IF NOT EXISTS `data_overage_kb` INT NOT NULL DEFAULT 0 AFTER `data_used_kb`,
    ADD COLUMN IF NOT EXISTS `low_data_notified` TINYINT(1) NOT NULL DEFAULT 0 AFTER `data_overage_kb`;

-- Data received at a number belongs to an owner: the Nova Account signed in
-- on the phone holding it, else that phone (owner_key). Earlier line-keyed
-- columns are renamed; their old values no longer resolve to anyone.
ALTER TABLE `phone_contacts` CHANGE COLUMN IF EXISTS `owner_line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_contact_numbers` CHANGE COLUMN IF EXISTS `owner_line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_contact_avatars` CHANGE COLUMN IF EXISTS `line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_blocked_numbers` CHANGE COLUMN IF EXISTS `owner_line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_communication_settings` CHANGE COLUMN IF EXISTS `line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_message_hidden` CHANGE COLUMN IF EXISTS `line_id` `owner_key` VARCHAR(40) NOT NULL;
-- Wolfden Phone Secure retention reads one owner's recently hidden rows every minute.
ALTER TABLE `phone_message_hidden`
    ADD INDEX IF NOT EXISTS `idx_hidden_owner_created` (`owner_key`, `created_at`);
ALTER TABLE `phone_call_visibility` CHANGE COLUMN IF EXISTS `line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_saved_locations` CHANGE COLUMN IF EXISTS `owner_line_id` `owner_key` VARCHAR(40) NOT NULL;
ALTER TABLE `phone_recent_locations` CHANGE COLUMN IF EXISTS `owner_line_id` `owner_key` VARCHAR(40) NOT NULL;

ALTER TABLE `phone_messages`
    ADD COLUMN IF NOT EXISTS `sender_owner_key` VARCHAR(40) DEFAULT NULL AFTER `sender_line_id`,
    ADD INDEX IF NOT EXISTS `idx_messages_sender_owner_created` (`sender_owner_key`, `created_at`);

ALTER TABLE `phone_message_receipts`
    ADD COLUMN IF NOT EXISTS `owner_key` VARCHAR(40) DEFAULT NULL AFTER `line_id`,
    ADD INDEX IF NOT EXISTS `idx_receipt_owner` (`owner_key`, `read_at`);

ALTER TABLE `phone_calls`
    ADD COLUMN IF NOT EXISTS `caller_owner_key` VARCHAR(40) DEFAULT NULL AFTER `receiver_line_id`,
    ADD COLUMN IF NOT EXISTS `receiver_owner_key` VARCHAR(40) DEFAULT NULL AFTER `caller_owner_key`,
    ADD INDEX IF NOT EXISTS `idx_calls_caller_owner` (`caller_owner_key`, `started_at`),
    ADD INDEX IF NOT EXISTS `idx_calls_receiver_owner` (`receiver_owner_key`, `started_at`);

-- Group calls: a call's legs share group_call_id (history collapses them).
ALTER TABLE `phone_calls`
    ADD COLUMN IF NOT EXISTS `group_call_id` VARCHAR(40) DEFAULT NULL AFTER `call_id`,
    ADD COLUMN IF NOT EXISTS `conversation_id` VARCHAR(40) DEFAULT NULL AFTER `group_call_id`,
    ADD INDEX IF NOT EXISTS `idx_calls_group` (`group_call_id`);

ALTER TABLE `phone_voicemails`
    ADD COLUMN IF NOT EXISTS `owner_key` VARCHAR(40) DEFAULT NULL AFTER `recipient_line_id`,
    ADD INDEX IF NOT EXISTS `idx_voicemail_owner` (`owner_key`, `deleted_at`, `created_at`);

ALTER TABLE `phone_mail_recipients`
    ADD COLUMN IF NOT EXISTS `owner_key` VARCHAR(40) DEFAULT NULL AFTER `line_id`,
    ADD INDEX IF NOT EXISTS `idx_mailrcpt_owner` (`owner_key`, `deleted_at`, `is_trashed`, `is_archived`);

-- Mail to a Nova Account mailbox has no line.
ALTER TABLE `phone_mail_recipients` MODIFY COLUMN `line_id` VARCHAR(40) DEFAULT NULL;

-- Setup is finished explicitly (phone_devices.setup_complete), so closing
-- the phone midway does not skip it.
ALTER TABLE `phone_devices`
    ADD COLUMN IF NOT EXISTS `setup_complete` TINYINT(1) NOT NULL DEFAULT 0 AFTER `last_used_at`;

-- Lost mode records the Nova Account that holds the phone's activation lock.
ALTER TABLE `phone_device_security`
    ADD COLUMN IF NOT EXISTS `lost_account_id` VARCHAR(24) DEFAULT NULL AFTER `lost_since`;

-- Nova Cloud backups belong to the Nova Account (citizen_id = creator).
ALTER TABLE `phone_backups`
    ADD COLUMN IF NOT EXISTS `account_id` VARCHAR(24) DEFAULT NULL AFTER `citizen_id`,
    ADD INDEX IF NOT EXISTS `idx_backups_account` (`account_id`, `deleted_at`, `created_at`);

-- Subscriber records: the carrier shop a SIM was bought at.
ALTER TABLE `phone_sims`
    ADD COLUMN IF NOT EXISTS `purchase_shop` VARCHAR(32) DEFAULT NULL AFTER `registered_owner`;

-- SIM suspensions: why (only a billing suspension is lifted by paying the bill).
ALTER TABLE `phone_sims`
    ADD COLUMN IF NOT EXISTS `suspend_reason` VARCHAR(32) DEFAULT NULL AFTER `status`;

-- Shade rooms: room messages.
ALTER TABLE `phone_shade_messages`
    ADD COLUMN IF NOT EXISTS `room_id` VARCHAR(32) DEFAULT NULL AFTER `thread_id`,
    ADD INDEX IF NOT EXISTS `idx_shade_messages_room` (`room_id`, `id`);

-- Verified badges (Config.Creators.verification): Snapgram and Clips.
ALTER TABLE `phone_snap_profiles`
    ADD COLUMN IF NOT EXISTS `verified` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_private`;
ALTER TABLE `phone_clips_profiles`
    ADD COLUMN IF NOT EXISTS `verified` TINYINT(1) NOT NULL DEFAULT 0 AFTER `is_private`;

-- Creator fund: the character that posted a clip (paid for its views).
ALTER TABLE `phone_clips`
    ADD COLUMN IF NOT EXISTS `author_citizenid` VARCHAR(64) DEFAULT NULL AFTER `removed_by`,
    ADD INDEX IF NOT EXISTS `idx_clips_author_citizen` (`author_citizenid`, `id`);

-- Creator analytics: views per clip and day.
ALTER TABLE `phone_clips_views`
    ADD INDEX IF NOT EXISTS `idx_clips_views_clip_time` (`clip_id`, `viewed_at`);

-- Clips charts: views and new follows per week, city-wide.
ALTER TABLE `phone_clips_views`
    ADD INDEX IF NOT EXISTS `idx_clips_views_time` (`viewed_at`);
ALTER TABLE `phone_clips_follows`
    ADD INDEX IF NOT EXISTS `idx_clips_follows_time` (`status`, `created_at`);

-- Brand deals: revision cap and payout / refund retry order.
ALTER TABLE `phone_clips_deals`
    ADD COLUMN IF NOT EXISTS `revisions` TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER `feedback`,
    ADD COLUMN IF NOT EXISTS `tried_at` TIMESTAMP NULL DEFAULT NULL AFTER `resolved_at`;

-- Brand deals: funding / payout / refund retries ordered by tried_at.
ALTER TABLE `phone_clips_deals`
    DROP INDEX IF EXISTS `idx_clips_deals_money`,
    ADD INDEX IF NOT EXISTS `idx_clips_deals_retry` (`money_status`, `tried_at`);

-- Clips gifts: reconcile / refund retry order.
ALTER TABLE `phone_clips_gifts`
    ADD COLUMN IF NOT EXISTS `tried_at` TIMESTAMP NULL DEFAULT NULL AFTER `status`,
    DROP INDEX IF EXISTS `idx_clips_gifts_status`,
    ADD INDEX IF NOT EXISTS `idx_clips_gifts_retry` (`status`, `tried_at`);

-- Verified badges: a badge a moderator / admin / resource took away, or
-- that a handle change cleared, is never given back by the auto-verify
-- sweep (only an explicit verify clears the mark).
ALTER TABLE `phone_social_profiles`
    ADD COLUMN IF NOT EXISTS `verified_revoked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `verified`;
ALTER TABLE `phone_snap_profiles`
    ADD COLUMN IF NOT EXISTS `verified_revoked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `verified`;
ALTER TABLE `phone_clips_profiles`
    ADD COLUMN IF NOT EXISTS `verified_revoked` TINYINT(1) NOT NULL DEFAULT 0 AFTER `verified`;

-- Racing: the buy-in fee waiting for feeSharedAccount, and the part of a
-- no-show's stake a race keeps (paid out, or given back if it never pays).
ALTER TABLE `phone_racing_races`
    ADD COLUMN IF NOT EXISTS `fee_status` VARCHAR(8) NOT NULL DEFAULT 'none' AFTER `fee`,
    ADD INDEX IF NOT EXISTS `idx_racing_races_fee` (`fee_status`);
ALTER TABLE `phone_racing_entries`
    ADD COLUMN IF NOT EXISTS `kept` INT UNSIGNED NOT NULL DEFAULT 0 AFTER `stake_status`,
    ADD INDEX IF NOT EXISTS `idx_racing_entries_kept` (`kept`);

-- NovaBuds: model and name, the Nova Account a pair belongs to, charging,
-- battery health, noise control and Locate (last seen, lost mode).
ALTER TABLE `phone_novapods`
    ADD COLUMN IF NOT EXISTS `model` VARCHAR(48) DEFAULT NULL AFTER `novapods_id`,
    ADD COLUMN IF NOT EXISTS `name` VARCHAR(48) DEFAULT NULL AFTER `model`,
    ADD COLUMN IF NOT EXISTS `owner_account_id` VARCHAR(24) DEFAULT NULL AFTER `name`,
    ADD COLUMN IF NOT EXISTS `charge_rate` FLOAT NOT NULL DEFAULT 0 AFTER `worn`,
    ADD COLUMN IF NOT EXISTS `last_seen_x` FLOAT DEFAULT NULL AFTER `charge_rate`,
    ADD COLUMN IF NOT EXISTS `last_seen_y` FLOAT DEFAULT NULL AFTER `last_seen_x`,
    ADD COLUMN IF NOT EXISTS `last_seen_z` FLOAT DEFAULT NULL AFTER `last_seen_y`,
    ADD COLUMN IF NOT EXISTS `last_seen_at` TIMESTAMP NULL DEFAULT NULL AFTER `last_seen_z`,
    ADD COLUMN IF NOT EXISTS `health` FLOAT NOT NULL DEFAULT 100 AFTER `last_seen_at`,
    ADD COLUMN IF NOT EXISTS `charged_total` DOUBLE NOT NULL DEFAULT 0 AFTER `health`,
    ADD COLUMN IF NOT EXISTS `health_wear` DOUBLE NOT NULL DEFAULT 0 AFTER `charged_total`,
    ADD COLUMN IF NOT EXISTS `noise_control` VARCHAR(12) NOT NULL DEFAULT 'transparency' AFTER `health_wear`,
    ADD COLUMN IF NOT EXISTS `left_behind` TINYINT(1) NOT NULL DEFAULT 1 AFTER `noise_control`,
    ADD COLUMN IF NOT EXISTS `lost_mode` TINYINT(1) NOT NULL DEFAULT 0 AFTER `left_behind`,
    ADD COLUMN IF NOT EXISTS `lost_message` VARCHAR(160) DEFAULT NULL AFTER `lost_mode`,
    ADD COLUMN IF NOT EXISTS `lost_contact` VARCHAR(20) DEFAULT NULL AFTER `lost_message`,
    ADD INDEX IF NOT EXISTS `idx_novapods_owner` (`owner_account_id`);

-- Account holder names (sign-up asks for both). Existing rows get ''.
ALTER TABLE `phone_accounts`
    ADD COLUMN IF NOT EXISTS `first_name` VARCHAR(30) NOT NULL DEFAULT '' AFTER `login`,
    ADD COLUMN IF NOT EXISTS `last_name` VARCHAR(30) NOT NULL DEFAULT '' AFTER `first_name`;

-- A business account's job / society account in the banking resource.
ALTER TABLE `phone_bank_accounts`
    ADD COLUMN IF NOT EXISTS `shared_account` VARCHAR(64) DEFAULT NULL AFTER `balance`;

-- The line mobile data uses (Sims.setDataLine).
ALTER TABLE `phone_device_line_prefs`
    ADD COLUMN IF NOT EXISTS `default_data_line_id` VARCHAR(40) DEFAULT NULL AFTER `default_message_line_id`;

-- Wallet payment requests find their transaction by reference.
ALTER TABLE `phone_bank_transactions`
    ADD INDEX IF NOT EXISTS `idx_bank_tx_reference` (`reference_id`);

-- Rides: the platform fee is marked before it is charged (never twice).
ALTER TABLE `phone_rides`
    ADD COLUMN IF NOT EXISTS `fee_status` VARCHAR(8) NOT NULL DEFAULT 'none' AFTER `payment_status`;


-- MediaServer.purge asks each table that may still show a photo / video
-- whether it does, and whether another record shares the remote file.
ALTER TABLE `phone_media`
    ADD INDEX IF NOT EXISTS `idx_media_remote_url` (`remote_url`(191)),
    ADD INDEX IF NOT EXISTS `idx_media_thumbnail_url` (`thumbnail_url`(191)),
    ADD INDEX IF NOT EXISTS `idx_media_tombstone` (`purge_after`, `deleted_at`, `id`);
ALTER TABLE `phone_mail_attachments`
    ADD INDEX IF NOT EXISTS `idx_mailatt_media` (`media_id`);
ALTER TABLE `phone_contact_avatars`
    ADD INDEX IF NOT EXISTS `idx_contact_avatar_media` (`media_id`);
ALTER TABLE `phone_communication_settings`
    ADD INDEX IF NOT EXISTS `idx_commsettings_card_media` (`my_card_media_id`);
ALTER TABLE `phone_snap_profiles`
    ADD INDEX IF NOT EXISTS `idx_snap_profiles_avatar` (`avatar_media_id`);
ALTER TABLE `phone_clips_profiles`
    ADD INDEX IF NOT EXISTS `idx_clips_profiles_avatar` (`avatar_media_id`);
ALTER TABLE `phone_social_profiles`
    ADD INDEX IF NOT EXISTS `idx_social_profiles_avatar` (`avatar_media_id`);
ALTER TABLE `phone_social_post_media`
    ADD INDEX IF NOT EXISTS `idx_social_post_media_media` (`media_id`);
ALTER TABLE `phone_market_listing_media`
    ADD INDEX IF NOT EXISTS `idx_market_media_media` (`media_id`);
ALTER TABLE `phone_device_wallpapers`
    ADD INDEX IF NOT EXISTS `idx_wallpapers_media` (`media_id`);
ALTER TABLE `phone_albums`
    ADD INDEX IF NOT EXISTS `idx_albums_cover` (`cover_media_id`);
ALTER TABLE `phone_gallery_favorites`
    ADD INDEX IF NOT EXISTS `idx_fav_media` (`media_id`);

-- Wolfden Phone Secure retention reads one owner's delivered receipts by age every
-- minute (sent messages: idx_messages_sender_owner_created).
ALTER TABLE `phone_message_receipts`
    ADD INDEX IF NOT EXISTS `idx_receipt_owner_delivered` (`owner_key`, `delivered_at`);

-- Conversations have no archive any more.
ALTER TABLE `phone_conversation_views`
    DROP COLUMN IF EXISTS `is_archived`;

-- Rotation Lock is gone (the phone stays portrait; the camera turns with its
-- own button).
ALTER TABLE `phone_device_settings`
    DROP COLUMN IF EXISTS `rotation_lock`;
```

The installer only creates and updates its own `phone_*` tables. It never changes your framework's or inventory's tables, and it is safe to import again on every update.

## 3. Add the inventory items

Every item is in one file per inventory. Use the block for the inventory your server runs and skip the other.

The file starts with the required items: 17 phones (Wolfden Phone Secure plus one item for each of the eight colours of Wolfden Phone and Wolfden Phone Pro) and three SIM cards. The phone on screen always matches the item's colour. Below them, each under its own heading, come the items that switch on a feature: chargers, the Wi-Fi router, NovaBuds, Bluetooth speakers, the blank SD card, Shade (its installer card and room key) and the Racing USB. Leave a group out only if you turn that feature off; the self-test in step 10 warns about missing items for features that are on. Shade installs only from its `shade_sdcard` item, and by default the Racing app appears only while a player carries a `racing_usb`.

If any of these item names already exist, replace those entries instead of adding duplicates. Keep the item names, `stack`/`unique` settings and export names exactly as shown.

### ox_inventory

Open `ox_inventory/data/items.lua`. Find the existing `return {` items table and paste the **entire block below inside it, before its final closing `}`**. Keep your other inventory items.

```lua ox_inventory/data/items.lua — every Wolfden Phone item (paste inside the existing items table)
--[[
    migrations/ox_inventory_items.lua

    Every wd_phone item for ox_inventory, in one file.

    COPY THESE ENTRIES into your ox_inventory items file:
        ox_inventory/data/items.lua
    and copy inventory/*.png to ox_inventory/web/images/ (one image per item,
    named after it; all SIMs share nova_sim.png).

    Do NOT require/import this file from ox_inventory and do NOT let wd_phone
    overwrite ox_inventory files. This is a copy/paste installation snippet only.

    Core items (phones and SIMs) are required. The groups after them are
    optional: leave a group out if you disable that feature in its config.

    Item names must match the configs: phones in config/devices.lua, SIMs in
    config/sims.lua, NovaBuds in Config.NovaPods.models (config/novapods.lua),
    speakers in Config.Speakers.models (config/speakers.lua).

    Every item is consume = 0 (never consumed) and close = true (closes the
    inventory on use). Items with their own identity (phones, SIMs, NovaBuds,
    speakers, routers, power banks) are stack = false.
]]

-- =========================================================================
-- CORE: Phones
-- =========================================================================
-- client.export -> wd_phone.usePhone. Wolfden Phone and Wolfden Phone Pro are one item per
-- colour (nova_phone_ocean, nova_business_rosegold, ...) and the phone's frame
-- always matches it; Wolfden Phone Secure is the single black nova_secure.

['nova_secure'] = {
    label = 'Wolfden Phone Secure',
    weight = 175,
    stack = false,
    close = true,
    consume = 0,
    description = 'A rugged Wolfden Phone Secure field terminal with cameras and a green tactical display.',
    client = {
        image = 'nova_secure.png',
        export = 'wd_phone.usePhone',
    },
},

-- One item per colour: the phone's finish (on-screen frame and side buttons)
-- is the item's colour. Names match config/devices.lua and the image files.
['nova_phone_midnight'] = {
    label = 'Wolfden Phone (Midnight Black)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Midnight Black.',
    client = {
        image = 'nova_phone_midnight.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_pearl'] = {
    label = 'Wolfden Phone (Pearl White)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Pearl White.',
    client = {
        image = 'nova_phone_pearl.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_titanium'] = {
    label = 'Wolfden Phone (Titanium Grey)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Titanium Grey.',
    client = {
        image = 'nova_phone_titanium.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_rosegold'] = {
    label = 'Wolfden Phone (Rose Gold)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Rose Gold.',
    client = {
        image = 'nova_phone_rosegold.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_ocean'] = {
    label = 'Wolfden Phone (Ocean Blue)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Ocean Blue.',
    client = {
        image = 'nova_phone_ocean.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_violet'] = {
    label = 'Wolfden Phone (Violet)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Violet.',
    client = {
        image = 'nova_phone_violet.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_red'] = {
    label = 'Wolfden Phone (Red)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Red.',
    client = {
        image = 'nova_phone_red.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_phone_forest'] = {
    label = 'Wolfden Phone (Forest Green)',
    weight = 180,
    stack = false,
    close = true,
    consume = 0,
    description = 'A modern NovaOS smartphone in Forest Green.',
    client = {
        image = 'nova_phone_forest.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_midnight'] = {
    label = 'Wolfden Phone Pro (Midnight Black)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Midnight Black.',
    client = {
        image = 'nova_business_midnight.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_pearl'] = {
    label = 'Wolfden Phone Pro (Pearl White)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Pearl White.',
    client = {
        image = 'nova_business_pearl.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_titanium'] = {
    label = 'Wolfden Phone Pro (Titanium Grey)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Titanium Grey.',
    client = {
        image = 'nova_business_titanium.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_rosegold'] = {
    label = 'Wolfden Phone Pro (Rose Gold)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Rose Gold.',
    client = {
        image = 'nova_business_rosegold.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_ocean'] = {
    label = 'Wolfden Phone Pro (Ocean Blue)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Ocean Blue.',
    client = {
        image = 'nova_business_ocean.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_violet'] = {
    label = 'Wolfden Phone Pro (Violet)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Violet.',
    client = {
        image = 'nova_business_violet.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_red'] = {
    label = 'Wolfden Phone Pro (Red)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Red.',
    client = {
        image = 'nova_business_red.png',
        export = 'wd_phone.usePhone',
    },
},

['nova_business_forest'] = {
    label = 'Wolfden Phone Pro (Forest Green)',
    weight = 190,
    stack = false,
    close = true,
    consume = 0,
    description = 'NovaOS with a dedicated work profile, in Forest Green.',
    client = {
        image = 'nova_business_forest.png',
        export = 'wd_phone.usePhone',
    },
},

-- =========================================================================
-- CORE: SIM cards
-- =========================================================================
-- Each activated SIM is uniquely identifiable (stack = false). Only nova_sim /
-- nova_business_sim open the SIM details on use (wd_phone.useSim). A blank SIM
-- is activated at a carrier shop.

['nova_sim_blank'] = {
    label = 'Blank Nova SIM',
    weight = 5,
    stack = false,
    close = true,
    consume = 0,
    description = 'An inactive SIM. Activate it at a carrier shop.',
    client = {
        image = 'nova_sim.png',
    },
},

['nova_sim'] = {
    label = 'Nova SIM',
    weight = 5,
    stack = false,
    close = true,
    consume = 0,
    description = 'An activated Nova SIM card.',
    client = {
        image = 'nova_sim.png',
        export = 'wd_phone.useSim',
    },
},

['nova_business_sim'] = {
    label = 'Nova Business SIM',
    weight = 5,
    stack = false,
    close = true,
    consume = 0,
    description = 'A business-tier Nova SIM card.',
    client = {
        image = 'nova_sim.png',
        export = 'wd_phone.useSim',
    },
},

-- =========================================================================
-- OPTIONAL: Charging (config/charging.lua)
-- =========================================================================
-- - nova_car_charger: using it in a vehicle with the engine running plugs a
--   phone / NovaBuds in (or unplugs). It unplugs when you get out.
-- - nova_power_bank: stack = false (each bank has its own charge, shown as the
--   durability bar). Using it plugs a phone / NovaBuds in. Recharge it on a
--   charging pad.
-- - nova_charging_pad: using it starts placement (scroll rotates, E places,
--   Backspace cancels). Put a phone, NovaBuds or a power bank on it with
--   ox_target. Only its owner can pick it up again, when it is empty.

['nova_car_charger'] = {
    label = 'Car Phone Charger',
    weight = 120,
    stack = true,
    close = true,
    consume = 0,
    description = 'Use it in a vehicle with the engine running to charge your phone or NovaBuds.',
    client = {
        image = 'nova_car_charger.png',
        export = 'wd_phone.useCarCharger',
    },
},

['nova_power_bank'] = {
    label = 'Power Bank',
    weight = 250,
    stack = false,
    close = true,
    consume = 0,
    description = 'Charges your phone or NovaBuds on the go. Recharge it on a charging pad.',
    client = {
        image = 'nova_power_bank.png',
        export = 'wd_phone.usePowerBank',
    },
},

['nova_charging_pad'] = {
    label = 'Charging Pad',
    weight = 400,
    stack = true,
    close = true,
    consume = 0,
    description = 'Place it, then put a phone, NovaBuds or a power bank on it to charge.',
    client = {
        image = 'nova_charging_pad.png',
        export = 'wd_phone.useChargingPad',
    },
},

-- =========================================================================
-- OPTIONAL: Wi-Fi router (config/wifi.lua)
-- =========================================================================
-- A picked-up router keeps its network, password and package in its metadata
-- `routerId`. Using it starts placement (wd_phone.useRouter): scroll rotates,
-- E places, Backspace cancels. Only its owner can pick it up again.

['nova_router'] = {
    label = 'Wi-Fi Router',
    weight = 800,
    stack = false,
    close = true,
    consume = 0,
    description = 'Place it to share Wi-Fi nearby. Manage it in the Routers app.',
    client = {
        image = 'nova_router.png',
        export = 'wd_phone.useRouter',
    },
},

-- =========================================================================
-- OPTIONAL: NovaBuds and Nova Studio (config/novapods.lua)
-- =========================================================================
-- Each pair has its own `novapodsId`, pairing and battery. Using one puts it
-- on / takes it off (wd_phone.useNovaPods). Pair them to a phone in
-- Settings -> Bluetooth.

['novapods_white'] = {
    label = 'NovaBuds · White',
    weight = 60,
    stack = false,
    close = true,
    consume = 0,
    description = 'Wireless earbuds. Use to put them in; pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_white.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_black'] = {
    label = 'NovaBuds · Black',
    weight = 60,
    stack = false,
    close = true,
    consume = 0,
    description = 'Wireless earbuds. Use to put them in; pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_black.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_max_silver'] = {
    label = 'Nova Studio · Silver',
    weight = 380,
    stack = false,
    close = true,
    consume = 0,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_max_silver.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_max_spacegray'] = {
    label = 'Nova Studio · Space Gray',
    weight = 380,
    stack = false,
    close = true,
    consume = 0,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_max_spacegray.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_max_skyblue'] = {
    label = 'Nova Studio · Sky Blue',
    weight = 380,
    stack = false,
    close = true,
    consume = 0,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_max_skyblue.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_max_pink'] = {
    label = 'Nova Studio · Pink',
    weight = 380,
    stack = false,
    close = true,
    consume = 0,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_max_pink.png',
        export = 'wd_phone.useNovaPods',
    },
},

['novapods_max_green'] = {
    label = 'Nova Studio · Green',
    weight = 380,
    stack = false,
    close = true,
    consume = 0,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
    client = {
        image = 'novapods_max_green.png',
        export = 'wd_phone.useNovaPods',
    },
},

-- =========================================================================
-- OPTIONAL: Bluetooth speakers (config/speakers.lua)
-- =========================================================================
-- Each speaker has its own `speakerId`, pairing and battery. Using one sets it
-- down (wd_phone.useSpeaker). Pair it in Settings -> Bluetooth, then pick it as
-- Music's output (Now Playing or Control Center). The Nova Mini also plays
-- from your hip while you carry it.

['nova_speaker_mini'] = {
    label = 'Nova Mini Speaker',
    weight = 450,
    stack = false,
    close = true,
    consume = 0,
    description = 'Portable Bluetooth speaker. Plays your phone\'s music while you carry it, or use it to set it down. Pair it in Settings -> Bluetooth.',
    client = {
        image = 'nova_speaker_mini.png',
        export = 'wd_phone.useSpeaker',
    },
},

['nova_speaker_boom'] = {
    label = 'Nova Boom Speaker',
    weight = 3200,
    stack = false,
    close = true,
    consume = 0,
    description = 'Loud party speaker. Use it to set it down, then play your phone\'s music on it. Pair it in Settings -> Bluetooth.',
    client = {
        image = 'nova_speaker_boom.png',
        export = 'wd_phone.useSpeaker',
    },
},

-- =========================================================================
-- OPTIONAL: SD card (Config.MobileShops.sdCard)
-- =========================================================================
-- A blank memory card, sold at the phone stores. Not used on its own.

['sd_card'] = {
    label = 'SD Card',
    weight = 10,
    stack = true,
    close = true,
    consume = 0,
    description = 'A blank memory card.',
    client = {
        image = 'sd_card.png',
    },
},

-- =========================================================================
-- OPTIONAL: Shade (config/shade.lua)
-- =========================================================================
-- - shade_sdcard: Shade is not in the Nova Store. Using the card installs
--   Shade on the phone you are using (wd_phone.useShadeCard) and uses it up
--   (Config.Shade.install).
-- - shade_key: a physical key to a Shade room, cut by its owner in the app.
--   stack = false: each key carries its room's signed `shadeKeyId`. Carrying
--   it opens that room; it is not used.

['shade_sdcard'] = {
    label = 'Unmarked SD Card',
    weight = 10,
    stack = true,
    close = true,
    consume = 0,
    description = 'A blank-looking memory card. Something is on it.',
    client = {
        image = 'shade_sdcard.png',
        export = 'wd_phone.useShadeCard',
    },
},

['shade_key'] = {
    label = 'Shade Key',
    weight = 15,
    stack = false,
    close = true,
    consume = 0,
    description = 'A small encrypted key. It opens a room on Shade.',
    client = {
        image = 'shade_key.png',
    },
},

-- =========================================================================
-- OPTIONAL: Racing (config/racing.lua)
-- =========================================================================
-- - racing_usb: carrying it opens the underground races in the Racing app
--   (Config.Racing.underground.item, re-checked on every underground list,
--   join and bet). It is not used. Sold by the black-market dealer.

['racing_usb'] = {
    label = 'Racing USB',
    weight = 20,
    stack = true,
    close = true,
    consume = 0,
    description = 'A scuffed USB-C flash drive that plugs into a phone, with a checkered flag scratched into it.',
    client = {
        image = 'racing_usb.png',
    },
},
```

You can also [download the ox_inventory items](reference/wd_phone/migrations/ox_inventory_items.lua).

### qb-inventory

qb-inventory 2.1.0 or newer is required. Open `qb-core/shared/items.lua` and paste the **entire block below inside the `QBShared.Items` table, before its final closing `}`**. Keep your other items. Wolfden Phone registers the usable items itself, so nothing else needs to be added.

```lua qb-core/shared/items.lua — every Wolfden Phone item (paste inside QBShared.Items)
--[[
    migrations/qb_inventory_items.lua

    Every wd_phone item for qb-inventory (QBCore), in one file.

    COPY THESE ENTRIES into the QBShared.Items table of:
        qb-core/shared/items.lua
    and copy inventory/*.png to qb-inventory/html/images/ (one image per
    item, named after it; all SIMs share nova_sim.png).

    Do NOT require/import this file and do NOT let wd_phone overwrite
    qb-core files. This is a copy/paste installation snippet only. It mirrors
    migrations/ox_inventory_items.lua (a test keeps the two in step).

    Core items (phones and SIMs) are required. The groups after them are
    optional: leave a group out if you disable that feature in its config.

    Item names must match the configs: phones in config/devices.lua, SIMs in
    config/sims.lua, NovaBuds in Config.NovaPods.models (config/novapods.lua),
    speakers in Config.Speakers.models (config/speakers.lua).

    Items with their own identity (phones, SIMs, NovaBuds, speakers, routers,
    power banks) are unique = true: their metadata (`info`) carries it.
    useable = true items are registered by wd_phone itself
    (QBCore.Functions.CreateUseableItem, bridge/inventory/qb_inventory.lua)
    when Config.Inventory selects qb-inventory; nothing else to add.
    Needs qb-inventory 2.1.0 or newer.
]]

-- =========================================================================
-- CORE: Phones
-- =========================================================================
-- Using one opens the phone (wd_phone.usePhone). Wolfden Phone and Wolfden Phone Pro are one item per
-- colour (nova_phone_ocean, nova_business_rosegold, ...) and the phone's frame
-- always matches it; Wolfden Phone Secure is the single black nova_secure.

nova_secure = {
    name = 'nova_secure',
    label = 'Wolfden Phone Secure',
    weight = 175,
    type = 'item',
    image = 'nova_secure.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A rugged Wolfden Phone Secure field terminal with cameras and a green tactical display.',
},

-- One item per colour: the phone's finish (on-screen frame and side buttons)
-- is the item's colour. Names match config/devices.lua and the image files.
nova_phone_midnight = {
    name = 'nova_phone_midnight',
    label = 'Wolfden Phone (Midnight Black)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_midnight.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Midnight Black.',
},

nova_phone_pearl = {
    name = 'nova_phone_pearl',
    label = 'Wolfden Phone (Pearl White)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_pearl.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Pearl White.',
},

nova_phone_titanium = {
    name = 'nova_phone_titanium',
    label = 'Wolfden Phone (Titanium Grey)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_titanium.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Titanium Grey.',
},

nova_phone_rosegold = {
    name = 'nova_phone_rosegold',
    label = 'Wolfden Phone (Rose Gold)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_rosegold.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Rose Gold.',
},

nova_phone_ocean = {
    name = 'nova_phone_ocean',
    label = 'Wolfden Phone (Ocean Blue)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_ocean.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Ocean Blue.',
},

nova_phone_violet = {
    name = 'nova_phone_violet',
    label = 'Wolfden Phone (Violet)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_violet.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Violet.',
},

nova_phone_red = {
    name = 'nova_phone_red',
    label = 'Wolfden Phone (Red)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_red.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Red.',
},

nova_phone_forest = {
    name = 'nova_phone_forest',
    label = 'Wolfden Phone (Forest Green)',
    weight = 180,
    type = 'item',
    image = 'nova_phone_forest.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A modern NovaOS smartphone in Forest Green.',
},

nova_business_midnight = {
    name = 'nova_business_midnight',
    label = 'Wolfden Phone Pro (Midnight Black)',
    weight = 190,
    type = 'item',
    image = 'nova_business_midnight.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Midnight Black.',
},

nova_business_pearl = {
    name = 'nova_business_pearl',
    label = 'Wolfden Phone Pro (Pearl White)',
    weight = 190,
    type = 'item',
    image = 'nova_business_pearl.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Pearl White.',
},

nova_business_titanium = {
    name = 'nova_business_titanium',
    label = 'Wolfden Phone Pro (Titanium Grey)',
    weight = 190,
    type = 'item',
    image = 'nova_business_titanium.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Titanium Grey.',
},

nova_business_rosegold = {
    name = 'nova_business_rosegold',
    label = 'Wolfden Phone Pro (Rose Gold)',
    weight = 190,
    type = 'item',
    image = 'nova_business_rosegold.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Rose Gold.',
},

nova_business_ocean = {
    name = 'nova_business_ocean',
    label = 'Wolfden Phone Pro (Ocean Blue)',
    weight = 190,
    type = 'item',
    image = 'nova_business_ocean.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Ocean Blue.',
},

nova_business_violet = {
    name = 'nova_business_violet',
    label = 'Wolfden Phone Pro (Violet)',
    weight = 190,
    type = 'item',
    image = 'nova_business_violet.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Violet.',
},

nova_business_red = {
    name = 'nova_business_red',
    label = 'Wolfden Phone Pro (Red)',
    weight = 190,
    type = 'item',
    image = 'nova_business_red.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Red.',
},

nova_business_forest = {
    name = 'nova_business_forest',
    label = 'Wolfden Phone Pro (Forest Green)',
    weight = 190,
    type = 'item',
    image = 'nova_business_forest.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'NovaOS with a dedicated work profile, in Forest Green.',
},

-- =========================================================================
-- CORE: SIM cards
-- =========================================================================
-- Each activated SIM is uniquely identifiable (unique = true). Only nova_sim /
-- nova_business_sim open the SIM details on use (wd_phone.useSim). A blank SIM
-- is activated at a carrier shop.

nova_sim_blank = {
    name = 'nova_sim_blank',
    label = 'Blank Nova SIM',
    weight = 5,
    type = 'item',
    image = 'nova_sim.png',
    unique = true,
    useable = false,
    shouldClose = true,
    description = 'An inactive SIM. Activate it at a carrier shop.',
},

nova_sim = {
    name = 'nova_sim',
    label = 'Nova SIM',
    weight = 5,
    type = 'item',
    image = 'nova_sim.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'An activated Nova SIM card.',
},

nova_business_sim = {
    name = 'nova_business_sim',
    label = 'Nova Business SIM',
    weight = 5,
    type = 'item',
    image = 'nova_sim.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'A business-tier Nova SIM card.',
},

-- =========================================================================
-- OPTIONAL: Charging (config/charging.lua)
-- =========================================================================
-- - nova_car_charger: using it in a vehicle with the engine running plugs a
--   phone / NovaBuds in (or unplugs). It unplugs when you get out.
-- - nova_power_bank: unique = true (each bank has its own charge, shown in its
--   description). Using it plugs a phone / NovaBuds in. Recharge it on a
--   charging pad.
-- - nova_charging_pad: using it starts placement (scroll rotates, E places,
--   Backspace cancels). Put a phone, NovaBuds or a power bank on it with
--   ox_target (or the [E] prompt). Only its owner can pick it up again, when it is empty.

nova_car_charger = {
    name = 'nova_car_charger',
    label = 'Car Phone Charger',
    weight = 120,
    type = 'item',
    image = 'nova_car_charger.png',
    unique = false,
    useable = true,
    shouldClose = true,
    description = 'Use it in a vehicle with the engine running to charge your phone or NovaBuds.',
},

nova_power_bank = {
    name = 'nova_power_bank',
    label = 'Power Bank',
    weight = 250,
    type = 'item',
    image = 'nova_power_bank.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Charges your phone or NovaBuds on the go. Recharge it on a charging pad.',
},

nova_charging_pad = {
    name = 'nova_charging_pad',
    label = 'Charging Pad',
    weight = 400,
    type = 'item',
    image = 'nova_charging_pad.png',
    unique = false,
    useable = true,
    shouldClose = true,
    description = 'Place it, then put a phone, NovaBuds or a power bank on it to charge.',
},

-- =========================================================================
-- OPTIONAL: Wi-Fi router (config/wifi.lua)
-- =========================================================================
-- A picked-up router keeps its network, password and package in its metadata
-- `routerId`. Using it starts placement (wd_phone.useRouter): scroll rotates,
-- E places, Backspace cancels. Only its owner can pick it up again.

nova_router = {
    name = 'nova_router',
    label = 'Wi-Fi Router',
    weight = 800,
    type = 'item',
    image = 'nova_router.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Place it to share Wi-Fi nearby. Manage it in the Routers app.',
},

-- =========================================================================
-- OPTIONAL: NovaBuds and Nova Studio (config/novapods.lua)
-- =========================================================================
-- Each pair has its own `novapodsId`, pairing and battery. Using one puts it
-- on / takes it off (wd_phone.useNovaPods). Pair them to a phone in
-- Settings -> Bluetooth.

novapods_white = {
    name = 'novapods_white',
    label = 'NovaBuds · White',
    weight = 60,
    type = 'item',
    image = 'novapods_white.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Wireless earbuds. Use to put them in; pair them in Settings -> Bluetooth.',
},

novapods_black = {
    name = 'novapods_black',
    label = 'NovaBuds · Black',
    weight = 60,
    type = 'item',
    image = 'novapods_black.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Wireless earbuds. Use to put them in; pair them in Settings -> Bluetooth.',
},

novapods_max_silver = {
    name = 'novapods_max_silver',
    label = 'Nova Studio · Silver',
    weight = 380,
    type = 'item',
    image = 'novapods_max_silver.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
},

novapods_max_spacegray = {
    name = 'novapods_max_spacegray',
    label = 'Nova Studio · Space Gray',
    weight = 380,
    type = 'item',
    image = 'novapods_max_spacegray.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
},

novapods_max_skyblue = {
    name = 'novapods_max_skyblue',
    label = 'Nova Studio · Sky Blue',
    weight = 380,
    type = 'item',
    image = 'novapods_max_skyblue.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
},

novapods_max_pink = {
    name = 'novapods_max_pink',
    label = 'Nova Studio · Pink',
    weight = 380,
    type = 'item',
    image = 'novapods_max_pink.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
},

novapods_max_green = {
    name = 'novapods_max_green',
    label = 'Nova Studio · Green',
    weight = 380,
    type = 'item',
    image = 'novapods_max_green.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Over-ear wireless headphones. Worn around the neck; use to put them on. Pair them in Settings -> Bluetooth.',
},

-- =========================================================================
-- OPTIONAL: Bluetooth speakers (config/speakers.lua)
-- =========================================================================
-- Each speaker has its own `speakerId`, pairing and battery. Using one sets it
-- down (wd_phone.useSpeaker). Pair it in Settings -> Bluetooth, then pick it as
-- Music's output (Now Playing or Control Center). The Nova Mini also plays
-- from your hip while you carry it.

nova_speaker_mini = {
    name = 'nova_speaker_mini',
    label = 'Nova Mini Speaker',
    weight = 450,
    type = 'item',
    image = 'nova_speaker_mini.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Portable Bluetooth speaker. Plays your phone\'s music while you carry it, or use it to set it down. Pair it in Settings -> Bluetooth.',
},

nova_speaker_boom = {
    name = 'nova_speaker_boom',
    label = 'Nova Boom Speaker',
    weight = 3200,
    type = 'item',
    image = 'nova_speaker_boom.png',
    unique = true,
    useable = true,
    shouldClose = true,
    description = 'Loud party speaker. Use it to set it down, then play your phone\'s music on it. Pair it in Settings -> Bluetooth.',
},

-- =========================================================================
-- OPTIONAL: SD card (Config.MobileShops.sdCard)
-- =========================================================================
-- A blank memory card, sold at the phone stores. Not used on its own.

sd_card = {
    name = 'sd_card',
    label = 'SD Card',
    weight = 10,
    type = 'item',
    image = 'sd_card.png',
    unique = false,
    useable = false,
    shouldClose = true,
    description = 'A blank memory card.',
},

-- =========================================================================
-- OPTIONAL: Shade (config/shade.lua)
-- =========================================================================
-- - shade_sdcard: Shade is not in the Nova Store. Using the card installs
--   Shade on the phone you are using and uses it up (Config.Shade.install).
-- - shade_key: a physical key to a Shade room, cut by its owner in the app.
--   unique = true: each key carries its room's signed `shadeKeyId`. Carrying
--   it opens that room; it is not used.

shade_sdcard = {
    name = 'shade_sdcard',
    label = 'Unmarked SD Card',
    weight = 10,
    type = 'item',
    image = 'shade_sdcard.png',
    unique = false,
    useable = true,
    shouldClose = true,
    description = 'A blank-looking memory card. Something is on it.',
},

shade_key = {
    name = 'shade_key',
    label = 'Shade Key',
    weight = 15,
    type = 'item',
    image = 'shade_key.png',
    unique = true,
    useable = false,
    shouldClose = true,
    description = 'A small encrypted key. It opens a room on Shade.',
},

-- =========================================================================
-- OPTIONAL: Racing (config/racing.lua)
-- =========================================================================
-- - racing_usb: carrying it opens the underground races in the Racing app
--   (Config.Racing.underground.item, re-checked on every underground list,
--   join and bet). It is not used. Sold by the black-market dealer.

racing_usb = {
    name = 'racing_usb',
    label = 'Racing USB',
    weight = 20,
    type = 'item',
    image = 'racing_usb.png',
    unique = false,
    useable = false,
    shouldClose = true,
    description = 'A scuffed USB-C flash drive that plugs into a phone, with a checkered flag scratched into it.',
},
```

You can also [download the qb-inventory items](reference/wd_phone/migrations/qb_inventory_items.lua).

## 4. Copy the item images

Copy every `.png` file from `wd_phone/inventory/` into your inventory's image folder. The item entries above already point to these images.

| Inventory | Image folder |
| --- | --- |
| ox_inventory | `ox_inventory/web/images/` |
| qb-inventory | `qb-inventory/html/images/` |

There is one image per phone colour, one for all SIM cards (`nova_sim.png`), and one for each accessory.

## 5. Check the phone UI

Confirm that `wd_phone/web/dist/index.html`, the `web/dist/assets` folder and the `web/dist/locales` folder are present. The interface ships ready to use: no Node.js, npm or build step is needed. If any of them are missing, extract the resource again from your download.

## 6. Paste the startup configuration

Open `server.cfg` and paste the entire block below. Keep only the framework and inventory you use: replace `qbx_core` with `qb-core` or `es_extended`, and `ox_inventory` with `qb-inventory`, as needed. If these resources already have individual `ensure` lines, move those lines into this order instead of adding duplicates. Keep your database connection and the rest of your server configuration.

```cfg server.cfg — Wolfden Phone startup block
ensure oxmysql
ensure ox_lib
ensure qbx_core
ensure ox_inventory
ensure pma-voice
ensure ox_target
ensure wd_phone

add_ace group.admin wd_phone.admin allow
```

Wolfden Phone picks the first started framework and inventory automatically, so both must start before `wd_phone`. To force one, set `Config.Framework.name` and `Config.Inventory` in `config/shared.lua`, or add these lines to `server.cfg`:

```cfg server.cfg — optional
setr wd_phone:framework "qbox"
setr wd_phone:inventory "ox_inventory"
```

The framework can be `auto`, `qbox`, `qbcore` or `esx`, and the inventory `auto`, `ox_inventory` or `qb-inventory`.

If you use a banking or dispatch resource, start it before `wd_phone` as well. If you deliberately installed without `ox_target`, remove only its `ensure` line; players then get an **[E]** prompt instead. Call audio and the Radio app require `pma-voice`.

The ACE line gives your existing `group.admin` members the `phoneadmin` command, the in-game admin menu and the cell tower editor. It does not add people to that group or replace your server's existing admin assignments. See [Admin tools](#/docs/wd_phone/admin) for what they can do and the separate moderator permissions.

### Item signatures

Phones, SIMs and accessories carry a server signature in their item metadata, so a forged or edited item never opens someone else's phone. Nothing needs to be set up: the signing key is created on the first start and kept in the database, and new items from `/giveitem`, shops or other resources are signed the first time they are used. To set your own key before the first start, or if the key ever changes, see [Item signatures](#/docs/wd_phone/admin?section=item-signatures).

## 7. Connect a media host

The camera saves photos and videos to a media host, and nothing is saved until one is configured. The quickest option is a [Fivemanage](https://fivemanage.com) account: create a media API key and add it to `server.cfg` **above** `ensure wd_phone`:

```cfg server.cfg
set FIVEMANAGE_MEDIA_API_KEY "your-key"
```

Replace `your-key` with your own key. To store files on your own server instead, follow [Camera & media hosting](#/docs/wd_phone/media).

Video calls and Clips Live also need a relay. You can set that up later with [Video calls](#/docs/wd_phone/video-calls); voice calls work without it.

Keep keys and secrets in `server.cfg` with `set`, never `setr`, rather than in the config files, so an update never overwrites them. Save `server.cfg` and restart the server. Check the console for missing dependencies or SQL errors before continuing.

## 8. Give yourself a phone

Join the server with an account allowed to use your inventory's give-item command. Replace `<id>` with your actual player server ID, then run this in game:

```text
/giveitem <id> nova_phone_ocean 1
```

Any phone item from step 3 works, such as `nova_business_rosegold` or `nova_secure`. Players can also buy phones and accessories at the phone stores, Digital Den, Wolfden Kiosk and an after-hours back room by default; move them to your map and set their prices in `config/mobile_shops_server.lua`.

Open your inventory and use the phone, or press **M**. The phone starts its first setup: sign in to or create a **Nova Account** (or skip it), restore a backup or set up as new, choose the appearance and wallpaper, decide whether your voice can be heard in other players' videos, and set an optional PIN. If the key is already bound differently, change it under FiveM → Settings → Key Bindings → FiveM.

## 9. Activate a SIM

A new phone starts with **No SIM**. Choose one of these setup paths.

### Buy and activate a SIM in game

1. Visit **Nova Mobile** at its map blip. The default shop is at `-655.0, -854.0, 24.5`.
2. Interact with the shop using ox_target or the displayed prompt.
3. Buy a blank SIM, then use the shop's activation option and choose **Nova Basic**.
4. Open the phone's **SIM Manager**, select the activated SIM from your inventory and install it.
5. Enable the line and confirm that your phone number and carrier appear.

### Give an activated SIM for testing

On a test server, set `Config.Debug = true` in `config/shared.lua` and restart the resource. Then, from the **server console**, replace `<id>` with the same player's server ID and run:

```text
wd_phone_givesim <id> nova_sim nova_mobile prepaid_basic
```

This creates an activated Nova Mobile SIM on the Nova Basic plan and prints its number. Open SIM Manager and install it. Set `Config.Debug` back to `false` before going live: the command only exists while it is on.

Installing the SIM removes the physical card from the inventory; removing it from the phone returns it.

## 10. Verify the installation

About five seconds after the resource starts, the console prints a short boot self-check: a count of passed checks and one line for each warning, with its fix. Then copy these commands into the **server console**, running them in order:

```text
phoneadmin schema
phoneadmin selftest report
```

`phoneadmin schema` should answer that the schema matches `migrations/install.sql`. The report is read-only: it lists the framework and inventory it selected, every item group as registered, the media host, the video call relay, the language files and the server secret. With Fivemanage configured, it shows `[PASS] media host fivemanage`. Fix any `[WARN]` line you did not expect.

For the full check, run `phoneadmin selftest`. It creates throwaway test phones, runs the real server code on them, then removes them, and must finish with `0 failed`. No player's data is touched. See [Check your installation](#/docs/wd_phone/admin?section=check-your-installation).

Then check the phone with two players:

1. Give each player a phone and activate a SIM using the steps above.
2. Save each other's displayed numbers in the Phone app's Contacts tab.
3. Send a message and confirm it arrives.
4. Place a call and confirm both players can hear each other.
5. Close one player's phone and call it again to check incoming calls while closed.
6. Take a photo and confirm the "Photo saved" notification appears.

Use [Troubleshooting](#/docs/wd_phone/troubleshooting) if a check fails. You can personalize prices, apps and appearance afterwards in [Configuration](#/docs/wd_phone/configuration).

## Optional: the Browser

Your download includes a second zip, `nova_browser-<version>.zip`, with the Browser app as a separate resource. Copy its `nova_browser` folder into `resources` and add `ensure nova_browser` **after** `ensure wd_phone`. Players then download Browser from the Nova Store.

Browser shows in-game sites that your resources register. Real websites load only from the addresses you list in `allowedOrigins` in `nova_browser/config.lua`, which is empty by default because a website can see the player's IP address. See `nova_browser/README.md` in that resource.