BEGIN;

CREATE TABLE IF NOT EXISTS warehouse.pick_exceptions (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL REFERENCES platform.tenants(id) ON DELETE CASCADE,
    exception_no varchar(100) NOT NULL,
    pick_list_id bigint,
    pick_list_no varchar(120),
    pick_list_item_id bigint,
    sales_order_id bigint,
    sales_order_no varchar(120),
    stock_allocation_id bigint,
    stock_allocation_no varchar(100),
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    ordered_quantity numeric(18,4) NOT NULL DEFAULT 0,
    picked_quantity numeric(18,4) NOT NULL DEFAULT 0,
    exception_quantity numeric(18,4) NOT NULL DEFAULT 0,
    unit_of_measure varchar(50),
    uom_id bigint,
    exception_type varchar(80) NOT NULL,
    exception_reason text NOT NULL,
    resolution_action varchar(80),
    resolution_notes text,
    status varchar(40) NOT NULL DEFAULT 'open',
    supervisor_override boolean NOT NULL DEFAULT false,
    override_reason text,
    reported_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reported_by_name varchar(255),
    resolved_at timestamp,
    resolved_by_name varchar(255),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT uq_warehouse_pick_exceptions_no UNIQUE (tenant_id, exception_no)
);

ALTER TABLE IF EXISTS warehouse.pick_list_items
    ADD COLUMN IF NOT EXISTS exception_status varchar(40),
    ADD COLUMN IF NOT EXISTS exception_type varchar(80),
    ADD COLUMN IF NOT EXISTS exception_reason text,
    ADD COLUMN IF NOT EXISTS exception_quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS supervisor_override boolean NOT NULL DEFAULT false,
    ADD COLUMN IF NOT EXISTS override_reason text;

CREATE INDEX IF NOT EXISTS idx_warehouse_pick_exceptions_pick_list
    ON warehouse.pick_exceptions (tenant_id, pick_list_id, status);

CREATE INDEX IF NOT EXISTS idx_warehouse_pick_exceptions_item
    ON warehouse.pick_exceptions (tenant_id, item_id, status);

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
    ('warehouse', 'warehouse.picking.exceptions.view', 'View Warehouse Picking Exceptions', 'Allows viewing picking exceptions.'),
    ('warehouse', 'warehouse.picking.exceptions.manage', 'Manage Warehouse Picking Exceptions', 'Allows resolving picking exceptions.'),
    ('warehouse', 'warehouse.picking.override', 'Override Warehouse Picking Exceptions', 'Allows supervisor override of picking exceptions.')
) 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 (
      'warehouse.picking.exceptions.view',
      'warehouse.picking.exceptions.manage',
      'warehouse.picking.override'
  )
  AND (
      LOWER(COALESCE(role.code, '')) IN ('tenant_owner', 'tenant-owner', 'owner', 'admin', 'administrator', 'warehouse_manager')
      OR LOWER(COALESCE(role.name, '')) IN ('tenant owner', 'owner', 'admin', 'administrator', '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 = 'warehouse.picking.reserve'
      )
  )
  AND NOT EXISTS (
      SELECT 1
      FROM iam.role_permissions existing
      WHERE existing.role_id = role.id
        AND existing.permission_id = permission.id
  );

COMMIT;