-- iloom 微服务数据库初始化脚本
-- 创建 5 个 Schema，迁移表结构，建立跨 Schema 视图

-- =============================================
-- 1. 创建 Schema
-- =============================================
CREATE SCHEMA IF NOT EXISTS common;
CREATE SCHEMA IF NOT EXISTS purchaser;
CREATE SCHEMA IF NOT EXISTS textile;
CREATE SCHEMA IF NOT EXISTS washing;
CREATE SCHEMA IF NOT EXISTS audit;

-- =============================================
-- 2. 创建枚举类型（在 public schema，所有 schema 共享）
-- =============================================
DO $$ BEGIN
    CREATE TYPE app_role AS ENUM ('purchaser', 'textile', 'washing');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE factory_type AS ENUM ('textile', 'washing');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE plan_status AS ENUM ('pending', 'producing', 'completed');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE step_status AS ENUM ('pending', 'active', 'completed', 'rejected');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE step_type AS ENUM ('confirm', 'yarn_purchase', 'dyeing', 'machine_start', 'fabric_warehouse');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE warehouse_type AS ENUM ('raw_fabric', 'fabric', 'finished', 'yarn');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

DO $$ BEGIN
    CREATE TYPE payment_status AS ENUM ('pending', 'completed');
EXCEPTION WHEN duplicate_object THEN NULL;
END $$;

-- =============================================
-- 3. common Schema 表
-- =============================================

CREATE TABLE IF NOT EXISTS common.companies (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    role app_role NOT NULL,
    address TEXT,
    contact_phone TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.profiles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    phone TEXT,
    company_id UUID REFERENCES common.companies(id),
    is_master BOOLEAN NOT NULL DEFAULT true,
    master_id UUID REFERENCES common.profiles(id),
    display_name TEXT,
    image_url TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.company_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    user_id UUID NOT NULL REFERENCES common.profiles(id),
    role TEXT NOT NULL DEFAULT 'member',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.company_relationships (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    purchaser_company_id UUID NOT NULL REFERENCES common.companies(id),
    factory_company_id UUID NOT NULL REFERENCES common.companies(id),
    factory_type factory_type NOT NULL,
    status TEXT NOT NULL DEFAULT 'active',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.notifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    recipient_id UUID NOT NULL,
    sender_id UUID,
    sender_company_id UUID REFERENCES common.companies(id),
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    type TEXT NOT NULL,
    plan_id UUID,
    is_read BOOLEAN DEFAULT false,
    read_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.share_links (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL,
    created_by UUID NOT NULL,
    short_code TEXT NOT NULL UNIQUE,
    factory_type TEXT NOT NULL,
    hide_sensitive BOOLEAN DEFAULT false,
    status TEXT NOT NULL DEFAULT 'pending',
    expires_at TIMESTAMPTZ,
    clicked_at TIMESTAMPTZ,
    click_count INT DEFAULT 0,
    used_at TIMESTAMPTZ,
    response_result TEXT,
    reject_reason TEXT,
    responded_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.user_feedback (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    type TEXT NOT NULL,
    content TEXT NOT NULL,
    contact TEXT,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS common.crash_reports (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    error_message TEXT NOT NULL,
    error_stack TEXT,
    component_stack TEXT,
    url TEXT,
    user_agent TEXT,
    user_description TEXT,
    status TEXT NOT NULL DEFAULT 'new',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================
-- 4. purchaser Schema 表
-- =============================================

CREATE TABLE IF NOT EXISTS purchaser.production_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_code TEXT NOT NULL,
    purchaser_id UUID NOT NULL REFERENCES common.companies(id),
    product_name TEXT NOT NULL,
    fabric_code TEXT NOT NULL,
    color TEXT NOT NULL,
    color_code TEXT NOT NULL DEFAULT '',
    target_quantity NUMERIC NOT NULL DEFAULT 0,
    completed_quantity NUMERIC NOT NULL DEFAULT 0,
    production_price NUMERIC,
    yarn_usage_per_meter NUMERIC,
    status plan_status NOT NULL DEFAULT 'pending',
    remark TEXT,
    start_time TIMESTAMPTZ,
    created_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.plan_factories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id) ON DELETE CASCADE,
    factory_id UUID NOT NULL REFERENCES common.companies(id),
    factory_type factory_type NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.yarn_ratios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id) ON DELETE CASCADE,
    yarn_name TEXT NOT NULL,
    yarn_type TEXT,
    ratio NUMERIC NOT NULL DEFAULT 0,
    amount_per_meter NUMERIC NOT NULL DEFAULT 0,
    total_amount NUMERIC NOT NULL DEFAULT 0,
    company_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    product_name TEXT NOT NULL,
    fabric_code TEXT NOT NULL,
    color TEXT NOT NULL,
    color_code TEXT NOT NULL DEFAULT '',
    weight NUMERIC NOT NULL DEFAULT 0,
    warp_weight NUMERIC,
    weft_weight NUMERIC,
    total_weight NUMERIC,
    yarn_usage_per_meter NUMERIC NOT NULL DEFAULT 0,
    production_price NUMERIC,
    total_stock NUMERIC NOT NULL DEFAULT 0,
    raw_fabric_meters NUMERIC NOT NULL DEFAULT 0,
    raw_fabric_rolls INT NOT NULL DEFAULT 0,
    image_url TEXT,
    yarn_types TEXT[],
    yarn_ratios NUMERIC[],
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.product_inventory_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES purchaser.products(id) ON DELETE CASCADE,
    batch_no TEXT NOT NULL,
    rolls INT NOT NULL DEFAULT 0,
    meters NUMERIC NOT NULL DEFAULT 0,
    notes TEXT,
    operator_id UUID,
    company_id UUID,
    warehouse_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.product_outbound_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES purchaser.products(id) ON DELETE CASCADE,
    batch_no TEXT NOT NULL DEFAULT '',
    rolls INT NOT NULL DEFAULT 0,
    meters NUMERIC NOT NULL DEFAULT 0,
    outbound_type TEXT NOT NULL DEFAULT 'normal',
    notes TEXT,
    operator_id UUID,
    company_id UUID,
    recipient_company_id UUID,
    recipient_company_name TEXT,
    source_batch_id UUID,
    washing_plan_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.product_yarn_ratios (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES purchaser.products(id) ON DELETE CASCADE,
    yarn_name TEXT NOT NULL,
    yarn_type TEXT,
    ratio NUMERIC NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.product_price_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id UUID NOT NULL REFERENCES purchaser.products(id) ON DELETE CASCADE,
    company_id UUID NOT NULL,
    old_price NUMERIC NOT NULL DEFAULT 0,
    new_price NUMERIC NOT NULL DEFAULT 0,
    change_amount NUMERIC NOT NULL DEFAULT 0,
    change_reason TEXT,
    operator_id UUID,
    operator_name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.production_price_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id) ON DELETE CASCADE,
    old_price NUMERIC NOT NULL DEFAULT 0,
    new_price NUMERIC NOT NULL DEFAULT 0,
    change_amount NUMERIC NOT NULL DEFAULT 0,
    change_reason TEXT,
    operator_id UUID,
    operator_name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.accounts_payable (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id),
    purchaser_id UUID NOT NULL REFERENCES common.companies(id),
    textile_factory_id UUID NOT NULL REFERENCES common.companies(id),
    price_per_meter NUMERIC NOT NULL DEFAULT 0,
    total_quantity NUMERIC NOT NULL DEFAULT 0,
    total_amount NUMERIC NOT NULL DEFAULT 0,
    paid_quantity NUMERIC NOT NULL DEFAULT 0,
    paid_amount NUMERIC NOT NULL DEFAULT 0,
    unpaid_quantity NUMERIC NOT NULL DEFAULT 0,
    unpaid_amount NUMERIC NOT NULL DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.accounts_payable_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    accounts_payable_id UUID NOT NULL REFERENCES purchaser.accounts_payable(id) ON DELETE CASCADE,
    inventory_record_id UUID,
    payment_id UUID,
    quantity NUMERIC NOT NULL DEFAULT 0,
    price_per_meter NUMERIC NOT NULL DEFAULT 0,
    amount NUMERIC NOT NULL DEFAULT 0,
    rolls INT,
    status TEXT NOT NULL DEFAULT 'pending',
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS purchaser.washing_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_code TEXT NOT NULL,
    company_id UUID NOT NULL REFERENCES common.companies(id),
    product_id UUID NOT NULL REFERENCES purchaser.products(id),
    washing_factory_id UUID REFERENCES common.companies(id),
    planned_meters NUMERIC NOT NULL DEFAULT 0,
    washing_price NUMERIC NOT NULL DEFAULT 0,
    estimated_shrinkage_rate NUMERIC NOT NULL DEFAULT 0,
    estimated_washed_meters NUMERIC,
    estimated_total_cost NUMERIC,
    actual_shrinkage_rate NUMERIC,
    actual_washed_meters NUMERIC,
    actual_total_cost NUMERIC,
    washing_date TIMESTAMPTZ,
    status TEXT NOT NULL DEFAULT 'pending',
    notes TEXT,
    created_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================
-- 5. textile Schema 表
-- =============================================

CREATE TABLE IF NOT EXISTS textile.plan_process_steps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id) ON DELETE CASCADE,
    step_type step_type NOT NULL,
    status step_status NOT NULL DEFAULT 'pending',
    notes TEXT,
    operator_id UUID,
    company_id UUID,
    timestamp TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS textile.warehouses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    name TEXT NOT NULL,
    type warehouse_type NOT NULL,
    location TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS textile.inventory_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id),
    warehouse_id UUID NOT NULL REFERENCES textile.warehouses(id),
    quantity NUMERIC NOT NULL DEFAULT 0,
    rolls INT,
    price_per_meter NUMERIC,
    price_note TEXT,
    warehouse_location TEXT,
    operator_id UUID,
    company_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS textile.yarn_stock (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    name TEXT NOT NULL,
    spec TEXT,
    quantity NUMERIC NOT NULL DEFAULT 0,
    unit TEXT NOT NULL DEFAULT 'kg',
    min_stock NUMERIC NOT NULL DEFAULT 0,
    warehouse_id UUID REFERENCES textile.warehouses(id),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS textile.yarn_stock_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    yarn_stock_id UUID REFERENCES textile.yarn_stock(id),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    record_type TEXT NOT NULL,
    quantity NUMERIC NOT NULL DEFAULT 0,
    unit TEXT NOT NULL DEFAULT 'kg',
    batch_no TEXT,
    plan_id UUID,
    notes TEXT,
    operator_id UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS textile.payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    plan_id UUID NOT NULL REFERENCES purchaser.production_plans(id),
    from_company_id UUID NOT NULL REFERENCES common.companies(id),
    to_company_id UUID NOT NULL REFERENCES common.companies(id),
    amount NUMERIC NOT NULL DEFAULT 0,
    quantity NUMERIC NOT NULL DEFAULT 0,
    price_per_meter NUMERIC NOT NULL DEFAULT 0,
    status payment_status NOT NULL DEFAULT 'pending',
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================
-- 6. washing Schema 表
-- =============================================

CREATE TABLE IF NOT EXISTS washing.washing_process_steps (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    washing_plan_id UUID NOT NULL REFERENCES purchaser.washing_plans(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES common.companies(id),
    step_type TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending',
    notes TEXT,
    operator_id UUID,
    timestamp TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS washing.washing_plan_completions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    washing_plan_id UUID NOT NULL REFERENCES purchaser.washing_plans(id),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    actual_shrinkage_rate NUMERIC NOT NULL,
    actual_washed_meters NUMERIC NOT NULL,
    actual_washing_cost NUMERIC,
    unit_cost NUMERIC,
    rolls INT NOT NULL DEFAULT 0,
    washing_date DATE NOT NULL,
    notes TEXT,
    completed_by UUID,
    completed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    finished_product_id UUID,
    auto_imported BOOLEAN DEFAULT false,
    warehouse_id UUID,
    warehouse_location TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS washing.finished_products (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    company_id UUID NOT NULL REFERENCES common.companies(id),
    product_name TEXT NOT NULL,
    fabric_code TEXT NOT NULL,
    color TEXT NOT NULL,
    color_code TEXT,
    weight NUMERIC,
    shrinkage_rate NUMERIC NOT NULL DEFAULT 0,
    washed_meters NUMERIC NOT NULL DEFAULT 0,
    stock_meters NUMERIC NOT NULL DEFAULT 0,
    stock_rolls INT NOT NULL DEFAULT 0,
    rolls INT,
    washing_cost NUMERIC,
    unit_cost NUMERIC,
    status TEXT NOT NULL DEFAULT 'active',
    notes TEXT,
    product_id UUID,
    washing_plan_id UUID,
    warehouse_id UUID,
    warehouse_location TEXT,
    created_by UUID,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS washing.finished_product_inventory_records (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    finished_product_id UUID NOT NULL REFERENCES washing.finished_products(id) ON DELETE CASCADE,
    company_id UUID NOT NULL REFERENCES common.companies(id),
    record_type TEXT NOT NULL,
    rolls INT NOT NULL DEFAULT 0,
    meters NUMERIC NOT NULL DEFAULT 0,
    batch_no TEXT,
    notes TEXT,
    operator_id UUID,
    warehouse_id UUID,
    warehouse_location TEXT,
    washing_completion_id UUID,
    related_order_id UUID,
    related_order_type TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================
-- 7. audit Schema 表
-- =============================================

CREATE TABLE IF NOT EXISTS audit.audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID,
    company_id UUID,
    action TEXT NOT NULL,
    resource_type TEXT NOT NULL,
    resource_id UUID,
    details JSONB,
    ip_address TEXT,
    user_agent TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit.data_audit_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name TEXT NOT NULL,
    record_id TEXT NOT NULL,
    operation TEXT NOT NULL,
    old_values JSONB,
    new_values JSONB,
    changed_by UUID,
    error_message TEXT,
    validation_passed BOOLEAN NOT NULL DEFAULT true,
    changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- =============================================
-- 8. 跨 Schema 只读视图
-- =============================================

-- textile-service 需要读取分配给自己的计划
CREATE OR REPLACE VIEW textile.v_assigned_plans AS
SELECT
    pp.id, pp.plan_code, pp.product_name, pp.fabric_code,
    pp.color, pp.color_code, pp.target_quantity, pp.completed_quantity,
    pp.production_price, pp.yarn_usage_per_meter, pp.status,
    pp.remark, pp.start_time, pp.created_at, pp.updated_at,
    pf.factory_id, pf.id AS plan_factory_id,
    c.name AS purchaser_name
FROM purchaser.production_plans pp
JOIN purchaser.plan_factories pf ON pp.id = pf.plan_id
JOIN common.companies c ON pp.purchaser_id = c.id
WHERE pf.factory_type = 'textile';

-- washing-service 需要读取分配给自己的水洗计划
CREATE OR REPLACE VIEW washing.v_assigned_washing_plans AS
SELECT
    wp.id, wp.plan_code, wp.company_id, wp.product_id,
    wp.washing_factory_id, wp.planned_meters, wp.washing_price,
    wp.estimated_shrinkage_rate, wp.estimated_washed_meters,
    wp.estimated_total_cost, wp.actual_shrinkage_rate,
    wp.actual_washed_meters, wp.actual_total_cost,
    wp.washing_date, wp.status, wp.notes, wp.created_at, wp.updated_at,
    p.product_name, p.fabric_code, p.color, p.color_code,
    c.name AS purchaser_name
FROM purchaser.washing_plans wp
LEFT JOIN purchaser.products p ON wp.product_id = p.id
LEFT JOIN common.companies c ON wp.company_id = c.id;

-- purchaser-service 需要读取计划的流程步骤
CREATE OR REPLACE VIEW purchaser.v_plan_steps AS
SELECT
    ps.id, ps.plan_id, ps.step_type, ps.status,
    ps.notes, ps.operator_id, ps.company_id, ps.timestamp, ps.created_at,
    pr.display_name AS operator_name
FROM textile.plan_process_steps ps
LEFT JOIN common.profiles pr ON ps.operator_id = pr.id;

-- purchaser-service 需要读取入库记录
CREATE OR REPLACE VIEW purchaser.v_plan_inventory AS
SELECT
    ir.id, ir.plan_id, ir.warehouse_id, ir.quantity, ir.rolls,
    ir.price_per_meter, ir.price_note, ir.warehouse_location,
    ir.operator_id, ir.company_id, ir.created_at,
    w.name AS warehouse_name,
    pr.display_name AS operator_name
FROM textile.inventory_records ir
LEFT JOIN textile.warehouses w ON ir.warehouse_id = w.id
LEFT JOIN common.profiles pr ON ir.operator_id = pr.id;

-- =============================================
-- 9. 索引
-- =============================================

CREATE INDEX IF NOT EXISTS idx_profiles_username ON common.profiles(username);
CREATE INDEX IF NOT EXISTS idx_profiles_company ON common.profiles(company_id);
CREATE INDEX IF NOT EXISTS idx_company_members_company ON common.company_members(company_id);
CREATE INDEX IF NOT EXISTS idx_company_relationships_purchaser ON common.company_relationships(purchaser_company_id);
CREATE INDEX IF NOT EXISTS idx_company_relationships_factory ON common.company_relationships(factory_company_id);
CREATE INDEX IF NOT EXISTS idx_notifications_recipient ON common.notifications(recipient_id);
CREATE INDEX IF NOT EXISTS idx_share_links_code ON common.share_links(short_code);

CREATE INDEX IF NOT EXISTS idx_plans_purchaser ON purchaser.production_plans(purchaser_id);
CREATE INDEX IF NOT EXISTS idx_plans_status ON purchaser.production_plans(status);
CREATE INDEX IF NOT EXISTS idx_plan_factories_plan ON purchaser.plan_factories(plan_id);
CREATE INDEX IF NOT EXISTS idx_plan_factories_factory ON purchaser.plan_factories(factory_id);
CREATE INDEX IF NOT EXISTS idx_yarn_ratios_plan ON purchaser.yarn_ratios(plan_id);
CREATE INDEX IF NOT EXISTS idx_products_company ON purchaser.products(company_id);
CREATE INDEX IF NOT EXISTS idx_product_inventory_product ON purchaser.product_inventory_records(product_id);
CREATE INDEX IF NOT EXISTS idx_ap_purchaser ON purchaser.accounts_payable(purchaser_id);
CREATE INDEX IF NOT EXISTS idx_washing_plans_company ON purchaser.washing_plans(company_id);
CREATE INDEX IF NOT EXISTS idx_washing_plans_factory ON purchaser.washing_plans(washing_factory_id);

CREATE INDEX IF NOT EXISTS idx_process_steps_plan ON textile.plan_process_steps(plan_id);
CREATE INDEX IF NOT EXISTS idx_inventory_plan ON textile.inventory_records(plan_id);
CREATE INDEX IF NOT EXISTS idx_yarn_stock_company ON textile.yarn_stock(company_id);
CREATE INDEX IF NOT EXISTS idx_yarn_records_stock ON textile.yarn_stock_records(yarn_stock_id);
CREATE INDEX IF NOT EXISTS idx_payments_to ON textile.payments(to_company_id);

CREATE INDEX IF NOT EXISTS idx_washing_steps_plan ON washing.washing_process_steps(washing_plan_id);
CREATE INDEX IF NOT EXISTS idx_completions_plan ON washing.washing_plan_completions(washing_plan_id);
CREATE INDEX IF NOT EXISTS idx_finished_products_company ON washing.finished_products(company_id);
CREATE INDEX IF NOT EXISTS idx_fp_inventory_product ON washing.finished_product_inventory_records(finished_product_id);

-- =============================================
-- 10. 权限授予
-- =============================================

GRANT USAGE ON SCHEMA common TO iloom;
GRANT USAGE ON SCHEMA purchaser TO iloom;
GRANT USAGE ON SCHEMA textile TO iloom;
GRANT USAGE ON SCHEMA washing TO iloom;
GRANT USAGE ON SCHEMA audit TO iloom;

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA common TO iloom;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA purchaser TO iloom;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA textile TO iloom;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA washing TO iloom;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA audit TO iloom;

GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA common TO iloom;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA purchaser TO iloom;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA textile TO iloom;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA washing TO iloom;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA audit TO iloom;

ALTER DEFAULT PRIVILEGES IN SCHEMA common GRANT ALL ON TABLES TO iloom;
ALTER DEFAULT PRIVILEGES IN SCHEMA purchaser GRANT ALL ON TABLES TO iloom;
ALTER DEFAULT PRIVILEGES IN SCHEMA textile GRANT ALL ON TABLES TO iloom;
ALTER DEFAULT PRIVILEGES IN SCHEMA washing GRANT ALL ON TABLES TO iloom;
ALTER DEFAULT PRIVILEGES IN SCHEMA audit GRANT ALL ON TABLES TO iloom;