iloom-flatten/migrations/20260604_143758_create_integrity_check_views.sql

-- ============================================
-- 此文件由 Meoo Cloud 自动生成，请勿修改
-- This file is auto-generated by Meoo Cloud, DO NOT MODIFY
-- ============================================


CREATE OR REPLACE VIEW v_plan_quantity_check AS
SELECT 
 p.id as plan_id,
 p.plan_code,
 p.status,
 p.completed_quantity,
 COALESCE(SUM(ir.quantity), 0) as inventory_total,
 p.completed_quantity - COALESCE(SUM(ir.quantity), 0) as difference,
 CASE 
 WHEN ABS(p.completed_quantity - COALESCE(SUM(ir.quantity), 0)) <= 0.01 THEN '✓ 一致'
 ELSE '✗ 不一致'
 END as check_result
FROM production_plans p
LEFT JOIN inventory_records ir ON ir.plan_id = p.id
GROUP BY p.id, p.plan_code, p.status, p.completed_quantity;

CREATE OR REPLACE VIEW v_payment_check AS
SELECT 
 id as payment_id,
 plan_id,
 quantity,
 price_per_meter,
 amount,
 quantity * price_per_meter as expected_amount,
 amount - (quantity * price_per_meter) as difference,
 CASE 
 WHEN ABS(amount - (quantity * price_per_meter)) <= 0.01 THEN '✓ 一致'
 ELSE '✗ 不一致'
 END as check_result
FROM payments;

CREATE OR REPLACE VIEW v_accounts_payable_check AS
SELECT 
 ap.id as ap_id,
 ap.plan_id,
 ap.total_amount,
 ap.paid_amount,
 ap.unpaid_amount,
 COALESCE(SUM(api.amount), 0) as items_total,
 COALESCE(SUM(CASE WHEN api.status = 'paid' THEN api.amount ELSE 0 END), 0) as items_paid,
 ap.total_amount - COALESCE(SUM(api.amount), 0) as total_diff,
 ap.paid_amount - COALESCE(SUM(CASE WHEN api.status = 'paid' THEN api.amount ELSE 0 END), 0) as paid_diff,
 CASE 
 WHEN ABS(ap.total_amount - COALESCE(SUM(api.amount), 0)) <= 0.01 
 AND ABS(ap.paid_amount - COALESCE(SUM(CASE WHEN api.status = 'paid' THEN api.amount ELSE 0 END), 0)) <= 0.01
 THEN '✓ 一致'
 ELSE '✗ 不一致'
 END as check_result
FROM accounts_payable ap
LEFT JOIN accounts_payable_items api ON api.accounts_payable_id = ap.id
GROUP BY ap.id, ap.plan_id, ap.total_amount, ap.paid_amount, ap.unpaid_amount;

CREATE OR REPLACE FUNCTION run_data_integrity_check()
RETURNS TABLE (
 check_type TEXT,
 total_records BIGINT,
 inconsistent_records BIGINT,
 error_rate NUMERIC,
 status TEXT
) LANGUAGE plpgsql AS $$
BEGIN
 RETURN QUERY
 SELECT 
 'plan_quantity'::TEXT,
 COUNT(*)::BIGINT,
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::BIGINT,
 ROUND(
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::NUMERIC / 
 NULLIF(COUNT(*), 0) * 100, 4
 ),
 CASE 
 WHEN SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END) = 0 THEN 'PASS'
 ELSE 'FAIL'
 END
 FROM v_plan_quantity_check;
 
 RETURN QUERY
 SELECT 
 'payment_amount'::TEXT,
 COUNT(*)::BIGINT,
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::BIGINT,
 ROUND(
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::NUMERIC / 
 NULLIF(COUNT(*), 0) * 100, 4
 ),
 CASE 
 WHEN SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END) = 0 THEN 'PASS'
 ELSE 'FAIL'
 END
 FROM v_payment_check;
 
 RETURN QUERY
 SELECT 
 'accounts_payable'::TEXT,
 COUNT(*)::BIGINT,
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::BIGINT,
 ROUND(
 SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END)::NUMERIC / 
 NULLIF(COUNT(*), 0) * 100, 4
 ),
 CASE 
 WHEN SUM(CASE WHEN check_result = '✗ 不一致' THEN 1 ELSE 0 END) = 0 THEN 'PASS'
 ELSE 'FAIL'
 END
 FROM v_accounts_payable_check;
END;
$$;