-- =====================================================
-- Rollback Script for Master-Template-Program System
-- Drops all tables, columns, sequences, and enums
-- Date: 2026-03-13
-- =====================================================

-- WARNING: This will permanently delete all master and template data
-- Run this only if you need to completely remove the master-template system

-- =====================================================
-- STEP 1: DROP FOREIGN KEY CONSTRAINTS FROM HDB TABLES
-- =====================================================

-- Drop constraints from hdb_question
ALTER TABLE hdb_question DROP CONSTRAINT IF EXISTS fk_hdb_question_template;

-- Drop constraints from hdb_form_section
ALTER TABLE hdb_form_section DROP CONSTRAINT IF EXISTS fk_hdb_section_template;
ALTER TABLE hdb_form_section DROP CONSTRAINT IF EXISTS fk_hdb_section_parent;

-- Drop constraints from program_v1
ALTER TABLE program_v1 DROP CONSTRAINT IF EXISTS fk_program_template;

-- =====================================================
-- STEP 2: DROP INDEXES FROM HDB TABLES
-- =====================================================

-- Drop indexes from hdb_question
DROP INDEX IF EXISTS idx_hdb_question_template;
DROP INDEX IF EXISTS idx_hdb_question_code;

-- Drop indexes from hdb_form_section
DROP INDEX IF EXISTS idx_hdb_section_template;
DROP INDEX IF EXISTS idx_hdb_section_parent;

-- Drop indexes from program_v1
DROP INDEX IF EXISTS idx_program_template;

-- =====================================================
-- STEP 3: DROP COLUMNS FROM HDB TABLES
-- =====================================================

-- Drop columns from hdb_question
ALTER TABLE hdb_question DROP COLUMN IF EXISTS template_question_id;
ALTER TABLE hdb_question DROP COLUMN IF EXISTS question_code;
ALTER TABLE hdb_question DROP COLUMN IF EXISTS option_config;

-- Drop columns from hdb_form_section
ALTER TABLE hdb_form_section DROP COLUMN IF EXISTS template_form_section_id;
ALTER TABLE hdb_form_section DROP COLUMN IF EXISTS parent_section_id;
ALTER TABLE hdb_form_section DROP COLUMN IF EXISTS conditional_config;

-- Drop columns from program_v1
ALTER TABLE program_v1 DROP COLUMN IF EXISTS program_template_id;

-- =====================================================
-- STEP 4: DROP CHILD TABLES (IN DEPENDENCY ORDER)
-- =====================================================

-- Drop template_question (references template_form_section and master_question)
DROP TABLE IF EXISTS template_question CASCADE;

-- Drop template_form_section (references master_form_section)
DROP TABLE IF EXISTS template_form_section CASCADE;

-- Drop program_template (references program_type)
DROP TABLE IF EXISTS program_template CASCADE;

-- Drop master_question (references master_form_section)
DROP TABLE IF EXISTS master_question CASCADE;

-- Drop master_form_section (parent table)
DROP TABLE IF EXISTS master_form_section CASCADE;

-- =====================================================
-- STEP 5: DROP SEQUENCES
-- =====================================================

DROP SEQUENCE IF EXISTS master_question_code_seq;

-- =====================================================
-- STEP 6: DROP ENUMS
-- =====================================================

DROP TYPE IF EXISTS question_type_enum CASCADE;
DROP TYPE IF EXISTS answer_type CASCADE;
DROP TYPE IF EXISTS template_status CASCADE;

-- =====================================================
-- ROLLBACK COMPLETE
-- =====================================================

-- Verify cleanup
DO $$ 
BEGIN
    RAISE NOTICE '====================================';
    RAISE NOTICE 'Rollback completed!';
    RAISE NOTICE '====================================';
    RAISE NOTICE 'Dropped:';
    RAISE NOTICE '  - 5 tables (master_form_section, master_question, program_template, template_form_section, template_question)';
    RAISE NOTICE '  - 7 columns from hdb tables and program_v1';
    RAISE NOTICE '  - 1 sequence (master_question_code_seq)';
    RAISE NOTICE '  - 3 enums (question_type_enum, answer_type, template_status)';
    RAISE NOTICE '====================================';
END $$;
