radionica3d/backend/schema.sql

CREATE DATABASE IF NOT EXISTS radionica3d;
USE radionica3d;

-- Materials table
CREATE TABLE IF NOT EXISTS materials (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_en VARCHAR(100) NOT NULL,
    name_ru VARCHAR(100),
    name_me VARCHAR(100),
    desc_en TEXT,
    desc_ru TEXT,
    desc_me TEXT,
    price_per_cm3 DECIMAL(10, 2) DEFAULT 0.00,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Services table
CREATE TABLE IF NOT EXISTS services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_en VARCHAR(100) NOT NULL,
    name_ru VARCHAR(100),
    name_me VARCHAR(100),
    desc_en TEXT,
    desc_ru TEXT,
    desc_me TEXT,
    tech_type VARCHAR(50), -- e.g., FDM, SLA
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(150) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(20),
    shipping_address TEXT,
    role VARCHAR(20) DEFAULT 'user', -- user, admin
    ip_address VARCHAR(45), -- Supports IPv6
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Password reset tokens
CREATE TABLE IF NOT EXISTS password_reset_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    token VARCHAR(255) NOT NULL UNIQUE,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Orders table
CREATE TABLE IF NOT EXISTS orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT 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,
    status VARCHAR(50) DEFAULT 'pending', -- pending, processing, shipped, completed, cancelled
    total_price DECIMAL(10, 2) DEFAULT NULL, -- To be filled by admin later
    estimated_price DECIMAL(10, 2) DEFAULT NULL,
    material_name VARCHAR(100) NULL, -- Factual Snapshot
    material_price DECIMAL(10, 4) DEFAULT NULL, -- Factual Snapshot
    allow_portfolio BOOLEAN DEFAULT FALSE,
    quantity INT DEFAULT 1,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- Order Files table
CREATE TABLE IF NOT EXISTS order_files (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(512) NOT NULL,
    file_size INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Order Photos table (for reports and portfolio)
CREATE TABLE IF NOT EXISTS order_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT NOT NULL,
    file_path VARCHAR(512) NOT NULL,
    is_public BOOLEAN DEFAULT FALSE, -- Only if allow_portfolio on order is true
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

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

INSERT INTO services (name_en, name_ru, name_me, desc_en, desc_ru, desc_me, tech_type) VALUES 
('FDM Printing', 'FDM Печать', 'FDM Štampa', 'Fast and durable parts made from high-strength engineering plastics. Ideal for functional prototypes and industrial components.', 'Быстрые и прочные детали из высокопрочных инженерных пластиков. Идеально для функциональных прототипов и промышленных узлов.', 'Brzi i izdržljivi djelovi od visokootporne inženjerske plastike. Idealno za funkcionalne prototipove i industrijske komponente.', 'FDM'),
('SLA Printing', 'SLA Печать', 'SLA Štampa', 'Maximum detail and smooth surface for professional prototypes and high-precision models.', 'Максимальная детализация и гладкость поверхности для профессиональных прототипов и высокоточных моделей.', 'Maksimalna detaljnost i glatka površina za profesionalne prototipove i modele visoke preciznosti.', 'SLA');