192 lines
6.4 KiB
SQL
192 lines
6.4 KiB
SQL
-- Database struktur til historisk vendor data (Bygma m.fl.)
|
|
-- Dette skema håndterer både historiske og fremtidige priser fra leverandører
|
|
|
|
-- Hovedtabel for leverandører
|
|
CREATE TABLE IF NOT EXISTS vendors (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
vendor_name VARCHAR(255) NOT NULL,
|
|
vendor_code VARCHAR(100),
|
|
contact_info JSON,
|
|
is_active BOOLEAN DEFAULT TRUE,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
UNIQUE KEY unique_vendor_code (vendor_code),
|
|
INDEX idx_vendor_name (vendor_name),
|
|
INDEX idx_active (is_active)
|
|
);
|
|
|
|
-- Produktkategorier og varegrupper
|
|
CREATE TABLE IF NOT EXISTS product_categories (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
category_code VARCHAR(100) NOT NULL,
|
|
category_name VARCHAR(255) NOT NULL,
|
|
parent_category_id INT,
|
|
vendor_id INT,
|
|
description TEXT,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (parent_category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
|
|
FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_category_vendor (category_code, vendor_id),
|
|
INDEX idx_category_name (category_name),
|
|
INDEX idx_parent_category (parent_category_id)
|
|
);
|
|
|
|
-- Historisk produktdata fra leverandører
|
|
CREATE TABLE IF NOT EXISTS vendor_products_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
vendor_id INT NOT NULL,
|
|
category_id INT,
|
|
|
|
-- Produkt identifikation
|
|
vendor_product_code VARCHAR(255) NOT NULL,
|
|
vendor_db_number VARCHAR(100),
|
|
product_name TEXT NOT NULL,
|
|
product_type VARCHAR(255),
|
|
product_description TEXT,
|
|
|
|
-- Produktspecifikationer
|
|
unit VARCHAR(50) NOT NULL, -- M, STK, PK, etc.
|
|
net_weight_kg DECIMAL(10,3),
|
|
functional_unit VARCHAR(100),
|
|
functional_unit_count DECIMAL(10,3),
|
|
conversion_factor DECIMAL(10,6),
|
|
|
|
-- Miljødata (ESG)
|
|
co2_a1_a3_kg DECIMAL(15,6), -- Total A1-A3 (kg CO2 eq)
|
|
co2_c3_kg DECIMAL(15,6), -- Total C3 (kg CO2 eq)
|
|
co2_c4_kg DECIMAL(15,6), -- Total C4 (kg CO2 eq)
|
|
co2_per_unit DECIMAL(15,6), -- A1-A3 GWP total per enhed
|
|
epd_link VARCHAR(500),
|
|
epd_document VARCHAR(255),
|
|
|
|
-- Metadata
|
|
source_file VARCHAR(255),
|
|
import_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
valid_from DATE NOT NULL,
|
|
valid_to DATE,
|
|
is_current BOOLEAN DEFAULT TRUE,
|
|
|
|
FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
|
|
INDEX idx_vendor_product (vendor_id, vendor_product_code),
|
|
INDEX idx_product_name (product_name(100)),
|
|
INDEX idx_valid_period (valid_from, valid_to),
|
|
INDEX idx_current (is_current),
|
|
INDEX idx_import_date (import_date)
|
|
);
|
|
|
|
-- Prishistorik fra leverandører
|
|
CREATE TABLE IF NOT EXISTS vendor_price_history (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
vendor_id INT NOT NULL,
|
|
product_history_id INT NOT NULL,
|
|
|
|
-- Ordre information
|
|
order_number VARCHAR(100),
|
|
invoice_date DATE NOT NULL,
|
|
delivery_date DATE,
|
|
project_number VARCHAR(100),
|
|
|
|
-- Pris og mængde
|
|
quantity DECIMAL(15,3) NOT NULL,
|
|
unit_price_dkk DECIMAL(15,2) NOT NULL,
|
|
total_price_dkk DECIMAL(15,2) NOT NULL,
|
|
currency VARCHAR(10) DEFAULT 'DKK',
|
|
|
|
-- Beregnet miljøpåvirkning for denne ordre
|
|
total_co2_a1_a3 DECIMAL(15,6),
|
|
total_co2_c3 DECIMAL(15,6),
|
|
total_co2_c4 DECIMAL(15,6),
|
|
|
|
-- Metadata
|
|
source_file VARCHAR(255),
|
|
import_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
|
|
FOREIGN KEY (product_history_id) REFERENCES vendor_products_history(id) ON DELETE CASCADE,
|
|
INDEX idx_vendor_date (vendor_id, invoice_date),
|
|
INDEX idx_order_number (order_number),
|
|
INDEX idx_project_number (project_number),
|
|
INDEX idx_price_date (invoice_date),
|
|
INDEX idx_import_date (import_date)
|
|
);
|
|
|
|
-- Aggregeret prisoversigt for hurtige opslag (materialized view concept)
|
|
CREATE TABLE IF NOT EXISTS vendor_current_prices (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
vendor_id INT NOT NULL,
|
|
vendor_product_code VARCHAR(255) NOT NULL,
|
|
product_name TEXT NOT NULL,
|
|
|
|
-- Aktuelle priser (baseret på seneste data)
|
|
current_unit_price_dkk DECIMAL(15,2),
|
|
last_purchase_date DATE,
|
|
last_purchase_quantity DECIMAL(15,3),
|
|
|
|
-- Prisstatistik (sidste 12 måneder)
|
|
avg_price_12m DECIMAL(15,2),
|
|
min_price_12m DECIMAL(15,2),
|
|
max_price_12m DECIMAL(15,2),
|
|
purchase_count_12m INT DEFAULT 0,
|
|
total_quantity_12m DECIMAL(15,3) DEFAULT 0,
|
|
|
|
-- Miljødata
|
|
co2_per_unit DECIMAL(15,6),
|
|
|
|
-- Metadata
|
|
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
|
|
FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
|
|
UNIQUE KEY unique_vendor_product (vendor_id, vendor_product_code),
|
|
INDEX idx_product_name (product_name(100)),
|
|
INDEX idx_current_price (current_unit_price_dkk),
|
|
INDEX idx_last_purchase (last_purchase_date)
|
|
);
|
|
|
|
-- Import log til at tracke data imports
|
|
CREATE TABLE IF NOT EXISTS vendor_import_log (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
vendor_id INT NOT NULL,
|
|
source_file VARCHAR(255) NOT NULL,
|
|
file_hash VARCHAR(64), -- SHA256 hash for duplicate detection
|
|
|
|
-- Import statistik
|
|
records_processed INT DEFAULT 0,
|
|
records_imported INT DEFAULT 0,
|
|
records_updated INT DEFAULT 0,
|
|
records_failed INT DEFAULT 0,
|
|
|
|
-- Import periode
|
|
data_period_start DATE,
|
|
data_period_end DATE,
|
|
|
|
-- Status
|
|
import_status ENUM('pending', 'processing', 'completed', 'failed') DEFAULT 'pending',
|
|
error_message TEXT,
|
|
|
|
-- Metadata
|
|
imported_by VARCHAR(100),
|
|
import_started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
import_completed_at TIMESTAMP NULL,
|
|
|
|
FOREIGN KEY (vendor_id) REFERENCES vendors(id) ON DELETE CASCADE,
|
|
INDEX idx_vendor_import (vendor_id, import_started_at),
|
|
INDEX idx_file_hash (file_hash),
|
|
INDEX idx_import_status (import_status)
|
|
);
|
|
|
|
-- Insert Bygma as vendor
|
|
INSERT INTO vendors (vendor_name, vendor_code, contact_info, is_active)
|
|
VALUES (
|
|
'Bygma A/S',
|
|
'BYGMA',
|
|
JSON_OBJECT(
|
|
'website', 'https://www.bygma.dk',
|
|
'customer_number', '156793',
|
|
'company_name', 'Tømrer- og Snedker Mikael Holck ApS'
|
|
),
|
|
TRUE
|
|
) ON DUPLICATE KEY UPDATE updated_at = CURRENT_TIMESTAMP;
|