23 lines
975 B
MySQL
23 lines
975 B
MySQL
|
-- Migration 134: Add billing period tracking to subscriptions
|
||
|
|
-- Adds next_invoice_date and period_start fields for tracking billing cycles
|
||
|
|
|
||
|
|
ALTER TABLE sag_subscriptions
|
||
|
|
ADD COLUMN IF NOT EXISTS next_invoice_date DATE,
|
||
|
|
ADD COLUMN IF NOT EXISTS period_start DATE;
|
||
|
|
|
||
|
|
-- Set default values for existing subscriptions
|
||
|
|
UPDATE sag_subscriptions
|
||
|
|
SET
|
||
|
|
next_invoice_date = start_date + INTERVAL '1 month',
|
||
|
|
period_start = start_date
|
||
|
|
WHERE next_invoice_date IS NULL AND status = 'active';
|
||
|
|
|
||
|
|
-- Create index for efficient querying of subscriptions due for invoicing
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_sag_subscriptions_next_invoice
|
||
|
|
ON sag_subscriptions(next_invoice_date)
|
||
|
|
WHERE status = 'active';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN sag_subscriptions.next_invoice_date IS 'Next date when an invoice should be generated for this subscription';
|
||
|
|
COMMENT ON COLUMN sag_subscriptions.period_start IS 'Start date of the current billing period - shifts to next period when invoice is generated';
|
||
|
|
|