DROP VIEW IF EXISTS projects_production_view;
CREATE VIEW projects_production_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,
    account_manager_id,
    currency_for_client,
    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,
    categorie_price,
    categories_gross_amount,
    categories_category_type_id,
    categories_volume,
    offers_length,
    offers_language_id,
    author_first_name,
    author_last_name,
    offerDeliveries_end_date,
    price_per_text_per_author,
    editor_first_name,
    editor_last_name,
    offerDeliveries_end_date_editor,
    price_per_text_per_editor,
    pm_first_name,
    pm_last_name,
    offerDeliveries_end_date_pm,
    pm_costs_in_eur,
    total_project_amount,
    total_team_amount,
    total_volumes,
    total_prices,
    copyscape_cost_project,
    total_additional_costs,
    overall_margin,
    overall_margin_percentage
) as
WITH 
cte_team_amount_project_list_view as (
    SELECT 
        offer_id, SUM(gross_amount) AS total_project_amount
    FROM
        offer_partitipants
    GROUP BY
        offer_id
), cte_prices_categories_project_list_view as (
    SELECT
    	project_id, SUM(volume) AS total_volumes, SUM(price) AS total_prices
    FROM
    	project_categories
    GROUP BY
    	project_id
), cte_copy_space_project_list_view as (
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
), cte_team_offers_project_list_view as (
	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
), cte_total_admin_amout_view as (
	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
), cte_additional_cost_list_view as (
 SELECT
        project_id, ROUND(COALESCE(SUM(amount), 0), 2) AS additional_costs
    FROM
        project_additional_costs
    GROUP BY
        project_id
), cte_total_team_and_overall_margin_child_view as (
    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
)
SELECT
    projects.id,
    projects.client_id,
    projects.start_date,
    projects.end_date,
    projects.total_amount,
    projects.special_offer,
    projects.special_invoice,
    projects.discount,
    projects.tax,
    projects.invoice_number,
    projects.invoice_date,
    projects.due_date,
    projects.notes_ht,
    projects.name,
    projects.paid,
    projects.paid_date,
	projects.status,
    projects.copyscape_enabled,
    projects.project_manager_price,
    projects.project_manager_gross_amount,
    projects.user_id,
    projects.type_project,
    projects.has_author,
    projects.has_reviser,
    projects.has_editor,
    projects.has_pm,
    projects.created_by_platform,
    projects.head_production_id,
    projects.error_invoices,
    projects.ac_chat_visited,
    projects.hp_chat_visited,
    projects.has_translator,
    projects.has_auto_reassign,
    projects.auto_assignable_topics,
    projects.account_manager_id,
    projects.currency_for_client,
    projects.deadline_for_client,
    projects.production_status,
    projects.payment_status,
    projects.open_order,
    projects.deadline_by_platform,
    projects.ready_to_invoices,
    projects.finish_to_project,
    projects.parent_id_project,
    projects.production_child,
    projects.parent_id_category,
    projects.invoice_father,
    clients.name as client_name,
    categories.price as categorie_price,
    categories.gross_amount as categories_gross_amount,
    categories.category_type_id as categories_category_type_id,
    categories.volume as categories_volume,
    offers.length as offers_length,
    offers.language_id as offers_language_id,
    author.first_name as author_first_name,
    author.last_name as author_last_name,
    offerDeliveries.end_date as offerDeliveries_end_date,
    financialReport.price_per_text_per_author as price_per_text_per_author, 
    editor.first_name as editor_first_name,
    editor.last_name as editor_last_name,
    offerDeliveries.end_date as offerDeliveries_end_date_editor,
    financialReport.price_per_text_per_editor as price_per_text_per_editor,
    pm.first_name as pm_first_name,
    pm.last_name as pm_last_name,
    offerDeliveries.end_date as offerDeliveries_end_date_pm,
    financialReport.pm_costs_in_eur as pm_costs_in_eur,
    COALESCE(cte_team_amount_project_list_view.total_project_amount, 0) AS total_project_amount,
    (COALESCE( cte_team_offers_project_list_view.total_team_amount , 0 ) + COALESCE(cte_copy_space_project_list_view.copyscape_cost_project, 0) + COALESCE(cte_total_team_and_overall_margin_child_view.total_team_cost , 0)) AS total_team_amount,
    cte_prices_categories_project_list_view.total_volumes AS total_volumes,
    cte_prices_categories_project_list_view.total_prices AS total_prices,
    COALESCE(cte_copy_space_project_list_view.copyscape_cost_project, 0) AS copyscape_cost_project,
    (COALESCE(cte_additional_cost_list_view.additional_costs, 0) + COALESCE (cte_total_team_and_overall_margin_child_view.total_costs, 0)) as total_additional_costs,
    (((projects.total_amount - (COALESCE( cte_team_offers_project_list_view.total_team_amount , 0) + COALESCE(cte_total_admin_amout_view.total_admin_amount, 0)  + COALESCE(
       cte_copy_space_project_list_view.copyscape_cost_project, 0) + COALESCE(cte_additional_cost_list_view.additional_costs,0))) - (COALESCE(cte_total_team_and_overall_margin_child_view.total_costs, 0)))) AS overall_margin,
    (((projects.total_amount - (COALESCE( cte_team_offers_project_list_view.total_team_amount, 0 ) + COALESCE(cte_total_admin_amout_view.total_admin_amount,0) + COALESCE(
    cte_copy_space_project_list_view.copyscape_cost_project,0) + COALESCE(cte_additional_cost_list_view.additional_costs, 0))) - (COALESCE (cte_total_team_and_overall_margin_child_view.total_costs,0 )))     * 100) / projects.total_amount AS overall_margin_percentage
FROM
    projects
LEFT JOIN project_categories categories ON projects.id = categories.project_id
LEFT JOIN project_offers offers ON projects.id = offers.project_id
LEFT JOIN languages ON offers.language_id = languages.id
LEFT JOIN offer_partitipants participants ON offers.id = participants.offer_id
LEFT JOIN user users ON participants.user_id = users.id
LEFT JOIN authors author ON users.id = author.user_id
LEFT JOIN editor_project ON projects.id = editor_project.project_id
LEFT JOIN editors editor ON editor_project.editor_id = editor.id
LEFT JOIN offer_deliveries offerDeliveries ON offers.id = offerDeliveries.offer_id
LEFT JOIN project_financial_statements financialReport ON projects.id = financialReport.project_id
LEFT JOIN project_manager_project ON projects.id = project_manager_project.project_id
LEFT JOIN editors pm ON project_manager_project.editor_id = pm.id
LEFT JOIN cte_team_amount_project_list_view ON cte_team_amount_project_list_view.offer_id = offers.id
LEFT JOIN cte_prices_categories_project_list_view ON cte_prices_categories_project_list_view.project_id = projects.id
LEFT JOIN cte_team_offers_project_list_view ON cte_team_offers_project_list_view.project_id = projects.id
LEFT JOIN cte_copy_space_project_list_view ON cte_copy_space_project_list_view.project_id = projects.id
LEFT JOIN cte_additional_cost_list_view ON cte_additional_cost_list_view.project_id = projects.id
LEFT JOIN cte_total_admin_amout_view ON cte_total_admin_amout_view.project_id = projects.id
LEFT JOIN cte_total_team_and_overall_margin_child_view on cte_total_team_and_overall_margin_child_view.parent_id_project = projects.id
INNER JOIN clients on clients.id = projects.client_id
WHERE
    type_project = 2
GROUP BY
    projects.id;