DROP VIEW IF EXISTS client_projects_invoice_view;
CREATE VIEW client_projects_invoice_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,
    volume,
    category_type_id,
    price,
    gross_amount,
    length,
    language_id,
    price_per_text_per_author,
    price_per_text_per_editor,
    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, COALESCE(SUM(gross_amount),0) AS total_project_amount
    FROM
        offer_partitipants
    GROUP BY offer_id
), cte_team_offers_project_list_view as (
    SELECT
        project_offers.project_id, COALESCE(SUM(gross_amount + reviser_gross_amount + editor_gross_amount + project_manager_gross_amount),0) AS total_team_amount
    FROM
        project_offers
    GROUP BY project_offers.project_id
), cte_prices_categories_project_list_view as (
    SELECT
        project_id, COALESCE(SUM(volume),0) AS total_volumes, COALESCE(SUM(price),0) AS total_prices
    FROM
        project_categories
    GROUP BY project_id
), cte_copy_space_project_list_view as (
    SELECT
        topics.project_id, COALESCE(SUM(articles.copyscape_cost_project),0) AS copyscape_cost_project
    FROM
        topics
    INNER JOIN projects ON projects.id = topics.project_id
    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
    where projects.type_project = 2
    GROUP BY topics.project_id
), cte_additional_cost_list_view as (
    SELECT
        project_id, COALESCE(SUM(amount), 0) AS additional_costs
    FROM
        project_additional_costs
    GROUP BY project_id
), cte_total_admin_amout_view as (
	SELECT
        project_admin_offers.project_id, COALESCE(sum(project_admin_offers.price_per_article),0) * 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_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
), 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,
        COALESCE(SUM(volume), 0) AS total_volumes,
        COALESCE(SUM(price), 0) AS total_prices
    FROM
        historical_project_categories
    GROUP BY historical_project_id
), cte_team_offers_historical_project_list_view as (
    SELECT
        historical_project_id,
        COALESCE(SUM(gross_amount + reviser_gross_amount + editor_gross_amount + 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,
        COALESCE(SUM(historical_articles.copyscape_cost_project), 0) AS copyscape_cost_project
    FROM
        historical_topics
    INNER JOIN historical_projects ON historical_projects.id = historical_topics.historical_project_id
    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
    WHERE historical_projects.type_project = 2 
    GROUP BY historical_topics.historical_project_id
), cte_additional_cost_historical_project_list_view as (
    SELECT
        historical_project_id, COALESCE(SUM(amount), 0) AS additional_costs
    FROM
        historical_project_additional_costs
    GROUP BY historical_project_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,
    project_categories.volume, project_categories.category_type_id, project_categories.price, project_categories.gross_amount,
    offers.length,offers.language_id,project_financial_statements.price_per_text_per_author,
    project_financial_statements.price_per_text_per_editor,project_financial_statements.pm_costs_in_eur,
	COALESCE(cte_team_amount_project_list_view.total_project_amount, 0 ) AS total_project_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_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,
    (
        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_offers offers ON projects.id = offers.project_id

LEFT JOIN project_categories ON projects.id = project_categories.project_id
LEFT JOIN languages ON offers.language_id = languages.id
LEFT JOIN offer_partitipants ON offers.id = offer_partitipants.offer_id
LEFT JOIN user ON offer_partitipants.user_id = user.id
LEFT JOIN authors ON user.id = authors.user_id
LEFT JOIN editor_project ON projects.id = editor_project.project_id
LEFT JOIN editors ON editor_project.editor_id = editors.id
LEFT JOIN offer_deliveries ON offers.id = offer_deliveries.offer_id
LEFT JOIN project_financial_statements ON projects.id = project_financial_statements.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_team_offers_project_list_view ON cte_team_offers_project_list_view.project_id = projects.id
LEFT JOIN cte_prices_categories_project_list_view ON cte_prices_categories_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
WHERE
    projects.type_project = 2
GROUP BY projects.id

UNION ALL

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,
    historical_project_categories.volume, historical_project_categories.category_type_id, historical_project_categories.price, 
    historical_project_categories.gross_amount,
    offers.length,offers.language_id, historical_project_financial_statements.price_per_text_per_author,
    historical_project_financial_statements.price_per_text_per_editor, historical_project_financial_statements.pm_costs_in_eur,
    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 + cte_copy_space_historical_project_list_view.copyscape_cost_project + cte_additional_cost_historical_project_list_view.additional_costs , 0)
    ) AS total_team_amount,
    cte_prices_categories_historical_project_list_view.total_volumes as total_volumes,
    cte_prices_categories_historical_project_list_view.total_prices as total_prices,
    cte_copy_space_historical_project_list_view.copyscape_cost_project as copyscape_cost_project,
    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 offers ON historical_projects.id = offers.historical_project_id
LEFT JOIN historical_project_categories ON historical_projects.id = historical_project_categories.historical_project_id

LEFT JOIN languages ON offers.language_id = languages.id
LEFT JOIN historcial_offer_partitipants ON offers.id = historcial_offer_partitipants.historical_offer_id
LEFT JOIN user ON historcial_offer_partitipants.user_id = user.id
LEFT JOIN authors ON user.id = authors.user_id
LEFT JOIN editor_project ON historical_projects.id = editor_project.project_id
LEFT JOIN editors ON editor_project.editor_id = editors.id
LEFT JOIN historical_offer_deliveries ON offers.id = historical_offer_deliveries.historical_offer_id
LEFT JOIN historical_project_financial_statements ON historical_projects.id = 	historical_project_financial_statements.historical_project_id
LEFT JOIN project_manager_project ON historical_projects.id = project_manager_project.project_id
LEFT JOIN editors pm ON project_manager_project.editor_id = pm.id

LEFT JOIN cte_team_amount_historical_project_list_view ON cte_team_amount_historical_project_list_view.historical_offer_id = 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
WHERE
    historical_projects.type_project = 2
GROUP BY historical_projects.id;