-- Add unit conversion fields to units table
-- This allows units to define conversion relationships (e.g., g -> kg)

-- Add columns if they don't exist
ALTER TABLE units ADD COLUMN IF NOT EXISTS base_unit INT NULL COMMENT 'Reference to base unit for conversion';
ALTER TABLE units ADD COLUMN IF NOT EXISTS conversion_factor DECIMAL(20,10) NULL COMMENT 'Multiply by this to convert to base unit (e.g., g to kg = 0.001)';

-- Add foreign key
ALTER TABLE units ADD CONSTRAINT fk_units_base_unit FOREIGN KEY (base_unit) REFERENCES units(id) ON DELETE SET NULL;

-- Create index for faster lookups
CREATE INDEX IF NOT EXISTS idx_units_base_unit ON units(base_unit);
CREATE INDEX IF NOT EXISTS idx_units_code ON units(code);

-- Example conversions (update existing units if they exist)

-- Weight conversions (base unit: kg)
-- kg is the base unit (no conversion needed)
-- UPDATE units SET base_unit = NULL, conversion_factor = NULL WHERE code = 'kg';

-- g -> kg: 1g = 0.001kg
-- UPDATE units SET base_unit = (SELECT id FROM units WHERE code='kg'), conversion_factor = 0.001 WHERE code = 'g';

-- mg -> kg: 1mg = 0.000001kg
-- UPDATE units SET base_unit = (SELECT id FROM units WHERE code='kg'), conversion_factor = 0.000001 WHERE code = 'mg';

-- Volume conversions (base unit: l)
-- l is the base unit
-- UPDATE units SET base_unit = NULL, conversion_factor = NULL WHERE code = 'l';

-- ml -> l: 1ml = 0.001l
-- UPDATE units SET base_unit = (SELECT id FROM units WHERE code='l'), conversion_factor = 0.001 WHERE code = 'ml';

-- Note: Uncomment UPDATE statements above after verifying your unit codes match
-- You can also insert new units with conversions directly:

/*
Example: Insert units with conversions

INSERT INTO units (code, name, scope, sort_order, is_active, base_unit, conversion_factor) VALUES
-- Base units (no conversion)
('kg', 'Kilogram', 'both', 1, 1, NULL, NULL),
('l', 'Liter', 'both', 2, 1, NULL, NULL),

-- Derived units
('g', 'Gram', 'both', 3, 1, (SELECT id FROM units WHERE code='kg'), 0.001),
('mg', 'Miligram', 'both', 4, 1, (SELECT id FROM units WHERE code='kg'), 0.000001),
('ml', 'Mililiter', 'both', 5, 1, (SELECT id FROM units WHERE code='l'), 0.001);
*/
