-- V3__create_pivot_tables.sql
-- Pivot Tables for indexing, uniqueness, relationships
-- Based on Force.com whitepaper architecture

-- ============================================
-- Indexes Pivot Table
-- "Synchronously copies field data marked for indexing to appropriate column"
-- Contains strongly typed indexed columns: StringValue, NumValue, DateValue
-- Standard NON-UNIQUE database indexes
-- ============================================
CREATE TABLE mt_indexes (
    idx_id BIGSERIAL,
    org_id INTEGER NOT NULL,
    obj_id INTEGER NOT NULL,
    field_num INTEGER NOT NULL,           -- Maps to FieldNum in mt_fields
    guid UUID NOT NULL,                   -- Reference to mt_data.guid

    -- Strongly typed columns for indexing
    -- StringValue uses case-folded format (universal, case-insensitive)
    string_value VARCHAR(255),            -- For text, email, url, phone, picklist
    num_value NUMERIC(18,6),              -- For number, currency, percent
    date_value TIMESTAMPTZ,               -- For date, datetime

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

CREATE TABLE mt_indexes_default PARTITION OF mt_indexes DEFAULT;

-- Non-unique indexes for each datatype (as per whitepaper)
CREATE INDEX idx_mt_indexes_string ON mt_indexes(org_id, obj_id, field_num, string_value)
    WHERE string_value IS NOT NULL;
CREATE INDEX idx_mt_indexes_num ON mt_indexes(org_id, obj_id, field_num, num_value)
    WHERE num_value IS NOT NULL;
CREATE INDEX idx_mt_indexes_date ON mt_indexes(org_id, obj_id, field_num, date_value)
    WHERE date_value IS NOT NULL;
CREATE INDEX idx_mt_indexes_guid ON mt_indexes(org_id, guid);

-- ============================================
-- UniqueFields Pivot Table
-- "Similar to Indexes except underlying indexes enforce uniqueness"
-- ============================================
CREATE TABLE mt_unique_fields (
    idx_id BIGSERIAL,
    org_id INTEGER NOT NULL,
    obj_id INTEGER NOT NULL,
    field_num INTEGER NOT NULL,
    guid UUID NOT NULL,

    -- Typed columns (same as Indexes)
    string_value VARCHAR(255),
    num_value NUMERIC(18,6),
    date_value TIMESTAMPTZ,

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

CREATE TABLE mt_unique_fields_default PARTITION OF mt_unique_fields DEFAULT;

-- UNIQUE indexes enforce field uniqueness per org+object+field
CREATE UNIQUE INDEX idx_mt_unique_string ON mt_unique_fields(org_id, obj_id, field_num, string_value)
    WHERE string_value IS NOT NULL;
CREATE UNIQUE INDEX idx_mt_unique_num ON mt_unique_fields(org_id, obj_id, field_num, num_value)
    WHERE num_value IS NOT NULL;
CREATE UNIQUE INDEX idx_mt_unique_date ON mt_unique_fields(org_id, obj_id, field_num, date_value)
    WHERE date_value IS NOT NULL;

-- ============================================
-- Relationships Pivot Table
-- "Optimizes join operations for relationship fields"
-- Two composite unique indexes for traversal in either direction
-- ============================================
CREATE TABLE mt_relationships (
    rel_id BIGSERIAL,
    org_id INTEGER NOT NULL,

    -- Relationship metadata
    relationship_id INTEGER NOT NULL,      -- Field ID of the relationship field

    -- Parent object (referenced)
    parent_obj_id INTEGER NOT NULL,
    parent_guid UUID NOT NULL,

    -- Child object (referencing)
    child_obj_id INTEGER NOT NULL,
    child_guid UUID NOT NULL,

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

CREATE TABLE mt_relationships_default PARTITION OF mt_relationships DEFAULT;

-- Two unique composite indexes for efficient traversal (as per whitepaper)
-- Index 1: OrgID + GUID (find all relationships for a record)
CREATE UNIQUE INDEX idx_mt_rel_child_guid ON mt_relationships(org_id, child_guid, relationship_id);
-- Index 2: OrgID + ObjID + RelationID + TargetObjID (traverse parent to children)
CREATE INDEX idx_mt_rel_parent ON mt_relationships(org_id, parent_obj_id, parent_guid);
CREATE INDEX idx_mt_rel_child ON mt_relationships(org_id, child_obj_id, child_guid);

-- ============================================
-- FallbackIndex Table
-- "Records the Name of all objects for global object searches"
-- "Updates happen synchronously as transactions modify objects"
-- Used when external search engine is unavailable
-- ============================================
CREATE TABLE mt_fallback_index (
    org_id INTEGER NOT NULL,
    obj_id INTEGER NOT NULL,
    guid UUID NOT NULL,
    name VARCHAR(255),                     -- Natural name of the object instance
    name_lower VARCHAR(255),               -- Lowercase for case-insensitive search
    modified_at TIMESTAMPTZ DEFAULT NOW(),

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

CREATE TABLE mt_fallback_index_default PARTITION OF mt_fallback_index DEFAULT;

-- Index for global name searches across objects
CREATE INDEX idx_mt_fallback_search ON mt_fallback_index(org_id, name_lower varchar_pattern_ops);
CREATE INDEX idx_mt_fallback_obj ON mt_fallback_index(org_id, obj_id, name_lower varchar_pattern_ops);

-- ============================================
-- NameDenorm Table
-- "Lean data table that stores ObjID and Name of each object instance"
-- "Used for hyperlinks to object instances in parent-child relationships"
-- ============================================
CREATE TABLE mt_name_denorm (
    org_id INTEGER NOT NULL,
    obj_id INTEGER NOT NULL,
    guid UUID NOT NULL,
    name VARCHAR(255),                     -- Display name for hyperlinks

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

CREATE TABLE mt_name_denorm_default PARTITION OF mt_name_denorm DEFAULT;

CREATE INDEX idx_mt_name_denorm_obj ON mt_name_denorm(org_id, obj_id);

-- ============================================
-- History Tracking Table
-- "Asynchronously records information about changes made to field"
-- Stores: old value, new value, change date, who changed
-- ============================================
CREATE TABLE mt_field_history (
    history_id BIGSERIAL,
    org_id INTEGER NOT NULL,
    obj_id INTEGER NOT NULL,
    guid UUID NOT NULL,                    -- Record that was changed
    field_num INTEGER NOT NULL,            -- Field that was changed

    -- Change details
    old_value TEXT,
    new_value TEXT,
    data_type VARCHAR(50),                 -- Type of the field for proper display

    -- Audit
    changed_at TIMESTAMPTZ DEFAULT NOW(),
    changed_by UUID,

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

CREATE TABLE mt_field_history_default PARTITION OF mt_field_history DEFAULT;

CREATE INDEX idx_mt_history_record ON mt_field_history(org_id, guid, changed_at DESC);
CREATE INDEX idx_mt_history_field ON mt_field_history(org_id, obj_id, field_num, changed_at DESC);