-- ============================================================================
-- Step 1: Alter the message column to JSONB
-- ============================================================================

-- First, migrate existing data (if any) to JSONB format with en/fr keys
ALTER TABLE public.notifications 
ADD COLUMN message_jsonb JSONB;

-- Migrate existing English messages to JSONB format
-- This assumes existing messages are in English
UPDATE public.notifications
SET message_jsonb = jsonb_build_object(
    'en', message,
    'fr', message  -- Placeholder, will be replaced by trigger going forward
);

-- Drop old column and rename new one
ALTER TABLE public.notifications 
DROP COLUMN message;

ALTER TABLE public.notifications 
RENAME COLUMN message_jsonb TO message;

-- Make it NOT NULL
ALTER TABLE public.notifications 
ALTER COLUMN message SET NOT NULL;

-- Set default to empty object
ALTER TABLE public.notifications 
ALTER COLUMN message SET DEFAULT '{}'::jsonb;

-- ============================================================================
-- Step 2: Update the notify_users function with translations and no RAISE LOG
-- ============================================================================

CREATE OR REPLACE FUNCTION public.notify_users()
RETURNS TRIGGER AS $$
DECLARE
    affected_users UUID[];
    affected_user UUID;
    actor UUID;
    action TEXT;
    msg JSONB;  -- Changed from TEXT to JSONB
    meta JSONB;
    entity_type_name TEXT;
    entity_identifier TEXT;
BEGIN
    -- Determine if this is an INSERT or UPDATE
    IF TG_OP = 'INSERT' THEN
        action := 'created';
    ELSE
        action := 'updated';
        -- Skip if soft delete happened (only for tables with deleted_at column)
        IF TG_TABLE_NAME IN ('tablos', 'events', 'notes', 'event_types') THEN
            IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
                RETURN NEW;
            END IF;
        END IF;
    END IF;

    -- Get the actor (current user)
    -- Note: auth.uid() may return NULL when called via service role (API calls)
    -- In such cases, we won't exclude anyone from notifications based on actor
    actor := auth.uid();
    
    -- Set entity type and ID based on the table
    entity_type_name := TG_TABLE_NAME;
    
    -- Determine entity ID and affected users based on table
    CASE TG_TABLE_NAME
        WHEN 'tablos' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'New tablo "' || NEW.name || '" was created',
                    'fr', 'Nouveau tablo "' || NEW.name || '" a été créé'
                );
                -- Notify owner (but not if they are the creator)
                IF actor IS NULL OR NEW.owner_id != actor THEN
                    affected_users := ARRAY[NEW.owner_id];
                END IF;
            ELSE
                msg := jsonb_build_object(
                    'en', 'Tablo "' || NEW.name || '" was updated',
                    'fr', 'Le tablo "' || NEW.name || '" a été mis à jour'
                );
                -- Notify owner and all collaborators (exclude actor if set)
                affected_users := ARRAY(
                    SELECT DISTINCT user_id 
                    FROM public.tablo_access 
                    WHERE tablo_id = NEW.id 
                        AND is_active = true
                        AND (actor IS NULL OR user_id != actor)
                    UNION
                    SELECT NEW.owner_id
                    WHERE actor IS NULL OR NEW.owner_id != actor
                );
            END IF;
            
            meta := jsonb_build_object(
                'tablo_name', NEW.name,
                'status', NEW.status,
                'color', NEW.color
            );

        WHEN 'tasks' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'New task "' || NEW.title || '" was created',
                    'fr', 'Nouvelle tâche "' || NEW.title || '" a été créée'
                );
            ELSE
                IF OLD.status != NEW.status THEN
                    msg := jsonb_build_object(
                        'en', 'Task "' || NEW.title || '" status changed to ' || NEW.status,
                        'fr', 'Le statut de la tâche "' || NEW.title || '" a changé à ' || NEW.status
                    );
                ELSIF OLD.assignee_id != NEW.assignee_id OR (OLD.assignee_id IS NULL AND NEW.assignee_id IS NOT NULL) THEN
                    msg := jsonb_build_object(
                        'en', 'You were assigned to task "' || NEW.title || '"',
                        'fr', 'Vous avez été assigné à la tâche "' || NEW.title || '"'
                    );
                ELSE
                    msg := jsonb_build_object(
                        'en', 'Task "' || NEW.title || '" was updated',
                        'fr', 'La tâche "' || NEW.title || '" a été mise à jour'
                    );
                END IF;
            END IF;
            
            -- Notify tablo collaborators and assignee (exclude actor if set)
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.tablo_access 
                WHERE tablo_id = NEW.tablo_id 
                    AND is_active = true
                    AND (actor IS NULL OR user_id != actor)
                UNION
                SELECT t.owner_id
                FROM public.tablos t
                WHERE t.id = NEW.tablo_id
                    AND (actor IS NULL OR t.owner_id != actor)
                UNION
                SELECT NEW.assignee_id
                WHERE NEW.assignee_id IS NOT NULL
                    AND (actor IS NULL OR NEW.assignee_id != actor)
            );
            
            meta := jsonb_build_object(
                'task_title', NEW.title,
                'status', NEW.status,
                'tablo_id', NEW.tablo_id,
                'assignee_id', NEW.assignee_id
            );

        WHEN 'events' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'New event "' || NEW.title || '" was created',
                    'fr', 'Nouvel événement "' || NEW.title || '" a été créé'
                );
            ELSE
                msg := jsonb_build_object(
                    'en', 'Event "' || NEW.title || '" was updated',
                    'fr', 'L''événement "' || NEW.title || '" a été mis à jour'
                );
            END IF;
            
            -- Notify tablo collaborators (exclude actor if set)
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.tablo_access 
                WHERE tablo_id = NEW.tablo_id 
                    AND is_active = true
                    AND (actor IS NULL OR user_id != actor)
                UNION
                SELECT t.owner_id
                FROM public.tablos t
                WHERE t.id = NEW.tablo_id
                    AND (actor IS NULL OR t.owner_id != actor)
                UNION
                SELECT NEW.created_by
                WHERE actor IS NULL OR NEW.created_by != actor
            );
            
            meta := jsonb_build_object(
                'event_title', NEW.title,
                'start_date', NEW.start_date,
                'start_time', NEW.start_time,
                'tablo_id', NEW.tablo_id
            );

        WHEN 'notes' THEN
            entity_identifier := NEW.id;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'New note "' || NEW.title || '" was created',
                    'fr', 'Nouvelle note "' || NEW.title || '" a été créée'
                );
            ELSE
                msg := jsonb_build_object(
                    'en', 'Note "' || NEW.title || '" was updated',
                    'fr', 'La note "' || NEW.title || '" a été mise à jour'
                );
            END IF;
            
            -- Notify note owner and users with access (exclude actor if set)
            affected_users := ARRAY(
                SELECT DISTINCT user_id 
                FROM public.note_access 
                WHERE note_id = NEW.id 
                    AND is_active = true
                    AND (actor IS NULL OR user_id != actor)
                UNION
                SELECT NEW.user_id
                WHERE actor IS NULL OR NEW.user_id != actor
            );
            
            meta := jsonb_build_object(
                'note_title', NEW.title,
                'note_owner', NEW.user_id
            );

        WHEN 'tablo_access' THEN
            entity_identifier := NEW.id::TEXT;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'You were granted access to a tablo',
                    'fr', 'Vous avez obtenu l''accès à un tablo'
                );
            ELSE
                IF OLD.is_admin != NEW.is_admin AND NEW.is_admin = true THEN
                    msg := jsonb_build_object(
                        'en', 'You were promoted to admin on a tablo',
                        'fr', 'Vous avez été promu administrateur d''un tablo'
                    );
                ELSIF OLD.is_active != NEW.is_active AND NEW.is_active = false THEN
                    msg := jsonb_build_object(
                        'en', 'Your access to a tablo was revoked',
                        'fr', 'Votre accès à un tablo a été révoqué'
                    );
                ELSE
                    msg := jsonb_build_object(
                        'en', 'Your tablo access was updated',
                        'fr', 'Votre accès au tablo a été mis à jour'
                    );
                END IF;
            END IF;
            
            -- Notify the user being granted/modified access (exclude actor if set)
            IF actor IS NULL OR NEW.user_id != actor THEN
                affected_users := ARRAY[NEW.user_id];
            END IF;
            
            meta := jsonb_build_object(
                'tablo_id', NEW.tablo_id,
                'is_admin', NEW.is_admin,
                'is_active', NEW.is_active,
                'granted_by', NEW.granted_by
            );

        WHEN 'tablo_invites' THEN
            entity_identifier := NEW.id::TEXT;
            
            -- For tablo_invites, use invited_by as actor instead of auth.uid()
            -- because this is called via service role which has no auth context
            actor := NEW.invited_by;
            
            IF TG_OP = 'INSERT' THEN
                msg := jsonb_build_object(
                    'en', 'You were invited to collaborate on a tablo',
                    'fr', 'Vous avez été invité à collaborer sur un tablo'
                );
            ELSE
                IF OLD.is_pending != NEW.is_pending AND NEW.is_pending = false THEN
                    msg := jsonb_build_object(
                        'en', 'Your tablo invitation status changed',
                        'fr', 'Le statut de votre invitation au tablo a changé'
                    );
                ELSE
                    msg := jsonb_build_object(
                        'en', 'Your tablo invitation was updated',
                        'fr', 'Votre invitation au tablo a été mise à jour'
                    );
                END IF;
            END IF;
            
            -- Find the user by email and notify them (case-insensitive)
            -- Only notify if user exists in the system
            -- Don't notify the person who sent the invite (exclude actor if set)
            affected_users := ARRAY(
                SELECT id 
                FROM auth.users 
                WHERE LOWER(email) = LOWER(NEW.invited_email)
                    AND (actor IS NULL OR id != actor)
            );
            
            meta := jsonb_build_object(
                'tablo_id', NEW.tablo_id,
                'invited_email', NEW.invited_email,
                'invited_by', NEW.invited_by,
                'is_pending', NEW.is_pending
            );

        ELSE
            -- Unknown table, skip
            RETURN NEW;
    END CASE;

    -- Insert notifications for all affected users
    IF affected_users IS NOT NULL AND array_length(affected_users, 1) > 0 THEN
        FOREACH affected_user IN ARRAY affected_users
        LOOP
            INSERT INTO public.notifications (
                user_id,
                actor_id,
                entity_type,
                entity_id,
                action_type,
                message,
                metadata
            ) VALUES (
                affected_user,
                actor,
                entity_type_name,
                entity_identifier,
                action,
                msg,
                meta
            );
        END LOOP;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

