-- Feature 2: Geometry Calculation Improvements
-- Date: 2025-01-XX
-- Description: Forbedrer tag geometri beregning med stern-til-kip højde, vinkel og spærafstand

-- Rename wall_height to stern_to_ridge_height for klarhed
ALTER TABLE roof_geometry 
CHANGE COLUMN wall_height stern_to_ridge_height DECIMAL(8,2) DEFAULT 0.00 COMMENT 'Højde fra stern til kip i meter';

-- Tilføj nye felter til geometri beregning
ALTER TABLE roof_geometry
ADD COLUMN roof_angle_degrees DECIMAL(5,2) DEFAULT NULL COMMENT 'Tagvinkel i grader (alternativ til roof_pitch)',
ADD COLUMN spaer_distance DECIMAL(5,2) DEFAULT 0.6 COMMENT 'Afstand mellem spær i meter (0.6m eller 1.0m standard)',
ADD COLUMN spaer_count INT DEFAULT NULL COMMENT 'Antal spær beregnet baseret på længde og afstand',
ADD COLUMN total_spaer_length DECIMAL(10,2) DEFAULT NULL COMMENT 'Total længde af alle spær i meter',
ADD COLUMN allow_angle_edit BOOLEAN DEFAULT TRUE COMMENT 'Om vinkel kan redigeres uden at nulstille andre værdier',
ADD COLUMN calculation_method ENUM('pitch', 'angle', 'manual') DEFAULT 'pitch' COMMENT 'Metode brugt til beregning af taggeometri',
ADD COLUMN actual_spaer_distance DECIMAL(5,2) DEFAULT NULL COMMENT 'Faktisk spærafstand efter beregning';

-- Tilføj index til ofte brugte søgefelter
ALTER TABLE roof_geometry
ADD INDEX idx_roof_angle (roof_angle_degrees),
ADD INDEX idx_calculation_method (calculation_method);

-- Opdater eksisterende records med default værdier
UPDATE roof_geometry 
SET 
  spaer_distance = 0.6,
  allow_angle_edit = TRUE,
  calculation_method = 'pitch'
WHERE spaer_distance IS NULL;

-- Beregn roof_angle_degrees baseret på eksisterende roof_pitch (hvis muligt)
UPDATE roof_geometry 
SET roof_angle_degrees = DEGREES(ATAN(roof_pitch))
WHERE roof_pitch IS NOT NULL AND roof_angle_degrees IS NULL;

-- Beregn spær count baseret på længde (hvis muligt)
UPDATE roof_geometry 
SET spaer_count = CEIL(length_main / spaer_distance) + 1
WHERE length_main IS NOT NULL AND spaer_distance IS NOT NULL AND spaer_count IS NULL;

-- Beregn actual_spaer_distance baseret på faktisk antal spær
UPDATE roof_geometry 
SET actual_spaer_distance = length_main / (spaer_count - 1)
WHERE length_main IS NOT NULL AND spaer_count > 1 AND actual_spaer_distance IS NULL;

-- Vis status
SELECT 'Feature 2 Migration Complete!' as Status;
SELECT 
  COUNT(*) as total_records,
  SUM(CASE WHEN roof_angle_degrees IS NOT NULL THEN 1 ELSE 0 END) as records_with_angle,
  SUM(CASE WHEN spaer_count IS NOT NULL THEN 1 ELSE 0 END) as records_with_spaer_count,
  AVG(spaer_distance) as avg_spaer_distance
FROM roof_geometry;