<?php

declare(strict_types=1);

require_once __DIR__ . '/../db.php';
require_once __DIR__ . '/../ActivityLog.php';

/**
 * ProjectManager
 * 
 * Handles project CRUD operations, categories, and project-level management
 */
class ProjectManager
{
    /**
     * Get all projects for a workspace with optional filters
     */
    public static function getProjects(int $companyId, array $filters = []): array
    {
        $pdo = getPDO();
        
        $sql = 'SELECT p.*, 
                       pc.name as category_name, pc.color as category_color,
                       c.name as client_name,
                       u.name as created_by_name,
                       (SELECT COUNT(*) FROM tasks WHERE project_id = p.id) as task_count,
                       (SELECT COUNT(*) FROM tasks t 
                        JOIN task_statuses ts ON t.status_id = ts.id 
                        WHERE t.project_id = p.id AND ts.is_completed = TRUE) as completed_task_count
                FROM projects p
                LEFT JOIN project_categories pc ON p.category_id = pc.id
                LEFT JOIN clients c ON p.client_id = c.id
                LEFT JOIN users u ON p.created_by = u.id
                WHERE p.company_id = ?';
        
        $params = [$companyId];
        
        if (!empty($filters['status'])) {
            $sql .= ' AND p.status = ?';
            $params[] = $filters['status'];
        }
        
        if (!empty($filters['category_id'])) {
            $sql .= ' AND p.category_id = ?';
            $params[] = (int)$filters['category_id'];
        }
        
        if (!empty($filters['client_id'])) {
            $sql .= ' AND p.client_id = ?';
            $params[] = (int)$filters['client_id'];
        }
        
        if (!empty($filters['priority'])) {
            $sql .= ' AND p.priority = ?';
            $params[] = $filters['priority'];
        }
        
        if (!empty($filters['search'])) {
            $sql .= ' AND (p.name LIKE ? OR p.description LIKE ?)';
            $searchTerm = '%' . $filters['search'] . '%';
            $params[] = $searchTerm;
            $params[] = $searchTerm;
        }
        
        $sql .= ' ORDER BY p.updated_at DESC';
        
        $stmt = $pdo->prepare($sql);
        $stmt->execute($params);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
    
    /**
     * Get a single project by ID
     */
    public static function getProject(int $projectId, int $companyId): ?array
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            SELECT p.*, 
                   pc.name as category_name, pc.color as category_color, pc.icon as category_icon,
                   c.name as client_name, c.email as client_email,
                   u.name as created_by_name
            FROM projects p
            LEFT JOIN project_categories pc ON p.category_id = pc.id
            LEFT JOIN clients c ON p.client_id = c.id
            LEFT JOIN users u ON p.created_by = u.id
            WHERE p.id = ? AND p.company_id = ?
        ');
        
        $stmt->execute([$projectId, $companyId]);
        $project = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $project ?: null;
    }
    
    /**
     * Get project by public link token (no auth required)
     */
    public static function getProjectByPublicToken(string $token): ?array
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            SELECT p.*, 
                   pc.name as category_name, pc.color as category_color,
                   c.name as client_name,
                   co.name as company_name
            FROM projects p
            LEFT JOIN project_categories pc ON p.category_id = pc.id
            LEFT JOIN clients c ON p.client_id = c.id
            LEFT JOIN companies co ON p.company_id = co.id
            WHERE p.public_link_token = ? AND p.public_link_enabled = TRUE
        ');
        
        $stmt->execute([$token]);
        $project = $stmt->fetch(PDO::FETCH_ASSOC);
        
        return $project ?: null;
    }
    
    /**
     * Create a new project
     */
    public static function createProject(int $companyId, int $userId, array $data): int
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            INSERT INTO projects (
                company_id, category_id, client_id, name, description,
                status, priority, start_date, due_date, created_by
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ');
        
        $stmt->execute([
            $companyId,
            $data['category_id'] ?? null,
            $data['client_id'] ?? null,
            $data['name'],
            $data['description'] ?? null,
            $data['status'] ?? 'planning',
            $data['priority'] ?? 'medium',
            $data['start_date'] ?? null,
            $data['due_date'] ?? null,
            $userId
        ]);
        
        $projectId = (int)$pdo->lastInsertId();
        
        // Add creator as project owner
        self::addProjectMember($projectId, $userId, 'owner', $userId);
        
        // Log activity
        ActivityLog::log($companyId, $userId, 'project_created', 'project', $projectId, $data['name']);
        
        return $projectId;
    }
    
    /**
     * Update a project
     */
    public static function updateProject(int $projectId, int $companyId, int $userId, array $data): bool
    {
        $pdo = getPDO();
        
        $fields = [];
        $params = [];
        
        if (isset($data['name'])) {
            $fields[] = 'name = ?';
            $params[] = $data['name'];
        }
        
        if (isset($data['description'])) {
            $fields[] = 'description = ?';
            $params[] = $data['description'];
        }
        
        if (isset($data['category_id'])) {
            $fields[] = 'category_id = ?';
            $params[] = $data['category_id'] ?: null;
        }
        
        if (isset($data['client_id'])) {
            $fields[] = 'client_id = ?';
            $params[] = $data['client_id'] ?: null;
        }
        
        if (isset($data['status'])) {
            $fields[] = 'status = ?';
            $params[] = $data['status'];
            
            if ($data['status'] === 'completed') {
                $fields[] = 'completed_at = NOW()';
            }
        }
        
        if (isset($data['priority'])) {
            $fields[] = 'priority = ?';
            $params[] = $data['priority'];
        }
        
        if (isset($data['start_date'])) {
            $fields[] = 'start_date = ?';
            $params[] = $data['start_date'] ?: null;
        }
        
        if (isset($data['due_date'])) {
            $fields[] = 'due_date = ?';
            $params[] = $data['due_date'] ?: null;
        }
        
        if (empty($fields)) {
            return false;
        }
        
        $params[] = $projectId;
        $params[] = $companyId;
        
        $sql = 'UPDATE projects SET ' . implode(', ', $fields) . ' WHERE id = ? AND company_id = ?';
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($params);
        
        if ($result) {
            $project = self::getProject($projectId, $companyId);
            ActivityLog::log($companyId, $userId, 'project_updated', 'project', $projectId, $project['name'] ?? '');
        }
        
        return $result;
    }
    
    /**
     * Delete a project
     */
    public static function deleteProject(int $projectId, int $companyId, int $userId): bool
    {
        $pdo = getPDO();
        
        $project = self::getProject($projectId, $companyId);
        if (!$project) {
            return false;
        }
        
        $stmt = $pdo->prepare('DELETE FROM projects WHERE id = ? AND company_id = ?');
        $result = $stmt->execute([$projectId, $companyId]);
        
        if ($result) {
            ActivityLog::log($companyId, $userId, 'project_deleted', 'project', $projectId, $project['name']);
        }
        
        return $result;
    }
    
    /**
     * Generate and enable public link for project
     */
    public static function enablePublicLink(int $projectId, int $companyId): ?string
    {
        $pdo = getPDO();
        
        $token = bin2hex(random_bytes(32));
        
        $stmt = $pdo->prepare('
            UPDATE projects 
            SET public_link_token = ?, public_link_enabled = TRUE 
            WHERE id = ? AND company_id = ?
        ');
        
        $stmt->execute([$token, $projectId, $companyId]);
        
        return $token;
    }
    
    /**
     * Disable public link for project
     */
    public static function disablePublicLink(int $projectId, int $companyId): bool
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            UPDATE projects 
            SET public_link_enabled = FALSE 
            WHERE id = ? AND company_id = ?
        ');
        
        return $stmt->execute([$projectId, $companyId]);
    }
    
    /**
     * Get project categories for a workspace
     */
    public static function getCategories(int $companyId): array
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            SELECT pc.*, 
                   COUNT(p.id) as project_count
            FROM project_categories pc
            LEFT JOIN projects p ON pc.id = p.category_id
            WHERE pc.company_id = ?
            GROUP BY pc.id
            ORDER BY pc.sort_order ASC, pc.name ASC
        ');
        
        $stmt->execute([$companyId]);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
    
    /**
     * Create a project category
     */
    public static function createCategory(int $companyId, array $data): int
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            INSERT INTO project_categories (company_id, name, color, icon, sort_order)
            VALUES (?, ?, ?, ?, ?)
        ');
        
        $stmt->execute([
            $companyId,
            $data['name'],
            $data['color'] ?? '#3B82F6',
            $data['icon'] ?? 'folder',
            $data['sort_order'] ?? 0
        ]);
        
        return (int)$pdo->lastInsertId();
    }
    
    /**
     * Add a member to a project
     */
    public static function addProjectMember(int $projectId, int $userId, string $role, int $addedBy): bool
    {
        $pdo = getPDO();
        
        try {
            $stmt = $pdo->prepare('
                INSERT INTO project_members (project_id, user_id, role, added_by)
                VALUES (?, ?, ?, ?)
                ON DUPLICATE KEY UPDATE role = VALUES(role)
            ');
            
            return $stmt->execute([$projectId, $userId, $role, $addedBy]);
        } catch (PDOException $e) {
            return false;
        }
    }
    
    /**
     * Get project members
     */
    public static function getProjectMembers(int $projectId): array
    {
        $pdo = getPDO();
        
        $stmt = $pdo->prepare('
            SELECT pm.*, u.name, u.email
            FROM project_members pm
            JOIN users u ON pm.user_id = u.id
            WHERE pm.project_id = ?
            ORDER BY pm.role ASC, u.name ASC
        ');
        
        $stmt->execute([$projectId]);
        
        return $stmt->fetchAll(PDO::FETCH_ASSOC) ?: [];
    }
    
    /**
     * Get project statistics for dashboard
     */
    public static function getProjectStats(int $companyId): array
    {
        $pdo = getPDO();
        
        $stats = [];
        
        // Total projects
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM projects WHERE company_id = ?');
        $stmt->execute([$companyId]);
        $stats['total'] = (int)$stmt->fetchColumn();
        
        // Active projects
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM projects WHERE company_id = ? AND status = ?');
        $stmt->execute([$companyId, 'active']);
        $stats['active'] = (int)$stmt->fetchColumn();
        
        // Completed projects
        $stmt = $pdo->prepare('SELECT COUNT(*) FROM projects WHERE company_id = ? AND status = ?');
        $stmt->execute([$companyId, 'completed']);
        $stats['completed'] = (int)$stmt->fetchColumn();
        
        // Overdue projects
        $stmt = $pdo->prepare('
            SELECT COUNT(*) FROM projects 
            WHERE company_id = ? AND status NOT IN (?, ?) AND due_date < CURDATE()
        ');
        $stmt->execute([$companyId, 'completed', 'archived']);
        $stats['overdue'] = (int)$stmt->fetchColumn();
        
        return $stats;
    }
}
