-- ============================================================
-- Magic Tech — Phase 4 SQL Migrations
-- Run in Supabase SQL Editor (one block at a time, or all together)
-- ============================================================

-- ============================================
-- 1) SOFT DELETE + TRASH
-- Adds a deleted_at column to items, and a view "items_active"
-- that hides soft-deleted rows. Existing queries against `items`
-- can be migrated to use this view to auto-filter.
-- ============================================
ALTER TABLE public.items
  ADD COLUMN IF NOT EXISTS deleted_at timestamptz,
  ADD COLUMN IF NOT EXISTS deleted_by uuid REFERENCES auth.users(id);

CREATE INDEX IF NOT EXISTS items_deleted_at_idx ON public.items (deleted_at);

-- A view that hides soft-deleted rows (use this in app code where you want only active)
CREATE OR REPLACE VIEW public.items_active AS
  SELECT * FROM public.items WHERE deleted_at IS NULL;

-- Soft delete RPC
CREATE OR REPLACE FUNCTION public.item_soft_delete(p_item_id text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  UPDATE public.items
     SET deleted_at = now(), deleted_by = auth.uid()
   WHERE item_id = p_item_id;
END;
$$;

-- Restore RPC
CREATE OR REPLACE FUNCTION public.item_restore(p_item_id text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  UPDATE public.items
     SET deleted_at = NULL, deleted_by = NULL
   WHERE item_id = p_item_id;
END;
$$;

-- Permanently purge (admin only, irreversible)
CREATE OR REPLACE FUNCTION public.item_purge(p_item_id text)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  is_admin boolean;
BEGIN
  SELECT (role = 'admin') INTO is_admin FROM public.profiles WHERE id = auth.uid();
  IF NOT is_admin THEN
    RAISE EXCEPTION 'admin only';
  END IF;
  DELETE FROM public.items WHERE item_id = p_item_id;
END;
$$;


-- ============================================
-- 2) APPROVAL WORKFLOW
-- For high-quantity transfers/outs that need manager sign-off.
-- ============================================
CREATE TABLE IF NOT EXISTS public.approvals (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at      timestamptz NOT NULL DEFAULT now(),
  requested_by    uuid NOT NULL REFERENCES auth.users(id),
  approved_by     uuid REFERENCES auth.users(id),
  approved_at     timestamptz,
  rejected_at     timestamptz,
  reject_reason   text,
  status          text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','approved','rejected','cancelled')),

  -- The action to perform once approved
  action_type     text NOT NULL CHECK (action_type IN ('out','transfer','purge')),
  item_id         text REFERENCES public.items(item_id) ON DELETE CASCADE,
  qty             numeric NOT NULL DEFAULT 0,
  from_warehouse  text,
  to_warehouse    text,
  warehouse       text,
  note            text,
  payload         jsonb  -- arbitrary extra
);

CREATE INDEX IF NOT EXISTS approvals_status_idx ON public.approvals (status, created_at DESC);
CREATE INDEX IF NOT EXISTS approvals_item_idx   ON public.approvals (item_id);

-- Threshold: any out/transfer with qty > this needs approval (tune as needed)
-- Read from app config table or hardcode in client.
CREATE TABLE IF NOT EXISTS public.app_settings (
  key   text PRIMARY KEY,
  value jsonb
);
INSERT INTO public.app_settings (key, value)
  VALUES ('approval_threshold_qty', '100'::jsonb)
  ON CONFLICT (key) DO NOTHING;

-- RPC: approve & execute
CREATE OR REPLACE FUNCTION public.approval_approve(p_id uuid)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  is_admin boolean;
  a record;
BEGIN
  SELECT (role = 'admin') INTO is_admin FROM public.profiles WHERE id = auth.uid();
  IF NOT is_admin THEN
    RAISE EXCEPTION 'admin only';
  END IF;
  SELECT * INTO a FROM public.approvals WHERE id = p_id AND status = 'pending';
  IF a IS NULL THEN
    RAISE EXCEPTION 'no pending approval';
  END IF;
  -- mark approved
  UPDATE public.approvals
     SET status = 'approved', approved_by = auth.uid(), approved_at = now()
   WHERE id = p_id;
  -- Note: actual execution should be done by app logic after approve.
  -- A trigger-based approach is possible but adds complexity.
END;
$$;

CREATE OR REPLACE FUNCTION public.approval_reject(p_id uuid, p_reason text DEFAULT NULL)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE is_admin boolean;
BEGIN
  SELECT (role = 'admin') INTO is_admin FROM public.profiles WHERE id = auth.uid();
  IF NOT is_admin THEN
    RAISE EXCEPTION 'admin only';
  END IF;
  UPDATE public.approvals
     SET status = 'rejected', rejected_at = now(), reject_reason = p_reason
   WHERE id = p_id AND status = 'pending';
END;
$$;


-- ============================================
-- 3) ALERTS PREFERENCES (for email / push delivery)
-- One row per user, opt-in per channel.
-- ============================================
CREATE TABLE IF NOT EXISTS public.alert_preferences (
  user_id            uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email_enabled      boolean NOT NULL DEFAULT false,
  telegram_chat_id   text,
  telegram_enabled   boolean NOT NULL DEFAULT false,
  push_subscription  jsonb,    -- web-push subscription object
  push_enabled       boolean NOT NULL DEFAULT false,
  -- which events to deliver
  on_low_stock       boolean NOT NULL DEFAULT true,
  on_new_move        boolean NOT NULL DEFAULT false,
  on_approval        boolean NOT NULL DEFAULT true,
  updated_at         timestamptz NOT NULL DEFAULT now()
);


-- ============================================
-- 4) RLS POLICIES (verify these match your security model)
-- ============================================
-- approvals: users see their own, admins see all
ALTER TABLE public.approvals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "approvals_select_self_or_admin" ON public.approvals;
CREATE POLICY "approvals_select_self_or_admin" ON public.approvals
  FOR SELECT TO authenticated
  USING (
    requested_by = auth.uid()
    OR EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin')
  );

DROP POLICY IF EXISTS "approvals_insert_self" ON public.approvals;
CREATE POLICY "approvals_insert_self" ON public.approvals
  FOR INSERT TO authenticated
  WITH CHECK (requested_by = auth.uid());

DROP POLICY IF EXISTS "approvals_update_admin" ON public.approvals;
CREATE POLICY "approvals_update_admin" ON public.approvals
  FOR UPDATE TO authenticated
  USING (EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin'))
  WITH CHECK (EXISTS (SELECT 1 FROM public.profiles WHERE id = auth.uid() AND role = 'admin'));

-- alert_preferences: each user manages own
ALTER TABLE public.alert_preferences ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "alert_prefs_self" ON public.alert_preferences;
CREATE POLICY "alert_prefs_self" ON public.alert_preferences
  FOR ALL TO authenticated
  USING (user_id = auth.uid())
  WITH CHECK (user_id = auth.uid());


-- ============================================
-- DONE
-- ============================================
-- Next steps in your Supabase project:
--   1) Run this SQL block.
--   2) Create an Edge Function `notify-alerts` that polls low_stock_total
--      or listens to triggers, then sends Email/Telegram/Push to users
--      where alert_preferences.* = true.
--   3) Frontend: build a Preferences page that lets users set their
--      email_enabled, telegram_chat_id, push_subscription.