62 lines
2.2 KiB
MySQL
62 lines
2.2 KiB
MySQL
|
-- Migration 104: Sag Subscriptions
|
||
|
|
-- Sag-based subscriptions (module on cases)
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS sag_subscriptions (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
subscription_number VARCHAR(50) UNIQUE NOT NULL,
|
||
|
|
|
||
|
|
-- Ownership
|
||
|
|
sag_id INTEGER NOT NULL REFERENCES sag_sager(id) ON DELETE CASCADE,
|
||
|
|
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
|
||
|
|
|
||
|
|
-- Product and billing
|
||
|
|
product_name VARCHAR(255),
|
||
|
|
billing_interval VARCHAR(20) NOT NULL DEFAULT 'monthly' CHECK (billing_interval IN ('monthly', 'quarterly', 'yearly')),
|
||
|
|
billing_day INTEGER NOT NULL DEFAULT 1 CHECK (billing_day BETWEEN 1 AND 31),
|
||
|
|
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
|
||
|
|
|
||
|
|
-- Lifecycle
|
||
|
|
start_date DATE NOT NULL,
|
||
|
|
end_date DATE,
|
||
|
|
status VARCHAR(20) NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'active', 'paused', 'cancelled')),
|
||
|
|
|
||
|
|
-- Metadata
|
||
|
|
notes TEXT,
|
||
|
|
created_by_user_id INTEGER,
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscriptions_customer ON sag_subscriptions(customer_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscriptions_sag ON sag_subscriptions(sag_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscriptions_status ON sag_subscriptions(status);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscriptions_dates ON sag_subscriptions(start_date, end_date);
|
||
|
|
|
||
|
|
-- Auto-generate subscription_number (SUB-YYYYMMDD-XXX)
|
||
|
|
CREATE OR REPLACE FUNCTION generate_sag_subscription_number()
|
||
|
|
RETURNS TRIGGER AS $$
|
||
|
|
DECLARE
|
||
|
|
new_number VARCHAR(50);
|
||
|
|
day_count INTEGER;
|
||
|
|
BEGIN
|
||
|
|
SELECT COUNT(*) INTO day_count
|
||
|
|
FROM sag_subscriptions
|
||
|
|
WHERE DATE(created_at) = CURRENT_DATE;
|
||
|
|
|
||
|
|
new_number := 'SUB-' || TO_CHAR(CURRENT_DATE, 'YYYYMMDD') || '-' || LPAD((day_count + 1)::TEXT, 3, '0');
|
||
|
|
NEW.subscription_number := new_number;
|
||
|
|
|
||
|
|
RETURN NEW;
|
||
|
|
END;
|
||
|
|
$$ LANGUAGE plpgsql;
|
||
|
|
|
||
|
|
CREATE TRIGGER trigger_generate_sag_subscription_number
|
||
|
|
BEFORE INSERT ON sag_subscriptions
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION generate_sag_subscription_number();
|
||
|
|
|
||
|
|
CREATE TRIGGER trigger_sag_subscriptions_updated_at
|
||
|
|
BEFORE UPDATE ON sag_subscriptions
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_updated_at_column();
|