radionica3d/backend/schema.sql

CREATE DATABASE IF NOT EXISTS radionica3d;
USE radionica3d;

-- 1. Users table
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(150) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `shipping_address` text DEFAULT NULL,
  `role` varchar(20) DEFAULT 'user',
  `is_active` tinyint(1) DEFAULT 1,
  `is_company` tinyint(1) DEFAULT 0,
  `company_name` varchar(200) DEFAULT NULL,
  `company_pib` varchar(50) DEFAULT NULL,
  `company_address` text DEFAULT NULL,
  `can_chat` tinyint(1) DEFAULT 1,
  `preferred_language` varchar(5) DEFAULT 'en',
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. Audit Logs
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(255) NOT NULL,
  `target_type` varchar(50) DEFAULT NULL,
  `target_id` int(11) DEFAULT NULL,
  `details` text DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Materials
CREATE TABLE IF NOT EXISTS `materials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name_en` varchar(100) NOT NULL,
  `name_ru` varchar(100) DEFAULT NULL,
  `name_ua` varchar(100) DEFAULT NULL,
  `name_me` varchar(100) DEFAULT NULL,
  `desc_en` text DEFAULT NULL,
  `desc_ru` text DEFAULT NULL,
  `desc_ua` text DEFAULT NULL,
  `desc_me` text DEFAULT NULL,
  `price_per_cm3` decimal(10,2) DEFAULT 0.00,
  `available_colors` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`available_colors`)),
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `long_desc_en` text DEFAULT NULL,
  `long_desc_ru` text DEFAULT NULL,
  `long_desc_ua` text DEFAULT NULL,
  `long_desc_me` text DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Services
CREATE TABLE IF NOT EXISTS `services` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name_en` varchar(100) NOT NULL,
  `name_ru` varchar(100) DEFAULT NULL,
  `name_ua` varchar(100) DEFAULT NULL,
  `name_me` varchar(100) DEFAULT NULL,
  `desc_en` text DEFAULT NULL,
  `desc_ru` text DEFAULT NULL,
  `desc_ua` text DEFAULT NULL,
  `desc_me` text DEFAULT NULL,
  `tech_type` varchar(50) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Orders
CREATE TABLE IF NOT EXISTS `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `material_id` int(11) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(150) NOT NULL,
  `shipping_address` text NOT NULL,
  `model_link` text DEFAULT NULL,
  `status` varchar(50) DEFAULT 'pending',
  `is_company` tinyint(1) DEFAULT 0,
  `company_name` varchar(200) DEFAULT NULL,
  `company_pib` varchar(50) DEFAULT NULL,
  `company_address` text DEFAULT NULL,
  `total_price` decimal(10,2) DEFAULT NULL,
  `estimated_price` decimal(10,2) DEFAULT NULL,
  `material_name` varchar(100) DEFAULT NULL,
  `material_price` decimal(10,4) DEFAULT NULL,
  `color_name` varchar(100) DEFAULT NULL,
  `allow_portfolio` tinyint(1) DEFAULT 0,
  `quantity` int(11) DEFAULT 1,
  `notes` text DEFAULT NULL,
  `invoice_path` text DEFAULT NULL,
  `fiscal_qr_url` text DEFAULT NULL,
  `ikof` varchar(100) DEFAULT NULL,
  `jikr` varchar(100) DEFAULT NULL,
  `fiscalized_at` timestamp NULL DEFAULT NULL,
  `review_text` text DEFAULT NULL,
  `rating` int(11) DEFAULT 0,
  `review_approved` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `original_params` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`original_params`)),
  `proforma_path` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `fk_order_material` (`material_id`),
  CONSTRAINT `fk_order_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL,
  CONSTRAINT `fk_order_material` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Order Files
CREATE TABLE IF NOT EXISTS `order_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `filename` varchar(255) NOT NULL,
  `file_path` varchar(512) NOT NULL,
  `file_size` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT 1,
  `unit_price` decimal(10,2) DEFAULT NULL,
  `file_hash` varchar(64) DEFAULT NULL,
  `print_time` varchar(50) DEFAULT NULL,
  `filament_g` float DEFAULT NULL,
  `preview_path` varchar(512) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  KEY `file_hash` (`file_hash`),
  CONSTRAINT `fk_file_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Order Items (for Manual Billing)
CREATE TABLE IF NOT EXISTS `order_items` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `quantity` int(11) NOT NULL DEFAULT 1,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `vat_rate` decimal(5,2) DEFAULT 21.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `fk_item_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 8. Order Messages (Chat)
CREATE TABLE IF NOT EXISTS `order_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `is_from_admin` tinyint(1) DEFAULT 0,
  `message` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `is_read` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `fk_msg_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 9. Order Photos
CREATE TABLE IF NOT EXISTS `order_photos` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_id` int(11) DEFAULT NULL,
  `file_path` varchar(512) NOT NULL,
  `is_public` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `order_id` (`order_id`),
  CONSTRAINT `fk_photo_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10. Blog Posts
CREATE TABLE IF NOT EXISTS `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `slug` varchar(255) NOT NULL,
  `title_en` varchar(255) NOT NULL,
  `title_me` varchar(255) DEFAULT NULL,
  `title_ru` varchar(255) DEFAULT NULL,
  `title_ua` varchar(255) DEFAULT NULL,
  `excerpt_en` text DEFAULT NULL,
  `excerpt_me` text DEFAULT NULL,
  `excerpt_ru` text DEFAULT NULL,
  `excerpt_ua` text DEFAULT NULL,
  `content_en` longtext DEFAULT NULL,
  `content_me` longtext DEFAULT NULL,
  `content_ru` longtext DEFAULT NULL,
  `content_ua` longtext DEFAULT NULL,
  `category` varchar(50) DEFAULT NULL,
  `image_url` varchar(512) DEFAULT NULL,
  `is_published` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 11. Password Reset Tokens
CREATE TABLE IF NOT EXISTS `password_reset_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `token` varchar(255) NOT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `token` (`token`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_reset_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Initial Data Migration
INSERT IGNORE INTO materials (id, name_en, name_ru, name_me, desc_en, desc_ru, desc_me, price_per_cm3) VALUES 
(1, 'PLA Plastic', 'PLA Пластик', 'PLA Plastika', 'Biodegradable, ideal for prototypes', 'Биоразлагаемый, идеален для прототипов', 'Biorazgradiva, idealna za prototipove', 0.04),
(2, 'ABS Plastic', 'ABS Пластик', 'ABS Plastika', 'Durable, impact resistant', 'Прочный, ударостойкий', 'Izdržljiva, otporna na udarce', 0.05),
(3, 'PETG Plastic', 'PETG Пластик', 'PETG Plastika', 'High chemical resistance', 'Высокая химическая стойкость', 'Visoka hemijska otpornost', 0.06),
(4, 'Photopolymer Resin', 'Фотополимерная смола', 'Fotopolimerna smola', 'Maximum detail and surface quality', 'Максимальная детализация и качество поверхности', 'Maksimalna detaljnost i kvalitet površine', 0.12);

INSERT IGNORE INTO services (id, name_en, name_ru, name_me, tech_type) VALUES 
(1, 'FDM Printing', 'FDM Печать', 'FDM Štampa', 'FDM'),
(2, 'SLA Printing', 'SLA Печать', 'SLA Štampa', 'SLA');

-- Default Admin User (password: admin123)
-- IMPORTANT: Change this in production!
INSERT IGNORE INTO users (id, email, password_hash, first_name, last_name, role) VALUES 
(1, 'admin@radionica3d.me', '$2b$12$cXGLw0yUqlPMxnaDIxVuU.IdKeeTwTMHyuu.a/hKh6BAGwqQLNyxy', 'Admin', 'Root', 'admin');

-- 11. Warehouse Stock
CREATE TABLE IF NOT EXISTS `warehouse_stock` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `material_id` int(11) NOT NULL,
  `color_name` varchar(100) NOT NULL,
  `quantity` decimal(10,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `material_id` (`material_id`),
  CONSTRAINT `fk_warehouse_material` FOREIGN KEY (`material_id`) REFERENCES `materials` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;