import { MigrationInterface, QueryRunner } from 'typeorm';

export class updateViews1698521871942 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
            CREATE VIEW asset_types
            AS
            WITH RECURSIVE r AS (
                SELECT a.id AS owner,
                    a.id AS parent_id,
                    a.project_id AS project_id,
                    a.id,
                    0 AS level
                FROM assets a
                UNION
                SELECT r_1.owner,
                    a.id AS parent_id,
                    a.project_id AS project_id,
                    a.id,
                    r_1.level + 1 AS level
                FROM r r_1
                        JOIN assets a ON a.parent_ids[1] = r_1.parent_id AND (r_1.project_id = 1 OR a.project_id = r_1.project_id)
            )
            SELECT r.project_id,
                r.id,
                r.level,
                r.owner AS type_id

            FROM r
            WHERE r.level > 0
    `);
    await queryRunner.query(`
    DROP VIEW asset_icons;

CREATE VIEW asset_icons
AS
WITH RECURSIVE r AS (
    SELECT a.id,
           a.project_id,
           a.icon
    FROM assets a
    WHERE a.icon IS NOT NULL
    UNION
    SELECT a.id,
           a.project_id,
           r_1.icon
    FROM r r_1
             JOIN assets a ON a.parent_ids[1] = r_1.id AND (a.project_id = r_1.project_id OR r_1.project_id = 1)
    WHERE a.icon IS NULL
)
SELECT r.project_id,
       r.id,
       r.icon
FROM r;

`);
    await queryRunner.query(`
drop view workspaces_nesting;
create view workspaces_nesting as
WITH RECURSIVE r AS (
    SELECT w.id AS owner_catalog,
           w.id AS parent_id,
           w.project_id,
           w.id
    FROM workspaces w
    UNION
    SELECT r_1.owner_catalog,
           w.id AS parent_id,
           w.project_id,
           w.id
    FROM r r_1
             JOIN workspaces w ON w.parent_id = r_1.parent_id AND (w.project_id = r_1.project_id OR r_1.project_id = 1)
)
SELECT
       r.project_id,
       r.id,
       r.owner_catalog AS parent_id

FROM r;

`);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
            DROP VIEW asset_types;
    `);
    await queryRunner.query(`
    DROP VIEW asset_icons;
    create view asset_icons(id, icon) as
	WITH RECURSIVE r AS (
    SELECT a.id,
           a.icon
    FROM assets a
    WHERE a.icon IS NOT NULL
    UNION
    SELECT a.id,
           r_1.icon
    FROM r r_1
             JOIN assets a ON a.parent_ids[1] = r_1.id
    WHERE a.icon IS NULL
)
SELECT r.id,
       r.icon
FROM r;

alter table asset_icons owner to postgres;
`);
    await queryRunner.query(`
drop view workspaces_nesting;
create view workspaces_nesting(parent_id, id) as
	WITH RECURSIVE r AS (
    SELECT w.id AS owner_catalog,
           w.id AS parent_id,
           w.id
    FROM workspaces w
    UNION
    SELECT r_1.owner_catalog,
           w.id AS parent_id,
           w.id
    FROM r r_1
             JOIN workspaces w ON w.parent_id = r_1.parent_id
)
SELECT r.owner_catalog AS parent_id,
       r.id
FROM r;

alter table workspaces_nesting owner to postgres;

`);
  }
}