-- Allergens table
CREATE TABLE public.allergens (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    name JSONB NOT NULL, -- Multilingual support
    description JSONB,
    icon_url TEXT,
    color VARCHAR(7), -- Hex color code
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Additives table
CREATE TABLE public.additives (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    name JSONB NOT NULL, -- Multilingual support
    description JSONB,
    number VARCHAR(10), -- E-number or similar
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Opening Hours table
CREATE TABLE public.opening_hours (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    day_of_week INTEGER NOT NULL CHECK (day_of_week BETWEEN 0 AND 6),
    open_time TIME,
    close_time TIME,
    is_closed BOOLEAN DEFAULT false,
    special_date DATE, -- For holiday hours
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(restaurant_id, day_of_week, special_date)
);

-- Social Media Links table
CREATE TABLE public.social_media_links (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    platform VARCHAR(50) NOT NULL, -- e.g., facebook, instagram, etc.
    url TEXT NOT NULL,
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Miscellaneous Settings table
CREATE TABLE public.misc_settings (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    key VARCHAR(100) NOT NULL,
    value JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(restaurant_id, key)
);

-- Analytics table
CREATE TABLE public.analytics_data (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES public.restaurants(id) NOT NULL,
    date DATE NOT NULL,
    metrics JSONB NOT NULL, -- Stores various metrics
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(restaurant_id, date)
);

-- Add menu item relationships
ALTER TABLE public.menu_items
ADD COLUMN allergens_ids UUID[] DEFAULT '{}',
ADD COLUMN additives_ids UUID[] DEFAULT '{}';

-- Create indexes
CREATE INDEX idx_allergens_restaurant ON public.allergens(restaurant_id);
CREATE INDEX idx_additives_restaurant ON public.additives(restaurant_id);
CREATE INDEX idx_opening_hours_restaurant ON public.opening_hours(restaurant_id);
CREATE INDEX idx_social_media_restaurant ON public.social_media_links(restaurant_id);
CREATE INDEX idx_misc_settings_restaurant ON public.misc_settings(restaurant_id);
CREATE INDEX idx_analytics_restaurant_date ON public.analytics_data(restaurant_id, date);

-- Add RLS policies
ALTER TABLE public.allergens ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.additives ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.opening_hours ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.social_media_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.misc_settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.analytics_data ENABLE ROW LEVEL SECURITY;

-- Create RLS policies
CREATE POLICY "Restaurant owners can manage their allergens" ON public.allergens
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

CREATE POLICY "Restaurant owners can manage their additives" ON public.additives
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

CREATE POLICY "Restaurant owners can manage their opening hours" ON public.opening_hours
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

CREATE POLICY "Restaurant owners can manage their social media links" ON public.social_media_links
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

CREATE POLICY "Restaurant owners can manage their misc settings" ON public.misc_settings
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

CREATE POLICY "Restaurant owners can view their analytics" ON public.analytics_data
    FOR ALL USING (auth.uid() IN (
        SELECT owner_id FROM public.restaurants WHERE id = restaurant_id
    ));

-- Create updated_at triggers
CREATE TRIGGER set_allergens_updated_at
    BEFORE UPDATE ON public.allergens
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER set_additives_updated_at
    BEFORE UPDATE ON public.additives
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER set_opening_hours_updated_at
    BEFORE UPDATE ON public.opening_hours
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER set_social_media_updated_at
    BEFORE UPDATE ON public.social_media_links
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER set_misc_settings_updated_at
    BEFORE UPDATE ON public.misc_settings
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();

CREATE TRIGGER set_analytics_updated_at
    BEFORE UPDATE ON public.analytics_data
    FOR EACH ROW
    EXECUTE FUNCTION handle_updated_at();
