DROP VIEW IF EXISTS client_projects_production_historical_view;
CREATE VIEW client_projects_production_historical_view(
    id,
    client_id,
    start_date,
    end_date,
    total_amount,
    discount,
    tax,
    invoice_number,
    invoice_date,
    due_date,
    name,
    paid,
    paid_date,
	status,
    type_project,
    has_editor,
    has_pm,
    error_invoices,
    account_manager_id,
    deadline_for_client,
    production_status,
    payment_status,
    parent_id_project,
    head_production_id,
    user_id,
    length,
    language_id,
    total_project_amount,
    total_team_amount,
    total_volumes,
    total_prices,
    copyscape_cost_project,
    total_additional_costs,
    overall_margin,
    overall_margin_percentage
) as
SELECT
    historical_projects.id, historical_projects.client_id, historical_projects.start_date, historical_projects.end_date, 
    historical_projects.total_amount, historical_projects.discount, historical_projects.tax,historical_projects.invoice_number, 
    historical_projects.invoice_date, historical_projects.due_date, historical_projects.name, historical_projects.paid,
    historical_projects.paid_date, historical_projects.status, historical_projects.type_project,
    historical_projects.has_editor, historical_projects.has_pm, historical_projects.error_invoices,
    historical_projects.account_manager_id, historical_projects.deadline_for_client,
    historical_projects.production_status, historical_projects.payment_status, historical_projects.parent_id_project,
    historical_projects.head_production_id, historical_projects.user_id,
    offers.length, offers.language_id,
    COALESCE(
        team_amount_historical_project_list_view.total_project_amount,
        0
    ) AS total_project_amount,
    (
        COALESCE(
            team_offers_historical_project_list_view.total_team_amount + copy_space_historical_project_list_view.copyscape_cost_project + additional_cost_historical_project_list_view.additional_costs,
            0
        )
    ) AS total_team_amount,
    prices_categories_historical_project_list_view.total_volumes AS total_volumes,
    prices_categories_historical_project_list_view.total_prices AS total_prices,
    copy_space_historical_project_list_view.copyscape_cost_project AS copyscape_cost_project,
    0 AS total_additional_costs,
    (
        historical_projects.total_amount - (
            COALESCE(team_offers_historical_project_list_view.total_team_amount, 0) + 
            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 - (
                COALESCE(team_offers_historical_project_list_view.total_team_amount, 0) + 
                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
WHERE
    historical_projects.type_project IN(1, 3)
GROUP BY
    historical_projects.id;