shorts-api/app/Controllers/Http/OcrDevicesController.ts

import { HttpContextContract } from '@ioc:Adonis/Core/HttpContext'
import PaginationService from 'App/Services/PaginationService'
import { schema } from '@ioc:Adonis/Core/Validator'
import OcrDevice from 'App/Models/OcrDevice'
import OcrRecord from 'App/Models/OcrRecord'
import OcrChannel from 'App/Models/OcrChannel'
import { DateTime } from 'luxon'
import * as console from 'node:console'
import Database from '@ioc:Adonis/Lucid/Database'
import { UserRoles } from 'App/Models/User'
import UserService from 'App/Services/UserService'

export default class OcrDevicesController {
    private paginationService = new PaginationService(OcrDevice)

    public async index({ request, response, auth }: HttpContextContract) {
        try {
            const { page = 1, size = 20, id, deviceId, channel } = request.qs()
            const user = auth.user
            const role = user?.$attributes?.role

            if (!role) {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'Unauthorized access'
                })
            }

            let userChannel: string | undefined

            if (role === UserRoles.Api) {
                userChannel = user!.username
            } else if (['admin', 'operator'].includes(role)) {
                const apiUsers = await UserService.findReferredUsers(user!.id)
                const allowedChannels = apiUsers.map((user) => user.username as string)

                // 如果请求中指定了channel，检查是否在允许的channel列表中
                if (channel) {
                    if (!allowedChannels.includes(channel)) {
                        return response.ok({
                            data: [],
                            meta: {
                                total: 0,
                                per_page: Number(size),
                                current_page: Number(page),
                                last_page: 0,
                                first_page: 1,
                                first_page_url: '/?page=1',
                                last_page_url: '/?page=0',
                                next_page_url: null,
                                previous_page_url: null
                            }
                        })
                    }
                    userChannel = channel
                } else {
                    userChannel = allowedChannels.join(',')
                }
            } else {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'You are not authorized to access this resource'
                })
            }

            // 构建查询条件
            const query = OcrDevice.query()

            if (id) {
                query.where('id', id)
            }

            if (deviceId) {
                query.where('id', deviceId)
            }

            if (userChannel) {
                query.whereIn('channel', userChannel.split(','))
            }

            // 执行分页查询
            const result = await query.orderBy('created_at', 'desc').paginate(page, size)

            return response.ok(result)
        } catch (error) {
            return response.internalServerError({
                message: '获取OCR设备列表时发生错误',
                error: error.message
            })
        }
    }

    public async store({ request, bouncer }: HttpContextContract) {
        // await bouncer.authorize('admin')
        const data = await request.validate({
            schema: schema.create({
                id: schema.string(),
                platform: schema.string(),
                channel: schema.string(),
                deviceInfo: schema.string.optional(),
                total: schema.number(),
                scanned: schema.number(),
                ipAddress: schema.string.optional()
            })
        })
        const clientIp = request.ip()
        if (!data.ipAddress) {
            data.ipAddress = clientIp
        }
        const device = await OcrDevice.findBy('id', data.id)
        let ocrDevice: OcrDevice
        if (device) {
            device.merge(data)
            ocrDevice = await device.save()
        } else {
            ocrDevice = await OcrDevice.create(data)
        }
        // 更新渠道统计数据
        await this.updateNums(data.channel)
        return ocrDevice
    }

    private async updateNums(channel: string) {
        if (channel) {
            const ocrChannel = await OcrChannel.findBy('name', channel)
            if (ocrChannel) {
                const deviceCount = await OcrDevice.query()
                    .where('channel', channel)
                    .count('* as total')
                const recordCount = await OcrRecord.query()
                    .where('channel', channel)
                    .count('* as total')
                const scanSum = await OcrDevice.query()
                    .where('channel', channel)
                    .sum('scanned as total')

                ocrChannel.deviceNum = Number(deviceCount[0].$extras.total || 0)
                ocrChannel.recordNum = Number(recordCount[0].$extras.total || 0)
                ocrChannel.scanNum = Number(scanSum[0].$extras.total || 0)

                await ocrChannel.save()
            }
        }
    }

    public async show({ params, bouncer }: HttpContextContract) {
        await bouncer.authorize('admin')
        return await OcrDevice.findOrFail(params.id)
    }

    public async plusTotal({ request, response }: HttpContextContract) {
        try {
            const device = await OcrDevice.findBy('id', request.param('id'))
            if (!device) {
                return response.notFound({ message: `未找到ID为 ${request.param('id')} 的OCR设备` })
            }
            device.total += 1
            await device.save()
            return response.ok(device)
        } catch (error) {
            return response.internalServerError({
                message: '更新设备记录数量时发生错误',
                error: error.message
            })
        }
    }

    public async plusScanned({ request, response }: HttpContextContract) {
        const scanCount = Number(request.param('scanCount'))
        if (isNaN(scanCount)) {
            return response.badRequest({ message: 'scanCount 参数必须是有效数字' })
        }

        try {
            const device = await OcrDevice.findBy('id', request.param('id'))
            if (!device) {
                return response.notFound({
                    message: `未找到 ID 为 ${request.param('id')} 的 OCR 设备`
                })
            }

            device.scanned += scanCount
            await device.save()

            return response.ok(device)
        } catch (error) {
            return response.internalServerError({
                message: '更新设备扫描数量时发生错误',
                error: error.message
            })
        }
    }

    public async getStatistics({ request, response, auth }: HttpContextContract) {
        try {
            const user = auth.user
            const role = user?.$attributes?.role

            if (!role) {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'Unauthorized access'
                })
            }

            let userChannel: string | undefined

            if (role === UserRoles.Api) {
                userChannel = user!.username
            } else if (['admin', 'operator'].includes(role)) {
                const apiUsers = await UserService.findReferredUsers(user!.id)
                const allowedChannels = apiUsers.map((user) => user.username as string)

                // 如果请求中指定了channel，检查是否在允许的channel列表中
                const requestChannel = request.input('channel')
                if (requestChannel) {
                    if (!allowedChannels.includes(requestChannel)) {
                        return response.ok({
                            dates: [],
                            total: [],
                            scanned: [],
                            deviceCount: []
                        })
                    }
                    userChannel = requestChannel
                } else {
                    userChannel = allowedChannels.join(',')
                }
            } else {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'You are not authorized to access this resource'
                })
            }

            // 获取开始日期和结束日期，默认为不包括今天的最近七天
            let startDate = request.input(
                'startDate',
                DateTime.now().minus({ days: 7 }).startOf('day').toFormat('yyyy-MM-dd HH:mm:ss')
            )
            let endDate = request.input(
                'endDate',
                DateTime.now().minus({ days: 1 }).endOf('day').toFormat('yyyy-MM-dd HH:mm:ss')
            )

            // 处理可能的不同日期格式
            let startDateTime: DateTime
            let endDateTime: DateTime

            // 尝试解析开始日期
            if (startDate.includes(' ')) {
                // 如果包含空格，假设是完整的日期时间格式
                startDateTime = DateTime.fromFormat(startDate, 'yyyy-MM-dd HH:mm:ss')
                if (!startDateTime.isValid) {
                    // 尝试其他可能的格式
                    startDateTime = DateTime.fromISO(startDate)
                }
            } else {
                // 只有日期部分
                startDateTime = DateTime.fromFormat(startDate, 'yyyy-MM-dd').startOf('day')
                startDate = startDateTime.toFormat('yyyy-MM-dd HH:mm:ss')
            }

            // 尝试解析结束日期
            if (endDate.includes(' ')) {
                // 如果包含空格，假设是完整的日期时间格式
                endDateTime = DateTime.fromFormat(endDate, 'yyyy-MM-dd HH:mm:ss')
                if (!endDateTime.isValid) {
                    // 尝试其他可能的格式
                    endDateTime = DateTime.fromISO(endDate)
                }
            } else {
                // 只有日期部分，设置为当天结束
                endDateTime = DateTime.fromFormat(endDate, 'yyyy-MM-dd').endOf('day')
                endDate = endDateTime.toFormat('yyyy-MM-dd HH:mm:ss')
            }

            // 验证日期是否有效
            if (!startDateTime.isValid || !endDateTime.isValid) {
                return response.badRequest({
                    message: '无效的日期格式',
                    details: {
                        startDate: startDateTime.isValid ? '有效' : '无效',
                        endDate: endDateTime.isValid ? '有效' : '无效'
                    }
                })
            }

            // 计算日期之间的天数差
            const diffInDays = Math.max(1, Math.ceil(endDateTime.diff(startDateTime, 'days').days))

            console.log('日期调试信息:', {
                startDate,
                endDate,
                startDateTime: startDateTime.toISO(),
                endDateTime: endDateTime.toISO(),
                diffInDays
            })

            // 生成日期数组
            const dates: string[] = []
            for (let i = 0; i < diffInDays; i++) {
                const date = startDateTime.plus({ days: i }).toFormat('yyyy-MM-dd')
                dates.push(date)
            }

            // 准备查询条件
            const deviceStatsQuery = Database.from('ocr_devices')
                .select(
                    Database.raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date"),
                    Database.raw('COUNT(id) as device_count'),
                    Database.raw('SUM(scanned) as scanned_count')
                )
                .whereBetween('created_at', [startDate, endDate])
                .whereIn('channel', userChannel!.split(','))
                .groupBy('date')
                .orderBy('date', 'asc')

            const deviceStats = await deviceStatsQuery

            // 使用SQL直接获取每日记录统计数据
            const recordStatsQuery = Database.from('ocr_records')
                .select(
                    Database.raw("DATE_FORMAT(created_at, '%Y-%m-%d') as date"),
                    Database.raw('COUNT(id) as record_count')
                )
                .whereBetween('created_at', [startDate, endDate])
                .whereIn('channel', userChannel!.split(','))
                .groupBy('date')
                .orderBy('date', 'asc')

            const recordStats = await recordStatsQuery

            // 合并结果
            const dailyStats = {}

            // 初始化所有日期的统计数据
            dates.forEach((date) => {
                dailyStats[date] = {
                    total: 0,
                    scanned: 0,
                    deviceCount: 0
                }
            })

            // 填充设备统计数据
            deviceStats.forEach((stat) => {
                if (dailyStats[stat.date]) {
                    dailyStats[stat.date].scanned = Number(stat.scanned_count) || 0
                    dailyStats[stat.date].deviceCount = Number(stat.device_count) || 0
                }
            })

            // 填充记录统计数据
            recordStats.forEach((stat) => {
                if (dailyStats[stat.date]) {
                    dailyStats[stat.date].total = Number(stat.record_count) || 0
                }
            })

            // 转换为前端需要的数组格式
            const totals = dates.map((date) => dailyStats[date].total)
            const scanned = dates.map((date) => dailyStats[date].scanned)
            const deviceCounts = dates.map((date) => dailyStats[date].deviceCount)

            return response.ok({
                dates,
                total: totals,
                scanned: scanned,
                deviceCount: deviceCounts
            })
        } catch (error) {
            return response.internalServerError({
                message: '获取设备统计数据时发生错误',
                error: error.message
            })
        }
    }

    public async getTodayStatistics({ request, response, auth }: HttpContextContract) {
        try {
            const user = auth.user
            const role = user?.$attributes?.role

            if (!role) {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'Unauthorized access'
                })
            }

            let userChannel: string | undefined

            if (role === UserRoles.Api) {
                userChannel = user!.username
            } else if (['admin', 'operator'].includes(role)) {
                const apiUsers = await UserService.findReferredUsers(user!.id)
                const allowedChannels = apiUsers.map((user) => user.username as string)

                // 如果请求中指定了channel，检查是否在允许的channel列表中
                const requestChannel = request.input('channel')
                if (requestChannel) {
                    if (!allowedChannels.includes(requestChannel)) {
                        return response.ok({
                            date: request.input('date', DateTime.now().toFormat('yyyy-MM-dd')),
                            total: 0,
                            scanned: 0,
                            deviceCount: 0
                        })
                    }
                    userChannel = requestChannel
                } else {
                    userChannel = allowedChannels.join(',')
                }
            } else {
                return response.forbidden({
                    error: 'Forbidden',
                    message: 'You are not authorized to access this resource'
                })
            }

            // 获取指定日期的数据，默认为今天
            const targetDate = request.input('date', DateTime.now().toFormat('yyyy-MM-dd'))
            const dayStart = DateTime.fromFormat(targetDate, 'yyyy-MM-dd').startOf('day').toSQL()
            const dayEnd = DateTime.fromFormat(targetDate, 'yyyy-MM-dd').endOf('day').toSQL()

            // 获取设备数据
            const deviceData = await Database.from('ocr_devices')
                .where('created_at', '>=', dayStart)
                .where('created_at', '<=', dayEnd)
                .whereIn('channel', userChannel!.split(','))
                .select('scanned')

            // 获取OcrRecord数据
            const recordCount = await Database.from('ocr_records')
                .where('created_at', '>=', dayStart)
                .where('created_at', '<=', dayEnd)
                .whereIn('channel', userChannel!.split(','))
                .count('* as total')

            // 计算统计数据
            const scanned = deviceData.reduce((acc, item) => acc + Number(item.scanned || 0), 0)
            const total = Number(recordCount[0].total) || 0
            const deviceCount = deviceData.length

            return response.ok({
                date: targetDate,
                total,
                scanned,
                deviceCount
            })
        } catch (error) {
            return response.internalServerError({
                message: '获取统计数据时发生错误',
                error: error.message
            })
        }
    }
}