-- ========================================
-- Smart Pakker Database Migration
-- ========================================

-- Opret smart_packages tabel
CREATE TABLE IF NOT EXISTS smart_packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  category VARCHAR(100),
  package_type VARCHAR(100),
  difficulty_level SMALLINT DEFAULT 1,
  estimated_hours DECIMAL(8,2) DEFAULT 0,
  hourly_rate DECIMAL(10,2) DEFAULT 0,
  installation_notes TEXT,
  is_template BOOLEAN DEFAULT FALSE,
  created_by VARCHAR(100),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Opret smart_package_tasks tabel
CREATE TABLE IF NOT EXISTS smart_package_tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  hours DECIMAL(6,2) DEFAULT 0,
  rate DECIMAL(10,2) DEFAULT 0,
  skills TEXT,
  tools TEXT,
  optional BOOLEAN DEFAULT FALSE,
  depends_on VARCHAR(255),
  installation_manual_id INT,
  task_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES smart_packages(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Opret smart_package_steps tabel
CREATE TABLE IF NOT EXISTS smart_package_steps (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  minutes INT DEFAULT 0,
  image VARCHAR(255),
  video VARCHAR(255),
  safety TEXT,
  quality TEXT,
  tips TEXT,
  step_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES smart_package_tasks(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Opret smart_package_materials tabel
CREATE TABLE IF NOT EXISTS smart_package_materials (
  id INT AUTO_INCREMENT PRIMARY KEY,
  package_id INT NOT NULL,
  material_id INT NOT NULL,
  quantity DECIMAL(10,2) DEFAULT 1,
  quantity_formula VARCHAR(255),
  custom_price DECIMAL(10,2),
  is_required BOOLEAN DEFAULT TRUE,
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES smart_packages(id) ON DELETE CASCADE,
  FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Opret smart_package_categories tabel (reference data)
CREATE TABLE IF NOT EXISTS smart_package_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  parent_id INT,
  icon VARCHAR(100),
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (parent_id) REFERENCES smart_package_categories(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Opret smart_package_types tabel (reference data)
CREATE TABLE IF NOT EXISTS smart_package_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  description TEXT,
  icon VARCHAR(100),
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_danish_ci;

-- Indsæt standard kategorier
INSERT INTO smart_package_categories (name, icon, display_order) 
VALUES 
('Tag', 'roof', 10),
('Facade', 'wall', 20),
('Terrasse', 'deck', 30),
('Carport', 'garage', 40),
('Udestue', 'sunroom', 50),
('Tilbygning', 'home-plus', 60),
('Andet', 'package', 100);

-- Indsæt standard pakketyper
INSERT INTO smart_package_types (name, description, icon, display_order) 
VALUES 
('Standard', 'Standard pakke med materialer og montering', 'package-variant-closed', 10),
('Premium', 'Premium pakke med ekstra kvalitetsmaterialer', 'package-variant-plus', 20),
('Komplet', 'Alt-i-én pakke med materialer, montering og tilbehør', 'package-variant-closed-check', 30),
('Basis', 'Kun grundlæggende materialer', 'package-variant', 40);

-- Tilføj index til hurtigere søgning
-- Tjek om kolonner findes før vi laver indeks
SET @exist := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'tilbudgivern'
    AND TABLE_NAME = 'smart_packages'
    AND COLUMN_NAME = 'category');

SET @query = IF(@exist > 0, 
    'CREATE INDEX idx_smart_packages_category ON smart_packages(category)',
    'SELECT "Skipping category index - column does not exist"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @exist := (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'tilbudgivern'
    AND TABLE_NAME = 'smart_packages'
    AND COLUMN_NAME = 'package_type');

SET @query = IF(@exist > 0, 
    'CREATE INDEX idx_smart_packages_type ON smart_packages(package_type)',
    'SELECT "Skipping package_type index - column does not exist"');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Resterende indeks - tasks
SET @exist_tasks := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = 'tilbudgivern'
  AND TABLE_NAME = 'smart_package_tasks'
  AND INDEX_NAME = 'idx_smart_package_tasks_package'
);

SET @query_tasks = IF(@exist_tasks = 0, 
    'CREATE INDEX idx_smart_package_tasks_package ON smart_package_tasks(package_id)',
    'SELECT "Index idx_smart_package_tasks_package already exists"');
PREPARE stmt FROM @query_tasks;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Indeks for steps
SET @exist_steps := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = 'tilbudgivern'
  AND TABLE_NAME = 'smart_package_steps'
  AND INDEX_NAME = 'idx_smart_package_steps_task'
);

SET @query_steps = IF(@exist_steps = 0, 
    'CREATE INDEX idx_smart_package_steps_task ON smart_package_steps(task_id)',
    'SELECT "Index idx_smart_package_steps_task already exists"');
PREPARE stmt FROM @query_steps;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Indeks for materials
SET @exist_materials := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = 'tilbudgivern'
  AND TABLE_NAME = 'smart_package_materials'
  AND INDEX_NAME = 'idx_smart_package_materials_package'
);

SET @query_materials = IF(@exist_materials = 0, 
    'CREATE INDEX idx_smart_package_materials_package ON smart_package_materials(package_id)',
    'SELECT "Index idx_smart_package_materials_package already exists"');
PREPARE stmt FROM @query_materials;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Indeks for materials relation til material_id
SET @exist_materials_rel := (
  SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS 
  WHERE TABLE_SCHEMA = 'tilbudgivern'
  AND TABLE_NAME = 'smart_package_materials'
  AND INDEX_NAME = 'idx_smart_package_materials_material'
);

SET @query_materials_rel = IF(@exist_materials_rel = 0, 
    'CREATE INDEX idx_smart_package_materials_material ON smart_package_materials(material_id)',
    'SELECT "Index idx_smart_package_materials_material already exists"');
PREPARE stmt FROM @query_materials_rel;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;