DROP VIEW IF EXISTS historical_total_team_and_overall_margin_view;
CREATE VIEW historical_total_team_and_overall_margin_view (
 project_id,
 total_project_amount,
 total_team_amount,
 overall_margin,
 overall_margin_percentage
) as
SELECT
    historical_projects.id as project_id,
    team_amount_historical_project_list_view.total_project_amount,
    (
        team_offers_historical_project_list_view.total_team_amount + COALESCE(
            copy_space_historical_project_list_view.copyscape_cost_project,
            0
        ) + COALESCE(
            additional_cost_historical_project_list_view.additional_costs,
            0
        )
    ) AS total_team_amount,
    (
        historical_projects.total_amount -(
            team_offers_historical_project_list_view.total_team_amount + COALESCE(
                copy_space_historical_project_list_view.copyscape_cost_project,
                0
            ) + COALESCE(
                additional_cost_historical_project_list_view.additional_costs,
                0
            )
        )
    ) AS overall_margin,
    (
        (
            historical_projects.total_amount -(
                team_offers_historical_project_list_view.total_team_amount + COALESCE(
                    copy_space_historical_project_list_view.copyscape_cost_project,
                    0
                ) + COALESCE(
                    additional_cost_historical_project_list_view.additional_costs,
                    0
                )
            )
        ) * 100
    ) / historical_projects.total_amount AS overall_margin_percentage
FROM
    historical_projects
LEFT JOIN historical_project_offers offers ON historical_projects.id = offers.historical_project_id
LEFT JOIN team_amount_historical_project_list_view ON team_amount_historical_project_list_view.historical_offer_id = offers.id
LEFT JOIN prices_categories_historical_project_list_view ON prices_categories_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN team_offers_historical_project_list_view ON team_offers_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN copy_space_historical_project_list_view ON copy_space_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN additional_cost_historical_project_list_view ON additional_cost_historical_project_list_view.historical_project_id = historical_projects.id
GROUP BY
    historical_projects.id