BEGIN; CREATE SCHEMA IF NOT EXISTS warehouse; CREATE TABLE IF NOT EXISTS warehouse.warehouses ( id bigserial PRIMARY KEY, tenant_id uuid, warehouse_code text, code text, warehouse_name text, name text, address_line text, location text, city text, country text, warehouse_type text, type text, total_capacity numeric(18,4) DEFAULT 0, used_capacity numeric(18,4) DEFAULT 0, capacity_uom text, active_item_count integer DEFAULT 0, manager_name text, cost_center_code text, receiving_policy text, status text DEFAULT 'active', is_active boolean DEFAULT true, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.bin_locations ( id bigserial PRIMARY KEY, tenant_id uuid, warehouse_id bigint, warehouse_code text, warehouse_name text, location_code text, code text, location_name text, name text, location_type text, type text, parent_location_id bigint, parent_code text, zone_code text, rack_code text, shelf_code text, bin_code text, capacity numeric(18,4) DEFAULT 0, occupied_capacity numeric(18,4) DEFAULT 0, active_item_count integer DEFAULT 0, utilization_percent numeric(8,4) DEFAULT 0, notes text, status text DEFAULT 'active', is_active boolean DEFAULT true, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.receiving_tasks ( id bigserial PRIMARY KEY, tenant_id uuid, task_no text, receiving_no text, grn_no text, purchase_order_id bigint, purchase_order_no text, supplier_id bigint, supplier_name text, warehouse_id bigint, warehouse_code text, warehouse_name text, expected_date date, received_at timestamp without time zone, status text DEFAULT 'pending', inspection_status text, putaway_status text, discrepancy_status text, expected_item_count integer DEFAULT 0, received_item_count integer DEFAULT 0, shortlanded_count integer DEFAULT 0, overage_count integer DEFAULT 0, total_received_amount numeric(18,4) DEFAULT 0, carrier_name text, tracking_no text, assigned_to_name text, received_by_name text, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.receiving_task_items ( id bigserial PRIMARY KEY, tenant_id uuid, receiving_task_id bigint, item_id bigint, item_code text, sku text, item_name text, description text, unit_of_measure text, ordered_quantity numeric(18,4) DEFAULT 0, expected_quantity numeric(18,4) DEFAULT 0, received_quantity numeric(18,4) DEFAULT 0, accepted_quantity numeric(18,4) DEFAULT 0, rejected_quantity numeric(18,4) DEFAULT 0, variance_quantity numeric(18,4) DEFAULT 0, bin_location_id bigint, bin_code text, unit_cost numeric(18,4) DEFAULT 0, line_value numeric(18,4) DEFAULT 0, condition_status text, variance_status text, notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.putaway_tasks ( id bigserial PRIMARY KEY, tenant_id uuid, task_no text, receiving_task_id bigint, receiving_no text, grn_no text, warehouse_id bigint, warehouse_code text, warehouse_name text, assigned_to_name text, priority text, status text DEFAULT 'pending', item_count integer DEFAULT 0, total_quantity numeric(18,4) DEFAULT 0, started_at timestamp without time zone, completed_at timestamp without time zone, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.putaway_task_items ( id bigserial PRIMARY KEY, tenant_id uuid, putaway_task_id bigint, receiving_task_item_id bigint, item_id bigint, item_code text, sku text, item_name text, unit_of_measure text, received_quantity numeric(18,4) DEFAULT 0, putaway_quantity numeric(18,4) DEFAULT 0, bin_location_id bigint, bin_code text, status text DEFAULT 'pending', notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.pick_lists ( id bigserial PRIMARY KEY, tenant_id uuid, pick_list_no text, source_doc_type text, source_doc_id bigint, source_doc_no text, sales_order_id bigint, sales_order_no text, customer_name text, warehouse_id bigint, warehouse_code text, warehouse_name text, picking_status text DEFAULT 'pending', packing_status text, dispatch_status text, status text DEFAULT 'pending', item_count integer DEFAULT 0, total_quantity numeric(18,4) DEFAULT 0, assigned_picker_name text, priority text, due_at timestamp without time zone, started_at timestamp without time zone, completed_at timestamp without time zone, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.pick_list_items ( id bigserial PRIMARY KEY, tenant_id uuid, pick_list_id bigint, item_id bigint, item_code text, sku text, item_name text, description text, unit_of_measure text, ordered_quantity numeric(18,4) DEFAULT 0, reserved_quantity numeric(18,4) DEFAULT 0, picked_quantity numeric(18,4) DEFAULT 0, bin_location_id bigint, bin_code text, barcode text, picked boolean DEFAULT false, status text DEFAULT 'pending', picked_at timestamp without time zone, picked_by_name text, notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.dispatches ( id bigserial PRIMARY KEY, tenant_id uuid, dispatch_no text, source_doc_type text, source_doc_id bigint, source_doc_no text, sales_order_id bigint, sales_order_no text, customer_name text, warehouse_id bigint, warehouse_code text, warehouse_name text, package_reference text, package_dimensions text, total_weight_kg numeric(18,4) DEFAULT 0, carrier_name text, tracking_no text, dispatch_date date, expected_delivery_date date, delivered_at timestamp without time zone, picking_status text, packing_status text, dispatch_status text, status text DEFAULT 'pending', dispatched_by_name text, delivery_address text, proof_of_delivery text, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.dispatch_items ( id bigserial PRIMARY KEY, tenant_id uuid, dispatch_id bigint, pick_list_item_id bigint, item_id bigint, item_code text, sku text, item_name text, description text, unit_of_measure text, ordered_quantity numeric(18,4) DEFAULT 0, picked_quantity numeric(18,4) DEFAULT 0, packed_quantity numeric(18,4) DEFAULT 0, dispatched_quantity numeric(18,4) DEFAULT 0, bin_location_id bigint, bin_code text, package_reference text, status text DEFAULT 'pending', notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.transfer_orders ( id bigserial PRIMARY KEY, tenant_id uuid, transfer_no text, order_no text, source_warehouse_id bigint, source_warehouse_code text, source_warehouse_name text, destination_warehouse_id bigint, destination_warehouse_code text, destination_warehouse_name text, transfer_date date, dispatch_date date, expected_receipt_date date, received_at timestamp without time zone, status text DEFAULT 'pending', approval_status text, initiated_by_name text, submitted_by_name text, approved_by_name text, dispatched_by_name text, received_by_name text, item_count integer DEFAULT 0, total_quantity numeric(18,4) DEFAULT 0, total_value numeric(18,4) DEFAULT 0, variance_count integer DEFAULT 0, priority text, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.transfer_order_items ( id bigserial PRIMARY KEY, tenant_id uuid, transfer_order_id bigint, item_id bigint, item_code text, sku text, item_name text, description text, unit_of_measure text, transfer_quantity numeric(18,4) DEFAULT 0, dispatched_quantity numeric(18,4) DEFAULT 0, expected_quantity numeric(18,4) DEFAULT 0, received_quantity numeric(18,4) DEFAULT 0, verified_quantity numeric(18,4) DEFAULT 0, variance_quantity numeric(18,4) DEFAULT 0, source_bin_location_id bigint, source_bin_code text, destination_bin_location_id bigint, destination_bin_code text, unit_cost numeric(18,4) DEFAULT 0, line_value numeric(18,4) DEFAULT 0, status text DEFAULT 'pending', note text, notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.bin_movements ( id bigserial PRIMARY KEY, tenant_id uuid, movement_no text, movement_type text, entity_type text, entity_id bigint, entity_no text, item_id bigint, item_code text, sku text, item_name text, warehouse_id bigint, warehouse_name text, from_bin_location_id bigint, from_bin_code text, to_bin_location_id bigint, to_bin_code text, quantity numeric(18,4) DEFAULT 0, occurred_at timestamp without time zone, occurred_by_name text, notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS warehouse.warehouse_events ( id bigserial PRIMARY KEY, tenant_id uuid, event_no text, entity_type text, entity_id bigint, entity_no text, event_type text, from_status text, to_status text, event_title text, event_details text, occurred_at timestamp without time zone, occurred_by_name text, notes text, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS warehouse.stock_reconciliation_cases ( id bigserial PRIMARY KEY, tenant_id uuid, reconciliation_no text, stock_count_session text, warehouse_id bigint, warehouse_name text, bin_location_id bigint, bin_code text, item_id bigint, item_code text, sku text, item_name text, description text, system_quantity numeric(18,4) DEFAULT 0, physical_quantity numeric(18,4) DEFAULT 0, variance_quantity numeric(18,4) DEFAULT 0, unit_cost numeric(18,4) DEFAULT 0, variance_value numeric(18,4) DEFAULT 0, status text DEFAULT 'unresolved', adjustment_status text, ai_explanation jsonb DEFAULT '[]'::jsonb, submitted_at timestamp without time zone, approved_at timestamp without time zone, approved_by_name text, resolved_at timestamp without time zone, resolved_by_name text, notes text, is_deleted boolean DEFAULT false, metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.warehouse_ai_insights ( id bigserial PRIMARY KEY, tenant_id uuid, insight_no text, insight_type text, module_area text, severity text, entity_type text, entity_id bigint, entity_no text, title text, summary text, evidence jsonb DEFAULT '[]'::jsonb, recommendation text, confidence_percent numeric(6,2), confidence_label text, status text DEFAULT 'active', metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, updated_at timestamp without time zone ); CREATE TABLE IF NOT EXISTS warehouse.warehouse_kpi_snapshots ( id bigserial PRIMARY KEY, tenant_id uuid, snapshot_date date, warehouse_id bigint, warehouse_name text, receiving_time_hours numeric(18,4), picking_time_minutes numeric(18,4), fulfillment_time_hours numeric(18,4), bin_utilization_percent numeric(8,4), error_rate_percent numeric(8,4), transfer_delay_rate_percent numeric(8,4), inbound_volume numeric(18,4), outbound_volume numeric(18,4), metadata jsonb DEFAULT '{}'::jsonb, created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP ); ALTER TABLE IF EXISTS warehouse.warehouses ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS name text, ADD COLUMN IF NOT EXISTS address_line text, ADD COLUMN IF NOT EXISTS location text, ADD COLUMN IF NOT EXISTS city text, ADD COLUMN IF NOT EXISTS country text, ADD COLUMN IF NOT EXISTS warehouse_type text, ADD COLUMN IF NOT EXISTS type text, ADD COLUMN IF NOT EXISTS total_capacity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS used_capacity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS capacity_uom text, ADD COLUMN IF NOT EXISTS active_item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS manager_name text, ADD COLUMN IF NOT EXISTS cost_center_code text, ADD COLUMN IF NOT EXISTS receiving_policy text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'active', ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT true, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.bin_locations ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS location_code text, ADD COLUMN IF NOT EXISTS code text, ADD COLUMN IF NOT EXISTS location_name text, ADD COLUMN IF NOT EXISTS name text, ADD COLUMN IF NOT EXISTS location_type text, ADD COLUMN IF NOT EXISTS type text, ADD COLUMN IF NOT EXISTS parent_location_id bigint, ADD COLUMN IF NOT EXISTS parent_code text, ADD COLUMN IF NOT EXISTS zone_code text, ADD COLUMN IF NOT EXISTS rack_code text, ADD COLUMN IF NOT EXISTS shelf_code text, ADD COLUMN IF NOT EXISTS bin_code text, ADD COLUMN IF NOT EXISTS capacity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS occupied_capacity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS active_item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS utilization_percent numeric(8,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'active', ADD COLUMN IF NOT EXISTS is_active boolean DEFAULT true, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.receiving_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS task_no text, ADD COLUMN IF NOT EXISTS receiving_no text, ADD COLUMN IF NOT EXISTS grn_no text, ADD COLUMN IF NOT EXISTS purchase_order_id bigint, ADD COLUMN IF NOT EXISTS purchase_order_no text, ADD COLUMN IF NOT EXISTS supplier_id bigint, ADD COLUMN IF NOT EXISTS supplier_name text, ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS expected_date date, ADD COLUMN IF NOT EXISTS received_at timestamp without time zone, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS inspection_status text, ADD COLUMN IF NOT EXISTS putaway_status text, ADD COLUMN IF NOT EXISTS discrepancy_status text, ADD COLUMN IF NOT EXISTS expected_item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS received_item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS shortlanded_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS overage_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS total_received_amount numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS carrier_name text, ADD COLUMN IF NOT EXISTS tracking_no text, ADD COLUMN IF NOT EXISTS assigned_to_name text, ADD COLUMN IF NOT EXISTS received_by_name text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.receiving_task_items ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS receiving_task_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_code text, ADD COLUMN IF NOT EXISTS sku text, ADD COLUMN IF NOT EXISTS item_name text, ADD COLUMN IF NOT EXISTS description text, ADD COLUMN IF NOT EXISTS unit_of_measure text, ADD COLUMN IF NOT EXISTS ordered_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS expected_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS received_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS accepted_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS rejected_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS variance_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS bin_location_id bigint, ADD COLUMN IF NOT EXISTS bin_code text, ADD COLUMN IF NOT EXISTS unit_cost numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS line_value numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS condition_status text, ADD COLUMN IF NOT EXISTS variance_status text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.putaway_tasks ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS task_no text, ADD COLUMN IF NOT EXISTS receiving_task_id bigint, ADD COLUMN IF NOT EXISTS receiving_no text, ADD COLUMN IF NOT EXISTS grn_no text, ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS assigned_to_name text, ADD COLUMN IF NOT EXISTS priority text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS total_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS started_at timestamp without time zone, ADD COLUMN IF NOT EXISTS completed_at timestamp without time zone, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.putaway_task_items ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS putaway_task_id bigint, ADD COLUMN IF NOT EXISTS receiving_task_item_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_code text, ADD COLUMN IF NOT EXISTS sku text, ADD COLUMN IF NOT EXISTS item_name text, ADD COLUMN IF NOT EXISTS unit_of_measure text, ADD COLUMN IF NOT EXISTS received_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS putaway_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS bin_location_id bigint, ADD COLUMN IF NOT EXISTS bin_code text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.pick_lists ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS pick_list_no text, ADD COLUMN IF NOT EXISTS source_doc_type text, ADD COLUMN IF NOT EXISTS source_doc_id bigint, ADD COLUMN IF NOT EXISTS source_doc_no text, ADD COLUMN IF NOT EXISTS sales_order_id bigint, ADD COLUMN IF NOT EXISTS sales_order_no text, ADD COLUMN IF NOT EXISTS customer_name text, ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS picking_status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS packing_status text, ADD COLUMN IF NOT EXISTS dispatch_status text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS total_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS assigned_picker_name text, ADD COLUMN IF NOT EXISTS priority text, ADD COLUMN IF NOT EXISTS due_at timestamp without time zone, ADD COLUMN IF NOT EXISTS started_at timestamp without time zone, ADD COLUMN IF NOT EXISTS completed_at timestamp without time zone, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.pick_list_items ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS pick_list_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_code text, ADD COLUMN IF NOT EXISTS sku text, ADD COLUMN IF NOT EXISTS item_name text, ADD COLUMN IF NOT EXISTS description text, ADD COLUMN IF NOT EXISTS unit_of_measure text, ADD COLUMN IF NOT EXISTS ordered_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS reserved_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS picked_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS bin_location_id bigint, ADD COLUMN IF NOT EXISTS bin_code text, ADD COLUMN IF NOT EXISTS barcode text, ADD COLUMN IF NOT EXISTS picked boolean DEFAULT false, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS picked_at timestamp without time zone, ADD COLUMN IF NOT EXISTS picked_by_name text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.dispatches ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS dispatch_no text, ADD COLUMN IF NOT EXISTS source_doc_type text, ADD COLUMN IF NOT EXISTS source_doc_id bigint, ADD COLUMN IF NOT EXISTS source_doc_no text, ADD COLUMN IF NOT EXISTS sales_order_id bigint, ADD COLUMN IF NOT EXISTS sales_order_no text, ADD COLUMN IF NOT EXISTS customer_name text, ADD COLUMN IF NOT EXISTS warehouse_id bigint, ADD COLUMN IF NOT EXISTS warehouse_code text, ADD COLUMN IF NOT EXISTS warehouse_name text, ADD COLUMN IF NOT EXISTS package_reference text, ADD COLUMN IF NOT EXISTS package_dimensions text, ADD COLUMN IF NOT EXISTS total_weight_kg numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS carrier_name text, ADD COLUMN IF NOT EXISTS tracking_no text, ADD COLUMN IF NOT EXISTS dispatch_date date, ADD COLUMN IF NOT EXISTS expected_delivery_date date, ADD COLUMN IF NOT EXISTS delivered_at timestamp without time zone, ADD COLUMN IF NOT EXISTS picking_status text, ADD COLUMN IF NOT EXISTS packing_status text, ADD COLUMN IF NOT EXISTS dispatch_status text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS dispatched_by_name text, ADD COLUMN IF NOT EXISTS delivery_address text, ADD COLUMN IF NOT EXISTS proof_of_delivery text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.dispatch_items ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS dispatch_id bigint, ADD COLUMN IF NOT EXISTS pick_list_item_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_code text, ADD COLUMN IF NOT EXISTS sku text, ADD COLUMN IF NOT EXISTS item_name text, ADD COLUMN IF NOT EXISTS description text, ADD COLUMN IF NOT EXISTS unit_of_measure text, ADD COLUMN IF NOT EXISTS ordered_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS picked_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS packed_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS dispatched_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS bin_location_id bigint, ADD COLUMN IF NOT EXISTS bin_code text, ADD COLUMN IF NOT EXISTS package_reference text, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.transfer_orders ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS transfer_no text, ADD COLUMN IF NOT EXISTS order_no text, ADD COLUMN IF NOT EXISTS source_warehouse_id bigint, ADD COLUMN IF NOT EXISTS source_warehouse_code text, ADD COLUMN IF NOT EXISTS source_warehouse_name text, ADD COLUMN IF NOT EXISTS destination_warehouse_id bigint, ADD COLUMN IF NOT EXISTS destination_warehouse_code text, ADD COLUMN IF NOT EXISTS destination_warehouse_name text, ADD COLUMN IF NOT EXISTS transfer_date date, ADD COLUMN IF NOT EXISTS dispatch_date date, ADD COLUMN IF NOT EXISTS expected_receipt_date date, ADD COLUMN IF NOT EXISTS received_at timestamp without time zone, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS approval_status text, ADD COLUMN IF NOT EXISTS initiated_by_name text, ADD COLUMN IF NOT EXISTS submitted_by_name text, ADD COLUMN IF NOT EXISTS approved_by_name text, ADD COLUMN IF NOT EXISTS dispatched_by_name text, ADD COLUMN IF NOT EXISTS received_by_name text, ADD COLUMN IF NOT EXISTS item_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS total_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS total_value numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS variance_count integer DEFAULT 0, ADD COLUMN IF NOT EXISTS priority text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS is_deleted boolean DEFAULT false, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; ALTER TABLE IF EXISTS warehouse.transfer_order_items ADD COLUMN IF NOT EXISTS tenant_id uuid, ADD COLUMN IF NOT EXISTS transfer_order_id bigint, ADD COLUMN IF NOT EXISTS item_id bigint, ADD COLUMN IF NOT EXISTS item_code text, ADD COLUMN IF NOT EXISTS sku text, ADD COLUMN IF NOT EXISTS item_name text, ADD COLUMN IF NOT EXISTS description text, ADD COLUMN IF NOT EXISTS unit_of_measure text, ADD COLUMN IF NOT EXISTS transfer_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS dispatched_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS expected_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS received_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS verified_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS variance_quantity numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS source_bin_location_id bigint, ADD COLUMN IF NOT EXISTS source_bin_code text, ADD COLUMN IF NOT EXISTS destination_bin_location_id bigint, ADD COLUMN IF NOT EXISTS destination_bin_code text, ADD COLUMN IF NOT EXISTS unit_cost numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS line_value numeric(18,4) DEFAULT 0, ADD COLUMN IF NOT EXISTS status text DEFAULT 'pending', ADD COLUMN IF NOT EXISTS note text, ADD COLUMN IF NOT EXISTS notes text, ADD COLUMN IF NOT EXISTS metadata jsonb DEFAULT '{}'::jsonb, ADD COLUMN IF NOT EXISTS created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP, ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone; CREATE UNIQUE INDEX IF NOT EXISTS warehouses_tenant_code_uidx ON warehouse.warehouses (tenant_id, warehouse_code) WHERE warehouse_code IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS warehouses_tenant_status_idx ON warehouse.warehouses (tenant_id, status); CREATE UNIQUE INDEX IF NOT EXISTS bin_locations_tenant_wh_code_uidx ON warehouse.bin_locations (tenant_id, warehouse_id, location_code) WHERE location_code IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS bin_locations_parent_idx ON warehouse.bin_locations (tenant_id, warehouse_id, parent_location_id); CREATE UNIQUE INDEX IF NOT EXISTS receiving_tasks_tenant_task_uidx ON warehouse.receiving_tasks (tenant_id, task_no) WHERE task_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS receiving_tasks_po_idx ON warehouse.receiving_tasks (tenant_id, purchase_order_no); CREATE INDEX IF NOT EXISTS receiving_task_items_task_idx ON warehouse.receiving_task_items (tenant_id, receiving_task_id); CREATE UNIQUE INDEX IF NOT EXISTS putaway_tasks_tenant_task_uidx ON warehouse.putaway_tasks (tenant_id, task_no) WHERE task_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS putaway_task_items_task_idx ON warehouse.putaway_task_items (tenant_id, putaway_task_id); CREATE UNIQUE INDEX IF NOT EXISTS pick_lists_tenant_no_uidx ON warehouse.pick_lists (tenant_id, pick_list_no) WHERE pick_list_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS pick_lists_sales_order_idx ON warehouse.pick_lists (tenant_id, sales_order_no); CREATE INDEX IF NOT EXISTS pick_list_items_pick_list_idx ON warehouse.pick_list_items (tenant_id, pick_list_id); CREATE UNIQUE INDEX IF NOT EXISTS dispatches_tenant_no_uidx ON warehouse.dispatches (tenant_id, dispatch_no) WHERE dispatch_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS dispatches_sales_order_idx ON warehouse.dispatches (tenant_id, sales_order_no); CREATE INDEX IF NOT EXISTS dispatch_items_dispatch_idx ON warehouse.dispatch_items (tenant_id, dispatch_id); CREATE UNIQUE INDEX IF NOT EXISTS transfer_orders_tenant_no_uidx ON warehouse.transfer_orders (tenant_id, transfer_no) WHERE transfer_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS transfer_orders_status_idx ON warehouse.transfer_orders (tenant_id, status, expected_receipt_date); CREATE INDEX IF NOT EXISTS transfer_order_items_order_idx ON warehouse.transfer_order_items (tenant_id, transfer_order_id); CREATE UNIQUE INDEX IF NOT EXISTS bin_movements_tenant_no_uidx ON warehouse.bin_movements (tenant_id, movement_no) WHERE movement_no IS NOT NULL; CREATE INDEX IF NOT EXISTS bin_movements_entity_idx ON warehouse.bin_movements (tenant_id, entity_type, entity_id); CREATE UNIQUE INDEX IF NOT EXISTS warehouse_events_tenant_no_uidx ON warehouse.warehouse_events (tenant_id, event_no) WHERE event_no IS NOT NULL; CREATE INDEX IF NOT EXISTS warehouse_events_entity_idx ON warehouse.warehouse_events (tenant_id, entity_type, entity_id, occurred_at); CREATE UNIQUE INDEX IF NOT EXISTS stock_reconciliation_tenant_no_uidx ON warehouse.stock_reconciliation_cases (tenant_id, reconciliation_no) WHERE reconciliation_no IS NOT NULL AND COALESCE(is_deleted, false) = false; CREATE INDEX IF NOT EXISTS stock_reconciliation_status_idx ON warehouse.stock_reconciliation_cases (tenant_id, status, warehouse_id); CREATE UNIQUE INDEX IF NOT EXISTS warehouse_ai_insights_tenant_no_uidx ON warehouse.warehouse_ai_insights (tenant_id, insight_no) WHERE insight_no IS NOT NULL; CREATE INDEX IF NOT EXISTS warehouse_ai_insights_status_idx ON warehouse.warehouse_ai_insights (tenant_id, status, severity); CREATE INDEX IF NOT EXISTS warehouse_kpi_snapshots_date_idx ON warehouse.warehouse_kpi_snapshots (tenant_id, snapshot_date, warehouse_id); COMMIT;