BEGIN;

CREATE TABLE IF NOT EXISTS platform.tenant_document_numbering (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    document_code character varying(50) NOT NULL,
    document_label character varying(100) NOT NULL,
    prefix character varying(20),
    next_sequence_number bigint NOT NULL DEFAULT 1,
    number_padding integer NOT NULL DEFAULT 5,
    sort_order integer NOT NULL DEFAULT 1,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_tenant_document_numbering_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_tenant_document_numbering_code
        UNIQUE (tenant_id, document_code)
);

CREATE INDEX IF NOT EXISTS idx_tenant_document_numbering_tenant
    ON platform.tenant_document_numbering (tenant_id)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS platform.tenant_approval_rules (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    rule_code character varying(50) NOT NULL,
    rule_label character varying(100) NOT NULL,
    threshold_value numeric(18, 2) NULL,
    threshold_unit character varying(30) NULL,
    approver_role_id uuid NULL,
    approver_role_name character varying(120) NULL,
    is_enabled boolean NOT NULL DEFAULT TRUE,
    sort_order integer NOT NULL DEFAULT 1,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_tenant_approval_rules_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_tenant_approval_rules_code
        UNIQUE (tenant_id, rule_code)
);

CREATE INDEX IF NOT EXISTS idx_tenant_approval_rules_tenant
    ON platform.tenant_approval_rules (tenant_id)
    WHERE deleted_at IS NULL;

CREATE TABLE IF NOT EXISTS platform.tenant_inventory_defaults (
    id bigserial PRIMARY KEY,
    tenant_id uuid NOT NULL,
    default_reorder_method character varying(50),
    auto_create_po_on_reorder boolean NOT NULL DEFAULT FALSE,
    default_costing_method character varying(50),
    require_bin_location boolean NOT NULL DEFAULT FALSE,
    low_stock_alert_threshold_percent numeric(5, 2) NULL,
    is_active boolean NOT NULL DEFAULT TRUE,
    created_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at timestamp without time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_by uuid NULL,
    updated_by uuid NULL,
    deleted_at timestamp without time zone NULL,
    deleted_by uuid NULL,
    CONSTRAINT fk_tenant_inventory_defaults_tenant
        FOREIGN KEY (tenant_id) REFERENCES platform.tenants(id) ON DELETE CASCADE,
    CONSTRAINT uq_tenant_inventory_defaults_tenant
        UNIQUE (tenant_id)
);

CREATE INDEX IF NOT EXISTS idx_tenant_inventory_defaults_tenant
    ON platform.tenant_inventory_defaults (tenant_id)
    WHERE deleted_at IS NULL;

COMMIT;