-- ============================================
-- 此文件由 Meoo Cloud 自动生成，请勿修改
-- This file is auto-generated by Meoo Cloud, DO NOT MODIFY
-- ============================================


-- 创建成品仓库表
CREATE TABLE finished_products (
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 company_id UUID NOT NULL REFERENCES companies(id) ON DELETE CASCADE,
 
 -- 关联信息
 washing_plan_id UUID REFERENCES washing_plans(id) ON DELETE SET NULL,
 product_id UUID REFERENCES products(id) ON DELETE SET NULL,
 
 -- 产品信息（从水洗计划继承）
 product_name TEXT NOT NULL,
 color TEXT NOT NULL,
 fabric_code TEXT NOT NULL,
 color_code TEXT,
 weight DECIMAL(8, 2),
 
 -- 水洗后数据
 washed_meters DECIMAL(12, 2) NOT NULL DEFAULT 0,
 shrinkage_rate DECIMAL(5, 2) NOT NULL DEFAULT 0,
 rolls INTEGER DEFAULT 0,
 
 -- 库存数据
 stock_meters DECIMAL(12, 2) NOT NULL DEFAULT 0,
 stock_rolls INTEGER NOT NULL DEFAULT 0,
 
 -- 仓库位置
 warehouse_id UUID REFERENCES warehouses(id) ON DELETE SET NULL,
 warehouse_location TEXT,
 
 -- 价格信息
 washing_cost DECIMAL(10, 2) DEFAULT 0,
 unit_cost DECIMAL(10, 2) DEFAULT 0,
 
 -- 状态
 status TEXT NOT NULL DEFAULT 'in_stock' CHECK (status IN ('in_stock', 'partial_out', 'sold_out')),
 
 -- 备注
 notes TEXT,
 
 -- 元数据
 created_by UUID REFERENCES profiles(id),
 created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
 updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_finished_products_company ON finished_products(company_id);
CREATE INDEX idx_finished_products_washing_plan ON finished_products(washing_plan_id);
CREATE INDEX idx_finished_products_product ON finished_products(product_id);
CREATE INDEX idx_finished_products_status ON finished_products(status);
CREATE INDEX idx_finished_products_fabric_code ON finished_products(fabric_code);

-- 启用 RLS
ALTER TABLE finished_products ENABLE ROW LEVEL SECURITY;

-- RLS 策略：公司成员可查看本公司成品
CREATE POLICY company_select_finished_products ON finished_products
 FOR SELECT USING (company_id = get_user_master_company_id());

-- RLS 策略：公司成员可创建本公司成品
CREATE POLICY company_insert_finished_products ON finished_products
 FOR INSERT WITH CHECK (company_id = get_user_master_company_id());

-- RLS 策略：公司成员可更新本公司成品
CREATE POLICY company_update_finished_products ON finished_products
 FOR UPDATE USING (company_id = get_user_master_company_id());

-- RLS 策略：公司成员可删除本公司成品
CREATE POLICY company_delete_finished_products ON finished_products
 FOR DELETE USING (company_id = get_user_master_company_id());

