
-- Add is_active column to conversations table
ALTER TABLE `conversations` ADD `is_active` integer DEFAULT true;--> statement-breakpoint

-- Single UPDATE with subquery - more efficient than temporary table
UPDATE `conversations` 
SET `is_active` = 0 
WHERE EXISTS (
    SELECT 1 FROM `group_memberships` gm
    WHERE gm.`conversation_id` = `conversations`.`id`
    AND gm.`is_active` = 0 
    AND gm.`user_id` = CAST(
        SUBSTR(
            REPLACE((SELECT file FROM pragma_database_list WHERE name = 'main'), 'humand-', ''),
            1,
            INSTR(REPLACE((SELECT file FROM pragma_database_list WHERE name = 'main'), 'humand-', ''), '-') - 1
        ) AS INTEGER
    )
);--> statement-breakpoint

-- Clean up inactive group_memberships
DELETE FROM `group_memberships` WHERE `is_active` = 0;--> statement-breakpoint

-- Drop the is_active column from group_memberships
ALTER TABLE `group_memberships` DROP COLUMN `is_active`;