-- 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');