<?php

class Cat_Lead_Admin {

    private static $all_columns = [
        'id' => 'ID',
        'request_id' => 'Request ID',
        'request_type' => 'Request Type',
        'current_request_status' => 'Current Request Status',
        'branch' => 'Branch',
        'created_date' => 'Created Date',
        'released_date' => 'Released Date',
        'first_received_date' => 'First Received Date',
        'first_received_by' => 'First Received By',
        'closed_date' => 'Closed Date',
        'closed_by' => 'Closed By',
        'disposition' => 'Disposition',
        'cart_info' => 'Cart Information',
        'rental_start_date' => 'Rental Start Date',
        'rental_end_date' => 'Rental End Date',
        'utm_source' => 'UTM Source',
        'utm_medium' => 'UTM Medium',
        'utm_term' => 'UTM Term',
        'utm_content' => 'UTM Content',
        'utm_campaign' => 'UTM Campaign',
        'referring_url' => 'Referring URL',
        'first_name' => 'First Name',
        'last_name' => 'Last Name',
        'email_id' => 'Email ID',       
        'phone' => 'Phone Number',
        'company_name' => 'Companty Name',
        'job_site_address' => 'Job Site Address',
        'job_site_city' => 'Job Site City',
        'job_site_state' => 'Job Siter State',
        'job_site_postcode' => 'Job Site Postcode',
        'job_site_county' => 'Job Site County',
        'delivery_status' => 'Delivery Status',
        'subscription_status' => 'Subscription Status',
        'zift_id' => 'ZIFT ID',
        'DLMA_dealer_code' => 'DLMA Dealer Code',
        'google_Geocode' => 'Google GEO Code',
        'Interface' => 'Interface',
        'National_Account_Number' => 'National Account Number',
        'Time_Zone' => 'Time Zone',
        'last_updated' => 'Last Updated'
    ];

    public static function init() {
        add_action('admin_menu', [__CLASS__, 'add_admin_menu']);
        add_action('admin_enqueue_scripts', [__CLASS__, 'enqueue_scripts']);
        add_action('wp_ajax_cat_lead_save_remark', [__CLASS__, 'save_remark']);
        add_action('admin_post_cat_lead_handle_import', [__CLASS__, 'handle_import']);
        add_action('admin_post_cat_lead_perform_import', [__CLASS__, 'perform_import']);

        // Add screen options
        add_filter('screen_settings', [__CLASS__, 'add_screen_options'], 10, 2);
        add_action('admin_init', [__CLASS__, 'save_screen_options']);

    }

    public static function add_admin_menu() {
        add_menu_page(
            'CAT Lead',
            'CAT Lead',
            'manage_options',
            'cat-lead',
            [__CLASS__, 'display_page'],
            'dashicons-pets',
            21
        );

        // Hidden submenu page for mapping, not visible in the menu
        add_submenu_page(
            null, 
            'CAT Lead Import', 
            'Import', 
            'manage_options', 
            'cat-lead-import', 
            [__CLASS__, 'display_import_page']
        );
    }
   
    
    
    public static function enqueue_scripts($hook_suffix) {
        if ($hook_suffix == 'toplevel_page_cat-lead') {
            wp_enqueue_script('jquery');
            wp_enqueue_script('cat-lead-admin', plugin_dir_url(__FILE__) . '../assets/js/oracle-lead-admin.js', ['jquery'], '1.0', true);
            wp_enqueue_style('cat-lead-admin', plugin_dir_url(__FILE__) . '../assets/css/oracle-lead-admin.css');

        }
    }

    
    
    public static function add_screen_options($status, $args) {
        if ($args->base == 'toplevel_page_cat-lead') {
            // Get current user's selected columns or use defaults
            $columns = get_user_meta(get_current_user_id(), 'cat_lead_columns', true);
            $columns = $columns ? $columns : ['id', 'request_id', 'created_date', 'first_name', 'phone', 'cart_info'];

            $rows_per_page = get_user_meta(get_current_user_id(), 'cat_lead_rows_per_page', true);
            $rows_per_page = $rows_per_page ? $rows_per_page : 100; // Default to 50 rows

            ob_start(); ?>

            <fieldset class="metabox-prefs">
                <legend><?php _e('Select columns to display', 'cat-lead'); ?></legend>
                <form method="post" action=""><div style="display: flex; flex-wrap: wrap; gap: 10px;">
                  <?php foreach (self::$all_columns as $column_key => $column_label) : ?>
                    <label style="font-size: 15px; display: inline-block;">
                      <input type="checkbox" name="cat_lead_columns[]" value="<?php echo esc_attr($column_key); ?>" <?php checked(in_array($column_key, $columns)); ?> />
                        <?php echo esc_html($column_label); ?>
                    </label>
                  <?php endforeach; ?>
                </div>
                    <label>
                        <span><?php _e('Number of rows per page:', 'cat-lead'); ?></span>
                        <input type="number" name="cat_lead_rows_per_page" value="<?php echo esc_attr($rows_per_page); ?>" min="1" max="500" />
                    </label><br />
                    <input type="submit" class="button-primary" value="Save" />
                </form>
            </fieldset>

            <?php
            $html = ob_get_clean();
            return $status . $html;
        }
        return $status;
    }

    public static function save_screen_options() {
        if (isset($_POST['cat_lead_columns']) && is_array($_POST['cat_lead_columns'])) {
            update_user_meta(get_current_user_id(), 'cat_lead_columns', $_POST['cat_lead_columns']);
        }

        if (isset($_POST['cat_lead_rows_per_page'])) {
            update_user_meta(get_current_user_id(), 'cat_lead_rows_per_page', intval($_POST['cat_lead_rows_per_page']));
        }
    }

public static function display_page() {
    // Display success message
    if (isset($_GET['import_success']) && isset($_GET['message'])) {
        echo '<div class="notice notice-success is-dismissible"><p>' . esc_html(urldecode($_GET['message'])) . '</p></div>';
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'cat_lead';

    // Extract unique month/year from "date_raised" column
    $months_years = $wpdb->get_results("SELECT DISTINCT DATE_FORMAT(created_date, '%Y-%m') AS month_year FROM $table_name ORDER BY month_year DESC", ARRAY_A);

    // Handle date filter if applied
    $date_filter = isset($_POST['date_filter']) ? sanitize_text_field($_POST['date_filter']) : '';

    // Get rows per page from user settings
    $rows_per_page = get_user_meta(get_current_user_id(), 'cat_lead_rows_per_page', true);
    $rows_per_page = $rows_per_page ? $rows_per_page : 200; // Default to 50 rows
    $paged = isset($_GET['paged']) ? absint($_GET['paged']) : 1;
    $offset = ($paged - 1) * $rows_per_page;

    // Handle search query
    $search_query = isset($_POST['search_query']) ? sanitize_text_field($_POST['search_query']) : '';

    // Build the base query
    $sql = "SELECT * FROM $table_name WHERE 1=1";

    // Apply date filter if set
    if ($date_filter) {
        $sql .= $wpdb->prepare(" AND DATE_FORMAT(created_date, '%Y-%m') = %s", $date_filter);
    }

    // Apply search query if set
    if ($search_query) {
        $sql .= " AND (";
        foreach (self::$all_columns as $column_key => $column_label) {
            $sql .= $wpdb->prepare(" $column_key LIKE %s OR", '%' . $wpdb->esc_like($search_query) . '%');
        }
        $sql = rtrim($sql, 'OR'); // Remove the trailing OR
        $sql .= ")";
    }

    // Apply pagination
    $sql .= $wpdb->prepare(" LIMIT %d OFFSET %d", $rows_per_page, $offset);

    // Fetch results
    $results = $wpdb->get_results($sql, ARRAY_A);

    // Get user-selected columns or default columns
    $columns = get_user_meta(get_current_user_id(), 'cat_lead_columns', true);
    if (!$columns) {
        $columns = ['id', 'request_id', 'created_date', 'first_name', 'phone', 'cart_info']; // Default columns
    }

    // Get total number of items for pagination
    $total_items_sql = "SELECT COUNT(*) FROM $table_name WHERE 1=1";
    if ($date_filter) {
        $total_items_sql .= $wpdb->prepare(" AND DATE_FORMAT(created_date, '%Y-%m') = %s", $date_filter);
    }
    if ($search_query) {
        $total_items_sql .= " AND (";
        foreach (self::$all_columns as $column_key => $column_label) {
            $total_items_sql .= $wpdb->prepare(" $column_key LIKE %s OR", '%' . $wpdb->esc_like($search_query) . '%');
        }
        $total_items_sql = rtrim($total_items_sql, 'OR');
        $total_items_sql .= ")";
    }
    $total_items = $wpdb->get_var($total_items_sql);

    echo '<div class="wrap">';
    echo '<h1>CAT Lead Data</h1>';

     // Import/Export buttons, Date Filter, and Search
     echo '<div class="tablenav top">';

     // Import/Export buttons
     echo '<div class="alignleft actions" style="display: flex; gap: 10px; align-items: center; padding:5px; border-style: ridge;">';
     cat_Lead_Import_Export::render_import_form();
     echo '|';
     echo '<a href="' . admin_url('admin-post.php?action=cat_lead_export') . '" class="button-primary">Export</a>';
     echo '</div>'; // .alignleft.actions

    // Date filter dropdown
    echo '<div class="alignleft" style="padding:5px; display: flex; gap: 10px; align-items: center;">';
    echo '<form method="post">';
    echo '<select name="date_filter">';
    echo '<option value="">Select Month/Year</option>';
    foreach ($months_years as $month_year) {
        $selected = ($month_year['month_year'] === $date_filter) ? 'selected' : '';
        echo '<option value="' . esc_attr($month_year['month_year']) . '" ' . $selected . '>' . esc_html(date('F Y', strtotime($month_year['month_year'] . '-01'))) . '</option>';
    }
    echo '</select>';
    echo '<button type="submit" class="button">Apply Filter</button>';
    echo '</form>';  
    echo '</div>'; 

    // Search form
    echo '<div class="alignright">';
    echo '<form method="post">';
    echo '<input type="text" name="search_query" placeholder="Search..." value="' . esc_attr($search_query) . '">';
    echo '<button type="submit" class="button">Search</button>';
    echo '</form>';
    echo '</div>'; // .alignright

    echo '<br class="clear">';
    echo '</div>'; // .tablenav.top

    echo '<br class="clear">';

    // Data display table
    if (!empty($results)) {
        echo '<div style="overflow-x: auto;">'; // Start the scrollable div
        echo '<table class="wp-list-table widefat auto striped">';
        echo '<thead>';
        echo '<tr>';
        foreach ($columns as $column) {
            echo '<th>' . esc_html(self::$all_columns[$column]) . '</th>';
        }
      //  echo '<th>Edit</th>';
        echo '</tr>';
        echo '</thead>';

        echo '<tbody>';
        foreach ($results as $row) {
            echo '<tr>';
            foreach ($columns as $column) {
                if($column == 'created_date') {
                    $date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $date); 
                    $time = date('H:i:s', $date); 
                    $formattedDateTime = $formattedDate . ' ' . $time; 
                    echo '<td>' . esc_html($formattedDateTime) . '</td>';
                }
                elseif($column == 'released_date') {
                    $release_date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $release_date); 
                    $time = date('H:i:s', $release_date); 
                    $formattedReleaseDateTime = $formattedDate . ' ' . $time; 
                    echo '<td>' . esc_html($formattedReleaseDateTime) . '</td>';
                }
                elseif($column == 'first_received_date') {
                    $first_received_date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $first_received_date); 
                    $time = date('H:i:s', $first_received_date); 
                    $formattedFirsteDateTime = $formattedDate . ' ' . $time; 
                    echo '<td>' . esc_html($formattedFirsteDateTime) . '</td>';
                }
                elseif($column == 'closed_date') {
                    $closed_date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $closed_date); 
                    $time = date('H:i:s', $closed_date); 
                    $formattedClosedeDateTime = $formattedDate . ' ' . $time; 
                    echo '<td>' . esc_html($formattedClosedeDateTime) . '</td>';
                }
                elseif($column == 'rental_start_date') {
                    $rental_start_date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $rental_start_date); 
                   
                    echo '<td>' . esc_html($formattedDate) . '</td>';
                }
                elseif($column == 'rental_end_date') {
                    $rental_end_date = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $rental_end_date); 
                   
                    echo '<td>' . esc_html($formattedDate) . '</td>';
                }
                elseif($column == 'last_updated') {
                    $last_updated = strtotime($row[$column]);
                    $formattedDate = date('d-m-Y', $last_updated); 
                    $time = date('H:i:s', $last_updated); 
                    $formattedLastUpdateDateTime = $formattedDate . ' ' . $time; 
                   
                    echo '<td>' . esc_html($formattedLastUpdateDateTime) . '</td>';
                }
                elseif ($column == 'ba_remarks') {
                    echo '<td class="ba-remarks" data-id="' . esc_attr($row['id']) . '">' . esc_html($row[$column]) . '</td>';
                } else {
                    echo '<td>' . esc_html($row[$column]) . '</td>';
                }
            }
          //  echo '<td>';
          //  echo '<span class="edit-remark dashicons dashicons-edit" data-id="' . esc_attr($row['id']) . '"></span>';
          //  echo '<span class="save-remark dashicons dashicons-yes" data-id="' . esc_attr($row['id']) . '" style="display:none;"></span>';
          //  echo '</td>';
            echo '</tr>';
        }
        echo '</tbody>';
        echo '</table>';
        echo '</div>'; // End the scrollable div

        // Pagination
        $total_pages = ceil($total_items / $rows_per_page);
        if ($total_pages > 1) {
            echo '<div class="tablenav bottom">';
            echo '<div class="tablenav-pages">';
            echo paginate_links([
                'base' => add_query_arg('paged', '%#%'),
                'format' => '',
                'prev_text' => __('&laquo;'),
                'next_text' => __('&raquo;'),
                'total' => $total_pages,
                'current' => $paged,
            ]);
            echo '</div>';
            echo '</div>';
        }
    } else {
        echo '<p>No data found.</p>';
    }

    echo '</div>';    
}

public static function display_import_page() {
    $step = isset($_GET['step']) ? sanitize_text_field($_GET['step']) : 'upload';
    $file_path = isset($_GET['file']) ? urldecode($_GET['file']) : '';

    if ($step === 'mapping' && file_exists($file_path)) {
        // Read CSV file to get headers
        $handle = fopen($file_path, 'r');
        $headers = fgetcsv($handle, 0, ',');
        fclose($handle);

        echo '<div class="wrap">';
        echo '<h1>Map CSV Columns</h1>';
        echo '<form method="post" action="' . admin_url('admin-post.php?action=cat_lead_perform_import') . '">';
        wp_nonce_field('cat_lead_import_nonce', 'cat_lead_import_nonce_field');
        echo '<input type="hidden" name="file_path" value="' . esc_attr($file_path) . '">';

        // Start a table for better layout
        echo '<table class="form-table">';
        echo '<thead><tr><th>Database Column</th><th>CSV Column</th></tr></thead>';
        echo '<tbody>';

        // Loop through each database column
        foreach (self::$all_columns as $column_key => $column_label) {
            echo '<tr>';
            echo '<td><strong>' . esc_html($column_label) . '</strong></td>';
            echo '<td>';
            echo '<select name="column_map[' . esc_attr($column_key) . ']">';
            echo '<option value="">Do Not Import</option>'; // Option to skip importing this column
            foreach ($headers as $header) {
                echo '<option value="' . esc_attr($header) . '">' . esc_html($header) . '</option>';
            }
            echo '</select>';
            echo '</td>';
            echo '</tr>';
        }

        // Extra CSV columns that do not match any DB columns
        foreach ($headers as $header) {
            if (!in_array($header, self::$all_columns)) {
                echo '<tr>';
                echo '<td><strong>Extra Column</strong></td>';
                echo '<td><select name="extra_columns[' . esc_attr($header) . ']">';
                echo '<option value="" selected>Do Not Import</option>';
                echo '</select></td>';
                echo '</tr>';
            }
        }

        echo '</tbody>';
        echo '</table>';

        echo '<p><label><input type="checkbox" name="update_existing" value="1"> Update existing records if ID matches</label></p>';
        echo '<button type="submit" class="button button-primary">Import Data</button>';
        echo '<div style="float:right;">';
        echo '<a href="' . admin_url('admin.php?page=cat-lead') . '" class="button-primary">Back</a>';
        echo '</div>';
        echo '</form>';
        echo '</div>';
    } else {
        echo '<div class="wrap">';
        echo '<h1>Import CAT Lead Data</h1>';
        echo '<form method="post" enctype="multipart/form-data" action="' . admin_url('admin-post.php?action=cat_lead_handle_import') . '">';
        wp_nonce_field('cat_lead_import_nonce', 'cat_lead_import_nonce_field');
        echo '<input type="file" name="import_file" accept=".csv" required />';
        echo '<button type="submit" class="button button-primary">Upload and Continue</button>';
        echo '<div style="float:right;">';
        echo '<a href="' . admin_url('admin.php?page=cat-lead') . '" class="button-primary">Back</a>';
        echo '</div>';
        echo '</form>';
        echo '</div>';
    }
} 

public static function handle_import() {
    if (!current_user_can('manage_options')) {
        wp_die('You are not allowed to perform this action.');
    }

    // Verify nonce
    if (!isset($_POST['cat_lead_import_nonce_field']) || !wp_verify_nonce($_POST['cat_lead_import_nonce_field'], 'cat_lead_import_nonce')) {
        wp_die('Security check failead.');
    }

    if (isset($_FILES['import_file']) && $_FILES['import_file']['error'] === UPLOAD_ERR_OK) {
        // Handle file upload
        $uploaded_file = $_FILES['import_file']['tmp_name'];
        $upload_dir = wp_upload_dir();
        $file_path = $upload_dir['path'] . '/cat_lead_import.csv';
        move_uploaded_file($uploaded_file, $file_path);

        // Redirect to column mapping page
        wp_redirect(admin_url('admin.php?page=cat-lead-import&step=mapping&file=' . urlencode($file_path)));
        exit;
    } else {
        wp_die('File upload failead.');
    }
}

public static function perform_import() {
    if (!current_user_can('manage_options')) {
        wp_die('You are not allowed to perform this action.');
    }

    // Verify nonce
    if (!isset($_POST['cat_lead_import_nonce_field']) || !wp_verify_nonce($_POST['cat_lead_import_nonce_field'], 'cat_lead_import_nonce')) {
        wp_die('Security check failead.');
    }

    $file_path = sanitize_text_field($_POST['file_path']);
    $column_map = $_POST['column_map'];
    $update_existing = isset($_POST['update_existing']) ? true : false;

    if (file_exists($file_path)) {
        global $wpdb;
        $table_name = $wpdb->prefix . 'cat_lead';

        // Open the CSV file and read the headers
        $handle = fopen($file_path, 'r');
        $headers = fgetcsv($handle, 0, ',');

        // Counters for records updated and added
        $updated_count = 0;
        $added_count = 0;

        // Process each row in the CSV
        while ($data = fgetcsv($handle, 0, ',')) {
            $row = [];
            foreach ($column_map as $db_column => $csv_column) {
                if (!empty($csv_column)) {
                    // Map the data from the CSV to the appropriate database column
                    $row[$db_column] = sanitize_text_field($data[array_search($csv_column, $headers)]);
                }
            }

            if ($update_existing && isset($row['id'])) {
                // Update the existing record if the ID matches
                $existing_record = $wpdb->get_row($wpdb->prepare("SELECT * FROM $table_name WHERE id = %d", $row['id']));
                if ($existing_record) {
                    $updated = $wpdb->update($table_name, $row, ['id' => $row['id']]);
                    if ($updated !== false) {
                        $updated_count++;
                    }
                } else {
                    $inserted = $wpdb->insert($table_name, $row);
                    if ($inserted) {
                        $added_count++;
                    }
                }
            } else {
                // Insert as new record
                $inserted = $wpdb->insert($table_name, $row);
                if ($inserted) {
                    $added_count++;
                }
            }
        }

        fclose($handle);
        unlink($file_path); // Remove the temporary file

        // Redirect back with a success message
        $message = $added_count . ' record(s) added, ' . $updated_count . ' record(s) updated.';
        wp_redirect(admin_url('admin.php?page=cat-lead&import_success=1&message=' . urlencode($message)));
        exit;
    } else {
        wp_die('File not found.');
    }
}
public static function save_remark() {
    if (!current_user_can('manage_options') || !isset($_POST['id']) || !isset($_POST['ba_remarks'])) {
        wp_send_json_error();
    }

    global $wpdb;
    $table_name = $wpdb->prefix . 'cat_lead';

    $id = intval($_POST['id']);
    $ba_remarks = sanitize_text_field($_POST['ba_remarks']);

    $updated = $wpdb->update(
        $table_name,
        ['ba_remarks' => $ba_remarks],
        ['id' => $id]
    );

    if ($updated !== false) {
        wp_send_json_success();
    } else {
        wp_send_json_error();
    }
}

}