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

CREATE UNIQUE INDEX idx_batches_code ON batches(batch_code);
CREATE INDEX idx_batches_enterprise ON batches(enterprise_id);
CREATE INDEX idx_batches_product ON batches(product_id);
CREATE INDEX idx_batches_status ON batches(status);
CREATE INDEX idx_batches_type ON batches(batch_type);
CREATE INDEX idx_batches_expiry ON batches(expiry_date) WHERE expiry_date IS NOT NULL;
CREATE INDEX idx_batches_code_search ON batches USING gin(to_tsvector('simple', batch_code));

-- Migration: 001_create_batches_tables.sql

CREATE TABLE production_plans (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    plan_code VARCHAR(30) NOT NULL UNIQUE,
    plan_name VARCHAR(200) NOT NULL,
    product_id UUID NOT NULL REFERENCES products(id),
    target_quantity DECIMAL(15,3) NOT NULL CHECK (target_quantity > 0),
    allocated_quantity DECIMAL(15,3) NOT NULL DEFAULT 0 CHECK (allocated_quantity >= 0),
    unit VARCHAR(20) NOT NULL,
    start_date DATE NOT NULL,
    end_date DATE NOT NULL,
    planned_batch_count INTEGER CHECK (planned_batch_count IS NULL OR planned_batch_count > 0),
    status VARCHAR(20) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT','ACTIVE','COMPLETED','CANCELLED')),
    notes TEXT,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT chk_plan_dates CHECK (end_date > start_date)
);

CREATE TABLE batches (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    enterprise_id UUID NOT NULL REFERENCES enterprises(id),
    batch_code VARCHAR(50) NOT NULL UNIQUE,
    batch_type VARCHAR(20) NOT NULL CHECK (batch_type IN ('CROP','LIVESTOCK','AQUACULTURE','PROCESSED')),
    product_id UUID NOT NULL REFERENCES products(id),
    production_plan_id UUID REFERENCES production_plans(id),
    production_area_id UUID NOT NULL REFERENCES production_areas(id),
    planned_quantity DECIMAL(15,3) NOT NULL CHECK (planned_quantity > 0),
    actual_quantity DECIMAL(15,3) DEFAULT 0 CHECK (actual_quantity >= 0),
    stock_quantity DECIMAL(15,3) NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    unit VARCHAR(20) NOT NULL,
    production_start_date DATE NOT NULL,
    production_end_date DATE,
    manufacture_date DATE,
    expiry_date DATE,
    storage_conditions VARCHAR(300),
    applied_standards JSONB DEFAULT '[]',
    status VARCHAR(20) NOT NULL DEFAULT 'PLANNED' CHECK (status IN ('PLANNED','IN_PRODUCTION','PRODUCED','PACKAGED','DISTRIBUTING','DISTRIBUTED','DELIVERED','COMBO_ASSEMBLED','DEPLETED','RECALLED','EXPIRED','DESTROYED','CANCELLED')), -- v1.2: thêm DESTROYED
    recall_reason TEXT,
    recall_severity VARCHAR(10) CHECK (recall_severity IS NULL OR recall_severity IN ('LOW','MEDIUM','HIGH','CRITICAL')),
    recalled_at TIMESTAMPTZ,
    destroyed_quantity DECIMAL(15,3) NOT NULL DEFAULT 0 CHECK (destroyed_quantity >= 0), -- v1.2
    destroyed_at TIMESTAMPTZ, -- v1.2
    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_batch_dates CHECK (production_end_date IS NULL OR production_end_date >= production_start_date),
    CONSTRAINT chk_expiry CHECK (expiry_date IS NULL OR manufacture_date IS NULL OR expiry_date > manufacture_date)
);

CREATE INDEX idx_batches_enterprise ON batches(enterprise_id);
CREATE INDEX idx_batches_status ON batches(status);
CREATE INDEX idx_batches_product ON batches(product_id);
CREATE INDEX idx_batches_expiry ON batches(expiry_date) WHERE expiry_date IS NOT NULL;

CREATE TABLE batch_crop_ext (
    batch_id UUID PRIMARY KEY REFERENCES batches(id) ON DELETE CASCADE,
    crop_variety VARCHAR(200),
    crop_season VARCHAR(100),
    area_hectares DECIMAL(10,4) CHECK (area_hectares IS NULL OR area_hectares > 0)
);

CREATE TABLE batch_livestock_ext (
    batch_id UUID PRIMARY KEY REFERENCES batches(id) ON DELETE CASCADE,
    species VARCHAR(50) NOT NULL,
    breed VARCHAR(200),
    animal_count INTEGER CHECK (animal_count IS NULL OR animal_count > 0)
);

CREATE TABLE batch_aquaculture_ext (
    batch_id UUID PRIMARY KEY REFERENCES batches(id) ON DELETE CASCADE,
    aqua_species VARCHAR(50) NOT NULL,
    pond_id UUID,
    stocking_density DECIMAL(10,2) CHECK (stocking_density IS NULL OR stocking_density > 0)
);

CREATE TABLE batch_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    batch_id UUID NOT NULL REFERENCES batches(id),
    action VARCHAR(20) NOT NULL CHECK (action IN ('CREATE','UPDATE','STATUS_CHANGE','RECALL','CANCEL')),
    changed_by UUID NOT NULL REFERENCES users(id),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    changes_json JSONB,
    ip_address INET
);

CREATE INDEX idx_batch_history_batch ON batch_history(batch_id);

CREATE TRIGGER trg_batches_updated_at
    BEFORE UPDATE ON batches
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

CREATE TRIGGER trg_plans_updated_at
    BEFORE UPDATE ON production_plans
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();