import { MigrationInterface, QueryRunner } from 'typeorm';

export class actualProjectLicenses1708973712618 implements MigrationInterface {
  public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
            CREATE VIEW actual_project_licenses
            AS
            SELECT
                x.id,
                x.project_id,
                x.start_at,
                x.till,
                x.updated_at,
                x.features,
                x.is_trial,
                x.license_type_id,
                x.license_type_name,
                x.license_type_title,
                X.license_type_features
            FROM (
                SELECT pl.*,
                        lt.title AS license_type_title,
                        lt.name AS license_type_name,
                        lt.features AS license_type_features,
                        row_number() OVER (PARTITION BY pl.project_id ORDER BY lt.priority DESC, pl.till DESC) AS rn
                    FROM project_licenses pl
                    JOIN license_types lt on pl.license_type_id = lt.id
                    WHERE pl.start_at <= NOW() AND (pl.till IS NULL OR pl.till > (NOW() + interval '8 hours'))
            ) x
            WHERE rn = 1        
        `);
  }

  public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.query(`
        DROP VIEW actual_project_licenses
    `);
  }
}