import { MigrationInterface, QueryRunner } from 'typeorm';

export class updateImcFuncs1698494225188 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
      
CREATE OR REPLACE FUNCTION imc_to_string(varchar) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT $1::varchar
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(anyarray) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT ''array['' || array_length($1, 1) || '']''
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(boolean) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT  CASE
        WHEN $1 THEN ''1''
        ELSE ''0''
      END
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(integer) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT $1::varchar
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(double precision) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT $1::varchar
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(uuid) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT $1::varchar
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(timestamp with time zone) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT to_char($1::timestamp at time zone ''UTC'', ''YYYY-MM-DD"T"HH24:MI:SS"Z"'')
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION imc_to_string(jsonb) RETURNS varchar
PARALLEL SAFE
    AS '
    SELECT CASE jsonb_typeof($1)
       WHEN ''object'' THEN
        CASE
          WHEN $1 ? ''assetId'' THEN
              ''['' || (COALESCE($1->>''title'', $1 ->> ''name'', '''')) || ''](#asset:'' || ($1->>''assetId'') || '')''
          WHEN $1 ? ''accountId'' THEN
              ''['' || (COALESCE($1 ->> ''name'', '''')) || ''](#account:'' ||  ($1 ->> ''accountId'') || '')''
          WHEN $1 ? ''blob'' THEN
              ''[](#blob:'' || (COALESCE($1 ->> ''type'', '''')) || '':'' || ($1 ->> ''blob'') || '')''
          WHEN $1 ? ''taskNum'' THEN
                              ''[](#task:'' || ($1 ->> ''taskNum'') || '')''
          WHEN $1 ? ''str'' THEN
                ($1 ->> ''str'')
          ELSE $1::varchar
        END
       WHEN ''array'' THEN ''array['' || jsonb_array_length($1) || '']''
       WHEN ''string'' THEN ($1->>0)::varchar
       WHEN ''number'' THEN ($1->>0)::varchar
       WHEN ''boolean'' THEN imc_to_string(($1->>0)::boolean)
       END;
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
 
        `);

    await queryRunner.query(`
        CREATE OR REPLACE FUNCTION imc_to_boolean(varchar) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT $1 <> '''' AND $1 <> ''0''
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(anyarray) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT true
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(boolean) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT $1
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(integer) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT $1 <> 0
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(double precision) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT $1 <> 0
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(uuid) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT true
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(timestamp with time zone) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT true
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        
        CREATE OR REPLACE FUNCTION imc_to_boolean(jsonb) RETURNS boolean
        PARALLEL SAFE
            AS '
            SELECT CASE jsonb_typeof($1)
               WHEN ''object'' THEN imc_to_boolean(imc_to_string($1))
               WHEN ''array'' THEN true
               WHEN ''string'' THEN imc_to_boolean(($1->>0)::varchar)
               WHEN ''number'' THEN imc_to_boolean(($1->>0)::varchar)
               WHEN ''boolean'' THEN ($1->>0)::boolean
               END;
        '
            LANGUAGE SQL
            IMMUTABLE
            RETURNS NULL ON NULL INPUT;
        

      `);
    await queryRunner.query(`
    
    CREATE OR REPLACE FUNCTION imc_to_timestamptz(jsonb) RETURNS timestamptz
    PARALLEL SAFE
        AS '
        SELECT CASE jsonb_typeof($1)
           WHEN ''object'' THEN imc_to_timestamptz(imc_to_string($1))
           WHEN ''array'' THEN NULL::timestamptz
           WHEN ''string'' THEN imc_to_timestamptz(($1->>0)::varchar)
           WHEN ''number'' THEN to_timestamp(($1->>0)::float8)::timestamptz
           WHEN ''boolean'' THEN NULL::timestamptz
           END;
    '
        LANGUAGE SQL
        IMMUTABLE
        RETURNS NULL ON NULL INPUT;
        
        
    CREATE OR REPLACE FUNCTION imc_to_double(jsonb) RETURNS float8
    PARALLEL SAFE
        AS '
        SELECT CASE jsonb_typeof($1)
            WHEN ''object'' THEN imc_to_double(imc_to_string($1))
           WHEN ''array'' THEN NULL::float8
           WHEN ''string'' THEN imc_to_double(($1->>0)::varchar)
           WHEN ''number'' THEN ($1->>0)::float8
           WHEN ''boolean'' THEN imc_to_double(($1->>0)::boolean)
           END;
    '
        LANGUAGE SQL
        IMMUTABLE
        RETURNS NULL ON NULL INPUT;
    `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
        drop function imc_to_string(varchar);

        drop function imc_to_string(anyarray);

        drop function imc_to_string(boolean);

        drop function imc_to_string(integer);

        drop function imc_to_string(double precision);

        drop function imc_to_string(uuid);

        drop function imc_to_string(timestamp with time zone);

        drop function imc_to_string(jsonb);
    `);
    await queryRunner.query(`
        drop function imc_to_boolean(varchar);

        drop function imc_to_boolean(anyarray);

        drop function imc_to_boolean(boolean);

        drop function imc_to_boolean(integer);

        drop function imc_to_boolean(double precision);

        drop function imc_to_boolean(uuid);

        drop function imc_to_boolean(timestamp with time zone);

        drop function imc_to_boolean(jsonb);
    `);
    await queryRunner.query(`
    
CREATE OR REPLACE FUNCTION imc_to_timestamptz(jsonb) RETURNS timestamptz
PARALLEL SAFE
    AS '
    SELECT CASE jsonb_typeof($1)
       WHEN ''object'' THEN NULL::timestamptz
       WHEN ''array'' THEN NULL::timestamptz
       WHEN ''string'' THEN imc_to_timestamptz(($1->>0)::varchar)
       WHEN ''number'' THEN to_timestamp(($1->>0)::float8)::timestamptz
       WHEN ''boolean'' THEN NULL::timestamptz
       END;
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
    
    
CREATE OR REPLACE FUNCTION imc_to_double(jsonb) RETURNS float8
PARALLEL SAFE
    AS '
    SELECT CASE jsonb_typeof($1)
       WHEN ''object'' THEN NULL::float8
       WHEN ''array'' THEN NULL::float8
       WHEN ''string'' THEN imc_to_double(($1->>0)::varchar)
       WHEN ''number'' THEN ($1->>0)::float8
       WHEN ''boolean'' THEN imc_to_double(($1->>0)::boolean)
       END;
'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;
`);
  }
}