// vtest retry: 3
// vtest build: present_sqlite3? && !windows && !sanitize-memory-clang
import db.sqlite

// Department table for testing JOINs - using dept_id to avoid column name conflicts
struct Department {
    dept_id   int @[primary; sql: serial]
    dept_name string
}

// User table with a foreign key reference to Department
struct User {
    user_id       int @[primary; sql: serial]
    user_name     string
    department_id int
}

fn test_inner_join() {
    db := sqlite.connect(':memory:') or { panic(err) }

    // Create tables
    sql db {
        create table Department
        create table User
    }!

    // Insert departments
    engineering := Department{
        dept_name: 'Engineering'
    }
    sales := Department{
        dept_name: 'Sales'
    }
    sql db {
        insert engineering into Department
        insert sales into Department
    }!

    // Insert users
    alice := User{
        user_name:     'Alice'
        department_id: 1
    }
    bob := User{
        user_name:     'Bob'
        department_id: 2
    }
    charlie := User{
        user_name:     'Charlie'
        department_id: 1
    }
    sql db {
        insert alice into User
        insert bob into User
        insert charlie into User
    }!

    // Test basic INNER JOIN
    users := sql db {
        select from User
        join Department on User.department_id == Department.dept_id
    }!

    assert users.len == 3
}

fn test_inner_join_with_where() {
    db := sqlite.connect(':memory:') or { panic(err) }

    // Create tables
    sql db {
        create table Department
        create table User
    }!

    // Insert departments
    engineering := Department{
        dept_name: 'Engineering'
    }
    sales := Department{
        dept_name: 'Sales'
    }
    sql db {
        insert engineering into Department
        insert sales into Department
    }!

    // Insert users
    alice := User{
        user_name:     'Alice'
        department_id: 1
    }
    bob := User{
        user_name:     'Bob'
        department_id: 2
    }
    charlie := User{
        user_name:     'Charlie'
        department_id: 1
    }
    sql db {
        insert alice into User
        insert bob into User
        insert charlie into User
    }!

    // Test INNER JOIN with WHERE clause - use simple field name (not Table.field)
    engineering_users := sql db {
        select from User
        join Department on User.department_id == Department.dept_id where department_id == 1
    }!

    assert engineering_users.len == 2
    assert engineering_users[0].user_name == 'Alice' || engineering_users[0].user_name == 'Charlie'
}

fn test_left_join() {
    db := sqlite.connect(':memory:') or { panic(err) }

    // Create tables
    sql db {
        create table Department
        create table User
    }!

    // Insert departments
    engineering := Department{
        dept_name: 'Engineering'
    }
    sql db {
        insert engineering into Department
    }!

    // Insert users - one with a department, one without (orphan)
    alice := User{
        user_name:     'Alice'
        department_id: 1
    }
    bob := User{
        user_name:     'Bob'
        department_id: 999 // No matching department
    }
    sql db {
        insert alice into User
        insert bob into User
    }!

    // Test LEFT JOIN - should return all users, even those without matching department
    users := sql db {
        select from User
        left join Department on User.department_id == Department.dept_id
    }!

    // Both users should be returned since it's a LEFT JOIN
    assert users.len == 2
}