84 lines
3.1 KiB
SQL
84 lines
3.1 KiB
SQL
-- P5-6898 Prune posters and framed print interiors
|
|
|
|
-- 1. Delete all poster interiors except first two
|
|
with poster_interiors_to_delete as (
|
|
select id
|
|
from interiors main
|
|
join "product-printproducts" posters on print_id = printid and posters.groupid = 7 -- only poster products
|
|
where room_id is not null -- only 3d interiors
|
|
and position >= 2
|
|
-- exclude products that also have own photos
|
|
and not exists (select 1 from interiors o_p where o_p.print_id = main.print_id and o_p.room_id is null)
|
|
)
|
|
DELETE
|
|
FROM interiors
|
|
USING poster_interiors_to_delete
|
|
WHERE interiors.id = poster_interiors_to_delete.id
|
|
;
|
|
|
|
-- 2. Delete the first two framed print interiors
|
|
with framed_print_interiors_to_delete as (
|
|
select id
|
|
from interiors main
|
|
join "product-printproducts" framed_prints on print_id = printid and framed_prints.groupid = 8 -- only framed prints
|
|
where room_id is not null -- only 3d interiors
|
|
and position < 2
|
|
-- exclude products that also have own photos
|
|
and not exists (select 1 from interiors o_p where o_p.print_id = main.print_id and o_p.room_id is null)
|
|
)
|
|
DELETE
|
|
FROM interiors
|
|
USING framed_print_interiors_to_delete
|
|
WHERE interiors.id = framed_print_interiors_to_delete.id
|
|
;
|
|
|
|
-- 3. Copy framed print inteiors position 3 and 4 to position 1 and 2
|
|
update interiors
|
|
set position = position - 2
|
|
from
|
|
"product-printproducts" framedprints
|
|
where
|
|
print_id = printid and framedprints.groupid = 8 -- only framed prints
|
|
and position in (2,3)
|
|
and room_id is not null -- only 3d interiors
|
|
-- exclude prodotucts that also have own photos
|
|
and print_id not in (select print_id from interiors where room_id is null)
|
|
;
|
|
|
|
-- 4. delete remainig framed print interiors (position 5 and up)
|
|
with framed_print_interiors_to_delete as (
|
|
select id
|
|
from interiors main
|
|
join "product-printproducts" framed_prints on print_id = printid and framed_prints.groupid = 8 -- only framed prints
|
|
where room_id is not null -- only 3d interiors
|
|
and position >= 2
|
|
-- exclude products that also have own photos
|
|
and not exists (select 1 from interiors o_p where o_p.print_id = main.print_id and o_p.room_id is null)
|
|
)
|
|
DELETE
|
|
FROM interiors
|
|
USING framed_print_interiors_to_delete
|
|
WHERE interiors.id = framed_print_interiors_to_delete.id
|
|
;
|
|
|
|
-- 5. When framed print product has no interiors at all: copy first poster interior
|
|
with framed_prints_without_interiors as (
|
|
select productid, printid from "product-printproducts" where groupid = 8
|
|
and not exists (select 1 from interiors where print_id = printid)
|
|
), matching_poster_interiors as (
|
|
select
|
|
framed_print.productid productid,
|
|
framed_print.printid fp_printid,
|
|
replace(rooms.name, 'poster', 'framed-print') fp_room_name
|
|
from
|
|
framed_prints_without_interiors framed_print
|
|
join "product-printproducts" poster on framed_print.productid = poster.productid and poster.groupid = 7
|
|
join interiors poster_interiors on poster.printid = poster_interiors.print_id and position = 0 and room_id is not null
|
|
join rooms on room_id = rooms.id
|
|
)
|
|
INSERT INTO interiors (print_id, position, room_id)
|
|
SELECT fp_printid, 0, (select id from rooms where name = fp_room_name)
|
|
FROM matching_poster_interiors
|
|
;
|
|
|