<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Project_model extends CI_Model {

    public function __construct() {
        log_message('info', 'Project Model Initialized');
    }
        public function get_image_by_id($image_id) {
        return $this->db->get_where('extracted_images', ['id' => $image_id])->row();
    }

    public function get_additional_photo_by_id($photo_id) {
        return $this->db->get_where('additional_photos', ['id' => $photo_id])->row();
    }

    public function get_all_projects() {
        try {
            $this->db->order_by('created_at', 'DESC');
            return $this->db->get('projects')->result();
        } catch (Exception $e) {
            log_message('error', 'Project Model - get_all_projects error: ' . $e->getMessage());
            return [];
        }
    }
    public function assign_agency_to_model($pdf_id, $agency_id) {
    $data = ['agency_id' => $agency_id];
    $this->db->where('pdf_id', $pdf_id);
    return $this->db->update('extracted_data', $data);
}

public function get_agency_for_model($pdf_id) {
    $this->db->select('a.*');
    $this->db->from('extracted_data ed');
    $this->db->join('agencies a', 'a.id = ed.agency_id', 'left');
    $this->db->where('ed.pdf_id', $pdf_id);
    return $this->db->get()->row();
}
// Add this method to your Project_model.php
public function get_agency_name($agency_id) {
    if (!$agency_id) {
        return '';
    }
    
    $this->db->select('name');
    $this->db->where('id', $agency_id);
    $result = $this->db->get('agencies')->row();
    
    return $result ? $result->name : '';
}

public function get_pdf_by_id($pdf_id) {
    return $this->db->get_where('project_pdfs', ['id' => $pdf_id])->row();
}

public function get_model_data_by_pdf_id($pdf_id) {
    return $this->db->get_where('extracted_data', ['pdf_id' => $pdf_id])->row();
}
    public function get_project($project_id) {
        try {
            return $this->db->get_where('projects', ['id' => $project_id])->row();
        } catch (Exception $e) {
            log_message('error', 'Project Model - get_project error: ' . $e->getMessage());
            return null;
        }
    }

    public function get_project_data($project_id) {
        $data = [];
        
        try {
            // Get PDFs with extracted data
            $this->db->select('pp.*, ed.model_name, ed.model_height, ed.model_weight, ed.model_waist, ed.instagram_handle, ed.contact_info, ed.specialties, ed.extracted_text');
            $this->db->from('project_pdfs pp');
            $this->db->join('extracted_data ed', 'pp.id = ed.pdf_id', 'left');
            $this->db->where('pp.project_id', $project_id);
            $data['pdfs'] = $this->db->get()->result_array();
            
            // Get images for each PDF
            foreach ($data['pdfs'] as &$pdf) {
                $pdf['images'] = $this->db->get_where('extracted_images', ['pdf_id' => $pdf['id']])->result_array();
                $pdf['image_count'] = count($pdf['images']);
            }
            
            // Get additional photos
            $data['additional_photos'] = $this->db->get_where('additional_photos', ['project_id' => $project_id])->result_array();
            
        } catch (Exception $e) {
            log_message('error', 'Project Model - get_project_data error: ' . $e->getMessage());
        }
        
        return $data;
    }

    public function create_project($data) {
        try {
            return $this->db->insert('projects', $data);
        } catch (Exception $e) {
            log_message('error', 'Project Model - create_project error: ' . $e->getMessage());
            return false;
        }
    }

    public function update_project($project_id, $data) {
        $this->db->where('id', $project_id);
        return $this->db->update('projects', $data);
    }

    public function delete_project($project_id) {
        $this->db->where('id', $project_id);
        return $this->db->delete('projects');
    }

    /**
     * Get model portfolio by project_id and model_name
     * Returns model data with all images
     */
    public function get_model_portfolio($project_id, $model_name) {
        try {
            // Log for debugging
            log_message('info', 'get_model_portfolio called - project_id: ' . $project_id . ', model_name: ' . $model_name);
            
            // First check if project exists
            $project = $this->get_project($project_id);
            if (!$project) {
                log_message('error', 'Project not found: ' . $project_id);
                return null;
            }
            
            // If model_name is empty, return null
            if (empty($model_name)) {
                log_message('error', 'Model name is empty');
                return null;
            }
            
            // Decode URL-encoded model name and normalize
            $model_name = urldecode($model_name);
            $model_name = str_replace(['-', '_'], ' ', $model_name);
            $model_name = trim($model_name);
            
            if (empty($model_name)) {
                log_message('error', 'Model name is empty after normalization');
                return null;
            }
            
            log_message('info', 'Searching for model: ' . $model_name . ' in project: ' . $project_id);
            
            // Get PDF with extracted data matching project_id and model_name
            // Try exact match first (case-insensitive)
            $this->db->select('pp.*, ed.*');
            $this->db->from('project_pdfs pp');
            $this->db->join('extracted_data ed', 'pp.id = ed.pdf_id', 'left');
            $this->db->where('pp.project_id', $project_id);
            // Use custom WHERE clause for complex expression
            $normalized_model_name = $this->db->escape(strtolower($model_name));
            $this->db->where("LOWER(TRIM(ed.model_name)) = " . $normalized_model_name, NULL, FALSE);
            $query = $this->db->get();
            $pdf_data = $query->row_array();
            
            log_message('info', 'Exact match query executed. Found: ' . ($pdf_data ? 'YES' : 'NO'));
            if ($pdf_data) {
                log_message('info', 'Found PDF ID: ' . ($pdf_data['id'] ?? 'N/A'));
            }
            
            // If not found, try partial match (LIKE)
            if (!$pdf_data) {
                $this->db->select('pp.*, ed.*');
                $this->db->from('project_pdfs pp');
                $this->db->join('extracted_data ed', 'pp.id = ed.pdf_id', 'left');
                $this->db->where('pp.project_id', $project_id);
                $this->db->like('LOWER(TRIM(ed.model_name))', strtolower($model_name));
                $pdf_data = $this->db->get()->row_array();
            }
            
            // If still not found, try matching with PDF original_name (without extension)
            if (!$pdf_data) {
                $this->db->select('pp.*, ed.*');
                $this->db->from('project_pdfs pp');
                $this->db->join('extracted_data ed', 'pp.id = ed.pdf_id', 'left');
                $this->db->where('pp.project_id', $project_id);
                // Remove .pdf extension and normalize - use custom WHERE clause with = operator
                $normalized_model_name = $this->db->escape(strtolower($model_name));
                $this->db->where("LOWER(REPLACE(REPLACE(REPLACE(pp.original_name, '.pdf', ''), '-', ' '), '_', ' ')) = " . $normalized_model_name, NULL, FALSE);
                $pdf_data = $this->db->get()->row_array();
            }
            
            // If still not found, try LIKE on original_name
            if (!$pdf_data) {
                $this->db->select('pp.*, ed.*');
                $this->db->from('project_pdfs pp');
                $this->db->join('extracted_data ed', 'pp.id = ed.pdf_id', 'left');
                $this->db->where('pp.project_id', $project_id);
                $this->db->like('LOWER(pp.original_name)', strtolower($model_name));
                $pdf_data = $this->db->get()->row_array();
            }
            
            if (!$pdf_data) {
                log_message('error', 'No PDF data found for project_id: ' . $project_id . ', model_name: ' . $model_name);
                return null;
            }
            
            // Get all images for this PDF
            $images = $this->db->get_where('extracted_images', ['pdf_id' => $pdf_data['id']])->result_array();
            $pdf_data['images'] = $images;
            
            log_message('info', 'Found ' . count($images) . ' images for PDF ID: ' . $pdf_data['id']);
            
            return $pdf_data;
            
        } catch (Exception $e) {
            log_message('error', 'Project Model - get_model_portfolio error: ' . $e->getMessage());
            return null;
        }
    }
}