path: root/db/schema/users.ts

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

export const userDomainEnum = pgEnum("user_domain", ["pending", "evcp", "procurement", "sales", "engineering", "partners"]);


export const users = pgTable("users", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  name: varchar("name", { length: 255 }).notNull(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  epId: varchar("epId", { length: 50 }), // Knox Unique Id (PK)
  deptCode: varchar("deptCode", { length: 50 }),
  deptName: varchar("deptName", { length: 255 }),

  companyId: integer("company_id")
    .references(() => vendors.id, { onDelete: "set null" }),
  techCompanyId: integer("tech_company_id")
    .references(() => techVendors.id, { onDelete: "set null" }),
  domain: userDomainEnum("domain").notNull().default("partners"),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  imageUrl: varchar("image_url", { length: 1024 }),
  language: varchar("language", { length: 10 }).default("en"),

  // MFA 관련 새 컬럼들
  phone: varchar("phone", { length: 20 }), // 국제 형식 전화번호 (+82-10-1234-5678), 해외번호 20자 초과건으로 사이즈 변경
  mfaEnabled: boolean("mfa_enabled").default(false).notNull(),
  mfaSecret: varchar("mfa_secret", { length: 32 }), // TOTP secret (나중에 사용)

  // 계정 보안 관련
  isLocked: boolean("is_locked").default(false).notNull(),
  lockoutUntil: timestamp("lockout_until", { withTimezone: true }),
  failedLoginAttempts: integer("failed_login_attempts").default(0).notNull(),
  lastLoginAt: timestamp("last_login_at", { withTimezone: true }),
  passwordChangeRequired: boolean("password_change_required").default(false).notNull(),

  // 비활성화 관련 새 필드들
  isActive: boolean("is_active").default(true).notNull(),
  deactivatedAt: timestamp("deactivated_at", { withTimezone: true }),
  deactivationReason: varchar("deactivation_reason", { length: 50 }), // 'INACTIVE', 'ADMIN', 'GDPR' 등

   // ✨ 새로 추가: 동의 관련 필드들
   lastConsentUpdate: timestamp("last_consent_update", { withTimezone: true }),
   consentVersion: varchar("consent_version", { length: 20 }), // 마지막 동의한 정책 버전
   requiresConsentUpdate: boolean("requires_consent_update").default(false).notNull(),
   
   // ✨ 새로 추가: 회원가입 관련
  //  emailVerified: boolean("email_verified").default(false).notNull(),
  //  emailVerifiedAt: timestamp("email_verified_at", { withTimezone: true }),
  //  registrationCompleted: boolean("registration_completed").default(false).notNull(),

  // 김희은 프로 요구사항으로 추가
  employeeNumber: varchar("employee_number", { length: 50 }),
  knoxId: varchar("knox_id", { length: 50 }), // Knox ID로 이메일 앞부분
  nonsapUserId: varchar("nonsap_user_id", { length: 50 }).unique(),
  isAbsent: boolean("is_absent"), // 휴직여부 (SHI-API LOFF_GB (Y/N))
  isDeletedOnNonSap: boolean("is_deleted_on_non_sap"), // 퇴직여부 (SHI-API DEL_YN (Y/N))
  isRegularEmployee: boolean("is_regular_employee"), // 정직원여부 (SHI-API REGL_ORORD_GB (S/N))

  // 사용자 코드 (구매그룹코드)
  userCode: varchar("user_code", { length: 50 }),

}, (table) => {
  return {
    emailIdx: uniqueIndex("users_email_idx").on(table.email),
    phoneIdx: index("users_phone_idx").on(table.phone),
  };
});

// 패스워드 테이블 (현재 활성 패스워드)
export const passwords = pgTable("passwords", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  userId: integer("user_id")
    .references(() => users.id, { onDelete: "cascade" })
    .notNull(),
  passwordHash: varchar("password_hash", { length: 255 }).notNull(), // bcrypt hash
  salt: varchar("salt", { length: 255 }).notNull(), // 추가 salt
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  expiresAt: timestamp("expires_at", { withTimezone: true }), // 패스워드 만료일
  isActive: boolean("is_active").default(true).notNull(),

  // 패스워드 메타데이터
  strength: integer("strength").notNull(), // 1-5 강도 점수
  hasUppercase: boolean("has_uppercase").notNull(),
  hasLowercase: boolean("has_lowercase").notNull(),
  hasNumbers: boolean("has_numbers").notNull(),
  hasSymbols: boolean("has_symbols").notNull(),
  length: integer("length").notNull(),
}, (table) => {
  return {
    userIdIdx: index("passwords_user_id_idx").on(table.userId),
    activeIdx: index("passwords_active_idx").on(table.isActive),
  };
});

// 패스워드 히스토리 (재사용 방지)
export const passwordHistory = pgTable("password_history", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  userId: integer("user_id")
    .references(() => users.id, { onDelete: "cascade" })
    .notNull(),
  passwordHash: varchar("password_hash", { length: 255 }).notNull(),
  salt: varchar("salt", { length: 255 }).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  replacedAt: timestamp("replaced_at", { withTimezone: true }), // 언제 교체되었는지
}, (table) => {
  return {
    userIdIdx: index("password_history_user_id_idx").on(table.userId),
    createdAtIdx: index("password_history_created_at_idx").on(table.createdAt),
  };
});

// 로그인 시도 로그 (보안 감사)
export const loginAttempts = pgTable("login_attempts", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  email: varchar("email", { length: 255 }).notNull(),
  userId: integer("user_id").references(() => users.id, { onDelete: "set null" }),
  success: boolean("success").notNull(),
  ipAddress: varchar("ip_address", { length: 45 }).notNull(), // IPv6 지원
  userAgent: text("user_agent"),
  failureReason: varchar("failure_reason", { length: 100 }), // 실패 이유
  attemptedAt: timestamp("attempted_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

  // 지리적 정보 (옵셔널)
  country: varchar("country", { length: 2 }),
  city: varchar("city", { length: 100 }),
}, (table) => {
  return {
    emailIdx: index("login_attempts_email_idx").on(table.email),
    attemptedAtIdx: index("login_attempts_attempted_at_idx").on(table.attemptedAt),
    ipAddressIdx: index("login_attempts_ip_address_idx").on(table.ipAddress),
  };
});

// MFA 토큰 테이블 (SMS, TOTP 등)
export const mfaTokens = pgTable("mfa_tokens", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
  userId: integer("user_id")
    .references(() => users.id, { onDelete: "cascade" })
    .notNull(),
  token: varchar("token", { length: 255 }).notNull(), // SMS 코드나 TOTP
  type: varchar("type", { length: 20 }).notNull(), // 'sms', 'totp', 'backup'
  expiresAt: timestamp("expires_at", { withTimezone: true }).notNull(),
  usedAt: timestamp("used_at", { withTimezone: true }),
  isActive: boolean("is_active").default(true).notNull(),
  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),

  // SMS 관련 추가 정보
  phoneNumber: varchar("phone_number", { length: 20 }), // 전송된 전화번호
  attempts: integer("attempts").default(0).notNull(), // 시도 횟수
}, (table) => {
  return {
    userIdIdx: index("mfa_tokens_user_id_idx").on(table.userId),
    tokenIdx: index("mfa_tokens_token_idx").on(table.token),
    expiresAtIdx: index("mfa_tokens_expires_at_idx").on(table.expiresAt),
  };
});

// 보안 설정 테이블
export const securitySettings = pgTable("security_settings", {
  id: integer("id").primaryKey().generatedAlwaysAsIdentity(),

  // 패스워드 정책
  minPasswordLength: integer("min_password_length").default(8).notNull(),
  requireUppercase: boolean("require_uppercase").default(true).notNull(),
  requireLowercase: boolean("require_lowercase").default(true).notNull(),
  requireNumbers: boolean("require_numbers").default(true).notNull(),
  requireSymbols: boolean("require_symbols").default(true).notNull(),
  passwordExpiryDays: integer("password_expiry_days").default(90), // null이면 만료 없음
  passwordHistoryCount: integer("password_history_count").default(5).notNull(),

  // 계정 잠금 정책
  maxFailedAttempts: integer("max_failed_attempts").default(5).notNull(),
  lockoutDurationMinutes: integer("lockout_duration_minutes").default(30).notNull(),

  // MFA 정책
  requireMfaForPartners: boolean("require_mfa_for_partners").default(true).notNull(),
  smsTokenExpiryMinutes: integer("sms_token_expiry_minutes").default(5).notNull(),
  maxSmsAttemptsPerDay: integer("max_sms_attempts_per_day").default(10).notNull(),

  // 세션 관리
  sessionTimeoutMinutes: integer("session_timeout_minutes").default(480).notNull(), // 8시간

  createdAt: timestamp("created_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
  updatedAt: timestamp("updated_at", { withTimezone: true })
    .defaultNow()
    .notNull(),
});


// 부서 정의 테이블
export const departments = pgTable("departments", {
  id: serial("id").primaryKey(),
  departmentCode: varchar("department_code", { length: 50 }).notNull().unique(),
  departmentName: varchar("department_name", { length: 100 }).notNull(),
  description: text("description"),
  isActive: boolean("is_active").notNull().default(true),

  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().notNull(),
});


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"),
      user_phone: sql<string>`${users.phone}`.as("user_phone"),

      // 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"),

      // 추가: 사번, 부서, 녹스ID
      employee_number: sql<string | null>`${users.employeeNumber}`.as("employee_number"),
      dept_name: sql<string | null>`${users.deptName}`.as("dept_name"),
      knox_id: sql<string | null>`${users.knoxId}`.as("knox_id"),

      // 추가: 계정 상태 플래그
      is_locked: sql<boolean>`${users.isLocked}`.as("is_locked"),
      is_absent: sql<boolean | null>`${users.isAbsent}`.as("is_absent"),
      is_deleted_on_non_sap: sql<boolean | null>`${users.isDeletedOnNonSap}`.as("is_deleted_on_non_sap"),
      is_regular_employee: sql<boolean | null>`${users.isRegularEmployee}`.as("is_regular_employee"),

      // 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) created/updated/deactivated dates
      created_at: sql<Date>`${users.createdAt}`.as("created_at"),
      updated_at: sql<Date>`${users.updatedAt}`.as("updated_at"),
      deactivated_at: sql<Date | null>`${users.deactivatedAt}`.as("deactivated_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;