55 lines
2.6 KiB
MySQL
55 lines
2.6 KiB
MySQL
|
-- Purchase Orders table
|
||
|
|
CREATE TABLE purchase_orders (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
||
|
|
vendor_id UUID NOT NULL REFERENCES vendors(id) ON DELETE CASCADE,
|
||
|
|
po_number VARCHAR(50) NOT NULL,
|
||
|
|
transaction_date DATE NOT NULL,
|
||
|
|
due_date DATE NOT NULL,
|
||
|
|
reference VARCHAR(100),
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'sent', 'approved', 'received', 'cancelled')),
|
||
|
|
message TEXT,
|
||
|
|
total_amount DECIMAL(15,2) NOT NULL DEFAULT 0,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Purchase Order Items table
|
||
|
|
CREATE TABLE purchase_order_items (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
purchase_order_id UUID NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
|
||
|
|
ingredient_id UUID NOT NULL REFERENCES ingredients(id) ON DELETE CASCADE,
|
||
|
|
description TEXT,
|
||
|
|
quantity DECIMAL(10,3) NOT NULL,
|
||
|
|
unit_id UUID NOT NULL REFERENCES units(id) ON DELETE CASCADE,
|
||
|
|
amount DECIMAL(15,2) NOT NULL,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Purchase Order Attachments table
|
||
|
|
CREATE TABLE purchase_order_attachments (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
purchase_order_id UUID NOT NULL REFERENCES purchase_orders(id) ON DELETE CASCADE,
|
||
|
|
file_id UUID NOT NULL REFERENCES files(id) ON DELETE CASCADE,
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX idx_purchase_orders_organization_id ON purchase_orders(organization_id);
|
||
|
|
CREATE INDEX idx_purchase_orders_vendor_id ON purchase_orders(vendor_id);
|
||
|
|
CREATE INDEX idx_purchase_orders_po_number ON purchase_orders(po_number);
|
||
|
|
CREATE INDEX idx_purchase_orders_status ON purchase_orders(status);
|
||
|
|
CREATE INDEX idx_purchase_orders_transaction_date ON purchase_orders(transaction_date);
|
||
|
|
CREATE INDEX idx_purchase_orders_created_at ON purchase_orders(created_at);
|
||
|
|
|
||
|
|
CREATE INDEX idx_purchase_order_items_purchase_order_id ON purchase_order_items(purchase_order_id);
|
||
|
|
CREATE INDEX idx_purchase_order_items_ingredient_id ON purchase_order_items(ingredient_id);
|
||
|
|
CREATE INDEX idx_purchase_order_items_unit_id ON purchase_order_items(unit_id);
|
||
|
|
|
||
|
|
CREATE INDEX idx_purchase_order_attachments_purchase_order_id ON purchase_order_attachments(purchase_order_id);
|
||
|
|
CREATE INDEX idx_purchase_order_attachments_file_id ON purchase_order_attachments(file_id);
|
||
|
|
|
||
|
|
-- Unique constraint for PO number per organization
|
||
|
|
CREATE UNIQUE INDEX idx_purchase_orders_po_number_org ON purchase_orders(organization_id, po_number);
|