booklets_downgrade.sql

-- SQL Script to remove all booklet entries and schema.
-- item.type range is 251 - 280
-- revrec sku cat ids are 92 - 121
-- Product types are 'booklets-85x11' and 'booklets-55x85'

BEGIN;

-- Delete Booklet entries from migration_log
DELETE FROM migration_log WHERE patch_file IN (
    'option.sql',
    'product.sql',
    '1360784384_booklets_revrec_insert.sql',
    '1360784385_booklets_product_type_insert.sql',
    '1360784386_booklets_omniture_insert.sql',
    '1360935597_add_tables_for_options.sql',
    '1360936589_add_tables_for_products.sql',
    '1360936897_booklets_add_options.sql',
    '1361277716_INSERT_coating_option.sql',
    '1361362860_update_option_conflicts.sql',
    '1361349867_add_option_mailing_magnets.sql',
    '1361396811_2-5-0_booklets_guide_files_product_info.sql',
    '1361435584_create_product_group_designer_table.sql',
    '1361448710_alter_product_table_enabled.sql',
    '1361526706_create_attributes_table.sql',
    '1361396810_booklets_insert_product_info.sql',
    '1361998470_2-5-0-booklet_slas.sql',
    '1361787394_create_product_designer_table.sql',
    '1361894251_2-5-0_product_option_pricing.sql',
    '1361928864_2-5-0_doctrine_schema_update.sql',
    '1361985464_add_enabled_product_option.sql',
    '1361989080_2-5-0_insert_booklet_product_options.sql',
    '1362063492_create_item_table.sql',
    '1362063492_create_order_item_table.sql',
    '1361396810_booklets_pdfsplit_render.sql'
);

-- Delete rows from item_desc
DELETE FROM item_desc WHERE item_id IN (SELECT id FROM items WHERE type BETWEEN 251 AND 280);

-- Delete rows from item_price
DELETE FROM item_price WHERE item_id IN (SELECT id FROM items WHERE type BETWEEN 251 AND  280);

-- Delete rows from items table
DELETE FROM items WHERE type BETWEEN 251 AND 280;

-- Delete rows from product_attribute
DELETE FROM product_attributes WHERE product IN ('booklets-85x11', 'booklets-55x85');

-- Delete rows from product_info
DELETE FROM product_info WHERE product IN ('booklets-85x11', 'booklets-55x85');

-- Delete rows from product_type
DELETE FROM product_type WHERE item_type_id BETWEEN 251 AND 280;

-- Delete revrec entries
DELETE FROM rr_sku_cat WHERE id BETWEEN 92 AND 121;

-- Remove SLAs
DELETE FROM production_times WHERE product IN ('booklets-85x11', 'booklets-55x85');
DELETE FROM rr_sku_sla WHERE printed_product_skucat_id BETWEEN 92 AND 121;

-- Delete from product groups
DELETE FROM product_groups WHERE group_name = 'booklets';

-- Delete from renderers
DELETE FROM product_renderers WHERE product IN ('booklets-85x11', 'booklets-55x85');

-- Drop Sequences
DROP SEQUENCE IF EXISTS option_group_id_seq;
DROP SEQUENCE IF EXISTS option_id_seq;
DROP SEQUENCE IF EXISTS product_group_id_seq;
DROP SEQUENCE IF EXISTS product_id_seq;
DROP SEQUENCE IF EXISTS product_option_pricing_id_seq;
DROP SEQUENCE IF EXISTS product_option_id_seq;
DROP SEQUENCE IF EXISTS attributes_id_seq;
DROP SEQUENCE IF EXISTS product_attribute_id_seq;
DROP SEQUENCE IF EXISTS item_id_seq;
DROP SEQUENCE IF EXISTS item_option_id_seq;
DROP SEQUENCE IF EXISTS order_items_id_seq;
DROP SEQUENCE IF EXISTS order_item_options_id_seq;

-- Drop Tables
DROP TABLE IF EXISTS option_conflict CASCADE;
DROP TABLE IF EXISTS option_dependent CASCADE;
DROP TABLE IF EXISTS option CASCADE;
DROP TABLE IF EXISTS option_group CASCADE;
DROP TABLE IF EXISTS product_option CASCADE;
DROP TABLE IF EXISTS product CASCADE;
DROP TABLE IF EXISTS product_group CASCADE;
DROP TABLE IF EXISTS attributes CASCADE;
DROP TABLE IF EXISTS product_attribute CASCADE;
DROP TABLE IF EXISTS product_designer CASCADE;
DROP TABLE IF EXISTS product_option_pricing CASCADE;
DROP TABLE IF EXISTS product_group_designer CASCADE;
DROP TABLE IF EXISTS item_option CASCADE;
DROP TABLE IF EXISTS item CASCADE;
DROP TABLE IF EXISTS order_item_options CASCADE;
DROP TABLE IF EXISTS order_items CASCADE;

COMMIT;