select * from public.program_v1;
select * from public.program_session;
select * from public.program_type_v1;
select * from public.hdb_option;
select * from public.hdb_option_category;
select * from public.hdb_program_question;
select * from public.hdb_program_registration;
select * from public.hdb_question;
select * from public.hdb_question_option_map;
select * from public.hdb_registration_approval;
select * from public.hdb_registration_approval_track;
select * from public.hdb_registration_invoice_detail;
select * from public.hdb_registration_payment_details_history;
select * from public.hdb_registration_payment_detail;
select * from public.hdb_registration_question_answer;
select * from public.hdb_registration_travel_info;
select * from public.hdb_registration_travel_plan;
select * from public.user_profile_extension;
select * from public.user_participation_summary;

select * from hdb_program_registration where registration_status = 'completed' and user_id is not null; --3328

-- Add new columns to user_participation_summary table
ALTER TABLE user_participation_summary 
ADD COLUMN IF NOT EXISTS program_starts_at TIMESTAMPTZ,
ADD COLUMN IF NOT EXISTS program_ends_at TIMESTAMPTZ;

-- Add comments for the new columns
COMMENT ON COLUMN user_participation_summary.program_starts_at IS 'Start date and time of the program';
COMMENT ON COLUMN user_participation_summary.program_ends_at IS 'End date and time of the program';

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_user_participation_summary_program_dates 
ON user_participation_summary (program_starts_at, program_ends_at);

CREATE INDEX IF NOT EXISTS idx_user_participation_summary_user_program 
ON user_participation_summary (user_id, program_id);


INSERT INTO user_participation_summary (
    user_id,
    program_id,
    program_name,
    sub_program_id,
    sub_program_name,
    session_id,
    session_name,
    program_starts_at,
    program_ends_at,
    created_by,
    updated_by,
    created_at,
    updated_at
)
-- Dynamic data extraction for all programs
SELECT
    reg.user_id,
    reg.program_id,
    prog.name AS program_name,
    reg.allocated_program_id AS sub_program_id,
    CASE 
        WHEN reg.allocated_program_id IS NOT NULL 
        THEN (SELECT name FROM program_v1 WHERE id = reg.allocated_program_id LIMIT 1)
        ELSE NULL
    END AS sub_program_name,
    reg.program_session_id AS session_id,
    ps.name AS session_name,
    COALESCE(prog.starts_at, prog.registration_starts_at) AS program_starts_at,
    COALESCE(prog.ends_at, prog.registration_ends_at) AS program_ends_at,
    -2 AS created_by,
    -2 AS updated_by,
    NOW() AS created_at,
    NOW() AS updated_at
FROM hdb_program_registration reg
JOIN program_v1 prog 
    ON prog.id = reg.program_id
LEFT JOIN program_session ps 
    ON ps.id = reg.program_session_id
WHERE reg.user_id IS NOT NULL
  AND reg.registration_status = 'completed'
ORDER BY reg.id;


select count(*), user_id, program_id from user_participation_summary
group by user_id,program_id
having count(*)>1; -- 1


-- -- Delete in correct dependency order
-- DELETE FROM hdb_registration_payment_details_history WHERE payment_details_id IN (SELECT id FROM hdb_registration_payment_detail WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979));
-- DELETE FROM hdb_registration_invoice_detail WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);
-- DELETE FROM hdb_registration_payment_detail WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);
-- DELETE FROM hdb_registration_travel_info WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);
-- DELETE FROM hdb_registration_travel_plan WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);

-- DELETE FROM hdb_registration_approval_track WHERE approval_id IN (SELECT id FROM hdb_registration_approval WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979));
-- DELETE FROM hdb_registration_approval WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);
-- DELETE FROM hdb_registration_question_answer WHERE registration_id IN (SELECT id FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979);
-- DELETE FROM hdb_program_registration WHERE program_id >= 962 AND program_id <= 979;
-- DELETE FROM hdb_program_question WHERE program_id >= 962 AND program_id <= 979;
-- DELETE FROM program_session WHERE program_id >= 962 AND program_id <= 979;
-- DELETE FROM program_v1 WHERE id >= 962 AND id <= 979;