66 lines
2.7 KiB
MySQL
66 lines
2.7 KiB
MySQL
|
-- Migration 102: Fixed-Price Billing Periods
|
||
|
|
-- Tracks monthly usage and billing status per agreement
|
||
|
|
-- Structure compatible with future subscription_billing_periods table
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS fixed_price_billing_periods (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
agreement_id INTEGER NOT NULL REFERENCES customer_fixed_price_agreements(id) ON DELETE CASCADE,
|
||
|
|
|
||
|
|
-- Period definition (compatible with future subscription_billing_periods)
|
||
|
|
period_start DATE NOT NULL,
|
||
|
|
period_end DATE NOT NULL,
|
||
|
|
period_type VARCHAR(20) DEFAULT 'calendar_month',
|
||
|
|
|
||
|
|
-- Usage tracking
|
||
|
|
included_hours DECIMAL(8,2) NOT NULL,
|
||
|
|
used_hours DECIMAL(8,2) DEFAULT 0,
|
||
|
|
overtime_hours DECIMAL(8,2) GENERATED ALWAYS AS (
|
||
|
|
CASE WHEN used_hours > included_hours THEN used_hours - included_hours ELSE 0 END
|
||
|
|
) STORED,
|
||
|
|
remaining_hours DECIMAL(8,2) GENERATED ALWAYS AS (
|
||
|
|
CASE WHEN used_hours < included_hours THEN included_hours - used_hours ELSE 0 END
|
||
|
|
) STORED,
|
||
|
|
|
||
|
|
-- Billing amounts (v2: integrate with subscription_line_items)
|
||
|
|
base_amount DECIMAL(12,2) NOT NULL,
|
||
|
|
overtime_amount DECIMAL(12,2) DEFAULT 0,
|
||
|
|
overtime_approved BOOLEAN DEFAULT false,
|
||
|
|
|
||
|
|
-- Billing status (compatible with subscription billing workflow)
|
||
|
|
status VARCHAR(20) DEFAULT 'active' CHECK (status IN (
|
||
|
|
'active', -- Currently accumulating usage
|
||
|
|
'pending_approval', -- Overtime needs approval
|
||
|
|
'ready_to_bill', -- Approved, ready for invoice generation
|
||
|
|
'billed', -- Invoice created
|
||
|
|
'cancelled' -- Period cancelled (e.g. early termination)
|
||
|
|
)),
|
||
|
|
|
||
|
|
-- Invoice tracking
|
||
|
|
billed_at TIMESTAMP,
|
||
|
|
economic_invoice_number VARCHAR(50),
|
||
|
|
invoice_id INTEGER,
|
||
|
|
|
||
|
|
-- Metadata
|
||
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
|
|
|
||
|
|
UNIQUE(agreement_id, period_start)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_fpbp_agreement ON fixed_price_billing_periods(agreement_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_fpbp_period ON fixed_price_billing_periods(period_start, period_end);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_fpbp_status ON fixed_price_billing_periods(status);
|
||
|
|
|
||
|
|
-- Update timestamp trigger
|
||
|
|
CREATE TRIGGER trigger_fpbp_updated_at
|
||
|
|
BEFORE UPDATE ON fixed_price_billing_periods
|
||
|
|
FOR EACH ROW
|
||
|
|
EXECUTE FUNCTION update_updated_at_column();
|
||
|
|
|
||
|
|
COMMENT ON TABLE fixed_price_billing_periods IS
|
||
|
|
'Billing periods for fixed-price agreements. Structure compatible with future unified subscription_billing_periods table.';
|
||
|
|
|
||
|
|
COMMENT ON COLUMN fixed_price_billing_periods.overtime_approved IS
|
||
|
|
'Overtime must be approved before billing. Periods with unapproved overtime → status=pending_approval';
|