iloom-flatten/migrations/20260604_000000_fix_all_rls_policies.sql

-- ============================================
-- RLS策略全面修复
-- 修复所有不符合设计规范的权限问题
-- ============================================

-- ============================================
-- 1. 修复 production_plans 表的严重安全漏洞
-- 问题：20260527_114150_fix_import_plan_select_rls.sql 中的策略过于宽松
-- 允许任何登录用户查看所有有关联的计划
-- ============================================

DROP POLICY IF EXISTS users_select_plans ON production_plans;

-- 正确的策略：采购商只能查看自己的计划，工厂只能查看关联的计划
CREATE POLICY purchaser_select_own_plans ON production_plans
  FOR SELECT USING (
    purchaser_id = get_user_master_company_id()
  );

CREATE POLICY factory_select_linked_plans ON production_plans
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM plan_factories
      WHERE plan_factories.plan_id = production_plans.id
      AND plan_factories.factory_id = get_user_master_company_id()
    )
  );

-- 分享链接导入时的临时访问（通过 share_links 表验证）
CREATE POLICY factory_select_via_share_link ON production_plans
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM share_links
      WHERE share_links.plan_id = production_plans.id
      AND share_links.short_code IS NOT NULL
      AND (share_links.expires_at IS NULL OR share_links.expires_at > NOW())
    )
  );

-- ============================================
-- 2. 修复 companies 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_companies ON companies;
DROP POLICY IF EXISTS purchaser_select_linked_companies ON companies;
DROP POLICY IF EXISTS factory_select_linked_companies ON companies;

-- 用户可以查看自己的公司
CREATE POLICY users_select_own_company ON companies
  FOR SELECT USING (
    id = get_user_master_company_id()
  );

-- 采购商可以查看已建立合作关系的工厂
CREATE POLICY purchaser_select_linked_factories ON companies
  FOR SELECT USING (
    role IN ('textile', 'washing')
    AND EXISTS (
      SELECT 1 FROM company_relationships
      WHERE company_relationships.purchaser_company_id = get_user_master_company_id()
      AND company_relationships.factory_company_id = companies.id
      AND company_relationships.status = 'active'
    )
  );

-- 工厂可以查看已建立合作关系的采购商
CREATE POLICY factory_select_linked_purchasers ON companies
  FOR SELECT USING (
    role = 'purchaser'
    AND EXISTS (
      SELECT 1 FROM company_relationships
      WHERE company_relationships.factory_company_id = get_user_master_company_id()
      AND company_relationships.purchaser_company_id = companies.id
      AND company_relationships.status = 'active'
    )
  );

-- ============================================
-- 3. 修复 profiles 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_profiles ON profiles;
DROP POLICY IF EXISTS anon_insert_profiles ON profiles;

-- 用户可以查看自己公司的所有成员
CREATE POLICY users_select_company_profiles ON profiles
  FOR SELECT USING (
    company_id = get_user_master_company_id()
    OR id = auth.uid()
  );

-- 用户只能插入自己的profile
CREATE POLICY users_insert_own_profile ON profiles
  FOR INSERT WITH CHECK (
    id = auth.uid()
  );

-- ============================================
-- 4. 修复 company_members 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_company_members ON company_members;
DROP POLICY IF EXISTS anon_insert_company_members ON company_members;

-- 只能查看自己公司的成员
CREATE POLICY users_select_company_members ON company_members
  FOR SELECT USING (
    company_id = get_user_master_company_id()
  );

-- 只能给自己公司添加成员
CREATE POLICY users_insert_company_members ON company_members
  FOR INSERT WITH CHECK (
    company_id = get_user_master_company_id()
  );

-- ============================================
-- 5. 修复 yarn_ratios 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_yarn_ratios ON yarn_ratios;
DROP POLICY IF EXISTS anon_insert_yarn_ratios ON yarn_ratios;
DROP POLICY IF EXISTS anon_delete_yarn_ratios ON yarn_ratios;

-- 查看权限：计划的采购商或关联工厂可以查看
CREATE POLICY users_select_yarn_ratios ON yarn_ratios
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = yarn_ratios.plan_id
      AND (
        purchaser_id = get_user_master_company_id()
        OR EXISTS (
          SELECT 1 FROM plan_factories
          WHERE plan_id = production_plans.id
          AND factory_id = get_user_master_company_id()
        )
      )
    )
  );

-- 插入权限：只有计划的采购商可以插入
CREATE POLICY users_insert_yarn_ratios ON yarn_ratios
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = yarn_ratios.plan_id
      AND purchaser_id = get_user_master_company_id()
    )
  );

-- 删除权限：只有计划的采购商可以删除
CREATE POLICY users_delete_yarn_ratios ON yarn_ratios
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = yarn_ratios.plan_id
      AND purchaser_id = get_user_master_company_id()
    )
  );

-- ============================================
-- 6. 修复 warehouses 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_warehouses ON warehouses;
DROP POLICY IF EXISTS anon_insert_warehouses ON warehouses;
DROP POLICY IF EXISTS anon_update_warehouses ON warehouses;

-- 只能查看自己公司的仓库
CREATE POLICY users_select_company_warehouses ON warehouses
  FOR SELECT USING (
    company_id = get_user_master_company_id()
  );

-- 只能插入自己公司的仓库
CREATE POLICY users_insert_company_warehouses ON warehouses
  FOR INSERT WITH CHECK (
    company_id = get_user_master_company_id()
  );

-- 只能更新自己公司的仓库
CREATE POLICY users_update_company_warehouses ON warehouses
  FOR UPDATE USING (
    company_id = get_user_master_company_id()
  );

-- ============================================
-- 7. 修复 inventory_records 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_inventory ON inventory_records;
DROP POLICY IF EXISTS anon_insert_inventory ON inventory_records;

-- 查看权限：通过仓库关联到公司
CREATE POLICY users_select_company_inventory ON inventory_records
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM warehouses
      WHERE id = warehouse_id
      AND company_id = get_user_master_company_id()
    )
    OR EXISTS (
      SELECT 1 FROM production_plans p
      JOIN plan_factories pf ON p.id = pf.plan_id
      WHERE p.id = inventory_records.plan_id
      AND (p.purchaser_id = get_user_master_company_id() OR pf.factory_id = get_user_master_company_id())
    )
  );

-- 插入权限：通过仓库验证
CREATE POLICY users_insert_company_inventory ON inventory_records
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM warehouses
      WHERE id = warehouse_id
      AND company_id = get_user_master_company_id()
    )
  );

-- ============================================
-- 8. 修复 yarn_stock 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_yarn_stock ON yarn_stock;
DROP POLICY IF EXISTS anon_insert_yarn_stock ON yarn_stock;
DROP POLICY IF EXISTS anon_update_yarn_stock ON yarn_stock;

-- 只能查看自己公司的纱线库存
CREATE POLICY users_select_company_yarn_stock ON yarn_stock
  FOR SELECT USING (
    company_id = get_user_master_company_id()
  );

-- 只能插入自己公司的纱线库存
CREATE POLICY users_insert_company_yarn_stock ON yarn_stock
  FOR INSERT WITH CHECK (
    company_id = get_user_master_company_id()
  );

-- 只能更新自己公司的纱线库存
CREATE POLICY users_update_company_yarn_stock ON yarn_stock
  FOR UPDATE USING (
    company_id = get_user_master_company_id()
  );

-- ============================================
-- 9. 修复 plan_process_steps 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_process_steps ON plan_process_steps;
DROP POLICY IF EXISTS anon_insert_process_steps ON plan_process_steps;
DROP POLICY IF EXISTS anon_update_process_steps ON plan_process_steps;

-- 查看权限：计划的采购商或关联工厂可以查看
CREATE POLICY users_select_process_steps ON plan_process_steps
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM production_plans
      LEFT JOIN plan_factories ON production_plans.id = plan_factories.plan_id
      WHERE production_plans.id = plan_process_steps.plan_id
      AND (
        production_plans.purchaser_id = get_user_master_company_id()
        OR plan_factories.factory_id = get_user_master_company_id()
      )
    )
  );

-- 插入权限：计划的采购商或关联工厂可以插入
CREATE POLICY users_insert_process_steps ON plan_process_steps
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM production_plans
      LEFT JOIN plan_factories ON production_plans.id = plan_factories.plan_id
      WHERE production_plans.id = plan_process_steps.plan_id
      AND (
        production_plans.purchaser_id = get_user_master_company_id()
        OR plan_factories.factory_id = get_user_master_company_id()
      )
    )
  );

-- 更新权限：计划的采购商或关联工厂可以更新
CREATE POLICY users_update_process_steps ON plan_process_steps
  FOR UPDATE USING (
    EXISTS (
      SELECT 1 FROM production_plans
      LEFT JOIN plan_factories ON production_plans.id = plan_factories.plan_id
      WHERE production_plans.id = plan_process_steps.plan_id
      AND (
        production_plans.purchaser_id = get_user_master_company_id()
        OR plan_factories.factory_id = get_user_master_company_id()
      )
    )
  );

-- ============================================
-- 10. 修复 payments 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_payments ON payments;
DROP POLICY IF EXISTS anon_insert_payments ON payments;
DROP POLICY IF EXISTS anon_update_payments ON payments;

-- 查看权限：付款方或收款方可以查看
CREATE POLICY users_select_payments ON payments
  FOR SELECT USING (
    from_company_id = get_user_master_company_id()
    OR to_company_id = get_user_master_company_id()
  );

-- 插入权限：付款方可以插入
CREATE POLICY users_insert_payments ON payments
  FOR INSERT WITH CHECK (
    from_company_id = get_user_master_company_id()
    OR to_company_id = get_user_master_company_id()
  );

-- 更新权限：付款方或收款方可以更新
CREATE POLICY users_update_payments ON payments
  FOR UPDATE USING (
    from_company_id = get_user_master_company_id()
    OR to_company_id = get_user_master_company_id()
  );

-- ============================================
-- 11. 修复 plan_factories 表的权限问题
-- ============================================

DROP POLICY IF EXISTS anon_select_plan_factories ON plan_factories;
DROP POLICY IF EXISTS anon_insert_plan_factories ON plan_factories;
DROP POLICY IF EXISTS anon_delete_plan_factories ON plan_factories;

-- 工厂可以查看关联到自己的计划
CREATE POLICY factory_select_plan_factories ON plan_factories
  FOR SELECT USING (
    factory_id = get_user_master_company_id()
  );

-- 采购商可以查看自己计划的工厂关联
CREATE POLICY purchaser_select_plan_factories ON plan_factories
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = plan_id
      AND purchaser_id = get_user_master_company_id()
    )
  );

-- 采购商可以创建工厂关联
CREATE POLICY purchaser_insert_plan_factories ON plan_factories
  FOR INSERT WITH CHECK (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = plan_id
      AND purchaser_id = get_user_master_company_id()
    )
  );

-- 采购商可以删除工厂关联
CREATE POLICY purchaser_delete_plan_factories ON plan_factories
  FOR DELETE USING (
    EXISTS (
      SELECT 1 FROM production_plans
      WHERE id = plan_id
      AND purchaser_id = get_user_master_company_id()
    )
  );

-- 工厂可以创建关联（通过分享链接导入时）
CREATE POLICY factory_insert_plan_factories ON plan_factories
  FOR INSERT WITH CHECK (
    factory_id = get_user_master_company_id()
  );

-- ============================================
-- 12. 修复 share_links 表的权限问题
-- ============================================

DROP POLICY IF EXISTS share_links_select ON share_links;

-- 创建者可以查看自己的分享链接
-- 有合作关系的公司可以查看相关分享链接
CREATE POLICY users_select_share_links ON share_links
  FOR SELECT USING (
    created_by = auth.uid()
    OR EXISTS (
      SELECT 1 FROM profiles p
      WHERE p.id = auth.uid()
      AND p.company_id IN (
        SELECT purchaser_company_id FROM company_relationships
        WHERE factory_company_id = p.company_id
      )
    )
    OR short_code IS NOT NULL
  );

-- ============================================
-- 13. 确保所有表都有RLS启用
-- ============================================

ALTER TABLE companies ENABLE ROW LEVEL SECURITY;
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE company_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE production_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE plan_factories ENABLE ROW LEVEL SECURITY;
ALTER TABLE yarn_ratios ENABLE ROW LEVEL SECURITY;
ALTER TABLE warehouses ENABLE ROW LEVEL SECURITY;
ALTER TABLE inventory_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE yarn_stock ENABLE ROW LEVEL SECURITY;
ALTER TABLE plan_process_steps ENABLE ROW LEVEL SECURITY;
ALTER TABLE payments ENABLE ROW LEVEL SECURITY;
ALTER TABLE share_links ENABLE ROW LEVEL SECURITY;
ALTER TABLE company_relationships ENABLE ROW LEVEL SECURITY;
ALTER TABLE products ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_inventory_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_yarn_ratios ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_price_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE product_outbound_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE production_price_history ENABLE ROW LEVEL SECURITY;
ALTER TABLE washing_plans ENABLE ROW LEVEL SECURITY;
ALTER TABLE washing_process_steps ENABLE ROW LEVEL SECURITY;
ALTER TABLE washing_plan_completions ENABLE ROW LEVEL SECURITY;
ALTER TABLE finished_products ENABLE ROW LEVEL SECURITY;
ALTER TABLE finished_product_inventory_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE yarn_stock_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts_payable ENABLE ROW LEVEL SECURITY;
ALTER TABLE accounts_payable_items ENABLE ROW LEVEL SECURITY;
ALTER TABLE notifications ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_feedback ENABLE ROW LEVEL SECURITY;
ALTER TABLE crash_reports ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;