tmp.sql

-- ==========================================
-- 1. ATTRIBUTE PARSERS MASTER
-- ==========================================
CREATE TABLE attribute_parsers (
    parser_id VARCHAR(50) PRIMARY KEY,           -- 'severity-v1', 'duration-v1'
    parser_name VARCHAR(100) NOT NULL,          -- 'severity', 'duration'
    importance INTEGER DEFAULT 1,                -- Execution priority order
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ==========================================
-- 2. GLOBAL/INITIAL PARSER VARIABLES
-- ==========================================
CREATE TABLE parser_initial_vars (
    var_id SERIAL PRIMARY KEY,
    parser_id VARCHAR(50) REFERENCES attribute_parsers(parser_id) ON DELETE CASCADE,
    var_key VARCHAR(50) NOT NULL,
    
    -- Store as text, enforce type with an enum or string indicator
    var_value TEXT NOT NULL, 
    var_type VARCHAR(20) NOT NULL, -- 'int', 'float', 'string', 'boolean', 'array'
    
    UNIQUE(parser_id, var_key)
);

-- ==========================================
-- 3. MATCHING STRATEGIES (The Regex Rules)
-- ==========================================
CREATE TABLE parser_strategies (
    strategy_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    parser_id VARCHAR(50) REFERENCES attribute_parsers(parser_id) ON DELETE CASCADE,
    strategy_name VARCHAR(100) NOT NULL,         -- 'full_text_fraction', 'numeric_fraction'
	hint_template TEXT;							 -- 'X out of {scale}'
	hint_capture TEXT;							 -- '^(\d+)$' (extracts raw numbers typed directly into the hint area)
	fill_template TEXT;							 -- ' out of {scale}'
    regex_pattern TEXT NOT NULL,                 -- '(?P<value>\d+)/(?P<max>\d+)'
    output_variable VARCHAR(50) NOT NULL,        -- The final variable to return ('score')
	unambiguous INTEGER DEFAULT 0,               -- Can be extracted before the main term 
    execution_order INTEGER NOT NULL,            -- Execution priority within this parser
    UNIQUE(parser_id, execution_order)
);

-- ==========================================
-- 4. PIPELINE OPERATIONS (The Functional Logic)
-- ==========================================
CREATE TABLE strategy_operations (
    operation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    strategy_id UUID REFERENCES parser_strategies(strategy_id) ON DELETE CASCADE,
    op_code VARCHAR(50) NOT NULL,                 -- 'divide', 'multiply', 'assert_leq'
    input_variables TEXT[] NOT NULL,             -- Array of inputs: ['frac', 'scale']
    output_variable VARCHAR(50) NOT NULL,        -- Target destination: 'score'
    step_sequence INTEGER NOT NULL,              -- Ordering of processing steps
    UNIQUE(strategy_id, step_sequence)
);

-- ==========================================
-- 5. TAG-TO-PARSER ROUTING MATRIX
-- ==========================================
CREATE TABLE tag_parser_routing (
    routing_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tag_id VARCHAR(50) REFERENCES expression_tags(tag_id) ON DELETE CASCADE,
    parser_id VARCHAR(50) REFERENCES attribute_parsers(parser_id) ON DELETE CASCADE,
    is_required BOOLEAN DEFAULT FALSE,  -- True = Linter flags if this parser fails to extract data
    UNIQUE(tag_id, parser_id)
);