-- V1__create_metadata_tables.sql
-- Таблицы метаданных в соответствии с Force.com Multitenancy Architecture

-- ============================================
-- Таблица организаций (Tenants)
-- ============================================
CREATE TABLE organizations (
    org_id SERIAL PRIMARY KEY,
    org_name VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    modified_at TIMESTAMPTZ DEFAULT NOW()
);

-- Тестовые организации
INSERT INTO organizations (org_id, org_name, display_name) VALUES
    (1, 'org_alpha', 'Alpha Corporation'),
    (2, 'org_beta', 'Beta Industries'),
    (3, 'org_gamma', 'Gamma Solutions');

-- ============================================
-- Objects Metadata Table (UDD)
-- Stores metadata about custom objects (tables/entities)
-- ============================================
CREATE TABLE mt_objects (
    obj_id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL REFERENCES organizations(org_id),
    obj_name VARCHAR(255) NOT NULL,        -- API name of the object
    label VARCHAR(255),                     -- Display label
    plural_label VARCHAR(255),              -- Plural form for UI
    description TEXT,
    is_custom BOOLEAN DEFAULT TRUE,         -- Custom vs Standard object
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    modified_at TIMESTAMPTZ DEFAULT NOW(),
    modified_by UUID,
    UNIQUE (org_id, obj_name)
);

CREATE INDEX idx_mt_objects_org ON mt_objects(org_id);

-- ============================================
-- Fields Metadata Table (UDD)
-- Stores metadata about custom fields (columns/attributes)
-- Maps fields to flex column slots in Data table
-- ============================================
CREATE TABLE mt_fields (
    field_id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL REFERENCES organizations(org_id),
    obj_id INTEGER NOT NULL REFERENCES mt_objects(obj_id) ON DELETE CASCADE,
    field_name VARCHAR(255) NOT NULL,       -- API name
    label VARCHAR(255),                      -- Display label
    -- Supported datatypes: text, number, date, datetime, boolean,
    -- picklist, autonumber, formula, master-detail, lookup, checkbox, email, url, phone
    data_type VARCHAR(50) NOT NULL,
    field_num INTEGER NOT NULL,              -- Position/slot in Data table (0-500)

    -- Field properties
    is_indexed BOOLEAN DEFAULT FALSE,        -- Should be copied to Indexes pivot table
    is_unique BOOLEAN DEFAULT FALSE,         -- Should be copied to UniqueFields pivot table
    is_required BOOLEAN DEFAULT FALSE,       -- NOT NULL constraint
    is_external_id BOOLEAN DEFAULT FALSE,    -- Can be used for upsert operations

    -- For relationship fields (lookup, master-detail)
    relationship_name VARCHAR(255),          -- Name of the relationship
    reference_obj_id INTEGER REFERENCES mt_objects(obj_id),  -- Target object
    relationship_type VARCHAR(20),           -- 'lookup' or 'master-detail'
    delete_constraint VARCHAR(20),           -- 'set_null', 'restrict', 'cascade'

    -- For picklist fields
    picklist_values TEXT[],                  -- Array of allowed values

    -- For formula fields
    formula_expression TEXT,                 -- Formula definition
    formula_return_type VARCHAR(50),         -- Return type of formula

    -- For autonumber fields
    autonumber_format VARCHAR(100),          -- e.g., 'INV-{0000}'
    autonumber_start INTEGER DEFAULT 1,

    -- Validation
    validation_rule TEXT,                    -- Custom validation expression
    validation_message VARCHAR(500),         -- Error message

    -- Field length/precision
    length INTEGER,                          -- Max length for text fields
    precision INTEGER,                       -- Total digits for number
    scale INTEGER,                           -- Decimal places for number

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW(),
    created_by UUID,
    modified_at TIMESTAMPTZ DEFAULT NOW(),
    modified_by UUID,

    UNIQUE (org_id, obj_id, field_name),
    UNIQUE (org_id, obj_id, field_num)       -- One field per slot per object
);

CREATE INDEX idx_mt_fields_org_obj ON mt_fields(org_id, obj_id);
CREATE INDEX idx_mt_fields_indexed ON mt_fields(org_id, obj_id) WHERE is_indexed = TRUE;
CREATE INDEX idx_mt_fields_unique ON mt_fields(org_id, obj_id) WHERE is_unique = TRUE;