eden/packages/database/migrations/1788447739229_client-registrations.cjs

/**
 * Customer 360 / Client Registration — eden.md §3 §1.1-1.2. Authenticated-
 * only MVP (Master Prompt §6.5): deliberately out of scope for this round
 * — the public unauthenticated token-based self-service form
 * (`GET/POST /public-client-registration/:token`), file attachment
 * upload/storage, CNPJ/CEP external lookup integration, the "official"
 * `cnpj_address_*` Receita Federal address block, and the
 * `*_externally_verified`/`*_verification_*` columns (only the format-
 * check booleans `cpf_format_valid`/`cnpj_format_valid` are kept). Those
 * all land in a later phase.
 *
 * `quote_id` is a plain nullable UUID WITHOUT a foreign key — the `quotes`
 * table was built in a parallel migration; the FK linking the two is
 * added in a follow-up migration once both are confirmed present (mirrors
 * the legacy's own history: client_registration_id was itself added to
 * quotes in a later migration, 1785513008756).
 */

exports.shorthands = undefined;

exports.up = (pgm) => {
  pgm.sql(`
    CREATE SEQUENCE client_registrations_client_code_seq START 1;

    CREATE TABLE client_registrations (
      id                          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      created_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at                  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by                  UUID REFERENCES users(id),
      quote_id                    UUID,
      reseller_id                 UUID REFERENCES resellers(id),
      token                       TEXT NOT NULL UNIQUE,
      token_created_at            TIMESTAMPTZ NOT NULL DEFAULT now(),
      client_code                 INTEGER NOT NULL UNIQUE DEFAULT nextval('client_registrations_client_code_seq'),
      is_portability              BOOLEAN NOT NULL DEFAULT false,
      person_type                 TEXT NOT NULL CHECK (person_type IN ('pf','pj')),
      registration_status         TEXT NOT NULL DEFAULT 'rascunho' CHECK (registration_status IN ('rascunho','pendente_validacao','ativo','bloqueado','inativo')),
      submitted_at                TIMESTAMPTZ,
      activated_at                TIMESTAMPTZ,
      internal_notes              TEXT,
      address_zip                 TEXT,
      address_street              TEXT,
      address_number              TEXT,
      address_complement          TEXT,
      address_neighborhood        TEXT,
      address_city                TEXT,
      address_state               TEXT,
      address_country             TEXT NOT NULL DEFAULT 'Brasil',
      contact_principal_name      TEXT,
      contact_principal_email     TEXT,
      contact_principal_phone     TEXT,
      contact_financial_name      TEXT,
      contact_financial_email     TEXT,
      contact_financial_phone     TEXT,
      contact_technical_name      TEXT,
      contact_technical_email     TEXT,
      contact_technical_phone     TEXT,
      pf_full_name                TEXT,
      pf_cpf                      TEXT,
      pf_birth_date                DATE,
      pf_email                    TEXT,
      pf_mobile_phone             TEXT,
      pf_alt_phone                TEXT,
      pf_social_name              TEXT,
      pf_id_document               TEXT,
      pf_id_issuer                 TEXT,
      pj_cnpj                     TEXT,
      pj_company_name             TEXT,
      pj_trade_name                TEXT,
      pj_state_registration        TEXT,
      pj_municipal_registration    TEXT,
      pj_opening_date              DATE,
      pj_legal_nature              TEXT,
      pj_cnpj_situation            TEXT,
      pj_main_activity             TEXT,
      pj_rep_full_name             TEXT,
      pj_rep_cpf                   TEXT,
      pj_rep_role                  TEXT,
      pj_rep_email                 TEXT,
      pj_rep_mobile_phone          TEXT,
      pj_rep_authorization_ack     BOOLEAN NOT NULL DEFAULT false,
      cpf_format_valid             BOOLEAN,
      cnpj_format_valid            BOOLEAN,
      portability_numbers          JSONB NOT NULL DEFAULT '[]'::jsonb,
      portability_ranges           JSONB NOT NULL DEFAULT '[]'::jsonb,
      ixc_client_id                TEXT,
      ixc_contract_number          TEXT
    );
    COMMENT ON TABLE client_registrations IS
      'Customer 360 / cadastro de cliente PF/PJ — eden.md §3. MVP autenticado apenas (sem fluxo público por token, sem anexos, sem lookup externo de CNPJ/CEP) — ver comentário no topo desta migration.';
    COMMENT ON COLUMN client_registrations.quote_id IS
      'Referencia quotes(id) sem FK por enquanto — FK a ser adicionada em migration futura.';
    COMMENT ON COLUMN client_registrations.pf_cpf IS
      'Imutável fora do fluxo de submissão original — nunca exposto em rota de edição administrativa (eden.md §1.1.2). Mesma regra para pj_cnpj e pj_rep_cpf.';

    CREATE TRIGGER trg_client_registrations_updated_at BEFORE UPDATE ON client_registrations
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    CREATE INDEX idx_client_registrations_status ON client_registrations(registration_status);
    CREATE INDEX idx_client_registrations_reseller ON client_registrations(reseller_id);
    CREATE INDEX idx_client_registrations_created_by ON client_registrations(created_by);

    CREATE TABLE client_registration_partners (
      id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      client_registration_id  UUID NOT NULL REFERENCES client_registrations(id) ON DELETE CASCADE,
      name                     TEXT NOT NULL,
      document                 TEXT,
      qualification            TEXT,
      source                   TEXT NOT NULL DEFAULT 'manual',
      cpf                      TEXT,
      will_sign                BOOLEAN NOT NULL DEFAULT false,
      signature_email          TEXT,
      created_at               TIMESTAMPTZ NOT NULL DEFAULT now()
    );
    COMMENT ON TABLE client_registration_partners IS
      'Sócios/QSA — eden.md §1.2, §4. source default "manual" (não "brasilapi" como no legado) — sem integração de lookup de CNPJ nesta fase.';

    CREATE INDEX idx_client_registration_partners_registration ON client_registration_partners(client_registration_id);
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    DROP TABLE IF EXISTS client_registration_partners;
    DROP TABLE IF EXISTS client_registrations;
    DROP SEQUENCE IF EXISTS client_registrations_client_code_seq;
  `);
};