-- ============================================================
--  Magic Tech — ربط المبيعات (نسخة 3): قبول ذرّي + تراجع + مرتجعات
--  شغّله بعد v2 (آمن/idempotent). Supabase → SQL Editor → Run
-- ============================================================

-- ---------- 1) سجل الخصومات الفعلية (لإلغاء القبول بدقة) ----------
create table if not exists public.sales_order_deductions (
  id            bigint generated always as identity primary key,
  order_id      bigint not null references public.sales_orders(id) on delete cascade,
  order_item_id bigint,
  item_id       text not null,
  warehouse     text not null,
  qty           numeric not null,
  created_at    timestamptz not null default now()
);
create index if not exists idx_sod_order on public.sales_order_deductions(order_id);

-- ---------- 2) المرتجعات ----------
create table if not exists public.sales_returns (
  id          bigint generated always as identity primary key,
  order_id    bigint references public.sales_orders(id) on delete set null,
  external_id text,
  note        text,
  status      text not null default 'done',
  created_by  uuid,
  created_at  timestamptz not null default now()
);
create table if not exists public.sales_return_items (
  id            bigint generated always as identity primary key,
  return_id     bigint not null references public.sales_returns(id) on delete cascade,
  order_item_id bigint,
  item_id       text not null,
  model         text,
  qty           numeric not null,
  warehouse     text not null
);
create index if not exists idx_sri_return on public.sales_return_items(return_id);
create index if not exists idx_sri_orderitem on public.sales_return_items(order_item_id);
create index if not exists idx_sr_order on public.sales_returns(order_id);

-- ---------- 3) RLS للجداول الجديدة ----------
alter table public.sales_order_deductions enable row level security;
alter table public.sales_returns          enable row level security;
alter table public.sales_return_items     enable row level security;

drop policy if exists "auth_all_sod" on public.sales_order_deductions;
drop policy if exists "auth_all_sr"  on public.sales_returns;
drop policy if exists "auth_all_sri" on public.sales_return_items;
create policy "auth_all_sod" on public.sales_order_deductions for all to authenticated using (true) with check (true);
create policy "auth_all_sr"  on public.sales_returns          for all to authenticated using (true) with check (true);
create policy "auth_all_sri" on public.sales_return_items     for all to authenticated using (true) with check (true);

-- ---------- 4) قبول ذرّي (كله-أو-لا-شي + قفل ضد الخصم المزدوج) ----------
-- p_lines = [{ line_id, item_id, warehouse, qty, deduct:[{wh,q}, ...] }, ...]
create or replace function public.accept_sales_order(p_order_id bigint, p_lines jsonb)
returns jsonb language plpgsql security invoker set search_path = public as $$
declare
  v_uid    uuid := auth.uid();
  v_status text; v_inv text; v_txdate date; v_note text;
  v_line jsonb; v_ded jsonb;
  v_item text; v_wh text; v_dq numeric; v_lqty numeric; v_cur numeric; v_next numeric;
begin
  if v_uid is null then raise exception 'unauthorized'; end if;

  select status, coalesce(invoice_no, external_id, id::text), coalesce(invoice_date::date, current_date)
    into v_status, v_inv, v_txdate
  from sales_orders where id = p_order_id for update;
  if not found then raise exception 'order not found'; end if;
  if v_status <> 'pending' then raise exception 'الطلب تمّت معالجته مسبقاً (%).', v_status; end if;

  v_note := 'مبيعات: فاتورة #' || v_inv;

  for v_line in select value from jsonb_array_elements(p_lines) as t(value) loop
    v_item := v_line->>'item_id';
    v_lqty := coalesce((v_line->>'qty')::numeric, 0);
    if v_item is null then raise exception 'بند بدون صنف'; end if;

    for v_ded in select value from jsonb_array_elements(coalesce(v_line->'deduct','[]'::jsonb)) as d(value) loop
      v_wh := lower(v_ded->>'wh');
      v_dq := coalesce((v_ded->>'q')::numeric, 0);
      if v_dq <= 0 then continue; end if;
      if v_wh not in ('r1','m1','m2','m3','m4','g1','g2','g3','g4','s1','s2','office') then
        raise exception 'مستودع غير صالح: %', v_wh; end if;

      execute format('select %I from items where item_id::text = $1 for update', v_wh) into v_cur using v_item;
      if v_cur is null then raise exception 'الصنف غير موجود: %', v_item; end if;
      v_next := coalesce(v_cur,0) - v_dq;
      if v_next < 0 then raise exception 'نقص رصيد للصنف % في % (متوفر %)', v_item, upper(v_wh), v_cur; end if;

      execute format('update items set %I = $1 where item_id::text = $2', v_wh) using v_next, v_item;
      insert into stock_moves(move_type,item_id,warehouse,qty,note,tx_date,created_by)
        values ('out', v_item, v_wh, v_dq, v_note, v_txdate, v_uid);
      insert into sales_order_deductions(order_id, order_item_id, item_id, warehouse, qty)
        values (p_order_id, nullif(v_line->>'line_id','')::bigint, v_item, v_wh, v_dq);
    end loop;

    update sales_order_items
       set item_id = v_item, warehouse = v_line->>'warehouse',
           fulfilled_qty = v_lqty, qty = v_lqty, match_status = 'matched'
     where id = nullif(v_line->>'line_id','')::bigint;
  end loop;

  update sales_orders set status='accepted', accepted_by=v_uid, accepted_at=now() where id=p_order_id;
  return jsonb_build_object('ok', true);
end; $$;
grant execute on function public.accept_sales_order(bigint, jsonb) to authenticated;

-- ---------- 5) تراجع عن القبول (يرجّع الكميات بدقة) ----------
create or replace function public.reverse_sales_order(p_order_id bigint)
returns jsonb language plpgsql security invoker set search_path = public as $$
declare
  v_uid uuid := auth.uid(); v_status text; v_inv text; v_txdate date; v_d record; v_cnt int;
begin
  if v_uid is null then raise exception 'unauthorized'; end if;
  select status, coalesce(invoice_no, external_id, id::text), coalesce(invoice_date::date, current_date)
    into v_status, v_inv, v_txdate
  from sales_orders where id = p_order_id for update;
  if not found then raise exception 'order not found'; end if;
  if v_status <> 'accepted' then raise exception 'الفاتورة ليست مقبولة (%).', v_status; end if;

  select count(*) into v_cnt from sales_returns where order_id = p_order_id;
  if v_cnt > 0 then raise exception 'يوجد مرتجعات على هذه الفاتورة — احذف المرتجع أولاً قبل التراجع.'; end if;

  for v_d in select * from sales_order_deductions where order_id = p_order_id loop
    execute format('update items set %I = coalesce(%I,0) + $1 where item_id::text = $2', v_d.warehouse, v_d.warehouse)
      using v_d.qty, v_d.item_id;
    insert into stock_moves(move_type,item_id,warehouse,qty,note,tx_date,created_by)
      values ('in', v_d.item_id, v_d.warehouse, v_d.qty, 'إلغاء قبول فاتورة #'||v_inv, v_txdate, v_uid);
  end loop;

  delete from sales_order_deductions where order_id = p_order_id;
  update sales_order_items set fulfilled_qty = 0, warehouse = null where order_id = p_order_id;
  update sales_orders set status='pending', accepted_by=null, accepted_at=null where id=p_order_id;
  return jsonb_build_object('ok', true);
end; $$;
grant execute on function public.reverse_sales_order(bigint) to authenticated;

-- ---------- 6) معالجة مرتجع (بنود وكميات محددة → يرجّع للمستودع) ----------
-- p_items = [{ line_id, item_id, model, warehouse, qty }, ...]
create or replace function public.process_return(p_order_id bigint, p_items jsonb, p_note text)
returns jsonb language plpgsql security invoker set search_path = public as $$
declare
  v_uid uuid := auth.uid(); v_inv text; v_txdate date := current_date;
  v_ret_id bigint; v_it jsonb; v_item text; v_wh text; v_qty numeric; v_n int := 0;
begin
  if v_uid is null then raise exception 'unauthorized'; end if;
  select coalesce(invoice_no, external_id, id::text) into v_inv from sales_orders where id = p_order_id;
  if v_inv is null then raise exception 'order not found'; end if;

  insert into sales_returns(order_id, note, status, created_by)
    values (p_order_id, p_note, 'done', v_uid) returning id into v_ret_id;

  for v_it in select value from jsonb_array_elements(p_items) as t(value) loop
    v_item := v_it->>'item_id';
    v_wh   := lower(v_it->>'warehouse');
    v_qty  := coalesce((v_it->>'qty')::numeric, 0);
    if v_item is null or v_qty <= 0 then continue; end if;
    if v_wh not in ('r1','m1','m2','m3','m4','g1','g2','g3','g4','s1','s2','office') then
      raise exception 'مستودع غير صالح: %', v_wh; end if;

    execute format('update items set %I = coalesce(%I,0) + $1 where item_id::text = $2', v_wh, v_wh)
      using v_qty, v_item;
    insert into stock_moves(move_type,item_id,warehouse,qty,note,tx_date,created_by)
      values ('in', v_item, v_wh, v_qty, 'مرتجع فاتورة #'||v_inv||coalesce(' — '||nullif(p_note,''),''), v_txdate, v_uid);
    insert into sales_return_items(return_id, order_item_id, item_id, model, qty, warehouse)
      values (v_ret_id, nullif(v_it->>'line_id','')::bigint, v_item, v_it->>'model', v_qty, v_wh);
    v_n := v_n + 1;
  end loop;

  if v_n = 0 then
    delete from sales_returns where id = v_ret_id;
    raise exception 'لم تحدّد أي بند للإرجاع';
  end if;
  return jsonb_build_object('ok', true, 'return_id', v_ret_id, 'lines', v_n);
end; $$;
grant execute on function public.process_return(bigint, jsonb, text) to authenticated;

-- ============================================================
--  تم. الواجهة بتستخدم هالدوال تلقائياً بعد رفع النسخة.
-- ============================================================