BEGIN;

CREATE SCHEMA IF NOT EXISTS inventory;

ALTER TABLE IF EXISTS inventory.items
    ADD COLUMN IF NOT EXISTS inventory_tracking_type varchar(40) DEFAULT 'none',
    ADD COLUMN IF NOT EXISTS lot_tracking boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS batch_tracking boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS expiry_controlled boolean DEFAULT FALSE,
    ADD COLUMN IF NOT EXISTS shelf_life_days integer,
    ADD COLUMN IF NOT EXISTS minimum_remaining_shelf_life_days integer,
    ADD COLUMN IF NOT EXISTS preferred_stock_selection_strategy varchar(40),
    ADD COLUMN IF NOT EXISTS preferred_putaway_strategy varchar(60),
    ADD COLUMN IF NOT EXISTS temperature_requirement varchar(80),
    ADD COLUMN IF NOT EXISTS hazard_class varchar(80),
    ADD COLUMN IF NOT EXISTS abc_classification varchar(10),
    ADD COLUMN IF NOT EXISTS costing_policy_override varchar(60);

CREATE TABLE IF NOT EXISTS inventory.inventory_policies (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    policy_code varchar(80) NOT NULL,
    policy_name varchar(160) NOT NULL,
    description text,
    default_stock_selection_strategy varchar(40) NOT NULL DEFAULT 'auto',
    default_costing_method varchar(60) NOT NULL DEFAULT 'moving_weighted_average',
    default_putaway_strategy varchar(60) NOT NULL DEFAULT 'capacity_zone',
    negative_stock_policy varchar(40) NOT NULL DEFAULT 'blocked',
    reservation_policy varchar(60) NOT NULL DEFAULT 'reserve_on_approval',
    manual_override_policy varchar(60) NOT NULL DEFAULT 'permission_and_reason',
    status varchar(40) NOT NULL DEFAULT 'active',
    is_default boolean NOT NULL DEFAULT FALSE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_inventory_policies_tenant_code UNIQUE (tenant_id, policy_code),
    CONSTRAINT chk_inventory_policies_stock_strategy CHECK (default_stock_selection_strategy IN ('auto','fifo','lifo','fefo','lefo','specific_lot','specific_serial','manual')),
    CONSTRAINT chk_inventory_policies_costing_method CHECK (default_costing_method IN ('moving_weighted_average','fifo_cost','standard_cost','specific_identification')),
    CONSTRAINT chk_inventory_policies_negative_stock CHECK (negative_stock_policy IN ('blocked','approval_required','allowed_with_reason'))
);

CREATE TABLE IF NOT EXISTS inventory.inventory_policy_versions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    policy_id bigint NOT NULL REFERENCES inventory.inventory_policies(id) ON DELETE CASCADE,
    version_no integer NOT NULL DEFAULT 1,
    effective_from date NOT NULL DEFAULT CURRENT_DATE,
    effective_to date,
    status varchar(40) NOT NULL DEFAULT 'approved',
    stock_selection_strategy varchar(40) NOT NULL DEFAULT 'auto',
    costing_method varchar(60) NOT NULL DEFAULT 'moving_weighted_average',
    putaway_strategy varchar(60) NOT NULL DEFAULT 'capacity_zone',
    shelf_life_rules jsonb NOT NULL DEFAULT '{}'::jsonb,
    allocation_rules jsonb NOT NULL DEFAULT '{}'::jsonb,
    putaway_rules jsonb NOT NULL DEFAULT '{}'::jsonb,
    override_rules jsonb NOT NULL DEFAULT '{}'::jsonb,
    approved_at timestamp,
    approved_by uuid,
    created_by uuid,
    updated_by uuid,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_inventory_policy_versions UNIQUE (tenant_id, policy_id, version_no),
    CONSTRAINT chk_inventory_policy_versions_dates CHECK (effective_to IS NULL OR effective_to >= effective_from),
    CONSTRAINT chk_inventory_policy_versions_strategy CHECK (stock_selection_strategy IN ('auto','fifo','lifo','fefo','lefo','specific_lot','specific_serial','manual')),
    CONSTRAINT chk_inventory_policy_versions_costing CHECK (costing_method IN ('moving_weighted_average','fifo_cost','standard_cost','specific_identification'))
);

CREATE TABLE IF NOT EXISTS inventory.inventory_policy_assignments (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    policy_id bigint NOT NULL REFERENCES inventory.inventory_policies(id) ON DELETE CASCADE,
    policy_version_id bigint REFERENCES inventory.inventory_policy_versions(id) ON DELETE SET NULL,
    assignment_scope varchar(40) NOT NULL,
    scope_id bigint,
    scope_code varchar(120),
    transaction_type varchar(80),
    priority integer NOT NULL DEFAULT 100,
    effective_from date NOT NULL DEFAULT CURRENT_DATE,
    effective_to date,
    status varchar(40) NOT NULL DEFAULT 'active',
    notes text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_by uuid,
    updated_by uuid,
    deleted_at timestamp,
    deleted_by uuid,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT chk_inventory_policy_assignments_scope CHECK (assignment_scope IN ('tenant','warehouse','warehouse_zone','item_category','item','transaction_type')),
    CONSTRAINT chk_inventory_policy_assignments_dates CHECK (effective_to IS NULL OR effective_to >= effective_from)
);

CREATE TABLE IF NOT EXISTS inventory.inventory_policy_resolution_audit (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    policy_id bigint,
    policy_version_id bigint,
    item_id bigint,
    warehouse_id bigint,
    transaction_type varchar(80),
    resolved_stock_selection_strategy varchar(40),
    resolved_costing_method varchar(60),
    resolved_putaway_strategy varchar(60),
    resolution_source varchar(80),
    reason text,
    request_context jsonb NOT NULL DEFAULT '{}'::jsonb,
    resolved_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    resolved_by uuid
);

CREATE TABLE IF NOT EXISTS inventory.inventory_override_logs (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    policy_id bigint,
    policy_version_id bigint,
    source_document_type varchar(80),
    source_document_id bigint,
    source_document_no varchar(120),
    item_id bigint,
    warehouse_id bigint,
    override_type varchar(80) NOT NULL,
    recommended_selection jsonb NOT NULL DEFAULT '{}'::jsonb,
    actual_selection jsonb NOT NULL DEFAULT '{}'::jsonb,
    override_reason text NOT NULL,
    approval_status varchar(40) NOT NULL DEFAULT 'not_required',
    approved_at timestamp,
    approved_by uuid,
    overridden_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    overridden_by uuid,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb
);

CREATE INDEX IF NOT EXISTS idx_inventory_policy_versions_effective
    ON inventory.inventory_policy_versions (tenant_id, policy_id, status, effective_from, effective_to);

CREATE INDEX IF NOT EXISTS idx_inventory_policy_assignments_scope
    ON inventory.inventory_policy_assignments (tenant_id, assignment_scope, scope_id, scope_code, transaction_type, status);

CREATE INDEX IF NOT EXISTS idx_inventory_policy_resolution_audit_lookup
    ON inventory.inventory_policy_resolution_audit (tenant_id, item_id, warehouse_id, transaction_type, resolved_at DESC);

CREATE INDEX IF NOT EXISTS idx_inventory_override_logs_lookup
    ON inventory.inventory_override_logs (tenant_id, item_id, warehouse_id, override_type, overridden_at DESC);

INSERT INTO inventory.inventory_policies (
    tenant_id, policy_code, policy_name, description, default_stock_selection_strategy,
    default_costing_method, default_putaway_strategy, negative_stock_policy,
    reservation_policy, manual_override_policy, status, is_default, metadata
)
SELECT
    t.id,
    'DEFAULT',
    'Default Inventory Policy',
    'Default Rails ERP inventory policy: automatic FIFO/FEFO stock selection, moving weighted average costing, capacity-aware putaway, and blocked negative stock.',
    'auto',
    'moving_weighted_average',
    'capacity_zone',
    'blocked',
    'reserve_on_approval',
    'permission_and_reason',
    'active',
    TRUE,
    '{"source":"inventory_policy_engine_seed"}'::jsonb
FROM platform.tenants t
WHERE t.deleted_at IS NULL
  AND NOT EXISTS (
      SELECT 1
      FROM inventory.inventory_policies existing
      WHERE existing.tenant_id = t.id
        AND existing.policy_code = 'DEFAULT'
  );

INSERT INTO inventory.inventory_policy_versions (
    tenant_id, policy_id, version_no, effective_from, status, stock_selection_strategy,
    costing_method, putaway_strategy, shelf_life_rules, allocation_rules, putaway_rules,
    override_rules, approved_at
)
SELECT
    p.tenant_id,
    p.id,
    1,
    CURRENT_DATE,
    'approved',
    'auto',
    'moving_weighted_average',
    'capacity_zone',
    '{"expired_stock":"block","near_expiry_warning_days":30,"minimum_remaining_shelf_life_days":0}'::jsonb,
    '{"allow_partial_allocation":true,"exclude_reserved":true,"exclude_quarantine":true,"exclude_non_pickable_locations":true}'::jsonb,
    '{"respect_capacity":true,"prefer_same_item_consolidation":true,"respect_temperature":true,"respect_hazard_class":true}'::jsonb,
    '{"manual_override_requires_permission":true,"manual_override_requires_reason":true,"expired_stock_override_allowed":false}'::jsonb,
    CURRENT_TIMESTAMP
FROM inventory.inventory_policies p
WHERE p.policy_code = 'DEFAULT'
  AND NOT EXISTS (
      SELECT 1
      FROM inventory.inventory_policy_versions existing
      WHERE existing.tenant_id = p.tenant_id
        AND existing.policy_id = p.id
        AND existing.version_no = 1
  );

INSERT INTO inventory.inventory_policy_assignments (
    tenant_id, policy_id, policy_version_id, assignment_scope, priority, status, notes, metadata
)
SELECT
    p.tenant_id,
    p.id,
    v.id,
    'tenant',
    10,
    'active',
    'Default tenant policy assignment.',
    '{"source":"inventory_policy_engine_seed"}'::jsonb
FROM inventory.inventory_policies p
JOIN inventory.inventory_policy_versions v
  ON v.tenant_id = p.tenant_id
 AND v.policy_id = p.id
 AND v.version_no = 1
WHERE p.policy_code = 'DEFAULT'
  AND NOT EXISTS (
      SELECT 1
      FROM inventory.inventory_policy_assignments existing
      WHERE existing.tenant_id = p.tenant_id
        AND existing.policy_id = p.id
        AND existing.assignment_scope = 'tenant'
        AND existing.deleted_at IS NULL
  );

INSERT INTO iam.permissions (id, module_code, permission_code, permission_name, description)
SELECT gen_random_uuid(), seed.module_code, seed.permission_code, seed.permission_name, seed.description
FROM (VALUES
    ('inventory', 'inventory.policy.view', 'View Inventory Policies', 'Allows viewing inventory policy, strategy, and policy-resolution records.'),
    ('inventory', 'inventory.policy.manage', 'Manage Inventory Policies', 'Allows creating and updating inventory policy, version, and assignment records.'),
    ('inventory', 'inventory.policy.approve', 'Approve Inventory Policies', 'Allows approving inventory policy versions and sensitive policy changes.'),
    ('inventory', 'inventory.allocation.view', 'View Inventory Allocations', 'Allows viewing stock allocation and policy-resolution evidence.'),
    ('inventory', 'inventory.allocation.override', 'Override Inventory Allocation', 'Allows controlled override of system-recommended stock allocations.'),
    ('inventory', 'inventory.cost.view', 'View Inventory Costing', 'Allows viewing inventory costing methods, average costs, and valuation evidence.'),
    ('inventory', 'inventory.cost.manage', 'Manage Inventory Costing', 'Allows managing inventory costing policies and valuation controls.')
) AS seed(module_code, permission_code, permission_name, description)
WHERE NOT EXISTS (
    SELECT 1 FROM iam.permissions permission WHERE permission.permission_code = seed.permission_code
);

INSERT INTO iam.role_permissions (id, role_id, permission_id, created_at)
SELECT gen_random_uuid(), role.id, permission.id, CURRENT_TIMESTAMP
FROM iam.roles role
CROSS JOIN iam.permissions permission
WHERE role.tenant_id IS NOT NULL
  AND role.is_active = TRUE
  AND permission.permission_code IN (
      'inventory.policy.view',
      'inventory.policy.manage',
      'inventory.policy.approve',
      'inventory.allocation.view',
      'inventory.allocation.override',
      'inventory.cost.view',
      'inventory.cost.manage'
  )
  AND (
      LOWER(COALESCE(role.code, '')) IN ('tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator', 'inventory_manager', 'warehouse_manager')
      OR LOWER(COALESCE(role.name, '')) IN ('tenant owner', 'owner', 'admin', 'administrator', 'inventory manager', 'warehouse manager')
      OR EXISTS (
          SELECT 1
          FROM iam.role_permissions existing_role_permission
          INNER JOIN iam.permissions existing_permission
              ON existing_permission.id = existing_role_permission.permission_id
          WHERE existing_role_permission.role_id = role.id
            AND existing_permission.permission_code IN ('inventory.view', 'warehouse.view')
      )
  )
  AND NOT EXISTS (
      SELECT 1
      FROM iam.role_permissions existing
      WHERE existing.role_id = role.id
        AND existing.permission_id = permission.id
  );

COMMIT;