-- SQL Queries til at hente monteringsopgaver for tilbud

-- 1. Hent komplet opgavebeskrivelse for et materiale
SELECT 
  product_name,
  manufacturer,
  time_estimate_per_unit,
  time_unit,
  skill_level,
  installation_steps,
  key_points,
  weather_conditions
FROM installation_manuals
WHERE product_name LIKE '%Bølgeplade%';

-- 2. Hent installation steps som tekstliste (til opgavebeskrivelse)
SELECT 
  product_name,
  GROUP_CONCAT(
    CONCAT(step_num, '. ', step_text)
    ORDER BY step_num
    SEPARATOR '\n'
  ) as opgave_beskrivelse
FROM (
  SELECT 
    product_name,
    (@row_number:=@row_number + 1) as step_num,
    JSON_UNQUOTE(JSON_EXTRACT(installation_steps, CONCAT('$[', @row_number - 1, ']'))) as step_text
  FROM installation_manuals,
  (SELECT @row_number:=0) as t
  WHERE JSON_LENGTH(installation_steps) > 0
) steps
GROUP BY product_name;

-- 3. Beregn opgavetid for specifik mængde
SELECT 
  product_name,
  time_estimate_per_unit,
  50 as quantity_m2,  -- Eksempel: 50 m²
  (time_estimate_per_unit * 50) as total_hours,
  CEIL((time_estimate_per_unit * 50) / 8) as work_days
FROM installation_manuals
WHERE product_name LIKE '%Bølgeplade%';

-- 4. Hent key points som bullet points (til tilbud)
SELECT 
  product_name,
  JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[0]')) as key_point_1,
  JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[1]')) as key_point_2,
  JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[2]')) as key_point_3,
  JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[3]')) as key_point_4,
  JSON_UNQUOTE(JSON_EXTRACT(key_points, '$[4]')) as key_point_5
FROM installation_manuals;

-- 5. Formater installation steps til brug i PDF tilbud
SELECT 
  id,
  product_name,
  CONCAT(
    'MONTAGEVEJLEDNING FOR ', UPPER(product_name), '\n\n',
    'Estimeret tid: ', time_estimate_per_unit, ' timer per m²\n',
    'Sværhedsgrad: ', 
    CASE skill_level 
      WHEN 'let' THEN 'Let ⭐'
      WHEN 'medium' THEN 'Medium ⭐⭐'
      WHEN 'svær' THEN 'Svær ⭐⭐⭐'
    END, '\n\n',
    'MONTERINGSTRIN:\n',
    installation_steps,
    '\n\nVEJRFORHOLD:\n',
    COALESCE(weather_conditions, 'Ingen specifikke krav')
  ) as tilbud_beskrivelse
FROM installation_manuals;