-- V2__create_data_table.sql
-- Data Table (Heap Storage) + Clobs Table

-- ============================================
-- MT_DATA - Main data storage with flex columns
-- ============================================
CREATE TABLE mt_data (
    org_id          INTEGER NOT NULL,
    guid            UUID NOT NULL,
    obj_id          INTEGER NOT NULL,
    name            VARCHAR(255),

    -- Flex columns (50 slots)
    value0 VARCHAR(4000), value1 VARCHAR(4000), value2 VARCHAR(4000), value3 VARCHAR(4000), value4 VARCHAR(4000),
    value5 VARCHAR(4000), value6 VARCHAR(4000), value7 VARCHAR(4000), value8 VARCHAR(4000), value9 VARCHAR(4000),
    value10 VARCHAR(4000), value11 VARCHAR(4000), value12 VARCHAR(4000), value13 VARCHAR(4000), value14 VARCHAR(4000),
    value15 VARCHAR(4000), value16 VARCHAR(4000), value17 VARCHAR(4000), value18 VARCHAR(4000), value19 VARCHAR(4000),
    value20 VARCHAR(4000), value21 VARCHAR(4000), value22 VARCHAR(4000), value23 VARCHAR(4000), value24 VARCHAR(4000),
    value25 VARCHAR(4000), value26 VARCHAR(4000), value27 VARCHAR(4000), value28 VARCHAR(4000), value29 VARCHAR(4000),
    value30 VARCHAR(4000), value31 VARCHAR(4000), value32 VARCHAR(4000), value33 VARCHAR(4000), value34 VARCHAR(4000),
    value35 VARCHAR(4000), value36 VARCHAR(4000), value37 VARCHAR(4000), value38 VARCHAR(4000), value39 VARCHAR(4000),
    value40 VARCHAR(4000), value41 VARCHAR(4000), value42 VARCHAR(4000), value43 VARCHAR(4000), value44 VARCHAR(4000),
    value45 VARCHAR(4000), value46 VARCHAR(4000), value47 VARCHAR(4000), value48 VARCHAR(4000), value49 VARCHAR(4000),

    -- Soft delete
    is_deleted      BOOLEAN DEFAULT FALSE,
    deleted_at      TIMESTAMPTZ,
    deleted_by      UUID,

    -- Audit
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by      UUID,
    modified_at     TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    modified_by     UUID,

    PRIMARY KEY (org_id, guid)
) PARTITION BY LIST (org_id);

-- Index for object queries
CREATE INDEX idx_mt_data_obj ON mt_data (org_id, obj_id);
CREATE INDEX idx_mt_data_name ON mt_data (org_id, obj_id, name);
CREATE INDEX idx_mt_data_deleted ON mt_data (org_id, obj_id) WHERE is_deleted = FALSE;

-- ============================================
-- MT_CLOBS - Large text fields storage
-- PK must include partition key (org_id)
-- ============================================
CREATE TABLE mt_clobs (
    org_id          INTEGER NOT NULL,
    clob_id         UUID NOT NULL DEFAULT gen_random_uuid(),
    data_guid       UUID NOT NULL,
    field_num       INTEGER NOT NULL,
    clob_value      TEXT,
    created_at      TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    modified_at     TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (org_id, clob_id)
) PARTITION BY LIST (org_id);

-- Index for lookups
CREATE INDEX idx_mt_clobs_data ON mt_clobs (org_id, data_guid, field_num);

-- ============================================
-- Default partitions
-- ============================================
CREATE TABLE mt_data_default PARTITION OF mt_data DEFAULT;
CREATE TABLE mt_clobs_default PARTITION OF mt_clobs DEFAULT;