CREATE TABLE IF NOT EXISTS settings.unit_of_measures (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    uom_code varchar(40) NOT NULL,
    uom_name varchar(120) NOT NULL,
    uom_category varchar(40) NOT NULL DEFAULT 'count',
    symbol varchar(20),
    base_unit_code varchar(40),
    conversion_factor_to_base numeric(18, 8) NOT NULL DEFAULT 1,
    decimal_precision integer NOT NULL DEFAULT 0,
    allow_fraction boolean NOT NULL DEFAULT false,
    is_base_unit boolean NOT NULL DEFAULT false,
    is_active boolean NOT NULL DEFAULT true,
    notes text,
    created_by uuid,
    updated_by uuid,
    deleted_by uuid,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at timestamp without time zone,
    CONSTRAINT unit_of_measures_decimal_precision_check CHECK (decimal_precision BETWEEN 0 AND 8),
    CONSTRAINT unit_of_measures_conversion_factor_check CHECK (conversion_factor_to_base > 0),
    CONSTRAINT unit_of_measures_category_check CHECK (
        uom_category IN ('count', 'weight', 'volume', 'length', 'area', 'time', 'package', 'service', 'custom')
    )
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_unit_of_measures_tenant_code_active
    ON settings.unit_of_measures (tenant_id, lower(uom_code))
    WHERE deleted_at IS NULL;

CREATE INDEX IF NOT EXISTS idx_unit_of_measures_tenant_category
    ON settings.unit_of_measures (tenant_id, uom_category, is_active)
    WHERE deleted_at IS NULL;

INSERT INTO settings.unit_of_measures (
    tenant_id,
    uom_code,
    uom_name,
    uom_category,
    symbol,
    base_unit_code,
    conversion_factor_to_base,
    decimal_precision,
    allow_fraction,
    is_base_unit,
    is_active,
    notes
)
SELECT
    tenant.id,
    seed.uom_code,
    seed.uom_name,
    seed.uom_category,
    seed.symbol,
    seed.base_unit_code,
    seed.conversion_factor_to_base,
    seed.decimal_precision,
    seed.allow_fraction,
    seed.is_base_unit,
    true,
    seed.notes
FROM platform.tenants tenant
CROSS JOIN (
    VALUES
        ('EA', 'Each', 'count', 'ea', 'EA', 1::numeric, 0, false, true, 'Default count unit for discrete items.'),
        ('PCS', 'Pieces', 'count', 'pcs', 'EA', 1::numeric, 0, false, false, 'Pieces mapped to each for countable stock.'),
        ('BOX', 'Box', 'package', 'box', 'EA', 1::numeric, 0, false, false, 'Package unit; item-specific pack size can be managed later.'),
        ('KG', 'Kilogram', 'weight', 'kg', 'KG', 1::numeric, 3, true, true, 'Base weight unit.'),
        ('G', 'Gram', 'weight', 'g', 'KG', 0.001::numeric, 3, true, false, 'Weight unit converted to kilograms.'),
        ('L', 'Litre', 'volume', 'L', 'L', 1::numeric, 3, true, true, 'Base volume unit.'),
        ('ML', 'Millilitre', 'volume', 'ml', 'L', 0.001::numeric, 3, true, false, 'Volume unit converted to litres.'),
        ('M', 'Metre', 'length', 'm', 'M', 1::numeric, 3, true, true, 'Base length unit.')
) AS seed(uom_code, uom_name, uom_category, symbol, base_unit_code, conversion_factor_to_base, decimal_precision, allow_fraction, is_base_unit, notes)
WHERE tenant.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM settings.unit_of_measures existing
      WHERE existing.tenant_id = tenant.id
        AND lower(existing.uom_code) = lower(seed.uom_code)
        AND existing.deleted_at IS NULL
  );