366 lines
12 KiB
SQL
366 lines
12 KiB
SQL
CREATE TABLE roles (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE users (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
role_id BIGINT NOT NULL REFERENCES roles(id),
|
|
name VARCHAR(150) NOT NULL,
|
|
email VARCHAR(150),
|
|
phone VARCHAR(50),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE suppliers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(150) NOT NULL,
|
|
phone VARCHAR(50),
|
|
email VARCHAR(150),
|
|
bank_name VARCHAR(100),
|
|
bank_account_number VARCHAR(100),
|
|
address TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE customers (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(150) NOT NULL,
|
|
phone VARCHAR(50),
|
|
email VARCHAR(150),
|
|
bank_name VARCHAR(100),
|
|
bank_account_number VARCHAR(100),
|
|
address TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE units (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(20) NOT NULL UNIQUE,
|
|
name VARCHAR(50) NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE item_types (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE item_grades (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
rank_order INT,
|
|
description TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE warehouses (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
address TEXT,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE warehouse_locations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
warehouse_id BIGINT NOT NULL REFERENCES warehouses(id),
|
|
code VARCHAR(50) NOT NULL,
|
|
name VARCHAR(100) NOT NULL,
|
|
location_type VARCHAR(50),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
UNIQUE (warehouse_id, code)
|
|
);
|
|
|
|
CREATE TABLE adjustment_reasons (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
code VARCHAR(50) NOT NULL UNIQUE,
|
|
name VARCHAR(100) NOT NULL,
|
|
category VARCHAR(50) NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE purchases (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
purchase_no VARCHAR(50) NOT NULL UNIQUE,
|
|
supplier_id BIGINT NOT NULL REFERENCES suppliers(id),
|
|
purchase_date DATE NOT NULL,
|
|
supplier_invoice_no VARCHAR(100),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
|
notes TEXT,
|
|
created_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE purchase_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
purchase_id BIGINT NOT NULL REFERENCES purchases(id) ON DELETE CASCADE,
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT REFERENCES item_grades(id),
|
|
qty_ordered NUMERIC(18,3) NOT NULL,
|
|
unit_id BIGINT NOT NULL REFERENCES units(id),
|
|
unit_price NUMERIC(18,2) NOT NULL,
|
|
subtotal NUMERIC(18,2) NOT NULL,
|
|
classification_status VARCHAR(20) NOT NULL DEFAULT 'FINAL',
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE receipts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
receipt_no VARCHAR(50) NOT NULL UNIQUE,
|
|
purchase_id BIGINT NOT NULL REFERENCES purchases(id),
|
|
supplier_id BIGINT NOT NULL REFERENCES suppliers(id),
|
|
receipt_date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
|
notes TEXT,
|
|
received_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE receipt_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
receipt_id BIGINT NOT NULL REFERENCES receipts(id) ON DELETE CASCADE,
|
|
purchase_line_id BIGINT NOT NULL REFERENCES purchase_lines(id),
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT REFERENCES item_grades(id),
|
|
qty_received NUMERIC(18,3) NOT NULL,
|
|
qty_accepted NUMERIC(18,3) NOT NULL,
|
|
qty_rejected NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
unit_id BIGINT NOT NULL REFERENCES units(id),
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE inventory_lots (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
lot_code VARCHAR(100) NOT NULL UNIQUE,
|
|
parent_lot_id BIGINT REFERENCES inventory_lots(id),
|
|
source_type VARCHAR(30) NOT NULL,
|
|
source_ref_id BIGINT,
|
|
supplier_id BIGINT REFERENCES suppliers(id),
|
|
purchase_id BIGINT REFERENCES purchases(id),
|
|
purchase_line_id BIGINT REFERENCES purchase_lines(id),
|
|
receipt_id BIGINT REFERENCES receipts(id),
|
|
receipt_line_id BIGINT REFERENCES receipt_lines(id),
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT NOT NULL REFERENCES item_grades(id),
|
|
warehouse_id BIGINT NOT NULL REFERENCES warehouses(id),
|
|
warehouse_location_id BIGINT REFERENCES warehouse_locations(id),
|
|
original_qty NUMERIC(18,3) NOT NULL,
|
|
available_qty NUMERIC(18,3) NOT NULL,
|
|
reserved_qty NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
damaged_qty NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
shrinkage_qty NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
unit_id BIGINT NOT NULL REFERENCES units(id),
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
received_at TIMESTAMP NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
qr_code_value VARCHAR(255),
|
|
barcode_value VARCHAR(255),
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX idx_inventory_lots_item_grade_wh ON inventory_lots(item_type_id, item_grade_id, warehouse_id);
|
|
CREATE INDEX idx_inventory_lots_supplier_item_grade ON inventory_lots(supplier_id, item_type_id, item_grade_id);
|
|
|
|
CREATE TABLE sorting_sessions (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sorting_no VARCHAR(50) NOT NULL UNIQUE,
|
|
source_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
sorting_date TIMESTAMP NOT NULL,
|
|
input_qty NUMERIC(18,3) NOT NULL,
|
|
output_qty NUMERIC(18,3) NOT NULL,
|
|
shrinkage_qty NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
notes TEXT,
|
|
sorted_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sorting_results (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sorting_session_id BIGINT NOT NULL REFERENCES sorting_sessions(id) ON DELETE CASCADE,
|
|
result_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT NOT NULL REFERENCES item_grades(id),
|
|
qty_result NUMERIC(18,3) NOT NULL,
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sales (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sales_no VARCHAR(50) NOT NULL UNIQUE,
|
|
customer_id BIGINT NOT NULL REFERENCES customers(id),
|
|
sales_date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
|
notes TEXT,
|
|
created_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sales_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sales_id BIGINT NOT NULL REFERENCES sales(id) ON DELETE CASCADE,
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT NOT NULL REFERENCES item_grades(id),
|
|
qty_sold NUMERIC(18,3) NOT NULL,
|
|
unit_id BIGINT NOT NULL REFERENCES units(id),
|
|
selling_price NUMERIC(18,2) NOT NULL,
|
|
subtotal NUMERIC(18,2) NOT NULL,
|
|
costing_total NUMERIC(18,2) NOT NULL DEFAULT 0,
|
|
gross_margin NUMERIC(18,2) NOT NULL DEFAULT 0,
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sales_allocations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sales_line_id BIGINT NOT NULL REFERENCES sales_lines(id) ON DELETE CASCADE,
|
|
inventory_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
qty_allocated NUMERIC(18,3) NOT NULL,
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
total_cost NUMERIC(18,2) NOT NULL,
|
|
allocated_at TIMESTAMP NOT NULL,
|
|
allocated_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE inventory_movements (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
movement_no VARCHAR(50) NOT NULL UNIQUE,
|
|
movement_type VARCHAR(30) NOT NULL,
|
|
inventory_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
related_lot_id BIGINT REFERENCES inventory_lots(id),
|
|
warehouse_id BIGINT NOT NULL REFERENCES warehouses(id),
|
|
warehouse_location_id BIGINT REFERENCES warehouse_locations(id),
|
|
qty_in NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
qty_out NUMERIC(18,3) NOT NULL DEFAULT 0,
|
|
balance_after NUMERIC(18,3) NOT NULL,
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
reference_type VARCHAR(30) NOT NULL,
|
|
reference_id BIGINT NOT NULL,
|
|
reason_id BIGINT REFERENCES adjustment_reasons(id),
|
|
movement_date TIMESTAMP NOT NULL,
|
|
created_by BIGINT NOT NULL REFERENCES users(id),
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sales_returns (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sales_id BIGINT NOT NULL REFERENCES sales(id),
|
|
customer_id BIGINT NOT NULL REFERENCES customers(id),
|
|
return_date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE sales_return_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
sales_return_id BIGINT NOT NULL REFERENCES sales_returns(id) ON DELETE CASCADE,
|
|
sales_line_id BIGINT NOT NULL REFERENCES sales_lines(id),
|
|
inventory_lot_id BIGINT REFERENCES inventory_lots(id),
|
|
item_type_id BIGINT NOT NULL REFERENCES item_types(id),
|
|
item_grade_id BIGINT NOT NULL REFERENCES item_grades(id),
|
|
qty_returned NUMERIC(18,3) NOT NULL,
|
|
return_condition VARCHAR(50),
|
|
resolution VARCHAR(50),
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE purchase_returns (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
purchase_id BIGINT NOT NULL REFERENCES purchases(id),
|
|
supplier_id BIGINT NOT NULL REFERENCES suppliers(id),
|
|
return_date DATE NOT NULL,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'DRAFT',
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE purchase_return_lines (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
purchase_return_id BIGINT NOT NULL REFERENCES purchase_returns(id) ON DELETE CASCADE,
|
|
inventory_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
qty_returned NUMERIC(18,3) NOT NULL,
|
|
unit_cost NUMERIC(18,2) NOT NULL,
|
|
notes TEXT,
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE stock_adjustments (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
adjustment_no VARCHAR(50) NOT NULL UNIQUE,
|
|
inventory_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id),
|
|
adjustment_type VARCHAR(30) NOT NULL,
|
|
reason_id BIGINT NOT NULL REFERENCES adjustment_reasons(id),
|
|
qty_before NUMERIC(18,3) NOT NULL,
|
|
qty_change NUMERIC(18,3) NOT NULL,
|
|
qty_after NUMERIC(18,3) NOT NULL,
|
|
cost_impact NUMERIC(18,2) NOT NULL DEFAULT 0,
|
|
adjustment_date TIMESTAMP NOT NULL,
|
|
notes TEXT,
|
|
created_by BIGINT NOT NULL REFERENCES users(id),
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE lot_labels (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
inventory_lot_id BIGINT NOT NULL REFERENCES inventory_lots(id) ON DELETE CASCADE,
|
|
label_type VARCHAR(20) NOT NULL,
|
|
label_value VARCHAR(255) NOT NULL,
|
|
printed_at TIMESTAMP,
|
|
printed_by BIGINT REFERENCES users(id),
|
|
print_count INT NOT NULL DEFAULT 0,
|
|
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
|
); |