DROP VIEW IF EXISTS historical_projects_view;
CREATE VIEW historical_projects_view(
    id,
    client_id,
    start_date,
    end_date,
    total_amount,
    special_offer,
    special_invoice,
    discount,
    tax,
    invoice_number,
    invoice_date,
    due_date,
    notes_ht,
    name,
    paid,
    paid_date,
	status,
    copyscape_enabled,
    project_manager_price,
    project_manager_gross_amount,
    user_id,
    type_project,
    has_author,
    has_reviser,
    has_editor,
    has_pm,
    created_by_platform,
    head_production_id,
    error_invoices,
    ac_chat_visited,
    hp_chat_visited,
    has_translator,
    has_auto_reassign,
    auto_assignable_topics,
    currency_for_client,
    account_manager_id,
    deadline_for_client,
    production_status,
    payment_status,
    open_order,
    deadline_by_platform,
    ready_to_invoices,
    finish_to_project,
    parent_id_project,
    production_child,
    parent_id_category,
    invoice_father,
    client_name,
    offers_length,
    offers_language_id,
    language,
    total_project_amount,
    total_team_amount,
    total_prices,
    total_volumes,
    total_additional_costs,
    overall_margin,
    overall_margin_percentage
) as
WITH
cte_team_amount_historical_project_list_view as (
	SELECT
            historical_offer_id,
            COALESCE(SUM(gross_amount), 0) AS total_project_amount
        FROM
            historcial_offer_partitipants
        GROUP BY
            historical_offer_id
), 
cte_prices_categories_historical_project_list_view as (
	SELECT
        historical_project_id, SUM(volume) AS total_volumes, SUM(price) AS total_prices
    FROM historical_project_categories
    GROUP BY historical_project_id
),
cte_team_offers_historical_project_list_view as (
		SELECT
        historical_project_id, SUM(COALESCE(gross_amount,0 ) + COALESCE(reviser_gross_amount,0) + COALESCE(editor_gross_amount, 0) + 		COALESCE(project_manager_gross_amount, 0)) AS total_team_amount
    FROM historical_project_offers
    GROUP BY historical_project_id
), 
cte_copy_space_historical_project_list_view as (
 	SELECT
        historical_topics.historical_project_id,
        ROUND(COALESCE(SUM(historical_articles.copyscape_cost_project), 0), 2) AS copyscape_cost_project
    FROM historical_topics
    INNER JOIN historical_offer_participant_deliveries ON historical_offer_participant_deliveries.historical_topic_id = historical_topics.id
    INNER JOIN historical_articles ON historical_articles.historical_offer_participant_delivery_id = historical_offer_participant_deliveries.id
    GROUP BY historical_topics.historical_project_id
),
cte_additional_cost_historical_project_list_view as (
	SELECT
        historical_project_id, ROUND(COALESCE(SUM(amount),0),2) AS additional_costs
    FROM historical_project_additional_costs
    GROUP BY historical_project_id
)
SELECT
    historical_projects.id,
    historical_projects.client_id,
    historical_projects.start_date,
    historical_projects.end_date,
    historical_projects.total_amount,
    historical_projects.special_offer,
    historical_projects.special_invoice,
    historical_projects.discount,
    historical_projects.tax,
    historical_projects.invoice_number,
    historical_projects.invoice_date,
    historical_projects.due_date,
    historical_projects.notes_ht,
    historical_projects.name,
    historical_projects.paid,
    historical_projects.paid_date,
	historical_projects.status,
    historical_projects.copyscape_enabled,
    historical_projects.project_manager_price,
    historical_projects.project_manager_gross_amount,
    historical_projects.user_id,
    historical_projects.type_project,
    historical_projects.has_author,
    historical_projects.has_reviser,
    historical_projects.has_editor,
    historical_projects.has_pm,
    historical_projects.created_by_platform,
    historical_projects.head_production_id,
    historical_projects.error_invoices,
    historical_projects.ac_chat_visited,
    historical_projects.hp_chat_visited,
    historical_projects.has_translator,
    historical_projects.has_auto_reassign,
    historical_projects.auto_assignable_topics,
    historical_projects.account_manager_id,
    historical_projects.currency_for_client,
    historical_projects.deadline_for_client,
    historical_projects.production_status,
    historical_projects.payment_status,
    historical_projects.open_order,
    historical_projects.deadline_by_platform,
    historical_projects.ready_to_invoices,
    historical_projects.finish_to_project,
    historical_projects.parent_id_project,
    historical_projects.production_child,
    historical_projects.parent_id_category,
    historical_projects.invoice_father,
    clients.name as client_name,
    historical_project_offers.length as offers_length,
    historical_project_offers.language_id as offers_language_id,
    languages.language,
    COALESCE(cte_team_amount_historical_project_list_view.total_project_amount, 0) as total_project_amount,
    (COALESCE(cte_team_offers_historical_project_list_view.total_team_amount, 0) + 		COALESCE(cte_copy_space_historical_project_list_view.copyscape_cost_project,0) + COALESCE(cte_additional_cost_historical_project_list_view.additional_costs,0)) AS total_team_amount,
    cte_prices_categories_historical_project_list_view.total_prices,
    cte_prices_categories_historical_project_list_view.total_volumes,
    COALESCE(cte_additional_cost_historical_project_list_view.additional_costs, 0) AS total_additional_costs,
    (historical_projects.total_amount - (COALESCE( cte_team_offers_historical_project_list_view.total_team_amount, 0) + COALESCE(cte_copy_space_historical_project_list_view.copyscape_cost_project,0) + COALESCE(cte_additional_cost_historical_project_list_view.additional_costs,0))) AS overall_margin,
    ((historical_projects.total_amount -(COALESCE(cte_team_offers_historical_project_list_view.total_team_amount, 0) + COALESCE(
cte_copy_space_historical_project_list_view.copyscape_cost_project,0) + COALESCE(cte_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 on historical_project_offers.historical_project_id = historical_projects.id
    LEFT JOIN languages ON historical_project_offers.language_id = languages.id
    LEFT JOIN cte_team_amount_historical_project_list_view on cte_team_amount_historical_project_list_view.historical_offer_id = historical_project_offers.id
    LEFT JOIN cte_prices_categories_historical_project_list_view on cte_prices_categories_historical_project_list_view.historical_project_id = historical_projects.id
    LEFT JOIN cte_team_offers_historical_project_list_view on cte_team_offers_historical_project_list_view.historical_project_id = historical_projects.id
    LEFT JOIN cte_copy_space_historical_project_list_view on cte_copy_space_historical_project_list_view.historical_project_id = historical_projects.id
    LEFT JOIN cte_additional_cost_historical_project_list_view on cte_additional_cost_historical_project_list_view.historical_project_id = historical_projects.id
	LEFT JOIN clients on clients.id = historical_projects.client_id
    GROUP BY historical_projects.id;