DROP VIEW IF EXISTS client_projects_production_view;
CREATE VIEW client_projects_production_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
WITH
cte_total_team_and_overall_margin_production_view 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)) AS total_team_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(additional_cost_list_view.additional_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)
                    )
                )
            )
        ) 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)
                    )
                )
            ) * 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
)
SELECT
    projects.id, projects.client_id, projects.start_date, projects.end_date, projects.total_amount,
    projects.discount, projects.tax, projects.invoice_number, projects.invoice_date, projects.due_date,
    projects.name, projects.paid, projects.paid_date, projects.status, projects.type_project, projects.has_editor, 
    projects.has_pm, projects.error_invoices, projects.account_manager_id, projects.deadline_for_client,
    projects.production_status, projects.payment_status, projects.parent_id_project, projects.head_production_id,
    projects.user_id,
    offers.length, offers.language_id,
    cte_total_team_and_overall_margin_production_view.total_project_amount AS total_project_amount,
    cte_total_team_and_overall_margin_production_view.total_team_amount AS total_team_amount,
    cte_total_team_and_overall_margin_production_view.total_volumes AS total_volumes,
    cte_total_team_and_overall_margin_production_view.total_prices AS total_prices,
    cte_total_team_and_overall_margin_production_view.copyscape_cost_project AS copyscape_cost_project,
    cte_total_team_and_overall_margin_production_view.total_additional_costs AS total_additional_costs,
    cte_total_team_and_overall_margin_production_view.overall_margin AS overall_margin,
    cte_total_team_and_overall_margin_production_view.overall_margin_percentage AS overall_margin_percentage
FROM
    projects
LEFT JOIN project_offers offers ON projects.id = offers.project_id
LEFT JOIN cte_total_team_and_overall_margin_production_view ON cte_total_team_and_overall_margin_production_view.project_id = projects.id
WHERE
    type_project IN(1, 3)
GROUP BY
    projects.id;