-- Directus key auto-generation triggers
-- Target tables: companies, sites, users, roles
-- Key format: PREFIX + yyyymmdd + 'X' + 4 digits
-- Example: CP20260404X1234

BEGIN;

CREATE OR REPLACE FUNCTION public.directus_autogen_entity_key()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  v_column_name text := TG_ARGV[0];
  v_prefix text := TG_ARGV[1];
  v_current_value text;
  v_candidate text;
  v_exists boolean;
  v_attempt int;
  v_day text;
  v_suffix text;
BEGIN
  v_current_value := to_jsonb(NEW) ->> v_column_name;
  IF v_current_value IS NOT NULL AND btrim(v_current_value) <> '' THEN
    RETURN NEW;
  END IF;

  v_day := to_char(clock_timestamp(), 'YYYYMMDD');

  FOR v_attempt IN 0..9999 LOOP
    v_suffix := lpad((((extract(epoch FROM clock_timestamp()) * 1000)::bigint + v_attempt) % 10000)::text, 4, '0');
    v_candidate := v_prefix || v_day || 'X' || v_suffix;

    EXECUTE format(
      'SELECT EXISTS (SELECT 1 FROM %I.%I WHERE %I = $1)',
      TG_TABLE_SCHEMA,
      TG_TABLE_NAME,
      v_column_name
    ) INTO v_exists USING v_candidate;

    IF NOT v_exists THEN
      NEW := jsonb_populate_record(NEW, jsonb_build_object(v_column_name, v_candidate));
      RETURN NEW;
    END IF;
  END LOOP;

  RAISE EXCEPTION 'Failed to generate unique key for %.% (column=%)', TG_TABLE_SCHEMA, TG_TABLE_NAME, v_column_name;
END;
$$;

DROP TRIGGER IF EXISTS trg_companies_key_autogen ON public.companies;
CREATE TRIGGER trg_companies_key_autogen
BEFORE INSERT ON public.companies
FOR EACH ROW
EXECUTE FUNCTION public.directus_autogen_entity_key('key', 'CP');

DROP TRIGGER IF EXISTS trg_sites_key_autogen ON public.sites;
CREATE TRIGGER trg_sites_key_autogen
BEFORE INSERT ON public.sites
FOR EACH ROW
EXECUTE FUNCTION public.directus_autogen_entity_key('key', 'ST');

DROP TRIGGER IF EXISTS trg_users_key_autogen ON public.users;
CREATE TRIGGER trg_users_key_autogen
BEFORE INSERT ON public.users
FOR EACH ROW
EXECUTE FUNCTION public.directus_autogen_entity_key('key', 'UE');

DROP TRIGGER IF EXISTS trg_roles_key_autogen ON public.roles;
CREATE TRIGGER trg_roles_key_autogen
BEFORE INSERT ON public.roles
FOR EACH ROW
EXECUTE FUNCTION public.directus_autogen_entity_key('key', 'RL');

-- cleanup old trigger names to avoid duplicates from previous scripts
DROP TRIGGER IF EXISTS trg_companies_company_key_autogen ON public.companies;
DROP TRIGGER IF EXISTS trg_sites_site_key_autogen ON public.sites;
DROP TRIGGER IF EXISTS trg_users_user_key_autogen ON public.users;
DROP TRIGGER IF EXISTS trg_systems_system_key_autogen ON public.systems;
DROP TRIGGER IF EXISTS trg_roles_role_key_autogen ON public.roles;

COMMIT;