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,
    total_project_amount,
    total_team_amount,
    overall_margin,
    overall_margin_percentage
) as
    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,
    CASE historical_projects.type_project
           WHEN 1
           THEN 'Production'
           WHEN 2
           THEN 'Invoice'
           ELSE 'Production + Invoice'
       END AS 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_total_team_and_overall_margin_view.total_project_amount,
    historical_total_team_and_overall_margin_view.total_team_amount AS total_team_amount,
    historical_total_team_and_overall_margin_view.overall_margin AS overall_margin,
    historical_total_team_and_overall_margin_view.overall_margin_percentage AS overall_margin_percentage
FROM
    historical_projects
LEFT JOIN historical_project_categories categories ON
    historical_projects.id = categories.historical_project_id
LEFT JOIN historical_project_offers offers ON
    historical_projects.id = offers.historical_project_id
LEFT JOIN languages ON offers.language_id = languages.id
LEFT JOIN historcial_offer_partitipants participants ON
    offers.id = participants.historical_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 historical_projects.id = editor_project.project_id
LEFT JOIN editors editor ON
    editor_project.editor_id = editor.id
LEFT JOIN historical_offer_deliveries offerDeliveries ON
    offers.id = offerDeliveries.historical_offer_id
LEFT JOIN historical_project_financial_statements financialReport ON
    historical_projects.id = financialReport.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 historical_total_team_and_overall_margin_view ON historical_total_team_and_overall_margin_view.project_id = historical_projects.id
INNER JOIN clients on clients.id = historical_projects.client_id
GROUP BY
    historical_projects.id;
   