DROP VIEW IF EXISTS writer_invoices_detail_view;
CREATE VIEW writer_invoices_detail_view(
   invoice_id,
   detail,
   quantity,
   price_unit,
   copyscape_cost,
   total,
   created_at
) as
WITH 
cte_detail as (
    SELECT
    offer_participant_bills.invoice_id,
    deliveries_bills.detail,
    offer_deliveries_offer_bills.price as 'price_unit',
    COUNT(offer_deliveries_offer_bills.id) as quantity,
    topics.offer_id,
    IF(user.role_id = 1 || user.role_id = 4, ROUND(offer_partitipants.copyscape_cost, 2), 0) as copyscape_cost,
    offer_participant_bills.created_at
    FROM `offer_participant_bills`
    LEFT JOIN offer_deliveries_offer_bills on offer_deliveries_offer_bills.offer_participant_bills_id = offer_participant_bills.id
    LEFT JOIN offer_participant_deliveries on offer_deliveries_offer_bills.offer_participant_deliveries_id = offer_participant_deliveries.id
    LEFT JOIN topics on offer_participant_deliveries.topic_id = topics.id
    LEFT JOIN offer_partitipants on offer_participant_deliveries.offer_participant_id = offer_partitipants.id
    LEFT JOIN project_offers on offer_partitipants.offer_id = project_offers.id
    LEFT JOIN user on user.id = offer_participant_bills.user_id
    LEFT JOIN (
        SELECT 
        project_offers.id as detail_offer_id,
        offer_deliveries_offer_bills.offer_participant_bills_id,
        GROUP_CONCAT('  ', CONCAT_WS(' - ', project_offers.offer_name, topics.topics, topics.article_number) ) as detail
        FROM offer_deliveries_offer_bills
        LEFT JOIN offer_participant_deliveries on offer_deliveries_offer_bills.offer_participant_deliveries_id = offer_participant_deliveries.id
        LEFT JOIN topics on offer_participant_deliveries.topic_id = topics.id
        LEFT JOIN offer_partitipants on offer_participant_deliveries.offer_participant_id = offer_partitipants.id
        LEFT JOIN project_offers on offer_partitipants.offer_id = project_offers.id
        GROUP BY offer_deliveries_offer_bills.offer_participant_bills_id,project_offers.id
    ) as deliveries_bills on deliveries_bills.offer_participant_bills_id = offer_participant_bills.id and deliveries_bills.detail_offer_id = topics.offer_id
        GROUP by offer_deliveries_offer_bills.id
    ORDER BY `offer_participant_bills`.`invoice_id` DESC 
)
SELECT
    cte_detail.invoice_id,
    cte_detail.detail,
    count(cte_detail.quantity) as quantity,
    cte_detail.price_unit as 'price_unit',
    cte_detail.copyscape_cost as 'copyscape_cost',
    ROUND((cte_detail.quantity * cte_detail.price_unit) - cte_detail.copyscape_cost, 2) as total,
    cte_detail.created_at
FROM cte_detail
GROUP by cte_detail.offer_id, cte_detail.invoice_id
ORDER by cte_detail.invoice_id asc;