CREATE TABLE ingredient_unit_converters (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    ingredient_id UUID NOT NULL REFERENCES ingredients(id) ON DELETE CASCADE,
    from_unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    to_unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
    conversion_factor DECIMAL(15,6) NOT NULL CHECK (conversion_factor > 0),
    is_active BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
    updated_by UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);

-- Create indexes for better performance
CREATE INDEX idx_ingredient_unit_converters_organization_id ON ingredient_unit_converters(organization_id);
CREATE INDEX idx_ingredient_unit_converters_ingredient_id ON ingredient_unit_converters(ingredient_id);
CREATE INDEX idx_ingredient_unit_converters_from_unit_id ON ingredient_unit_converters(from_unit_id);
CREATE INDEX idx_ingredient_unit_converters_to_unit_id ON ingredient_unit_converters(to_unit_id);
CREATE INDEX idx_ingredient_unit_converters_active ON ingredient_unit_converters(is_active);

-- Create unique constraint to prevent duplicate converters for the same ingredient and unit pair
CREATE UNIQUE INDEX idx_ingredient_unit_converters_unique 
ON ingredient_unit_converters(organization_id, ingredient_id, from_unit_id, to_unit_id) 
WHERE is_active = true;

-- Add comments for documentation
COMMENT ON TABLE ingredient_unit_converters IS 'Stores unit conversion factors for ingredients within an organization';
COMMENT ON COLUMN ingredient_unit_converters.conversion_factor IS 'How many from_unit_id units equal one to_unit_id unit (e.g., 1000 for grams to kilograms)';
COMMENT ON COLUMN ingredient_unit_converters.is_active IS 'Whether this conversion is currently active and can be used';
COMMENT ON COLUMN ingredient_unit_converters.created_by IS 'User who created this conversion rule';
COMMENT ON COLUMN ingredient_unit_converters.updated_by IS 'User who last updated this conversion rule';