import type { Kysely } from "kysely";
import { sql } from "kysely";

export async function up(db: Kysely<any>): Promise<void> {
  // Add is_super_admin to console_users
  await sql`
    ALTER TABLE console_users
    ADD COLUMN IF NOT EXISTS is_super_admin boolean NOT NULL DEFAULT false;
  `.execute(db);

  // Create console_features table
  await db.schema
    .createTable("console_features")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("name", "text", (col) => col.notNull())
    .addColumn("slug", "text", (col) => col.notNull().unique())
    .addColumn("is_active", "boolean", (col) => col.notNull().defaultTo(true))
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`NOW()`),
    )
    .addColumn("updated_at", "timestamptz")
    .execute();

  // Create console_roles table
  await db.schema
    .createTable("console_roles")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("name", "text", (col) => col.notNull().unique())
    .addColumn("description", "text")
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`NOW()`),
    )
    .addColumn("updated_at", "timestamptz")
    .execute();

  // Create console_role_privileges table
  await db.schema
    .createTable("console_role_privileges")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("console_feature_id", "uuid", (col) =>
      col.notNull().references("console_features.id").onDelete("cascade"),
    )
    .addColumn("console_role_id", "uuid", (col) =>
      col.notNull().references("console_roles.id").onDelete("cascade"),
    )
    .addColumn("create", "boolean", (col) => col.notNull().defaultTo(false))
    .addColumn("read", "boolean", (col) => col.notNull().defaultTo(false))
    .addColumn("update", "boolean", (col) => col.notNull().defaultTo(false))
    .addColumn("delete", "boolean", (col) => col.notNull().defaultTo(false))
    .addColumn("is_active", "boolean", (col) => col.notNull().defaultTo(true))
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`NOW()`),
    )
    .addColumn("updated_at", "timestamptz")
    .execute();

  await sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uniq_console_role_privileges
    ON console_role_privileges (console_role_id, console_feature_id);
  `.execute(db);

  // Create console_user_roles table
  await db.schema
    .createTable("console_user_roles")
    .ifNotExists()
    .addColumn("id", "uuid", (col) =>
      col.primaryKey().defaultTo(sql`gen_random_uuid()`),
    )
    .addColumn("console_user_id", "uuid", (col) =>
      col.notNull().references("console_users.id").onDelete("cascade"),
    )
    .addColumn("console_role_id", "uuid", (col) =>
      col.notNull().references("console_roles.id").onDelete("cascade"),
    )
    .addColumn("created_at", "timestamptz", (col) =>
      col.notNull().defaultTo(sql`NOW()`),
    )
    .addColumn("updated_at", "timestamptz")
    .execute();

  await sql`
    CREATE UNIQUE INDEX IF NOT EXISTS uniq_console_user_roles
    ON console_user_roles (console_role_id, console_user_id);
  `.execute(db);
}

export async function down(db: Kysely<any>): Promise<void> {
  await sql`DROP INDEX IF EXISTS uniq_console_user_roles;`.execute(db);
  await sql`DROP INDEX IF EXISTS uniq_console_role_privileges;`.execute(db);
  await db.schema.dropTable("console_user_roles").ifExists().execute();
  await db.schema.dropTable("console_role_privileges").ifExists().execute();
  await db.schema.dropTable("console_roles").ifExists().execute();
  await db.schema.dropTable("console_features").ifExists().execute();
  await sql`
    ALTER TABLE console_users
    DROP COLUMN IF EXISTS is_super_admin;
  `.execute(db);
}