glints
.public
Tables
(current)
Columns
Constraints
Relationships
Orphan Tables
Anomalies
Routines
delete_deletable_company_data_in_monetisation_tables(IN company_ids_to_delete uuid[])
Parameters
Name
Type
Mode
company_ids_to_delete
uuid[]
IN
Definition
DECLARE companies_with_payments TEXT[]; companies_with_journal_entries_from_sales TEXT[]; companies_with_active_paid_job_slots_subscription TEXT[]; points_accounts_table_list TEXT[]; subscription_related_table_list TEXT[]; journal_entry_related_table_list TEXT[]; table_name TEXT; points_account_ids_to_delete uuid[]; BEGIN --- check if safe to delete companies_with_payments := ( select ARRAY_AGG(distinct company_id) from ( SELECT orders.id, COALESCE(orders."companyId", user_companies."CompanyId") as company_id FROM employer_commerce."PointsTopUpOrderPayments" as payments inner join employer_commerce."PointsTopUpOrders" as orders on payments."pointsTopUpOrderId" = orders.id left join "UserCompanies" as user_companies on user_companies.id = orders."userCompanyId" where orders.status != 'EXPIRED' and (user_companies."CompanyId" = ANY(company_ids_to_delete) or orders."companyId" = ANY(company_ids_to_delete)) ) as companies_data_with_payments ); IF cardinality(companies_with_payments) >= 1 THEN RAISE EXCEPTION 'These ids % given as input have payment related data so might be unsafe to delete.', companies_with_payments; END IF; companies_with_journal_entries_from_sales := ( SELECT ARRAY_AGG("companyId") FROM employer_commerce."PointsJournalEntries" WHERE "companyId" = ANY(company_ids_to_delete) AND type IN ('SALES_ADHOC_POINTS', 'SALES_SYSTEM_POINTS', 'PLATFORM_FEE') ); IF cardinality(companies_with_journal_entries_from_sales) >= 1 THEN RAISE EXCEPTION 'Unable to delete these ids % because they have record related to sales system.', companies_with_journal_entries_from_sales; END IF; companies_with_active_paid_job_slots_subscription := ( SELECT ARRAY_AGG("companyId") FROM employer_commerce."CompanyJobSlotsSubscriptions" WHERE "companyId" = ANY(company_ids_to_delete) AND status = 'ACTIVE' AND ("planType" != 'FREE' or price > 0) ); IF cardinality(companies_with_active_paid_job_slots_subscription) >= 1 THEN RAISE EXCEPTION 'Unable to delete these ids % because they have active paid job slots subscription record.', companies_with_active_paid_job_slots_subscription; END IF; --- Delete data for subscription related tables ALTER TABLE employer_commerce."SubscriptionTalentChatQuotas" DISABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."SubscriptionCsvDownloads" DISABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."SubscriptionCsvDownloads" DISABLE TRIGGER update_not_allowed; ALTER TABLE employer_commerce."TalentChatAllocations" DISABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."TalentChatAllocations" DISABLE TRIGGER update_not_allowed; subscription_related_table_list := ARRAY['SubscriptionTalentChatQuotas', 'SubscriptionCsvDownloads', 'TalentChatAllocations']; FOR i IN 1..array_length(subscription_related_table_list, 1) LOOP table_name := subscription_related_table_list[i]; EXECUTE format(' DELETE FROM employer_commerce.%I where "companyJobSlotsSubscriptionId" in ( select "id" from employer_commerce."CompanyJobSlotsSubscriptions" where "companyId" = ANY(''%s'') ); ', table_name, company_ids_to_delete); END LOOP; ALTER TABLE employer_commerce."CompanyJobSlotsSubscriptions" DISABLE TRIGGER delete_not_allowed; DELETE FROM employer_commerce."CompanyJobSlotsSubscriptions" where "companyId" = ANY(company_ids_to_delete); ALTER TABLE employer_commerce."SubscriptionTalentChatQuotas" ENABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."SubscriptionCsvDownloads" ENABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."SubscriptionCsvDownloads" ENABLE TRIGGER update_not_allowed; ALTER TABLE employer_commerce."TalentChatAllocations" ENABLE TRIGGER delete_not_allowed; ALTER TABLE employer_commerce."TalentChatAllocations" ENABLE TRIGGER update_not_allowed; ALTER TABLE employer_commerce."CompanyJobSlotsSubscriptions" ENABLE TRIGGER delete_not_allowed; --- Delete data for journal entry journal_entry_related_table_list := ARRAY['PointsUsageJournalEntries', 'PointsJournalEntryDebitCredits', 'PointsTopUpJournalEntries', 'PointsExpiryDatetimes']; FOR i IN 1..array_length(journal_entry_related_table_list, 1) LOOP table_name := journal_entry_related_table_list[i]; EXECUTE format(' DELETE FROM employer_commerce.%I where "pointsJournalEntryId" in ( select "id" from employer_commerce."PointsJournalEntries" where "companyId" = ANY(''%s'') ); ', table_name, company_ids_to_delete); END LOOP; DELETE FROM employer_commerce."PointsJournalEntries" where "companyId" = ANY(company_ids_to_delete); --- Delete data for point accounts tables points_accounts_table_list := ARRAY['EmployerPaidPointsAccounts', 'EmployerBonusPointsAccounts', 'JobPostPointsUsageAccounts', 'JobPostExtensionPointsUsageAccounts', 'ExpiryPointsAccounts', 'TalentChatPointsAccounts']; FOR i IN 1..array_length(points_accounts_table_list, 1) LOOP table_name := points_accounts_table_list[i]; EXECUTE format('select ARRAY_AGG("pointsAccountId") from employer_commerce.%I where "companyId" = ANY(''%s'')', table_name, company_ids_to_delete) into points_account_ids_to_delete; IF cardinality(points_account_ids_to_delete) >= 1 THEN EXECUTE format('DELETE from employer_commerce.%I where "pointsAccountId" = ANY(''%s'')', table_name, points_account_ids_to_delete); DELETE FROM employer_commerce."PointsAccounts" where id = ANY(points_account_ids_to_delete); END IF; END LOOP; DELETE FROM employer_commerce."Orders" where id in ( select orders.id from employer_commerce."Orders" orders left join "UserCompanies" as user_companies on user_companies.id = orders."userCompanyId" where user_companies."CompanyId" = ANY(company_ids_to_delete) or orders."companyId" = ANY(company_ids_to_delete) ); DELETE FROM employer_commerce."CompanyBalanceExpiryDates" where "companyId" = ANY(company_ids_to_delete); END;