-- Añade soporte multi-idioma a system_config
DROP TABLE IF EXISTS public.system_config CASCADE;

CREATE TABLE public.system_config (
  config_key   varchar(100) NOT NULL,
  language     varchar(5)   NOT NULL DEFAULT 'es',
  config_value text         NOT NULL,
  updated_at   timestamptz  NOT NULL DEFAULT now(),
  PRIMARY KEY (config_key, language)
);

INSERT INTO public.system_config (config_key, language, config_value) VALUES
  ('system_description', 'es', 'Sistema de formación para partners del equipo REW de Zinzino'),
  ('system_description', 'en', 'Training system for REW team partners'),
  ('system_description', 'fr', 'Système de formation pour les partenaires de l''équipe REW'),
  ('system_description', 'it', 'Sistema di formazione per i partner del team REW');

DROP FUNCTION IF EXISTS public.fn_get_system_config(varchar);
DROP FUNCTION IF EXISTS public.fn_set_system_config(varchar, text);

CREATE OR REPLACE FUNCTION public.fn_get_system_config(p_key varchar, p_language varchar DEFAULT 'es')
RETURNS text LANGUAGE sql STABLE AS $$
  SELECT COALESCE(
    (SELECT config_value FROM public.system_config WHERE config_key = p_key AND language = p_language),
    (SELECT config_value FROM public.system_config WHERE config_key = p_key AND language = 'es')
  );
$$;

CREATE OR REPLACE FUNCTION public.fn_set_system_config(p_key varchar, p_language varchar, p_value text)
RETURNS void LANGUAGE sql AS $$
  INSERT INTO public.system_config (config_key, language, config_value, updated_at)
  VALUES (p_key, p_language, p_value, now())
  ON CONFLICT (config_key, language) DO UPDATE SET config_value = p_value, updated_at = now();
$$;

GRANT SELECT, INSERT, UPDATE ON public.system_config TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_get_system_config(varchar, varchar) TO "elfi1953_stc-eu";
GRANT EXECUTE ON FUNCTION public.fn_set_system_config(varchar, varchar, text) TO "elfi1953_stc-eu";