7925ac4ba4
- db.sql: tables for printers, filaments, print_jobs, todos, pricing_config - .gitignore: excludes .env files and Python build artefacts - README: cleaned up structure and added Getting Started section Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
85 lines
4.4 KiB
SQL
85 lines
4.4 KiB
SQL
-- POPS — Printing Operations Production System
|
|
-- Initial schema
|
|
|
|
CREATE DATABASE IF NOT EXISTS pops CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
USE pops;
|
|
|
|
-- ─── Printers ────────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE printers (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL,
|
|
model VARCHAR(100),
|
|
location VARCHAR(100),
|
|
status ENUM('idle', 'printing', 'error', 'offline') NOT NULL DEFAULT 'offline',
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ─── Filaments ───────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE filaments (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
brand VARCHAR(100),
|
|
material ENUM('PLA', 'PETG', 'ABS', 'ASA', 'TPU', 'Nylon', 'PC', 'Other') NOT NULL DEFAULT 'PLA',
|
|
color VARCHAR(100),
|
|
color_hex CHAR(7), -- e.g. #FF5733
|
|
weight_total_g DECIMAL(8,2) NOT NULL, -- spool size in grams
|
|
weight_remaining_g DECIMAL(8,2) NOT NULL,
|
|
price_per_kg_eur DECIMAL(8,2),
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- ─── Print jobs ──────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE print_jobs (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
printer_id INT UNSIGNED NOT NULL,
|
|
filament_id INT UNSIGNED,
|
|
name VARCHAR(200) NOT NULL,
|
|
file_name VARCHAR(255),
|
|
status ENUM('queued', 'printing', 'done', 'failed', 'cancelled') NOT NULL DEFAULT 'queued',
|
|
queue_position INT UNSIGNED, -- NULL when not queued
|
|
started_at DATETIME,
|
|
finished_at DATETIME,
|
|
duration_minutes INT UNSIGNED,
|
|
filament_used_g DECIMAL(8,2),
|
|
cost_eur DECIMAL(8,2), -- calculated at job completion
|
|
notes TEXT,
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT fk_job_printer FOREIGN KEY (printer_id) REFERENCES printers(id),
|
|
CONSTRAINT fk_job_filament FOREIGN KEY (filament_id) REFERENCES filaments(id)
|
|
);
|
|
|
|
-- ─── To-do list ──────────────────────────────────────────────────────────────
|
|
|
|
CREATE TABLE todos (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
title VARCHAR(255) NOT NULL,
|
|
done BOOLEAN NOT NULL DEFAULT FALSE,
|
|
printer_id INT UNSIGNED, -- optional: linked to a specific printer
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
CONSTRAINT fk_todo_printer FOREIGN KEY (printer_id) REFERENCES printers(id)
|
|
);
|
|
|
|
-- ─── Pricing configuration ───────────────────────────────────────────────────
|
|
|
|
CREATE TABLE pricing_config (
|
|
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(100) NOT NULL DEFAULT 'default',
|
|
electricity_kwh_eur DECIMAL(6,4) NOT NULL DEFAULT 0.30,
|
|
printer_watt DECIMAL(8,2) NOT NULL DEFAULT 200.00, -- average power draw
|
|
hourly_rate_eur DECIMAL(8,2) NOT NULL DEFAULT 0.00, -- operator cost per hour
|
|
markup_percent DECIMAL(5,2) NOT NULL DEFAULT 20.00,
|
|
valid_from DATE NOT NULL DEFAULT (CURRENT_DATE),
|
|
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Seed a default pricing row
|
|
INSERT INTO pricing_config (name) VALUES ('default');
|