database: 2 nvl vues pour simplifier les requêtes & abstraction billing

[syndr0m]

CREATE MATERIALIZED VIEW "Vue_SubscribedUsers" AS 
SELECT 
BS._id as "billingLastSubscriptionId",
BU._id as "billingUserId",
BU.user_reference_uuid as "backoUserId",
BUO.value as "billingUserEmail"
FROM (
    -- derniere souscription par utilisateur
    SELECT user_reference_uuid, max(SUBBS._id) as _id
    FROM "Vue_billing_users" SUBBU
    INNER JOIN "Vue_billing_subscriptions" SUBBS ON SUBBS.userid = SUBBU._id
    WHERE
        SUBBS.deleted = false AND SUBBU.deleted = false AND
        SUBBU.platformid = 1
    GROUP BY user_reference_uuid
) as "lastSub"
INNER JOIN "Vue_billing_subscriptions" BS ON "lastSub"._id = BS."_id"
INNER JOIN "Vue_billing_users" BU ON BS.userid = BU._id
INNER JOIN "Vue_billing_users_opts" BUO ON (BUO.userid = BU._id AND BUO.key = 'email' AND BUO.deleted = 'no')
WHERE 
    BS.deleted = false AND BU.deleted = false
AND (BS.sub_expires_date is null OR BS.sub_expires_date > CURRENT_TIMESTAMP)
AND (BUO.value NOT LIKE '%yopmail.com' OR BUO.value IS NULL)
AND length(BUO.value) > 0 AND BU.platformid = 1
ORDER BY BUO.value ASC

CREATE MATERIALIZED VIEW "Vue_UnsubscribedUsers" AS 
 SELECT bs._id AS "billingLastSubscriptionId",
    bu._id AS "billingUserId",
    bu.user_reference_uuid AS "backoUserId",
    buo.value as "billingUserEmail"
   FROM ( SELECT subbu.user_reference_uuid,
            max(subbs._id) AS _id
           FROM "Vue_billing_users" subbu
             JOIN "Vue_billing_subscriptions" subbs ON subbs.userid = subbu._id
          WHERE subbs.deleted = false AND subbu.deleted = false AND subbu.platformid = 1
          GROUP BY subbu.user_reference_uuid) "lastSub"
     JOIN "Vue_billing_subscriptions" bs ON "lastSub"._id = bs._id
     JOIN "Vue_billing_users" bu ON bs.userid = bu._id
     JOIN "Vue_billing_users_opts" buo ON buo.userid = bu._id AND buo.key::text = 'email'::text AND buo.deleted = false
  WHERE bs.deleted = false AND bu.deleted = false AND bs.sub_expires_date < now() AND length(buo.value::text) > 0 AND bu.platformid = 1
  AND (BUO.value NOT LIKE '%yopmail.com' OR BUO.value IS NULL)
  ORDER BY buo.value