<?php

/**
 * Submissions Management Class
 *
 * @package Giraforms
 */
// Exit if accessed directly.
if ( !defined( 'ABSPATH' ) ) {
    exit;
}
/**
 * Class GiraForms_Submissions
 */
class GiraForms_Submissions {
    /**
     * Get submissions with formatted data.
     *
     * @param array $args Query arguments.
     * @return array
     */
    public static function get_submissions( $args = array() ) {
        $db = GiraForms_Database::instance();
        $submissions = $db->get_submissions( $args );
        $formatted = array();
        foreach ( $submissions as $submission ) {
            $meta = $db->get_submission_meta( $submission->id );
            $formatted[] = array(
                'id'         => $submission->id,
                'form_id'    => $submission->form_id,
                'form_name'  => ( isset( $meta['giraforms_name'] ) ? $meta['giraforms_name'] : $submission->form_id ),
                'post_id'    => $submission->post_id,
                'user_id'    => $submission->user_id,
                'user_ip'    => $submission->user_ip,
                'user_agent' => $submission->user_agent,
                'status'     => $submission->status,
                'created_at' => $submission->created_at,
                'data'       => $meta,
            );
        }
        return $formatted;
    }

    /**
     * Get single submission.
     *
     * @param int $submission_id Submission ID.
     * @return array|null
     */
    public static function get_submission( $submission_id ) {
        $db = GiraForms_Database::instance();
        $submission = $db->get_submission( $submission_id );
        if ( !$submission ) {
            return null;
        }
        $meta = $db->get_submission_meta( $submission_id );
        return array(
            'id'         => $submission->id,
            'form_id'    => $submission->form_id,
            'form_name'  => ( isset( $meta['giraforms_name'] ) ? $meta['giraforms_name'] : $submission->form_id ),
            'post_id'    => $submission->post_id,
            'user_id'    => $submission->user_id,
            'user_ip'    => $submission->user_ip,
            'user_agent' => $submission->user_agent,
            'status'     => $submission->status,
            'created_at' => $submission->created_at,
            'data'       => $meta,
        );
    }

    /**
     * Delete submission and associated files.
     *
     * @param int $submission_id Submission ID.
     * @return bool
     */
    public static function delete_submission( $submission_id ) {
        // Get submission data first to retrieve uploaded files
        $submission = self::get_submission( $submission_id );
        // Delete submission from database
        $db = GiraForms_Database::instance();
        return $db->delete_submission( $submission_id );
    }

    /**
     * Update submission status.
     *
     * @param int    $submission_id Submission ID.
     * @param string $status New status.
     * @return bool
     */
    public static function update_status( $submission_id, $status ) {
        $db = GiraForms_Database::instance();
        return $db->update_submission_status( $submission_id, $status );
    }

    /**
     * Get form statistics.
     *
     * @param string $form_id Form ID.
     * @return array
     */
    public static function get_form_stats( $form_id ) {
        $db = GiraForms_Database::instance();
        return array(
            'total'  => $db->count_submissions( array(
                'form_id' => $form_id,
            ) ),
            'unread' => $db->count_submissions( array(
                'form_id' => $form_id,
                'status'  => 'unread',
            ) ),
            'read'   => $db->count_submissions( array(
                'form_id' => $form_id,
                'status'  => 'read',
            ) ),
        );
    }

    /**
     * Get all unique form IDs.
     *
     * @return array
     */
    public static function get_form_ids() {
        global $wpdb;
        $db = GiraForms_Database::instance();
        $table = $db->get_submissions_table();
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions.
        $results = $wpdb->get_col( "SELECT DISTINCT form_id FROM {$wpdb->prefix}giraforms_submissions ORDER BY form_id ASC" );
        return ( $results ? $results : array() );
    }

    /**
     * Get all forms with submission stats.
     *
     * @return array
     */
    public static function get_forms_list() {
        global $wpdb;
        $db = GiraForms_Database::instance();
        $table = $db->get_submissions_table();
        $meta_table = $db->get_submission_meta_table();
        // Get form IDs with stats and latest submission date
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions.
        $results = $wpdb->get_results( "SELECT\n\t\t\t\ts.form_id,\n\t\t\t\tCOUNT(*) as total,\n\t\t\t\tSUM(CASE WHEN s.status = 'unread' THEN 1 ELSE 0 END) as unread,\n\t\t\t\tMAX(s.created_at) as latest_submission,\n\t\t\t\tMAX(s.post_id) as post_id\n\t\t\tFROM {$wpdb->prefix}giraforms_submissions s\n\t\t\tGROUP BY s.form_id\n\t\t\tORDER BY latest_submission DESC" );
        if ( !$results ) {
            return array();
        }
        $forms = array();
        foreach ( $results as $row ) {
            // Get form name from the latest submission
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Custom table for form submissions metadata.
            $form_name = $wpdb->get_var( $wpdb->prepare( "SELECT m.meta_value\n\t\t\t\t\tFROM {$wpdb->prefix}giraforms_submission_meta m\n\t\t\t\t\tINNER JOIN {$wpdb->prefix}giraforms_submissions s ON m.submission_id = s.id\n\t\t\t\t\tWHERE s.form_id = %s\n\t\t\t\t\tAND m.meta_key = 'giraforms_name'\n\t\t\t\t\tORDER BY s.created_at DESC\n\t\t\t\t\tLIMIT 1", $row->form_id ) );
            $forms[] = array(
                'form_id'           => $row->form_id,
                'form_name'         => ( $form_name ? $form_name : $row->form_id ),
                'total'             => (int) $row->total,
                'unread'            => (int) $row->unread,
                'latest_submission' => $row->latest_submission,
                'post_id'           => (int) $row->post_id,
            );
        }
        return $forms;
    }

}
