-- Create or replace a view that links orders to product segments, customers, and milestone-based revenue/cost
CREATE OR REPLACE VIEW vw_orders_details AS
SELECT
    order_number,                                      -- Unique order identifier
    order_details.project_id,                          -- Project ID from the order
    contract_master.contract_id,                       -- Contract ID linked from the contract master table
    customer_list.customer_name,                       -- Customer name from the customer list
    contract_master.customer_number,                   -- Customer number (for joins and reference)
    customer_list.customer_segment,                    -- Segment classification of the customer
    order_details.product,                             -- Product name from the order
    product_list.segment,                              -- Segment classification of the product (e.g., Hardware, Software)
    date_sold,                                         -- Date the order was sold
    SUM(milestone_tracking.Revenue) AS "Total Milestone Revenue", -- Total revenue from milestones
    SUM(milestone_tracking.Cost)    AS "Total Milestone Cost"     -- Total cost from milestones

FROM order_details
LEFT JOIN contract_master
    ON order_details.project_id = contract_master.project_number
LEFT JOIN product_list
    ON order_details.product = product_list.product
LEFT JOIN customer_list
    ON contract_master.customer_number = customer_list.customer_number
LEFT JOIN milestone_tracking
    ON order_details.project_id = milestone_tracking.project_id
    AND order_details.product_id = milestone_tracking.product_id

GROUP BY 
    order_number,
    order_details.project_id, 
    contract_master.contract_id,
    contract_master.customer_number,
    customer_list.customer_name,    
    customer_list.customer_segment, 
    order_details.product,
    product_list.segment,
    date_sold;

-- View the resulting table
SELECT * FROM vw_orders_details;