-----Date: 22-jan-2025-----
-----Queries are listed below----

-- Step 1: Add a column `is_announcement` to the `user_post` table
ALTER TABLE user_post
ADD COLUMN is_announcement BOOLEAN NOT NULL DEFAULT FALSE;

-- Step 2: Migrate posts from the `announcements` table to the `user_post` table
-- Insert data from announcements into user_post, setting is_announcement as TRUE
INSERT INTO user_post (text, user_id, created_at, updated_at, is_announcement)
SELECT caption, user_id, created_at, updated_at, TRUE
FROM announcements;

-- Step 3: Fetch the mapping of old announcement IDs to new user_post IDs
-- This assumes the `id` column in both tables is the primary key

  SELECT a.id AS old_announcement_id, u.id AS new_user_post_id
  FROM announcement a
  JOIN user_post u
    ON a.caption = u.text
   AND a.user_id = u.user_id AND u.is_announcement = TRUE

-- Step 4: Update the `user_post_images` table with the new `user_post_id`
UPDATE user_post_images
JOIN (
  SELECT a.id AS old_announcement_id, u.id AS new_user_post_id
  FROM announcement a
  JOIN user_post u
    ON a.caption = u.text
   AND a.user_id = u.user_id
   AND u.is_announcement = TRUE
) AS mp
ON user_post_images.user_post_id = mp.old_announcement_id
SET user_post_images.user_post_id = mp.new_user_post_id;

-- (Optional) Step 5: Clean up the announcements table if no longer needed
-- DELETE FROM announcements;

-- Step 6: Ensure `is_announcement` is FALSE for all existing posts in `user_post`
UPDATE user_post
SET is_announcement = FALSE
WHERE is_announcement IS NULL;

----Step 7: for updating the data related to mandir_id , status , image, deleted_at -----

UPDATE user_post up
JOIN announcements a
ON up.text = a.caption         -- Adjust this condition to match your mapping logic
   AND up.created_at = a.created_at -- Optional additional matching criteria
SET
    up.mandir_id = a.mandir_id,
    up.status = a.status,
    up.image_id = a.image,
    up.deleted_at = a.deleted_at
WHERE up.is_announcement = TRUE;

-- Step 8: Add a new column 'announcement_id' to the 'user_post' table
ALTER TABLE user_post
ADD COLUMN announcement_id INT;

-- Step 9: Update the 'announcement_id' column with the corresponding 'id' from the 'announcements' table
UPDATE user_post up
JOIN announcement a
ON up.text = a.caption         -- Adjust this condition to match your mapping logic
   AND up.created_at = a.created_at -- Optional additional matching criteria
SET up.announcement_id = a.id
WHERE up.is_announcement = TRUE;

---Step 10 :Update the user_post_images table by setting the user_post_id ----
-- based on the announcement_id from the user_post table ---

UPDATE gurujikaashramapp_api_app.user_post_images upi
JOIN gurujikaashramapp_api_app.user_post up
ON upi.user_announcement_id = up.announcement_id
   AND upi.mandir_id = up.mandir_id
   AND upi.name = up.text
   SET upi.user_post_id = up.announcement_id
WHERE up.is_announcement = TRUE AND upi.user_post_id IS NULL;

---Step 11: verify the result after updating ------

SELECT upi.user_post_id, up.id AS new_user_post_id, up.announcement_id
FROM gurujikaashramapp_api_app.user_post_images upi
JOIN gurujikaashramapp_api_app.user_post up
ON upi.user_announcement_id = up.announcement_id
   AND upi.mandir_id = up.mandir_id
   AND upi.name = up.text
WHERE up.is_announcement = TRUE;
----------------------------------------------------------------


Date:24-jan-2025
Queries are listed below

NOTE:This script is used for Playlist changes only

----Step 1 : To add Like column in the Playlist Table----
ALTER TABLE playlist
ADD COLUMN `liked` BOOLEAN NOT NULL DEFAULT FALSE;

-----Step 2:Update play_count to its intial state comes from song_counter table ---

SET SQL_SAFE_UPDATES = 0;
UPDATE playlist p
JOIN (
    SELECT user_song_id, COUNT(*) AS counter
    FROM song_counters
    WHERE user_song_id IS NOT NULL
    GROUP BY user_song_id
) sc ON p.id = sc.user_song_id
SET p.play_count = sc.counter
WHERE p.id = sc.user_song_id;
SET SQL_SAFE_UPDATES = 1;

-----Step 3:Update Like column to its intial state from Liked table (please ignore this)-----

SET SQL_SAFE_UPDATES = 0;
UPDATE playlist p
JOIN likes l ON p.id = l.user_song_id AND p.user_id=l.user_id
SET p.liked = 1
WHERE l.user_song_id IS NOT NULL;
SET SQL_SAFE_UPDATES = 1;


---Step 4 : to verify result after Like update (please ignore this)---

select * from playlist p
JOIN likes l ON p.id = l.user_song_id AND p.user_id=l.user_id
WHERE l.user_song_id IS NOT NULL and p.liked=1;


----------------------new updation as per instruction giving by sir--------------------

-----Step 1: Drop liked column from playlist-----

ALTER TABLE playlist
DROP COLUMN `liked`;

-----Step 2: Add new column liked_count---

ALTER TABLE playlist
ADD COLUMN `liked_count` INT NOT NULL DEFAULT 0;


-----Step 3:update count in the liked_count ----

SET SQL_SAFE_UPDATES = 0;
UPDATE playlist p
SET liked_count = (
    SELECT COUNT(*)
    FROM likes l
    WHERE l.user_song_id = p.id
      AND l.user_song_id IS NOT NULL
);
SET SQL_SAFE_UPDATES = 1;

-----Step 4: verify results after update query---

SELECT *
    FROM likes l
    join playlist p on p.id=l.user_song_id
    WHERE l.user_song_id = p.id
      AND l.user_song_id IS NOT NULL;

------------------------------------------------------------------------------------------------------------------------------

Date:27-jan-2025
Queries are listed below

ALTER TABLE `user_post_images`
MODIFY `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE deviceversion
MODIFY COLUMN version DOUBLE(8,2);

----------------------------------------------------------------
Date:29-jan-2025
Queries are listed below

----Step 1 : To add Liked and playCount column in the satsang_sharings Table----
ALTER TABLE satsang_sharings
ADD COLUMN `liked` INT NOT NULL DEFAULT 0;

ALTER TABLE satsang_sharings
ADD COLUMN `play_count` INT NOT NULL DEFAULT 0;

-----Step 2:Update play_count to its intial state comes from satsang_play table ---
SET SQL_SAFE_UPDATES = 0;
UPDATE  satsang_sharings p
JOIN (
    SELECT satsang_id, COUNT(*) AS counter
    FROM satsang_play
    WHERE satsang_id IS NOT NULL
    GROUP BY satsang_id
) sc ON p.id = sc.satsang_id
SET p.play_count = sc.counter
WHERE p.id = sc.satsang_id;
SET SQL_SAFE_UPDATES = 1;

-----Step 3:Update liked to its intial state comes from satsang_likes table ---

SET SQL_SAFE_UPDATES = 0;
UPDATE satsang_sharings p
SET liked = (
    SELECT COUNT(*)
    FROM satsang_likes l
    WHERE l.satsang_id = p.id
      AND l.satsang_id IS NOT NULL
);
SET SQL_SAFE_UPDATES = 1;

-----Step 3:verify liked after updating intial state comes from satsang_likes table ---

SELECT *
    FROM satsang_likes l
    join satsang_sharings p on p.id=l.satsang_id
    WHERE l.satsang_id = p.id
      AND l.satsang_id IS NOT NULL;

--------------------------------------------------------------------------

Date: 30-jan-2025
Queries are listed below

----Step 1 : To add Liked column in the user_post Table----

ALTER TABLE user_post
ADD COLUMN `liked` INT NOT NULL DEFAULT 0;

-----Step 2:Update liked to its intial state comes from user_post table ---

SET SQL_SAFE_UPDATES = 0;
UPDATE user_post p
SET liked = (
    SELECT COUNT(*)
    FROM likes l
    WHERE l.user_post_id = p.id
      AND l.user_post_id IS NOT NULL
);
SET SQL_SAFE_UPDATES = 1;

--------------------------------------------------------------------------------------------
Date:2-feb-2024
Queries:
------------------ updated fcm_token in the users table as per instructions giving by charan sir (ignore this) --------------------------------

update users set fcm_token=null where id in (41,117,1234,17546,17596);

update users set fcm_token='fwn6EJ-XS8GCPa3p1Yvlho:APA91bFE3XNupX6xBlzL4iRCJa9POJRn0fbHJHumDUsaNd8sHlhwzMRGgpYiSHs94Y_2I6TTFkg7pXXH55BAoKRNpZ1bnpgZSXme3FlgNtBonYxVJ7-CcHs' where id in (41,117,1234,17546,17596);
------------------------------------------------------------------------------------------------------------------
Date:04-feb-2025
Queries:

-------------------------------------update column default 0 value ------------------
ALTER TABLE user_post
MODIFY COLUMN is_announcement TINYINT DEFAULT 0;


------------------------------------------------------------------------------------------------------------------
Date:05-feb-2025
Queries:

-------------------------------------update youtube_broadcast last_heartbeat_at column ------------------

ALTER TABLE youtube_broadcast
ADD COLUMN last_heartbeat_at DATETIME NULL AFTER status;

------------------------------------------------------------------------------------------------------
Date:06-feb-2025
Queries:

-------------------------------------update youtube_broadcast youtube_token column------------------------------------
ALTER TABLE youtube_broadcast
ADD COLUMN youtube_token LONGTEXT NULL AFTER last_heartbeat_at;



-----------------------------------------------------------------------------------------------------------------
Date:07-feb-2025
Queries:

-----------------------------------------------Create Splash screen table--------------------------------------------------------
CREATE TABLE `splash_screen` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `image` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

--------------------------------------------------------------------------------------------------------------------
Date:07-feb-2025
Queries:
----------------------------------updated user_post_video table with video_thumbnail column ------------------------

ALTER TABLE user_post_video
ADD COLUMN video_thumbnail VARCHAR(255) NULL AFTER video;
--------------------------------------------------------------------------------------------------------------------
Date:07-feb-2025
Queries:
----------------------------------updated user_post_video table with video_thumbnail column ------------------------

ALTER TABLE user_post_video
ADD COLUMN video_thumbnail VARCHAR(255) NULL AFTER video;


----------------------------------------------------------------------------------------------------------------

Date:11-feb-2025
Queries:
----------------------------------updated users table with device_id column ------------------------

ALTER TABLE users
ADD COLUMN device_id TEXT NULL AFTER last_login;


----------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------------------------------
Date:12-feb-2025
Queries:

---------------------------------updated notification table for user_post_id --------------------------------

ALTER TABLE notifications
ADD COLUMN `user_post_id` INT NULL;

----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------

Date:11-feb-2025
Queries:
----------------------------------updated users table with device_id column ------------------------

ALTER TABLE users
ADD COLUMN device_id TEXT NULL AFTER last_login;


----------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------------------------------------------------
Date:21-feb-2024
Queries:

-------------------------------------upodated admin notification table for columns---------------------------------------


ALTER TABLE admin_notifications
ADD related_to VARCHAR(255) NOT NULL,
ADD mandir_id INT NULL,
ADD when_to_send ENUM('NOW', 'LATER') NOT NULL,
ADD send_time TIMESTAMP NULL;

-------------------------------------------------------------------------------------------------------------------