CREATE OR REPLACE VIEW view_shipments_delivery_notes_summary AS
WITH last_5_days AS (
    SELECT generate_series(
        CURRENT_DATE - INTERVAL '4 days',
        CURRENT_DATE,
        INTERVAL '1 day'
    )::date AS date
),
shipment_counts AS (
    SELECT
        shipments.company_id,
        shipments.status_id,
        DATE(shipments.created_at) AS date,
        COUNT(
            DISTINCT CASE
                WHEN shipments.shipment_status_id = 6 THEN shipments.id
            END
        ) AS no_of_dn_delivered
    FROM shipments
    WHERE shipments.deleted_at IS NULL
      AND shipments.company_id = 1
      AND shipments.status_id = 1
    GROUP BY
        shipments.company_id,
        shipments.status_id,
        DATE(shipments.created_at)
),
delivery_note_counts AS (
    SELECT
        delivery_notes.company_id,
        DATE(delivery_notes.created_at) AS date,
        COUNT(delivery_notes.id) AS no_of_dn_received
    FROM delivery_notes
    WHERE delivery_notes.deleted_at IS NULL
      AND delivery_notes.company_id = 1
    GROUP BY
        delivery_notes.company_id,
        DATE(delivery_notes.created_at)
)
SELECT
    1 AS company_id,
    1 AS status_id,
    last_5_days.date,
    COALESCE(shipment_counts.no_of_dn_delivered, 0) AS no_of_dn_delivered,
    COALESCE(delivery_note_counts.no_of_dn_received, 0) AS no_of_dn_received
FROM last_5_days
LEFT JOIN shipment_counts
    ON shipment_counts.date = last_5_days.date
LEFT JOIN delivery_note_counts
    ON delivery_note_counts.date = last_5_days.date
ORDER BY last_5_days.date DESC;
