-- 资产操作审计日志手工验收 SQL
-- 表：tb_asset_operation_log

-- 1) 表结构与索引核验
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name = 'tb_asset_operation_log';

SELECT indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename = 'tb_asset_operation_log'
ORDER BY indexname;

-- 2) 最近 50 条审计日志
SELECT id,
       created_at,
       operator_type,
       operator_id,
       asset_type,
       asset_id,
       asset_identifier,
       operation_type,
       result_status,
       batch_total,
       success_count,
       fail_count
FROM tb_asset_operation_log
ORDER BY id DESC
LIMIT 50;

-- 3) success / failed / denied 三态分布
SELECT operation_type, result_status, COUNT(*) AS cnt
FROM tb_asset_operation_log
GROUP BY operation_type, result_status
ORDER BY operation_type, result_status;

-- 4) before/after 镜像完整性核验（近 200 条）
SELECT id,
       operation_type,
       result_status,
       (before_data IS NOT NULL) AS has_before,
       (after_data IS NOT NULL)  AS has_after
FROM tb_asset_operation_log
ORDER BY id DESC
LIMIT 200;

-- 5) 批量聚合字段核验（batch_total/success_count/fail_count）
SELECT id,
       operation_type,
       batch_total,
       success_count,
       fail_count,
       (batch_total >= success_count + fail_count) AS counter_ok
FROM tb_asset_operation_log
WHERE batch_total > 0
ORDER BY id DESC
LIMIT 200;

-- 6) 系统触发语义核验
SELECT id,
       operation_type,
       operator_type,
       operator_id,
       operator_name,
       result_status
FROM tb_asset_operation_log
WHERE operation_type IN ('card_auto_stop', 'card_auto_start')
ORDER BY id DESC
LIMIT 100;

-- 7) WiFi 脱敏核验（不应出现明文密码）
SELECT id,
       operation_type,
       after_data
FROM tb_asset_operation_log
WHERE operation_type = 'device_set_wifi'
ORDER BY id DESC
LIMIT 20;

-- 8) 拒绝与失败热点排查
SELECT operation_type,
       result_status,
       COUNT(*) AS cnt
FROM tb_asset_operation_log
WHERE result_status IN ('failed', 'denied')
GROUP BY operation_type, result_status
ORDER BY cnt DESC, operation_type;