CREATE OR REPLACE FUNCTION public.grant_subscription_access()
RETURNS TRIGGER AS $$
DECLARE
v_subject_type TEXT;
v_subject_id TEXT;
BEGIN
IF NEW.provider = 'stripe'
AND NEW.event_type = 'invoice.paid'
AND NEW.processing_status = 'processed' THEN
v_subject_type := COALESCE(
NEW.payload -> 'data' -> 'object' -> 'parent'
-> 'subscription_details' -> 'metadata' ->> 'insforge_subject_type',
NEW.payload -> 'data' -> 'object' -> 'metadata' ->> 'insforge_subject_type'
);
v_subject_id := COALESCE(
NEW.payload -> 'data' -> 'object' -> 'parent'
-> 'subscription_details' -> 'metadata' ->> 'insforge_subject_id',
NEW.payload -> 'data' -> 'object' -> 'metadata' ->> 'insforge_subject_id'
);
IF v_subject_id IS NULL THEN
SELECT m.subject_type, m.subject_id
INTO v_subject_type, v_subject_id
FROM payments.customer_mappings m
WHERE m.provider = NEW.provider
AND m.environment = NEW.environment
AND m.provider_customer_id = NEW.payload -> 'data' -> 'object' ->> 'customer';
END IF;
IF v_subject_id IS NULL THEN
RAISE WARNING 'Stripe event % has no resolvable billing subject', NEW.provider_event_id;
RETURN NEW;
END IF;
-- 在 checkout 處傳送的主體類型上分支；team_id 是這裡的 UUID，
-- 所以類型檢查也保護轉換。
IF v_subject_type = 'team' THEN
INSERT INTO public.team_entitlements (team_id, plan, active, updated_at)
VALUES (v_subject_id::uuid, 'pro', true, NOW())
ON CONFLICT (team_id) DO UPDATE SET
plan = EXCLUDED.plan,
active = true,
updated_at = NOW();
END IF;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER grant_subscription_access_from_stripe_webhook
AFTER INSERT OR UPDATE ON payments.webhook_events
FOR EACH ROW
EXECUTE FUNCTION public.grant_subscription_access();
