TÀI LIỆU ĐẶC TẢ YÊU CẦU PHẦN MỀM (SRS) ​

-- processing_orders
CREATE UNIQUE INDEX idx_processing_orders_code ON processing_orders(order_code);
CREATE INDEX idx_processing_orders_enterprise ON processing_orders(enterprise_id);
CREATE INDEX idx_processing_orders_status ON processing_orders(status);
CREATE INDEX idx_processing_orders_product ON processing_orders(product_id);
CREATE INDEX idx_processing_orders_dates ON processing_orders(planned_start_date, planned_end_date);
CREATE INDEX idx_processing_orders_search ON processing_orders USING gin(to_tsvector('simple', order_name));
CREATE INDEX idx_processing_orders_not_deleted ON processing_orders(enterprise_id) WHERE deleted_at IS NULL;

-- processing_events
CREATE INDEX idx_processing_events_order ON processing_events(order_id);
CREATE INDEX idx_processing_events_enterprise ON processing_events(enterprise_id);
CREATE INDEX idx_processing_events_type ON processing_events(event_type);
CREATE INDEX idx_processing_events_time ON processing_events(event_time);

-- processing_inputs
CREATE INDEX idx_processing_inputs_order ON processing_inputs(order_id);
CREATE INDEX idx_processing_inputs_source_lot ON processing_inputs(source_lot_id);
CREATE UNIQUE INDEX idx_processing_inputs_unique ON processing_inputs(order_id, source_lot_id, event_id);

-- processing_outputs
CREATE UNIQUE INDEX idx_processing_outputs_lot_code ON processing_outputs(output_lot_code) WHERE deleted_at IS NULL;
CREATE INDEX idx_processing_outputs_order ON processing_outputs(order_id);
CREATE INDEX idx_processing_outputs_enterprise ON processing_outputs(enterprise_id);
CREATE INDEX idx_processing_outputs_status ON processing_outputs(status);
CREATE INDEX idx_processing_outputs_expiry ON processing_outputs(expiry_date);

-- processing_event_images
CREATE INDEX idx_processing_event_images_event ON processing_event_images(event_id);

-- logistics_units
CREATE UNIQUE INDEX idx_logistics_units_sscc ON logistics_units(sscc_code);
CREATE INDEX idx_logistics_units_enterprise ON logistics_units(enterprise_id);
CREATE INDEX idx_logistics_units_output ON logistics_units(output_id);
CREATE INDEX idx_logistics_units_status ON logistics_units(status);

-- Migration: 001_create_processing_tables.sql

-- Lệnh sản xuất
CREATE TABLE processing_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    order_code VARCHAR(30) NOT NULL,
    order_name VARCHAR(200) NOT NULL,
    product_id UUID NOT NULL REFERENCES products(id),
    facility_id UUID NOT NULL REFERENCES facilities(id),
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT'
        CHECK (status IN ('DRAFT','IN_PROGRESS','PENDING_QC','COMPLETED','CANCELLED')),
    planned_start_date DATE NOT NULL,
    planned_end_date DATE NOT NULL,
    actual_start_date DATE,
    actual_end_date DATE,
    planned_quantity DECIMAL(15,3) NOT NULL CHECK (planned_quantity > 0),
    actual_quantity DECIMAL(15,3) CHECK (actual_quantity >= 0),
    unit VARCHAR(20) NOT NULL CHECK (unit IN ('kg','lit','thung','goi','chai','lon','hop')),
    default_shelf_life_days INTEGER NOT NULL CHECK (default_shelf_life_days > 0),
    notes TEXT,
    created_by UUID NOT NULL REFERENCES users(id),
    updated_by UUID REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_planned_dates CHECK (planned_end_date > planned_start_date)
);

CREATE UNIQUE INDEX idx_processing_orders_code ON processing_orders(order_code);
CREATE INDEX idx_processing_orders_enterprise ON processing_orders(enterprise_id);
CREATE INDEX idx_processing_orders_status ON processing_orders(status);

-- Sự kiện CTE
CREATE TABLE processing_events (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES processing_orders(id),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    event_type VARCHAR(20) NOT NULL
        CHECK (event_type IN ('CTE-PROC-01','CTE-PROC-02','CTE-PROC-03','CTE-PROC-04','CTE-PROC-05','CTE-PROC-06')),
    event_time TIMESTAMPTZ NOT NULL,
    performed_by_name VARCHAR(100) NOT NULL,
    location_description VARCHAR(500),
    event_data JSONB NOT NULL DEFAULT '{}',
    notes TEXT,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_processing_events_order ON processing_events(order_id);
CREATE INDEX idx_processing_events_type ON processing_events(event_type);

-- Nguyên liệu đầu vào (N:N)
CREATE TABLE processing_inputs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES processing_orders(id),
    event_id UUID NOT NULL REFERENCES processing_events(id),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    source_lot_id UUID NOT NULL,
    source_type VARCHAR(20) NOT NULL CHECK (source_type IN ('CROP_HARVEST','LIVESTOCK_OUTPUT')),
    received_quantity DECIMAL(15,3) NOT NULL CHECK (received_quantity > 0),
    received_unit VARCHAR(20) NOT NULL CHECK (received_unit IN ('kg','lit','tan','con')),
    material_condition VARCHAR(15) NOT NULL CHECK (material_condition IN ('TOT','TRUNG_BINH','KEM')),
    material_temperature DECIMAL(5,2),
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_processing_inputs_order ON processing_inputs(order_id);
CREATE INDEX idx_processing_inputs_source ON processing_inputs(source_lot_id);

-- Lô thành phẩm đầu ra
CREATE TABLE processing_outputs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES processing_orders(id),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    event_id UUID NOT NULL REFERENCES processing_events(id),
    output_lot_code VARCHAR(50) NOT NULL,
    product_id UUID NOT NULL REFERENCES products(id),
    packaged_quantity DECIMAL(15,3) NOT NULL CHECK (packaged_quantity > 0),
    packaged_unit VARCHAR(20) NOT NULL,
    packaging_spec VARCHAR(200) NOT NULL,
    unit_count INTEGER NOT NULL CHECK (unit_count > 0),
    production_date DATE NOT NULL,
    expiry_date DATE NOT NULL,
    gtin_code VARCHAR(14),
    qr_code_id UUID,
    status VARCHAR(20) NOT NULL DEFAULT 'PACKAGED'
        CHECK (status IN ('PRODUCING','PACKAGED','DISTRIBUTING','RECALLED','EXPIRED')),
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ,
    CONSTRAINT chk_expiry CHECK (expiry_date > production_date)
);

CREATE UNIQUE INDEX idx_processing_outputs_lot ON processing_outputs(output_lot_code) WHERE deleted_at IS NULL;
CREATE INDEX idx_processing_outputs_order ON processing_outputs(order_id);

-- Hình ảnh sự kiện
CREATE TABLE processing_event_images (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    event_id UUID NOT NULL REFERENCES processing_events(id) ON DELETE CASCADE,
    file_path VARCHAR(500) NOT NULL,
    file_name VARCHAR(200) NOT NULL,
    file_size INTEGER NOT NULL CHECK (file_size <= 5242880),
    mime_type VARCHAR(50) NOT NULL CHECK (mime_type IN ('image/jpeg','image/png')),
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Đơn vị logistic (SSCC)
CREATE TABLE logistics_units (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    output_id UUID NOT NULL REFERENCES processing_outputs(id),
    sscc_code VARCHAR(18) NOT NULL,
    sscc_extension CHAR(1) NOT NULL,
    gs1_company_prefix VARCHAR(10) NOT NULL,
    sscc_serial_ref VARCHAR(9) NOT NULL,
    sscc_check_digit CHAR(1) NOT NULL,
    unit_type VARCHAR(20) NOT NULL CHECK (unit_type IN ('PALLET','THUNG_CARTON','CONTAINER')),
    item_count INTEGER NOT NULL CHECK (item_count > 0),
    gtin_code VARCHAR(14),
    gs1_128_data TEXT,
    status VARCHAR(15) NOT NULL DEFAULT 'ACTIVE'
        CHECK (status IN ('ACTIVE','SHIPPED','RECEIVED')),
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_logistics_units_sscc ON logistics_units(sscc_code);
CREATE INDEX idx_logistics_units_enterprise ON logistics_units(enterprise_id);

-- Trigger auto-update updated_at
CREATE TRIGGER trg_processing_orders_updated_at
    BEFORE UPDATE ON processing_orders
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_processing_events_updated_at
    BEFORE UPDATE ON processing_events
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_processing_outputs_updated_at
    BEFORE UPDATE ON processing_outputs
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_logistics_units_updated_at
    BEFORE UPDATE ON logistics_units
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();