-- =====================================================================
-- SQL_FULL_SETUP_lite.sql  ·  Magic Tech LITE — مشروع Supabase جديد
-- نسخة كاملة لكل الجداول والـ Views والـ Triggers والـ RPCs و Storage
-- اللازمة لكل صفحات النسخة الخفيفة:
--   dashboard, in, out, transfer, stock, count, import, barcodes,
--   alerts, users, audit, admin, login
-- =====================================================================
-- شغّل هذا الملف كاملاً مرة واحدة في Supabase SQL Editor
-- =====================================================================


-- ============================================================
-- 0) EXTENSIONS
-- ============================================================
CREATE EXTENSION IF NOT EXISTS "pgcrypto";


-- ============================================================
-- 1) PROFILES — مرتبط بـ auth.users
-- ============================================================
CREATE TABLE IF NOT EXISTS profiles (
  id              UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  full_name       TEXT,
  email           TEXT,
  role            TEXT DEFAULT 'user',          -- 'admin' | 'user' | 'viewer'
  permissions     TEXT[] DEFAULT ARRAY[]::TEXT[],
  -- مفاتيح الصلاحيات:
  -- in, out, transfer, stock, count, import, users, alerts, audit, admin
  -- wh_r1, wh_m1, wh_m2, wh_m3, wh_m4, wh_g1, wh_g2, wh_g3, wh_g4, wh_s1, wh_s2, wh_office
  last_seen       TIMESTAMPTZ,
  current_page    TEXT,
  created_at      TIMESTAMPTZ DEFAULT now(),
  updated_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_profiles_email ON profiles(email);
CREATE INDEX IF NOT EXISTS idx_profiles_role  ON profiles(role);

-- ✅ Auto-create profile لمّا ينعمل auth.users جديد
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO profiles (id, full_name, email, role, permissions)
  VALUES (
    NEW.id,
    COALESCE(NEW.raw_user_meta_data->>'full_name', split_part(NEW.email,'@',1)),
    NEW.email,
    'user',
    ARRAY[]::TEXT[]
  )
  ON CONFLICT (id) DO NOTHING;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();


-- ============================================================
-- 2) ITEMS — جدول الأصناف الرئيسي
-- ============================================================
CREATE TABLE IF NOT EXISTS items (
  item_id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  brand                TEXT,
  model                TEXT,
  description          TEXT,
  meters               NUMERIC,
  -- 12 مستودع
  r1                   NUMERIC DEFAULT 0,
  m1                   NUMERIC DEFAULT 0,
  m2                   NUMERIC DEFAULT 0,
  m3                   NUMERIC DEFAULT 0,
  m4                   NUMERIC DEFAULT 0,
  g1                   NUMERIC DEFAULT 0,
  g2                   NUMERIC DEFAULT 0,
  g3                   NUMERIC DEFAULT 0,
  g4                   NUMERIC DEFAULT 0,
  s1                   NUMERIC DEFAULT 0,
  s2                   NUMERIC DEFAULT 0,
  office               NUMERIC DEFAULT 0,
  -- باركود + كميات
  image_url            TEXT,
  barcode              TEXT UNIQUE,
  barcode_default_qty  INT,
  -- إعدادات
  min_stock            INT DEFAULT 0,            -- لتنبيه الإجمالي
  sort_order           INT,                       -- ترتيب يدوي
  -- اختياري (مش مستخدم في النسخة Lite بس مفيد لو رجعت تشغّل المبيعات)
  price                NUMERIC(12,3) DEFAULT 0,
  tax_rate             NUMERIC(5,2) DEFAULT 16,
  -- timestamps
  created_at           TIMESTAMPTZ DEFAULT now(),
  updated_at           TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_items_brand ON items(brand);
CREATE INDEX IF NOT EXISTS idx_items_model ON items(model);
CREATE INDEX IF NOT EXISTS idx_items_barcode ON items(barcode) WHERE barcode IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_items_sort ON items(sort_order) WHERE sort_order IS NOT NULL;


-- ============================================================
-- 3) STOCK_MOVES — سجل الحركات (in/out/transfer)
-- ============================================================
CREATE TABLE IF NOT EXISTS stock_moves (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  move_type       TEXT NOT NULL,        -- 'in' | 'out' | 'transfer'
  item_id         UUID REFERENCES items(item_id) ON DELETE SET NULL,
  warehouse       TEXT,                  -- للـ in/out (in: المستودع المضاف إليه، out: المخصوم منه)
  from_warehouse  TEXT,                  -- للـ transfer
  to_warehouse    TEXT,                  -- للـ transfer
  qty             NUMERIC NOT NULL,
  note            TEXT,
  tx_date         DATE DEFAULT CURRENT_DATE,
  created_by      UUID REFERENCES profiles(id) ON DELETE SET NULL,
  created_at      TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_moves_date ON stock_moves(tx_date DESC);
CREATE INDEX IF NOT EXISTS idx_moves_item ON stock_moves(item_id);
CREATE INDEX IF NOT EXISTS idx_moves_type ON stock_moves(move_type);
CREATE INDEX IF NOT EXISTS idx_moves_user ON stock_moves(created_by);
CREATE INDEX IF NOT EXISTS idx_moves_created_at ON stock_moves(created_at DESC);


-- ============================================================
-- 4) ALERT_SETTINGS — إعدادات تنبيه لكل صنف
--    scope = "total"  → عتبة على الإجمالي
--    scope = "wh"     → عتبة لكل مستودع (عبر item_alert_rules)
-- ============================================================
CREATE TABLE IF NOT EXISTS alert_settings (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id     UUID NOT NULL REFERENCES items(item_id) ON DELETE CASCADE,
  scope       TEXT NOT NULL CHECK (scope IN ('total','wh')),
  alert_qty   NUMERIC NOT NULL DEFAULT 0,
  enabled     BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now(),
  UNIQUE (item_id, scope)
);

CREATE INDEX IF NOT EXISTS idx_alert_settings_item ON alert_settings(item_id);


-- ============================================================
-- 5) ITEM_ALERT_RULES — قواعد تنبيه لكل مستودع لكل صنف
-- ============================================================
CREATE TABLE IF NOT EXISTS item_alert_rules (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id     UUID NOT NULL REFERENCES items(item_id) ON DELETE CASCADE,
  scope       TEXT NOT NULL CHECK (scope IN ('total','wh')),
  wh          TEXT,                              -- NULL لو scope=total
  threshold   NUMERIC NOT NULL DEFAULT 0,
  is_enabled  BOOLEAN DEFAULT true,
  created_at  TIMESTAMPTZ DEFAULT now(),
  updated_at  TIMESTAMPTZ DEFAULT now()
);

-- UNIQUE معقّد بسبب NULL handling — نستخدم index بدل constraint
CREATE UNIQUE INDEX IF NOT EXISTS uq_alert_rules_item_scope_wh
  ON item_alert_rules(item_id, scope, COALESCE(wh, ''));

CREATE INDEX IF NOT EXISTS idx_alert_rules_item ON item_alert_rules(item_id);


-- ============================================================
-- 6) SHORTAGES — قائمة النواقص (لما يصير إخراج وما في كمية كافية)
-- ============================================================
CREATE TABLE IF NOT EXISTS shortages (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  item_id     UUID REFERENCES items(item_id) ON DELETE CASCADE,
  warehouse   TEXT,
  qty         NUMERIC NOT NULL,
  note        TEXT,
  created_by  UUID REFERENCES profiles(id) ON DELETE SET NULL,
  resolved    BOOLEAN DEFAULT false,
  created_at  TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_shortages_item ON shortages(item_id);
CREATE INDEX IF NOT EXISTS idx_shortages_unres ON shortages(resolved) WHERE resolved = false;


-- ============================================================
-- 7) VIEWS: low_stock_total + low_stock_wh
--    تعطي الأصناف اللي رصيدها أقل من العتبة
-- ============================================================

-- 7.a) view: الأصناف اللي إجمالي مخزونها أقل من حد التنبيه
CREATE OR REPLACE VIEW low_stock_total AS
SELECT
  i.item_id,
  i.brand,
  i.model,
  i.description,
  i.meters,
  (COALESCE(i.r1,0)+COALESCE(i.m1,0)+COALESCE(i.m2,0)+COALESCE(i.m3,0)+COALESCE(i.m4,0)
   +COALESCE(i.g1,0)+COALESCE(i.g2,0)+COALESCE(i.g3,0)+COALESCE(i.g4,0)
   +COALESCE(i.s1,0)+COALESCE(i.s2,0)+COALESCE(i.office,0)) AS total_qty,
  COALESCE(ast.alert_qty, NULLIF(i.min_stock,0)) AS threshold
FROM items i
LEFT JOIN alert_settings ast ON ast.item_id = i.item_id AND ast.scope = 'total' AND ast.enabled = true
WHERE
  (
    (ast.enabled = true AND ast.alert_qty > 0)
    OR (i.min_stock > 0)
  )
  AND (COALESCE(i.r1,0)+COALESCE(i.m1,0)+COALESCE(i.m2,0)+COALESCE(i.m3,0)+COALESCE(i.m4,0)
       +COALESCE(i.g1,0)+COALESCE(i.g2,0)+COALESCE(i.g3,0)+COALESCE(i.g4,0)
       +COALESCE(i.s1,0)+COALESCE(i.s2,0)+COALESCE(i.office,0))
      < COALESCE(ast.alert_qty, i.min_stock, 0);

-- 7.b) view: الأصناف اللي مخزون مستودع معيّن أقل من الحد
--      نولّد صف لكل (item,wh) من قواعد item_alert_rules بـ scope='wh'
CREATE OR REPLACE VIEW low_stock_wh AS
SELECT
  i.item_id,
  i.brand,
  i.model,
  i.description,
  r.wh AS warehouse,
  CASE r.wh
    WHEN 'r1' THEN COALESCE(i.r1,0)
    WHEN 'm1' THEN COALESCE(i.m1,0)
    WHEN 'm2' THEN COALESCE(i.m2,0)
    WHEN 'm3' THEN COALESCE(i.m3,0)
    WHEN 'm4' THEN COALESCE(i.m4,0)
    WHEN 'g1' THEN COALESCE(i.g1,0)
    WHEN 'g2' THEN COALESCE(i.g2,0)
    WHEN 'g3' THEN COALESCE(i.g3,0)
    WHEN 'g4' THEN COALESCE(i.g4,0)
    WHEN 's1' THEN COALESCE(i.s1,0)
    WHEN 's2' THEN COALESCE(i.s2,0)
    WHEN 'office' THEN COALESCE(i.office,0)
    ELSE 0
  END AS wh_qty,
  r.threshold
FROM items i
JOIN item_alert_rules r ON r.item_id = i.item_id AND r.scope = 'wh' AND r.is_enabled = true AND r.wh IS NOT NULL
WHERE
  CASE r.wh
    WHEN 'r1' THEN COALESCE(i.r1,0)
    WHEN 'm1' THEN COALESCE(i.m1,0)
    WHEN 'm2' THEN COALESCE(i.m2,0)
    WHEN 'm3' THEN COALESCE(i.m3,0)
    WHEN 'm4' THEN COALESCE(i.m4,0)
    WHEN 'g1' THEN COALESCE(i.g1,0)
    WHEN 'g2' THEN COALESCE(i.g2,0)
    WHEN 'g3' THEN COALESCE(i.g3,0)
    WHEN 'g4' THEN COALESCE(i.g4,0)
    WHEN 's1' THEN COALESCE(i.s1,0)
    WHEN 's2' THEN COALESCE(i.s2,0)
    WHEN 'office' THEN COALESCE(i.office,0)
    ELSE 0
  END < r.threshold;


-- ============================================================
-- 8) RPC: reset_items — يمسح كل الأصناف (يستخدمها import.html — وضع "استبدال كامل")
-- ============================================================
CREATE OR REPLACE FUNCTION reset_items()
RETURNS VOID AS $$
BEGIN
  -- مسح آمن: نمسح stock_moves الأول (cascade ما بتشتغل لو item_id ON DELETE SET NULL)
  -- ثم alert_settings و item_alert_rules و shortages — هاي بتنمسح بالـ CASCADE من items
  -- خلّينا نمسح بترتيب صريح لتفادي مشاكل القيود:
  TRUNCATE TABLE shortages CASCADE;
  TRUNCATE TABLE item_alert_rules CASCADE;
  TRUNCATE TABLE alert_settings CASCADE;
  -- stock_moves: ما نمسحها — هي سجل تاريخي ضروري
  -- إذا فعلاً بدك تمسحها برضو، فك التعليق:
  -- TRUNCATE TABLE stock_moves CASCADE;
  DELETE FROM items;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;


-- ============================================================
-- 9) RPC: item_total_stock — يرجّع إجمالي مخزون صنف
-- ============================================================
CREATE OR REPLACE FUNCTION item_total_stock(p_item_id UUID)
RETURNS NUMERIC AS $$
DECLARE v_total NUMERIC;
BEGIN
  SELECT COALESCE(r1,0)+COALESCE(m1,0)+COALESCE(m2,0)+COALESCE(m3,0)+COALESCE(m4,0)
       + COALESCE(g1,0)+COALESCE(g2,0)+COALESCE(g3,0)+COALESCE(g4,0)
       + COALESCE(s1,0)+COALESCE(s2,0)+COALESCE(office,0)
    INTO v_total
  FROM items WHERE item_id = p_item_id;
  RETURN COALESCE(v_total, 0);
END;
$$ LANGUAGE plpgsql;


-- ============================================================
-- 10) STORAGE BUCKETS
-- ============================================================
INSERT INTO storage.buckets (id, name, public)
VALUES ('item-images', 'item-images', true)
ON CONFLICT (id) DO UPDATE SET public = true;

DROP POLICY IF EXISTS "item-images public read"  ON storage.objects;
DROP POLICY IF EXISTS "item-images auth insert"  ON storage.objects;
DROP POLICY IF EXISTS "item-images auth update"  ON storage.objects;
DROP POLICY IF EXISTS "item-images auth delete"  ON storage.objects;

CREATE POLICY "item-images public read"
ON storage.objects FOR SELECT
USING (bucket_id = 'item-images');

CREATE POLICY "item-images auth insert"
ON storage.objects FOR INSERT
TO authenticated
WITH CHECK (bucket_id = 'item-images');

CREATE POLICY "item-images auth update"
ON storage.objects FOR UPDATE
TO authenticated
USING (bucket_id = 'item-images')
WITH CHECK (bucket_id = 'item-images');

CREATE POLICY "item-images auth delete"
ON storage.objects FOR DELETE
TO authenticated
USING (bucket_id = 'item-images');


-- ============================================================
-- 11) ROW LEVEL SECURITY (RLS)
--     مفعّل + سماحية للمسجّلين (التحقق من الصلاحيات بمستوى التطبيق)
-- ============================================================
ALTER TABLE profiles          ENABLE ROW LEVEL SECURITY;
ALTER TABLE items             ENABLE ROW LEVEL SECURITY;
ALTER TABLE stock_moves       ENABLE ROW LEVEL SECURITY;
ALTER TABLE alert_settings    ENABLE ROW LEVEL SECURITY;
ALTER TABLE item_alert_rules  ENABLE ROW LEVEL SECURITY;
ALTER TABLE shortages         ENABLE ROW LEVEL SECURITY;

-- PROFILES
DROP POLICY IF EXISTS "profiles read all auth"   ON profiles;
DROP POLICY IF EXISTS "profiles update own"      ON profiles;
DROP POLICY IF EXISTS "profiles admin all"       ON profiles;
DROP POLICY IF EXISTS "profiles auth insert"     ON profiles;

CREATE POLICY "profiles read all auth"
  ON profiles FOR SELECT TO authenticated USING (true);

CREATE POLICY "profiles update own"
  ON profiles FOR UPDATE TO authenticated
  USING (auth.uid() = id) WITH CHECK (auth.uid() = id);

CREATE POLICY "profiles admin all"
  ON profiles FOR ALL TO authenticated
  USING ((SELECT role FROM profiles WHERE id = auth.uid()) = 'admin')
  WITH CHECK ((SELECT role FROM profiles WHERE id = auth.uid()) = 'admin');

-- باقي الجداول: سماح كامل للمسجّلين
DROP POLICY IF EXISTS "items auth all"            ON items;
DROP POLICY IF EXISTS "stock_moves auth all"      ON stock_moves;
DROP POLICY IF EXISTS "alert_settings auth all"   ON alert_settings;
DROP POLICY IF EXISTS "item_alert_rules auth all" ON item_alert_rules;
DROP POLICY IF EXISTS "shortages auth all"        ON shortages;

CREATE POLICY "items auth all"
  ON items FOR ALL TO authenticated USING (true) WITH CHECK (true);

CREATE POLICY "stock_moves auth all"
  ON stock_moves FOR ALL TO authenticated USING (true) WITH CHECK (true);

CREATE POLICY "alert_settings auth all"
  ON alert_settings FOR ALL TO authenticated USING (true) WITH CHECK (true);

CREATE POLICY "item_alert_rules auth all"
  ON item_alert_rules FOR ALL TO authenticated USING (true) WITH CHECK (true);

CREATE POLICY "shortages auth all"
  ON shortages FOR ALL TO authenticated USING (true) WITH CHECK (true);


-- ============================================================
-- 12) REALTIME — تفعيل البث على الجداول اللي تستخدم realtime في الموقع
-- ============================================================
-- يمكّن صفحات stock/alerts/etc من استقبال التحديثات لايف
BEGIN;
  -- يضيف الجداول لـ publication الافتراضية supabase_realtime
  ALTER PUBLICATION supabase_realtime ADD TABLE items;
  ALTER PUBLICATION supabase_realtime ADD TABLE stock_moves;
  ALTER PUBLICATION supabase_realtime ADD TABLE alert_settings;
  ALTER PUBLICATION supabase_realtime ADD TABLE shortages;
COMMIT;
-- ⚠️ لو هاي الـ ALTER فشلت بـ "relation is already member" فهي مفعّلة مسبقاً — تجاهل الخطأ


-- ============================================================
-- ✅ ENJOY!
--
-- خطوات ما بعد التشغيل:
-- ----------------------
-- 1) سجّل أول مستخدم من login.html (Sign Up)
-- 2) في SQL Editor شغّل:
--      UPDATE profiles SET role='admin' WHERE email='YOUR@EMAIL.COM';
-- 3) ارجع للموقع → reload → بتشوف كل الصفحات (admin)
-- 4) ابدأ بإضافة أصناف من stock أو count أو import
-- =====================================================================