Merge pull request 'feat: add SQL helper functions for inventory management (#15)' (#44) from feature/issue-15-sql-functions into develop

Created 8 helper functions for common inventory operations:

1. get_inventory_details() - Full inventory list with denormalized data
2. get_expiring_items(days) - Items expiring within N days
3. get_items_by_tag(tag_name) - Filter by tag
4. get_low_stock_items(threshold) - Items below quantity threshold
5. update_item_quantity() - Consume/restock with optional auto-delete
6. get_inventory_stats() - Dashboard statistics
7. search_inventory() - Full-text search across items and products

All functions include comments and are optimized for frontend queries.

Closes #15

supabase/migrations/001_initial_schema.sql

@@ -0,0 +1,282 @@
+-- Migration: Initial database schema
+-- Created: 2026-02-09
+-- Issue: #13
+-- Description: Creates core tables for Pantry app (inventory, products, tags, units)
+
+-- ======================
+-- ENUM TYPES
+-- ======================
+
+CREATE TYPE tag_category AS ENUM (
+  'position',    -- Location: fridge, freezer, pantry
+  'type',        -- Food type: dairy, meat, vegan
+  'dietary',     -- Diet: gluten-free, vegan, organic
+  'custom'       -- User-defined
+);
+
+CREATE TYPE unit_type AS ENUM (
+  'weight',   -- kg, g, lb, oz
+  'volume',   -- L, mL, cup, tbsp
+  'count',    -- pcs, items (no conversion)
+  'custom'    -- can, jar, bottle (user-defined)
+);
+
+-- ======================
+-- BASE TABLES (no foreign keys)
+-- ======================
+
+-- Units: measurement units with conversion support
+CREATE TABLE units (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  
+  -- Core data
+  name TEXT NOT NULL,           -- "kilogram", "liter", "piece"
+  abbreviation TEXT NOT NULL,   -- "kg", "L", "pcs"
+  unit_type unit_type NOT NULL,
+  
+  -- Conversion system
+  base_unit_id UUID REFERENCES units(id),  -- NULL = this is a base unit
+  conversion_factor DECIMAL(20,10),        -- Factor to convert to base unit
+  
+  -- Metadata
+  is_default BOOLEAN DEFAULT false,  -- Shipped with app
+  created_by UUID REFERENCES auth.users(id),  -- NULL = system unit
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  
+  CONSTRAINT unique_unit_abbr UNIQUE (abbreviation, created_by),
+  CONSTRAINT check_conversion_logic CHECK (
+    (base_unit_id IS NULL AND conversion_factor IS NULL) OR 
+    (base_unit_id IS NOT NULL AND conversion_factor IS NOT NULL)
+  )
+);
+
+-- Tags: flexible labeling system
+CREATE TABLE tags (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  
+  -- Core data
+  name TEXT NOT NULL,
+  category tag_category NOT NULL DEFAULT 'custom',
+  
+  -- Visual
+  icon TEXT,    -- Emoji or icon name: "🧊", "cheese"
+  color TEXT,   -- Hex color: "#3b82f6"
+  
+  -- Ownership
+  created_by UUID REFERENCES auth.users(id),  -- NULL = system tag
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  
+  CONSTRAINT unique_tag_name UNIQUE (name, created_by)
+);
+
+-- ======================
+-- MAIN TABLES
+-- ======================
+
+-- Products: cached product data from Open Food Facts
+CREATE TABLE products (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  
+  -- Open Food Facts data
+  barcode TEXT UNIQUE NOT NULL,
+  name TEXT NOT NULL,
+  brand TEXT,
+  image_url TEXT,
+  image_small_url TEXT,  -- Thumbnail
+  
+  -- Categories from Open Food Facts
+  categories TEXT[],  -- Array: ['dairy', 'milk']
+  
+  -- Nutrition (optional, for future features)
+  nutrition JSONB,  -- Full nutrition data
+  
+  -- Defaults
+  default_unit_id UUID REFERENCES units(id),
+  default_quantity DECIMAL(10,2),  -- E.g., 1L bottle
+  
+  -- Metadata
+  source TEXT DEFAULT 'openfoodfacts',
+  cached_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  last_fetched TIMESTAMPTZ,
+  
+  -- Quality score (from Open Food Facts)
+  completeness_score INTEGER CHECK (completeness_score BETWEEN 0 AND 100)
+);
+
+-- Inventory Items: actual items in your kitchen right now
+CREATE TABLE inventory_items (
+  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
+  
+  -- Product reference (nullable for custom items)
+  product_id UUID REFERENCES products(id) ON DELETE SET NULL,
+  
+  -- Core data
+  name TEXT NOT NULL,  -- Product name or custom name
+  quantity DECIMAL(10,2) NOT NULL CHECK (quantity >= 0),
+  unit_id UUID NOT NULL REFERENCES units(id),
+  
+  -- Optional metadata
+  expiry_date DATE,
+  location TEXT,  -- Free text: "top shelf", "door", etc.
+  notes TEXT,
+  
+  -- Audit trail
+  added_by UUID NOT NULL REFERENCES auth.users(id),
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- Item Tags: many-to-many relationship between items and tags
+CREATE TABLE item_tags (
+  item_id UUID NOT NULL REFERENCES inventory_items(id) ON DELETE CASCADE,
+  tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
+  
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  
+  PRIMARY KEY (item_id, tag_id)
+);
+
+-- User Profiles: additional user metadata
+CREATE TABLE user_profiles (
+  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
+  
+  display_name TEXT,
+  avatar_url TEXT,
+  
+  -- Preferences
+  default_unit_system TEXT DEFAULT 'metric',  -- 'metric' or 'imperial'
+  theme TEXT DEFAULT 'auto',                  -- 'light', 'dark', 'auto'
+  
+  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+-- ======================
+-- INDEXES
+-- ======================
+
+-- Units
+CREATE INDEX idx_units_type ON units(unit_type);
+CREATE INDEX idx_units_base ON units(base_unit_id);
+
+-- Tags
+CREATE INDEX idx_tags_category ON tags(category);
+CREATE INDEX idx_tags_created_by ON tags(created_by);
+
+-- Products
+CREATE UNIQUE INDEX idx_products_barcode ON products(barcode);
+CREATE INDEX idx_products_name ON products USING GIN (to_tsvector('english', name));
+CREATE INDEX idx_products_search ON products 
+  USING GIN (to_tsvector('english', name || ' ' || COALESCE(brand, '')));
+
+-- Inventory Items
+CREATE INDEX idx_items_product ON inventory_items(product_id);
+CREATE INDEX idx_items_added_by ON inventory_items(added_by);
+CREATE INDEX idx_items_expiry ON inventory_items(expiry_date) WHERE expiry_date IS NOT NULL;
+
+-- Item Tags
+CREATE INDEX idx_item_tags_tag ON item_tags(tag_id);
+CREATE INDEX idx_item_tags_item ON item_tags(item_id);
+
+-- ======================
+-- FUNCTIONS
+-- ======================
+
+-- Auto-update timestamp trigger function
+CREATE OR REPLACE FUNCTION update_updated_at()
+RETURNS TRIGGER AS $$
+BEGIN
+  NEW.updated_at = NOW();
+  RETURN NEW;
+END;
+$$ LANGUAGE plpgsql;
+
+-- Unit conversion function
+CREATE OR REPLACE FUNCTION convert_unit(
+  quantity DECIMAL,
+  from_unit_id UUID,
+  to_unit_id UUID
+)
+RETURNS DECIMAL AS $$
+DECLARE
+  from_factor DECIMAL;
+  to_factor DECIMAL;
+  from_type unit_type;
+  to_type unit_type;
+  base_quantity DECIMAL;
+BEGIN
+  -- Get unit types and conversion factors
+  SELECT unit_type, 
+         COALESCE(conversion_factor, 1.0) INTO from_type, from_factor
+  FROM units WHERE id = from_unit_id;
+  
+  SELECT unit_type,
+         COALESCE(conversion_factor, 1.0) INTO to_type, to_factor
+  FROM units WHERE id = to_unit_id;
+  
+  -- Check if units are compatible
+  IF from_type != to_type THEN
+    RAISE EXCEPTION 'Cannot convert between % and %', from_type, to_type;
+  END IF;
+  
+  -- Convert to base unit, then to target unit
+  base_quantity := quantity * from_factor;
+  RETURN base_quantity / to_factor;
+END;
+$$ LANGUAGE plpgsql IMMUTABLE;
+
+-- Full-text search function for products
+CREATE OR REPLACE FUNCTION search_products(search_query TEXT)
+RETURNS TABLE (
+  id UUID,
+  barcode TEXT,
+  name TEXT,
+  brand TEXT,
+  rank REAL
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    p.id,
+    p.barcode,
+    p.name,
+    p.brand,
+    ts_rank(to_tsvector('english', p.name || ' ' || COALESCE(p.brand, '')), 
+            plainto_tsquery('english', search_query)) AS rank
+  FROM products p
+  WHERE to_tsvector('english', p.name || ' ' || COALESCE(p.brand, '')) 
+        @@ plainto_tsquery('english', search_query)
+  ORDER BY rank DESC
+  LIMIT 20;
+END;
+$$ LANGUAGE plpgsql;
+
+-- ======================
+-- TRIGGERS
+-- ======================
+
+-- Auto-update timestamp triggers
+CREATE TRIGGER update_items_updated_at
+  BEFORE UPDATE ON inventory_items
+  FOR EACH ROW
+  EXECUTE FUNCTION update_updated_at();
+
+CREATE TRIGGER update_profiles_updated_at
+  BEFORE UPDATE ON user_profiles
+  FOR EACH ROW
+  EXECUTE FUNCTION update_updated_at();
+
+-- ======================
+-- COMMENTS
+-- ======================
+
+COMMENT ON TABLE inventory_items IS 'Current inventory items in the kitchen';
+COMMENT ON TABLE products IS 'Cached product data from Open Food Facts';
+COMMENT ON TABLE tags IS 'Flexible labeling system for organization';
+COMMENT ON TABLE item_tags IS 'Many-to-many relationship between items and tags';
+COMMENT ON TABLE units IS 'Measurement units with conversion support';
+COMMENT ON TABLE user_profiles IS 'Additional user metadata and preferences';
+
+COMMENT ON FUNCTION update_updated_at() IS 'Automatically updates the updated_at timestamp';
+COMMENT ON FUNCTION convert_unit(DECIMAL, UUID, UUID) IS 'Converts quantity between compatible units';
+COMMENT ON FUNCTION search_products(TEXT) IS 'Full-text search for products by name and brand';

supabase/migrations/002_rls_policies.sql

@@ -0,0 +1,180 @@
+-- Migration: Row Level Security Policies
+-- Created: 2026-02-09
+-- Issue: #14
+-- Description: Enables RLS and creates security policies for all tables
+
+-- ======================
+-- ENABLE RLS
+-- ======================
+
+ALTER TABLE inventory_items ENABLE ROW LEVEL SECURITY;
+ALTER TABLE products ENABLE ROW LEVEL SECURITY;
+ALTER TABLE tags ENABLE ROW LEVEL SECURITY;
+ALTER TABLE item_tags ENABLE ROW LEVEL SECURITY;
+ALTER TABLE units ENABLE ROW LEVEL SECURITY;
+ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
+
+-- ======================
+-- INVENTORY_ITEMS POLICIES
+-- ======================
+
+-- Everyone can read (shared household inventory)
+CREATE POLICY "inventory_items_select_all" ON inventory_items
+  FOR SELECT
+  USING (true);
+
+-- Authenticated users can insert items
+CREATE POLICY "inventory_items_insert_auth" ON inventory_items
+  FOR INSERT
+  WITH CHECK (auth.uid() IS NOT NULL);
+
+-- Authenticated users can update any item
+CREATE POLICY "inventory_items_update_auth" ON inventory_items
+  FOR UPDATE
+  USING (auth.uid() IS NOT NULL);
+
+-- Authenticated users can delete any item
+CREATE POLICY "inventory_items_delete_auth" ON inventory_items
+  FOR DELETE
+  USING (auth.uid() IS NOT NULL);
+
+-- ======================
+-- PRODUCTS POLICIES
+-- ======================
+
+-- Everyone can read cached products
+CREATE POLICY "products_select_all" ON products
+  FOR SELECT
+  USING (true);
+
+-- Only service role can write (via Edge Functions)
+-- No user-level INSERT/UPDATE/DELETE policies
+
+-- ======================
+-- TAGS POLICIES
+-- ======================
+
+-- Everyone can read all tags
+CREATE POLICY "tags_select_all" ON tags
+  FOR SELECT
+  USING (true);
+
+-- Authenticated users can create tags
+CREATE POLICY "tags_insert_auth" ON tags
+  FOR INSERT
+  WITH CHECK (auth.uid() IS NOT NULL);
+
+-- Users can update their own tags OR system tags (created_by IS NULL)
+CREATE POLICY "tags_update_own" ON tags
+  FOR UPDATE
+  USING (
+    created_by = auth.uid() OR created_by IS NULL
+  );
+
+-- Users can only delete their own custom tags
+CREATE POLICY "tags_delete_own" ON tags
+  FOR DELETE
+  USING (created_by = auth.uid());
+
+-- ======================
+-- ITEM_TAGS POLICIES
+-- ======================
+
+-- Everyone can read
+CREATE POLICY "item_tags_select_all" ON item_tags
+  FOR SELECT
+  USING (true);
+
+-- Authenticated users can add tags to items
+CREATE POLICY "item_tags_insert_auth" ON item_tags
+  FOR INSERT
+  WITH CHECK (auth.uid() IS NOT NULL);
+
+-- Authenticated users can remove tags from items
+CREATE POLICY "item_tags_delete_auth" ON item_tags
+  FOR DELETE
+  USING (auth.uid() IS NOT NULL);
+
+-- ======================
+-- UNITS POLICIES
+-- ======================
+
+-- Everyone can read all units
+CREATE POLICY "units_select_all" ON units
+  FOR SELECT
+  USING (true);
+
+-- Authenticated users can create custom units (not default ones)
+CREATE POLICY "units_insert_auth" ON units
+  FOR INSERT
+  WITH CHECK (
+    auth.uid() IS NOT NULL AND is_default = false
+  );
+
+-- Users can update their own custom units only
+CREATE POLICY "units_update_own" ON units
+  FOR UPDATE
+  USING (
+    created_by = auth.uid() AND is_default = false
+  );
+
+-- Users can delete their own custom units only
+CREATE POLICY "units_delete_own" ON units
+  FOR DELETE
+  USING (
+    created_by = auth.uid() AND is_default = false
+  );
+
+-- ======================
+-- USER_PROFILES POLICIES
+-- ======================
+
+-- Users can read all profiles (for display names, avatars)
+CREATE POLICY "user_profiles_select_all" ON user_profiles
+  FOR SELECT
+  USING (true);
+
+-- Users can insert their own profile
+CREATE POLICY "user_profiles_insert_own" ON user_profiles
+  FOR INSERT
+  WITH CHECK (auth.uid() = id);
+
+-- Users can update only their own profile
+CREATE POLICY "user_profiles_update_own" ON user_profiles
+  FOR UPDATE
+  USING (auth.uid() = id);
+
+-- Users can delete only their own profile
+CREATE POLICY "user_profiles_delete_own" ON user_profiles
+  FOR DELETE
+  USING (auth.uid() = id);
+
+-- ======================
+-- COMMENTS
+-- ======================
+
+COMMENT ON POLICY "inventory_items_select_all" ON inventory_items IS 'Allow all users to view shared household inventory';
+COMMENT ON POLICY "inventory_items_insert_auth" ON inventory_items IS 'Authenticated users can add items';
+COMMENT ON POLICY "inventory_items_update_auth" ON inventory_items IS 'Authenticated users can update any item (shared inventory)';
+COMMENT ON POLICY "inventory_items_delete_auth" ON inventory_items IS 'Authenticated users can delete any item';
+
+COMMENT ON POLICY "products_select_all" ON products IS 'Allow all users to view cached product data';
+
+COMMENT ON POLICY "tags_select_all" ON tags IS 'Allow all users to view tags (system and custom)';
+COMMENT ON POLICY "tags_insert_auth" ON tags IS 'Authenticated users can create custom tags';
+COMMENT ON POLICY "tags_update_own" ON tags IS 'Users can update their own tags or system tags';
+COMMENT ON POLICY "tags_delete_own" ON tags IS 'Users can only delete their own custom tags';
+
+COMMENT ON POLICY "item_tags_select_all" ON item_tags IS 'Allow all users to view item tag associations';
+COMMENT ON POLICY "item_tags_insert_auth" ON item_tags IS 'Authenticated users can tag items';
+COMMENT ON POLICY "item_tags_delete_auth" ON item_tags IS 'Authenticated users can remove tags from items';
+
+COMMENT ON POLICY "units_select_all" ON units IS 'Allow all users to view all units';
+COMMENT ON POLICY "units_insert_auth" ON units IS 'Authenticated users can create custom units';
+COMMENT ON POLICY "units_update_own" ON units IS 'Users can only update their own custom units';
+COMMENT ON POLICY "units_delete_own" ON units IS 'Users can only delete their own custom units';
+
+COMMENT ON POLICY "user_profiles_select_all" ON user_profiles IS 'Allow users to view all profiles for display purposes';
+COMMENT ON POLICY "user_profiles_insert_own" ON user_profiles IS 'Users can create their own profile';
+COMMENT ON POLICY "user_profiles_update_own" ON user_profiles IS 'Users can only update their own profile';
+COMMENT ON POLICY "user_profiles_delete_own" ON user_profiles IS 'Users can only delete their own profile';

supabase/migrations/003_helper_functions.sql

@@ -0,0 +1,251 @@
+-- Migration: Additional SQL Functions for Inventory Management
+-- Week 2: Helper functions for common queries
+
+-- Function: Get inventory items with full details (tags, product info, unit conversion)
+CREATE OR REPLACE FUNCTION get_inventory_details()
+RETURNS TABLE (
+  item_id UUID,
+  item_name TEXT,
+  quantity DECIMAL,
+  unit_abbreviation TEXT,
+  unit_name TEXT,
+  expiry_date DATE,
+  days_until_expiry INTEGER,
+  tags TEXT[],
+  product_brand TEXT,
+  product_image_url TEXT,
+  product_barcode TEXT,
+  created_at TIMESTAMPTZ,
+  updated_at TIMESTAMPTZ
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    i.id AS item_id,
+    i.name AS item_name,
+    i.quantity,
+    u.abbreviation AS unit_abbreviation,
+    u.name AS unit_name,
+    i.expiry_date,
+    (i.expiry_date - CURRENT_DATE) AS days_until_expiry,
+    COALESCE(ARRAY_AGG(DISTINCT t.name) FILTER (WHERE t.name IS NOT NULL), '{}') AS tags,
+    p.brand AS product_brand,
+    p.image_url AS product_image_url,
+    p.barcode AS product_barcode,
+    i.created_at,
+    i.updated_at
+  FROM inventory_items i
+    JOIN units u ON i.unit_id = u.id
+    LEFT JOIN products p ON i.product_id = p.id
+    LEFT JOIN item_tags it ON i.id = it.item_id
+    LEFT JOIN tags t ON it.tag_id = t.id
+  GROUP BY 
+    i.id, i.name, i.quantity, u.abbreviation, u.name, 
+    i.expiry_date, p.brand, p.image_url, p.barcode, 
+    i.created_at, i.updated_at
+  ORDER BY i.created_at DESC;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION get_inventory_details() IS 'Returns all inventory items with denormalized data for display';
+
+-- Function: Get items expiring soon
+CREATE OR REPLACE FUNCTION get_expiring_items(days_ahead INTEGER DEFAULT 7)
+RETURNS TABLE (
+  item_id UUID,
+  item_name TEXT,
+  quantity DECIMAL,
+  unit_abbreviation TEXT,
+  expiry_date DATE,
+  days_until_expiry INTEGER,
+  tags TEXT[]
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    i.id AS item_id,
+    i.name AS item_name,
+    i.quantity,
+    u.abbreviation AS unit_abbreviation,
+    i.expiry_date,
+    (i.expiry_date - CURRENT_DATE) AS days_until_expiry,
+    COALESCE(ARRAY_AGG(DISTINCT t.name) FILTER (WHERE t.name IS NOT NULL), '{}') AS tags
+  FROM inventory_items i
+    JOIN units u ON i.unit_id = u.id
+    LEFT JOIN item_tags it ON i.id = it.item_id
+    LEFT JOIN tags t ON it.tag_id = t.id
+  WHERE 
+    i.expiry_date IS NOT NULL
+    AND i.expiry_date <= CURRENT_DATE + MAKE_INTERVAL(days => days_ahead)
+  GROUP BY i.id, i.name, i.quantity, u.abbreviation, i.expiry_date
+  ORDER BY i.expiry_date ASC;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION get_expiring_items(INTEGER) IS 'Returns items expiring within specified days (default 7)';
+
+-- Function: Get items by tag
+CREATE OR REPLACE FUNCTION get_items_by_tag(tag_name TEXT)
+RETURNS TABLE (
+  item_id UUID,
+  item_name TEXT,
+  quantity DECIMAL,
+  unit_abbreviation TEXT,
+  expiry_date DATE,
+  created_at TIMESTAMPTZ
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    i.id AS item_id,
+    i.name AS item_name,
+    i.quantity,
+    u.abbreviation AS unit_abbreviation,
+    i.expiry_date,
+    i.created_at
+  FROM inventory_items i
+    JOIN units u ON i.unit_id = u.id
+    JOIN item_tags it ON i.id = it.item_id
+    JOIN tags t ON it.tag_id = t.id
+  WHERE t.name ILIKE tag_name
+  ORDER BY i.created_at DESC;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION get_items_by_tag(TEXT) IS 'Returns all items with specified tag (case-insensitive)';
+
+-- Function: Get low stock items (quantity <= threshold)
+CREATE OR REPLACE FUNCTION get_low_stock_items(threshold DECIMAL DEFAULT 1.0)
+RETURNS TABLE (
+  item_id UUID,
+  item_name TEXT,
+  quantity DECIMAL,
+  unit_abbreviation TEXT,
+  tags TEXT[]
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    i.id AS item_id,
+    i.name AS item_name,
+    i.quantity,
+    u.abbreviation AS unit_abbreviation,
+    COALESCE(ARRAY_AGG(DISTINCT t.name) FILTER (WHERE t.name IS NOT NULL), '{}') AS tags
+  FROM inventory_items i
+    JOIN units u ON i.unit_id = u.id
+    LEFT JOIN item_tags it ON i.id = it.item_id
+    LEFT JOIN tags t ON it.tag_id = t.id
+  WHERE i.quantity <= threshold
+  GROUP BY i.id, i.name, i.quantity, u.abbreviation
+  ORDER BY i.quantity ASC, i.name ASC;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION get_low_stock_items(DECIMAL) IS 'Returns items with quantity at or below threshold';
+
+-- Function: Update item quantity (consume or restock)
+CREATE OR REPLACE FUNCTION update_item_quantity(
+  item_uuid UUID,
+  quantity_change DECIMAL,
+  delete_if_zero BOOLEAN DEFAULT TRUE
+)
+RETURNS BOOLEAN AS $$
+DECLARE
+  new_quantity DECIMAL;
+BEGIN
+  -- Calculate new quantity
+  SELECT quantity + quantity_change INTO new_quantity
+  FROM inventory_items
+  WHERE id = item_uuid;
+
+  IF new_quantity IS NULL THEN
+    RETURN FALSE;  -- Item not found
+  END IF;
+
+  -- Delete if zero and flag is set
+  IF new_quantity <= 0 AND delete_if_zero THEN
+    DELETE FROM inventory_items WHERE id = item_uuid;
+    RETURN TRUE;
+  END IF;
+
+  -- Update quantity (ensure non-negative)
+  UPDATE inventory_items
+  SET quantity = GREATEST(new_quantity, 0),
+      updated_at = NOW()
+  WHERE id = item_uuid;
+
+  RETURN TRUE;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION update_item_quantity(UUID, DECIMAL, BOOLEAN) IS 'Updates item quantity (positive for restock, negative for consume). Optionally deletes if zero.';
+
+-- Function: Get inventory statistics
+CREATE OR REPLACE FUNCTION get_inventory_stats()
+RETURNS TABLE (
+  total_items BIGINT,
+  total_unique_products BIGINT,
+  items_expiring_week BIGINT,
+  items_expired BIGINT,
+  total_tags_used BIGINT
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    COUNT(DISTINCT i.id) AS total_items,
+    COUNT(DISTINCT i.product_id) FILTER (WHERE i.product_id IS NOT NULL) AS total_unique_products,
+    COUNT(i.id) FILTER (
+      WHERE i.expiry_date IS NOT NULL 
+      AND i.expiry_date BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
+    ) AS items_expiring_week,
+    COUNT(i.id) FILTER (
+      WHERE i.expiry_date IS NOT NULL 
+      AND i.expiry_date < CURRENT_DATE
+    ) AS items_expired,
+    COUNT(DISTINCT it.tag_id) AS total_tags_used
+  FROM inventory_items i
+    LEFT JOIN item_tags it ON i.id = it.item_id;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION get_inventory_stats() IS 'Returns summary statistics for the entire inventory';
+
+-- Function: Search inventory (full-text search on items and products)
+CREATE OR REPLACE FUNCTION search_inventory(search_query TEXT)
+RETURNS TABLE (
+  item_id UUID,
+  item_name TEXT,
+  quantity DECIMAL,
+  unit_abbreviation TEXT,
+  product_brand TEXT,
+  tags TEXT[],
+  relevance REAL
+) AS $$
+BEGIN
+  RETURN QUERY
+  SELECT 
+    i.id AS item_id,
+    i.name AS item_name,
+    i.quantity,
+    u.abbreviation AS unit_abbreviation,
+    p.brand AS product_brand,
+    COALESCE(ARRAY_AGG(DISTINCT t.name) FILTER (WHERE t.name IS NOT NULL), '{}') AS tags,
+    ts_rank(
+      to_tsvector('english', i.name || ' ' || COALESCE(p.brand, '') || ' ' || COALESCE(p.name, '')),
+      plainto_tsquery('english', search_query)
+    ) AS relevance
+  FROM inventory_items i
+    JOIN units u ON i.unit_id = u.id
+    LEFT JOIN products p ON i.product_id = p.id
+    LEFT JOIN item_tags it ON i.id = it.item_id
+    LEFT JOIN tags t ON it.tag_id = t.id
+  WHERE 
+    to_tsvector('english', i.name || ' ' || COALESCE(p.brand, '') || ' ' || COALESCE(p.name, ''))
+    @@ plainto_tsquery('english', search_query)
+  GROUP BY i.id, i.name, i.quantity, u.abbreviation, p.brand, p.name
+  ORDER BY relevance DESC, i.created_at DESC
+  LIMIT 50;
+END;
+$$ LANGUAGE plpgsql;
+
+COMMENT ON FUNCTION search_inventory(TEXT) IS 'Full-text search across inventory items and products';