JessieExcel/api/db.ts

import fs from 'fs/promises'
import path from 'path'
import bcrypt from 'bcryptjs'
import { nanoid } from 'nanoid'
import type {
  DbShape,
  Factory,
  Order,
  OrderProgressEvent,
  OrderProgressStatus,
  PublicUser,
  Role,
  UserRecord,
} from '../shared/types.js'

const DATA_DIR = path.resolve(process.cwd(), 'api', 'data')
const DB_PATH = path.resolve(DATA_DIR, 'db.json')

let dbCache: DbShape | null = null
let writeLock: Promise<void> = Promise.resolve()

const nowIso = (): string => new Date().toISOString()

const sanitizeUser = (u: UserRecord): PublicUser => {
  const { passwordHash: _pw, ...rest } = u
  return rest
}

const ensureDataDir = async (): Promise<void> => {
  await fs.mkdir(DATA_DIR, { recursive: true })
}

const seedFactories = (): Factory[] => {
  const t = nowIso()
  return [
    {
      id: nanoid(),
      name: 'A厂',
      contactPerson: '王工',
      contactPhone: '13800000001',
      contactEmail: 'factory-a@example.com',
      address: '广东省深圳市',
      status: 'active',
      createdAt: t,
      updatedAt: t,
    },
    {
      id: nanoid(),
      name: 'B厂',
      contactPerson: '李工',
      contactPhone: '13800000002',
      contactEmail: 'factory-b@example.com',
      address: '浙江省宁波市',
      status: 'active',
      createdAt: t,
      updatedAt: t,
    },
  ]
}

const seedUsers = async (): Promise<UserRecord[]> => {
  const t = nowIso()
  const makeUser = async (
    username: string,
    password: string,
    role: Role,
  ): Promise<UserRecord> => {
    const passwordHash = await bcrypt.hash(password, 10)
    return {
      id: nanoid(),
      username,
      role,
      status: 'active',
      passwordHash,
      createdAt: t,
      updatedAt: t,
    }
  }

  return [
    await makeUser('admin', 'admin123', 'admin'),
    await makeUser('sales', 'sales123', 'sales'),
    await makeUser('purchase', 'purchase123', 'purchase'),
    await makeUser('manager', 'manager123', 'manager'),
  ]
}

const seedOrders = (users: UserRecord[], factories: Factory[]): Order[] => {
  const t = nowIso()
  const salesUser = users.find((u) => u.role === 'sales')
  const f1 = factories[0]
  const f2 = factories[1]
  if (!salesUser) return []

  return [
    {
      id: nanoid(),
      orderNo: 'SO-2026-0001',
      poNo: 'PO-10001',
      productName: '玻璃杯',
      country: '美国',
      orderAmount: 120000,
      orderDate: '2026-03-01',
      customerDeliveryDate: '2026-04-05',
      factoryId: f1?.id,
      factoryDeliveryDate: '2026-03-28',
      factoryContract: 'FC-2026-A-001',
      packagingStatus: '进行中',
      stickerStatus: '未开始',
      shippingStatus: '未订舱',
      inspectionStatus: '未验货',
      purchaseAmount: 85000,
      orderProgress: '生产',
      remarks: '重点客户，优先排产',
      ciAmount: 118000,
      etd: '2026-04-02',
      eta: '2026-04-22',
      paymentDate: '2026-03-15',
      paymentAmount: 60000,
      balance: 60000,
      createdByUserId: salesUser.id,
      createdAt: t,
      updatedAt: t,
    },
    {
      id: nanoid(),
      orderNo: 'SO-2026-0002',
      poNo: 'PO-10002',
      productName: '保温壶',
      country: '德国',
      orderAmount: 90000,
      orderDate: '2026-03-08',
      customerDeliveryDate: '2026-04-18',
      factoryId: f2?.id,
      factoryDeliveryDate: '2026-04-05',
      factoryContract: 'FC-2026-B-002',
      packagingStatus: '未开始',
      stickerStatus: '未开始',
      shippingStatus: '未订舱',
      inspectionStatus: '未验货',
      purchaseAmount: 62000,
      orderProgress: '下单',
      remarks: '',
      ciAmount: 89000,
      etd: '2026-04-12',
      eta: '2026-05-02',
      paymentDate: undefined,
      paymentAmount: undefined,
      balance: 90000,
      createdByUserId: salesUser.id,
      createdAt: t,
      updatedAt: t,
    },
  ]
}

const seedProgressEvents = (
  orders: Order[],
  users: UserRecord[],
): OrderProgressEvent[] => {
  const t = nowIso()
  const salesUser = users.find((u) => u.role === 'sales')
  const purchaseUser = users.find((u) => u.role === 'purchase')
  if (!salesUser) return []

  const o1 = orders[0]
  const o2 = orders[1]
  const events: OrderProgressEvent[] = []

  if (o1) {
    events.push(
      {
        id: nanoid(),
        orderId: o1.id,
        status: '下单',
        note: '订单录入完成',
        createdByUserId: salesUser.id,
        createdAt: t,
      },
      {
        id: nanoid(),
        orderId: o1.id,
        status: '生产',
        note: '已排产，预计本月完成',
        createdByUserId: purchaseUser?.id ?? salesUser.id,
        createdAt: t,
      },
    )
  }

  if (o2) {
    events.push({
      id: nanoid(),
      orderId: o2.id,
      status: '下单',
      note: '等待工厂确认交期',
      createdByUserId: salesUser.id,
      createdAt: t,
    })
  }

  return events
}

const createInitialDb = async (): Promise<DbShape> => {
  const factories = seedFactories()
  const users = await seedUsers()
  const orders = seedOrders(users, factories)
  const progressEvents = seedProgressEvents(orders, users)
  return { users, factories, orders, progressEvents }
}

const readDbFromDisk = async (): Promise<DbShape | null> => {
  try {
    const raw = await fs.readFile(DB_PATH, 'utf-8')
    const parsed = JSON.parse(raw) as DbShape
    if (!parsed?.users || !parsed?.orders || !parsed?.factories) return null
    return {
      users: parsed.users ?? [],
      factories: parsed.factories ?? [],
      orders: parsed.orders ?? [],
      progressEvents: parsed.progressEvents ?? [],
    }
  } catch {
    return null
  }
}

const persistDb = async (db: DbShape): Promise<void> => {
  await ensureDataDir()
  const tmp = DB_PATH + '.tmp'
  await fs.writeFile(tmp, JSON.stringify(db, null, 2), 'utf-8')
  try {
    await fs.unlink(DB_PATH)
  } catch {
  }
  await fs.rename(tmp, DB_PATH)
}

export const getDb = async (): Promise<DbShape> => {
  if (dbCache) return dbCache
  await ensureDataDir()
  const loaded = await readDbFromDisk()
  if (loaded) {
    dbCache = loaded
    return loaded
  }
  const seeded = await createInitialDb()
  await persistDb(seeded)
  dbCache = seeded
  return seeded
}

export const updateDb = async (updater: (db: DbShape) => DbShape): Promise<DbShape> => {
  writeLock = writeLock.then(async () => {
    const db = await getDb()
    const next = updater(JSON.parse(JSON.stringify(db)) as DbShape)
    dbCache = next
    await persistDb(next)
  })
  await writeLock
  return (await getDb())
}

export const findUserByUsername = async (
  username: string,
): Promise<UserRecord | undefined> => {
  const db = await getDb()
  return db.users.find((u) => u.username.toLowerCase() === username.toLowerCase())
}

export const createUser = async (params: {
  username: string
  password: string
  role: Role
  email?: string
}): Promise<PublicUser> => {
  const { username, password, role, email } = params
  const t = nowIso()
  const passwordHash = await bcrypt.hash(password, 10)
  const record: UserRecord = {
    id: nanoid(),
    username,
    role,
    email,
    status: 'active',
    passwordHash,
    createdAt: t,
    updatedAt: t,
  }
  await updateDb((db) => ({ ...db, users: [...db.users, record] }))
  return sanitizeUser(record)
}

export const getPublicUsers = async (): Promise<PublicUser[]> => {
  const db = await getDb()
  return db.users.map(sanitizeUser)
}

export const listFactories = async (): Promise<Factory[]> => {
  const db = await getDb()
  return db.factories
}

export const listOrders = async (): Promise<Order[]> => {
  const db = await getDb()
  return db.orders
}

export const getOrder = async (id: string): Promise<Order | undefined> => {
  const db = await getDb()
  return db.orders.find((o) => o.id === id)
}

export const createOrder = async (params: {
  input: Omit<Order, 'id' | 'createdAt' | 'updatedAt'>
}): Promise<Order> => {
  const t = nowIso()
  const order: Order = {
    id: nanoid(),
    ...params.input,
    createdAt: t,
    updatedAt: t,
  }
  await updateDb((db) => ({ ...db, orders: [order, ...db.orders] }))
  return order
}

export const bulkCreateOrders = async (params: {
  inputs: Omit<Order, 'id' | 'createdAt' | 'updatedAt'>[]
  createdByUserId: string
  skipDuplicates?: boolean
}): Promise<{ created: Order[]; skippedOrderNos: string[] }> => {
  const created: Order[] = []
  const skippedOrderNos: string[] = []

  await updateDb((db) => {
    const existingOrderNos = new Set(db.orders.map((o) => o.orderNo))
    const nextOrders = [...db.orders]
    const nextProgressEvents = [...db.progressEvents]

    for (const input of params.inputs) {
      const orderNo = input.orderNo.trim()
      if (!orderNo) continue
      if (params.skipDuplicates && existingOrderNos.has(orderNo)) {
        skippedOrderNos.push(orderNo)
        continue
      }
      existingOrderNos.add(orderNo)

      const t = nowIso()
      const order: Order = {
        id: nanoid(),
        ...input,
        orderNo,
        createdByUserId: params.createdByUserId,
        createdAt: t,
        updatedAt: t,
      }
      created.push(order)
      nextOrders.unshift(order)

      const evt: OrderProgressEvent = {
        id: nanoid(),
        orderId: order.id,
        status: order.orderProgress,
        note: '创建订单',
        createdByUserId: params.createdByUserId,
        createdAt: t,
      }
      nextProgressEvents.unshift(evt)
    }

    return { ...db, orders: nextOrders, progressEvents: nextProgressEvents }
  })

  return { created, skippedOrderNos }
}

export const updateOrder = async (params: {
  id: string
  patch: Partial<Order>
}): Promise<Order | null> => {
  const { id, patch } = params
  let updated: Order | null = null
  await updateDb((db) => {
    const nextOrders = db.orders.map((o) => {
      if (o.id !== id) return o
      updated = { ...o, ...patch, updatedAt: nowIso() }
      return updated
    })
    return { ...db, orders: nextOrders }
  })
  return updated
}

export const deleteOrder = async (id: string): Promise<boolean> => {
  let removed = false
  await updateDb((db) => {
    const nextOrders = db.orders.filter((o) => o.id !== id)
    removed = nextOrders.length !== db.orders.length
    const nextProgress = db.progressEvents.filter((e) => e.orderId !== id)
    return { ...db, orders: nextOrders, progressEvents: nextProgress }
  })
  return removed
}

export const bulkDeleteOrders = async (params: { ids: string[] }): Promise<number> => {
  const idSet = new Set(params.ids.filter((x) => x.trim().length > 0))
  if (idSet.size === 0) return 0
  let removedCount = 0
  await updateDb((db) => {
    const nextOrders = db.orders.filter((o) => {
      if (!idSet.has(o.id)) return true
      removedCount += 1
      return false
    })
    const nextProgress = db.progressEvents.filter((e) => !idSet.has(e.orderId))
    return { ...db, orders: nextOrders, progressEvents: nextProgress }
  })
  return removedCount
}

export const clearOrders = async (): Promise<number> => {
  let removedCount = 0
  await updateDb((db) => {
    removedCount = db.orders.length
    return { ...db, orders: [], progressEvents: [] }
  })
  return removedCount
}

export const listProgressEvents = async (orderId: string): Promise<OrderProgressEvent[]> => {
  const db = await getDb()
  return db.progressEvents
    .filter((e) => e.orderId === orderId)
    .sort((a, b) => (a.createdAt < b.createdAt ? 1 : -1))
}

export const addProgressEvent = async (params: {
  orderId: string
  status: OrderProgressStatus
  note?: string
  createdByUserId: string
}): Promise<OrderProgressEvent> => {
  const evt: OrderProgressEvent = {
    id: nanoid(),
    orderId: params.orderId,
    status: params.status,
    note: params.note,
    createdByUserId: params.createdByUserId,
    createdAt: nowIso(),
  }
  await updateDb((db) => ({ ...db, progressEvents: [evt, ...db.progressEvents] }))
  return evt
}