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_volumes,
 total_prices,
 copyscape_cost_project,
 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,
    prices_categories_project_list_view.total_volumes AS total_volumes,
    prices_categories_project_list_view.total_prices AS total_prices,
    COALESCE(copy_space_project_list_view.copyscape_cost_project, 0) AS copyscape_cost_project,
    (
        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  ( 
    SELECT 
        offer_id, SUM(gross_amount) AS total_project_amount
    FROM
        offer_partitipants
    GROUP BY
        offer_id
) AS team_amount_project_list_view ON team_amount_project_list_view.offer_id = offers.id
   LEFT JOIN (
        SELECT
            project_id, SUM(volume) AS total_volumes, SUM(price) AS total_prices
        FROM
            project_categories
        GROUP BY
            project_id
   ) AS prices_categories_project_list_view ON prices_categories_project_list_view.project_id = projects.id
LEFT JOIN (
    SELECT
        project_offers.project_id, SUM(gross_amount + reviser_gross_amount + editor_gross_amount + project_manager_gross_amount) AS total_team_amount
    FROM
        project_offers
    GROUP BY
        project_offers.project_id
) AS team_offers_project_list_view ON team_offers_project_list_view.project_id = projects.id
LEFT JOIN (
    SELECT
        topics.project_id, ROUND(SUM(articles.copyscape_cost_project), 2) AS copyscape_cost_project
    FROM
        topics
    INNER JOIN offer_participant_deliveries ON offer_participant_deliveries.topic_id = topics.id
    INNER JOIN articles ON articles.offer_participant_delivery_id = offer_participant_deliveries.id
    GROUP BY
        topics.project_id
) AS copy_space_project_list_view ON copy_space_project_list_view.project_id = projects.id
LEFT JOIN (
    SELECT
        project_id, ROUND(COALESCE(SUM(amount), 0), 2) AS additional_costs
    FROM
        project_additional_costs
    GROUP BY
        project_id
) AS additional_cost_list_view ON additional_cost_list_view.project_id = projects.id
LEFT JOIN (
    SELECT
        project_admin_offers.project_id, sum(project_admin_offers.price_per_article * po.volume ) as total_admin_amount
    FROM
        project_admin_offers 
        INNER JOIN project_offers as po on po.project_id = project_admin_offers.project_id
    GROUP BY project_admin_offers.project_id
) AS total_admin_amout_view ON total_admin_amout_view.project_id = projects.id
LEFT JOIN (
    SELECT
        projects.parent_id_project,
        SUM(COALESCE(team_offers_project_list_view.total_team_amount, 0 ) + COALESCE(total_admin_amout_view.total_admin_amount, 0))  as total_team_cost,
        SUM(COALESCE(copy_space_project_list_view.copyscape_cost_project,0 )) as total_cost_copy_scapes,
        SUM(COALESCE(additional_cost_list_view.additional_costs, 0)) as total_cost_extra,
        SUM(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)) as total_costs
    FROM
        projects
    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
    where projects.parent_id_project > 0
    GROUP BY
        projects.parent_id_project
) AS total_team_and_overall_margin_child_view on total_team_and_overall_margin_child_view.parent_id_project = projects.id
GROUP BY
    projects.id;