-- ============================================================
--  Magic Tech — ربط نظام المبيعات (نسخة 2 — مطابقة ذكية + ذاكرة تعلّم)
--  آمن للتشغيل حتى لو شغّلت النسخة الأولى (idempotent).
--  شغّله مرة واحدة في: Supabase → SQL Editor → Run
--  ⚠️ لا تنسَ تغيير السر CHANGE_ME_SECRET_123 في نهاية الملف.
-- ============================================================

-- ---------- 0) امتداد التشابه الضبابي ----------
create extension if not exists pg_trgm;

-- ---------- 1) دالة التطبيع (تشيل الشرطة/المسافة/النقطة + توحّد الأرقام العربية + أحرف صغيرة) ----------
-- "DS-2CD1043" = "ds 2cd1043" = "DS_2CD/1043" = "ds٢cd1043"  → كلها: ds2cd1043
create or replace function public.mt_norm(p text)
returns text language sql immutable as $$
  select regexp_replace(
           lower(translate(coalesce(p,''),
             '٠١٢٣٤٥٦٧٨٩۰۱۲۳۴۵۶۷۸۹',
             '01234567890123456789')),
           '[[:space:]_./,()\-]+', '', 'g');
$$;

-- فهارس تسريع المطابقة
create index if not exists idx_items_model_norm on public.items (mt_norm(model));
create index if not exists idx_items_model_trgm on public.items using gin (model gin_trgm_ops);

-- ---------- 2) الجداول (لو مش موجودة) ----------
create table if not exists public.sales_orders (
  id            bigint generated always as identity primary key,
  source        text        not null default 'sales',
  external_id   text,
  invoice_no    text,
  invoice_date  timestamptz,
  customer_name text,
  seller_name   text,
  note          text,
  status        text        not null default 'pending',
  total_amount  numeric,
  raw           jsonb,
  created_at    timestamptz not null default now(),
  accepted_by   uuid,
  accepted_at   timestamptz,
  unique (source, external_id)
);

create table if not exists public.sales_order_items (
  id            bigint generated always as identity primary key,
  order_id      bigint  not null references public.sales_orders(id) on delete cascade,
  line_no       int,
  barcode       text,
  model         text,
  description   text,
  qty           numeric not null default 0,
  unit_price    numeric,
  item_id       text,
  warehouse     text,
  fulfilled_qty numeric not null default 0,
  match_status  text    not null default 'unmatched',  -- matched | review | unmatched
  note          text
);

-- أعمدة جديدة للمطابقة الذكية
alter table public.sales_order_items add column if not exists match_score      numeric;
alter table public.sales_order_items add column if not exists match_candidates jsonb;

-- ---------- 3) جدول الذاكرة (تعلّم الربط) ----------
create table if not exists public.sales_model_map (
  id          bigint generated always as identity primary key,
  src_model   text,
  src_barcode text,
  item_id     text not null,
  created_by  uuid,
  created_at  timestamptz not null default now()
);
create index if not exists idx_smm_model_norm on public.sales_model_map (mt_norm(src_model));
create index if not exists idx_smm_barcode    on public.sales_model_map (src_barcode);

-- ---------- 4) الحماية (RLS) ----------
alter table public.sales_orders      enable row level security;
alter table public.sales_order_items enable row level security;
alter table public.sales_model_map   enable row level security;

drop policy if exists "auth_read_orders"        on public.sales_orders;
drop policy if exists "auth_update_orders"      on public.sales_orders;
drop policy if exists "auth_read_order_items"   on public.sales_order_items;
drop policy if exists "auth_update_order_items" on public.sales_order_items;
drop policy if exists "auth_all_model_map"      on public.sales_model_map;

create policy "auth_read_orders"        on public.sales_orders      for select to authenticated using (true);
create policy "auth_update_orders"      on public.sales_orders      for update to authenticated using (true) with check (true);
create policy "auth_read_order_items"   on public.sales_order_items for select to authenticated using (true);
create policy "auth_update_order_items" on public.sales_order_items for update to authenticated using (true) with check (true);
create policy "auth_all_model_map"      on public.sales_model_map   for all    to authenticated using (true) with check (true);

-- ---------- 5) دالة بحث المرشّحين (للواجهة: بحث مطبّع + ضبابي) ----------
create or replace function public.mt_match_candidates(q text, lim int default 25)
returns jsonb language sql stable
set search_path = public as $$
  select coalesce(jsonb_agg(row_to_json(t)), '[]'::jsonb) from (
    select i.item_id::text as item_id, i.brand, i.model, i.description,
           round(greatest(
             case when mt_norm(i.model) = mt_norm(q) then 1.0 else 0 end,
             similarity(i.model, q)
           )::numeric, 3) as score
    from items i
    where q is not null and length(q) >= 1
      and ( mt_norm(i.model) like '%'||mt_norm(q)||'%' or i.model % q )
    order by score desc, i.model
    limit lim
  ) t;
$$;
grant execute on function public.mt_match_candidates(text, int) to anon, authenticated;

-- ---------- 6) دالة الاستقبال الذكية (API لنظام المبيعات) ----------
create or replace function public.ingest_sales_order(payload jsonb)
returns jsonb
language plpgsql
security definer
set search_path = public
as $$
declare
  v_secret   text   := 'CHANGE_ME_SECRET_123';   -- 🔴 غيّره لسر قوي
  v_token    text   := payload->>'token';
  v_source   text   := coalesce(payload->>'source','sales');
  v_ext      text   := nullif(payload->>'external_id','');
  v_order_id bigint;
  v_item     jsonb;
  v_ln       int    := 0;
  v_barcode  text;
  v_model    text;
  v_item_id  text;
  v_status   text;
  v_score    numeric;
  v_cands    jsonb;
begin
  if v_token is distinct from v_secret then
    raise exception 'unauthorized: invalid token';
  end if;

  if v_ext is not null then
    select id into v_order_id from sales_orders where source = v_source and external_id = v_ext;
    if v_order_id is not null then
      return jsonb_build_object('ok', true, 'duplicate', true, 'order_id', v_order_id);
    end if;
  end if;

  insert into sales_orders(source, external_id, invoice_no, invoice_date,
                           customer_name, seller_name, note, total_amount, raw)
  values (v_source, v_ext, payload->>'invoice_no',
          nullif(payload->>'invoice_date','')::timestamptz,
          payload->>'customer_name', payload->>'seller_name', payload->>'note',
          nullif(payload->>'total_amount','')::numeric, payload)
  returning id into v_order_id;

  for v_item in
    select value from jsonb_array_elements(coalesce(payload->'items','[]'::jsonb)) as t(value)
  loop
    v_ln      := v_ln + 1;
    v_barcode := nullif(v_item->>'barcode','');
    v_model   := nullif(v_item->>'model','');
    v_item_id := null; v_status := 'unmatched'; v_score := null; v_cands := '[]'::jsonb;

    -- (أ) الذاكرة: ربط سابق محفوظ (باركود ثم موديل مطبّع)
    if v_barcode is not null then
      select item_id into v_item_id from sales_model_map
        where src_barcode = v_barcode order by created_at desc limit 1;
    end if;
    if v_item_id is null and v_model is not null then
      select item_id into v_item_id from sales_model_map
        where mt_norm(src_model) = mt_norm(v_model) order by created_at desc limit 1;
    end if;
    if v_item_id is not null then v_status := 'matched'; v_score := 1; end if;

    -- (ب) باركود تام
    if v_item_id is null and v_barcode is not null then
      select i.item_id::text into v_item_id from items i where i.barcode = v_barcode limit 1;
      if v_item_id is not null then v_status := 'matched'; v_score := 1; end if;
    end if;

    -- (ج) موديل تام
    if v_item_id is null and v_model is not null then
      select i.item_id::text into v_item_id from items i where lower(i.model) = lower(v_model) limit 1;
      if v_item_id is not null then v_status := 'matched'; v_score := 1; end if;
    end if;

    -- (د) موديل مطبّع (يحل مشكلة الشرطة/المسافة)
    if v_item_id is null and v_model is not null then
      select i.item_id::text into v_item_id from items i where mt_norm(i.model) = mt_norm(v_model) limit 1;
      if v_item_id is not null then v_status := 'matched'; v_score := 0.97; end if;
    end if;

    -- (هـ) تشابه ضبابي: ابنِ المرشّحين واحفظهم
    if v_model is not null then
      select coalesce(jsonb_agg(c order by (c->>'score')::numeric desc), '[]'::jsonb)
        into v_cands
      from (
        select jsonb_build_object(
                 'item_id', i.item_id::text, 'model', i.model, 'brand', i.brand,
                 'score', round(similarity(i.model, v_model)::numeric, 3)) as c
        from items i
        where i.model % v_model
        order by similarity(i.model, v_model) desc
        limit 4
      ) s;

      if v_item_id is null then
        v_score := nullif(v_cands->0->>'score','')::numeric;
        if v_score is not null and v_score >= 0.6 then
          v_item_id := v_cands->0->>'item_id';   -- اقتراح قوي → عبّئه لكن علّمه "review"
          v_status  := 'review';
        else
          v_status  := 'unmatched';              -- اقتراحات فقط، يحتاج تأكيد يدوي
        end if;
      end if;
    end if;

    insert into sales_order_items(order_id, line_no, barcode, model, description,
                                  qty, unit_price, note, item_id, match_status, match_score, match_candidates)
    values (v_order_id, v_ln, v_barcode, v_model, v_item->>'description',
            coalesce(nullif(v_item->>'qty',''),'0')::numeric,
            nullif(v_item->>'unit_price','')::numeric, v_item->>'note',
            v_item_id, v_status, v_score, v_cands);
  end loop;

  return jsonb_build_object('ok', true, 'order_id', v_order_id, 'lines', v_ln);
end;
$$;

grant execute on function public.ingest_sales_order(jsonb) to anon, authenticated;

-- ============================================================
--  تم. الخطوات:
--   1) غيّر CHANGE_ME_SECRET_123 لسر قوي وأعد التشغيل.
--   2) (اختياري للـ AI) انشر Edge Function: ai-match-item (شوف ملف النشر).
-- ============================================================