30 lines
1.6 KiB
MySQL
30 lines
1.6 KiB
MySQL
|
-- Orders table
|
||
|
|
CREATE TABLE orders (
|
||
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
|
|
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
|
||
|
|
outlet_id UUID NOT NULL REFERENCES outlets(id) ON DELETE CASCADE,
|
||
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
|
||
|
|
customer_id UUID,
|
||
|
|
order_number VARCHAR(50) UNIQUE NOT NULL,
|
||
|
|
table_number VARCHAR(20),
|
||
|
|
order_type VARCHAR(50) NOT NULL CHECK (order_type IN ('dine_in', 'takeout', 'delivery')),
|
||
|
|
status VARCHAR(50) DEFAULT 'pending' CHECK (status IN ('pending', 'preparing', 'ready', 'completed', 'cancelled')),
|
||
|
|
subtotal DECIMAL(10,2) NOT NULL CHECK (subtotal >= 0),
|
||
|
|
tax_amount DECIMAL(10,2) NOT NULL CHECK (tax_amount >= 0),
|
||
|
|
discount_amount DECIMAL(10,2) DEFAULT 0.00 CHECK (discount_amount >= 0),
|
||
|
|
total_amount DECIMAL(10,2) NOT NULL CHECK (total_amount >= 0),
|
||
|
|
payment_status VARCHAR(50) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'completed', 'failed', 'refunded')),
|
||
|
|
metadata JSONB DEFAULT '{}',
|
||
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX idx_orders_organization_id ON orders(organization_id);
|
||
|
|
CREATE INDEX idx_orders_outlet_id ON orders(outlet_id);
|
||
|
|
CREATE INDEX idx_orders_user_id ON orders(user_id);
|
||
|
|
CREATE INDEX idx_orders_order_number ON orders(order_number);
|
||
|
|
CREATE INDEX idx_orders_order_type ON orders(order_type);
|
||
|
|
CREATE INDEX idx_orders_status ON orders(status);
|
||
|
|
CREATE INDEX idx_orders_payment_status ON orders(payment_status);
|
||
|
|
CREATE INDEX idx_orders_created_at ON orders(created_at);
|