CREATE TABLE IF NOT EXISTS `nord_inventory_custom_categories` (
    `name` VARCHAR(32) NOT NULL,
    `label` VARCHAR(64) NOT NULL,
    `icon` VARCHAR(64) NOT NULL DEFAULT 'fa-folder',
    `sort_order` INT NOT NULL DEFAULT 100,
    `created_by` VARCHAR(96) NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS `nord_inventory_storage` (
  `inv_key` VARCHAR(160) NOT NULL,
  `inv_type` VARCHAR(32) NOT NULL,
  `owner` VARCHAR(96) NULL,
  `slots` INT NOT NULL DEFAULT 40,
  `max_weight` INT NOT NULL DEFAULT 80000,
  `data` LONGTEXT NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`inv_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_custom_items` (
  `name` VARCHAR(64) NOT NULL,
  `definition` LONGTEXT NOT NULL,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `created_by` VARCHAR(96) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_item_versions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_name` VARCHAR(64) NOT NULL,
  `definition` LONGTEXT NOT NULL,
  `changed_by` VARCHAR(96) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_item_name` (`item_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `nord_inventory_disabled_reference_items` (
  `name` VARCHAR(64) NOT NULL,
  `disabled_by` VARCHAR(96) NULL,
  `disabled_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_preferences` (
  `identifier` VARCHAR(96) NOT NULL,
  `preferences` LONGTEXT NOT NULL,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `txid` VARCHAR(72) NOT NULL,
  `source_identifier` VARCHAR(96) NULL,
  `action` VARCHAR(32) NOT NULL,
  `from_inventory` VARCHAR(160) NULL,
  `to_inventory` VARCHAR(160) NULL,
  `item_name` VARCHAR(64) NULL,
  `amount` INT NULL,
  `payload` LONGTEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_txid` (`txid`),
  INDEX `idx_created_at` (`created_at`),
  INDEX `idx_item_name_tx` (`item_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_vehicle_categories` (
  `name` VARCHAR(48) NOT NULL,
  `label` VARCHAR(80) NOT NULL,
  `max_weight` INT NOT NULL DEFAULT 120000,
  `max_slots` INT NOT NULL DEFAULT 50,
  `glovebox_max_weight` INT NOT NULL DEFAULT 15000,
  `glovebox_slots` INT NOT NULL DEFAULT 10,
  `sort_order` INT NOT NULL DEFAULT 100,
  `source` VARCHAR(24) NOT NULL DEFAULT 'auto',
  `updated_by` VARCHAR(96) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_vehicle_models` (
  `model` VARCHAR(64) NOT NULL,
  `model_hash` BIGINT NOT NULL DEFAULT 0,
  `label` VARCHAR(96) NOT NULL,
  `brand` VARCHAR(64) NULL,
  `detected_category` VARCHAR(48) NOT NULL DEFAULT 'other',
  `category_override` VARCHAR(48) NULL,
  `max_weight_override` INT NULL,
  `max_slots_override` INT NULL,
  `glovebox_max_weight_override` INT NULL,
  `glovebox_slots_override` INT NULL,
  `trunk_side_override` VARCHAR(8) NULL,
  `trunk_offset_x` DECIMAL(9,4) NULL,
  `trunk_offset_y` DECIMAL(9,4) NULL,
  `trunk_offset_z` DECIMAL(9,4) NULL,
  `trunk_door_override` TINYINT NULL,
  `source` VARCHAR(24) NOT NULL DEFAULT 'game',
  `framework` VARCHAR(24) NOT NULL DEFAULT 'standalone',
  `manual` TINYINT(1) NOT NULL DEFAULT 0,
  `enabled` TINYINT(1) NOT NULL DEFAULT 1,
  `updated_by` VARCHAR(96) NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`model`),
  INDEX `idx_vehicle_hash` (`model_hash`),
  INDEX `idx_vehicle_category` (`detected_category`),
  INDEX `idx_vehicle_enabled` (`enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `nord_inventory_audit_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `identifier` VARCHAR(96) NULL,
  `role` VARCHAR(24) NULL,
  `action` VARCHAR(64) NOT NULL,
  `subject` VARCHAR(160) NULL,
  `payload` LONGTEXT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_inventory_audit_created` (`created_at`),
  INDEX `idx_inventory_audit_action` (`action`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_backups` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(96) NOT NULL,
  `created_by` VARCHAR(96) NULL,
  `payload` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_inventory_backup_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_admin_access` (
  `identifier` VARCHAR(96) NOT NULL,
  `role` VARCHAR(16) NOT NULL DEFAULT 'admin',
  `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- v2.0.0 Operations / Forensics Center
CREATE TABLE IF NOT EXISTS `nord_inventory_snapshots` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `inv_key` VARCHAR(160) NOT NULL,
  `inv_type` VARCHAR(32) NOT NULL,
  `owner` VARCHAR(96) NULL,
  `slots` INT NOT NULL,
  `max_weight` INT NOT NULL,
  `reason` VARCHAR(96) NULL,
  `created_by` VARCHAR(96) NULL,
  `data` LONGTEXT NOT NULL,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_snapshot_key_created` (`inv_key`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_forensics_alerts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `severity` VARCHAR(16) NOT NULL DEFAULT 'warning',
  `code` VARCHAR(48) NOT NULL,
  `identity_value` VARCHAR(160) NULL,
  `item_name` VARCHAR(64) NULL,
  `payload` LONGTEXT 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`),
  INDEX `idx_forensics_status_created` (`status`, `created_at`),
  INDEX `idx_forensics_identity` (`identity_value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `nord_inventory_placed_items` (
  `id` VARCHAR(40) NOT NULL,
  `inv_key` VARCHAR(160) NOT NULL,
  `owner_identifier` VARCHAR(96) NULL,
  `x` DOUBLE NOT NULL,
  `y` DOUBLE NOT NULL,
  `z` DOUBLE NOT NULL,
  `model` VARCHAR(96) 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 `uniq_placed_inv_key` (`inv_key`),
  INDEX `idx_placed_owner` (`owner_identifier`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;