-- Add address, city, and country_id columns to restaurants table
-- These columns will store location information for each restaurant

-- Add new columns
ALTER TABLE restaurants 
ADD COLUMN address VARCHAR(500),
ADD COLUMN city VARCHAR(100),
ADD COLUMN country_id INTEGER;

-- Add foreign key constraint for country_id
ALTER TABLE restaurants 
ADD CONSTRAINT fk_restaurant_country 
FOREIGN KEY (country_id) 
REFERENCES countries(country_id);

-- Add indexes for better query performance
CREATE INDEX idx_restaurants_city ON restaurants(city);
CREATE INDEX idx_restaurants_country_id ON restaurants(country_id);

-- Add comments for documentation
COMMENT ON COLUMN restaurants.address IS 'Street address of the restaurant';
COMMENT ON COLUMN restaurants.city IS 'City where the restaurant is located';
COMMENT ON COLUMN restaurants.country_id IS 'Foreign key reference to countries table';