db_init.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
CREATE DATABASE IF NOT EXISTS `inventaris` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `inventaris`;
START TRANSACTION;
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;


CREATE TABLE `asset_tags` (
  `id` int(10) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED NOT NULL,
  `tag_code` varchar(200) NOT NULL,
  `issued_at` date DEFAULT NULL,
  `issued_by` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `categories` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `equipment_items` (
  `id` int(10) UNSIGNED NOT NULL,
  `inventory_number` varchar(100) NOT NULL,
  `serial_number` varchar(200) DEFAULT NULL,
  `model_id` int(10) UNSIGNED NOT NULL,
  `location_id` int(10) UNSIGNED DEFAULT NULL,
  `purchase_id` int(10) UNSIGNED DEFAULT NULL,
  `status_id` int(10) UNSIGNED NOT NULL,
  `purchase_date` date DEFAULT NULL,
  `warranty_until` date DEFAULT NULL,
  `price` decimal(12,2) DEFAULT NULL,
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  `created_by` int(10) UNSIGNED DEFAULT NULL,
  `updated_at` datetime NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `updated_by` int(10) UNSIGNED DEFAULT NULL,
  `id_before_update` int(10) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `equipment_models` (
  `id` int(10) UNSIGNED NOT NULL,
  `manufacturer` varchar(150) DEFAULT NULL,
  `model_name` varchar(200) NOT NULL,
  `category_id` int(10) UNSIGNED NOT NULL,
  `spec` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`spec`)),
  `warranty_months` smallint(5) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `item_movements` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED NOT NULL,
  `movement_type` varchar(50) NOT NULL,
  `from_location_id` int(10) UNSIGNED DEFAULT NULL,
  `to_location_id` int(10) UNSIGNED DEFAULT NULL,
  `performed_by` int(10) UNSIGNED NOT NULL,
  `performed_at` datetime NOT NULL DEFAULT current_timestamp(),
  `note` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `locations` (
  `id` int(10) UNSIGNED NOT NULL,
  `building` varchar(100) DEFAULT NULL,
  `floor` varchar(50) DEFAULT NULL,
  `room` varchar(100) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `maintenance_records` (
  `id` bigint(20) UNSIGNED NOT NULL,
  `item_id` int(10) UNSIGNED NOT NULL,
  `maintenance_date` date NOT NULL,
  `performed_by_user_id` int(10) UNSIGNED DEFAULT NULL,
  `description` text DEFAULT NULL,
  `cost` decimal(12,2) DEFAULT NULL,
  `next_due` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `purchases` (
  `id` int(10) UNSIGNED NOT NULL,
  `supplier_id` int(10) UNSIGNED DEFAULT NULL,
  `order_number` varchar(150) DEFAULT NULL,
  `invoice_number` varchar(150) DEFAULT NULL,
  `purchase_date` date NOT NULL,
  `total_cost` decimal(12,2) DEFAULT NULL,
  `notes` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `roles` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(150) NOT NULL,
  `permissions` char(3) NOT NULL DEFAULT '111',
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `statuses` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(100) NOT NULL,
  `description` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `suppliers` (
  `id` int(10) UNSIGNED NOT NULL,
  `name` varchar(255) NOT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `users` (
  `id` int(10) UNSIGNED NOT NULL,
  `full_name` varchar(200) NOT NULL,
  `email` varchar(150) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `role_id` int(10) UNSIGNED DEFAULT NULL,
  `employee_number` varchar(100) DEFAULT NULL,
  `login` tinytext NOT NULL,
  `password_hash` tinytext NOT NULL,
  `hired_date` date DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;


ALTER TABLE `asset_tags`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `item_id` (`item_id`),
  ADD UNIQUE KEY `tag_code` (`tag_code`),
  ADD KEY `fk_tag_issued_by` (`issued_by`);

ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `name` (`name`);

ALTER TABLE `equipment_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `ix_equipment_inventory_number` (`inventory_number`),
  ADD KEY `fk_item_purchase` (`purchase_id`),
  ADD KEY `ix_equipment_items_model` (`model_id`),
  ADD KEY `ix_equipment_items_location` (`location_id`),
  ADD KEY `ix_equipment_created_by` (`created_by`),
  ADD KEY `ix_equipment_updated_by` (`updated_by`),
  ADD KEY `ix_equipment_id_before_update` (`id_before_update`),
  ADD KEY `ix_equipment_items_status` (`status_id`);

ALTER TABLE `equipment_models`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ux_model_manufacturer` (`manufacturer`,`model_name`),
  ADD KEY `fk_model_category` (`category_id`);

ALTER TABLE `item_movements`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_move_from_location` (`from_location_id`),
  ADD KEY `fk_move_to_location` (`to_location_id`),
  ADD KEY `ix_item_movements_item` (`item_id`),
  ADD KEY `ix_item_movements_performed_at` (`performed_at`);

ALTER TABLE `locations`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ux_location_code` (`building`,`floor`,`room`);

ALTER TABLE `maintenance_records`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_performed_by_user` (`performed_by_user_id`),
  ADD KEY `ix_maintenance_item` (`item_id`),
  ADD KEY `ix_maintenance_date` (`maintenance_date`);

ALTER TABLE `purchases`
  ADD PRIMARY KEY (`id`),
  ADD KEY `fk_purchase_supplier` (`supplier_id`);

ALTER TABLE `roles`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `statuses`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `suppliers`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `employee_number` (`employee_number`),
  ADD KEY `fk_user_role` (`role_id`);


ALTER TABLE `asset_tags`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `categories`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `equipment_items`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `equipment_models`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `item_movements`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `locations`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `maintenance_records`
  MODIFY `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `purchases`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `roles`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `statuses`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `suppliers`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `users`
  MODIFY `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;


ALTER TABLE `asset_tags`
  ADD CONSTRAINT `fk_tag_issued_by` FOREIGN KEY (`issued_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_tag_item` FOREIGN KEY (`item_id`) REFERENCES `equipment_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE `equipment_items`
  ADD CONSTRAINT `fk_item_location` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_model` FOREIGN KEY (`model_id`) REFERENCES `equipment_models` (`id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_purchase` FOREIGN KEY (`purchase_id`) REFERENCES `purchases` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_id_before_update` FOREIGN KEY (`id_before_update`) REFERENCES `equipment_items` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_item_status` FOREIGN KEY (`status_id`) REFERENCES `statuses` (`id`) ON UPDATE CASCADE;

ALTER TABLE `equipment_models`
  ADD CONSTRAINT `fk_model_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE CASCADE;

ALTER TABLE `item_movements`
  ADD CONSTRAINT `fk_move_from_location` FOREIGN KEY (`from_location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_move_item` FOREIGN KEY (`item_id`) REFERENCES `equipment_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_move_to_location` FOREIGN KEY (`to_location_id`) REFERENCES `locations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `maintenance_records`
  ADD CONSTRAINT `fk_maint_item` FOREIGN KEY (`item_id`) REFERENCES `equipment_items` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_performed_by_user` FOREIGN KEY (`performed_by_user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `purchases`
  ADD CONSTRAINT `fk_purchase_supplier` FOREIGN KEY (`supplier_id`) REFERENCES `suppliers` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE `users`
  ADD CONSTRAINT `fk_user_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- Минимальные seed-данные для первого запуска.
-- Администратор: login admin, password admin123. Пароль нужно сменить после первого входа.
INSERT INTO `roles` (`name`, `permissions`, `description`) VALUES
('Администратор', '333', 'Полный доступ к системе'),
('Оператор оборудования', '311', 'Создание и редактирование оборудования и перемещений'),
('Просмотр', '222', 'Просмотр основных разделов');

INSERT INTO `users` (`full_name`, `email`, `phone`, `role_id`, `employee_number`, `login`, `password_hash`, `hired_date`, `active`) VALUES
('Администратор системы', NULL, NULL, (SELECT `id` FROM `roles` WHERE `name` = 'Администратор' LIMIT 1), 'ADM-001', 'admin', 'scrypt:32768:8:1$AOdETiMy01YPQ9oY$9ec3e1363945d74b55f4d9858286a97c0613134bee51acb21b7f60e034d219f8da15cb36fbc634721b0956456a8ffcaa808fc3c78eb01b463564ad89e62a457e', NULL, 1);

INSERT INTO `statuses` (`name`, `description`) VALUES
('В эксплуатации', 'Оборудование используется'),
('На складе', 'Оборудование доступно на складе'),
('В ремонте', 'Оборудование передано в ремонт'),
('Списано', 'Оборудование выведено из эксплуатации');

INSERT INTO `categories` (`name`, `description`) VALUES
('Компьютеры', 'Рабочие станции и ноутбуки'),
('Мониторы', 'Мониторы и панели'),
('Принтеры', 'Печатающее оборудование');

INSERT INTO `suppliers` (`name`, `contact`, `address`, `phone`, `email`) VALUES
('Основной поставщик', NULL, NULL, NULL, NULL);

INSERT INTO `locations` (`building`, `floor`, `room`, `description`) VALUES
('Главный корпус', '1', '101', 'Склад оборудования'),
('Главный корпус', '2', '204', 'Офисный кабинет');

INSERT INTO `equipment_models` (`manufacturer`, `model_name`, `category_id`, `spec`, `warranty_months`) VALUES
('Lenovo', 'ThinkPad E14', (SELECT `id` FROM `categories` WHERE `name` = 'Компьютеры' LIMIT 1), JSON_OBJECT('cpu', 'Intel Core', 'ram', '16 GB'), 36);

INSERT INTO `purchases` (`supplier_id`, `order_number`, `invoice_number`, `purchase_date`, `total_cost`, `notes`) VALUES
((SELECT `id` FROM `suppliers` WHERE `name` = 'Основной поставщик' LIMIT 1), 'ORD-001', 'INV-001', CURDATE(), 85000.00, 'Стартовая закупка для демонстрации');
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;