📜SQL Schema Documentation

This section describes the database tables used by the Collectibles Pickup Script. These tables manage pickups, player progress, and rewards, ensuring data is stored and retrieved efficiently.

---

1. Table: player_pickups 🧍‍♂️📦

Tracks the progress of players collecting pickups.

Schema Overview:

CREATE TABLE IF NOT EXISTS player_pickups (
    id INT AUTO_INCREMENT PRIMARY KEY,
    identifier VARCHAR(50) NOT NULL,
    char_id VARCHAR(50) DEFAULT NULL,
    pickups TEXT NOT NULL,
    UNIQUE KEY unique_pickup (identifier, char_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Columns:

Column	Type	Description
🆔 id	INT	Unique record ID (auto-incremented).
🔑 identifier	VARCHAR(50)	Player's unique identifier (e.g., license or SteamID).
👤 char_id	VARCHAR(50)	Character-specific ID (for multicharacter systems, optional).
📋 pickups	TEXT	JSON-encoded data of collected pickups.

Indexes:

• unique_pickup: Prevents duplicate entries for the same player and character.

Usage Example:

• Track which pickups a player has collected.

• Enable server-wide monitoring of player progress.

---

2. Table: collectables_pickups 🌍📍

Manages the configuration and metadata for pickups placed in the game world.

Schema Overview:

CREATE TABLE IF NOT EXISTS collectables_pickups (
    id VARCHAR(50) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(50) UNIQUE NOT NULL,
    model VARCHAR(100) NOT NULL,
    x FLOAT NOT NULL,
    y FLOAT NOT NULL,
    z FLOAT NOT NULL,
    heading FLOAT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Columns:

Column	Type	Description
🆔 id	VARCHAR(50)	Unique ID for the pickup (automatically generated UUID).
🏷️ name	VARCHAR(50)	Custom name for the pickup.
📦 model	VARCHAR(100)	Model name associated with the pickup (e.g., prop name).
📍 x, y, z	FLOAT	World coordinates for the pickup's position.
↩️ heading	FLOAT	Direction the pickup is facing.
🕒 created_at	TIMESTAMP	Date and time the pickup was created.
✍️ created_by	VARCHAR(100)	Identifier of the user who created the pickup.

Usage Example:

• Define pickups with unique positions, models, and orientations.

• Monitor when and by whom pickups are created.

---

3. Table: collectables_prizes 🎁🏆

Defines rewards that players can earn by collecting pickups.

Schema Overview:

CREATE TABLE IF NOT EXISTS collectables_prizes (
    id VARCHAR(50) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(50) UNIQUE NOT NULL,
    needed INT NOT NULL,
    type VARCHAR(50) NOT NULL,
    item_name VARCHAR(100) NOT NULL,
    amount INT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    created_by VARCHAR(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Columns:

Column	Type	Description
🆔 id	VARCHAR(50)	Unique ID for the prize (automatically generated UUID).
🏷️ name	VARCHAR(50)	Custom name for the prize.
🎯 needed	INT	Number of collectables required to claim this prize.
🎁 type	VARCHAR(50)	Type of reward (e.g., money, item, vehicle).
📦 item_name	VARCHAR(100)	Name of the item, account (bank), or vehicle model.
🔢 amount	INT	Quantity awarded (if applicable).
🕒 created_at	TIMESTAMP	Date and time the prize was created.
✍️ created_by	VARCHAR(100)	Identifier of the user who created the prize.

Usage Example:

• Set rewards like money, items, or vehicles for players.

• Log prize creation to track server activity.

---

Quick Reference Table 📑

Table Name	Purpose
🧍‍♂️ player_pickups	Tracks player progress with collected pickups.
🌍 collectables_pickups	Stores configuration and metadata for placed pickups.
🎁 collectables_prizes	Manages prizes and rewards based on collected pickups.

---

Example Queries

1. Insert a New Pickup 📍

INSERT INTO collectables_pickups (name, model, x, y, z, heading, created_by)
VALUES ('Golden Coin', 'prop_gold_coin', 100.0, 200.0, 30.0, 180.0, 'admin');

2. Add a New Prize 🎁

INSERT INTO collectables_prizes (name, needed, type, item_name, amount, created_by)
VALUES ('Gold Reward', 10, 'money', 'bank', 5000, 'admin');

3. Record Player Progress 🧍‍♂️

INSERT INTO player_pickups (identifier, char_id, pickups)
VALUES ('license:abcdef1234567890', 'char1', '[{"pickup_id":"12345","timestamp":"2025-01-01T12:00:00Z"}]')
ON DUPLICATE KEY UPDATE pickups = VALUES(pickups);

---

Tips and Best Practices 🛠️

1. Optimize Queries: Use indexes to improve database performance, especially for high-traffic servers.

2. Backup Data: Regularly back up your database to prevent data loss.

3. Consistency: Ensure identifiers like identifier and char_id match between your database and server configurations.

4. Audit Logs: Use the created_by and created_at fields to track changes and maintain accountability.

---