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
),

delivered_dn_per_shipment AS (
    SELECT
        s.company_id,
        DATE(s.created_at) AS date,
        COUNT(DISTINCT sdn.id) AS dn_count
    FROM shipments s
    JOIN shipment_customers sc 
        ON sc.shipment_id = s.id
    JOIN shipment_location sl 
        ON sl.shipment_customer_uid = sc.shipment_customer_uid
    JOIN shipment_delivery_notes sdn 
        ON sdn.shipment_location_uid = sl.shipment_location_uid
    JOIN shipment_status ss 
        ON ss.id = s.shipment_status_id
    WHERE
        s.company_id = 1
        AND s.status_id != 3
        AND ss.name = 'Delivered'
        AND s.deleted_at IS NULL
    GROUP BY s.company_id, DATE(s.created_at), s.id
),

dn_delivered_daily AS (
    SELECT
        company_id,
        date,
        SUM(dn_count) AS no_of_dn_delivered
    FROM delivered_dn_per_shipment
    GROUP BY company_id, date
),

dn_received AS (
    SELECT
        company_id,
        DATE(created_at) AS date,
        COUNT(id) AS no_of_dn_received
    FROM delivery_notes
    WHERE
        deleted_at IS NULL
        AND company_id = 1
    GROUP BY company_id, DATE(created_at)
)

SELECT
    1 AS company_id,
    1 AS status_id,
    l.date,
    COALESCE(dd.no_of_dn_delivered, 0) AS no_of_dn_delivered,
    COALESCE(dr.no_of_dn_received, 0) AS no_of_dn_received
FROM last_5_days l
LEFT JOIN dn_delivered_daily dd 
    ON dd.date = l.date
LEFT JOIN dn_received dr 
    ON dr.date = l.date
ORDER BY l.date DESC;
