import { query, withTransaction } from '../db/client.js'

type AdminUserRow = {
  id: number
  username: string
  password_hash: string
  role: string
  status: string
  created_at: string
  updated_at: string
  inventory_group_codes: string[]
}

type AdminUserCreateInput = {
  username: string
  passwordHash: string
  role: string
  status: string
  createdAt: string
  updatedAt: string
}

type AdminUserPatch = Partial<Pick<
  AdminUserRow,
  'username' | 'password_hash' | 'role' | 'status' | 'updated_at'
>>

type AdminUserListInput = {
  page?: number
  pageSize?: number
  username?: string
  role?: string
  status?: string
}

type AdminUserListResult = {
  items: AdminUserRow[]
  total: number
}

const ADMIN_USER_SELECT = `
  SELECT
    au.*,
    COALESCE(bindings.inventory_group_codes, ARRAY[]::text[]) AS inventory_group_codes
  FROM admin_users au
  LEFT JOIN LATERAL (
    SELECT ARRAY_AGG(auigb.inventory_group_code ORDER BY auigb.inventory_group_code) AS inventory_group_codes
    FROM admin_user_inventory_group_bindings auigb
    WHERE auigb.admin_user_id = au.id
  ) bindings ON TRUE
`

export async function getAdminUserById(userId: number | string): Promise<AdminUserRow | null> {
  const result = await query<AdminUserRow>(
    `${ADMIN_USER_SELECT} WHERE au.id = $1 LIMIT 1`,
    [Number(userId)],
  )
  return result.rows[0] || null
}

export async function getAdminUserByUsername(username: string): Promise<AdminUserRow | null> {
  const result = await query<AdminUserRow>(
    `${ADMIN_USER_SELECT} WHERE au.username = $1 LIMIT 1`,
    [String(username || '').trim().toLowerCase()],
  )
  return result.rows[0] || null
}

export async function createAdminUser(input: AdminUserCreateInput): Promise<AdminUserRow | null> {
  const result = await query<{ [column: string]: unknown, id: number }>(
    `
      INSERT INTO admin_users (
        username,
        password_hash,
        role,
        status,
        created_at,
        updated_at
      ) VALUES ($1, $2, $3, $4, $5, $6)
      RETURNING id
    `,
    [
      input.username,
      input.passwordHash,
      input.role,
      input.status,
      input.createdAt,
      input.updatedAt,
    ],
  )

  return getAdminUserById(result.rows[0]?.id || 0)
}

export async function updateAdminUser(
  userId: number | string,
  patch: AdminUserPatch,
): Promise<AdminUserRow | null> {
  const current = await getAdminUserById(userId)
  if (!current) {
    return null
  }

  const next = { ...current, ...patch }
  const result = await query<{ [column: string]: unknown, id: number }>(
    `
      UPDATE admin_users
      SET
        username = $1,
        password_hash = $2,
        role = $3,
        status = $4,
        updated_at = $5
      WHERE id = $6
      RETURNING id
    `,
    [
      next.username,
      next.password_hash,
      next.role,
      next.status,
      next.updated_at,
      Number(userId),
    ],
  )

  return getAdminUserById(result.rows[0]?.id || 0)
}

export async function listAdminUsers({
  page = 1,
  pageSize = 20,
  username = '',
  role = '',
  status = '',
}: AdminUserListInput = {}): Promise<AdminUserListResult> {
  const offset = (page - 1) * pageSize
  const filters: string[] = []
  const params: unknown[] = []

  if (username) {
    params.push(`%${username}%`)
    filters.push(`username ILIKE $${params.length}`)
  }

  if (role) {
    params.push(role)
    filters.push(`role = $${params.length}`)
  }

  if (status) {
    params.push(status)
    filters.push(`status = $${params.length}`)
  }

  const whereClause = filters.length > 0 ? `WHERE ${filters.join(' AND ')}` : ''
  const totalResult = await query<{ [column: string]: unknown, total: number }>(
    `SELECT COUNT(*)::int AS total FROM admin_users ${whereClause}`,
    params,
  )

  params.push(pageSize)
  params.push(offset)
  const itemsResult = await query<AdminUserRow>(
    `
      ${ADMIN_USER_SELECT}
      ${whereClause}
      ORDER BY au.id DESC
      LIMIT $${params.length - 1} OFFSET $${params.length}
    `,
    params,
  )

  return {
    items: itemsResult.rows,
    total: Number(totalResult.rows[0]?.total || 0),
  }
}

export async function countActiveAdminUsers(): Promise<number> {
  const result = await query<{ [column: string]: unknown, total: number }>(
    `SELECT COUNT(*)::int AS total FROM admin_users WHERE role = 'admin' AND status = 'active'`,
  )
  return Number(result.rows[0]?.total || 0)
}

export async function replaceAdminUserInventoryGroupBindings(
  userId: number | string,
  inventoryGroupCodes: unknown[] = [],
  timestamp: string,
): Promise<AdminUserRow | null> {
  const normalizedUserId = Number(userId)
  const normalizedCodes = Array.from(new Set((Array.isArray(inventoryGroupCodes) ? inventoryGroupCodes : [])
    .map((value) => String(value || '').trim())
    .filter(Boolean)))

  await withTransaction(async (client) => {
    await client.query(
      'DELETE FROM admin_user_inventory_group_bindings WHERE admin_user_id = $1',
      [normalizedUserId],
    )

    for (const inventoryGroupCode of normalizedCodes) {
      await client.query(
        `
          INSERT INTO admin_user_inventory_group_bindings (
            admin_user_id,
            inventory_group_code,
            created_at,
            updated_at
          ) VALUES ($1, $2, $3, $4)
          ON CONFLICT (admin_user_id, inventory_group_code) DO UPDATE
          SET updated_at = EXCLUDED.updated_at
        `,
        [normalizedUserId, inventoryGroupCode, timestamp, timestamp],
      )
    }
  })

  return getAdminUserById(normalizedUserId)
}
