Database Functions

CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_users_updated_at 
  BEFORE UPDATE ON users 
  FOR EACH ROW 
  EXECUTE FUNCTION update_updated_at_column();

CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.users (id, email, name)
    VALUES (
        NEW.id,
        NEW.email,
        COALESCE(
          NEW.raw_user_meta_data->>'name', 
          NEW.raw_user_meta_data->>'full_name', 
          split_part(NEW.email, '@', 1)
        )
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW 
    EXECUTE FUNCTION handle_new_user();

CREATE OR REPLACE FUNCTION encrypt_text(
  text_to_encrypt TEXT, 
  encryption_key TEXT
)
RETURNS BYTEA AS $$
BEGIN
  RETURN pgp_sym_encrypt(text_to_encrypt, encryption_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

UPDATE user_oauth_tokens 
SET access_token_encrypted = encrypt_text(access_token, 'secret-key')
WHERE id = 'token-uuid';

CREATE OR REPLACE FUNCTION decrypt_text(
  encrypted_data BYTEA, 
  encryption_key TEXT
)
RETURNS TEXT AS $$
BEGIN
  RETURN pgp_sym_decrypt(encrypted_data, encryption_key);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

SELECT decrypt_text(access_token_encrypted, 'secret-key') as token
FROM user_oauth_tokens 
WHERE id = 'token-uuid';

CREATE OR REPLACE FUNCTION get_distinct_currencies()
RETURNS TABLE (currency TEXT)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT DISTINCT t.currency::TEXT
  FROM transactions t
  WHERE t.user_id = auth.uid()
  ORDER BY t.currency;
END;
$$;

CREATE OR REPLACE FUNCTION public.get_distinct_currencies()
RETURNS TABLE (currency text)
LANGUAGE sql
SECURITY DEFINER
IMMUTABLE
SET search_path = ''
AS $$
  SELECT DISTINCT currency FROM public.transactions;
$$;

SELECT * FROM get_distinct_currencies();

currency
--------
EUR
GBP
USD

CREATE OR REPLACE FUNCTION get_active_gmail_emails()
RETURNS TABLE (gmail_email TEXT)
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  RETURN QUERY
  SELECT DISTINCT uot.gmail_email::TEXT
  FROM user_oauth_tokens uot
  WHERE uot.user_id = auth.uid()
    AND uot.is_active = true
  ORDER BY uot.gmail_email;
END;
$$;

CREATE OR REPLACE FUNCTION public.get_active_gmail_emails()
RETURNS TABLE (gmail_email text)
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
  RETURN QUERY
  SELECT DISTINCT gmail_email
  FROM public.user_oauth_tokens
  WHERE is_active = true;
END;
$$;

SELECT * FROM get_active_gmail_emails();

gmail_email
-------------------
[email protected]
[email protected]

CREATE OR REPLACE FUNCTION public.stop_all_watches_for_user()
RETURNS void
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
  -- Stop all Gmail watches for current user
  UPDATE public.gmail_watches
  SET active = false
  WHERE user_id = auth.uid();
END;
$$;

SELECT stop_all_watches_for_user();

CREATE OR REPLACE FUNCTION public.delete_gmail_connection(
  token_id uuid
)
RETURNS void
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
  -- Delete specific OAuth token if owned by user
  DELETE FROM public.user_oauth_tokens
  WHERE id = delete_gmail_connection.token_id
    AND user_id = auth.uid();
END;
$$;

SELECT delete_gmail_connection('123e4567-e89b-12d3-a456-426614174000');

CREATE OR REPLACE FUNCTION public.renew_watches_for_user()
RETURNS void
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = ''
AS $$
BEGIN
  -- Renew/activate watches for current user
  UPDATE public.gmail_watches
  SET expires_at = now() + interval '7 days',
      active = true
  WHERE user_id = auth.uid() 
    AND expires_at < now();
END;
$$;

SELECT renew_watches_for_user();

CREATE OR REPLACE FUNCTION renew_gmail_watches()
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  backend_url text := 'https://backend-production-74b9.up.railway.app/renew-watches';
BEGIN
  -- Make HTTP request to backend renewal endpoint
  PERFORM net.http_post(
    url := backend_url,
    headers := jsonb_build_object('Content-Type', 'application/json'),
    body := jsonb_build_object('action', 'renew_all')
  );
  
  -- Log execution
  INSERT INTO cron_job_logs (job_name, status, details) 
  VALUES ('renew-gmail-watches', 'executed', 'Gmail watches renewal triggered');
  
  RAISE LOG 'Gmail watches renewal triggered at %', NOW();
EXCEPTION
  WHEN OTHERS THEN
    -- Log error
    INSERT INTO cron_job_logs (job_name, status, details) 
    VALUES ('renew-gmail-watches', 'error', SQLERRM);
    
    RAISE LOG 'Error triggering Gmail watches renewal: %', SQLERRM;
END;
$$;

SELECT cron.schedule(
  'renew-gmail-watches',
  '0 2 * * *',  -- Daily at 2 AM UTC
  'SELECT renew_gmail_watches()'
);

-- Default: minimum 2 occurrences, 50% confidence
SELECT * FROM get_subscription_candidates();

-- Stricter: minimum 3 occurrences, 70% confidence
SELECT * FROM get_subscription_candidates(3, 70);

[
  {
    "merchant_display": "Netflix",
    "merchant_normalized": "netflix",
    "currency": "USD",
    "avg_amount": 15.99,
    "min_amount": 15.99,
    "max_amount": 15.99,
    "occurrences": 12,
    "interval_days_avg": 30.5,
    "interval_stddev": 1.2,
    "frequency": "monthly",
    "last_date": "2026-02-15",
    "next_estimated_date": "2026-03-17",
    "category": "services",
    "source_email_consistent": true,
    "confidence_score": 94.5
  }
]

GRANT EXECUTE ON FUNCTION get_distinct_currencies() TO authenticated;
GRANT EXECUTE ON FUNCTION get_active_gmail_emails() TO authenticated;
GRANT EXECUTE ON FUNCTION get_subscription_candidates(integer, numeric) TO authenticated;