-- Add last_signed_in column to profiles table
ALTER TABLE profiles 
ADD COLUMN IF NOT EXISTS last_signed_in TIMESTAMP WITH TIME ZONE;

-- Add comment to document the column
COMMENT ON COLUMN profiles.last_signed_in IS 
    'Timestamp when the user last signed in, updated from auth.users.last_sign_in_at';


-- Create function to update last_signed_in column on profiles table
CREATE OR REPLACE FUNCTION public.create_last_signed_in_on_profiles()
    RETURNS TRIGGER AS $$
    BEGIN
      IF (NEW.last_sign_in_at is null) THEN
        RETURN NULL;
      ELSE
        UPDATE public.profiles
        SET last_signed_in = NEW.last_sign_in_at
        WHERE id = (NEW.id)::uuid;
        RETURN NEW;
      END IF;
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;
    
-- Create trigger to update last_signed_in column on profiles table
CREATE TRIGGER trigger_on_last_signed_in
    AFTER UPDATE ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION public.create_last_signed_in_on_profiles();
    
-- Create function to update tablo_invites is_pending for temporary users
CREATE OR REPLACE FUNCTION public.update_tablo_invites_on_login()
    RETURNS TRIGGER AS $$
    BEGIN
      IF (NEW.last_sign_in_at IS NULL OR NEW.last_sign_in_at = OLD.last_sign_in_at) THEN
        RETURN NULL;
      ELSE
        -- Check if the user is temporary and update pending invites
        UPDATE public.tablo_invites 
        SET is_pending = FALSE
        WHERE invited_email = NEW.email
          AND is_pending = TRUE
          AND EXISTS (
            SELECT 1 FROM public.profiles 
            WHERE id = (NEW.id)::uuid 
            AND is_temporary = TRUE
          );
        RETURN NEW;
      END IF;
    END;
    $$ LANGUAGE plpgsql SECURITY DEFINER;

-- Create trigger to update tablo_invites on user login
CREATE TRIGGER trigger_update_tablo_invites_on_login
    AFTER UPDATE ON auth.users
    FOR EACH ROW
    EXECUTE FUNCTION public.update_tablo_invites_on_login();

-- Add comment to document the trigger
COMMENT ON TRIGGER trigger_update_tablo_invites_on_login ON auth.users IS 
    'Automatically sets is_pending=false for tablo_invites when a temporary user signs in';

-- Trigger after login: https://github.com/orgs/supabase/discussions/7463