CREATE OR REPLACE FUNCTION public.me_has_permission(perm_code text)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1
    FROM public.user_roles ur
    JOIN public.role_permissions rp ON ur.role_id = rp.role_id
    JOIN public.permissions p ON rp.permission_id = p.id
    WHERE ur.user_id = auth.uid()
      AND p.code = perm_code
      AND p.is_active = true
  );
END;
$$;

CREATE OR REPLACE FUNCTION public.set_role_permissions(
  p_role_id int,
  p_permission_ids uuid[]
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
BEGIN
  -- Delete existing permissions
  DELETE FROM public.role_permissions WHERE role_id = p_role_id;
  
  -- Insert new permissions
  INSERT INTO public.role_permissions (role_id, permission_id)
  SELECT p_role_id, unnest(p_permission_ids);
END;
$$;

CREATE OR REPLACE FUNCTION public.sync_permissions_from_registry(
  p_permissions jsonb
)
RETURNS integer
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_count integer := 0;
BEGIN
  -- Upsert permissions from registry
  INSERT INTO public.permissions (code, resource, action, label, description, is_active)
  SELECT
    p->>'code',
    p->>'resource',
    (p->>'action')::permission_action,
    p->>'label',
    p->>'description',
    true
  FROM jsonb_array_elements(p_permissions) p
  ON CONFLICT (code) DO UPDATE SET
    resource = EXCLUDED.resource,
    action = EXCLUDED.action,
    label = EXCLUDED.label,
    description = EXCLUDED.description,
    is_active = EXCLUDED.is_active;
    
  GET DIAGNOSTICS v_count = ROW_COUNT;
  RETURN v_count;
END;
$$;

CREATE OR REPLACE FUNCTION public.update_user_password(
  p_user_id uuid,
  p_new_password text
)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, auth, pg_temp
AS $$
BEGIN
  -- Update auth.users password
  UPDATE auth.users
  SET encrypted_password = crypt(p_new_password, gen_salt('bf'))
  WHERE id = p_user_id;
  
  -- Record password reset in public.users
  UPDATE public.users
  SET password_reset_at = now(),
      password_reset_by = auth.uid()
  WHERE id = p_user_id;
END;
$$;

CREATE OR REPLACE FUNCTION public.handle_ticket_updated()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER ticket_updated_trigger
  BEFORE UPDATE ON public.tickets
  FOR EACH ROW
  EXECUTE FUNCTION public.handle_ticket_updated();

CREATE OR REPLACE FUNCTION public.create_notification_event(
  p_category text,
  p_actor_user_id uuid,
  p_title text,
  p_body text,
  p_data jsonb DEFAULT '{}'::jsonb,
  p_recipient_user_ids uuid[] DEFAULT NULL,
  p_channel_mask integer DEFAULT 3
)
RETURNS uuid
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
  v_event_id uuid;
BEGIN
  -- Insert notification event
  INSERT INTO public.notification_events (
    category, actor_user_id, title, body, data, channel_mask
  ) VALUES (
    p_category, p_actor_user_id, p_title, p_body, p_data, p_channel_mask
  ) RETURNING id INTO v_event_id;
  
  -- Create deliveries for each recipient
  INSERT INTO public.notification_deliveries (
    event_id, recipient_user_id, status
  )
  SELECT v_event_id, unnest(p_recipient_user_ids), 'delivered';
  
  RETURN v_event_id;
END;
$$;

CREATE OR REPLACE VIEW public.v_tickets_compat AS
SELECT
  t.*,
  l.name AS location_name,
  a.name || ' ' || a.last_name AS assignee_name
FROM public.tickets t
LEFT JOIN public.locations l ON t.location_id = l.id
LEFT JOIN public.assignees a ON t.assignee = a.name;