<?php

declare(strict_types=1);

namespace App\Support;

/**
 * 单据综合查询视图 SQL（与 {@see DberpDocumentQuerySourceUrlResolver} 的 row_id 分段需保持一致）。
 */
final class DberpDocumentQueryViewDefinition
{
    public static function sql(): string
    {
        return <<<'SQL'
CREATE VIEW dberp_document_queries AS
SELECT
    (1 * 1000000000) + id AS row_id,
    'other_stock_in' AS document_type,
    code,
    status,
    business_time,
    (
        SELECT COALESCE(SUM(quantity), 0)
        FROM dberp_other_stock_in_order_items
        WHERE dberp_other_stock_in_order_items.order_id = dberp_other_stock_in_orders.id
    ) AS total_quantity,
    NULL AS total_amount,
    NULL AS customer_id,
    NULL AS supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    NULL AS handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_other_stock_in_orders
UNION ALL
SELECT
    (2 * 1000000000) + id AS row_id,
    'other_stock_out' AS document_type,
    code,
    status,
    business_time,
    (
        SELECT COALESCE(SUM(quantity), 0)
        FROM dberp_other_stock_out_order_items
        WHERE dberp_other_stock_out_order_items.order_id = dberp_other_stock_out_orders.id
    ) AS total_quantity,
    NULL AS total_amount,
    NULL AS customer_id,
    NULL AS supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    NULL AS handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_other_stock_out_orders
UNION ALL
SELECT
    (3 * 1000000000) + id AS row_id,
    'transfer_order' AS document_type,
    code,
    status,
    business_time,
    (
        SELECT COALESCE(SUM(COALESCE(base_quantity, quantity)), 0)
        FROM dberp_transfer_order_items
        WHERE dberp_transfer_order_items.order_id = dberp_transfer_orders.id
    ) AS total_quantity,
    NULL AS total_amount,
    NULL AS customer_id,
    NULL AS supplier_id,
    from_warehouse_id AS warehouse_id,
    to_warehouse_id AS to_warehouse_id,
    NULL AS handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_transfer_orders
UNION ALL
SELECT
    (4 * 1000000000) + id AS row_id,
    'stocktake_order' AS document_type,
    code,
    status,
    business_time,
    NULL AS total_quantity,
    NULL AS total_amount,
    NULL AS customer_id,
    NULL AS supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    stocktake_employee_id AS handler_employee_id,
    scope_type AS source_type,
    remark
FROM dberp_stocktake_orders
UNION ALL
SELECT
    (5 * 1000000000) + id AS row_id,
    'purchase_order' AS document_type,
    code,
    status,
    business_time,
    total_quantity,
    total_amount,
    NULL AS customer_id,
    supplier_id,
    (
        SELECT MIN(requested_warehouse_id)
        FROM dberp_purchase_order_items
        WHERE order_id = dberp_purchase_orders.id
          AND requested_warehouse_id IS NOT NULL
    ) AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_purchase_orders
UNION ALL
SELECT
    (6 * 1000000000) + id AS row_id,
    'purchase_receipt' AS document_type,
    code,
    status,
    business_time,
    total_received_quantity AS total_quantity,
    total_amount,
    NULL AS customer_id,
    supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_purchase_receipt_orders
UNION ALL
SELECT
    (7 * 1000000000) + id AS row_id,
    'purchase_return' AS document_type,
    code,
    status,
    business_time,
    total_return_quantity AS total_quantity,
    total_amount,
    NULL AS customer_id,
    supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    NULL AS source_type,
    remark
FROM dberp_purchase_return_orders
UNION ALL
SELECT
    (8 * 1000000000) + id AS row_id,
    'sales_order' AS document_type,
    code,
    status,
    business_time,
    total_quantity,
    final_amount AS total_amount,
    customer_id,
    NULL AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    source_type,
    remark
FROM dberp_sales_orders
UNION ALL
SELECT
    (9 * 1000000000) + id AS row_id,
    'sales_outbound' AS document_type,
    code,
    status,
    business_time,
    total_shipped_quantity AS total_quantity,
    total_amount,
    customer_id,
    NULL AS supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    source_type,
    remark
FROM dberp_sales_outbound_orders
UNION ALL
SELECT
    (10 * 1000000000) + id AS row_id,
    'sales_return' AS document_type,
    code,
    status,
    business_time,
    total_return_quantity AS total_quantity,
    total_amount,
    customer_id,
    NULL AS supplier_id,
    warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    source_type,
    remark
FROM dberp_sales_return_orders
UNION ALL
SELECT
    (11 * 1000000000) + id AS row_id,
    'receipt_order' AS document_type,
    code,
    status,
    business_time,
    NULL AS total_quantity,
    amount AS total_amount,
    customer_id,
    NULL AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    'sales_order' AS source_type,
    remark
FROM dberp_receipt_orders
UNION ALL
SELECT
    (12 * 1000000000) + id AS row_id,
    'payment_order' AS document_type,
    code,
    status,
    business_time,
    NULL AS total_quantity,
    amount AS total_amount,
    NULL AS customer_id,
    supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    'purchase_order' AS source_type,
    remark
FROM dberp_payment_orders
UNION ALL
SELECT
    (13 * 1000000000) + id AS row_id,
    'purchase_request' AS document_type,
    code,
    status,
    request_time AS business_time,
    total_quantity,
    NULL AS total_amount,
    NULL AS customer_id,
    suggested_supplier_id AS supplier_id,
    (
        SELECT MIN(warehouse_id)
        FROM dberp_purchase_request_items
        WHERE request_id = dberp_purchase_requests.id
          AND warehouse_id IS NOT NULL
    ) AS warehouse_id,
    NULL AS to_warehouse_id,
    applicant_employee_id AS handler_employee_id,
    source_type,
    remark
FROM dberp_purchase_requests
UNION ALL
SELECT
    (14 * 1000000000) + id AS row_id,
    'sales_quote' AS document_type,
    code,
    status,
    quote_date AS business_time,
    total_quantity,
    final_amount AS total_amount,
    customer_id,
    NULL AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    source_system AS source_type,
    remark
FROM dberp_sales_quotes
UNION ALL
SELECT
    (15 * 1000000000) + id AS row_id,
    'settlement_write_off' AS document_type,
    code,
    write_off_status AS status,
    business_date AS business_time,
    NULL AS total_quantity,
    effective_amount AS total_amount,
    CASE WHEN counterparty_type = 'customer' THEN counterparty_id ELSE NULL END AS customer_id,
    CASE WHEN counterparty_type = 'supplier' THEN counterparty_id ELSE NULL END AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    settlement_side AS source_type,
    remark
FROM dberp_settlement_write_offs
UNION ALL
SELECT
    (16 * 1000000000) + id AS row_id,
    'settlement_statement' AS document_type,
    code,
    statement_status AS status,
    statement_date AS business_time,
    NULL AS total_quantity,
    closing_balance AS total_amount,
    CASE WHEN counterparty_type = 'customer' THEN counterparty_id ELSE NULL END AS customer_id,
    CASE WHEN counterparty_type = 'supplier' THEN counterparty_id ELSE NULL END AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    settlement_side AS source_type,
    remark
FROM dberp_settlement_statements
UNION ALL
SELECT
    (17 * 1000000000) + id AS row_id,
    'settlement_adjustment' AS document_type,
    code,
    status,
    business_date AS business_time,
    NULL AS total_quantity,
    effective_amount AS total_amount,
    CASE WHEN counterparty_type = 'customer' THEN counterparty_id ELSE NULL END AS customer_id,
    CASE WHEN counterparty_type = 'supplier' THEN counterparty_id ELSE NULL END AS supplier_id,
    NULL AS warehouse_id,
    NULL AS to_warehouse_id,
    handler_employee_id,
    adjustment_type AS source_type,
    remark
FROM dberp_settlement_adjustments
SQL;
    }
}
