import { sql } from 'drizzle-orm';
import {
  pgTable,
  serial,
  text,
  timestamp,
  boolean,
  integer,
  json,
  bigint,
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

// 管理员表：保存系统管理员与普通管理员
export const adminUsers = pgTable('admin_users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  name: text('name'),
  // 使用 Node.js crypto.scrypt 生成的口令哈希，包含算法/盐/哈希等信息
  passwordHash: text('password_hash').notNull(),
  // 角色：super=系统管理员，admin=普通管理员
  role: text('role').notNull().default('admin'), // 'super' | 'admin'
  isActive: boolean('is_active').notNull().default(true),
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
  updatedAt: timestamp('updated_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
});

// 会话表：保存用户 session，7 天有效
export const adminSessions = pgTable('admin_sessions', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').notNull(),
  // 保存 session token 的哈希（sha256），避免明文落库
  tokenHash: text('token_hash').notNull().unique(),
  expiresAt: timestamp('expires_at', { withTimezone: true }).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true })
    .notNull()
    .default(sql`now()`),
});

export type AdminRole = 'super' | 'admin';

// Supabase: public.words
//
// create table public.words (
//   id bigint generated by default as identity not null,
//   "wordRank" integer null,
//   "headWord" text null,
//   content json null,
//   "bookId" text null,
//   constraint words_pkey primary key (id)
// );
export const words = pgTable('words', {
  id: bigint('id', { mode: 'number' }).primaryKey(),
  wordRank: integer('wordRank'),
  headWord: text('headWord'),
  content: json('content').$type<unknown | null>(),
  bookId: text('bookId'),
});

export type WordRow = typeof words.$inferSelect;
export type NewWordRow = typeof words.$inferInsert;

// 单词书表
export const books = pgTable('books', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  wordCount: integer('word_count').notNull().default(0),
  coverUrl: text('cover_url'),
  bookId: text('book_id').notNull().unique(),
  // 以逗号分割的标签原文存储
  tags: text('tags'),
  createdAt: timestamp('created_at', { withTimezone: true }).notNull().default(sql`now()`),
  updatedAt: timestamp('updated_at', { withTimezone: true }).notNull().default(sql`now()`),
});

export const booksRelations = relations(books, ({ many }) => ({
  words: many(words),
}));

export const wordsRelations = relations(words, ({ one }) => ({
  book: one(books, {
    fields: [words.bookId],
    references: [books.bookId],
  }),
}));

export type BookRow = typeof books.$inferSelect;
export type NewBookRow = typeof books.$inferInsert;
