<?php

namespace GriffinForms\Admin\Sql;

class Form extends \GriffinForms\Admin\Sql\Single
{
    public function fieldsInForm($form_id)
    {
        global $wpdb;

        if (!$this->itemExists('form', $form_id)) {
            return [];
        }

        $field_table = $this->config->getTable('field');
        $field_ids = [];

        $page_ids = $this->maybeDecode(
            $wpdb->get_var(
                $wpdb->prepare(
                    'SELECT formpages FROM %i WHERE id = %d',
                    $this->config->getTable('form'),
                    $form_id
                )
            )
        );

        if (!is_array($page_ids)) {
            return [];
        }

        foreach ($page_ids as $page_id) {
            $row_ids = $this->maybeDecode(
                $wpdb->get_var(
                    $wpdb->prepare(
                        'SELECT pagerows FROM %i WHERE id = %d',
                        $this->config->getTable('formpage'),
                        $page_id
                    )
                )
            );

            if (!is_array($row_ids)) {
                continue;
            }

            foreach ($row_ids as $row_id) {
                $column_ids = $this->maybeDecode(
                    $wpdb->get_var(
                        $wpdb->prepare(
                            'SELECT rowcolumns FROM %i WHERE id = %d',
                            $this->config->getTable('pagerow'),
                            $row_id
                        )
                    )
                );

                if (!is_array($column_ids)) {
                    continue;
                }

                foreach ($column_ids as $column_id) {
                    $column_field_ids = $this->maybeDecode(
                        $wpdb->get_var(
                            $wpdb->prepare(
                                'SELECT fields FROM %i WHERE id = %d',
                                $this->config->getTable('rowcolumn'),
                                $column_id
                            )
                        )
                    );

                    if (is_array($column_field_ids)) {
                        $field_ids = array_merge($field_ids, $column_field_ids);
                    }
                }
            }
        }

        if (empty($field_ids)) {
            return [];
        }

        $placeholders = implode(',', array_fill(0, count($field_ids), '%d'));
        $sql = $wpdb->prepare(
            "SELECT * FROM {$field_table} WHERE id IN ($placeholders)",
            ...$field_ids
        );
        
        return $wpdb->get_results(
            $wpdb->prepare(
                "SELECT * FROM %i WHERE id IN ($placeholders)",
                $field_table,
                ...$field_ids
            )
        );
    }
}