alex/tilbudgivern
1
0
Fork 0
Code Issues Pull Requests Actions 1 Packages Projects Releases Wiki Activity
Files
main
tilbudgivern/migrations/feature_1_task_categorization.sql
alexpolo1 0a2c02b51b feat: Implement task categorization service with fuzzy matching and caching 
- Added TaskCategorizationService to categorize tasks based on keywords in descriptions.
- Implemented fuzzy matching using Levenshtein distance for better keyword matching.
- Introduced caching for categories and keywords to optimize database queries.
- Added methods for categorizing individual tasks and batch processing for project tasks.
- Created SQL migration scripts for task_categories and category_keywords tables.
- Included default categories and keywords for initial setup.
- Enhanced project_tasks table with new columns for categorization data.
- Added statistics export functionality for category performance analysis.
2025-10-20 09:05:59 +00:00

289 lines
16 KiB
SQL
Raw Permalink Blame History

-- Feature 1: Standardisering af opgavebeskrivelser
-- Migration Script
-- Dato: 2025-10-19
-- =====================================================
-- Step 1: Opret task_categories tabel
-- =====================================================
CREATE TABLE IF NOT EXISTS task_categories (
id INT AUTO_INCREMENT PRIMARY KEY,
category_name VARCHAR(100) NOT NULL,
category_key VARCHAR(50) UNIQUE NOT NULL,
description TEXT,
icon VARCHAR(50),
color_code VARCHAR(7),
sort_order INT DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_category_key (category_key),
INDEX idx_sort_order (sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- =====================================================
-- Step 2: Opret category_keywords tabel
-- =====================================================
CREATE TABLE IF NOT EXISTS category_keywords (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
keyword VARCHAR(100) NOT NULL,
weight DECIMAL(3,2) DEFAULT 1.0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (category_id) REFERENCES task_categories(id) ON DELETE CASCADE,
INDEX idx_keyword (keyword),
INDEX idx_category_weight (category_id, weight DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- =====================================================
-- Step 3: Alter project_tasks tabel (hvis den findes)
-- =====================================================
-- Check if project_tasks table exists, if not create basic structure
CREATE TABLE IF NOT EXISTS project_tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT NOT NULL,
task_name VARCHAR(255) NOT NULL,
task_description TEXT,
estimated_hours DECIMAL(10,2),
actual_hours DECIMAL(10,2),
actual_cost DECIMAL(10,2),
task_status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
INDEX idx_project_id (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-- Add new columns for categorization
ALTER TABLE project_tasks
ADD COLUMN IF NOT EXISTS category_id INT,
ADD COLUMN IF NOT EXISTS auto_categorized BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS category_confidence DECIMAL(3,2),
ADD COLUMN IF NOT EXISTS categorized_at TIMESTAMP NULL;
-- Add foreign key if it doesn't exist
SET @fk_exists = (
SELECT COUNT(*)
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = DATABASE()
AND TABLE_NAME = 'project_tasks'
AND CONSTRAINT_NAME = 'fk_project_tasks_category'
);
SET @sql = IF(@fk_exists = 0,
'ALTER TABLE project_tasks ADD CONSTRAINT fk_project_tasks_category FOREIGN KEY (category_id) REFERENCES task_categories(id) ON DELETE SET NULL',
'SELECT "Foreign key already exists"'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Add index for category queries
CREATE INDEX IF NOT EXISTS idx_category_id ON project_tasks(category_id);
CREATE INDEX IF NOT EXISTS idx_auto_categorized ON project_tasks(auto_categorized);
CREATE INDEX IF NOT EXISTS idx_category_confidence ON project_tasks(category_confidence);
-- =====================================================
-- Step 4: Indsæt standard kategorier
-- =====================================================
INSERT INTO task_categories (category_name, category_key, icon, color_code, description, sort_order) VALUES
('Vinduer', 'vinduer', 'window', '#3b82f6', 'Montering og udskiftning af vinduer, termoruder, karme', 1),
('Døre', 'døre', 'door', '#10b981', 'Montering og udskiftning af døre, entredøre, indvendige døre', 2),
('Gulve', 'gulve', 'floor', '#f59e0b', 'Lægning af gulve: trægulv, laminat, parket, vinyl, fliser', 3),
('Tage', 'tage', 'roof', '#ef4444', 'Tag arbejde: tagplader, spær, lægter, undertag, renovering', 4),
('Vægge', 'vægge', 'wall', '#8b5cf6', 'Væg arbejde: facader, puds, maling, isolering', 5),
('El-arbejde', 'el', 'electric', '#eab308', 'Elektrisk installation: stikkontakter, belysning, ledninger', 6),
('VVS', 'vvs', 'plumbing', '#06b6d4', 'VVS arbejde: rør, badeværelse, køkken, vandhaner, afløb', 7),
('Tømrerarbejde', 'tømrer', 'carpenter', '#d97706', 'Generelt tømrerarbejde: træ konstruktioner, tømmerarbejde', 8),
('Maler arbejde', 'maler', 'paint', '#ec4899', 'Malerarbejde: maling af vægge, lofter, træværk', 9),
('Nedrivning', 'nedrivning', 'demolition', '#dc2626', 'Nedrivning og fjernelse af eksisterende konstruktioner', 10),
('Andet', 'andre', 'tool', '#6b7280', 'Andre opgaver der ikke passer i ovenstående kategorier', 99)
ON DUPLICATE KEY UPDATE
category_name = VALUES(category_name),
description = VALUES(description),
icon = VALUES(icon),
color_code = VALUES(color_code);
-- =====================================================
-- Step 5: Indsæt keywords for hver kategori
-- =====================================================
-- Vinduer
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'vindue', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'vinduer', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'termoruder', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'termorude', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'karm', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'karme', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'rude', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'ruder', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'glasparti', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vinduer'), 'vinduesparti', 1.0)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Døre
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'dør', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'døre', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'entre', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'entredør', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'indgang', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'udgang', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'hoveddør', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'sikkerhedsdør', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'dørkarm', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'døre'), 'skydedør', 0.9)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Gulve
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'gulv', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'gulve', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'trægulv', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'laminat', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'parket', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'vinyl', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'flise', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'fliser', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'gulvbelægning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'gulve'), 'undergulv', 0.8)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Tage
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tag', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tage', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagplade', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagplader', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'spær', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'spærer', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'lægt', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'lægter', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'undertag', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagrende', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagsten', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagrenovering', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'tagudskiftning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tage'), 'nedtagning', 0.9)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Vægge
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'væg', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'vægge', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'facade', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'facader', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'puds', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'pudse', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'maling', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'isolering', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'vægbeklædning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vægge'), 'gipsvæg', 0.9)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- El-arbejde
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'el'), 'el', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'elektriker', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'elektrik', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'stikkontakt', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'stikkontakter', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'belysning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'lampe', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'lamper', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'ledning', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'ledninger', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'elskab', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'el'), 'afbryder', 0.8)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- VVS
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'vvs', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'blikkenslager', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'rør', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'rørføring', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'badeværelse', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'køkken', 0.6),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'vandhane', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'vandhaner', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'afløb', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'kloakering', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'toilet', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'vvs'), 'brusekabine', 0.8)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Tømrerarbejde
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'tømrer', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'tømrerarbejde', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'træ', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'træværk', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'konstruktion', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'montering', 0.6),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'bjælke', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'bjælker', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'tømrer'), 'skelet', 0.7)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Maler arbejde
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'maler', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'malerarbejde', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'male', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'maling', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'spartling', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'spartle', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'overfladebehandling', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'lakering', 0.8),
((SELECT id FROM task_categories WHERE category_key = 'maler'), 'tapet', 0.7)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- Nedrivning
INSERT INTO category_keywords (category_id, keyword, weight) VALUES
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'nedrivning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'nedrive', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'nedtagning', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'nedtage', 1.0),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'fjernelse', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'fjerne', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'demontering', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'demontere', 0.9),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'bortkørsel', 0.7),
((SELECT id FROM task_categories WHERE category_key = 'nedrivning'), 'affald', 0.6)
ON DUPLICATE KEY UPDATE weight = VALUES(weight);
-- =====================================================
-- Step 6: Opret view for statistik
-- =====================================================
CREATE OR REPLACE VIEW v_category_statistics AS
SELECT
tc.id as category_id,
tc.category_name,
tc.category_key,
tc.icon,
tc.color_code,
COUNT(pt.id) as task_count,
SUM(pt.estimated_hours) as total_estimated_hours,
AVG(pt.estimated_hours) as avg_hours_per_task,
SUM(pt.actual_hours) as total_actual_hours,
SUM(pt.actual_cost) as total_cost,
AVG(pt.category_confidence) as avg_confidence,
COUNT(CASE WHEN pt.auto_categorized = TRUE THEN 1 END) as auto_categorized_count,
COUNT(CASE WHEN pt.auto_categorized = FALSE OR pt.auto_categorized IS NULL THEN 1 END) as manual_categorized_count
FROM task_categories tc
LEFT JOIN project_tasks pt ON pt.category_id = tc.id
GROUP BY tc.id, tc.category_name, tc.category_key, tc.icon, tc.color_code
ORDER BY task_count DESC;
-- =====================================================
-- Migration Complete
-- =====================================================
SELECT 'Feature 1 migration completed successfully!' as status;
SELECT CONCAT('Created ', COUNT(*), ' task categories') as result FROM task_categories;
SELECT CONCAT('Created ', COUNT(*), ' category keywords') as result FROM category_keywords;
Reference in New Issue View Git Blame Copy Permalink