schema.sql

-- 1. Enable UUID extension (Good practice)
create extension if not exists "uuid-ossp";

-- 2. TABLE: Menu Items
create table public.menu_items (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  name text not null,
  arabic_name text,
  price numeric not null,
  description text,
  image_url text,
  category_tag text default 'Classic',
  daily_limit integer default 50,
  is_available boolean default true
);

-- 3. TABLE: Daily Capacities (For reservations)
create table public.daily_capacities (
  id bigint generated by default as identity primary key,
  date date not null unique,
  day_name text,
  max_capacity integer default 50,
  current_fill integer default 0,
  is_closed boolean default false
);

-- 4. TABLE: Orders
create table public.orders (
  id bigint generated by default as identity primary key,
  created_at timestamp with time zone default timezone('utc'::text, now()) not null,
  customer_name text not null,
  customer_phone text not null,
  delivery_address text,
  order_type text not null, -- 'DELIVERY' or 'PICKUP'
  status text default 'NEW', -- 'NEW', 'PREPARING', 'READY', 'COMPLETED'
  payment_method text default 'COD',
  total numeric not null,
  delivery_date date not null,
  notes text,
  order_items jsonb not null -- Stores the array of items bought
);

-- 5. FUNCTION: Efficiently count sold items (Bypasses RLS)
create or replace function get_sold_counts(target_date date)
returns table (item_id int, sold_count bigint) 
language plpgsql
security definer
as $$
begin
  return query
  select 
    (elem->>'item_id')::int as item_id,
    sum((elem->>'quantity')::int) as sold_count
  from orders,
       jsonb_array_elements(orders.order_items) as elem
  where delivery_date = target_date
  group by (elem->>'item_id')::int;
end;
$$;

-- 6. FUNCTION: Calculate Popular Items (For Admin Dashboard)
create or replace function get_popular_items()
returns table (name text, count bigint, total_revenue numeric) 
language plpgsql
security definer
as $$
begin
  return query
  select 
    (elem->>'name')::text as name,
    sum((elem->>'quantity')::int) as count,
    sum((elem->>'quantity')::int * (elem->>'price')::numeric) as total_revenue
  from orders,
       jsonb_array_elements(orders.order_items) as elem
  group by name
  order by count desc
  limit 4;
end;
$$;

-- 7. FUNCTION: Increment Daily Fill (Updates capacity when order is placed)
-- Note: You mentioned this RPC in CartOverlay.jsx, adding it here for completeness.
create or replace function increment_daily_fill(target_date date, fill_amount int)
returns void
language plpgsql
security definer
as $$
begin
  insert into daily_capacities (date, current_fill)
  values (target_date, fill_amount)
  on conflict (date) 
  do update set current_fill = daily_capacities.current_fill + fill_amount;
end;
$$;

-- 8. INDEXES (For Performance on Free Tier)
create index if not exists idx_orders_date on public.orders (delivery_date);
create index if not exists idx_capacities_date on public.daily_capacities (date);