glints.public

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;

delete_deletable_company_data_in_monetisation_tables(IN company_ids_to_delete uuid[])

Parameters

Definition