test_conversations/
test_conversations.rs

1//! Test QAIL against real get_conversations query
2
3use qail_core::parse;
4use qail_core::transpiler::ToSql;
5
6fn main() {
7    println!("=== Testing QAIL with get_conversations Pattern ===\n");
8
9    // CTE 1: latest_messages
10    let cte1 = r#"get distinct on (phone_number) whatsapp_messages 
11        fields phone_number, content as last_message, created_at as last_message_time
12        order by phone_number, created_at desc"#;
13
14    println!("CTE 1 (latest_messages):");
15    match parse(cte1) {
16        Ok(cmd) => {
17            let sql = cmd.to_sql();
18            println!("  QAIL: {}", cte1.replace('\n', " "));
19            println!("  SQL:  {}\n", sql);
20        }
21        Err(e) => println!("  ERR: {}\n", e),
22    }
23
24    // CTE 2: customer_names
25    let cte2 = r#"get distinct on (phone_number) whatsapp_messages 
26        fields phone_number, sender_name as customer_sender_name
27        where direction = 'inbound' and sender_name is not null
28        order by phone_number, created_at desc"#;
29
30    println!("CTE 2 (customer_names):");
31    match parse(cte2) {
32        Ok(cmd) => {
33            let sql = cmd.to_sql();
34            println!("  QAIL: {}", cte2.replace('\n', " "));
35            println!("  SQL:  {}\n", sql);
36        }
37        Err(e) => println!("  ERR: {}\n", e),
38    }
39
40    // CTE 3: unread_counts
41    let cte3 = r#"get whatsapp_messages 
42        fields phone_number, count(*) as unread_count
43        where direction = 'inbound' and status = 'received'"#;
44
45    println!("CTE 3 (unread_counts):");
46    match parse(cte3) {
47        Ok(cmd) => {
48            let sql = cmd.to_sql();
49            println!("  SQL:  {}\n", sql);
50        }
51        Err(e) => println!("  ERR: {}\n", e),
52    }
53
54    // CTE 4: order_counts (JSON access)
55    let cte4 = r#"get orders 
56        fields contact_info->>'phone' as phone_number, count(*) as order_count
57        where contact_info->>'phone' is not null"#;
58
59    println!("CTE 4 (order_counts with JSON):");
60    match parse(cte4) {
61        Ok(cmd) => {
62            let sql = cmd.to_sql();
63            println!("  SQL:  {}\n", sql);
64        }
65        Err(e) => println!("  ERR: {}\n", e),
66    }
67
68    // CTE 5: order_names (Complex DISTINCT ON with CASE WHEN)
69    let cte5 = r#"get distinct on (case when contact_info->>'phone' like '0%' then '62' || substring(contact_info->>'phone' from 2) else replace(contact_info->>'phone', '+', '') end) orders
70        fields 
71            case when contact_info->>'phone' like '0%' then '62' || substring(contact_info->>'phone' from 2) else replace(contact_info->>'phone', '+', '') end as normalized_phone,
72            contact_info->>'name' as order_customer_name,
73            user_id
74        where contact_info->>'phone' is not null
75        order by case when contact_info->>'phone' like '0%' then '62' || substring(contact_info->>'phone' from 2) else replace(contact_info->>'phone', '+', '') end, created_at desc"#;
76
77    println!("CTE 5 (order_names - COMPLEX):");
78    match parse(cte5) {
79        Ok(cmd) => {
80            let sql = cmd.to_sql();
81            println!("  SQL:  {}\n", sql);
82        }
83        Err(e) => println!("  ERR: {}\n", e),
84    }
85
86    // CTE 6: active_sessions
87    let cte6 = r#"get distinct on (phone_number) whatsapp_sessions 
88        fields phone_number, id as session_id, status as session_status
89        order by phone_number, created_at desc"#;
90
91    println!("CTE 6 (active_sessions):");
92    match parse(cte6) {
93        Ok(cmd) => {
94            let sql = cmd.to_sql();
95            println!("  SQL:  {}\n", sql);
96        }
97        Err(e) => println!("  ERR: {}\n", e),
98    }
99
100    // Final query with multiple JOINs
101    let final_q = r#"get latest_messages 
102        left join customer_names on customer_names.phone_number = latest_messages.phone_number
103        left join unread_counts on unread_counts.phone_number = latest_messages.phone_number
104        left join order_counts on order_counts.phone_number = latest_messages.phone_number
105        left join order_names on order_names.normalized_phone = latest_messages.phone_number
106        left join users on users.id = order_names.user_id
107        left join active_sessions on active_sessions.phone_number = latest_messages.phone_number
108        left join whatsapp_contacts on whatsapp_contacts.phone_number = latest_messages.phone_number
109        fields 
110            latest_messages.phone_number,
111            coalesce(whatsapp_contacts.custom_name, whatsapp_contacts.meta_profile_name, customer_names.customer_sender_name, order_names.order_customer_name, users.first_name || ' ' || users.last_name) as customer_name,
112            coalesce(latest_messages.last_message, '') as last_message,
113            latest_messages.last_message_time,
114            coalesce(unread_counts.unread_count, 0) as unread_count,
115            coalesce(order_counts.order_count, 0) as order_count,
116            active_sessions.session_id,
117            active_sessions.session_status
118        order by latest_messages.last_message_time desc"#;
119
120    println!("FINAL (multiple LEFT JOINs):");
121    match parse(final_q) {
122        Ok(cmd) => {
123            println!("  Joins: {}", cmd.joins.len());
124            let sql = cmd.to_sql();
125            println!("  SQL:  {}\n", sql);
126        }
127        Err(e) => println!("  ERR: {}\n", e),
128    }
129}
test_conversations/test_conversations.rs

test_conversations/
test_conversations.rs
