whyrating-engine-legacy/migrations/versions/013_insert_primitives.sql

-- Migration: Insert frozen primitive dictionary (36 primitives)
-- Description: Populates the primitives table with the complete URT taxonomy
-- Date: 2025-01-31

-- Quality dimension (8 primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'EFFECTIVENESS',
    'QUALITY',
    'Effectiveness',
    'Did it achieve its intended purpose?',
    FALSE,
    '["worked perfectly", "exactly what I needed", "solved my problem"]',
    '["didn''t work", "useless", "waste of time"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'TASTE',
    'QUALITY',
    'Taste',
    'Sensory quality (flavor, texture, smell)',
    FALSE,
    '["delicious", "amazing taste", "flavorful"]',
    '["bland", "tasteless", "disgusting"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'CRAFT',
    'QUALITY',
    'Craft',
    'Skill of execution, workmanship',
    FALSE,
    '["well-made", "professional", "quality work"]',
    '["sloppy", "poorly made", "amateur"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'ACCURACY',
    'QUALITY',
    'Accuracy',
    'Correct as ordered/specified',
    FALSE,
    '["exactly what I ordered", "perfect", "got everything right"]',
    '["wrong order", "missing items", "not what I asked for"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'FRESHNESS',
    'QUALITY',
    'Freshness',
    'Fresh vs stale/expired',
    FALSE,
    '["fresh", "just made", "new"]',
    '["stale", "old", "expired"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'TEMPERATURE',
    'QUALITY',
    'Temperature',
    'Appropriate temperature for the item',
    FALSE,
    '["hot", "perfect temperature", "cold as it should be"]',
    '["cold", "lukewarm", "too hot", "room temperature"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'CONDITION',
    'QUALITY',
    'Condition',
    'Physical state, damage, defects',
    FALSE,
    '["perfect condition", "like new", "undamaged"]',
    '["damaged", "broken", "defective"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'CONSISTENCY',
    'QUALITY',
    'Consistency',
    'Same quality across visits/units',
    FALSE,
    '["always consistent", "reliable quality", "same every time"]',
    '["inconsistent", "hit or miss", "varies"]'
);

-- Service dimension (4 primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'MANNER',
    'SERVICE',
    'Manner',
    'Warmth, respect, patience in interactions',
    FALSE,
    '["friendly", "nice", "welcoming", "patient"]',
    '["rude", "dismissive", "impatient", "attitude"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'COMPETENCE',
    'SERVICE',
    'Competence',
    'Knowledge and skill of staff',
    FALSE,
    '["knowledgeable", "professional", "knew what they were doing"]',
    '["clueless", "incompetent", "didn''t know"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'ATTENTIVENESS',
    'SERVICE',
    'Attentiveness',
    'Present, notices needs, proactive',
    FALSE,
    '["attentive", "checked on us", "anticipated needs"]',
    '["ignored", "had to flag down", "neglected"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'COMMUNICATION',
    'SERVICE',
    'Communication',
    'Clear, listens, keeps informed',
    FALSE,
    '["clear", "good communication", "kept us updated"]',
    '["confusing", "didn''t listen", "no updates"]'
);

-- Process dimension (4 primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'SPEED',
    'PROCESS',
    'Speed',
    'How fast/slow things happen',
    FALSE,
    '["fast", "quick", "no wait"]',
    '["slow", "took forever", "long wait"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'FRICTION',
    'PROCESS',
    'Friction',
    'Ease vs obstacles in the process',
    FALSE,
    '["easy", "smooth", "hassle-free"]',
    '["complicated", "difficult", "hassle"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RELIABILITY',
    'PROCESS',
    'Reliability',
    'Process works consistently, no errors',
    FALSE,
    '["reliable", "dependable", "always works"]',
    '["unreliable", "errors", "problems"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'AVAILABILITY',
    'PROCESS',
    'Availability',
    'Hours, capacity, stock availability',
    FALSE,
    '["always available", "open when needed", "in stock"]',
    '["closed", "sold out", "no appointments"]'
);

-- Environment dimension (6 primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'CLEANLINESS',
    'ENVIRONMENT',
    'Cleanliness',
    'Clean, sanitary conditions',
    FALSE,
    '["clean", "spotless", "hygienic"]',
    '["dirty", "filthy", "unsanitary"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'COMFORT',
    'ENVIRONMENT',
    'Comfort',
    'Physical comfort of the space',
    FALSE,
    '["comfortable", "cozy", "spacious"]',
    '["uncomfortable", "cramped", "hard seats"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'AMBIANCE',
    'ENVIRONMENT',
    'Ambiance',
    'Vibe, atmosphere, noise level',
    FALSE,
    '["nice atmosphere", "great vibe", "quiet"]',
    '["loud", "noisy", "bad atmosphere"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'SAFETY',
    'ENVIRONMENT',
    'Safety',
    'Physical and health safety',
    FALSE,
    '["safe", "secure", "clean protocols"]',
    '["unsafe", "dangerous", "health hazard"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'ACCESSIBILITY',
    'ENVIRONMENT',
    'Accessibility',
    'Disability access, location convenience',
    FALSE,
    '["accessible", "easy to get to", "good parking"]',
    '["hard to access", "no parking", "not wheelchair accessible"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'DIGITAL_UX',
    'ENVIRONMENT',
    'Digital UX',
    'App/website usability and performance',
    FALSE,
    '["easy to use", "great app", "fast website"]',
    '["app crashed", "hard to navigate", "slow website"]'
);

-- Value dimension (4 primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'PRICE_LEVEL',
    'VALUE',
    'Price Level',
    'Absolute cost perception',
    FALSE,
    '["affordable", "cheap", "good prices"]',
    '["expensive", "overpriced", "pricey"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'PRICE_FAIRNESS',
    'VALUE',
    'Price Fairness',
    'Fair value for what was received',
    FALSE,
    '["fair price", "worth it", "good value"]',
    '["rip off", "not worth it", "overcharged"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'PRICE_TRANSPARENCY',
    'VALUE',
    'Price Transparency',
    'Clear pricing, no surprises',
    FALSE,
    '["clear pricing", "no hidden fees", "upfront"]',
    '["hidden fees", "surprise charges", "bait and switch"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'VALUE_FOR_MONEY',
    'VALUE',
    'Value for Money',
    'Overall worth judgment',
    FALSE,
    '["great value", "worth every penny", "good deal"]',
    '["bad value", "waste of money", "not worth it"]'
);

-- Trust dimension (3 meta primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'HONESTY',
    'TRUST',
    'Honesty',
    'Truthful, no deception',
    TRUE,
    '["honest", "transparent", "truthful"]',
    '["lied", "deceived", "dishonest", "scam"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'ETHICS',
    'TRUST',
    'Ethics',
    'Ethical, fair dealing',
    TRUE,
    '["ethical", "fair", "integrity"]',
    '["unethical", "shady", "crooked"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'PROMISES',
    'TRUST',
    'Promises',
    'Kept or broken commitments',
    TRUE,
    '["kept their word", "delivered as promised", "reliable"]',
    '["broke promise", "didn''t deliver", "false advertising"]'
);

-- Resolution dimension (3 meta primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'ACKNOWLEDGMENT',
    'RESOLUTION',
    'Acknowledgment',
    'Recognized the problem',
    TRUE,
    '["acknowledged", "apologized", "admitted mistake"]',
    '["denied", "dismissed", "blamed me"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RESPONSE_QUALITY',
    'RESOLUTION',
    'Response Quality',
    'How well they handled the issue',
    TRUE,
    '["handled well", "resolved quickly", "took care of it"]',
    '["ignored complaint", "unhelpful", "made it worse"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RECOVERY',
    'RESOLUTION',
    'Recovery',
    'Made it right, compensation',
    TRUE,
    '["made it right", "refunded", "compensated"]',
    '["refused refund", "no compensation", "wouldn''t fix"]'
);

-- Loyalty dimension (3 meta primitives)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RETURN_INTENT',
    'LOYALTY',
    'Return Intent',
    'Will/won''t come back',
    TRUE,
    '["will be back", "returning customer", "coming again"]',
    '["never again", "won''t return", "last time"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RECOMMEND',
    'LOYALTY',
    'Recommend',
    'Would/wouldn''t recommend',
    TRUE,
    '["highly recommend", "tell everyone", "must try"]',
    '["avoid", "don''t go", "stay away", "wouldn''t recommend"]'
);

INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'RECOGNITION',
    'LOYALTY',
    'Recognition',
    'Felt valued, remembered',
    TRUE,
    '["remembered me", "felt valued", "personal touch"]',
    '["treated like a number", "didn''t care", "no loyalty"]'
);

-- Escape dimension (1 meta primitive)
INSERT INTO pipeline.primitives (code, dimension, name, definition, is_meta, base_positive_signals, base_negative_signals)
VALUES (
    'UNMAPPED',
    'ESCAPE',
    'Unmapped',
    'Does not fit taxonomy; preserve evidence',
    TRUE,
    '[]',
    '[]'
);

-- Verify count
-- SELECT COUNT(*) FROM pipeline.primitives; -- Should return 36