-- Create restaurant_photos table to store restaurant photos
-- Connected to restaurants table via restaurant_id foreign key

CREATE TABLE IF NOT EXISTS restaurant_photos (
    photo_id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID NOT NULL,
    photo_path VARCHAR(1000) NOT NULL,
    photo_url VARCHAR(2000),
    photo_caption TEXT,
    is_primary BOOLEAN DEFAULT FALSE,
    photo_type VARCHAR(50) DEFAULT 'general', -- 'general', 'interior', 'exterior', 'food', 'logo'
    file_size BIGINT,
    width INTEGER,
    height INTEGER,
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    -- Foreign key constraint
    CONSTRAINT fk_restaurant_photos_restaurant 
        FOREIGN KEY (restaurant_id) 
        REFERENCES restaurants(restaurant_id) 
        ON DELETE CASCADE,
    
    -- Ensure unique primary photo per restaurant
    CONSTRAINT unique_primary_photo_per_restaurant 
        EXCLUDE (restaurant_id WITH =) 
        WHERE (is_primary = TRUE)
);

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_restaurant_photos_restaurant_id ON restaurant_photos(restaurant_id);
CREATE INDEX IF NOT EXISTS idx_restaurant_photos_is_primary ON restaurant_photos(is_primary);
CREATE INDEX IF NOT EXISTS idx_restaurant_photos_photo_type ON restaurant_photos(photo_type);

-- Add comments for documentation
COMMENT ON TABLE restaurant_photos IS 'Stores photos for restaurants with metadata and file information';
COMMENT ON COLUMN restaurant_photos.photo_id IS 'Unique identifier for each photo';
COMMENT ON COLUMN restaurant_photos.restaurant_id IS 'Foreign key reference to restaurants table';
COMMENT ON COLUMN restaurant_photos.photo_path IS 'Local file system path to the stored photo';
COMMENT ON COLUMN restaurant_photos.photo_url IS 'Original URL where photo was downloaded from';
COMMENT ON COLUMN restaurant_photos.is_primary IS 'Whether this is the primary/main photo for the restaurant';
COMMENT ON COLUMN restaurant_photos.photo_type IS 'Type of photo: general, interior, exterior, food, logo';