import { integer, pgTable, varchar,timestamp,pgEnum ,pgView, text, primaryKey} from "drizzle-orm/pg-core";
import { eq , sql} from "drizzle-orm";
import { vendors } from "./vendors";

export const userDomainEnum = pgEnum("user_domain", ["evcp", "partners"]); 


export const users = pgTable("users", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  companyId: integer("company_id")
  .references(() => vendors.id, { onDelete: "set null" }),
  domain: userDomainEnum("domain").notNull().default("partners"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  imageUrl: varchar("image_url", { length: 1024 }),

});

export const otps = pgTable('otps', {
  email: varchar('email', { length: 256 }).notNull().primaryKey(),
  code: varchar('code', { length: 6 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
  otpToken: varchar({ length: 512 }).notNull(),  // 이제 null 불가능
  otpExpires: timestamp('otp_expires').notNull(), // null 불가능
});

export const permissions = pgTable("permissions", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  permissionKey: text("permission_key").notNull(),
  description: text("description"),
  createdAt: timestamp("created_at").default(sql`now()`),
});

export const roles = pgTable("roles", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  name: text("name").notNull(), // admin, manager
  domain: userDomainEnum("domain").notNull(),
  companyId: integer("company_id")
  .references(() => vendors.id, { onDelete: "cascade" }), 
  description: text("description").default("").notNull(),
  createdAt: timestamp("created_at").default(sql`now()`),
});

export const rolePermissions = pgTable("role_permissions", {
  roleId: integer("role_id")
    .references(() => roles.id, { onDelete: "cascade" })
    .notNull(),
  permissionId: integer("permission_id")
    .references(() => permissions.id, { onDelete: "cascade" })
    .notNull(),
}, (table) => {
  return [{
    pk: primaryKey({ columns: [table.roleId, table.permissionId] }),
    pkWithCustomName: primaryKey({ name: 'rolePermissions_pk', columns: [table.roleId, table.permissionId] }),
  }];
});

export const userRoles = pgTable("user_roles", {
  userId: integer("user_id")
    .references(() => users.id, { onDelete: "cascade" })
    .notNull(),
  roleId: integer("role_id")
    .references(() => roles.id, { onDelete: "cascade" })
    .notNull(),
}, (table) => {
  return [{
    pk: primaryKey({ columns: [table.userId, table.roleId] }),
    pkWithCustomName: primaryKey({ name: 'userRoles_pk', columns: [table.userId, table.roleId] }),
  }];
});

export type User = typeof users.$inferSelect
export type UserRole = typeof userRoles.$inferSelect
export type Role = typeof roles.$inferSelect
export type UserDomainType = (typeof userDomainEnum.enumValues)[number];


export const userView = pgView("user_view").as((qb) => {
  return qb
    .select({
      // 1) userId: number
      user_id: sql<number>`${users.id}`.as("user_id"),

      // 2) userName: string
      user_name: sql<string>`${users.name}`.as("user_name"),

      // 3) userEmail: string
      user_email: sql<string>`${users.email}`.as("user_email"),

      user_domain: sql<string>`${users.domain}`.as("user_domain"),

      user_image: sql<string>`${users.imageUrl}`.as("user_image"),

      // 4) companyId: number | null
      company_id: sql<number | null>`${vendors.id}`.as("company_id"),

      // 5) companyName: string | null
      company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),

      // 6) roles: string[]
      //    Drizzle가 이 배열을 제대로 추론하려면 sql<string[]> 붙여야 함
      roles: sql<string[]>`
      array_agg(${roles.name})
    `.as("roles"),
      // 7) createdAt: Date
      created_at: sql<Date>`${users.createdAt}`.as("created_at"),
    })
    .from(users)
    .leftJoin(vendors, eq(users.companyId, vendors.id))
    .leftJoin(userRoles, eq(users.id, userRoles.userId))
    .leftJoin(roles, eq(userRoles.roleId, roles.id))
    // array_agg를 쓰려면 GROUP BY 필요
    .groupBy(
      users.id,
      vendors.id
      // user_roles 테이블은 array_agg로 묶이므로 groupBy 필요 X
    );
});

export const roleView = pgView("role_view").as((qb) => {
  return qb
    .select({
      id: sql<number >`${roles.id}`.as("id"),
      name: sql<string>`${roles.name}`.as("name"),
      description: sql<string>`${roles.description}`.as("description"),
      domain: sql<string>`${roles.domain}`.as("domain"),
      created_at: sql<Date>`${roles.createdAt}`.as("created_at"),
      // company
      company_id: sql<number | null>`${vendors.id}`.as("company_id"),
      company_name: sql<string | null>`${vendors.vendorName}`.as("company_name"),

      // userCount
      user_count: sql<number>`COUNT(${users.id})`.as("user_count"),
    })
    .from(roles)
    .leftJoin(userRoles, eq(userRoles.roleId, roles.id))
    .leftJoin(users, eq(users.id, userRoles.userId))
    .leftJoin(vendors, eq(roles.companyId, vendors.id))
    .groupBy(roles.id, vendors.id) // user_count(집계) 위해 groupBy
})

export type UserView = typeof userView.$inferSelect;
export type RoleView = typeof roleView.$inferSelect;
export type RolePermission = typeof rolePermissions.$inferSelect;