26 lines
1013 B
MySQL
26 lines
1013 B
MySQL
|
-- Migration 105: Sag Subscription Items
|
||
|
|
-- Line items for sag-based subscriptions
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sag_subscription_items (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
subscription_id INTEGER NOT NULL REFERENCES sag_subscriptions(id) ON DELETE CASCADE,
|
||
|
|
line_no INTEGER NOT NULL DEFAULT 1,
|
||
|
|
description VARCHAR(255) NOT NULL,
|
||
|
|
quantity DECIMAL(10,2) NOT NULL CHECK (quantity > 0),
|
||
|
|
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
|
||
|
|
line_total DECIMAL(12,2) NOT NULL CHECK (line_total >= 0),
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscription_items_subscription
|
||
|
|
ON sag_subscription_items(subscription_id);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscription_items_line_no
|
||
|
|
ON sag_subscription_items(subscription_id, line_no);
|
||
|
|
|
||
|
|
CREATE TRIGGER trigger_sag_subscription_items_updated_at
|
||
|
|
BEFORE UPDATE ON sag_subscription_items
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_updated_at_column();
|