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,
    total_project_amount,
    total_team_amount,
    total_volumes,
    total_prices,
    copyscape_cost_project,
    total_additional_costs,
    overall_margin,
    overall_margin_percentage
) as
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,
    total_team_and_overall_margin_production_view.total_project_amount AS total_project_amount,
    total_team_and_overall_margin_production_view.total_team_amount AS total_team_amount,
    total_team_and_overall_margin_production_view.total_volumes as total_volumes,
    total_team_and_overall_margin_production_view.total_prices as total_prices,
    total_team_and_overall_margin_production_view.copyscape_cost_project as copyscape_cost_project,
    total_team_and_overall_margin_production_view.total_additional_costs AS total_additional_costs,
    total_team_and_overall_margin_production_view.overall_margin AS overall_margin,
    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 (
    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  ( 
    SELECT 
        offer_id, SUM(gross_amount) AS total_project_amount
    FROM
        offer_partitipants
    GROUP BY
        offer_id
) AS team_amount_project_list_view ON team_amount_project_list_view.offer_id = offers.id
     LEFT JOIN (
        SELECT
            project_id, SUM(volume) AS total_volumes, SUM(price) AS total_prices
        FROM
            project_categories
        GROUP BY
            project_id
   ) AS prices_categories_project_list_view ON prices_categories_project_list_view.project_id = projects.id
LEFT JOIN (
    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
) AS team_offers_project_list_view ON team_offers_project_list_view.project_id = projects.id
    
    
LEFT JOIN (
    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
) AS copy_space_project_list_view ON copy_space_project_list_view.project_id = projects.id
LEFT JOIN (
    SELECT
        project_id, ROUND(COALESCE(SUM(amount), 0), 2) AS additional_costs
    FROM
        project_additional_costs
    GROUP BY
        project_id
) AS additional_cost_list_view ON additional_cost_list_view.project_id = projects.id
LEFT JOIN (
    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
) AS total_admin_amout_view ON total_admin_amout_view.project_id = projects.id
    
    
) total_team_and_overall_margin_production_view ON total_team_and_overall_margin_production_view.project_id = projects.id
WHERE
   type_project IN(1, 3)
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,
    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 (
     SELECT
             historical_offer_id,
             COALESCE(SUM(gross_amount), 0) AS total_project_amount
         FROM
             historcial_offer_partitipants
         GROUP BY
             historical_offer_id
) AS team_amount_historical_project_list_view ON team_amount_historical_project_list_view.historical_offer_id = offers.id
LEFT JOIN (
    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
) AS prices_categories_historical_project_list_view ON prices_categories_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN (
    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
) AS team_offers_historical_project_list_view ON team_offers_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN (
    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 
) AS copy_space_historical_project_list_view ON copy_space_historical_project_list_view.historical_project_id = historical_projects.id
LEFT JOIN (
    SELECT
        historical_project_id,
        COALESCE(SUM(amount),
        0)AS additional_costs
    FROM
        historical_project_additional_costs
    GROUP BY
        historical_project_id
) AS 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