eden/packages/database/migrations/1789694035000_product-catalogs.cjs

/**
 * Products bounded context, follow-up slice: turns Tipo/Grupo/Subgrupo/
 * Marca/Unidade from free TEXT columns on `products` (see
 * 1788438907074_products.cjs header: "`product_type` is free text for
 * now ... tightening it to a CHECK/lookup table is future work") into
 * five first-class, cadastrable catalogs — `product_types`, `product_groups`,
 * `product_subgroups`, `brands`, `units`. Same "simple catalog" shape
 * already used by `fiscal_ncm`/`fiscal_cfop` (1788457368744_fiscal.cjs),
 * minus the `source`/`source_version`/`metadata` sync-log columns: unlike
 * the fiscal catalogs, these five are never imported from an external
 * source — they are 100% manually curated by the ERP user, so those
 * columns would just sit unused.
 *
 * `product_subgroups` is the one catalog with an extra shape difference:
 * it always belongs to a `product_groups` row (`group_id NOT NULL`,
 * `ON DELETE RESTRICT` — a group in use by a subgroup can't be deleted
 * out from under it, mirroring every other FK to these catalogs from
 * `products`). Uniqueness is case-insensitive and, for subgroups, scoped
 * to the owning group: the same subgroup name may exist under two
 * different groups, but not twice under the same one.
 *
 * Data migration note (verified against the real dev/QA Postgres before
 * writing this migration): `products` has 37 rows today, all
 * test/QA data (`qa-test-`/`Live Test` fixtures plus one manual "Link
 * Dedicado 100Mbps" row). `product_group`/`product_subgroup`/`brand` are
 * empty on every row; `unit` is always `'unidade'`; `product_type` is only
 * ever `'service'` or `'telecom'`. Given that, the backfill below is
 * intentionally the simplest thing that is still correct — no need for a
 * sophisticated historical-data-preservation strategy for data that never
 * existed. `product_groups`/`product_subgroups` backfill is best-effort:
 * since the group->subgroup relationship never existed in the legacy TEXT
 * columns (there was no FK to reconstruct it from), any distinct
 * `product_subgroup` value found without a way to attribute it to a
 * specific group is simply left unmigrated — the affected product's new
 * `product_subgroup_id` stays NULL rather than inventing a placeholder
 * "generic" group the user never asked for. In practice this branch is
 * moot today (no environment has a populated `product_subgroup`), but the
 * decision is documented here for whoever re-reads this after real data
 * exists.
 *
 * `product_type_id`/`unit_id` end up NOT NULL (mirroring the TEXT columns
 * they replace, which were both `NOT NULL`); `product_group_id`/
 * `product_subgroup_id`/`brand_id` stay nullable, exactly like the TEXT
 * columns they replace were optional.
 */

exports.shorthands = undefined;

exports.up = (pgm) => {
  pgm.sql(`
    -- =====================================================================
    -- product_types / product_groups / brands / units — identical "simple
    -- catalog" shape, manually curated (no source/source_version/metadata,
    -- unlike the fiscal catalogs — these are never imported).
    -- =====================================================================
    CREATE TABLE product_types (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name        TEXT NOT NULL,
      active      BOOLEAN NOT NULL DEFAULT true,
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      updated_by  UUID REFERENCES users(id)
    );
    COMMENT ON TABLE product_types IS
      'Catálogo de Tipos de produto (eden.md §2 §1.1) — antes TEXT livre em products.product_type. Cadastro manual, sem exclusão física (active=false é o "apagar"). Leitura aberta a qualquer autenticado, escrita exige products:edit.';
    CREATE UNIQUE INDEX idx_product_types_name ON product_types (lower(name));
    CREATE INDEX idx_product_types_active ON product_types (active);
    CREATE TRIGGER trg_product_types_updated_at BEFORE UPDATE ON product_types
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    CREATE TABLE product_groups (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name        TEXT NOT NULL,
      active      BOOLEAN NOT NULL DEFAULT true,
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      updated_by  UUID REFERENCES users(id)
    );
    COMMENT ON TABLE product_groups IS
      'Catálogo de Grupos de produto — antes TEXT livre em products.product_group. Cadastro manual, sem exclusão física.';
    CREATE UNIQUE INDEX idx_product_groups_name ON product_groups (lower(name));
    CREATE INDEX idx_product_groups_active ON product_groups (active);
    CREATE TRIGGER trg_product_groups_updated_at BEFORE UPDATE ON product_groups
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    CREATE TABLE product_subgroups (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      group_id    UUID NOT NULL REFERENCES product_groups(id) ON DELETE RESTRICT,
      name        TEXT NOT NULL,
      active      BOOLEAN NOT NULL DEFAULT true,
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      updated_by  UUID REFERENCES users(id)
    );
    COMMENT ON TABLE product_subgroups IS
      'Catálogo de Subgrupos de produto — antes TEXT livre em products.product_subgroup. Sempre pertence a um grupo (group_id NOT NULL, ON DELETE RESTRICT). Nome único case-insensitivo por grupo, não globalmente: o mesmo nome pode existir em grupos diferentes.';
    CREATE UNIQUE INDEX idx_product_subgroups_group_name ON product_subgroups (group_id, lower(name));
    CREATE INDEX idx_product_subgroups_active ON product_subgroups (active);
    CREATE INDEX idx_product_subgroups_group ON product_subgroups (group_id);
    CREATE TRIGGER trg_product_subgroups_updated_at BEFORE UPDATE ON product_subgroups
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    CREATE TABLE brands (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name        TEXT NOT NULL,
      active      BOOLEAN NOT NULL DEFAULT true,
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      updated_by  UUID REFERENCES users(id)
    );
    COMMENT ON TABLE brands IS
      'Catálogo de Marcas — antes TEXT livre em products.brand. Cadastro manual, sem exclusão física.';
    CREATE UNIQUE INDEX idx_brands_name ON brands (lower(name));
    CREATE INDEX idx_brands_active ON brands (active);
    CREATE TRIGGER trg_brands_updated_at BEFORE UPDATE ON brands
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    CREATE TABLE units (
      id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
      name        TEXT NOT NULL,
      active      BOOLEAN NOT NULL DEFAULT true,
      created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      updated_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
      created_by  UUID REFERENCES users(id),
      updated_by  UUID REFERENCES users(id)
    );
    COMMENT ON TABLE units IS
      'Catálogo de Unidades de medida — antes TEXT livre em products.unit (default legado ''unidade''). Cadastro manual, sem exclusão física.';
    CREATE UNIQUE INDEX idx_units_name ON units (lower(name));
    CREATE INDEX idx_units_active ON units (active);
    CREATE TRIGGER trg_units_updated_at BEFORE UPDATE ON units
      FOR EACH ROW EXECUTE FUNCTION set_updated_at();

    INSERT INTO units (name) VALUES ('Unidade') ON CONFLICT (lower(name)) DO NOTHING;

    -- =====================================================================
    -- products: swap the 5 free-TEXT columns for FK columns into the
    -- catalogs above. Nullable at first on purpose — tightened to NOT NULL
    -- (product_type_id/unit_id only) after the backfill below.
    -- =====================================================================
    ALTER TABLE products
      ADD COLUMN product_type_id     UUID REFERENCES product_types(id),
      ADD COLUMN product_group_id    UUID REFERENCES product_groups(id) ON DELETE RESTRICT,
      ADD COLUMN product_subgroup_id UUID REFERENCES product_subgroups(id) ON DELETE RESTRICT,
      ADD COLUMN brand_id            UUID REFERENCES brands(id) ON DELETE RESTRICT,
      ADD COLUMN unit_id             UUID REFERENCES units(id) ON DELETE RESTRICT;

    INSERT INTO product_types (name)
      SELECT DISTINCT product_type FROM products
      WHERE product_type IS NOT NULL AND product_type <> ''
      ON CONFLICT DO NOTHING;

    INSERT INTO brands (name)
      SELECT DISTINCT brand FROM products
      WHERE brand IS NOT NULL AND brand <> ''
      ON CONFLICT DO NOTHING;

    INSERT INTO units (name)
      SELECT DISTINCT unit FROM products
      WHERE unit IS NOT NULL AND unit <> ''
      ON CONFLICT DO NOTHING;

    -- Best-effort only — see migration header for why group/subgroup
    -- relationship isn't (and can't be) reconstructed from legacy data.
    INSERT INTO product_groups (name)
      SELECT DISTINCT product_group FROM products
      WHERE product_group IS NOT NULL AND product_group <> ''
      ON CONFLICT DO NOTHING;

    UPDATE products SET product_type_id = pt.id
      FROM product_types pt WHERE lower(pt.name) = lower(products.product_type);

    UPDATE products SET brand_id = b.id
      FROM brands b WHERE lower(b.name) = lower(products.brand);

    UPDATE products SET unit_id = u.id
      FROM units u WHERE lower(u.name) = lower(products.unit);

    UPDATE products SET product_group_id = pg.id
      FROM product_groups pg WHERE lower(pg.name) = lower(products.product_group);

    -- product_subgroup_id intentionally left NULL here: no legacy data
    -- ever populated products.product_subgroup with a resolvable group,
    -- so there is nothing safe to backfill (see migration header).

    ALTER TABLE products
      DROP COLUMN product_type,
      DROP COLUMN product_group,
      DROP COLUMN product_subgroup,
      DROP COLUMN brand,
      DROP COLUMN unit;

    ALTER TABLE products
      ALTER COLUMN product_type_id SET NOT NULL,
      ALTER COLUMN unit_id SET NOT NULL;

    CREATE INDEX idx_products_product_type_id ON products (product_type_id);
    CREATE INDEX idx_products_product_group_id ON products (product_group_id);
    CREATE INDEX idx_products_product_subgroup_id ON products (product_subgroup_id);
    CREATE INDEX idx_products_brand_id ON products (brand_id);
    CREATE INDEX idx_products_unit_id ON products (unit_id);
  `);
};

exports.down = (pgm) => {
  pgm.sql(`
    ALTER TABLE products
      ADD COLUMN product_type     TEXT,
      ADD COLUMN product_group    TEXT,
      ADD COLUMN product_subgroup TEXT,
      ADD COLUMN brand            TEXT,
      ADD COLUMN unit             TEXT;

    UPDATE products SET product_type = pt.name
      FROM product_types pt WHERE pt.id = products.product_type_id;

    UPDATE products SET product_group = pg.name
      FROM product_groups pg WHERE pg.id = products.product_group_id;

    UPDATE products SET product_subgroup = psg.name
      FROM product_subgroups psg WHERE psg.id = products.product_subgroup_id;

    UPDATE products SET brand = b.name
      FROM brands b WHERE b.id = products.brand_id;

    UPDATE products SET unit = u.name
      FROM units u WHERE u.id = products.unit_id;

    UPDATE products SET unit = 'unidade' WHERE unit IS NULL;
    UPDATE products SET product_type = 'service' WHERE product_type IS NULL;

    ALTER TABLE products
      ALTER COLUMN product_type SET NOT NULL,
      ALTER COLUMN unit SET NOT NULL,
      ALTER COLUMN unit SET DEFAULT 'unidade';

    DROP INDEX IF EXISTS idx_products_product_type_id;
    DROP INDEX IF EXISTS idx_products_product_group_id;
    DROP INDEX IF EXISTS idx_products_product_subgroup_id;
    DROP INDEX IF EXISTS idx_products_brand_id;
    DROP INDEX IF EXISTS idx_products_unit_id;

    ALTER TABLE products
      DROP COLUMN product_type_id,
      DROP COLUMN product_group_id,
      DROP COLUMN product_subgroup_id,
      DROP COLUMN brand_id,
      DROP COLUMN unit_id;

    DROP TABLE IF EXISTS product_subgroups;
    DROP TABLE IF EXISTS product_groups;
    DROP TABLE IF EXISTS product_types;
    DROP TABLE IF EXISTS brands;
    DROP TABLE IF EXISTS units;
  `);
};