/* =========================================================================
PAGE 3 — Product (alias: product) Item: P3_SLUG, P3_ID (hidden)
Before Header: :P3_ID := ecp_web.product_page(:P3_SLUG);
Regions below: condition "P3_ID is not null"
========================================================================= */
-- Region: Product detail Static ID: ecp_product Template: product-detail.html
select c.*, ecp_web.cover_html(c.id, 600) cover_html,
ecp_store.url_shop(c.dept_code) dept_url,
ecp_store.url_shop(c.dept_code, c.category_slug) cat_url,
case when c.author_slug is not null then ecp_store.url_author(c.author_slug) end author_url,
e.name_ar region_name, e.eta_ar, e.ship_fee,
to_char(c.weight_kg, 'FM990.00') weight_label,
substr(c.author_name, 1, 1) author_initial,
(select bio_ar from ecp_authors a where a.id = c.author_id and a.company_id = c.company_id and a.branch_id = c.branch_id) author_bio,
ecp_store.can_review(c.id) can_review
from ecp_v_catalog c
join ecp_regions e on e.company_id = c.company_id and e.branch_id = c.branch_id and e.code = :G_REGION
where c.company_id = :G_COMPANY_ID and c.branch_id = :G_BRANCH_ID and c.id = :P3_ID;
-- Region: Specs Static ID: ecp_product_specs Template: specs.html
with me as (select * from ecp_v_products where id = :P3_ID and company_id = :G_COMPANY_ID and branch_id = :G_BRANCH_ID)
select label, value from (
select 1 s, 'المؤلف' label, case when kind = 'BOOK' then author_name end value from me union all
select 2, 'المترجم', translator_ar from me union all
select 3, 'الناشر', publisher_ar from me union all
select 4, 'سنة الطبعة', to_char(edition_year) from me union all
select 5, 'الغلاف', binding_ar from me union all
select 6, 'عدد الصفحات', to_char(pages) from me union all
select 7, 'ISBN', isbn from me union all
select 8, 'التصنيف', category_name from me union all
select 9, 'اللغة', case when kind = 'BOOK' then 'العربية' end from me union all
select 10 + j.seq, j.k, j.v
from me,
json_table(me.specs_json, '$[*]' columns (seq for ordinality, k varchar2(100) path '$.k', v varchar2(300) path '$.v')) j
) where value is not null
order by s;
-- Region: Rating summary Static ID: ecp_rating Template: rating-summary.html
select r.rating, r.review_count,
round(r.r5 / r.review_count * 100) p5, round(r.r4 / r.review_count * 100) p4,
round(r.r3 / r.review_count * 100) p3, round(r.r2 / r.review_count * 100) p2,
round(r.r1 / r.review_count * 100) p1
from ecp_v_ratings r
where r.product_id = :P3_ID and r.company_id = :G_COMPANY_ID and r.branch_id = :G_BRANCH_ID;
-- When no row: show the "لا توجد مراجعات بعد" empty state (No Data Found message).
-- Region: Reviews list Static ID: ecp_reviews Template: review.html
select regexp_substr(cu.full_name, '^\S+') || nvl2(regexp_substr(cu.full_name, '\s(\S)', 1, 1, null, 1),
' ' || regexp_substr(cu.full_name, '\s(\S)', 1, 1, null, 1) || '.', '') as reviewer,
r.rating, r.body,
to_char(r.published_at, 'fmDD Month YYYY', 'NLS_DATE_LANGUAGE=ARABIC') published_label,
(select name_ar from ecp_regions e where e.company_id = o.company_id and e.branch_id = o.branch_id and e.code = o.region_code) region_name
from ecp_reviews r
join ecp_customers cu on cu.id = r.customer_id
join ecp_orders o on o.id = r.order_id
where r.product_id = :P3_ID and r.company_id = :G_COMPANY_ID and r.branch_id = :G_BRANCH_ID and r.status = 'PUBLISHED'
order by r.published_at desc
fetch first 20 rows only;
-- Region: Related Static ID: ecp_related Template: product-card.html inside .m-rail
select c.*, ecp_store.url_product(c.slug) url, ecp_web.cover_html(c.id, 300) cover_html
from ecp_v_catalog c
join ecp_products me on me.id = :P3_ID and me.company_id = c.company_id and me.branch_id = c.branch_id
where c.company_id = :G_COMPANY_ID and c.branch_id = :G_BRANCH_ID and c.id <> me.id and c.dept_code = me.dept_code
order by case when c.category_id = me.category_id then 0 else 1 end,
case when c.author_id = me.author_id then 0 else 1 end,
c.review_count desc
fetch first 10 rows only;