-- =====================================================
-- Master Data Migration Script
-- Migrates existing sections and questions to master tables
-- Version: 3.1.0
-- Date: 2026-03-13
-- Order: 01 - Run after schema migration
-- =====================================================

-- =====================================================
-- STEP 1: INSERT MASTER FORM SECTIONS
-- =====================================================

-- Insert sections from master-sections.csv
INSERT INTO master_form_section (
    section_key,
    name,
    description,
    parent_section_id,
    conditional_config,
    is_active,
    created_at,
    updated_at,
    deleted_at,
    created_by,
    updated_by
) VALUES
    (
        'FS_BASICDETAILS',
        'Basic Details',
        'Section for basic user details',
        NULL,
        NULL,
        TRUE,
        '2025-05-28 07:29:51.789449+00',
        '2025-06-14 06:57:19.255535+00',
        NULL,
        -2,
        -2
    ),
    (
        'FS_PAYMENTINVOICE',
        'Payment & Invoice',
        'Section for invoice information',
        NULL,
        NULL,
        TRUE,
        '2025-05-28 07:29:51.789449+00',
        '2025-06-14 06:57:19.375288+00',
        NULL,
        -2,
        -2
    ),
    (
        'FS_TRAVELPLAN',
        'Travel Plan',
        'Section for travel plan details',
        NULL,
        NULL,
        TRUE,
        '2025-05-28 07:29:51.789449+00',
        '2025-06-14 06:57:19.49327+00',
        NULL,
        -2,
        -2
    ),
    (
        'FS_MAHATRIAQUESTIONS',
        'Questions and Answers',
        'Section to display seeker filled questions to mahatria',
        NULL,
        NULL,
        TRUE,
        '2025-05-28 07:29:51.789449+00',
        '2025-05-28 07:29:51.789449+00',
        NULL,
        -2,
        -2
    ),
    (
        'FS_GOODIES',
        'Goodies and Ratria Pillars',
        'Section for goodies details',
        NULL,
        NULL,
        TRUE,
        '2025-07-29 05:40:04.703362+00',
        '2025-07-29 05:40:04.703362+00',
        NULL,
        -2,
        -2
    )
ON CONFLICT (section_key) WHERE deleted_at IS NULL DO UPDATE SET
    name = EXCLUDED.name,
    description = EXCLUDED.description,
    updated_at = EXCLUDED.updated_at;



-- =====================================================
-- STEP 3: MIGRATE DATA FROM HDB_QUESTION
-- =====================================================

-- Reset question code sequence to start after existing questions
SELECT setval('master_question_code_seq', COALESCE((SELECT MAX(id) FROM hdb_question), 0));


INSERT INTO master_question (
    question_code,
    binding_key,
    master_form_section_id,
    question_text,
    question_type,
    answer_type,
    answer_location,
    option_config,
    config,
    is_active,
    created_at,
    updated_at,
    deleted_at,
    created_by,
    updated_by
)
SELECT 
    'Q_' || LPAD(nextval('master_question_code_seq')::text, 10, '0') as question_code,
    hq.binding_key,
    mfs.id as master_form_section_id,
    hq.label as question_text,
    hq.typ::question_type_enum as question_type,
    CASE 
        WHEN hq.typ IN ('text', 'email', 'tel', 'textarea', 'radio', 'select', 'year', 'yearRange', 'time', 'file', 'checkbox', 'multiselect', 'Address', 'address', 'draganddrop', 'apicall', 'button', 'multiQuestion') THEN 'string'::answer_type
        WHEN hq.typ = 'number' THEN 'number'::answer_type
        WHEN hq.typ = 'boolean' THEN 'boolean'::answer_type
        WHEN hq.typ IN ('date', 'dateandtime') THEN 'date'::answer_type
        ELSE 'string'::answer_type
    END as answer_type,
    hq.answer_location,
    (
        SELECT COALESCE(jsonb_agg(
            jsonb_build_object(
                'name', opt_data.opt_name,
                'value', opt_data.opt_name,
                'type', opt_data.opt_type,
                'order', opt_data.row_order
            ) ORDER BY opt_data.row_order
        ), '[]'::jsonb)
        FROM (
            SELECT 
                opt.name as opt_name,
                opt.typ as opt_type,
                (row_number() OVER (ORDER BY qom.id) - 1) as row_order
            FROM hdb_question_option_map qom
            INNER JOIN hdb_option opt ON opt.id = qom.option_id
            WHERE qom.question_id = hq.id
              AND qom.deleted_at IS NULL
              AND opt.deleted_at IS NULL
        ) opt_data
    ) as option_config,
    hq.config,
    CASE WHEN hq.stats = 'published' THEN TRUE ELSE FALSE END as is_active,
    hq.created_at,
    hq.updated_at,
    hq.deleted_at,
    COALESCE((SELECT id FROM "users" u WHERE u.id = hq.created_by), -2) as created_by,
    COALESCE((SELECT id FROM "users" u WHERE u.id = hq.updated_by), -2) as updated_by
FROM hdb_question hq
INNER JOIN hdb_form_section hfs ON hfs.id = hq.form_section_id
INNER JOIN master_form_section mfs ON mfs.section_key = hfs.key
WHERE hq.binding_key IS NOT NULL
  AND hq.binding_key != ''
  AND hq.form_section_id IS NOT NULL
  AND hq.created_by = -2
  AND NOT EXISTS (
    SELECT 1 FROM master_question mq 
    WHERE mq.id = hq.id
  );

-- Reset sequence to continue from the highest ID
SELECT setval('master_question_code_seq', (SELECT MAX(id) FROM master_question));


-- =====================================================
-- STEP 5: VERIFICATION QUERIES
-- =====================================================

-- Count sections
DO $$ 
DECLARE
    section_count INTEGER;
    question_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO section_count FROM master_form_section WHERE deleted_at IS NULL;
    SELECT COUNT(*) INTO question_count FROM master_question WHERE deleted_at IS NULL;
    
    RAISE NOTICE 'Migration completed successfully!';
    RAISE NOTICE 'Total sections inserted: %', section_count;
    RAISE NOTICE 'Total questions inserted: %', question_count;
END $$;

-- Display summary
SELECT 
    mfs.section_key,
    mfs.name as section_name,
    COUNT(mq.id) as question_count
FROM master_form_section mfs
LEFT JOIN master_question mq ON mq.master_form_section_id = mfs.id AND mq.deleted_at IS NULL
WHERE mfs.deleted_at IS NULL
GROUP BY mfs.id, mfs.section_key, mfs.name
ORDER BY mfs.id;

-- =====================================================
-- END OF MIGRATION
-- =====================================================
