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