database-schema.sql

-- ShelfIQ Database Schema for Supabase
-- Run this in your Supabase SQL Editor to create the database structure

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- =====================================================
-- TABLES
-- =====================================================

-- Companies Table
CREATE TABLE companies (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name TEXT NOT NULL,
  address TEXT,
  phone TEXT,
  email TEXT,
  logo_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Users/Profiles Table (extends Supabase auth.users)
CREATE TABLE profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  company_id UUID REFERENCES companies(id) ON DELETE SET NULL,
  first_name TEXT,
  last_name TEXT,
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'manager', 'admin')),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Warehouses Table
CREATE TABLE warehouses (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  address TEXT,
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Products Table
CREATE TABLE products (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  sku TEXT NOT NULL,
  description TEXT,
  cost DECIMAL(10,2) DEFAULT 0,
  selling_price DECIMAL(10,2) DEFAULT 0,
  reorder_level INTEGER DEFAULT 0,
  reorder_quantity INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true,
  image_url TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT unique_sku_per_company UNIQUE (company_id, sku)
);

-- Inventory Table
CREATE TABLE inventory (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
  quantity_on_hand INTEGER DEFAULT 0,
  quantity_reserved INTEGER DEFAULT 0,
  last_stock_check TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT unique_product_warehouse UNIQUE (product_id, warehouse_id)
);

-- Customers Table (also used for Suppliers)
CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  email TEXT,
  phone TEXT,
  address TEXT,
  party_type TEXT DEFAULT 'customer' NOT NULL CHECK (party_type IN ('customer', 'supplier', 'both')),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Invoices Table
CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  invoice_number TEXT NOT NULL,
  customer_id UUID REFERENCES customers(id) ON DELETE SET NULL,
  invoice_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  due_date TIMESTAMP WITH TIME ZONE,
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'posted', 'paid', 'cancelled')),
  subtotal DECIMAL(10,2) DEFAULT 0,
  tax DECIMAL(10,2) DEFAULT 0,
  total DECIMAL(10,2) DEFAULT 0,
  payment_method TEXT,
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT unique_invoice_number_per_company UNIQUE (company_id, invoice_number)
);

-- Invoice Items Table
CREATE TABLE invoice_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  invoice_id UUID NOT NULL REFERENCES invoices(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Purchases Table
CREATE TABLE purchases (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
  purchase_number TEXT NOT NULL,
  supplier_id UUID REFERENCES customers(id) ON DELETE SET NULL,
  purchase_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  status TEXT DEFAULT 'draft' CHECK (status IN ('draft', 'posted', 'paid', 'cancelled')),
  subtotal DECIMAL(10,2) DEFAULT 0,
  tax DECIMAL(10,2) DEFAULT 0,
  total DECIMAL(10,2) DEFAULT 0,
  payment_method TEXT,
  payment_status TEXT DEFAULT 'pending' CHECK (payment_status IN ('pending', 'partial', 'paid')),
  notes TEXT,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  CONSTRAINT unique_purchase_number_per_company UNIQUE (company_id, purchase_number)
);

-- Purchase Items Table
CREATE TABLE purchase_items (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  purchase_id UUID NOT NULL REFERENCES purchases(id) ON DELETE CASCADE,
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
  quantity INTEGER NOT NULL DEFAULT 1,
  unit_cost DECIMAL(10,2) NOT NULL DEFAULT 0,
  line_total DECIMAL(10,2) NOT NULL DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Low Stock Alerts Table
CREATE TABLE low_stock_alerts (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  warehouse_id UUID NOT NULL REFERENCES warehouses(id) ON DELETE CASCADE,
  quantity_on_hand INTEGER NOT NULL,
  reorder_level INTEGER NOT NULL,
  is_acknowledged BOOLEAN DEFAULT false,
  acknowledged_by UUID REFERENCES profiles(id) ON DELETE SET NULL,
  acknowledged_at TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- =====================================================
-- INDEXES
-- =====================================================

CREATE INDEX idx_profiles_company_id ON profiles(company_id);
CREATE INDEX idx_products_company_id ON products(company_id);
CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_inventory_product_id ON inventory(product_id);
CREATE INDEX idx_inventory_warehouse_id ON inventory(warehouse_id);
CREATE INDEX idx_invoices_company_id ON invoices(company_id);
CREATE INDEX idx_invoices_date ON invoices(invoice_date DESC);
CREATE INDEX idx_invoice_items_invoice_id ON invoice_items(invoice_id);
CREATE INDEX idx_invoice_items_product_id ON invoice_items(product_id);
CREATE INDEX idx_purchases_company_id ON purchases(company_id);
CREATE INDEX idx_purchases_supplier_id ON purchases(supplier_id);
CREATE INDEX idx_purchases_date ON purchases(purchase_date DESC);
CREATE INDEX idx_purchase_items_purchase_id ON purchase_items(purchase_id);
CREATE INDEX idx_purchase_items_product_id ON purchase_items(product_id);
CREATE INDEX idx_low_stock_alerts_product_id ON low_stock_alerts(product_id);
CREATE INDEX idx_warehouses_company_id ON warehouses(company_id);
CREATE INDEX idx_customers_company_id ON customers(company_id);

-- =====================================================
-- FUNCTIONS
-- =====================================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to calculate invoice totals
CREATE OR REPLACE FUNCTION calculate_invoice_totals()
RETURNS TRIGGER AS $$
DECLARE
  v_subtotal DECIMAL(10,2);
BEGIN
  -- Calculate subtotal from invoice items
  SELECT COALESCE(SUM(line_total), 0)
  INTO v_subtotal
  FROM invoice_items
  WHERE invoice_id = COALESCE(NEW.invoice_id, OLD.invoice_id);

  -- Update invoice
  UPDATE invoices
  SET
    subtotal = v_subtotal,
    total = v_subtotal + COALESCE(tax, 0)
  WHERE id = COALESCE(NEW.invoice_id, OLD.invoice_id);

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Function to check and create low stock alerts
CREATE OR REPLACE FUNCTION check_low_stock()
RETURNS TRIGGER AS $$
BEGIN
  -- Check if quantity is at or below reorder level
  IF NEW.quantity_on_hand <= (SELECT reorder_level FROM products WHERE id = NEW.product_id) THEN
    -- Check if alert already exists
    IF NOT EXISTS (
      SELECT 1 FROM low_stock_alerts
      WHERE product_id = NEW.product_id
        AND warehouse_id = NEW.warehouse_id
        AND is_acknowledged = false
    ) THEN
      -- Create new alert
      INSERT INTO low_stock_alerts (
        product_id,
        warehouse_id,
        quantity_on_hand,
        reorder_level
      )
      SELECT
        NEW.product_id,
        NEW.warehouse_id,
        NEW.quantity_on_hand,
        reorder_level
      FROM products
      WHERE id = NEW.product_id;
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Function to add inventory (for purchases)
CREATE OR REPLACE FUNCTION add_inventory(
  p_product_id UUID,
  p_warehouse_id UUID,
  p_quantity INTEGER
)
RETURNS VOID AS $$
DECLARE
  v_current_quantity INTEGER;
BEGIN
  -- Check if inventory record exists
  SELECT quantity_on_hand INTO v_current_quantity
  FROM inventory
  WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;

  IF v_current_quantity IS NULL THEN
    -- Create new inventory record if it doesn't exist
    INSERT INTO inventory (product_id, warehouse_id, quantity_on_hand)
    VALUES (p_product_id, p_warehouse_id, p_quantity);
  ELSE
    -- Update existing inventory record
    UPDATE inventory
    SET quantity_on_hand = quantity_on_hand + p_quantity,
        updated_at = NOW()
    WHERE product_id = p_product_id AND warehouse_id = p_warehouse_id;
  END IF;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Function to calculate purchase totals
CREATE OR REPLACE FUNCTION calculate_purchase_totals()
RETURNS TRIGGER AS $$
DECLARE
  v_subtotal DECIMAL(10,2);
BEGIN
  -- Calculate subtotal from purchase items
  SELECT COALESCE(SUM(line_total), 0)
  INTO v_subtotal
  FROM purchase_items
  WHERE purchase_id = COALESCE(NEW.purchase_id, OLD.purchase_id);

  -- Update purchase
  UPDATE purchases
  SET
    subtotal = v_subtotal,
    total = v_subtotal + COALESCE(tax, 0)
  WHERE id = COALESCE(NEW.purchase_id, OLD.purchase_id);

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- =====================================================
-- TRIGGERS
-- =====================================================

-- Updated_at triggers
CREATE TRIGGER update_companies_updated_at BEFORE UPDATE ON companies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_profiles_updated_at BEFORE UPDATE ON profiles
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_products_updated_at BEFORE UPDATE ON products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_inventory_updated_at BEFORE UPDATE ON inventory
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_invoices_updated_at BEFORE UPDATE ON invoices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_warehouses_updated_at BEFORE UPDATE ON warehouses
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_customers_updated_at BEFORE UPDATE ON customers
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Updated_at trigger for purchases
CREATE TRIGGER update_purchases_updated_at BEFORE UPDATE ON purchases
  FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Invoice total calculation triggers
CREATE TRIGGER calculate_invoice_totals_on_insert
  AFTER INSERT ON invoice_items
  FOR EACH ROW EXECUTE FUNCTION calculate_invoice_totals();

CREATE TRIGGER calculate_invoice_totals_on_update
  AFTER UPDATE ON invoice_items
  FOR EACH ROW EXECUTE FUNCTION calculate_invoice_totals();

CREATE TRIGGER calculate_invoice_totals_on_delete
  AFTER DELETE ON invoice_items
  FOR EACH ROW EXECUTE FUNCTION calculate_invoice_totals();

-- Purchase total calculation triggers
CREATE TRIGGER calculate_purchase_totals_on_insert
  AFTER INSERT ON purchase_items
  FOR EACH ROW EXECUTE FUNCTION calculate_purchase_totals();

CREATE TRIGGER calculate_purchase_totals_on_update
  AFTER UPDATE ON purchase_items
  FOR EACH ROW EXECUTE FUNCTION calculate_purchase_totals();

CREATE TRIGGER calculate_purchase_totals_on_delete
  AFTER DELETE ON purchase_items
  FOR EACH ROW EXECUTE FUNCTION calculate_purchase_totals();

-- Low stock alert trigger
CREATE TRIGGER check_low_stock_trigger
  AFTER INSERT OR UPDATE OF quantity_on_hand ON inventory
  FOR EACH ROW EXECUTE FUNCTION check_low_stock();

-- =====================================================
-- VIEWS
-- =====================================================

-- View for products with current stock levels
CREATE OR REPLACE VIEW products_with_stock AS
SELECT
  p.id,
  p.company_id,
  p.name,
  p.sku,
  p.description,
  p.cost,
  p.selling_price,
  p.reorder_level,
  p.reorder_quantity,
  p.is_active,
  COALESCE(SUM(i.quantity_on_hand), 0) as total_quantity,
  COALESCE(SUM(i.quantity_reserved), 0) as total_reserved,
  COALESCE(SUM(i.quantity_on_hand - i.quantity_reserved), 0) as available_quantity
FROM products p
LEFT JOIN inventory i ON p.id = i.product_id
GROUP BY p.id;

-- View for low stock items
CREATE OR REPLACE VIEW low_stock_items AS
SELECT
  p.id,
  p.company_id,
  p.name,
  p.sku,
  p.reorder_level,
  i.warehouse_id,
  w.name as warehouse_name,
  i.quantity_on_hand,
  i.quantity_reserved,
  (i.quantity_on_hand - i.quantity_reserved) as available_quantity
FROM products p
JOIN inventory i ON p.id = i.product_id
JOIN warehouses w ON i.warehouse_id = w.id
WHERE p.is_active = true
  AND (i.quantity_on_hand - i.quantity_reserved) <= p.reorder_level;

-- =====================================================
-- ROW LEVEL SECURITY (RLS) POLICIES
-- =====================================================

-- Enable RLS on all tables
ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE warehouses ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory ENABLE ROW LEVEL SECURITY;
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoice_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchases ENABLE ROW LEVEL SECURITY;
ALTER TABLE purchase_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE low_stock_alerts ENABLE ROW LEVEL SECURITY;

-- Helper function to get user's company_id (prevents infinite recursion)
CREATE OR REPLACE FUNCTION public.user_company_id()
RETURNS UUID
LANGUAGE SQL
SECURITY DEFINER
STABLE
AS $$
  SELECT company_id FROM public.profiles WHERE id = auth.uid() LIMIT 1;
$$;

-- Companies policies (using helper function to avoid recursion)
CREATE POLICY "Users can view their company"
  ON companies FOR SELECT
  USING (id = public.user_company_id());

CREATE POLICY "Users can insert their company"
  ON companies FOR INSERT
  WITH CHECK (true);

CREATE POLICY "Users can update their company"
  ON companies FOR UPDATE
  USING (id = public.user_company_id())
  WITH CHECK (id = public.user_company_id());

-- Profiles policies (Fixed to avoid infinite recursion and duplicate rows)
CREATE POLICY "Users can view profiles"
  ON profiles FOR SELECT
  USING (
    id = auth.uid()
    OR (company_id IS NOT NULL AND company_id = public.user_company_id())
  );

CREATE POLICY "Users can update their own profile"
  ON profiles FOR UPDATE
  USING (id = auth.uid())
  WITH CHECK (id = auth.uid());

CREATE POLICY "Users can insert their own profile"
  ON profiles FOR INSERT
  WITH CHECK (id = auth.uid());

-- Warehouses policies (using helper function)
CREATE POLICY "Users can view warehouses in their company"
  ON warehouses FOR SELECT
  USING (company_id = public.user_company_id());

CREATE POLICY "Users can insert warehouses in their company"
  ON warehouses FOR INSERT
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can update warehouses in their company"
  ON warehouses FOR UPDATE
  USING (company_id = public.user_company_id())
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can delete warehouses in their company"
  ON warehouses FOR DELETE
  USING (company_id = public.user_company_id());

-- Products policies (using helper function)
CREATE POLICY "Users can view products in their company"
  ON products FOR SELECT
  USING (company_id = public.user_company_id());

CREATE POLICY "Users can insert products in their company"
  ON products FOR INSERT
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can update products in their company"
  ON products FOR UPDATE
  USING (company_id = public.user_company_id())
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can delete products in their company"
  ON products FOR DELETE
  USING (company_id = public.user_company_id());

-- Inventory policies (using helper function)
CREATE POLICY "Users can view inventory in their company"
  ON inventory FOR SELECT
  USING (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can insert inventory in their company"
  ON inventory FOR INSERT
  WITH CHECK (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can update inventory in their company"
  ON inventory FOR UPDATE
  USING (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()))
  WITH CHECK (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can delete inventory in their company"
  ON inventory FOR DELETE
  USING (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

-- Customers policies (using helper function)
CREATE POLICY "Users can view customers in their company"
  ON customers FOR SELECT
  USING (company_id = public.user_company_id());

CREATE POLICY "Users can insert customers in their company"
  ON customers FOR INSERT
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can update customers in their company"
  ON customers FOR UPDATE
  USING (company_id = public.user_company_id())
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can delete customers in their company"
  ON customers FOR DELETE
  USING (company_id = public.user_company_id());

-- Invoices policies (using helper function)
CREATE POLICY "Users can view invoices in their company"
  ON invoices FOR SELECT
  USING (company_id = public.user_company_id());

CREATE POLICY "Users can insert invoices in their company"
  ON invoices FOR INSERT
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can update invoices in their company"
  ON invoices FOR UPDATE
  USING (company_id = public.user_company_id())
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can delete invoices in their company"
  ON invoices FOR DELETE
  USING (company_id = public.user_company_id());

-- Invoice items policies (using helper function)
CREATE POLICY "Users can view invoice items in their company"
  ON invoice_items FOR SELECT
  USING (invoice_id IN (SELECT id FROM invoices WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can insert invoice items in their company"
  ON invoice_items FOR INSERT
  WITH CHECK (invoice_id IN (SELECT id FROM invoices WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can update invoice items in their company"
  ON invoice_items FOR UPDATE
  USING (invoice_id IN (SELECT id FROM invoices WHERE company_id = public.user_company_id()))
  WITH CHECK (invoice_id IN (SELECT id FROM invoices WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can delete invoice items in their company"
  ON invoice_items FOR DELETE
  USING (invoice_id IN (SELECT id FROM invoices WHERE company_id = public.user_company_id()));

-- Purchases policies (using helper function)
CREATE POLICY "Users can view purchases in their company"
  ON purchases FOR SELECT
  USING (company_id = public.user_company_id());

CREATE POLICY "Users can insert purchases in their company"
  ON purchases FOR INSERT
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can update purchases in their company"
  ON purchases FOR UPDATE
  USING (company_id = public.user_company_id())
  WITH CHECK (company_id = public.user_company_id());

CREATE POLICY "Users can delete purchases in their company"
  ON purchases FOR DELETE
  USING (company_id = public.user_company_id());

-- Purchase items policies (using helper function)
CREATE POLICY "Users can view purchase items in their company"
  ON purchase_items FOR SELECT
  USING (purchase_id IN (SELECT id FROM purchases WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can insert purchase items in their company"
  ON purchase_items FOR INSERT
  WITH CHECK (purchase_id IN (SELECT id FROM purchases WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can update purchase items in their company"
  ON purchase_items FOR UPDATE
  USING (purchase_id IN (SELECT id FROM purchases WHERE company_id = public.user_company_id()))
  WITH CHECK (purchase_id IN (SELECT id FROM purchases WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can delete purchase items in their company"
  ON purchase_items FOR DELETE
  USING (purchase_id IN (SELECT id FROM purchases WHERE company_id = public.user_company_id()));

-- Low stock alerts policies (using helper function)
CREATE POLICY "Users can view alerts in their company"
  ON low_stock_alerts FOR SELECT
  USING (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

CREATE POLICY "Users can update alerts in their company"
  ON low_stock_alerts FOR UPDATE
  USING (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()))
  WITH CHECK (product_id IN (SELECT id FROM products WHERE company_id = public.user_company_id()));

-- =====================================================
-- INITIAL DATA (Optional - for testing)
-- =====================================================

-- Uncomment to insert test data
/*
-- Insert a test company
INSERT INTO companies (name, address, email) VALUES
  ('Test Company', '123 Main St, City, State', 'test@company.com');

-- Insert a test warehouse (replace company_id with actual UUID)
INSERT INTO warehouses (company_id, name, address) VALUES
  ('YOUR_COMPANY_UUID', 'Main Warehouse', '123 Warehouse St');
*/