160 lines
7.0 KiB
SQL
160 lines
7.0 KiB
SQL
SET NAMES utf8mb4;
|
|
SET time_zone = '+00:00';
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_characters` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`owner_license` VARCHAR(80) NOT NULL,
|
|
`slot` TINYINT UNSIGNED NOT NULL,
|
|
`first_name` VARCHAR(32) NOT NULL,
|
|
`last_name` VARCHAR(32) NOT NULL,
|
|
`date_of_birth` DATE NOT NULL,
|
|
`sex` ENUM('m','f','x') NOT NULL DEFAULT 'x',
|
|
`model` VARCHAR(64) NOT NULL DEFAULT 'mp_m_freemode_01',
|
|
`phone_number` VARCHAR(16) NULL,
|
|
`cash` BIGINT NOT NULL DEFAULT 150,
|
|
`position` LONGTEXT NULL,
|
|
`metadata` LONGTEXT 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_devx_character_slot` (`owner_license`, `slot`),
|
|
UNIQUE KEY `uq_devx_character_phone` (`phone_number`),
|
|
KEY `idx_devx_character_owner` (`owner_license`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_bank_accounts` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`account_number` VARCHAR(24) NOT NULL,
|
|
`account_name` VARCHAR(80) NOT NULL,
|
|
`account_type` ENUM('personal','business','government') NOT NULL DEFAULT 'personal',
|
|
`balance` BIGINT NOT NULL DEFAULT 500,
|
|
`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_devx_account_number` (`account_number`),
|
|
KEY `idx_devx_account_character` (`character_id`),
|
|
CONSTRAINT `fk_devx_account_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_bank_transactions` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`account_id` BIGINT UNSIGNED NOT NULL,
|
|
`direction` ENUM('credit','debit') NOT NULL,
|
|
`amount` BIGINT UNSIGNED NOT NULL,
|
|
`balance_after` BIGINT NOT NULL,
|
|
`counterparty` VARCHAR(80) NULL,
|
|
`reference` VARCHAR(140) NOT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_devx_transaction_account_date` (`account_id`, `created_at`),
|
|
CONSTRAINT `fk_devx_transaction_account`
|
|
FOREIGN KEY (`account_id`) REFERENCES `devx_bank_accounts` (`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_properties` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`property_key` VARCHAR(64) NOT NULL,
|
|
`label` VARCHAR(80) NOT NULL,
|
|
`price` BIGINT UNSIGNED NOT NULL,
|
|
`property_type` ENUM('apartment','house','business') NOT NULL DEFAULT 'apartment',
|
|
`entry_data` LONGTEXT NOT NULL,
|
|
`interior_data` LONGTEXT NOT 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_devx_property_key` (`property_key`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_property_owners` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`property_id` BIGINT UNSIGNED NOT NULL,
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`purchase_price` BIGINT UNSIGNED NOT NULL,
|
|
`purchased_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uq_devx_property_owner_property` (`property_id`),
|
|
KEY `idx_devx_property_owner_character` (`character_id`),
|
|
CONSTRAINT `fk_devx_owner_property`
|
|
FOREIGN KEY (`property_id`) REFERENCES `devx_properties` (`id`)
|
|
ON DELETE CASCADE,
|
|
CONSTRAINT `fk_devx_owner_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_phone_contacts` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`contact_name` VARCHAR(64) NOT NULL,
|
|
`phone_number` VARCHAR(24) NOT NULL,
|
|
`contact_type` VARCHAR(32) NOT NULL DEFAULT 'player',
|
|
`metadata` LONGTEXT NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_devx_phone_contact_character` (`character_id`),
|
|
CONSTRAINT `fk_devx_contact_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`)
|
|
ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_bans` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`license` VARCHAR(80) NOT NULL,
|
|
`player_name` VARCHAR(96) NULL,
|
|
`reason` VARCHAR(255) NOT NULL,
|
|
`banned_by` VARCHAR(96) NOT NULL,
|
|
`expires_at` DATETIME NULL,
|
|
`created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
`revoked_at` DATETIME NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `idx_devx_bans_license` (`license`),
|
|
KEY `idx_devx_bans_active` (`license`, `revoked_at`, `expires_at`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_heist_progress` (
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`drill_done` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`card_done` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`vehicle_done` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`character_id`),
|
|
CONSTRAINT `fk_devx_heist_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_casino_daily` (
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`last_spin` DATETIME NULL,
|
|
`total_spins` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (`character_id`),
|
|
CONSTRAINT `fk_devx_casino_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_casino_wallets` (
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`chips` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`character_id`),
|
|
CONSTRAINT `fk_devx_casino_wallet_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS `devx_club_relations` (
|
|
`character_id` BIGINT UNSIGNED NOT NULL,
|
|
`relation_key` VARCHAR(32) NOT NULL,
|
|
`satisfaction` TINYINT UNSIGNED NOT NULL DEFAULT 0,
|
|
`contact_unlocked` TINYINT(1) NOT NULL DEFAULT 0,
|
|
`updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`character_id`, `relation_key`),
|
|
CONSTRAINT `fk_devx_club_character`
|
|
FOREIGN KEY (`character_id`) REFERENCES `devx_characters` (`id`) ON DELETE CASCADE
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|