-- Create the restaurants table
CREATE TABLE restaurants (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    description TEXT,
    address TEXT,
    phone TEXT,
    opening_hours JSONB,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE
);

-- Create the reservations table
CREATE TABLE reservations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
    date DATE NOT NULL,
    time TIME NOT NULL,
    num_guests INTEGER NOT NULL,
    customer_name TEXT NOT NULL,
    customer_email TEXT NOT NULL,
    customer_phone TEXT NOT NULL,
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    status TEXT CHECK (status IN ('pending', 'confirmed', 'cancelled')) DEFAULT 'pending'
);

-- Create the profiles table to store user roles and restaurant associations
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT CHECK (role IN ('owner', 'staff')),
  restaurant_id UUID REFERENCES restaurants(id) ON DELETE CASCADE,
  invited_by UUID REFERENCES profiles(id)
);

-- Enable Row Level Security (RLS) on all tables
ALTER TABLE restaurants ENABLE ROW LEVEL SECURITY;
ALTER TABLE reservations ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;

-- RLS Policies for restaurants table
CREATE POLICY "Owners can manage their own restaurant" ON restaurants
    FOR ALL USING (auth.uid() = user_id);
CREATE POLICY "Staff can select their restaurant" ON restaurants
    FOR SELECT USING ((SELECT role FROM profiles WHERE id = auth.uid()) = 'staff' AND id IN (SELECT restaurant_id FROM profiles WHERE id = auth.uid()));
CREATE POLICY "Enable read access for all users" ON restaurants
    FOR SELECT USING (true);

-- RLS Policies for reservations table
CREATE POLICY "Owners and staff can manage reservations for their restaurant" ON reservations
    FOR ALL USING (restaurant_id IN (SELECT restaurant_id FROM profiles WHERE id = auth.uid()));
CREATE POLICY "Enable read access for checking availability (limited info)" ON reservations
    FOR SELECT USING (true); -- Consider limiting columns returned here

-- RLS Policies for profiles table
CREATE POLICY "Users can manage their own profile" ON profiles
    FOR ALL USING (auth.uid() = id);
CREATE POLICY "Owners can manage users associated with their restaurant" ON profiles
    FOR ALL USING ((SELECT role FROM profiles WHERE id = auth.uid()) = 'owner' AND restaurant_id IN (SELECT restaurant_id FROM profiles WHERE id = auth.uid()));
CREATE POLICY "Staff can select users associated with their restaurant" ON profiles
    FOR SELECT USING ((SELECT role FROM profiles WHERE id = auth.uid()) = 'staff' AND restaurant_id IN (SELECT restaurant_id FROM profiles WHERE id = auth.uid()));

-- Enable the uuid-ossp extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
