CREATE TABLE tickets AS
SELECT * FROM (VALUES
    (1, 'Broken mug', 'The mug arrived in pieces. Please refund my order.'),
    (2, 'Double charge', 'You charged my card twice. Please reverse the duplicate payment.'),
    (3, 'Login loop', 'Since the update, signing in sends me back to the login screen.'),
    (4, 'Thank you', 'Everything arrived on time. The mug looks great!'),
    (5, 'Where is it?', 'Tracking says delivered, but there is no package at my door.'),
    (6, 'Invoice address', 'Can you change the company address on my invoice?'),
    (7, 'Broken checkout', 'Checkout crashed, but my card was charged. Did my order go through?'),
    (8, 'Changed my mind', 'The order is fine, but I no longer need it. Can I return it for a refund?')
) AS t(id, subject, message);
CREATE TABLE triage AS
FROM tickets DECIDE (
    CHOOSE 'Which team should handle this customer message?' AMONG (
        billing 'Billing: charges, refunds, invoices',
        shipping 'Shipping: delivery, lost or damaged packages',
        tech 'Tech support: bugs, crashes, login problems'
    ) AS team,
    ASK 'Is the customer asking for a refund or an exchange?' AS refund
    GIVEN subject, message
);
SELECT id, subject, team, round(team_confidence, 3) AS confidence,
       refund, round(refund_probabilities['yes'], 3) AS p_refund
FROM triage ORDER BY id;

CREATE OR REPLACE TABLE triage AS
FROM tickets DECIDE (
    CHOOSE 'Which team should handle this customer message?' AMONG (
        billing 'Payment or invoice issue',
        shipping 'Delivery, damaged item, or product return',
        tech 'Software bug or account access problem',
        praise 'Compliment or thank-you note'
    ) AS team,
    ASK 'Is the customer asking for a refund or an exchange?' AS refund
    GIVEN subject, message
);

SELECT id, subject, team, round(team_confidence, 3) AS confidence
FROM triage
ORDER BY id;

CREATE VIEW routing AS
WITH scored AS (
    SELECT id, subject, team AS proposed_team, team_confidence,
           refund, refund_probabilities['yes'] AS p_refund
    FROM triage
)
SELECT *,
       CASE WHEN team_confidence < 0.6 THEN 'unknown'
            ELSE proposed_team
       END AS route
FROM scored;

SELECT id, subject, proposed_team, round(team_confidence, 3) AS confidence, route
FROM routing ORDER BY id;

SELECT id, subject, proposed_team, team_confidence, route, p_refund
FROM routing
WHERE route = 'unknown'
   OR p_refund BETWEEN 0.2 AND 0.8
ORDER BY team_confidence, id;

SELECT route, count(*) AS tickets,
       count(*) FILTER (WHERE refund) AS flagged_for_refund
FROM routing
GROUP BY route
ORDER BY tickets DESC, route;

CREATE TABLE checks AS SELECT * FROM (VALUES
 (1, 'All fixed', 'That solved it. Thank you for the quick help!', 'praise'),
 (2, 'Lovely mug', 'I love the color. Everything is perfect.', 'praise'),
 (3, 'Thanks, but still missing', 'Thanks for checking. My package still has not arrived. Can you find it?', 'shipping'),
 (4, 'Refund please', 'Thanks for your help. Please refund the duplicate charge on my card.', 'billing'),
 (5, 'Great app, one problem', 'I love the app, but it crashes when I upload a photo. Can you fix it?', 'tech'),
 (6, 'VAT invoice', 'Please add our VAT number to the invoice.', 'billing'),
 (7, 'Wrong delivery', 'You sent the wrong size. How do I get the correct package?', 'shipping'),
 (8, 'Password reset', 'The password reset link gives an error and I cannot access my account.', 'tech')
) t(id, subject, message, expected);
SELECT id, expected, team, round(team_confidence,3) AS confidence
FROM checks DECIDE (
 CHOOSE 'Which team should handle this customer message?' AMONG (
 billing 'Payment or invoice issue',
 shipping 'Delivery, damaged item, or product return',
 tech 'Software bug or account access problem',
 praise 'Compliment or thank-you note'
 ) AS team GIVEN subject, message
) ORDER BY id;

CREATE TABLE validation AS SELECT * FROM (VALUES
(1, 'Unexpected renewal', 'My subscription renewed after I cancelled. Please reverse that charge.', 'billing'),
(2, 'Receipt needed', 'Please send me a receipt for the payment I made yesterday.', 'billing'),
(3, 'Wrong invoice amount', 'The invoice lists 12 seats, but we only pay for 8. Can you correct it?', 'billing'),
(4, 'Polite payment question', 'Thank you for the excellent service. Why was my card billed twice?', 'billing'),
(5, 'Missing parcel', 'The courier never delivered my order. Where is the parcel?', 'shipping'),
(6, 'Damaged plate', 'The plate is cracked. Please send a replacement.', 'shipping'),
(7, 'Wrong item', 'I ordered a blue shirt and received a red one. How can I exchange it?', 'shipping'),
(8, 'Return request', 'These shoes do not fit. How do I send them back?', 'shipping'),
(9, 'Upload error', 'Every file upload fails with an error. Can someone investigate?', 'tech'),
(10, 'Locked out', 'My account is locked and I cannot sign in.', 'tech'),
(11, 'Checkout button', 'Clicking Pay does nothing. I cannot complete checkout.', 'tech'),
(12, 'Friendly bug report', 'Thanks for building a great app. It freezes whenever I open settings.', 'tech'),
(13, 'Excellent support', 'Your support team was wonderful. I really appreciate the help.', 'praise'),
(14, 'Perfect delivery', 'My order arrived early and everything looks fantastic!', 'praise'),
(15, 'Problem solved', 'The login works now. Thanks for fixing it so quickly.', 'praise'),
(16, 'Happy customer', 'Just wanted to say that I love the product. Keep up the good work!', 'praise')
) t(id, subject, message, expected);
SELECT id, expected, team, round(team_confidence,3) AS confidence,
       CASE WHEN team_confidence < 0.6 THEN 'unknown' ELSE team END AS route
FROM validation DECIDE (
 CHOOSE 'Which team should handle this customer message?' AMONG (
 billing 'Payment or invoice issue',
 shipping 'Delivery, damaged item, or product return',
 tech 'Software bug or account access problem',
 praise 'Compliment or thank-you note'
 ) AS team GIVEN subject, message
) ORDER BY id;