DROP VIEW IF EXISTS projects_invoice_view;
CREATE VIEW projects_invoice_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_additional_costs,
    overall_margin,
    overall_margin_percentage
) as
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,
    total_team_and_overall_margin_view.total_project_amount AS total_project_amount,
    total_team_and_overall_margin_view.total_team_amount AS total_team_amount,
    total_team_and_overall_margin_view.total_additional_costs AS total_additional_costs,
    total_team_and_overall_margin_view.overall_margin AS overall_margin,
    total_team_and_overall_margin_view.overall_margin_percentage 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 total_team_and_overall_margin_view ON total_team_and_overall_margin_view.project_id = projects.id
INNER JOIN clients on clients.id = projects.client_id
WHERE
    type_project = 2
GROUP BY
    projects.id;