<?php
/**
 * Database operations handler
 *
 * @package SPAI_Contact_Form
 */

class SPAI_DB_Handler {
    
    /**
     * Get all forms
     */
    public function get_forms($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'active' => null,
            'orderby' => 'created_at',
            'order' => 'DESC',
            'limit' => -1,
            'offset' => 0
        );
        
        $args = wp_parse_args($args, $defaults);
        $table = $wpdb->prefix . 'spai_forms';
        
        $where = '1=1';
        if ($args['active'] !== null) {
            $where .= $wpdb->prepare(' AND active = %d', $args['active']);
        }
        
        $sql = "SELECT * FROM $table WHERE $where";
        $sql .= " ORDER BY {$args['orderby']} {$args['order']}";
        
        if ($args['limit'] > 0) {
            $sql .= $wpdb->prepare(' LIMIT %d OFFSET %d', $args['limit'], $args['offset']);
        }
        
        return $wpdb->get_results($sql);
    }
    
    /**
     * Get a single form
     */
    public function get_form($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_forms';
        return $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE id = %d", $id));
    }
    
    /**
     * Insert or update a form
     */
    public function save_form($data, $id = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_forms';
        
        // Ensure form_fields is properly encoded
        $form_fields = isset($data['form_fields']) ? $data['form_fields'] : array();
        if (is_string($form_fields)) {
            // If it's already a JSON string, decode it first
            $decoded = json_decode($form_fields, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                $form_fields = $decoded;
            }
        }
        
        // Ensure form_settings is properly encoded
        $form_settings = isset($data['form_settings']) ? $data['form_settings'] : array();
        if (is_string($form_settings)) {
            // If it's already a JSON string, decode it first
            $decoded = json_decode($form_settings, true);
            if (json_last_error() === JSON_ERROR_NONE) {
                $form_settings = $decoded;
            }
        }
        
        $form_data = array(
            'form_name' => sanitize_text_field($data['form_name']),
            'form_fields' => wp_json_encode($form_fields),
            'form_settings' => wp_json_encode($form_settings),
            'active' => isset($data['active']) ? (int)$data['active'] : 1
        );
        
        if ($id) {
            $form_data['updated_at'] = current_time('mysql');
            $result = $wpdb->update($table, $form_data, array('id' => $id));
            return $result !== false ? $id : false;
        } else {
            $form_data['created_at'] = current_time('mysql');
            $form_data['updated_at'] = current_time('mysql');
            $result = $wpdb->insert($table, $form_data);
            return $result !== false ? $wpdb->insert_id : false;
        }
    }
    
    /**
     * Delete a form
     */
    public function delete_form($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_forms';
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }
    
    /**
     * Save submission
     */
    public function save_submission($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_submissions';
        
        $submission_data = array(
            'form_id' => $data['form_id'],
            'submission_data' => wp_json_encode($data['submission_data']),
            'integration_status' => wp_json_encode($data['integration_status']),
            'file_data' => isset($data['file_data']) ? wp_json_encode($data['file_data']) : null,
            'ip_address' => $data['ip_address'],
            'user_agent' => $data['user_agent'],
            'created_at' => current_time('mysql')
        );
        
        $result = $wpdb->insert($table, $submission_data);
        
        return $result !== false ? $wpdb->insert_id : false;
    }
    
    /**
     * Get submission
     */
    public function get_submission($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_submissions';
        $row = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table WHERE id = %d", $id));
        
        if ($row) {
            $row->submission_data = json_decode($row->submission_data, true);
            $row->integration_status = json_decode($row->integration_status, true);
            $row->file_data = $row->file_data ? json_decode($row->file_data, true) : null;
        }
        
        return $row;
    }
    
    /**
     * Update submission integration status
     */
    public function update_submission_status($submission_id, $integration_type, $status, $external_id = null, $error = null) {
        global $wpdb;
        
        $submission = $this->get_submission($submission_id);
        if (!$submission) {
            return false;
        }
        
        $integration_status = $submission->integration_status;
        $integration_status[$integration_type] = array(
            'status' => $status,
            'external_id' => $external_id,
            'error' => $error,
            'updated_at' => current_time('mysql')
        );
        
        $table = $wpdb->prefix . 'spai_submissions';
        return $wpdb->update(
            $table,
            array('integration_status' => wp_json_encode($integration_status)),
            array('id' => $submission_id)
        );
    }
    
    /**
     * Add to integration queue
     */
    public function add_to_queue($submission_id, $integration_type, $payload) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_integration_queue';
        
        $queue_data = array(
            'submission_id' => $submission_id,
            'integration_type' => $integration_type,
            'payload' => wp_json_encode($payload),
            'status' => 'pending',
            'created_at' => current_time('mysql')
        );
        
        return $wpdb->insert($table, $queue_data);
    }
    
    /**
     * Get pending queue items
     */
    public function get_pending_queue_items($limit = 10) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_integration_queue';
        
        $sql = $wpdb->prepare(
            "SELECT * FROM $table 
            WHERE status = 'pending' 
            AND attempts < 3 
            ORDER BY created_at ASC 
            LIMIT %d",
            $limit
        );
        
        return $wpdb->get_results($sql);
    }
    
    /**
     * Update queue item status
     */
    public function update_queue_status($queue_id, $status, $external_id = null, $error = null) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'spai_integration_queue';
        
        $update_data = array(
            'status' => $status,
            'external_id' => $external_id,
            'last_error' => $error,
            'processed_at' => $status === 'completed' ? current_time('mysql') : null
        );
        
        if ($status === 'failed') {
            $wpdb->query($wpdb->prepare(
                "UPDATE $table SET attempts = attempts + 1 WHERE id = %d",
                $queue_id
            ));
        }
        
        return $wpdb->update($table, $update_data, array('id' => $queue_id));
    }
    
    /**
     * Get all submissions with form data
     */
    public function get_all_submissions() {
        global $wpdb;
        
        $table_submissions = $wpdb->prefix . 'spai_submissions';
        $table_forms = $wpdb->prefix . 'spai_forms';
        
        $sql = "SELECT s.*, f.form_name 
                FROM $table_submissions s 
                LEFT JOIN $table_forms f ON s.form_id = f.id 
                ORDER BY s.created_at DESC";
        
        $results = $wpdb->get_results($sql);
        
        // Decode JSON fields
        foreach ($results as &$row) {
            $row->submission_data = json_decode($row->submission_data, true);
            $row->integration_status = json_decode($row->integration_status, true);
            $row->file_data = $row->file_data ? json_decode($row->file_data, true) : null;
        }
        
        return $results;
    }
    
    /**
     * Drop all plugin tables
     */
    public function drop_tables() {
        global $wpdb;
        
        $tables = array(
            $wpdb->prefix . 'spai_forms',
            $wpdb->prefix . 'spai_submissions',
            $wpdb->prefix . 'spai_integration_queue'
        );
        
        foreach ($tables as $table) {
            $wpdb->query("DROP TABLE IF EXISTS $table");
        }
    }
}
