

-- load the history tables
ALTER TABLE users
ADD COLUMN country_name VARCHAR(255);

CREATE TABLE user_profile_extension (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    id_front_back_only_from_download_link TEXT,
    id_back_only_download_link TEXT,
    email_to_which_invoice_to_be_sent CITEXT,
    name_in_which_invoice_to_be_issued TEXT,
    invoice_address TEXT,
    tan VARCHAR(20),
    gstin VARCHAR(20),
    are_you_registered_under_gst BOOLEAN,
    when_was_your_last_hdb_msd TEXT,
    since_when_have_you_been_associated_with_this_path TEXT,
    how_many_hdb_msd_have_you_done INT,
    which_of_the_following_have_you_experienced TEXT,
    song_preference_1 TEXT,
    song_preference_2 TEXT,
    rm_rating DECIMAL(5,2),
    rm_review TEXT,
    contact_person TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP DEFAULT NULL,
    created_by BIGINT,
    updated_by BIGINT,
    CONSTRAINT fk_user_profile_extension_user FOREIGN KEY (user_id)
        REFERENCES users(id),
    CONSTRAINT fk_user_profile_extension_created_by FOREIGN KEY (created_by)
        REFERENCES users(id),
    CONSTRAINT fk_user_profile_extension_updated_by FOREIGN KEY (updated_by)
        REFERENCES users(id)
);

CREATE TABLE user_participation_summary (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    program_id BIGINT NOT NULL,
    program_name TEXT,
    sub_program_id BIGINT,
    sub_program_name TEXT,
    session_id BIGINT,
    session_name TEXT,
    created_by BIGINT,
    updated_by BIGINT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP DEFAULT NULL,
    CONSTRAINT fk_user_participation_user FOREIGN KEY (user_id)
        REFERENCES users(id),
    CONSTRAINT fk_user_participation_created_by FOREIGN KEY (created_by)
        REFERENCES users(id),
    CONSTRAINT fk_user_participation_updated_by FOREIGN KEY (updated_by)
        REFERENCES users(id)
);

select count(*) from users; -- 9782

select phone_number from users;


SELECT gstin, are_you_registered_under_gst
FROM zz_ref_20250714_hdb_info_asis_trimmed
WHERE gstin IS NOT NULL AND TRIM(gstin) <> '' and (are_you_registered_under_gst ilike 'No' or are_you_registered_under_gst = '');


SELECT gstin, are_you_registered_under_gst
FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
WHERE gstin IS NOT NULL AND TRIM(gstin) <> '' and (are_you_registered_under_gst ilike 'No' or are_you_registered_under_gst = '');

UPDATE zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
SET are_you_registered_under_gst = 'Yes'
WHERE gstin IS NOT NULL AND TRIM(gstin) <> '' and (are_you_registered_under_gst ilike 'No' or are_you_registered_under_gst = '');

-- Table: zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
SELECT gstin, are_you_registered_under_gst
FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
WHERE gstin IS NOT NULL AND TRIM(gstin) <> '' and (are_you_registered_under_gst ilike 'No' or are_you_registered_under_gst = '');

CREATE TABLE zz_ref_temp_unified_hdb_info AS
SELECT 
     u.id AS user_id,

    -- HDB journey fields
    CASE 
        WHEN COALESCE(src.status, 'Confirmed') = 'Confirmed' 
        THEN src.hdb_year
        ELSE src.when_was_your_last_hdb_msd
    END AS when_was_your_last_hdb_msd,
    
    src.since_when_have_you_been_associated_with_this_path,
    
    CASE 
        WHEN COALESCE(src.status, 'Confirmed') = 'Confirmed' 
        THEN COALESCE(NULLIF(src.how_many_hdb_msd_have_you_done, '')::INT, 0) + 1
        ELSE COALESCE(NULLIF(src.how_many_hdb_msd_have_you_done, '')::INT, 0)
    END AS how_many_hdb_msd_have_you_done,

    src.which_of_the_following_have_you_experienced,

    -- Preferences / feedback
    src.song_preference_1,
    src.song_preference_2,
    NULLIF(src.rm_rating, '')::DECIMAL(5,2) AS rm_rating,
    src.rm_review,
    src.contact_person,

    -- Invoice / tax details
    src.email_to_which_invoice_to_be_sent::CITEXT,
    src.name_in_which_invoice_to_be_issued,
    src.invoice_address,
    src.tan,
    src.gstin,
    (CASE WHEN src.are_you_registered_under_gst ILIKE 'yes' THEN TRUE
          WHEN src.are_you_registered_under_gst ILIKE 'no' or src.are_you_registered_under_gst = '' THEN FALSE
          ELSE NULL END) AS are_you_registered_under_gst,

    src.hdb_year,
    -- ID columns
    src.id_front_back_only_from_download_link,
    src.id_back_only_download_link

FROM (
    -- Base table: 20250714
    SELECT 
         bare_mobile,
         id_front_back_only_from_download_link,
         id_back_only_download_link,
         email_to_which_invoice_to_be_sent,
         name_in_which_invoice_to_be_issued,
         invoice_address,
         tan,
         gstin,
         are_you_registered_under_gst,
         when_was_your_last_hdb_msd,
         since_when_have_you_been_associated_with_this_path,
         how_many_hdb_msd_have_you_done,
         which_of_the_following_have_you_experienced,
         song_preference_1,
         song_preference_2,
         rm_rating,
         rm_review,
         contact_person,
         hdb_year,
         status
    FROM zz_ref_20250714_hdb_info_asis_trimmed

    UNION

    -- 2023–2024
    SELECT 
        bare_mobile,
        id_1_download_link AS id_front_back_only_from_download_link,
        id_2_download_link AS id_back_only_download_link,
        email_to_which_invoice_to_be_sent,
        name_in_which_invoice_to_be_issued,
        invoice_address,
        tan,
        gstin,
        are_you_registered_under_gst,
        when_was_your_last_hdb_msd,
        since_when_have_you_been_associated_with_this_path,
        how_many_hdb_msd_have_you_done,
        which_of_the_following_have_you_experienced,
        song_preference_1,
        song_preference_2,
        rm_rating,
        rm_review,
        contact_person,
        hdb_year,
        status
    FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    WHERE mobile NOT IN (SELECT mobile FROM zz_ref_20250714_hdb_info_asis_trimmed)

    UNION

    -- 2022–2023
    SELECT 
        bare_mobile,
        id_1_download_link AS id_front_back_only_from_download_link,
        id_2_download_link AS id_back_only_download_link,
        email_to_which_invoice_to_be_sent,
        name_in_which_invoice_to_be_issued,
        invoice_address,
        tan,
        gstin,
        are_you_registered_under_gst,
        when_was_your_last_hdb_msd,
        since_when_have_you_been_associated_with_this_path,
        how_many_hdb_msd_have_you_done,
        which_of_the_following_have_you_experienced,
        song_preference_1,
        song_preference_2,
        rm_rating,
        rm_review,
        contact_person,
        hdb_year,
        status
    FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    WHERE mobile NOT IN (
        SELECT mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    )

    UNION

    -- 2021–2022 (no status column → force Confirmed)
    SELECT 
         bare_mobile,
         NULL AS id_front_back_only_from_download_link,
         NULL AS id_back_only_download_link,
         NULL AS email_to_which_invoice_to_be_sent,
         NULL AS name_in_which_invoice_to_be_issued,
         NULL AS invoice_address,
         NULL AS tan,
         NULL AS gstin,
         NULL AS are_you_registered_under_gst,
         when_was_your_last_hdb_msd,
         since_when_have_you_been_associated_with_this_path,
         how_many_hdb_msd_have_you_done,
         which_of_the_following_have_you_experienced,
         NULL AS song_preference_1,
         NULL AS song_preference_2,
         rm_rating,
         rm_review,
         contact_person,
         hdb_year,
         'Confirmed' AS status
    FROM zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
    WHERE mobile NOT IN (
        SELECT mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
        UNION
        SELECT mobile FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    )
) src
JOIN users u
  ON u.phone_number = (src.bare_mobile);



select * from zz_ref_temp_unified_hdb_info;
select * from zz_ref_temp_unified_hdb_info where are_you_registered_under_gst is null and gstin is not null and gstin != '';


INSERT INTO user_profile_extension (
    user_id,
    id_front_back_only_from_download_link,
    id_back_only_download_link,
    email_to_which_invoice_to_be_sent,
    name_in_which_invoice_to_be_issued,
    invoice_address,
    tan,
    gstin,
    are_you_registered_under_gst,
    when_was_your_last_hdb_msd,
    since_when_have_you_been_associated_with_this_path,
    how_many_hdb_msd_have_you_done,
    which_of_the_following_have_you_experienced,
    song_preference_1,
    song_preference_2,
    rm_rating,
    rm_review,
    contact_person,
    created_by,
    updated_by
)
SELECT 
    user_id,
    id_front_back_only_from_download_link,
    id_back_only_download_link,
    email_to_which_invoice_to_be_sent,
    name_in_which_invoice_to_be_issued,
    invoice_address,
    tan,
    gstin,
    are_you_registered_under_gst,
    when_was_your_last_hdb_msd,
    since_when_have_you_been_associated_with_this_path,
    how_many_hdb_msd_have_you_done,
    which_of_the_following_have_you_experienced,
    song_preference_1,
    song_preference_2,
    rm_rating,
    rm_review,
    contact_person,
    -2 AS created_by,
    -2 AS updated_by
FROM zz_ref_temp_unified_hdb_info;



INSERT INTO user_profile_extension (
    user_id,
    created_at,
    updated_at,
    created_by,
    updated_by
)
SELECT 
    u.id AS user_id,
    NOW() AS created_at,
    NOW() AS updated_at,
    -2 AS created_by,
    -2 AS updated_by
FROM users u
WHERE NOT EXISTS (
    SELECT 1 
    FROM user_profile_extension upe 
    WHERE upe.user_id = u.id
);
select * from user_profile_extension order by id;


select * from users order by id desc;

select DISTINCT contact_person from user_profile_extension where contact_person is not null or contact_person != '';
select id, first_name,full_name,phone_number from users where phone_number in (
'9966279944',
'9880702700',
'9884874161',
'9841149060',
'8094444402',
'9845191961',
'9657152211',
'9841405117',
'9791140214',
'9841660000',
'9011240808',
'9790768202',
'9841021495',
'9841280188',
'9841727022',
'8939910100',
'9841042289');

-- "Charuta" "9011240808"
-- "Chithambaran" "9790768202"
-- "Sridhar" "9841727022"
-- "Mukundan" "9884874161"
-- "Vinod" "8094444402"
-- "Arun" '9845191961'
-- "Ramel" "9841617412"
-- "Gopinath" "7373314345"
-- "Athiaman" "7299903879"
-- "Other" "9841660000"
-- "Jaya" "9841660000"
-- "Gopal" "9841021495"
-- "Bhupendhar" '9841149060'
-- "Sakthi" "9840032328"
-- "Manohar" "9841042289"
-- "Kishan" "9966279944"
-- "Swaroop" "9657152211"
-- "Vijisubbu" "8939910100"
-- "Organization" "9841660000"
-- "Subbu" '9841405117',
-- "Mahesh" "9791140214"
-- "Mohini""9880702700"


select id, first_name,full_name,phone_number from users where first_name in (
'Jaya');


CREATE TEMP TABLE contact_mapping (
    contact_name TEXT,
    phone_number TEXT
);

INSERT INTO contact_mapping (contact_name, phone_number) VALUES
('Charuta', '9011240808'),
('Chithambaran', '9790768202'),
('Sridhar', '9841727022'),
('Mukundan', '9884874161'),
('Vinod', '8094444402'),
('Arun', '9845191961'),
('Ramel', '9841660000'),
('Gopinath', '9841660000'),
('Athiaman', '9841660000'),
('Other', '9841660000'),
('Gopal', '9841021495'),
('Bhupendhar', '9841149060'),
('Sakthi', '9841660000'),
('Jaya', '9841660000'),
('Manohar', '9841042289'),
('Kishan', '9966279944'),
('Swaroop', '9657152211'),
('Vijisubbu', '8939910100'),
('Organization', '9841660000'),
('Subbu', '9841405117'),
('Mahesh', '9791140214'),
('Mohini', '9880702700');

ALTER TABLE user_profile_extension 
ADD COLUMN infinitheism_contact BIGINT REFERENCES users(id);


UPDATE user_profile_extension upe
SET infinitheism_contact = u.id
FROM contact_mapping cm
JOIN users u ON u.phone_number = cm.phone_number
WHERE upe.contact_person = cm.contact_name;

select contact_person, infinitheism_contact from user_profile_extension where contact_person is not null or contact_person != '';

-- need to drop contact_mapping //TODO MK



CREATE TABLE zz_ref_temp_unified_hdb_country AS
SELECT 
     u.id AS user_id,
     src.country
FROM (
    -- Base table: 20250714
    SELECT bare_mobile, country
    FROM zz_ref_20250714_hdb_info_asis_trimmed

    UNION

    -- 2023–2024
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    WHERE bare_mobile NOT IN (SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed)

    UNION

    -- 2022–2023
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    WHERE bare_mobile NOT IN (
        SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    )

    UNION

    -- 2021–2022
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
    WHERE bare_mobile NOT IN (
        SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    )
) src
JOIN users u
  ON u.phone_number = (src.bare_mobile);


select * from zz_ref_temp_unified_hdb_country;

UPDATE users u
SET country_name = c.country
FROM zz_ref_temp_unified_hdb_country c
WHERE u.id = c.user_id
  AND (c.country IS NOT NULL OR u.country_name = '')
  AND (u.country_name IS NULL OR u.country_name = '');


select distinct country_name from users where country_name is not null;

select * from lookup_data where lookup_category = 'COUNTRY';


SELECT DISTINCT u.country_name
FROM users u
WHERE u.country_name IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM lookup_data l
    WHERE l.lookup_category = 'COUNTRY'
      AND l.lookup_label = u.country_name
  ); -- 0

SELECT * FROM zz_ref_20250714_hdb_info_asis_trimmed;

SELECT 
     u.id AS user_id,
     src.bare_mobile,
     src.country
FROM (
    -- Base table: 20250714
    SELECT bare_mobile, country
    FROM zz_ref_20250714_hdb_info_asis_trimmed

    UNION

    -- 2023–2024
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    WHERE bare_mobile NOT IN (SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed)

    UNION

    -- 2022–2023
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    WHERE bare_mobile NOT IN (
        SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
    )

    UNION

    -- 2021–2022
    SELECT bare_mobile, country
    FROM zz_ref_20250717_hdb_2021_2022_info_asis_trimmed
    WHERE bare_mobile NOT IN (
        SELECT bare_mobile FROM zz_ref_20250714_hdb_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2023_2024_info_asis_trimmed
        UNION
        SELECT bare_mobile FROM zz_ref_20250717_hdb_2022_2023_info_asis_trimmed
    )
) src
JOIN users u
  ON u.phone_number = (src.bare_mobile);


SELECT 
    user_id,
    when_was_your_last_hdb_msd,
    CASE
        -- If it's exactly 4 digits like 2019 → "2019 - 2020"
        WHEN when_was_your_last_hdb_msd ~ '^\d{4}$' 
            THEN when_was_your_last_hdb_msd || ' - ' || (when_was_your_last_hdb_msd::int + 1)

        -- If it's like 2019-20 → expand to "2019 - 2020"
        WHEN when_was_your_last_hdb_msd ~ '^\d{4}-\d{2}$'
            THEN split_part(when_was_your_last_hdb_msd, '-', 1) 
                 || ' - ' || (split_part(when_was_your_last_hdb_msd, '-', 1)::int + 1)

        -- If it's like 2019-2020 → convert to "2019 - 2020"
        WHEN when_was_your_last_hdb_msd ~ '^\d{4}-\d{4}$'
            THEN replace(when_was_your_last_hdb_msd, '-', ' - ')

        -- If it's already "2019 - 2020", keep as is
        WHEN when_was_your_last_hdb_msd ~ '^\d{4} - \d{4}$'
            THEN when_was_your_last_hdb_msd

        ELSE when_was_your_last_hdb_msd
    END AS normalized_value
FROM user_profile_extension
WHERE when_was_your_last_hdb_msd IS NOT NULL;


UPDATE user_profile_extension
SET when_was_your_last_hdb_msd = CASE
    -- Single year, convert to "YYYY - YYYY+1"
    WHEN when_was_your_last_hdb_msd ~ '^\d{4}$' 
        THEN when_was_your_last_hdb_msd || ' - ' || (when_was_your_last_hdb_msd::int + 1)
    
    -- Year with 2-digit suffix, convert to "YYYY - YYYY+1"
    WHEN when_was_your_last_hdb_msd ~ '^\d{4}-\d{2}$'
        THEN split_part(when_was_your_last_hdb_msd, '-', 1)
             || ' - ' || (split_part(when_was_your_last_hdb_msd, '-', 1)::int + 1)
    
    -- Year range without spaces, convert to "YYYY - YYYY"
    WHEN when_was_your_last_hdb_msd ~ '^\d{4}-\d{4}$'
        THEN replace(when_was_your_last_hdb_msd, '-', ' - ')
    
    -- Already in "YYYY - YYYY" format, leave as-is
    WHEN when_was_your_last_hdb_msd ~ '^\d{4} - \d{4}$'
        THEN when_was_your_last_hdb_msd

    ELSE when_was_your_last_hdb_msd
END
WHERE when_was_your_last_hdb_msd IS NOT NULL;


-- =============================================
-- SEPARATE ALTER STATEMENTS FOR NEW COLUMNS
-- (Execute these separately if previous migration already run)
-- =============================================

-- Add hdb_year column to user_profile_extension table
ALTER TABLE user_profile_extension 
ADD COLUMN IF NOT EXISTS hdb_year TEXT;

-- Add pincode column to user_profile_extension table
ALTER TABLE user_profile_extension 
ADD COLUMN pincode VARCHAR(10);

COMMENT ON COLUMN user_profile_extension.pincode IS 'Postal/ZIP code for the user';

-- Update existing records with hdb_year data if the column was just added
-- (Run this only if hdb_year column was just added and data needs to be populated)

UPDATE user_profile_extension upe
SET hdb_year = unified.hdb_year
FROM zz_ref_temp_unified_hdb_info unified
WHERE upe.user_id = unified.user_id
  AND upe.hdb_year IS NULL;


-- =============================================
-- END OF SEPARATE ALTER STATEMENTS
-- =============================================


select * from user_profile_extension where contact_person is not null or user_id in('9271');


-- in create user need to insert empty record into this user_profile_extension //TODO MK
