door_sql.sql

-- ============================================
-- RDE Doors - Database Schema v4.0.0
-- Compatible with ox_core v3
-- ============================================
-- ⚠️  This file is for REFERENCE only.
-- The resource creates and migrates the schema automatically on startup.
-- You do NOT need to run this manually on a fresh install.
--
-- AUTO-MIGRATION:
-- ✓ v2.x → v3.0.0: 'double_door_data' column added automatically
-- ✓ v3.0.0 → v4.0.0: passcode, auto, door_rate, lockpick, lockpick_difficulty,
--                    hide_ui, hold_open, lock_sound, unlock_sound, groups_data,
--                    items_data columns added automatically
--
-- DOWNGRADE COMPATIBILITY:
-- ✓ v4.0.0 → v3.0.0 is safe — v3 ignores the new columns. Doors created under
--   v4 that use only old features still work. Doors using new features (e.g.
--   lockpick, passcode) revert to v3 behavior (no lockpick, no passcode).
--
-- ZERO DATA LOSS GUARANTEED on both directions.
-- ============================================

CREATE TABLE IF NOT EXISTS `rde_owned_doors` (
    `id`                  VARCHAR(50)  PRIMARY KEY,
    `type`                VARCHAR(20)  DEFAULT 'single',
    `name`                VARCHAR(100) NOT NULL,
    `coords`              LONGTEXT     NOT NULL,
    `model`               VARCHAR(100) NOT NULL DEFAULT '',
    `model_hash`          VARCHAR(50),
    `locked`              TINYINT(1)   DEFAULT 1,
    `auth`                LONGTEXT     DEFAULT '[]',
    `autolock`            INT          DEFAULT 0,
    `items`               LONGTEXT     DEFAULT '[]',
    `heading`             FLOAT        DEFAULT 0.0,
    `maxDistance`         FLOAT        DEFAULT 2.5,
    `owner_charid`        VARCHAR(50),
    `owner_name`          VARCHAR(100),
    `price`               INT          DEFAULT 0,
    `access_list`         LONGTEXT     DEFAULT '[]',
    `group_id`            VARCHAR(50),
    -- v3.0.0
    `double_door_data`    LONGTEXT     DEFAULT NULL,
    -- v4.0.0 — ox_doorlock feature parity
    `passcode`            VARCHAR(100) DEFAULT NULL,    -- NEW: anyone with code can unlock
    `auto`                TINYINT(1)   DEFAULT 0,       -- NEW: sliding/garage/automatic flag
    `door_rate`           FLOAT        DEFAULT NULL,    -- NEW: movement speed (NULL = derived from auto)
    `lockpick`            TINYINT(1)   DEFAULT 0,       -- NEW: door is lockpickable
    `lockpick_difficulty` LONGTEXT     DEFAULT NULL,    -- NEW: JSON, custom skillcheck difficulty
    `hide_ui`             TINYINT(1)   DEFAULT 0,       -- NEW: hide 3D text & sprite indicators
    `hold_open`           TINYINT(1)   DEFAULT 0,       -- NEW: keep door physically open when unlocked
    `lock_sound`          VARCHAR(100) DEFAULT NULL,    -- NEW: custom lock sound name
    `unlock_sound`        VARCHAR(100) DEFAULT NULL,    -- NEW: custom unlock sound name
    `groups_data`         LONGTEXT     DEFAULT NULL,    -- NEW: JSON {groupName: minGrade}
    `items_data`          LONGTEXT     DEFAULT NULL,    -- NEW: JSON [{name, metadata, remove}]
    `created_at`          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    `updated_at`          TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `rde_door_groups` (
    `id`         VARCHAR(50)  PRIMARY KEY,
    `name`       VARCHAR(100) NOT NULL,
    `doors`      LONGTEXT     DEFAULT '[]',
    `created_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Performance indexes
CREATE INDEX IF NOT EXISTS idx_owner_charid ON rde_owned_doors(owner_charid);
CREATE INDEX IF NOT EXISTS idx_locked       ON rde_owned_doors(locked);
CREATE INDEX IF NOT EXISTS idx_price        ON rde_owned_doors(price);
CREATE INDEX IF NOT EXISTS idx_type         ON rde_owned_doors(type);
CREATE INDEX IF NOT EXISTS idx_auto         ON rde_owned_doors(auto);
CREATE INDEX IF NOT EXISTS idx_lockpick     ON rde_owned_doors(lockpick);

-- ============================================
-- EXAMPLE DOORS (optional — uses INSERT IGNORE so safe to run multiple times)
-- ============================================

-- Single door example
INSERT IGNORE INTO `rde_owned_doors` (`id`, `name`, `coords`, `model`, `locked`, `auth`, `price`)
VALUES ('door_police_main', 'LSPD Main Entrance',
    '{"x":434.7,"y":-981.91,"z":30.69}', 'v_ilev_ph_gendoor004', 1, '["police"]', 0);

-- Double door example
INSERT IGNORE INTO `rde_owned_doors`
    (`id`, `name`, `type`, `coords`, `model`, `locked`, `auth`, `price`, `double_door_data`)
VALUES (
    'door_bank_double_main',
    'Fleeca Bank Double Entrance',
    'double',
    '{"x":311.48,"y":-284.49,"z":54.16}',
    '',
    1, '[]', 0,
    '{"door_a":{"model":"v_ilev_bk_door","coords":{"x":311.28,"y":-284.49,"z":54.16},"heading":0.0},"door_b":{"model":"v_ilev_bk_door","coords":{"x":311.68,"y":-284.49,"z":54.16},"heading":180.0}}'
);

-- v4.0.0 example: Sliding/Automatic door with lockpick + passcode
INSERT IGNORE INTO `rde_owned_doors`
    (`id`, `name`, `type`, `coords`, `model`, `locked`, `auto`, `door_rate`, `lockpick`,
     `lockpick_difficulty`, `passcode`, `autolock`, `groups_data`)
VALUES (
    'door_warehouse_sliding',
    'Sandy Shores Warehouse Sliding Door',
    'sliding',
    '{"x":1735.5,"y":3829.2,"z":34.85}',
    'v_ilev_shrf2door',
    1, 1, 0.0, 1,
    '["easy","medium","hard"]',
    '1337',
    30,
    '{"police":2,"sheriff":3}'
);

-- v4.0.0 example: Item with metadata (only "police_hq" branded badge works)
INSERT IGNORE INTO `rde_owned_doors`
    (`id`, `name`, `coords`, `model`, `locked`, `items_data`)
VALUES (
    'door_chief_office',
    'Police Chief Office',
    '{"x":443.7,"y":-980.5,"z":30.69}',
    'v_ilev_ph_gendoor002',
    1,
    '[{"name":"badge","metadata":"police_hq","remove":false}]'
);