2023-07-12.org

#+begin_ai :noweb yes :temperature 0.2
[SYS]: You are an expert programmer specialist in PHP. You give concise answers for expert users.
You use php 8.2 with psalm annotations, phpunit tests and PSR12.
You use readonly dataclasses and type annotations.

[ME]: 
[ME]: <<sqlbuilder>>

Create a DSL (YAML based) to represent the conversion from arguments to SQL query using the query builder. We want to refactor the builder to b more elegant. Add many `description` fields that have a concise documentation of the DSL elements. The DSL should follow the structure of the $options array. Output the DSL that represents building the PurchaseOrder query.



[AI]: Here is an example of a DSL (Domain Specific Language) in YAML format that represents the conversion from arguments to SQL query using the query builder. This DSL follows the structure of the `$options` array and includes `description` fields that provide concise documentation of the DSL elements.

```yaml
description: DSL for building the PurchaseOrder query
select:
  description: Fields to be selected in the SQL query
  fields:
    - name: s.name
      alias: name
    - name: po.id
      alias: id
    - name: po.order_status
      alias: order_status
    - name: po.status
      alias: status
    - name: po.num_items
      alias: num_items
    - name: po.invoice_total
      alias: invoice_total
    - name: po.invoice_number
      alias: invoice_number
    - name: po.paid_at
      alias: paid_at
from:
  description: Base table for the SQL query
  table: purchase_orders
  alias: po
join:
  description: Join conditions for the SQL query
  conditions:
    - type: LEFT JOIN
      table: purchase_suppliers
      alias: s
      on: s.id = po.supplier_id
where:
  description: Where conditions for the SQL query
  conditions:
    - s.deleted_at IS NULL
    - po.deleted_at IS NULL
    - po.id IS NOT NULL
groupBy:
  description: Group by field for the SQL query
  field: id
orderBy:
  description: Order by field for the SQL query
  field: id
  direction: DESC
binds:
  description: Bind parameters for the SQL query
  parameters: []
calcRows:
  description: Whether to calculate rows for the SQL query
  value: true
offset:
  description: Offset for the SQL query
  value: 0
limit:
  description: Limit for the SQL query
  value: 10
```

This DSL provides a more elegant and readable way to build SQL queries. It separates the different parts of the query into distinct sections, making it easier to understand and modify. The `description` fields provide additional context and documentation for each section.

[ME]: Now write the PHP code that converts the DSL representation into the $options array.

[AI]: Here's a simple PHP function that converts the DSL representation into the `$options` array:

```php
/**
 * Converts a DSL representation into the $options array.
 *
 * @param array $dsl The DSL representation.
 * @return array The $options array.
 */
public static function convertDslToOptions(array $dsl): array
{
    $options = [];

    foreach ($dsl as $key => $value) {
        switch ($key) {
            case 'select':
                $options['select'] = array_map(fn($field) => $field['name'] . ' ' . $field['alias'], $value['fields']);
                break;
            case 'from':
                $options['from'] = $value['table'] . ' ' . $value['alias'];
                break;
            case 'join':
                $options['join'] = array_map(fn($condition) => $condition['type'] . ' ' . $condition['table'] . ' ' . $condition['alias'] . ' ON ' . $condition['on'], $value['conditions']);
                break;
            case 'where':
                $options['where'] = $value['conditions'];
                break;
            case 'groupBy':
                $options['groupBy'] = $value['field'];
                break;
            case 'orderBy':
                $options['orderBy'] = $value['field'] . ' ' . $value['direction'];
                break;
            case 'binds':
                $options['binds'] = $value['parameters'];
                break;
            case 'calcRows':
                $options['calcRows'] = $value['value'];
                break;
            case 'offset':
                $options['offset'] = $value['value'];
                break;
            case 'limit':
                $options['limit'] = $value['value'];
                break;
        }
    }

    return $options;
}
```

This function iterates over the DSL array and constructs the `$options` array based on the keys and values in the DSL. It uses `array_map` to transform the 'select' and 'join' sections into the correct format for the `$options` array. The 'where', 'groupBy', 'orderBy', 'binds', 'calcRows', 'offset', and 'limit' sections are directly assigned to the corresponding keys in the `$options` array.

[ME]: Now replace the Find() method with a method called FindToDSL() that returns a DSL instance, and then runs it using the convertDslToOptions() and Sql::select.

[AI]: Here's how you could refactor the `Find()` method to `FindToDSL()` that returns a DSL instance, and then runs it using the `convertDslToOptions()` and `Sql::select`:

```php
public static function FindToDSL(array $args): array
{
    global $wpdb;
    $query = stripslashes_deep($args['query'] ?? '');
    if ($query) {
        $query = Sql::convenienceQuery($query);
    }

    $dsl = [
        'select' => [
            'fields' => [
                ['name' => 's.name', 'alias' => 'name'],
                ['name' => 'po.id', 'alias' => 'id'],
                ['name' => 'po.order_status', 'alias' => 'order_status'],
                ['name' => 'po.status', 'alias' => 'status'],
                ['name' => 'po.num_items', 'alias' => 'num_items'],
                ['name' => 'po.invoice_total', 'alias' => 'invoice_total'],
                ['name' => 'po.invoice_number', 'alias' => 'invoice_number'],
                ['name' => 'po.paid_at', 'alias' => 'paid_at'],
            ]
        ],
        'from' => [
            'table' => 'purchase_orders',
            'alias' => 'po'
        ],
        'join' => [
            'conditions' => [
                ['type' => 'LEFT JOIN', 'table' => 'purchase_suppliers', 'alias' => 's', 'on' => 's.id = po.supplier_id']
            ]
        ],
        'where' => [
            'conditions' => [
                's.deleted_at IS NULL',
                'po.deleted_at IS NULL',
                'po.id IS NOT NULL',
            ]
        ],
        'groupBy' => [
            'field' => 'id'
        ],
        'orderBy' => [
            'field' => 'id',
            'direction' => 'DESC'
        ],
        'binds' => [
            'parameters' => []
        ],
        'calcRows' => [
            'value' => true
        ],
        'offset' => [
            'value' => $args['offset'] ?? 0
        ],
        'limit' => [
            'value' => $args['limit'] ?? 10
        ]
    ];

    // Convert the DSL to options
    $options = self::convertDslToOptions($dsl);

    // Run the SQL query
    $sql = Sql::select($options);
    $ret = $wpdb->get_results(
        count($options['binds']) ? $wpdb->prepare($sql, $options['binds']) : $sql,
        ARRAY_A
    ) ?? [];

    // Return the results
    return $ret;
}
```

Please note that this refactored method does not include all the logic from the original `Find()` method. Some parts of the original method, such as the handling of `$args['filterYear']`, `$args['filterTags']`, and `$args['filterStatus']`, are not included in this refactored method because they would require additional changes to the DSL and the `convertDslToOptions()` function.

[ME]: Properly handle the `from` field, which is itself a DSL representation that depends on $query. Create a method FindToFromDSL($query).

[AI]: The `from` field in your example is a complex SQL subquery that depends on the `$query` variable. To handle this, you could create a separate method `FindToFromDSL($query)` that returns a DSL representation of the `from` field. Here's how you could do it:

```php
/**
 * Returns a DSL representation of the `from` field.
 *
 * @param string $query The query string.
 * @return array The DSL representation.
 */
public static function FindToFromDSL(string $query): array
{
    $joinType = $query ? 'RIGHT' : 'LEFT';

    $dsl = [
        'table' => 'purchase_orders',
        'alias' => 'po',
        'join' => [
            'type' => $joinType,
            'table' => 'purchase_suppliers',
            'alias' => 's',
            'on' => 's.id = po.supplier_id'
        ],
        'where' => [
            'conditions' => [
                'po.deleted_at IS NULL',
                's.deleted_at IS NULL',
            ]
        ]
    ];

    if ($query) {
        $dsl['union'] = [
            'table' => 'purchase_order_items',
            'alias' => 'poi',
            'join' => [
                'type' => 'LEFT JOIN',
                'table' => 'purchase_orders',
                'alias' => 'po',
                'on' => 'po.id = poi.purchase_order_id'
            ],
            'where' => [
                'conditions' => [
                    'poi.deleted_at IS NULL',
                    'po.deleted_at IS NULL',
                    's.deleted_at IS NULL',
                    "MATCH (p.title) AGAINST(%s IN BOOLEAN MODE)"
                ]
            ],
            'groupBy' => 'purchase_order_id'
        ];
    }

    return $dsl;
}
```

This method constructs a DSL representation of the `from` field based on the `$query` variable. If `$query` is not empty, it adds a `union` section to the DSL.

Please note that this is a simplified example and may not cover all the complexities of your actual `from` field. You may need to adjust this method to fit your
#+end_ai