BEGIN;

CREATE SCHEMA IF NOT EXISTS inventory;

CREATE TABLE IF NOT EXISTS inventory.stock_reservations (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    reservation_no varchar(100),
    item_id bigint,
    item_code varchar(100),
    item_name varchar(255),
    warehouse_id bigint,
    warehouse_name varchar(255),
    source_module varchar(80) NOT NULL,
    source_type varchar(100) NOT NULL,
    source_id bigint NOT NULL,
    source_no varchar(100),
    source_line_id bigint,
    quantity numeric(18,4) NOT NULL DEFAULT 0,
    consumed_quantity numeric(18,4) NOT NULL DEFAULT 0,
    released_quantity numeric(18,4) NOT NULL DEFAULT 0,
    status varchar(40) NOT NULL DEFAULT 'reserved',
    reserved_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    consumed_at timestamp without time zone,
    released_at timestamp without time zone,
    reserved_by uuid,
    reserved_by_name varchar(255),
    notes text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE IF EXISTS inventory.stock_reservations
    ADD COLUMN IF NOT EXISTS reservation_no varchar(100),
    ADD COLUMN IF NOT EXISTS item_id bigint,
    ADD COLUMN IF NOT EXISTS item_code varchar(100),
    ADD COLUMN IF NOT EXISTS item_name varchar(255),
    ADD COLUMN IF NOT EXISTS warehouse_id bigint,
    ADD COLUMN IF NOT EXISTS warehouse_name varchar(255),
    ADD COLUMN IF NOT EXISTS source_module varchar(80),
    ADD COLUMN IF NOT EXISTS source_type varchar(100),
    ADD COLUMN IF NOT EXISTS source_id bigint,
    ADD COLUMN IF NOT EXISTS source_no varchar(100),
    ADD COLUMN IF NOT EXISTS source_line_id bigint,
    ADD COLUMN IF NOT EXISTS quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS consumed_quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS released_quantity numeric(18,4) NOT NULL DEFAULT 0,
    ADD COLUMN IF NOT EXISTS status varchar(40) NOT NULL DEFAULT 'reserved',
    ADD COLUMN IF NOT EXISTS reserved_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS consumed_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS released_at timestamp without time zone,
    ADD COLUMN IF NOT EXISTS reserved_by uuid,
    ADD COLUMN IF NOT EXISTS reserved_by_name varchar(255),
    ADD COLUMN IF NOT EXISTS notes text,
    ADD COLUMN IF NOT EXISTS metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    ADD COLUMN IF NOT EXISTS created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN IF NOT EXISTS updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP;

CREATE UNIQUE INDEX IF NOT EXISTS idx_inventory_stock_reservations_source_line
    ON inventory.stock_reservations (tenant_id, source_module, source_type, source_id, source_line_id)
    WHERE status IN ('reserved', 'partial');

CREATE INDEX IF NOT EXISTS idx_inventory_stock_reservations_balance
    ON inventory.stock_reservations (tenant_id, item_id, item_code, warehouse_id, status);

CREATE INDEX IF NOT EXISTS idx_inventory_stock_movements_reference
    ON inventory.stock_movements (tenant_id, reference_type, reference_id);

COMMIT;