-- Migration: Convert qty_per_hr from VARCHAR to DECIMAL(10,2)
-- Date: 2026-01-22
-- Purpose: Enable numeric calculation of duration (qty / qty_per_hr)
-- Current state: qty_per_hr is VARCHAR(64), may contain text values like "4", "1.2", "1,2"

-- Step 1: Create temporary numeric column
ALTER TABLE llx_planning_job ADD COLUMN qty_per_hr_num DECIMAL(10,2) NULL DEFAULT NULL;

-- Step 2: Migrate data from VARCHAR to numeric (handle comma as decimal separator)
UPDATE llx_planning_job
SET qty_per_hr_num = CAST(REPLACE(REPLACE(qty_per_hr, ',', '.'), ' ', '') AS DECIMAL(10,2))
WHERE qty_per_hr IS NOT NULL
  AND qty_per_hr != ''
  AND REPLACE(REPLACE(qty_per_hr, ',', '.'), ' ', '') REGEXP '^[0-9]+(\.[0-9]+)?$';

-- Step 3: Validate migration (optional - shows count of successfully converted rows)
-- SELECT COUNT(*) as rows_migrated FROM llx_planning_job WHERE qty_per_hr_num IS NOT NULL;

-- Step 4: Drop old VARCHAR column
ALTER TABLE llx_planning_job DROP COLUMN qty_per_hr;

-- Step 5: Rename numeric column to final name
ALTER TABLE llx_planning_job CHANGE COLUMN qty_per_hr_num qty_per_hr DECIMAL(10,2) NULL DEFAULT NULL;
