-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Include the contents of schema.sql here
-- [Previous schema.sql content goes here]

-- Include the contents of demo-data.sql here
-- [Previous demo-data.sql content goes here]

-- Create necessary indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_restaurants_owner ON restaurants(owner_id);
CREATE INDEX idx_menu_items_restaurant ON menu_items(restaurant_id);
CREATE INDEX idx_menu_items_category ON menu_items(category_id);
CREATE INDEX idx_orders_restaurant ON orders(restaurant_id);
CREATE INDEX idx_orders_customer ON orders(customer_id);

-- Create necessary functions
CREATE OR REPLACE FUNCTION get_revenue_analytics(time_range TEXT)
RETURNS TABLE (
  date DATE,
  amount DECIMAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    DATE_TRUNC('day', created_at)::DATE,
    SUM(total_amount)
  FROM orders
  WHERE
    CASE
      WHEN time_range = 'week' THEN created_at >= NOW() - INTERVAL '7 days'
      WHEN time_range = 'month' THEN created_at >= NOW() - INTERVAL '30 days'
      WHEN time_range = 'year' THEN created_at >= NOW() - INTERVAL '365 days'
    END
  GROUP BY 1
  ORDER BY 1;
END;
$$ LANGUAGE plpgsql;

-- Add row level security policies
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE restaurants ENABLE ROW LEVEL SECURITY;
ALTER TABLE menu_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "Users can view their own data"
  ON users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Restaurant owners can view their restaurants"
  ON restaurants FOR SELECT
  USING (auth.uid() = owner_id);

CREATE POLICY "Public can view active menu items"
  ON menu_items FOR SELECT
  USING (active = true);

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