-- Items: ITEM document series for auto item_code generation
-- Run before deploying BE that auto-generates item_code via document_series.

BEGIN;

INSERT INTO document_series (code, prefix, current_number, padding, description, is_active)
SELECT
  'ITEM',
  'ITM-',
  COALESCE((
    SELECT COUNT(*)::int
    FROM items
    WHERE deleted_at IS NULL
  ), 0),
  5,
  'Item master code',
  TRUE
WHERE NOT EXISTS (
  SELECT 1 FROM document_series WHERE code = 'ITEM'
);

UPDATE document_series ds
SET current_number = GREATEST(
  ds.current_number,
  COALESCE((SELECT COUNT(*)::int FROM items WHERE deleted_at IS NULL), 0)
),
updated_at = NOW()
WHERE ds.code = 'ITEM';

COMMIT;

-- Verify:
-- SELECT code, prefix, current_number, padding FROM document_series WHERE code = 'ITEM';