leaudit-platform-frontend/database/migrations/add_document_version_management_v4.sql

-- ============================================
-- 文档版本管理 RPC 函数（第4版 - 显式类型转换）
-- 功能：支持同名文档的版本管理和历史查询
-- 修复：所有字段显式转换为函数返回类型
-- 创建时间：2025-11-14
-- ============================================

-- ====================
-- 删除旧函数
-- ====================
DROP FUNCTION IF EXISTS documents_get_latest_documents_with_version_info(integer,integer,integer,text,text,integer[],integer,text,text,text);
DROP FUNCTION IF EXISTS documents_count_latest_documents_with_filters(integer,text,text,integer[],integer,text,text,text);
DROP FUNCTION IF EXISTS documents_get_document_history(text,integer,integer);


-- ====================
-- 1. 获取最新版本的文档列表（带版本信息）
-- ====================
CREATE FUNCTION documents_get_latest_documents_with_version_info(
  p_user_id integer,
  p_page integer DEFAULT 1,
  p_page_size integer DEFAULT 10,
  p_search_name text DEFAULT NULL,
  p_search_document_number text DEFAULT NULL,
  p_search_document_types integer[] DEFAULT NULL,
  p_search_audit_status integer DEFAULT NULL,
  p_search_file_status text DEFAULT NULL,
  p_search_date_from text DEFAULT NULL,
  p_search_date_to text DEFAULT NULL
)
RETURNS TABLE (
  id integer,
  name text,
  document_number text,
  type_id integer,
  type_name text,
  file_size integer,
  audit_status integer,
  status text,
  false_count bigint,
  created_at timestamp with time zone,
  updated_at timestamp with time zone,
  path text,
  is_test_document boolean,
  ocr_result jsonb,
  history_count bigint,
  previous_issues bigint
) AS $$
BEGIN
  RETURN QUERY
  WITH latest_docs AS (
    -- 使用 DISTINCT ON 获取每个文档名称的最新版本
    SELECT DISTINCT ON (d.name)
      d.id,
      d.name,
      d.user_id,
      d.created_at
    FROM documents d
    WHERE d.user_id = p_user_id
      -- 文档名称搜索
      AND (p_search_name IS NULL OR d.name ILIKE '%' || p_search_name || '%')
      -- 文档编号搜索
      AND (p_search_document_number IS NULL OR d.document_number ILIKE '%' || p_search_document_number || '%')
      -- 文档类型筛选
      AND (p_search_document_types IS NULL OR d.type_id = ANY(p_search_document_types))
      -- 审核状态筛选
      AND (p_search_audit_status IS NULL OR d.audit_status = p_search_audit_status)
      -- 文件状态筛选
      AND (p_search_file_status IS NULL OR d.status = p_search_file_status)
      -- 日期范围筛选
      AND (p_search_date_from IS NULL OR d.created_at >= p_search_date_from::timestamp)
      AND (p_search_date_to IS NULL OR d.created_at <= p_search_date_to::timestamp)
    ORDER BY d.name, d.created_at DESC
  )
  SELECT
    d.id::integer,
    d.name::text,
    d.document_number::text,
    d.type_id::integer,
    COALESCE(dt.name::text, '') as type_name,
    d.file_size::integer,
    d.audit_status::integer,
    d.status::text,
    -- 计算当前文档的问题数量（从 evaluation_results 表统计）
    COALESCE((
      SELECT COUNT(*)
      FROM evaluation_results er
      WHERE er.document_id = d.id
        AND (er.evaluated_results ->> 'result')::text = 'false'
    ), 0)::bigint as false_count,
    d.created_at::timestamp with time zone,
    d.updated_at::timestamp with time zone,
    d.path::text,
    d.is_test_document::boolean,
    d.ocr_result::jsonb,
    -- 计算历史版本数量（不包含当前版本）
    COALESCE((
      SELECT COUNT(*)
      FROM documents d2
      WHERE d2.name = d.name
        AND d2.user_id = d.user_id
        AND d2.id != d.id
    ), 0)::bigint as history_count,
    -- 获取上一个版本的问题数量
    COALESCE((
      SELECT COUNT(*)
      FROM evaluation_results er2
      WHERE er2.document_id = (
        SELECT d3.id
        FROM documents d3
        WHERE d3.name = d.name
          AND d3.user_id = d.user_id
          AND d3.created_at < d.created_at
        ORDER BY d3.created_at DESC
        LIMIT 1
      )
      AND (er2.evaluated_results ->> 'result')::text = 'false'
    ), 0)::bigint as previous_issues
  FROM documents d
  INNER JOIN latest_docs ld ON d.id = ld.id
  LEFT JOIN document_types dt ON d.type_id = dt.id
  ORDER BY d.created_at DESC
  LIMIT p_page_size OFFSET (p_page - 1) * p_page_size;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION documents_get_latest_documents_with_version_info IS '获取最新版本的文档列表，包含历史版本数量和上一个版本的问题数量（从 evaluation_results 表计算）';


-- ====================
-- 2. 统计最新版本文档的总数
-- ====================
CREATE FUNCTION documents_count_latest_documents_with_filters(
  p_user_id integer,
  p_search_name text DEFAULT NULL,
  p_search_document_number text DEFAULT NULL,
  p_search_document_types integer[] DEFAULT NULL,
  p_search_audit_status integer DEFAULT NULL,
  p_search_file_status text DEFAULT NULL,
  p_search_date_from text DEFAULT NULL,
  p_search_date_to text DEFAULT NULL
)
RETURNS integer AS $$
DECLARE
  doc_count integer;
BEGIN
  WITH latest_docs AS (
    SELECT DISTINCT ON (d.name)
      d.id
    FROM documents d
    WHERE d.user_id = p_user_id
      AND (p_search_name IS NULL OR d.name ILIKE '%' || p_search_name || '%')
      AND (p_search_document_number IS NULL OR d.document_number ILIKE '%' || p_search_document_number || '%')
      AND (p_search_document_types IS NULL OR d.type_id = ANY(p_search_document_types))
      AND (p_search_audit_status IS NULL OR d.audit_status = p_search_audit_status)
      AND (p_search_file_status IS NULL OR d.status = p_search_file_status)
      AND (p_search_date_from IS NULL OR d.created_at >= p_search_date_from::timestamp)
      AND (p_search_date_to IS NULL OR d.created_at <= p_search_date_to::timestamp)
    ORDER BY d.name, d.created_at DESC
  )
  SELECT COUNT(*)::integer INTO doc_count FROM latest_docs;

  RETURN doc_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION documents_count_latest_documents_with_filters IS '统计符合条件的最新版本文档总数（用于分页）';


-- ====================
-- 3. 获取文档的历史版本列表
-- ====================
CREATE FUNCTION documents_get_document_history(
  p_document_name text,
  p_user_id integer,
  p_exclude_id integer
)
RETURNS TABLE (
  id integer,
  name text,
  document_number text,
  type_id integer,
  type_name text,
  file_size integer,
  audit_status integer,
  status text,
  false_count bigint,
  created_at timestamp with time zone,
  updated_at timestamp with time zone,
  path text,
  is_test_document boolean,
  ocr_result jsonb
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    d.id::integer,
    d.name::text,
    d.document_number::text,
    d.type_id::integer,
    COALESCE(dt.name::text, '') as type_name,
    d.file_size::integer,
    d.audit_status::integer,
    d.status::text,
    -- 计算每个历史版本的问题数量
    COALESCE((
      SELECT COUNT(*)
      FROM evaluation_results er
      WHERE er.document_id = d.id
        AND (er.evaluated_results ->> 'result')::text = 'false'
    ), 0)::bigint as false_count,
    d.created_at::timestamp with time zone,
    d.updated_at::timestamp with time zone,
    d.path::text,
    d.is_test_document::boolean,
    d.ocr_result::jsonb
  FROM documents d
  LEFT JOIN document_types dt ON d.type_id = dt.id
  WHERE d.name = p_document_name
    AND d.user_id = p_user_id
    AND d.id != p_exclude_id
  ORDER BY d.created_at DESC;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION documents_get_document_history IS '获取指定文档名称的所有历史版本（不包含指定的当前版本ID），从 evaluation_results 表计算问题数量';


-- ====================
-- 4. 创建索引优化查询性能
-- ====================

-- 复合索引：user_id + name + created_at
CREATE INDEX IF NOT EXISTS idx_documents_user_name_created
ON documents(user_id, name, created_at DESC);

-- 复合索引：name + user_id + created_at
CREATE INDEX IF NOT EXISTS idx_documents_name_user_created
ON documents(name, user_id, created_at DESC);

-- 单列索引：created_at
CREATE INDEX IF NOT EXISTS idx_documents_created_at
ON documents(created_at DESC);

-- 单列索引：status
CREATE INDEX IF NOT EXISTS idx_documents_status
ON documents(status);

-- evaluation_results 的 document_id
CREATE INDEX IF NOT EXISTS idx_evaluation_results_document_id
ON evaluation_results(document_id);

-- evaluation_results 的 evaluated_results->>'result'
CREATE INDEX IF NOT EXISTS idx_evaluation_results_result
ON evaluation_results((evaluated_results ->> 'result'));