tilbudgivern/migrations/create_smart_packages_tables.sql

-- Smart Pakker System Database Schema
-- Oprettet: 2025-10-21
-- Formål: Komplet system til håndtering af smart pakker med materialer, tasks og montagevejledninger

-- Udvid material_packages tabel med nye felter
ALTER TABLE material_packages
ADD COLUMN estimated_hours DECIMAL(10,2) DEFAULT 0 COMMENT 'Estimerede timer for hele pakken',
ADD COLUMN hourly_rate DECIMAL(10,2) DEFAULT 580 COMMENT 'Timepris for arbejde',
ADD COLUMN installation_notes TEXT COMMENT 'Generelle installationsnoter',
ADD COLUMN difficulty_level ENUM('let', 'mellem', 'svær') DEFAULT 'mellem' COMMENT 'Sværhedsgrad',
ADD COLUMN package_type ENUM('tag', 'køkken', 'bad', 'gulv', 'carport', 'generel') DEFAULT 'generel' COMMENT 'Type af pakke',
ADD COLUMN is_template BOOLEAN DEFAULT 0 COMMENT 'Er det en template pakke?',
ADD COLUMN popularity_score INT DEFAULT 0 COMMENT 'Popularitetsscore baseret på brug',
ADD COLUMN last_used_at DATETIME NULL COMMENT 'Sidste gang pakken blev brugt';

-- Opret tabel til smart pakke tasks (arbejdsopgaver)
CREATE TABLE IF NOT EXISTS smart_package_tasks (
  id INT PRIMARY KEY AUTO_INCREMENT,
  package_id INT NOT NULL,
  task_name VARCHAR(255) NOT NULL COMMENT 'Navn på opgaven',
  task_description TEXT COMMENT 'Detaljeret beskrivelse af opgaven',
  task_order INT NOT NULL DEFAULT 0 COMMENT 'Rækkefølge af opgaven i pakken',
  estimated_hours DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT 'Estimerede timer for denne opgave',
  hourly_rate DECIMAL(10,2) DEFAULT 580 COMMENT 'Timepris for denne specifikke opgave',
  required_skills TEXT COMMENT 'Nødvendige færdigheder (JSON array)',
  required_tools TEXT COMMENT 'Nødvendige værktøjer (JSON array)',
  is_optional BOOLEAN DEFAULT 0 COMMENT 'Er opgaven valgfri?',
  depends_on_task_id INT NULL COMMENT 'Afhængig af anden opgave',
  installation_manual_id INT NULL COMMENT 'Link til montagevejledning',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES material_packages(id) ON DELETE CASCADE,
  FOREIGN KEY (depends_on_task_id) REFERENCES smart_package_tasks(id) ON DELETE SET NULL,
  FOREIGN KEY (installation_manual_id) REFERENCES installation_manuals(id) ON DELETE SET NULL,
  INDEX idx_package_order (package_id, task_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Arbejdsopgaver for smart pakker';

-- Opret tabel til montage steps (trin-for-trin vejledninger)
CREATE TABLE IF NOT EXISTS smart_package_steps (
  id INT PRIMARY KEY AUTO_INCREMENT,
  task_id INT NOT NULL,
  step_order INT NOT NULL DEFAULT 0 COMMENT 'Rækkefølge af trinnet',
  step_title VARCHAR(255) NOT NULL COMMENT 'Titel på trinnet',
  step_description TEXT NOT NULL COMMENT 'Detaljeret beskrivelse',
  estimated_minutes INT DEFAULT 0 COMMENT 'Estimerede minutter for dette trin',
  image_url VARCHAR(500) NULL COMMENT 'Link til billedvejledning',
  video_url VARCHAR(500) NULL COMMENT 'Link til videovejledning',
  safety_notes TEXT COMMENT 'Sikkerhedsnoter for dette trin',
  quality_check TEXT COMMENT 'Kvalitetstjek efter trinnet',
  tips TEXT COMMENT 'Tips og tricks',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES smart_package_tasks(id) ON DELETE CASCADE,
  INDEX idx_task_order (task_id, step_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Montage trin for tasks';

-- Opret tabel til at koble materialer til specifikke tasks
CREATE TABLE IF NOT EXISTS task_materials (
  id INT PRIMARY KEY AUTO_INCREMENT,
  task_id INT NOT NULL,
  package_material_id INT NOT NULL,
  quantity_used DECIMAL(10,2) NOT NULL DEFAULT 1 COMMENT 'Hvor meget af materialet bruges i denne opgave',
  usage_notes TEXT COMMENT 'Noter om materialeforbruget',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES smart_package_tasks(id) ON DELETE CASCADE,
  FOREIGN KEY (package_material_id) REFERENCES package_materials(id) ON DELETE CASCADE,
  UNIQUE KEY unique_task_material (task_id, package_material_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Materialer koblet til specifikke tasks';

-- Opret tabel til at tracke brug af pakker (for statistik og popularitet)
CREATE TABLE IF NOT EXISTS smart_package_usage (
  id INT PRIMARY KEY AUTO_INCREMENT,
  package_id INT NOT NULL,
  project_id INT NULL,
  used_by VARCHAR(100) COMMENT 'Bruger der anvendte pakken',
  usage_date DATETIME DEFAULT CURRENT_TIMESTAMP,
  adjustments_made TEXT COMMENT 'Eventuelle justeringer der blev lavet (JSON)',
  feedback TEXT COMMENT 'Brugerfeedback',
  FOREIGN KEY (package_id) REFERENCES material_packages(id) ON DELETE CASCADE,
  INDEX idx_package_usage (package_id, usage_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='Tracking af pakke brug for statistik';

-- Opret view til at få komplet overblik over smart pakker
CREATE OR REPLACE VIEW smart_packages_overview AS
SELECT 
  mp.id,
  mp.name,
  mp.description,
  mp.category,
  mp.package_type,
  mp.difficulty_level,
  mp.estimated_hours as package_hours,
  mp.total_estimated_price as material_cost,
  mp.estimated_hours * mp.hourly_rate as labor_cost,
  (mp.total_estimated_price + (mp.estimated_hours * mp.hourly_rate)) as total_package_price,
  mp.is_template,
  mp.popularity_score,
  mp.last_used_at,
  COUNT(DISTINCT spt.id) as task_count,
  COUNT(DISTINCT pm.id) as material_count,
  COUNT(DISTINCT spu.id) as times_used,
  mp.created_at,
  mp.updated_at
FROM material_packages mp
LEFT JOIN smart_package_tasks spt ON mp.id = spt.package_id
LEFT JOIN package_materials pm ON mp.id = pm.package_id
LEFT JOIN smart_package_usage spu ON mp.id = spu.package_id
WHERE mp.is_active = 1
GROUP BY mp.id;

-- Indeks for bedre performance
CREATE INDEX idx_package_type ON material_packages(package_type);
CREATE INDEX idx_difficulty ON material_packages(difficulty_level);
CREATE INDEX idx_template ON material_packages(is_template);
CREATE INDEX idx_popularity ON material_packages(popularity_score DESC);

-- Eksempel data: B7 Tag Udskiftning Smart Pakke
INSERT INTO material_packages (
  name, description, category, package_type, difficulty_level, 
  estimated_hours, hourly_rate, installation_notes, is_template, created_by
) VALUES (
  'B7 Tag Udskiftning - Komplet',
  'Komplet pakke til udskiftning af B7 tag inkl. alle materialer og arbejdstrin',
  'Tagarbejde',
  'tag',
  'svær',
  32.0,
  580,
  'Kræver erfaring med tagarbejde. Husk arbejdstilladelse til højdearbejde.',
  1,
  'system'
);

SET @package_id = LAST_INSERT_ID();

-- Eksempel materialer for B7 tag pakken
INSERT INTO package_materials (package_id, material_name, material_category, quantity, unit, unit_price, total_price) VALUES
(@package_id, 'Eternit B7 tagplader', 'Tagmateriale', 100, 'm²', 245.00, 24500.00),
(@package_id, 'Tagpap diffusionsåben', 'Tagmateriale', 105, 'm²', 65.00, 6825.00),
(@package_id, 'Lægter 38x73mm', 'Tømmer', 250, 'løbende m', 24.50, 6125.00),
(@package_id, 'Spær 45x195mm', 'Tømmer', 8, 'stk', 285.00, 2280.00);

-- Eksempel tasks for B7 tag pakken
INSERT INTO smart_package_tasks (package_id, task_name, task_description, task_order, estimated_hours, required_skills, required_tools) VALUES
(@package_id, 'Nedrivning af gammelt tag', 'Forsigtig nedrivning og bortskaffelse af gamle tagmaterialer', 1, 8.0, '["højdearbejde", "nedrivning"]', '["stige", "hammer", "brechstang", "container"]'),
(@package_id, 'Inspektion og reparation af spær', 'Kontrol af bærende konstruktion og udbedring af skader', 2, 4.0, '["tømrerarbejde", "konstruktionsvurdering"]', '["vaterpas", "målebånd", "sav"]'),
(@package_id, 'Montage af undertag', 'Udlægning af diffusionsåben tagpap', 3, 6.0, '["tagdækning"]', '["saks", "hæftepistol", "målebånd"]'),
(@package_id, 'Montering af lægter', 'Præcis opsætning af lægter med korrekt afstand', 4, 8.0, '["tømrerarbejde", "præcision"]', '["skruemaskine", "vaterpas", "snor"]'),
(@package_id, 'Tagdækning med B7 plader', 'Montering af Eternit B7 tagplader', 5, 12.0, '["tagdækning", "højdearbejde"]', '["vinkelsliber", "boremaskine", "tagstiger"]');

-- Eksempel steps for første task
SET @task_id = (SELECT id FROM smart_package_tasks WHERE package_id = @package_id AND task_order = 1);

INSERT INTO smart_package_steps (task_id, step_order, step_title, step_description, estimated_minutes, safety_notes) VALUES
(@task_id, 1, 'Opsæt sikkerhedsudstyr', 'Etabler tagsikring, stiger og faldsikring', 30, 'VIGTIGT: Brug altid sikkerhedssele ved arbejde i højden'),
(@task_id, 2, 'Start nedtagning ved tagryggen', 'Begynd fra toppen og arbejd nedad', 120, 'Pas på løse tagsten der kan falde'),
(@task_id, 3, 'Fjern gamle lægter', 'Fjern systematisk lægter og søm', 90, 'Brug handsker - mange søm og skarpe dele'),
(@task_id, 4, 'Bortskaffelse', 'Fyld container og hold arbejdsområdet ryddeligt', 60, 'Sorter materialer korrekt til affald');