DROP VIEW IF EXISTS total_team_and_overall_margin_view;
CREATE VIEW total_team_and_overall_margin_view (
 project_id,
 parent_id_project,
 total_project_amount,
 total_team_amount,
 total_additional_costs,
 overall_margin,
 overall_margin_percentage
) as
SELECT
 projects.id AS project_id, projects.parent_id_project,
    COALESCE( team_amount_project_list_view.total_project_amount, 0 ) AS total_project_amount,
    
    (
        COALESCE( team_offers_project_list_view.total_team_amount , 0 ) + COALESCE(
            copy_space_project_list_view.copyscape_cost_project,
            0
        ) + COALESCE(total_team_and_overall_margin_child_view.total_team_cost , 0)
    ) AS total_team_amount,
    
    (
        COALESCE(
                additional_cost_list_view.additional_costs,
                0
        ) + COALESCE (total_team_and_overall_margin_child_view.total_costs, 0)
     ) as total_additional_costs,
     (
       (  
         (
            projects.total_amount - (
                COALESCE( team_offers_project_list_view.total_team_amount , 0) + COALESCE( total_admin_amout_view.total_admin_amount,0)  + COALESCE(
                    copy_space_project_list_view.copyscape_cost_project,
                    0
                ) + COALESCE(
                    additional_cost_list_view.additional_costs,
                    0
                )
            )
         ) - ( COALESCE(total_team_and_overall_margin_child_view.total_costs, 0))
          )
        ) AS overall_margin,
        (
           (
            (
                projects.total_amount -(
                    COALESCE( team_offers_project_list_view.total_team_amount, 0 ) + COALESCE( total_admin_amout_view.total_admin_amount,0) + COALESCE(
                        copy_space_project_list_view.copyscape_cost_project,
                        0
                    ) + COALESCE(
                        additional_cost_list_view.additional_costs,
                        0
                    )
                )
            ) - (COALESCE (total_team_and_overall_margin_child_view.total_costs,0 )) 
              ) * 100
        ) / projects.total_amount AS overall_margin_percentage
FROM
    projects
LEFT JOIN project_offers offers ON
    projects.id = offers.project_id
LEFT JOIN team_amount_project_list_view ON team_amount_project_list_view.offer_id = offers.id
LEFT JOIN prices_categories_project_list_view ON prices_categories_project_list_view.project_id = projects.id
LEFT JOIN team_offers_project_list_view ON team_offers_project_list_view.project_id = projects.id
LEFT JOIN copy_space_project_list_view ON copy_space_project_list_view.project_id = projects.id
LEFT JOIN additional_cost_list_view ON additional_cost_list_view.project_id = projects.id
LEFT JOIN total_admin_amout_view ON total_admin_amout_view.project_id = projects.id
LEFT JOIN total_team_and_overall_margin_child_view on total_team_and_overall_margin_child_view.parent_id_project = projects.id
GROUP BY
    projects.id;