<?php
/**
 * Form Response Model
 *
 * @package Freelance_Studio
 */

class FS_Form_Response {

    /**
     * Create a new form response
     *
     * @param array $data Response data
     * @return int|false Insert ID or false on failure
     */
    public static function create($data) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_form_responses';
        
        $insert_data = array(
                'form_id' => intval($data['form_id']),
                'client_id' => !empty($data['client_id']) ? intval($data['client_id']) : null,
                'project_id' => !empty($data['project_id']) ? intval($data['project_id']) : null,
                'respondent_name' => isset($data['respondent_name']) ? sanitize_text_field($data['respondent_name']) : '',
                'respondent_email' => isset($data['respondent_email']) ? sanitize_email($data['respondent_email']) : '',
                'response_data' => wp_json_encode($data['response_data']),
                'ip_address' => isset($data['ip_address']) ? sanitize_text_field($data['ip_address']) : '',
                'user_agent' => isset($data['user_agent']) ? sanitize_text_field(substr($data['user_agent'], 0, 500)) : '',
                'submitted_at' => current_time('mysql'),
        );
        
        $result = $wpdb->insert(
            $table,
            $insert_data,
            array('%d', '%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s')
        );
        
        if ($result === false) {
            error_log('FS_Form_Response::create failed: ' . $wpdb->last_error);
            return false;
        }
        
        $new_id = $wpdb->insert_id;
        FS_DB_Helper::fix_null_fields($table, $new_id, array(
            'client_id' => $insert_data['client_id'],
            'project_id' => $insert_data['project_id'],
        ));
        
        return $new_id;
    }

    /**
     * Get a response by ID
     *
     * @param int $id Response ID
     * @return object|null
     */
    public static function get($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_form_responses';
        
        $response = $wpdb->get_row($wpdb->prepare(
            "SELECT * FROM {$table} WHERE id = %d",
            $id
        ));
        
        if ($response) {
            $response->response_data = json_decode($response->response_data ?? '', true) ?: array();
        }
        
        return $response;
    }

    /**
     * Get all responses for a form
     *
     * @param int   $form_id Form ID
     * @param array $args    Query arguments
     * @return array
     */
    public static function get_by_form($form_id, $args = array()) {
        global $wpdb;
        
        $defaults = array(
            'orderby' => 'submitted_at',
            'order' => 'DESC',
            'limit' => 100,
            'offset' => 0,
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $table = $wpdb->prefix . 'fs_form_responses';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']) ?: 'submitted_at DESC';
        
        $sql = $wpdb->prepare(
            "SELECT r.*, c.name as client_name, c.company_name as client_company
             FROM {$table} r
             LEFT JOIN {$clients_table} c ON r.client_id = c.id
             WHERE r.form_id = %d
             ORDER BY r.{$orderby}
             LIMIT %d OFFSET %d",
            $form_id,
            $args['limit'],
            $args['offset']
        );
        
        $results = $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        
        foreach ($results as &$response) {
            $response->response_data = json_decode($response->response_data ?? '', true) ?: array();
        }
        
        return $results;
    }

    /**
     * Get all responses
     *
     * @param array $args Query arguments
     * @return array
     */
    public static function get_all($args = array()) {
        global $wpdb;
        
        $defaults = array(
            'form_id' => null,
            'client_id' => null,
            'project_id' => null,
            'orderby' => 'submitted_at',
            'order' => 'DESC',
            'limit' => 100,
            'offset' => 0,
        );
        
        $args = wp_parse_args($args, $defaults);
        
        $table = $wpdb->prefix . 'fs_form_responses';
        $forms_table = $wpdb->prefix . 'fs_forms';
        $clients_table = $wpdb->prefix . 'fs_clients';
        
        $where = array('1=1');
        $values = array();
        
        if ($args['form_id']) {
            $where[] = 'r.form_id = %d';
            $values[] = $args['form_id'];
        }
        
        if ($args['client_id']) {
            $where[] = 'r.client_id = %d';
            $values[] = $args['client_id'];
        }
        
        if ($args['project_id']) {
            $where[] = 'r.project_id = %d';
            $values[] = $args['project_id'];
        }
        
        $where_clause = implode(' AND ', $where);
        $orderby = sanitize_sql_orderby($args['orderby'] . ' ' . $args['order']) ?: 'submitted_at DESC';
        
        $sql = "SELECT r.*, 
                       f.name as form_name,
                       c.name as client_name,
                       c.company_name as client_company
                FROM {$table} r
                LEFT JOIN {$forms_table} f ON r.form_id = f.id
                LEFT JOIN {$clients_table} c ON r.client_id = c.id
                WHERE {$where_clause}
                ORDER BY r.{$orderby}
                LIMIT %d OFFSET %d";
        
        $values[] = $args['limit'];
        $values[] = $args['offset'];
        
        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }
        
        $results = $wpdb->get_results($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        
        foreach ($results as &$response) {
            $response->response_data = json_decode($response->response_data ?? '', true) ?: array();
        }
        
        return $results;
    }

    /**
     * Delete a response
     *
     * @param int $id Response ID
     * @return int|false
     */
    public static function delete($id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_form_responses';
        
        // Get response to find any uploaded files
        $response = self::get($id);
        
        if ($response && !empty($response->response_data)) {
            // Clean up any uploaded files
            foreach ($response->response_data as $field_id => $value) {
                if (is_array($value) && isset($value['attachment_id'])) {
                    // Delete the attachment
                    wp_delete_attachment(intval($value['attachment_id']), true);
                }
            }
        }
        
        return $wpdb->delete($table, array('id' => $id), array('%d'));
    }

    /**
     * Delete all responses for a form
     *
     * @param int $form_id Form ID
     * @return int|false
     */
    public static function delete_by_form($form_id) {
        global $wpdb;
        
        // Get all responses to clean up files
        $responses = self::get_by_form($form_id, array('limit' => 1000));
        
        foreach ($responses as $response) {
            self::delete($response->id);
        }
        
        return true;
    }

    /**
     * Count responses
     *
     * @param array $args Query arguments
     * @return int
     */
    public static function count($args = array()) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_form_responses';
        
        $where = array('1=1');
        $values = array();
        
        if (!empty($args['form_id'])) {
            $where[] = 'form_id = %d';
            $values[] = $args['form_id'];
        }
        
        if (!empty($args['client_id'])) {
            $where[] = 'client_id = %d';
            $values[] = $args['client_id'];
        }
        
        if (!empty($args['project_id'])) {
            $where[] = 'project_id = %d';
            $values[] = $args['project_id'];
        }
        
        $where_clause = implode(' AND ', $where);
        
        $sql = "SELECT COUNT(*) FROM {$table} WHERE {$where_clause}";
        
        if (!empty($values)) {
            $sql = $wpdb->prepare($sql, $values); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
        }
        
        return (int) $wpdb->get_var($sql); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.NotPrepared
    }

    /**
     * Export responses to CSV
     *
     * @param int $form_id Form ID
     * @return string CSV content
     */
    public static function export_csv($form_id) {
        $form = FS_Form::get($form_id);
        
        if (!$form) {
            return '';
        }
        
        $responses = self::get_by_form($form_id, array('limit' => 10000));
        
        if (empty($responses)) {
            return '';
        }
        
        // Build header row from form fields
        $headers = array('Response ID', 'Submitted At', 'Respondent Name', 'Respondent Email');
        $field_ids = array();
        
        foreach ($form->fields as $field) {
            if (!in_array($field['type'], array('heading', 'paragraph', 'divider', 'file_download'))) {
                $headers[] = $field['label'];
                $field_ids[] = $field['id'];
            }
        }
        
        // Start CSV output
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen -- Using PHP stream wrapper for in-memory CSV generation.
        $output = fopen('php://temp', 'r+');
        
        // Write BOM for Excel UTF-8 compatibility
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fputs -- Writing BOM marker to stream for Excel compatibility.
        fputs($output, "\xEF\xBB\xBF");
        
        // Write headers
        fputcsv($output, $headers);
        
        // Write data rows
        foreach ($responses as $response) {
            $row = array(
                $response->id,
                $response->submitted_at,
                $response->respondent_name,
                $response->respondent_email,
            );
            
            foreach ($field_ids as $field_id) {
                $value = isset($response->response_data[$field_id]) ? $response->response_data[$field_id] : '';
                
                // Handle array values (multiple choice, file uploads)
                if (is_array($value)) {
                    if (isset($value['url'])) {
                        $value = $value['url']; // File upload
                    } else {
                        $value = implode(', ', $value); // Multiple choice
                    }
                }
                
                $row[] = $value;
            }
            
            fputcsv($output, $row);
        }
        
        rewind($output);
        $csv = stream_get_contents($output);
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose -- Closing stream/file handle after CSV generation.
        fclose($output);
        
        return $csv;
    }

    /**
     * Get response statistics for a form
     *
     * @param int $form_id Form ID
     * @return array
     */
    public static function get_stats($form_id) {
        global $wpdb;
        
        $table = $wpdb->prefix . 'fs_form_responses';
        
        $stats = array(
            'total' => 0,
            'today' => 0,
            'this_week' => 0,
            'this_month' => 0,
        );
        
        // Total responses
        $stats['total'] = self::count(array('form_id' => $form_id));
        
        // Today
        $stats['today'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND DATE(submitted_at) = CURDATE()",
            $form_id
        ));
        
        // This week
        $stats['this_week'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND submitted_at >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)",
            $form_id
        ));
        
        // This month
        $stats['this_month'] = (int) $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM {$table} WHERE form_id = %d AND MONTH(submitted_at) = MONTH(CURDATE()) AND YEAR(submitted_at) = YEAR(CURDATE())",
            $form_id
        ));
        
        return $stats;
    }
}
