<?php
/**
 * Contact List Model for database operations.
 *
 * @package BoostBot
 * @since   1.0.0
 */

// Prevent direct access
// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared
if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

/**
 * Class BoostBot_Contact_List_Model
 *
 * Handles CRUD operations for contact lists.
 *
 * @since 1.0.0
 */
class BoostBot_Contact_List_Model extends BoostBot_Base_Model {

    /**
     * Constructor.
     */
    public function __construct() {
        parent::__construct();
        $this->table_name = self::sanitize_identifier( $this->wpdb->prefix . 'boostbot_contact_lists' );
        $this->fillable   = array(
            'name',
            'description',
            'is_primary',
        );
        $this->column_types = array(
            'id'         => 'bigint',
            'is_primary' => 'tinyint',
        );
    }

    /**
     * Get the primary contact list.
     *
     * @return object|null The primary list or null.
     */
    public function get_primary() {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $this->wpdb->get_row(
            "SELECT * FROM {$this->table_name} WHERE is_primary = 1 LIMIT 1" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );
    }

    /**
     * Set a contact list as primary.
     *
     * @param int $id The list ID.
     * @return bool True on success.
     */
    public function set_primary( $id ) {
        // Remove current primary
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $this->wpdb->update(
            $this->table_name,
            array( 'is_primary' => 0 ),
            array( 'is_primary' => 1 ),
            array( '%d' ),
            array( '%d' )
        );

        // Set new primary
        return $this->update( $id, array( 'is_primary' => 1 ) );
    }

    /**
     * Get a list with its contacts.
     *
     * @param int $id The list ID.
     * @return object|null The list with contacts or null.
     */
    public function get_with_contacts( $id ) {
        $list = $this->get( $id );

        if ( ! $list ) {
            return null;
        }

        $contact_model   = new BoostBot_Contact_Model();
        $list->contacts  = $contact_model->get_by_list( $id );
        $list->contacts_count = count( $list->contacts );

        return $list;
    }

    /**
     * Get all lists with contact counts.
     *
     * @param array $args Optional. Query arguments.
     * @return array Array of lists with contact counts.
     */
    public function get_all_with_counts( $args = array() ) {
        $lists = $this->get_all( $args );

        $contact_model = new BoostBot_Contact_Model();

        foreach ( $lists as &$list ) {
            $list->contacts_count = $contact_model->count( array( 'list_id' => $list->id ) );
        }

        return $lists;
    }

    /**
     * Import contacts from CSV file.
     *
     * @param int    $list_id   The list ID.
     * @param string $file_path The CSV file path.
     * @return int|WP_Error Number of imported contacts or error.
     */
    public function import_contacts( $list_id, $file_path ) {
        // Verify file exists and is readable
        if ( ! file_exists( $file_path ) || ! is_readable( $file_path ) ) {
            return new WP_Error( 'file_error', __( 'Cannot read the file.', 'boostbot' ) );
        }

        // Read entire file content
        $file_content = file_get_contents( $file_path ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_get_contents_file_get_contents
        if ( false === $file_content ) {
            return new WP_Error( 'file_read_error', __( 'Cannot read the file.', 'boostbot' ) );
        }

        return $this->import_contacts_from_content( $list_id, $file_content );
    }

    /**
     * Import contacts from CSV content string.
     *
     * @param int    $list_id The list ID.
     * @param string $content The CSV content.
     * @return int|WP_Error Number of imported contacts or error.
     */
    public function import_contacts_from_content( $list_id, $content ) {
        // Verify list exists
        $list = $this->get( $list_id );
        if ( ! $list ) {
            return new WP_Error( 'list_not_found', __( 'Contact list not found.', 'boostbot' ) );
        }

        $contact_model = new BoostBot_Contact_Model();
        $imported      = 0;
        $errors        = array();

        // Detect and convert encoding to UTF-8
        $file_content = $this->convert_to_utf8( $content );

        if ( '' === trim( $file_content ) ) {
            return new WP_Error( 'empty_csv', __( 'CSV file is empty.', 'boostbot' ) );
        }

        // Use fgetcsv via memory stream to correctly handle quoted fields with embedded newlines
        $handle = fopen( 'php://temp', 'r+' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fopen
        fwrite( $handle, $file_content ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fwrite
        rewind( $handle );

        // Parse header row
        $headers = fgetcsv( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fgetcsv

        if ( ! $headers || empty( $headers ) ) {
            fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
            return new WP_Error( 'invalid_csv', __( 'Invalid CSV file - no headers found.', 'boostbot' ) );
        }

        // Remove BOM if present
        if ( ! empty( $headers[0] ) ) {
            $headers[0] = preg_replace( '/^\x{FEFF}/u', '', $headers[0] );
            $headers[0] = preg_replace( '/^[\xEF\xBB\xBF]+/', '', $headers[0] );
        }

        // Normalize headers
        $headers = array_map( function( $h ) {
            return mb_strtolower( trim( $h ), 'UTF-8' );
        }, $headers );

        // Map possible header names to our fields
        $header_map = array(
            'name'         => array( 'name', 'שם', 'full_name', 'fullname' ),
            'title'        => array( 'title', 'תפקיד', 'job_title', 'position' ),
            'department'   => array( 'department', 'מחלקה', 'dept' ),
            'phone'        => array( 'phone', 'טלפון', 'telephone', 'tel', 'mobile' ),
            'email'        => array( 'email', 'מייל', 'e-mail', 'mail' ),
            'whatsapp'     => array( 'whatsapp', 'וואטסאפ', 'whats_app' ),
            'description'  => array( 'description', 'תיאור', 'notes', 'הערות' ),
        );

        // Find column indices
        $column_indices = array();
        foreach ( $header_map as $field => $possible_names ) {
            foreach ( $headers as $index => $header ) {
                if ( in_array( $header, $possible_names, true ) ) {
                    $column_indices[ $field ] = $index;
                    break;
                }
            }
        }

        // Must have at least name column
        if ( ! isset( $column_indices['name'] ) ) {
            fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose
            return new WP_Error( 'no_name_column', __( 'CSV must have a name column. Found headers: ', 'boostbot' ) . implode( ', ', $headers ) );
        }

        // Read data rows
        $row_num = 1;
        while ( ( $row = fgetcsv( $handle ) ) !== false ) { // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fgetcsv
            $row_num++;

            if ( empty( $row ) || ( count( $row ) === 1 && empty( trim( $row[0] ) ) ) ) {
                continue;
            }

            // Build contact data
            $data = array( 'list_id' => $list_id );

            foreach ( $column_indices as $field => $index ) {
                if ( isset( $row[ $index ] ) && '' !== trim( $row[ $index ] ) ) {
                    $data[ $field ] = trim( $row[ $index ] );
                }
            }

            // Skip if no name
            if ( empty( $data['name'] ) ) {
                /* translators: %d: CSV row number */
                $errors[] = sprintf( __( 'Row %d: Missing name, skipped.', 'boostbot' ), $row_num );
                continue;
            }

            // Create contact
            $result = $contact_model->create( $data );

            if ( $result ) {
                $imported++;
            } else {
                /* translators: %d: CSV row number */
                $errors[] = sprintf( __( 'Row %d: Failed to create contact.', 'boostbot' ), $row_num );
            }
        }

        fclose( $handle ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_fclose

        // Log import
        if ( class_exists( 'BoostBot_Logger' ) ) {
            BoostBot_Logger::info( 'Contacts imported', array(
                'list_id'  => $list_id,
                'imported' => $imported,
                'errors'   => count( $errors ),
            ) );
        }

        return $imported;
    }

    /**
     * Get lists for dropdown selection.
     *
     * @return array Array of lists with id, name, is_primary.
     */
    public function get_for_dropdown() {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $this->wpdb->get_results(
            "SELECT id, name, is_primary FROM {$this->table_name} ORDER BY is_primary DESC, name ASC" // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        );
    }

    /**
     * Validate list data.
     *
     * @param array    $data The data to validate.
     * @param int|null $id   Optional. The record ID for updates.
     * @return true|WP_Error True if valid, WP_Error otherwise.
     */
    public function validate( $data, $id = null ) {
        $errors = new WP_Error();

        // Name is required
        if ( empty( $data['name'] ) ) {
            $errors->add( 'name_required', __( 'Contact list name is required.', 'boostbot' ) );
        }

        if ( $errors->has_errors() ) {
            return $errors;
        }

        return true;
    }

    /**
     * Sanitize list data.
     *
     * @param array $data The data to sanitize.
     * @return array The sanitized data.
     */
    public function sanitize( $data ) {
        $sanitized = array();

        if ( isset( $data['name'] ) ) {
            $sanitized['name'] = sanitize_text_field( $data['name'] );
        }

        if ( isset( $data['description'] ) ) {
            $sanitized['description'] = sanitize_textarea_field( $data['description'] );
        }

        if ( isset( $data['is_primary'] ) ) {
            $sanitized['is_primary'] = absint( $data['is_primary'] ) ? 1 : 0;
        }

        return $sanitized;
    }

    /**
     * Create a new list with primary handling.
     *
     * @param array $data The data to insert.
     * @return int|false The new record ID or false on failure.
     */
    public function create( $data ) {
        // If setting as primary, unset other primaries first
        if ( ! empty( $data['is_primary'] ) ) {
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $this->wpdb->update(
                $this->table_name,
                array( 'is_primary' => 0 ),
                array( 'is_primary' => 1 ),
                array( '%d' ),
                array( '%d' )
            );
        }

        return parent::create( $data );
    }

    /**
     * Delete a list and all its contacts.
     *
     * @param int $id The list ID.
     * @return bool True on success.
     */
    public function delete( $id ) {
        // Delete all contacts in this list first
        $contact_model = new BoostBot_Contact_Model();
        $contact_model->delete_by_list( $id );

        return parent::delete( $id );
    }

    /**
     * Get a writable temp directory.
     *
     * @return string Path to temp directory.
     */
    private function get_temp_dir() {
        // Try WordPress upload directory first (most reliable)
        $upload_dir = wp_upload_dir();
        $temp_dir = $upload_dir['basedir'] . '/boostbot-temp';
        
        if ( ! file_exists( $temp_dir ) ) {
            wp_mkdir_p( $temp_dir );
            // Add .htaccess to prevent direct access
            @file_put_contents( $temp_dir . '/.htaccess', 'deny from all' ); // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_file_put_contents
        }
        
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_is_writable -- Checking directory writability for CSV export
        if ( is_writable( $temp_dir ) ) {
            return $temp_dir;
        }
        
        // Fallback to system temp
        $sys_temp = sys_get_temp_dir();
        // phpcs:ignore WordPress.WP.AlternativeFunctions.file_system_operations_is_writable -- Checking directory writability for CSV export
        if ( is_writable( $sys_temp ) ) {
            return $sys_temp;
        }
        
        // Last resort - use upload dir directly
        return $upload_dir['basedir'];
    }

    /**
     * Convert string to UTF-8 encoding.
     *
     * @param string $content The content to convert.
     * @return string UTF-8 encoded content.
     */
    private function convert_to_utf8( $content ) {
        // Remove BOM if present
        $bom = pack( 'H*', 'EFBBBF' );
        $content = preg_replace( "/^$bom/", '', $content );
        $content = preg_replace( '/^\xFE\xFF/', '', $content );
        $content = preg_replace( '/^\xFF\xFE/', '', $content );

        // If already valid UTF-8, return as is
        if ( mb_check_encoding( $content, 'UTF-8' ) ) {
            return $content;
        }

        // Get list of supported encodings
        $supported = mb_list_encodings();
        
        // Try common encodings (only if supported)
        $try_encodings = array( 'ISO-8859-8', 'ISO-8859-1', 'UTF-16LE', 'UTF-16BE', 'ASCII' );
        
        foreach ( $try_encodings as $encoding ) {
            if ( in_array( $encoding, $supported, true ) ) {
                $converted = @mb_convert_encoding( $content, 'UTF-8', $encoding );
                if ( $converted && mb_check_encoding( $converted, 'UTF-8' ) ) {
                    return $converted;
                }
            }
        }

        // Fallback: try auto-detection with only supported encodings
        $detect_list = array_intersect( array( 'UTF-8', 'ISO-8859-8', 'ISO-8859-1', 'ASCII' ), $supported );
        $detected = mb_detect_encoding( $content, $detect_list, true );
        
        if ( $detected && 'UTF-8' !== $detected ) {
            return @mb_convert_encoding( $content, 'UTF-8', $detected );
        }

        return $content;
    }
}


