<?php

namespace App\Models;

use CodeIgniter\Model;

class ListingModel extends Model
{
    protected $table      = 'property';
    protected $primaryKey = 'id';

    protected $useAutoIncrement = true;
    protected $returnType     = 'array';
    protected $useSoftDeletes = false;

    protected $allowedFields = ['gps', 'lat', 'lng', 'date', 'date_modified', 'address', 
                                'repository_id', 'search_values', 'is_visible', 
                                'image_filename', 'image_repository', 'is_activated', 'approval_status'];

    protected $orderBy = 'id DESC';

    // Dates
    protected $useTimestamps = false;
    protected $dateFormat    = 'datetime';
    protected $createdField  = 'created_at';
    protected $updatedField  = 'updated_at';
    protected $deletedField  = 'deleted_at';

    // Validation
    protected $validationRules      = [];
    protected $validationMessages   = [];
    protected $skipValidation       = true;
    protected $cleanValidationRules = true;

    // Callbacks
    protected $allowCallbacks = true;
    protected $beforeInsert   = [];
    protected $afterInsert    = [];
    protected $beforeUpdate   = [];
    protected $afterUpdate    = [];
    protected $beforeFind     = [];
    protected $afterFind      = [];
    protected $beforeDelete   = [];
    protected $afterDelete    = [];

    public function findAll($limit = 0, $offset = 0, $user_check = FALSE)
    {
        $this->resetQuery(); // Query'yi temizle
        
        $this->select('property.*, property_lang.*, property_user.user_id');
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        
        // Only approved properties
        $this->where('property.approval_status', 'approved');

        $results = parent::findAll($limit, $offset);
      
        return $results;
    }
    
    public function getPropertyById($id)
    {
        $this->resetQuery(); // Query'yi temizle
        
        $this->select('property.*, property_lang.*, property_user.user_id');
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        $this->where('property.id', $id);
        $this->where('property_lang.language_id', getenv('LANGUAGE_ID'));
        
        // Only approved properties
        $this->where('property.approval_status', 'approved');

        $result = $this->first();
        
        return $result;
    }

    public function countAllResults(bool $reset = true, bool $test = false)
    {
        $this->resetQuery(); // Query'yi temizle
        
        $this->select('property.*, property_lang.*, property_user.user_id');
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        
        // Only count approved properties
        $this->where('property.approval_status', 'approved');

        $results = parent::countAllResults($reset, $test);
        
        return $results;
    }

    /**
     * Basic pagination method (backward compatibility)
     */
    public function get_pagination($limit, $offset, $where = array(), $user_check = FALSE, $user_id=NULL, $show_other_agents_litings = FALSE)
    {
        $this->resetQuery(); // Query'yi temizle
        
        // Favorites join
        if(!empty($user_id) && array_key_exists('favorites.id > 0', $where))
        {
            unset($where['favorites.id > 0']);
            $this->join('favorites', 'property.id = favorites.property_id');
        }
        
        // Hidden items join
        if(!empty($user_id) && array_key_exists('hiddens.id > 0', $where))
        {
            unset($where['hiddens.id > 0']);
            $this->join('hiddens', 'property.id = hiddens.property_id');
        }
        
        if($user_check && $user_id !== NULL)
        {
            $where['property_user.user_id'] = $user_id;
        }
        
        // Always include approval status check
        $where['property.approval_status'] = 'approved';
        
        if(count($where) > 0)
            $this->where($where);
        
        $results = $this->findAll($limit, $offset, $user_check);
        return $results;
    }

public function get_pagination_with_analytics($limit, $offset, $where = array(), $user_check = FALSE, $user_id=NULL, $show_other_agents_litings = FALSE, $order_by = 'property.id DESC')
{
    // Query builder'ı sıfırla
    $this->resetQuery();
    
    // Analytics join gerekli mi kontrol et
    $needs_analytics_join = $this->needsAnalyticsJoin($order_by);
    
    if(getenv('CI_ENVIRONMENT') == 'development') {
        log_message('info', 'Analytics join needed: ' . ($needs_analytics_join ? 'Yes' : 'No'));
        log_message('info', 'Order by: ' . $order_by);
    }
    
    // FIXED: Query builder'ı manuel olarak oluştur
    $builder = $this->db->table('property');
    
    if($needs_analytics_join) {
        $builder->select('
            property.id as property_id,
            property.*,
            property_lang.*,
            property_user.user_id,
            COALESCE(ps.total_views, 0) as analytics_total_views,
            COALESCE(ps.unique_views, 0) as analytics_unique_views,
            COALESCE(ps.total_favorites, 0) as analytics_total_favorites,
            COALESCE(ps.total_hidden, 0) as analytics_total_hidden,
            COALESCE(ps.total_enquiries, 0) as analytics_total_enquiries,
            COALESCE(ps.avg_view_duration, 0) as analytics_avg_view_duration
        ');
        
        $builder->join('property_lang', 'property.id = property_lang.property_id');
        $builder->join('property_user', 'property.id = property_user.property_id', 'left');
        $builder->join('property_statistics ps', 'property.id = ps.property_id', 'left');
    } else {
        $builder->select('property.id as property_id, property.*, property_lang.*, property_user.user_id');
        $builder->join('property_lang', 'property.id = property_lang.property_id');
        $builder->join('property_user', 'property.id = property_user.property_id', 'left');
    }
    
    // User-specific joins
    if(!empty($user_id) && array_key_exists('favorites.id > 0', $where))
    {
        unset($where['favorites.id > 0']);
        $builder->join('favorites', 'property.id = favorites.property_id');
    }
    
    if(!empty($user_id) && array_key_exists('hiddens.id > 0', $where))
    {
        unset($where['hiddens.id > 0']);
        $builder->join('hiddens', 'property.id = hiddens.property_id');
    }
    
    // Base conditions
    $builder->where('property_lang.language_id', getenv('LANGUAGE_ID'));
    $builder->where('property.is_activated', '1');
    $builder->where('property.approval_status', 'approved');
    
    // User check
    if($user_check && $user_id !== NULL)
    {
        $builder->where('property_user.user_id', $user_id);
    }
    
    // Additional where conditions
    if(!empty($where)) {
        foreach($where as $key => $value) {
            if($value === NULL) {
                $builder->where($key);
            } else {
                $builder->where($key, $value);
            }
        }
    }
    
    // Apply ordering
    if(!empty($order_by)) {
        $builder->orderBy($order_by);
    }
    
    // Apply pagination
    if($limit > 0) {
        $builder->limit($limit, $offset);
    }
    
    try {
        $results = $builder->get()->getResultArray();
        
        // Debug
        if(getenv('CI_ENVIRONMENT') == 'development') {
            log_message('info', 'Final SQL Query: ' . $builder->getCompiledSelect());
            log_message('info', 'Results count: ' . count($results));
            
            if($needs_analytics_join && !empty($results)) {
                $sample_data = [];
                foreach(array_slice($results, 0, 3) as $result) {
                    $sample_data[] = 'ID:' . $result['property_id'] . ' Favorites:' . ($result['analytics_total_favorites'] ?? 'NULL');
                }
                log_message('info', 'Sample analytics data: ' . implode(', ', $sample_data));
            }
        }
        
        return $results;
        
    } catch (\Exception $e) {
        log_message('error', 'Query error: ' . $e->getMessage());
        throw $e;
    }
}

    /**
     * FIXED: Reset query method to prevent join conflicts
     */
    private function resetQuery()
    {
        // CodeIgniter 4'te query'yi temizleme
        $this->builder()->resetQuery();
        return $this;
    }

    /**
     * Determine if analytics join is needed based on order_by clause
     */
    private function needsAnalyticsJoin($order_by)
    {
        $analytics_keywords = [
            'ps.',
            'COALESCE(ps.',
            'analytics_',
            'total_views',
            'unique_views',
            'total_favorites',
            'total_hidden',
            'total_enquiries',
            'avg_view_duration'
        ];
        
        foreach($analytics_keywords as $keyword) {
            if(strpos($order_by, $keyword) !== false) {
                return true;
            }
        }
        
        return false;
    }

    /**
     * SIMPLE METHOD: Basic listing fetch without complex joins
     */
    public function getBasicListings($limit = 20, $offset = 0, $where = array(), $order_by = 'property.id DESC')
    {
        $this->resetQuery();
        
        $this->select('
            property.id as property_id,
            property.*,
            property_lang.*,
            property_user.user_id
        ');
        
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        
        // Base conditions
        $this->where('property_lang.language_id', getenv('LANGUAGE_ID'));
        $this->where('property.is_activated', '1');
        $this->where('property.approval_status', 'approved');
        
        // Additional where conditions
        if(!empty($where)) {
            foreach($where as $key => $value) {
                if($value === NULL) {
                    $this->where($key);
                } else {
                    $this->where($key, $value);
                }
            }
        }
        
        // Ordering
        if(!empty($order_by)) {
            $this->orderBy($order_by);
        }
        
        // Pagination
        if($limit > 0) {
            $this->limit($limit, $offset);
        }
        
        return $this->findAll();
    }

    /**
     * Advanced search method with full filter support
     */
    public function searchListings($params = array())
    {
        $this->resetQuery();
        
        $this->select('property.id as property_id, property.*, property_lang.*, property_user.user_id');
        
        // Standard joins
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        
        // Base conditions
        $this->where('property_lang.language_id', getenv('LANGUAGE_ID'));
        $this->where('property.is_activated', '1');
        $this->where('property.approval_status', 'approved');
        
        // Text search
        if(!empty($params['search'])) {
            $this->where('property_lang.search_values LIKE', '%'.$params['search'].'%');
        }
        
        // Location filter
        if(!empty($params['location_ids'])) {
            $location_conditions = array();
            foreach($params['location_ids'] as $location_id) {
                $location_conditions[] = 'property_lang.json_object LIKE \'%location_'.$location_id.'%\'';
            }
            if(!empty($location_conditions)) {
                $this->where('('.implode(' OR ', $location_conditions).')');
            }
        }
        
        // Price range
        if(!empty($params['price_min'])) {
            $this->where('property_lang.field_36_int >=', intval($params['price_min']));
        }
        if(!empty($params['price_max'])) {
            $this->where('property_lang.field_36_int <=', intval($params['price_max']));
        }
        
        // Sorting
        if(!empty($params['sort'])) {
            $this->orderBy($params['sort']);
        } else {
            $this->orderBy('property.id DESC');
        }
        
        // Pagination
        $limit = isset($params['limit']) ? intval($params['limit']) : 20;
        $offset = isset($params['offset']) ? intval($params['offset']) : 0;
        
        if($limit > 0) {
            $this->limit($limit, $offset);
        }
        
        return $this->findAll();
    }

    /**
     * Get listings with analytics data
     */
    public function getListingsWithAnalytics($limit = 20, $offset = 0, $where = array(), $order_by = 'property.id DESC')
    {
        $this->resetQuery();
        
        // Full select with analytics
        $this->select('
            property.id as property_id,
            property.*,
            property_lang.*,
            property_user.user_id,
            ps.total_views,
            ps.unique_views,
            ps.total_favorites,
            ps.total_hidden,
            ps.total_enquiries,
            ps.avg_view_duration
        ');
        
        // Joins
        $this->join('property_lang', 'property.id = property_lang.property_id');
        $this->join('property_user', 'property.id = property_user.property_id', 'left');
        $this->join('property_statistics ps', 'property.id = ps.property_id', 'left');
        
        // Base conditions
        $this->where('property_lang.language_id', getenv('LANGUAGE_ID'));
        $this->where('property.is_activated', '1');
        $this->where('property.approval_status', 'approved');
        
        // Additional conditions
        if(!empty($where)) {
            foreach($where as $key => $value) {
                if($value === NULL) {
                    $this->where($key);
                } else {
                    $this->where($key, $value);
                }
            }
        }
        
        // Ordering
        if(!empty($order_by)) {
            $this->orderBy($order_by);
        }
        
        // Pagination
        if($limit > 0) {
            $this->limit($limit, $offset);
        }
        
        return $this->findAll();
    }

    /**
     * COMPLETE DELETE METHOD
     */
    public function delete_item($id) {
        // Get listing data
        $query = $this->getWhere(['id' => $id], 1);
        $listings = $query->getResultArray();

        if(!isset($listings[0]))
        {
            return FALSE;
        }

        $listing = $listings[0];

        // Delete related records
        $builder = $this->db->table('property_user');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('property_lang');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('property_value');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('favorites');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('hiddens');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('property_statistics');
        $builder->where('property_id', $id);
        $builder->delete();

        $builder = $this->db->table('enquires');
        $builder->where('property_id', $id);
        $builder->delete();

        // Delete repository and files
        if(!empty($listing['repository_id']))
        {
            $builder = $this->db->table('file');
            $query   = $builder->getWhere(['repository_id' => $listing['repository_id']]);
            $files   = $query->getResultArray();
    
            foreach($files as $file)
            {
                $file_name = $file['filename'];
                @unlink(getenv('FILES_DIRECTORY').'' . $file_name);
                @unlink(getenv('FILES_DIRECTORY').'thumbnail/' . $file_name);
            }

            $builder = $this->db->table('file');
            $builder->where('repository_id', $listing['repository_id']);
            $builder->delete();

            $builder = $this->db->table('repository');
            $builder->where('id', $listing['repository_id']);
            $builder->delete();
        }

        // Finally delete the main property record
        $builder = $this->db->table('property');
        $builder->where('id', $id);
        return $builder->delete();
    }
}