<?php

/**
 * Note Model
 * Handles note management with single and regular types
 */
class Note {
    
    private $db;
    
    public $id;
    public $user_id;
    public $title;
    public $note_type;
    public $reminder_date;
    public $image_path;
    public $content;
    public $created_at;
    public $updated_at;
    
    public function __construct() {
        $this->db = Database::getInstance();
    }
    
    /**
     * Create a new note
     * 
     * @param array $data
     * @return int|false Note ID on success, false on failure
     */
    public function create($data) {
        // Validate required fields
        if (empty($data['user_id']) || empty($data['title']) || empty($data['note_type']) || empty($data['reminder_date'])) {
            return false;
        }
        
        // Validate note type
        if (!in_array($data['note_type'], ['single', 'regular'])) {
            return false;
        }
        
        $sql = "INSERT INTO notes (user_id, title, note_type, reminder_date, image_path, content) 
                VALUES (?, ?, ?, ?, ?, ?)";
        
        $this->db->execute($sql, [
            $data['user_id'],
            $data['title'],
            $data['note_type'],
            $data['reminder_date'],
            $data['image_path'] ?? '',
            $data['content'] ?? ''
        ]);
        
        return $this->db->lastInsertId();
    }
    
    /**
     * Find note by ID
     * 
     * @param int $id
     * @return Note|false
     */
    public function findById($id) {
        $sql = "SELECT * FROM notes WHERE id = ? LIMIT 1";
        $note = $this->db->fetchOne($sql, [$id]);
        
        if ($note) {
            $this->fillFromArray($note);
            return $this;
        }
        
        return false;
    }
    
    /**
     * Update note
     * 
     * @param int $id
     * @param array $data
     * @return bool
     */
    public function update($id, $data) {
        $fields = [];
        $values = [];
        
        // Build update query dynamically
        if (isset($data['user_id'])) {
            $fields[] = "user_id = ?";
            $values[] = $data['user_id'];
        }
        
        if (isset($data['title'])) {
            $fields[] = "title = ?";
            $values[] = $data['title'];
        }
        
        if (isset($data['note_type'])) {
            // Validate note type
            if (!in_array($data['note_type'], ['single', 'regular'])) {
                return false;
            }
            $fields[] = "note_type = ?";
            $values[] = $data['note_type'];
        }
        
        if (isset($data['reminder_date'])) {
            $fields[] = "reminder_date = ?";
            $values[] = $data['reminder_date'];
        }
        
        if (isset($data['image_path'])) {
            $fields[] = "image_path = ?";
            $values[] = $data['image_path'];
        }
        
        if (isset($data['content'])) {
            $fields[] = "content = ?";
            $values[] = $data['content'];
        }
        
        if (empty($fields)) {
            return false;
        }
        
        $values[] = $id;
        $sql = "UPDATE notes SET " . implode(', ', $fields) . " WHERE id = ?";
        
        try {
            $this->db->execute($sql, $values);
            return true;
        } catch (Exception $e) {
            logError("Note update failed: " . $e->getMessage(), ['note_id' => $id]);
            return false;
        }
    }
    
    /**
     * Delete note
     * 
     * @param int $id
     * @return bool
     */
    public function delete($id) {
        $sql = "DELETE FROM notes WHERE id = ?";
        return $this->db->execute($sql, [$id]) > 0;
    }
    
    /**
     * Get all notes
     * 
     * @return array
     */
    public function getAll() {
        $sql = "SELECT n.*, u.username 
                FROM notes n 
                LEFT JOIN users u ON n.user_id = u.id 
                ORDER BY n.reminder_date DESC, n.created_at DESC";
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get notes by user ID
     * 
     * @param int $userId
     * @return array
     */
    public function getByUser($userId) {
        $sql = "SELECT * FROM notes WHERE user_id = ? ORDER BY reminder_date DESC";
        return $this->db->fetchAll($sql, [$userId]);
    }
    
    /**
     * Get today's notes for a user
     * 
     * @param int $userId
     * @return array
     */
    public function getTodayNotes($userId) {
        $sql = "SELECT * FROM notes 
                WHERE user_id = ? 
                AND (
                    (note_type = 'single' AND reminder_date = CURDATE())
                    OR
                    (note_type = 'regular' AND MONTH(reminder_date) = MONTH(CURDATE()) AND DAY(reminder_date) = DAY(CURDATE()))
                )
                ORDER BY title ASC";
        return $this->db->fetchAll($sql, [$userId]);
    }
    
    /**
     * Get today's reminders for a user (alias for getTodayNotes)
     * 
     * @param int $userId
     * @return array
     */
    public function getTodayReminders($userId) {
        return $this->getTodayNotes($userId);
    }
    
    /**
     * Get all today's notes (all users)
     * 
     * @return array
     */
    public function getAllTodayNotes() {
        $sql = "SELECT n.*, u.username 
                FROM notes n 
                LEFT JOIN users u ON n.user_id = u.id 
                WHERE (
                    (n.note_type = 'single' AND n.reminder_date = CURDATE())
                    OR
                    (n.note_type = 'regular' AND MONTH(n.reminder_date) = MONTH(CURDATE()) AND DAY(n.reminder_date) = DAY(CURDATE()))
                )
                ORDER BY n.title ASC";
        return $this->db->fetchAll($sql);
    }
    
    /**
     * Get upcoming notes (next N days)
     * 
     * @param int $days
     * @param int|null $userId
     * @return array
     */
    public function getUpcomingNotes($days = 7, $userId = null) {
        $sql = "SELECT n.*, u.username 
                FROM notes n
                LEFT JOIN users u ON n.user_id = u.id
                WHERE (
                    (n.note_type = 'single' AND n.reminder_date BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL ? DAY))
                    OR
                    (n.note_type = 'regular')
                )";
        
        $params = [$days];
        
        if ($userId) {
            $sql .= " AND n.user_id = ?";
            $params[] = $userId;
        }
        
        $sql .= " ORDER BY n.reminder_date ASC";
        
        return $this->db->fetchAll($sql, $params);
    }
    
    /**
     * Delete past single notes (bulk deletion)
     * 
     * @return int Number of deleted notes
     */
    public function deletePastSingleNotes() {
        $sql = "DELETE FROM notes 
                WHERE note_type = 'single' 
                AND reminder_date < CURDATE()";
        
        return $this->db->execute($sql);
    }
    
    /**
     * Count total notes
     * 
     * @param int|null $userId
     * @return int
     */
    public function count($userId = null) {
        if ($userId) {
            $sql = "SELECT COUNT(*) as count FROM notes WHERE user_id = ?";
            $result = $this->db->fetchOne($sql, [$userId]);
        } else {
            $sql = "SELECT COUNT(*) as count FROM notes";
            $result = $this->db->fetchOne($sql);
        }
        return $result['count'] ?? 0;
    }
    
    /**
     * Count notes by type
     * 
     * @param string $type 'single' or 'regular'
     * @param int|null $userId
     * @return int
     */
    public function countByType($type, $userId = null) {
        if ($userId) {
            $sql = "SELECT COUNT(*) as count FROM notes WHERE user_id = ? AND note_type = ?";
            $result = $this->db->fetchOne($sql, [$userId, $type]);
        } else {
            $sql = "SELECT COUNT(*) as count FROM notes WHERE note_type = ?";
            $result = $this->db->fetchOne($sql, [$type]);
        }
        return $result['count'] ?? 0;
    }
    
    /**
     * Fill object properties from array
     * 
     * @param array $data
     */
    private function fillFromArray($data) {
        $this->id = $data['id'] ?? null;
        $this->user_id = $data['user_id'] ?? null;
        $this->title = $data['title'] ?? null;
        $this->note_type = $data['note_type'] ?? null;
        $this->reminder_date = $data['reminder_date'] ?? null;
        $this->image_path = $data['image_path'] ?? null;
        $this->content = $data['content'] ?? null;
        $this->created_at = $data['created_at'] ?? null;
        $this->updated_at = $data['updated_at'] ?? null;
    }
    
    /**
     * Convert note to array
     * 
     * @return array
     */
    public function toArray() {
        return [
            'id' => $this->id,
            'user_id' => $this->user_id,
            'title' => $this->title,
            'note_type' => $this->note_type,
            'reminder_date' => $this->reminder_date,
            'image_path' => $this->image_path,
            'content' => $this->content,
            'created_at' => $this->created_at,
            'updated_at' => $this->updated_at
        ];
    }
    
    /**
     * Get paginated notes
     */
    public function getPaginated($limit = 10, $offset = 0) {
        $sql = "SELECT n.*, u.username 
                FROM notes n
                LEFT JOIN users u ON n.user_id = u.id
                ORDER BY n.created_at DESC 
                LIMIT ? OFFSET ?";
        return $this->db->fetchAll($sql, [$limit, $offset]);
    }
    
    /**
     * Search notes with pagination
     */
    public function searchPaginated($query, $limit = 10, $offset = 0) {
        $searchTerm = '%' . $query . '%';
        $sql = "SELECT n.*, u.username 
                FROM notes n
                LEFT JOIN users u ON n.user_id = u.id
                WHERE n.title LIKE ? OR n.content LIKE ?
                ORDER BY n.created_at DESC 
                LIMIT ? OFFSET ?";
        return $this->db->fetchAll($sql, [$searchTerm, $searchTerm, $limit, $offset]);
    }
    
    /**
     * Count total notes
     */
    public function countTotal() {
        $sql = "SELECT COUNT(*) as total FROM notes";
        $result = $this->db->fetchOne($sql);
        return $result ? (int)$result['total'] : 0;
    }
    
    /**
     * Count search results
     */
    public function countSearch($query) {
        $searchTerm = '%' . $query . '%';
        $sql = "SELECT COUNT(*) as total FROM notes 
                WHERE title LIKE ? OR content LIKE ?";
        $result = $this->db->fetchOne($sql, [$searchTerm, $searchTerm]);
        return $result ? (int)$result['total'] : 0;
    }
}
