panels-origin/db/core/changesets/039-fn-next-project-code-global.sql

--liquibase formatted sql
-- PANELS · core · código PRY global (UNIQUE en projects.code) vs RLS por tenant

--changeset panel:core-039a-fn-next-project-code-global splitStatements:false
CREATE OR REPLACE FUNCTION core.fn_next_project_code(payload jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = core
AS $$
DECLARE
  v_n integer;
  v_code text;
BEGIN
  -- projects.code es UNIQUE en toda la base. Con RLS el tenant solo ve sus filas
  -- y MAX+1 puede repetir PRY-0001 de otro tenant → projects_code_key.
  -- SECURITY DEFINER (dueño del esquema) lee la secuencia global.
  SELECT COALESCE(MAX(substring(code FROM 5)::integer), 0) + 1
    INTO v_n
    FROM projects
   WHERE code ~ '^(PRY|OBR)-[0-9]+$';
  v_code := 'PRY-' || lpad(v_n::text, 4, '0');
  RETURN core.rpc_ok(
    jsonb_build_object('code', v_code),
    format('Siguiente código de proyecto asignado: %s', v_code),
    jsonb_build_object('fn', 'fn_next_project_code', 'sequence', v_n)
  );
EXCEPTION WHEN OTHERS THEN
  RETURN core.rpc_from_exception('fn_next_project_code', SQLSTATE, SQLERRM);
END;
$$;

--changeset panel:core-039b-fn-project-create-code-lock splitStatements:false
CREATE OR REPLACE FUNCTION core.fn_project_create(payload jsonb)
RETURNS jsonb
LANGUAGE plpgsql
SECURITY INVOKER
SET search_path = core
AS $$
DECLARE
  v_tid integer := COALESCE(
    (payload->>'tenant_id')::integer,
    NULLIF(current_setting('app.tenant_id', true), '')::integer
  );
  v_name text := nullif(btrim(payload->>'name'), '');
  v_address text := coalesce(nullif(btrim(payload->>'address'), ''), '');
  v_theme_id text := coalesce(nullif(btrim(payload->>'theme_id'), ''), 'arctec-dos-logos-fold');
  v_status text := coalesce(nullif(btrim(payload->>'status'), ''), 'activo');
  v_company_id integer := (payload->>'company_id')::integer;
  v_contract_amount numeric(14,2) := (payload->>'contract_amount')::numeric(14,2);
  v_start_date date := nullif(btrim(payload->>'start_date'), '')::date;
  v_end_date date := nullif(btrim(payload->>'end_date'), '')::date;
  v_resident_name text := coalesce(nullif(btrim(payload->>'resident_name'), ''), '');
  v_siroc text := upper(coalesce(nullif(btrim(payload->>'siroc'), ''), ''));
  v_payroll_tax_pct numeric(5,2) := coalesce((payload->>'payroll_tax_pct')::numeric(5,2), 4);
  v_code_env jsonb;
  v_code text;
  v_row projects%ROWTYPE;
BEGIN
  IF v_name IS NULL THEN
    RETURN core.rpc_err(
      'VALIDATION',
      'fn_project_create: nombre de proyecto obligatorio',
      jsonb_build_object('fn', 'fn_project_create', 'field', 'name')
    );
  END IF;
  IF v_company_id IS NULL THEN
    RETURN core.rpc_err(
      'VALIDATION',
      'fn_project_create: company_id es obligatorio',
      jsonb_build_object('fn', 'fn_project_create', 'field', 'company_id')
    );
  END IF;
  IF NOT EXISTS (SELECT 1 FROM companies WHERE id = v_company_id) THEN
    RETURN core.rpc_err(
      'VALIDATION',
      format('fn_project_create: empresa id=%s no encontrada', v_company_id),
      jsonb_build_object('fn', 'fn_project_create', 'company_id', v_company_id)
    );
  END IF;
  IF v_status NOT IN ('activo', 'pausado', 'concluido', 'cancelado') THEN
    RETURN core.rpc_err(
      'VALIDATION',
      format('fn_project_create: estado inválido %s', v_status),
      jsonb_build_object('fn', 'fn_project_create', 'field', 'status', 'value', v_status)
    );
  END IF;
  IF v_start_date IS NOT NULL AND v_end_date IS NOT NULL AND v_end_date < v_start_date THEN
    RETURN core.rpc_err(
      'VALIDATION',
      'fn_project_create: la fecha de término no puede ser anterior al inicio',
      jsonb_build_object('fn', 'fn_project_create', 'field', 'end_date')
    );
  END IF;

  PERFORM pg_advisory_xact_lock(41092001);

  v_code_env := core.fn_next_project_code('{}'::jsonb);
  IF NOT COALESCE((v_code_env->>'ok')::boolean, false) THEN
    RETURN v_code_env;
  END IF;
  v_code := v_code_env->'data'->>'code';
  INSERT INTO projects (
    code, name, address, theme_id, status, company_id, contract_amount,
    start_date, end_date, resident_name, siroc, payroll_tax_pct, tenant_id
  ) VALUES (
    v_code, v_name, v_address, v_theme_id, v_status, v_company_id, v_contract_amount,
    v_start_date, v_end_date, v_resident_name, v_siroc, v_payroll_tax_pct, v_tid
  )
  RETURNING * INTO v_row;
  RETURN core.rpc_created(
    jsonb_build_object('id', v_row.id, 'code', v_row.code, 'status', v_row.status),
    format('Proyecto %s (%s) creado', v_row.code, v_row.name),
    jsonb_build_object('fn', 'fn_project_create', 'id', v_row.id, 'code', v_row.code)
  );
EXCEPTION WHEN OTHERS THEN
  RETURN core.rpc_from_exception('fn_project_create', SQLSTATE, SQLERRM);
END;
$$;