module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();

    try {
      const rows = await queryInterface.sequelize.query(
        "SELECT to_regclass('public.\"tenantsOrganizationsOrganizations\"') AS regclass_name;",
        {
          transaction,
          type: Sequelize.QueryTypes.SELECT,
        },
      );
      const tableName = rows[0].regclass_name;

      if (!tableName) {
        await transaction.commit();
        return;
      }

      await queryInterface.sequelize.query(
        `
          INSERT INTO "tenantsOrganizationsOrganizations" (
            "tenants_organizationsId",
            "organizationId",
            "createdAt",
            "updatedAt"
          )
          SELECT DISTINCT
            relation_pairs.tenant_id,
            relation_pairs.organization_id,
            NOW(),
            NOW()
          FROM (
            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM booking_requests
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM reservations
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM documents
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM invoices
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM role_assignments
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationId" AS organization_id
            FROM activity_comments
            WHERE "tenantId" IS NOT NULL AND "organizationId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM service_requests
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM properties
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM audit_logs
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM notifications
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM checklists
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL

            UNION

            SELECT "tenantId" AS tenant_id, "organizationsId" AS organization_id
            FROM job_runs
            WHERE "tenantId" IS NOT NULL AND "organizationsId" IS NOT NULL
          ) AS relation_pairs
          ON CONFLICT ("tenants_organizationsId", "organizationId") DO NOTHING;
        `,
        { transaction },
      );

      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  },

  async down() {
    return Promise.resolve();
  },
};
