tilbudgivern/archive/sql/ordrestyring_complete_schema.sql

-- Ordrestyring API Database Schema
-- Generated: 2025-09-19T11:37:55.040Z
-- 
-- This schema supports all data types from Ordrestyring API

USE ordrestyring_local;

-- Table for /cases
CREATE TABLE IF NOT EXISTS cases (
    id INT AUTO_INCREMENT PRIMARY KEY,
    yourref TEXT NULL,
    requestor TEXT NULL,
    creation_date BIGINT NULL,
    description TEXT NULL,
    main_technician INT NULL,
    status INT NULL,
    made_by INT NULL,
    offer_number INT NULL,
    additional_technicians JSON NULL,
    remarks TEXT NULL,
    work_done TEXT NULL,
    delivery_address INT NULL,
    contact TEXT NULL,
    case_type INT NULL,
    department INT NULL,
    no_materials BOOLEAN NULL,
    case_number VARCHAR(255) NULL,
    sub_number INT NULL,
    upper_case_number INT NULL,
    updated_at BIGINT NULL,
    created_at BIGINT NULL,
    customer_number VARCHAR(255) NULL
);

-- Table for /case-materials
CREATE TABLE IF NOT EXISTS case_materials (
    id INT PRIMARY KEY,
    quantity INT NULL,
    ean VARCHAR(255) NULL,
    product_number VARCHAR(255) NULL,
    product_text TEXT NULL,
    supplier VARCHAR(255) NULL,
    discount VARCHAR(255) NULL,
    cost_price VARCHAR(255) NULL,
    list_price VARCHAR(255) NULL,
    sales_price VARCHAR(255) NULL,
    created_by INT NULL,
    added_type VARCHAR(255) NULL,
    edi_invoice_number VARCHAR(255) NULL,
    updated_at BIGINT NULL,
    created_at BIGINT NULL,
    added_time BIGINT NULL,
    case_number VARCHAR(255) NULL
);

-- Table for /hours
CREATE TABLE IF NOT EXISTS hours (
    id INT PRIMARY KEY,
    emp_id INT NULL,
    start_time BIGINT NULL,
    stop_time BIGINT NULL,
    remark TEXT NULL,
    hour_type INT NULL,
    updated_at BIGINT NULL,
    created_at BIGINT NULL,
    costprice INT NULL,
    exported BOOLEAN NULL,
    approval_status INT NULL,
    new_case_number VARCHAR(255) NULL
);

-- Table for /debtors
CREATE TABLE IF NOT EXISTS debtors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NULL,
    customer_attention TEXT NULL,
    customer_address TEXT NULL,
    customer_postalcode VARCHAR(255) NULL,
    customer_city VARCHAR(255) NULL,
    customer_telephone TEXT NULL,
    customer_email VARCHAR(255) NULL,
    customer_mobile TEXT NULL,
    invoice_name VARCHAR(255) NULL,
    invoice_address TEXT NULL,
    invoice_postalcode TEXT NULL,
    invoice_city TEXT NULL,
    invoice_telephone TEXT NULL,
    invoice_email VARCHAR(255) NULL,
    invoice_mobile TEXT NULL,
    due_dates INT NULL,
    cvr TEXT NULL,
    ean VARCHAR(255) NULL,
    customer_remarks TEXT NULL,
    customer_number VARCHAR(255) NULL,
    status INT NULL,
    invoice_attention TEXT NULL,
    avance_type INT NULL,
    updated_at BIGINT NULL,
    is_blocked BOOLEAN NULL,
    created_at BIGINT NULL,
    pdf_layout_id INT NULL,
    currency_id INT NULL,
    invoice_social_security_number TEXT NULL,
    invoice_property_designation TEXT NULL,
    invoice_residence_association_organisation_number TEXT NULL,
    invoice_vat_number TEXT NULL
);

-- Table for /debtor-invoices
CREATE TABLE IF NOT EXISTS debtor_invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_name VARCHAR(255) NULL,
    invoice_address TEXT NULL,
    invoice_postalcode VARCHAR(255) NULL,
    invoice_city VARCHAR(255) NULL,
    cust_name VARCHAR(255) NULL,
    cust_address TEXT NULL,
    cust_postalcode VARCHAR(255) NULL,
    cust_city VARCHAR(255) NULL,
    del_name TEXT NULL,
    del_address TEXT NULL,
    del_city TEXT NULL,
    del_postalcode TEXT NULL,
    text TEXT NULL,
    date BIGINT NULL,
    payment_date BIGINT NULL,
    type INT NULL,
    ref TEXT NULL,
    rek TEXT NULL,
    customer_number VARCHAR(255) NULL,
    made_by INT NULL,
    amount VARCHAR(255) NULL,
    vat VARCHAR(255) NULL,
    amount_vat VARCHAR(255) NULL,
    fi_number TEXT NULL,
    header VARCHAR(255) NULL,
    status INT NULL,
    del_att TEXT NULL,
    cust_att TEXT NULL,
    invoice_att TEXT NULL,
    payed_date BIGINT NULL,
    payed_amount VARCHAR(255) NULL,
    invoice_number INT NULL,
    department TEXT NULL,
    updated_at BIGINT NULL,
    created_at BIGINT NULL,
    cust_cvr TEXT NULL,
    use_skattered BOOLEAN NULL,
    currency_code VARCHAR(255) NULL,
    currency_multiplier INT NULL,
    case_number VARCHAR(255) NULL
);

-- Table for /users
CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY,
    init VARCHAR(255) NULL,
    last_name VARCHAR(255) NULL,
    tlf VARCHAR(255) NULL,
    mobil_privat VARCHAR(255) NULL,
    first_name VARCHAR(255) NULL,
    status INT NULL,
    email VARCHAR(255) NULL,
    department_id TEXT NULL,
    employee_type_id INT NULL,
    fullName VARCHAR(255) NULL
);

-- Table for /case-types
CREATE TABLE IF NOT EXISTS case_types (
    id INT PRIMARY KEY,
    text VARCHAR(255) NULL,
    site VARCHAR(255) NULL,
    colorcode TEXT NULL,
    show_in_app BOOLEAN NULL,
    show_in_app_status_change BOOLEAN NULL
);

-- Table for /case-statuses
CREATE TABLE IF NOT EXISTS case_statuses (
    id INT PRIMARY KEY,
    text VARCHAR(255) NULL,
    site VARCHAR(255) NULL,
    colorcode VARCHAR(255) NULL,
    show_in_app BOOLEAN NULL,
    show_in_app_status_change BOOLEAN NULL
);

-- Table for /employee-types
CREATE TABLE IF NOT EXISTS employee_types (
    id INT PRIMARY KEY,
    title VARCHAR(255) NULL,
    internal_number VARCHAR(255) NULL,
    is_personal BOOLEAN NULL,
    color VARCHAR(255) NULL,
    updated_at BIGINT NULL,
    created_at BIGINT NULL,
    salary_handle TEXT NULL,
    sort_order INT NULL
);