-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `asset_management_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `activity_logs`
--

CREATE TABLE `activity_logs` (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `action` varchar(50) DEFAULT NULL,
  `target_id` int(11) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `activity_logs`
--

INSERT INTO `activity_logs` (`id`, `user_id`, `username`, `action`, `target_id`, `details`, `created_at`) VALUES
(1, 1, 'مستخدم', 'تعديل', 11, 'تم تعديل بيانات الأصل: جهاز كمبيوتر', '2025-11-28 20:43:49'),
(2, NULL, 'System Agent', 'اكتشاف', NULL, 'تم اكتشاف جهاز جديد تلقائياً: MRITPCWORK', '2025-11-29 18:40:31');

-- --------------------------------------------------------

--
-- Table structure for table `assets`
--

CREATE TABLE `assets` (
  `id` int(11) NOT NULL,
  `code` varchar(50) NOT NULL COMMENT 'رمز الباركود أو الرقم المرجعي',
  `name` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `subcategory` varchar(100) DEFAULT NULL,
  `branch` varchar(100) NOT NULL,
  `location` varchar(255) DEFAULT NULL,
  `department` varchar(100) DEFAULT NULL,
  `owner` varchar(100) DEFAULT NULL,
  `value` decimal(15,2) DEFAULT 0.00,
  `status` enum('نشط','صيانة','خارج الخدمة') DEFAULT 'نشط',
  `criticality` enum('منخفضة','متوسطة','عالية','عالية جداً') DEFAULT 'منخفضة',
  `manufacturer` varchar(100) DEFAULT NULL,
  `model` varchar(100) DEFAULT NULL,
  `serial_number` varchar(100) DEFAULT NULL,
  `mac_address` varchar(50) DEFAULT NULL,
  `specifications` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`specifications`)),
  `warranty_expiry` date DEFAULT NULL,
  `purchase_date` date DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_discovered` datetime DEFAULT NULL,
  `discovery_method` varchar(50) DEFAULT 'يدوي',
  `ip_address` varchar(50) DEFAULT NULL,
  `operating_system` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `assets`
--

INSERT INTO `assets` (`id`, `code`, `name`, `category`, `subcategory`, `branch`, `location`, `department`, `owner`, `value`, `status`, `criticality`, `manufacturer`, `model`, `serial_number`, `mac_address`, `specifications`, `warranty_expiry`, `purchase_date`, `notes`, `created_by`, `created_at`, `updated_at`, `last_discovered`, `discovery_method`, `ip_address`, `operating_system`) VALUES
(11, 'SN13544', 'جهاز كمبيوتر', 'خوادم', NULL, 'الرياض', 'الرياض', NULL, NULL, 15000.00, 'نشط', 'منخفضة', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 1, '2025-11-28 20:34:47', '2025-11-28 20:43:49', NULL, 'يدوي', NULL, NULL),
(12, 'PC-631146', 'MRITPCWORK', 'أجهزة كمبيوتر', NULL, 'الرياض', NULL, NULL, NULL, 0.00, 'نشط', 'منخفضة', 'HP', 'HP EliteDesk 800 G4 DM 65W', '8CC8521XQS', '3c:6a:a7:2a:ee:d7', '{\"cpu\":\"Intel Core™ i7-8700 (12 Cores)\",\"ram\":\"15.8 GB\",\"architecture\":\"x64\"}', NULL, NULL, NULL, NULL, '2025-11-29 18:40:31', '2025-11-29 18:40:31', NULL, 'Agent', '192.168.100.10', 'Microsoft Windows 11 Pro 10.0.26100');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `assets`
--
ALTER TABLE `assets`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `code` (`code`),
  ADD UNIQUE KEY `mac_address` (`mac_address`),
  ADD KEY `created_by` (`created_by`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `activity_logs`
--
ALTER TABLE `activity_logs`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `assets`
--
ALTER TABLE `assets`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `activity_logs`
--
ALTER TABLE `activity_logs`
  ADD CONSTRAINT `activity_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `assets`
--
ALTER TABLE `assets`
  ADD CONSTRAINT `assets_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL;
COMMIT;

