-- V4__create_partitions_and_functions.sql
-- Partitions for test organizations and utility functions

-- ============================================
-- Create partitions for test organizations (1, 2, 3)
-- "All data, metadata, and pivot table structures are physically
-- partitioned by OrgID using native database partitioning"
-- ============================================

-- Partitions for mt_data
CREATE TABLE mt_data_org1 PARTITION OF mt_data FOR VALUES IN (1);
CREATE TABLE mt_data_org2 PARTITION OF mt_data FOR VALUES IN (2);
CREATE TABLE mt_data_org3 PARTITION OF mt_data FOR VALUES IN (3);

-- Partitions for mt_clobs
CREATE TABLE mt_clobs_org1 PARTITION OF mt_clobs FOR VALUES IN (1);
CREATE TABLE mt_clobs_org2 PARTITION OF mt_clobs FOR VALUES IN (2);
CREATE TABLE mt_clobs_org3 PARTITION OF mt_clobs FOR VALUES IN (3);

-- Partitions for mt_indexes
CREATE TABLE mt_indexes_org1 PARTITION OF mt_indexes FOR VALUES IN (1);
CREATE TABLE mt_indexes_org2 PARTITION OF mt_indexes FOR VALUES IN (2);
CREATE TABLE mt_indexes_org3 PARTITION OF mt_indexes FOR VALUES IN (3);

-- Partitions for mt_unique_fields
CREATE TABLE mt_unique_fields_org1 PARTITION OF mt_unique_fields FOR VALUES IN (1);
CREATE TABLE mt_unique_fields_org2 PARTITION OF mt_unique_fields FOR VALUES IN (2);
CREATE TABLE mt_unique_fields_org3 PARTITION OF mt_unique_fields FOR VALUES IN (3);

-- Partitions for mt_relationships
CREATE TABLE mt_relationships_org1 PARTITION OF mt_relationships FOR VALUES IN (1);
CREATE TABLE mt_relationships_org2 PARTITION OF mt_relationships FOR VALUES IN (2);
CREATE TABLE mt_relationships_org3 PARTITION OF mt_relationships FOR VALUES IN (3);

-- Partitions for mt_fallback_index
CREATE TABLE mt_fallback_index_org1 PARTITION OF mt_fallback_index FOR VALUES IN (1);
CREATE TABLE mt_fallback_index_org2 PARTITION OF mt_fallback_index FOR VALUES IN (2);
CREATE TABLE mt_fallback_index_org3 PARTITION OF mt_fallback_index FOR VALUES IN (3);

-- Partitions for mt_name_denorm
CREATE TABLE mt_name_denorm_org1 PARTITION OF mt_name_denorm FOR VALUES IN (1);
CREATE TABLE mt_name_denorm_org2 PARTITION OF mt_name_denorm FOR VALUES IN (2);
CREATE TABLE mt_name_denorm_org3 PARTITION OF mt_name_denorm FOR VALUES IN (3);

-- Partitions for mt_field_history
CREATE TABLE mt_field_history_org1 PARTITION OF mt_field_history FOR VALUES IN (1);
CREATE TABLE mt_field_history_org2 PARTITION OF mt_field_history FOR VALUES IN (2);
CREATE TABLE mt_field_history_org3 PARTITION OF mt_field_history FOR VALUES IN (3);

-- ============================================
-- Function: Create all partitions for a new organization
-- ============================================
CREATE OR REPLACE FUNCTION create_org_partitions(p_org_id INTEGER)
RETURNS VOID AS $$
BEGIN
    -- mt_data
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_data_org%s PARTITION OF mt_data FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_clobs
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_clobs_org%s PARTITION OF mt_clobs FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_indexes
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_indexes_org%s PARTITION OF mt_indexes FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_unique_fields
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_unique_fields_org%s PARTITION OF mt_unique_fields FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_relationships
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_relationships_org%s PARTITION OF mt_relationships FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_fallback_index
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_fallback_index_org%s PARTITION OF mt_fallback_index FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_name_denorm
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_name_denorm_org%s PARTITION OF mt_name_denorm FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
    -- mt_field_history
    EXECUTE format('CREATE TABLE IF NOT EXISTS mt_field_history_org%s PARTITION OF mt_field_history FOR VALUES IN (%s)',
                   p_org_id, p_org_id);
END;
$$ LANGUAGE plpgsql;

-- ============================================
-- Function: Case-folding for StringValue (case-insensitive search)
-- "Application servers use a case-folding algorithm that converts
-- string values to a universal, case-insensitive format"
-- ============================================
CREATE OR REPLACE FUNCTION case_fold(p_value VARCHAR)
RETURNS VARCHAR AS $$
BEGIN
    RETURN LOWER(TRIM(p_value));
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- ============================================
-- Function: Convert flex column value to appropriate type
-- "Uses underlying database datatype-conversion functions
-- (TO_NUMBER, TO_DATE, TO_CHAR) as necessary"
-- ============================================
CREATE OR REPLACE FUNCTION to_num(p_value VARCHAR)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_value::NUMERIC;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION to_dt(p_value VARCHAR)
RETURNS TIMESTAMPTZ AS $$
BEGIN
    RETURN p_value::TIMESTAMPTZ;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION to_bool(p_value VARCHAR)
RETURNS BOOLEAN AS $$
BEGIN
    RETURN CASE
        WHEN LOWER(p_value) IN ('true', '1', 'yes', 'y', 't') THEN TRUE
        WHEN LOWER(p_value) IN ('false', '0', 'no', 'n', 'f') THEN FALSE
        ELSE NULL
    END;
END;
$$ LANGUAGE plpgsql IMMUTABLE;