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_key VARCHAR(100) NOT NULL,
    description_key VARCHAR(255),
    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_key VARCHAR(100) NOT NULL,
    description_key VARCHAR(255),
    tech_type VARCHAR(50), -- e.g., FDM, SLA, SLS
    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 (Optional, can be done via init script)
INSERT INTO materials (name_key, description_key, price_per_cm3) VALUES 
('pricing.matNames.pla', 'pricing.matDescs.pla', 0.04),
('pricing.matNames.abs', 'pricing.matDescs.abs', 0.05),
('pricing.matNames.petg', 'pricing.matDescs.petg', 0.06),
('pricing.matNames.resin', 'pricing.matDescs.resin', 0.12);

INSERT INTO services (name_key, description_key, tech_type) VALUES 
('services.fdm.title', 'services.fdm.description', 'FDM'),
('services.sla.title', 'services.sla.description', 'SLA');