-- Seed 6 missing subscription skills (declared in subscriptions-module.ts but absent from agent_skills)
INSERT INTO public.agent_skills (name, description, category, scope, handler, tool_definition, enabled, mcp_exposed)
VALUES
('upcoming_renewals',
 'List subscriptions renewing within N days. Use when: planning outreach, weekly briefing on renewals, identifying win-back candidates with cancel_at_period_end. NOT for: aggregate MRR (subscription_mrr) or risk flagging (flag_at_risk_subscriptions).',
 'commerce','internal','rpc:upcoming_renewals',
 '{"type":"function","function":{"name":"upcoming_renewals","description":"Subscriptions renewing within p_days_ahead days (default 7).","parameters":{"type":"object","properties":{"p_days_ahead":{"type":"number","description":"Window in days (default 7, max 90)"}}}}}'::jsonb,
 true, true),
('record_churn_reason',
 'Record why a customer churned (reason category + free-text feedback + NPS). Use when: customer cancels via portal, exit survey returned. NOT for: technical cancellation (use Stripe customer-portal flow).',
 'commerce','internal','rpc:record_churn_reason',
 '{"type":"function","function":{"name":"record_churn_reason","description":"Stores a structured churn reason for a subscription.","parameters":{"type":"object","required":["p_subscription_id","p_reason"],"properties":{"p_subscription_id":{"type":"string","format":"uuid"},"p_reason":{"type":"string","enum":["too_expensive","missing_feature","switched_competitor","no_longer_needed","poor_support","technical_issues","temporary_pause","other"]},"p_feedback":{"type":"string"},"p_nps_score":{"type":"number","minimum":0,"maximum":10},"p_would_return":{"type":"boolean"}}}}}'::jsonb,
 true, true),
('list_winback_campaigns',
 'List configured win-back campaigns (active or all). Use when: choosing which offer to send, auditing win-back program. NOT for: sending the campaign (send_winback) or campaign creation (create via /admin/subscriptions UI).',
 'commerce','internal','edge:agent-execute',
 '{"type":"function","function":{"name":"list_winback_campaigns","description":"Lists subscription_winback_campaigns rows.","parameters":{"type":"object","properties":{"active_only":{"type":"boolean","description":"Only return active=true campaigns"},"limit":{"type":"number"}}}}}'::jsonb,
 true, true),
('create_manual_subscription',
 'Create a recurring subscription billed by invoice (not via Stripe card). Use when: B2B customer signs a service plan paid by invoice (telecom plans, retainers, hosted services). NOT for: online card checkout (use Stripe checkout flow instead).',
 'commerce','internal','rpc:create_manual_subscription',
 '{"type":"function","function":{"name":"create_manual_subscription","description":"Register a recurring subscription billed by invoice. Generates first invoice on start_date via daily cron.","parameters":{"type":"object","required":["customer_email","product_name","unit_amount_cents"],"properties":{"customer_email":{"type":"string"},"customer_name":{"type":"string"},"product_name":{"type":"string"},"unit_amount_cents":{"type":"integer"},"currency":{"type":"string"},"billing_interval":{"type":"string","enum":["day","week","month","year"]},"billing_interval_count":{"type":"integer"},"quantity":{"type":"integer"},"payment_terms":{"type":"string","enum":["invoice_30","invoice_14","invoice_7","direct_debit","manual","prepaid_card"]},"start_date":{"type":"string"},"billing_contact_email":{"type":"string"},"po_number":{"type":"string"},"product_id":{"type":"string"},"auto_finalize":{"type":"boolean"}}}}}'::jsonb,
 true, true),
('generate_subscription_invoice',
 'Force-generate the next invoice for a manual subscription. Use when: ad-hoc billing run, customer requested immediate invoice, testing. NOT for: stripe-billed subscriptions (Stripe handles those). Normally the daily cron handles this automatically.',
 'commerce','internal','rpc:generate_subscription_invoice',
 '{"type":"function","function":{"name":"generate_subscription_invoice","description":"Create a draft invoice from a manual subscription and advance next_invoice_date.","parameters":{"type":"object","required":["subscription_id"],"properties":{"subscription_id":{"type":"string"},"tax_rate":{"type":"number"},"due_in_days":{"type":"integer"}}}}}'::jsonb,
 true, true),
('cancel_manual_subscription',
 'Cancel a manual (invoice-billed) subscription. Use when: customer terminates B2B plan, account closed. NOT for: Stripe subscriptions (use Stripe customer portal or cancel_subscription).',
 'commerce','internal','rpc:cancel_manual_subscription',
 '{"type":"function","function":{"name":"cancel_manual_subscription","description":"Cancel an invoice-driven subscription. Stops further invoicing.","parameters":{"type":"object","required":["subscription_id"],"properties":{"subscription_id":{"type":"string"},"reason":{"type":"string"},"effective_date":{"type":"string"}}}}}'::jsonb,
 true, true)
ON CONFLICT (name) DO UPDATE
  SET description = EXCLUDED.description,
      category = EXCLUDED.category,
      scope = EXCLUDED.scope,
      handler = EXCLUDED.handler,
      tool_definition = EXCLUDED.tool_definition,
      enabled = true,
      mcp_exposed = true,
      updated_at = now();