-- Create agency_subscriptions table
CREATE TABLE IF NOT EXISTS agency_subscriptions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agency_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    plan_id UUID NOT NULL REFERENCES plans(id) ON DELETE RESTRICT,
    billing_type VARCHAR(20) NOT NULL DEFAULT 'monthly', -- monthly or annual
    current_users INTEGER NOT NULL DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'active', -- active, suspended, cancelled
    start_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    renewal_date TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(agency_id) -- One active subscription per agency
);

-- Add indexes
CREATE INDEX idx_agency_subscriptions_agency_id ON agency_subscriptions(agency_id);
CREATE INDEX idx_agency_subscriptions_plan_id ON agency_subscriptions(plan_id);
CREATE INDEX idx_agency_subscriptions_status ON agency_subscriptions(status);

-- Add comments
COMMENT ON TABLE agency_subscriptions IS 'Tracks agency subscription to plans';
COMMENT ON COLUMN agency_subscriptions.billing_type IS 'Monthly or annual billing';
COMMENT ON COLUMN agency_subscriptions.current_users IS 'Current count of users (collaborators + clients)';