import { MigrationInterface, QueryRunner } from 'typeorm';

/**
 * Dedicated role hierarchy. One active row per role; parent_role_id
 * null = root. Rows are created when a role is created or when
 * hierarchy is saved — this migration only creates the table.
 */
export class RoleHierarchy1786700000000 implements MigrationInterface {
  name = 'RoleHierarchy1786700000000';

  public async up(qr: QueryRunner): Promise<void> {
    await qr.query(`
      CREATE TABLE IF NOT EXISTS "role_hierarchy" (
        "id" uuid NOT NULL DEFAULT gen_random_uuid(),
        "tenant_id" uuid NOT NULL,
        "created_by" uuid,
        "updated_by" uuid,
        "created_at" timestamptz NOT NULL DEFAULT now(),
        "updated_at" timestamptz NOT NULL DEFAULT now(),
        "is_deleted" boolean NOT NULL DEFAULT false,
        "role_id" uuid NOT NULL,
        "parent_role_id" uuid,
        CONSTRAINT "PK_role_hierarchy" PRIMARY KEY ("id"),
        CONSTRAINT "FK_role_hierarchy_role" FOREIGN KEY ("role_id") REFERENCES "roles"("id"),
        CONSTRAINT "FK_role_hierarchy_parent" FOREIGN KEY ("parent_role_id") REFERENCES "roles"("id"),
        CONSTRAINT "CHK_role_hierarchy_not_self" CHECK ("parent_role_id" IS NULL OR "parent_role_id" <> "role_id")
      )
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "IDX_role_hierarchy_tenant_id"
        ON "role_hierarchy" ("tenant_id")
    `);
    await qr.query(`
      CREATE UNIQUE INDEX IF NOT EXISTS "UQ_role_hierarchy_tenant_role"
        ON "role_hierarchy" ("tenant_id", "role_id")
        WHERE "is_deleted" = false
    `);
    await qr.query(`
      CREATE INDEX IF NOT EXISTS "ix_role_hierarchy_parent"
        ON "role_hierarchy" ("tenant_id", "parent_role_id")
        WHERE "is_deleted" = false
    `);
  }

  public async down(qr: QueryRunner): Promise<void> {
    await qr.query(`DROP TABLE IF EXISTS "role_hierarchy"`);
  }
}