<?php
/**
 * ============================================================================
 * classes/Post.php - Enhanced Post Management Class
 * ============================================================================
 */

namespace App;

class Post {
    private $conn;
    private $table_name = "posts";
    
    public function __construct($db) {
        $this->conn = $db;
    }
    
    /**
     * Create a new post
     */
    public function create($userId, $content, $postType = 'text', $mediaUrls = null, $visibility = 'public', $location = null) {
        $query = "INSERT INTO posts 
                  (user_id, content, post_type, media_urls, visibility, location) 
                  VALUES (:user_id, :content, :post_type, :media_urls, :visibility, :location)";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':post_type', $postType);
        $stmt->bindParam(':media_urls', $mediaUrls);
        $stmt->bindParam(':visibility', $visibility);
        $stmt->bindParam(':location', $location);
        
        if ($stmt->execute()) {
            return $this->conn->lastInsertId();
        }
        
        return false;
    }
    
    /**
     * Get comprehensive feed with intelligent sorting
     * Includes: friends, friends of friends, agricultural tips, disease alerts, 
     * market prices, buyer posts, extension worker posts
     */
    public function getEnhancedFeed($userId, $limit = 20, $offset = 0) {
        // Get user's location details for local content prioritization
        $userLocation = $this->getUserLocation($userId);
        
        $query = "
        (
            -- 1. Posts from friends (highest priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id1) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'friend' as source_type,
                10 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            INNER JOIN friendships f ON (
                (f.user_id = p.user_id AND f.friend_id = :user_id2)
                OR (f.friend_id = p.user_id AND f.user_id = :user_id3)
            )
            WHERE f.status = 'accepted'
            AND p.visibility IN ('public', 'friends')
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
        )
        
        UNION ALL
        
        (
            -- 2. Posts from friends of friends (medium-high priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id4) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'friend_of_friend' as source_type,
                7 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.user_id IN (
                SELECT DISTINCT 
                    CASE 
                        WHEN f1.user_id = f2.user_id THEN f2.friend_id
                        WHEN f1.user_id = f2.friend_id THEN f2.user_id
                        WHEN f1.friend_id = f2.user_id THEN f2.friend_id
                        ELSE f2.user_id
                    END as friend_of_friend_id
                FROM friendships f1
                INNER JOIN friendships f2 ON (
                    f1.user_id = f2.user_id OR f1.user_id = f2.friend_id OR
                    f1.friend_id = f2.user_id OR f1.friend_id = f2.friend_id
                )
                WHERE (f1.user_id = :user_id5 OR f1.friend_id = :user_id6)
                AND f1.status = 'accepted'
                AND f2.status = 'accepted'
                AND f2.user_id != :user_id7
                AND f2.friend_id != :user_id8
            )
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 5 DAY)
            LIMIT 10
        )
        
        UNION ALL
        
        (
            -- 3. Agricultural tips (high priority for farmers)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id9) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'agricultural_tip' as source_type,
                9 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.post_type = 'tip'
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
            ORDER BY p.created_at DESC
            LIMIT 3
        )
        
        UNION ALL
        
        (
            -- 4. Extension workers in user's area (high priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id10) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'extension_worker_local' as source_type,
                8 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE u.user_type = 'extension_officer'
            AND (
                u.district_id = :district_id1
                OR u.subcounty_id = :subcounty_id1
            )
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 10 DAY)
            ORDER BY p.created_at DESC
            LIMIT 5
        )
        
        UNION ALL
        
        (
            -- 5. Buyers in user's area (medium priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id11) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'buyer_local' as source_type,
                6 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE u.user_type = 'buyer'
            AND (
                u.district_id = :district_id2
                OR u.subcounty_id = :subcounty_id2
            )
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
            ORDER BY p.created_at DESC
            LIMIT 5
        )
        
        UNION ALL
        
        (
            -- 6. Popular posts from same district (medium priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id12) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'district_popular' as source_type,
                5 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE u.district_id = :district_id3
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)
            AND (
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) >= 3
                OR (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) >= 2
            )
            ORDER BY (p.likes_count + p.comments_count * 2) DESC
            LIMIT 5
        )
        
        UNION ALL
        
        (
            -- 7. Extension workers from outside (lower priority)
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id13) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'extension_worker_external' as source_type,
                4 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE u.user_type = 'extension_officer'
            AND u.district_id != :district_id4
            AND p.visibility = 'public'
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 14 DAY)
            ORDER BY p.created_at DESC
            LIMIT 3
        )
        
        UNION ALL
        
        (
            -- 8. User's own posts
            SELECT 
                p.*,
                u.full_name,
                u.profile_picture,
                u.user_type,
                u.district_id,
                u.subcounty_id,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id14) as user_liked,
                (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count,
                'own_post' as source_type,
                10 as priority_score
            FROM posts p
            INNER JOIN users u ON p.user_id = u.id
            WHERE p.user_id = :user_id15
            AND p.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
        )
        
        ORDER BY 
            is_pinned DESC,
            priority_score DESC,
            (like_count + comment_count * 2 + shares_count * 3) DESC,
            created_at DESC
        LIMIT :limit OFFSET :offset
        ";
        
        $stmt = $this->conn->prepare($query);
        
        // Bind all user_id parameters
        for ($i = 1; $i <= 15; $i++) {
            $stmt->bindValue(":user_id{$i}", $userId, \PDO::PARAM_INT);
        }
        
        // Bind location parameters
        $stmt->bindValue(':district_id1', $userLocation['district_id'], \PDO::PARAM_INT);
        $stmt->bindValue(':district_id2', $userLocation['district_id'], \PDO::PARAM_INT);
        $stmt->bindValue(':district_id3', $userLocation['district_id'], \PDO::PARAM_INT);
        $stmt->bindValue(':district_id4', $userLocation['district_id'], \PDO::PARAM_INT);
        $stmt->bindValue(':subcounty_id1', $userLocation['subcounty_id'], \PDO::PARAM_INT);
        $stmt->bindValue(':subcounty_id2', $userLocation['subcounty_id'], \PDO::PARAM_INT);
        
        // Bind pagination
        $stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int)$offset, \PDO::PARAM_INT);
        
        $stmt->execute();
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Get user's location details
     */
    private function getUserLocation($userId) {
        $query = "SELECT district_id, subcounty_id, parish_id, village_id 
                  FROM users WHERE id = :user_id";
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        
        $location = $stmt->fetch(\PDO::FETCH_ASSOC);
        return $location ?: [
            'district_id' => null,
            'subcounty_id' => null,
            'parish_id' => null,
            'village_id' => null
        ];
    }
    
    /**
     * Get original feed (fallback/simple version)
     */
    public function getFeed($userId, $limit = 20, $offset = 0) {
        $query = "SELECT 
                    p.*,
                    u.full_name,
                    u.profile_picture,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id) as user_liked,
                    (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count
                FROM posts p
                INNER JOIN users u ON p.user_id = u.id
                WHERE (
                    p.visibility = 'public'
                    OR p.user_id = :user_id2
                    OR (p.visibility = 'friends' AND EXISTS (
                        SELECT 1 FROM friendships f 
                        WHERE ((f.user_id = p.user_id AND f.friend_id = :user_id3) 
                        OR (f.friend_id = p.user_id AND f.user_id = :user_id4))
                        AND f.status = 'accepted'
                    ))
                )
                ORDER BY p.is_pinned DESC, p.created_at DESC
                LIMIT :limit OFFSET :offset";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':user_id2', $userId);
        $stmt->bindParam(':user_id3', $userId);
        $stmt->bindParam(':user_id4', $userId);
        $stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int)$offset, \PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Get user's posts
     */
    public function getUserPosts($userId, $viewerId = null, $limit = 20, $offset = 0) {
        if ($viewerId === null) {
            $viewerId = $userId;
        }
        
        $query = "SELECT 
                    p.*,
                    u.full_name,
                    u.profile_picture,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :viewer_id) as user_liked,
                    (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count
                FROM posts p
                INNER JOIN users u ON p.user_id = u.id
                WHERE p.user_id = :user_id
                AND (
                    p.visibility = 'public'
                    OR p.user_id = :viewer_id2
                    OR (p.visibility = 'friends' AND EXISTS (
                        SELECT 1 FROM friendships f 
                        WHERE ((f.user_id = p.user_id AND f.friend_id = :viewer_id3) 
                        OR (f.friend_id = p.user_id AND f.user_id = :viewer_id4))
                        AND f.status = 'accepted'
                    ))
                )
                ORDER BY p.is_pinned DESC, p.created_at DESC
                LIMIT :limit OFFSET :offset";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':viewer_id', $viewerId);
        $stmt->bindParam(':viewer_id2', $viewerId);
        $stmt->bindParam(':viewer_id3', $viewerId);
        $stmt->bindParam(':viewer_id4', $viewerId);
        $stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
        $stmt->bindValue(':offset', (int)$offset, \PDO::PARAM_INT);
        $stmt->execute();
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Get single post
     */
    public function getPost($postId, $userId) {
        $query = "SELECT 
                    p.*,
                    u.full_name,
                    u.profile_picture,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id) as like_count,
                    (SELECT COUNT(*) FROM post_likes pl WHERE pl.post_id = p.id AND pl.user_id = :user_id) as user_liked,
                    (SELECT COUNT(*) FROM post_comments pc WHERE pc.post_id = p.id) as comment_count
                FROM posts p
                INNER JOIN users u ON p.user_id = u.id
                WHERE p.id = :post_id";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        $stmt->execute();
        
        return $stmt->fetch(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Like a post
     */
    public function like($postId, $userId) {
        $query = "INSERT INTO post_likes (post_id, user_id) 
                  VALUES (:post_id, :user_id)";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        
        try {
            return $stmt->execute();
        } catch (\PDOException $e) {
            return false;
        }
    }
    
    /**
     * Unlike a post
     */
    public function unlike($postId, $userId) {
        $query = "DELETE FROM post_likes 
                  WHERE post_id = :post_id AND user_id = :user_id";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        
        return $stmt->execute();
    }
    
    /**
     * Add comment
     */
    public function addComment($postId, $userId, $content, $parentCommentId = null) {
        $query = "INSERT INTO post_comments 
                  (post_id, user_id, content, parent_comment_id) 
                  VALUES (:post_id, :user_id, :content, :parent_comment_id)";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':parent_comment_id', $parentCommentId);
        
        if ($stmt->execute()) {
            return $this->conn->lastInsertId();
        }
        
        return false;
    }
    
    /**
     * Get post comments
     */
    public function getComments($postId, $limit = 20) {
        $query = "SELECT 
                    pc.*,
                    u.full_name,
                    u.profile_picture
                FROM post_comments pc
                INNER JOIN users u ON pc.user_id = u.id
                WHERE pc.post_id = :post_id
                AND pc.parent_comment_id IS NULL
                ORDER BY pc.created_at ASC
                LIMIT :limit";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindValue(':limit', (int)$limit, \PDO::PARAM_INT);
        $stmt->execute();
        
        $comments = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        
        foreach ($comments as &$comment) {
            $comment['replies'] = $this->getCommentReplies($comment['id']);
        }
        
        return $comments;
    }
    
    /**
     * Get comment replies
     */
    private function getCommentReplies($commentId) {
        $query = "SELECT 
                    pc.*,
                    u.full_name,
                    u.profile_picture
                FROM post_comments pc
                INNER JOIN users u ON pc.user_id = u.id
                WHERE pc.parent_comment_id = :comment_id
                ORDER BY pc.created_at ASC";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':comment_id', $commentId);
        $stmt->execute();
        
        return $stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
    
    /**
     * Delete post
     */
    public function delete($postId, $userId) {
        $query = "DELETE FROM posts 
                  WHERE id = :post_id AND user_id = :user_id";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        
        return $stmt->execute();
    }
    
    /**
     * Update post
     */
    public function update($postId, $userId, $content, $visibility = null) {
        if ($visibility) {
            $query = "UPDATE posts 
                      SET content = :content, visibility = :visibility, updated_at = NOW()
                      WHERE id = :post_id AND user_id = :user_id";
            
            $stmt = $this->conn->prepare($query);
            $stmt->bindParam(':visibility', $visibility);
        } else {
            $query = "UPDATE posts 
                      SET content = :content, updated_at = NOW()
                      WHERE id = :post_id AND user_id = :user_id";
            
            $stmt = $this->conn->prepare($query);
        }
        
        $stmt->bindParam(':content', $content);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        
        return $stmt->execute();
    }
    
    /**
     * Pin/Unpin post
     */
    public function togglePin($postId, $userId) {
        $query = "UPDATE posts 
                  SET is_pinned = NOT is_pinned 
                  WHERE id = :post_id AND user_id = :user_id";
        
        $stmt = $this->conn->prepare($query);
        $stmt->bindParam(':post_id', $postId);
        $stmt->bindParam(':user_id', $userId);
        
        return $stmt->execute();
    }
}