<?php
declare(strict_types=1);

class ProjectController
{
    public static function index(): void
    {
        Auth::requireLogin();
        $db     = Database::getInstance();
        $userId = Auth::id();
        $status = Helper::get('status', 'active');

        if (Auth::isAdmin()) {
            $projects = $db->fetchAll(
                'SELECT p.*, u.username as owner_name,
                        (SELECT COUNT(*) FROM tasks WHERE project_id = p.id) as task_count,
                        (SELECT COUNT(*) FROM project_members WHERE project_id = p.id) as member_count
                 FROM projects p JOIN users u ON u.id = p.owner_id
                 WHERE p.status = ? ORDER BY p.name',
                [$status]
            );
        } else {
            $projects = $db->fetchAll(
                'SELECT p.*, u.username as owner_name, pm.role as my_role,
                        (SELECT COUNT(*) FROM tasks WHERE project_id = p.id) as task_count,
                        (SELECT COUNT(*) FROM project_members WHERE project_id = p.id) as member_count
                 FROM projects p
                 JOIN project_members pm ON pm.project_id = p.id AND pm.user_id = ?
                 JOIN users u ON u.id = p.owner_id
                 WHERE p.status = ? ORDER BY p.name',
                [$userId, $status]
            );
        }

        view('projects/index', compact('projects', 'status'));
    }

    public static function create(): void
    {
        Auth::requireLogin();
        $errors = [];

        if (Helper::isPost()) {
            Helper::verifyCsrf();
            $name  = trim(Helper::post('name'));
            $desc  = trim(Helper::post('description'));
            $color = Helper::color((string)Helper::post('color', '#4f46e5'), '#4f46e5');
            $userPref = Auth::user()['preferred_view'] ?? 'kanban';
            $view  = Helper::post('default_view', $userPref);

            if (empty($name)) $errors[] = t('name_required');

            if (empty($errors)) {
                $db = Database::getInstance();
                $db->beginTransaction();
                try {
                    $projectId = $db->insert(
                        'INSERT INTO projects (name, description, owner_id, color, default_view) VALUES (?,?,?,?,?)',
                        [$name, $desc, Auth::id(), $color, $view]
                    );

                    // Add creator as owner
                    $db->execute(
                        'INSERT INTO project_members (project_id, user_id, role) VALUES (?,?,?)',
                        [$projectId, Auth::id(), 'owner']
                    );

                    // Default statuses
                    $statuses = [
                        [t('status_open'),        '#6b7280', 0, 0],
                        [t('status_in_progress'), '#3b82f6', 1, 0],
                        [t('status_review'),      '#f59e0b', 2, 0],
                        [t('status_done'),        '#10b981', 3, 1],
                    ];
                    foreach ($statuses as [$sName, $sColor, $sPos, $sDone]) {
                        $db->execute(
                            'INSERT INTO task_statuses (project_id, name, color, position, is_done) VALUES (?,?,?,?,?)',
                            [$projectId, $sName, $sColor, $sPos, $sDone]
                        );
                    }

                    $db->commit();
                    Helper::logActivity(Auth::id(), 'project_created', $name, (int)$projectId);
                    Helper::redirect('/project/' . $projectId);
                } catch (\Throwable $e) {
                    $db->rollback();
                    $errors[] = t('project_create_error');
                }
            }
        }

        view('projects/create', ['errors' => $errors]);
    }

    public static function show(int $id): void
    {
        Auth::requireProjectAccess($id);
        $db      = Database::getInstance();
        $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$id]);
        if (!$project) Helper::redirect('/projects');

        $userPref = Auth::user()['preferred_view'] ?? 'kanban';
        $view = Helper::get('view', $project['default_view'] ?? $userPref);
        match ($view) {
            'list'  => self::listView($project, $db),
            'gantt' => self::ganttView($project, $db),
            default => self::kanbanView($project, $db),
        };
    }

    private static function kanbanView(array $project, Database $db): void
    {
        $statuses = $db->fetchAll(
            'SELECT * FROM task_statuses WHERE project_id = ? ORDER BY position',
            [$project['id']]
        );

        $tasks = $db->fetchAll(
            'SELECT t.*,
                    (SELECT COUNT(*) FROM comments WHERE task_id = t.id) as comment_count,
                    (SELECT COUNT(*) FROM attachments WHERE task_id = t.id) as attachment_count
             FROM tasks t
             WHERE t.project_id = ?
             ORDER BY t.position, t.created_at',
            [$project['id']]
        );

        Helper::loadAssignees($db, $tasks);
        // Re-group since tasks array is now modified
        $tasksByStatus = [];
        foreach ($statuses as $status) { $tasksByStatus[$status['id']] = []; }
        foreach ($tasks as $task) {
            if (isset($tasksByStatus[$task['status_id']])) $tasksByStatus[$task['status_id']][] = $task;
        }

        $members = self::getProjectMembers($project['id'], $db);
        $labels  = $db->fetchAll('SELECT * FROM labels WHERE project_id = ?', [$project['id']]);
        $myRole  = Auth::getProjectRole($project['id']);

        view('projects/kanban', compact('project', 'statuses', 'tasksByStatus', 'members', 'labels', 'myRole'));
    }

    private static function listView(array $project, Database $db): void
    {
        $statuses  = $db->fetchAll('SELECT * FROM task_statuses WHERE project_id = ? ORDER BY position', [$project['id']]);
        $members   = self::getProjectMembers($project['id'], $db);
        $externals = $db->fetchAll('SELECT * FROM project_externals WHERE project_id = ? AND email IS NOT NULL ORDER BY name', [$project['id']]);
        $dummies   = $db->fetchAll('SELECT * FROM project_externals WHERE project_id = ? AND email IS NULL ORDER BY name', [$project['id']]);
        $labels    = $db->fetchAll('SELECT * FROM labels WHERE project_id = ?', [$project['id']]);

        // Filters
        $filterStatus   = Helper::get('status_id');
        $filterPriority = Helper::get('priority');
        $filterAssignee = Helper::get('assigned_to'); // "u_<id>" or "e_<id>"
        $filterLabel    = Helper::get('label_id');
        $search         = Helper::get('search');

        $where  = ['t.project_id = ?'];
        $params = [$project['id']];

        if ($filterStatus)   { $where[] = 't.status_id = ?'; $params[] = $filterStatus; }
        if ($filterPriority) { $where[] = 't.priority = ?';  $params[] = $filterPriority; }
        if ($filterAssignee) {
            if (str_starts_with($filterAssignee, 'e_')) {
                $where[] = 'EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.external_id = ?)';
                $params[] = (int)substr($filterAssignee, 2);
            } else {
                $uid = str_starts_with($filterAssignee, 'u_') ? (int)substr($filterAssignee, 2) : (int)$filterAssignee;
                $where[] = 'EXISTS (SELECT 1 FROM task_assignees ta WHERE ta.task_id = t.id AND ta.user_id = ?)';
                $params[] = $uid;
            }
        }
        if ($search) { $where[] = 't.title LIKE ?'; $params[] = '%' . $search . '%'; }

        // Column-header sorting. Both `sort` and `dir` are matched against an
        // allowlist before being interpolated into the SQL — never user input
        // directly. Default is start_date asc with NULLs last, which mirrors
        // the Gantt timeline ordering and is what users see most often.
        $sortKey = (string)Helper::get('sort', 'start');
        $sortDir = strtolower((string)Helper::get('dir', 'asc')) === 'desc' ? 'desc' : 'asc';
        $sortMap = [
            'start'    => 'CASE WHEN t.start_date IS NULL THEN 1 ELSE 0 END, t.start_date',
            'due'      => 'CASE WHEN t.due_date   IS NULL THEN 1 ELSE 0 END, t.due_date',
            'title'    => 'LOWER(t.title)',
            'status'   => 'CASE WHEN ts.position IS NULL THEN 1 ELSE 0 END, ts.position',
            'priority' => "CASE t.priority WHEN 'urgent' THEN 0 WHEN 'high' THEN 1 WHEN 'medium' THEN 2 ELSE 3 END",
        ];
        if (!isset($sortMap[$sortKey])) $sortKey = 'start';
        $orderBy = $sortMap[$sortKey] . ' ' . strtoupper($sortDir) . ', t.id ASC';

        $sql = 'SELECT t.*, ts.name as status_name, ts.color as status_color
                FROM tasks t
                LEFT JOIN task_statuses ts ON ts.id = t.status_id
                WHERE ' . implode(' AND ', $where) . '
                ORDER BY ' . $orderBy;

        $tasks  = $db->fetchAll($sql, $params);
        Helper::loadAssignees($db, $tasks);
        $myRole = Auth::getProjectRole($project['id']);

        view('projects/list', compact('project', 'statuses', 'tasks', 'members', 'externals', 'dummies', 'labels', 'myRole',
            'filterStatus', 'filterPriority', 'filterAssignee', 'search', 'sortKey', 'sortDir'));
    }

    private static function ganttView(array $project, Database $db): void
    {
        $tasks = $db->fetchAll(
            'SELECT t.*, ts.name as status_name, ts.color as status_color, ts.is_done
             FROM tasks t
             LEFT JOIN task_statuses ts ON ts.id = t.status_id
             WHERE t.project_id = ?
             ORDER BY CASE WHEN t.start_date IS NULL THEN 1 ELSE 0 END, t.start_date ASC, t.title ASC',
            [$project['id']]
        );

        Helper::loadAssignees($db, $tasks);
        $myRole = Auth::getProjectRole($project['id']);

        $taskIds = array_column($tasks, 'id');
        $deps = [];
        if ($taskIds) {
            $placeholders = implode(',', array_fill(0, count($taskIds), '?'));
            $deps = $db->fetchAll(
                "SELECT predecessor_id, successor_id FROM task_dependencies
                 WHERE predecessor_id IN ($placeholders) OR successor_id IN ($placeholders)",
                array_merge($taskIds, $taskIds)
            );
        }

        view('projects/gantt', compact('project', 'tasks', 'deps', 'myRole'));
    }

    public static function settings(int $id): void
    {
        Auth::requireLogin();
        if (!Auth::canEditProject($id)) {
            Helper::redirect('/project/' . $id, ['error' => 'access_denied']);
        }

        $db      = Database::getInstance();
        $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$id]);
        if (!$project) Helper::redirect('/projects');

        $statuses = $db->fetchAll('SELECT * FROM task_statuses WHERE project_id = ? ORDER BY position', [$id]);
        $labels   = $db->fetchAll('SELECT * FROM labels WHERE project_id = ?', [$id]);
        $errors   = [];
        $success  = Helper::flash('success');

        if (Helper::isPost()) {
            Helper::verifyCsrf();
            $action = Helper::post('action');

            if ($action === 'update_project') {
                $name           = trim(Helper::post('name'));
                $desc           = trim(Helper::post('description'));
                $color          = Helper::color((string)Helper::post('color', '#4f46e5'), '#4f46e5');
                $defaultView    = Helper::post('default_view', 'kanban');
                $requiredFields = Helper::post('required_fields', []);

                if (empty($name)) {
                    $errors[] = t('name_required');
                } else {
                    $db->execute(
                        'UPDATE projects SET name=?, description=?, color=?, default_view=?, required_fields=?, updated_at=CURRENT_TIMESTAMP WHERE id=?',
                        [$name, $desc, $color, $defaultView, json_encode($requiredFields), $id]
                    );
                    Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_updated')]);
                }
            }

            if ($action === 'add_status') {
                $sName  = trim(Helper::post('status_name'));
                $sColor = Helper::color((string)Helper::post('status_color', '#6b7280'), '#6b7280');
                $isDone = (int)(Helper::post('is_done') === '1');
                if (!empty($sName)) {
                    $pos = $db->fetch('SELECT MAX(position) as m FROM task_statuses WHERE project_id = ?', [$id])['m'] + 1;
                    $db->execute(
                        'INSERT INTO task_statuses (project_id, name, color, position, is_done) VALUES (?,?,?,?,?)',
                        [$id, $sName, $sColor, $pos, $isDone]
                    );
                    Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_status_added')]);
                }
            }

            if ($action === 'delete_status') {
                $sid = (int)Helper::post('status_id');
                // Move tasks to first other status
                $first = $db->fetch('SELECT id FROM task_statuses WHERE project_id = ? AND id != ? ORDER BY position LIMIT 1', [$id, $sid]);
                if ($first) {
                    $db->execute('UPDATE tasks SET status_id = ? WHERE status_id = ?', [$first['id'], $sid]);
                }
                $db->execute('DELETE FROM task_statuses WHERE id = ? AND project_id = ?', [$sid, $id]);
                Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_status_deleted')]);
            }

            if ($action === 'add_label') {
                $lName  = trim(Helper::post('label_name'));
                $lColor = Helper::color((string)Helper::post('label_color', '#6b7280'), '#6b7280');
                if (!empty($lName)) {
                    $db->execute('INSERT INTO labels (project_id, name, color) VALUES (?,?,?)', [$id, $lName, $lColor]);
                    Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_label_added')]);
                }
            }

            if ($action === 'delete_label') {
                $lid = (int)Helper::post('label_id');
                $db->execute('DELETE FROM labels WHERE id = ? AND project_id = ?', [$lid, $id]);
                Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_label_deleted')]);
            }

            if ($action === 'save_notifications') {
                $fields = [
                    'notify_assignee_on_assign',
                    'notify_assignee_reminder',
                    'notify_assignee_predecessors',
                    'notify_assignee_on_comment',
                    'notify_assignee_on_change',
                    'notify_assignee_on_due_day',
                    'notify_assignee_on_unassign',
                    'notify_assignee_on_attachment',
                    'notify_manager_any_activity',
                    'notify_manager_task_done',
                    'notify_manager_task_overdue',
                    'notify_manager_task_created',
                    'notify_manager_assignees_changed',
                ];
                $values = [];
                foreach ($fields as $f) {
                    $values[] = Helper::post($f, '0') === '1' ? 1 : 0;
                }
                $cols   = implode(', ', $fields);
                $ph     = implode(', ', array_fill(0, count($fields), '?'));
                $upd    = implode(', ', array_map(fn($f) => "$f = excluded.$f", $fields));
                $db->execute(
                    "INSERT INTO project_notification_settings (project_id, $cols) VALUES (?, $ph)
                     ON CONFLICT(project_id) DO UPDATE SET $upd",
                    array_merge([$id], $values)
                );
                Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('success')]);
            }

            if ($action === 'archive_project' && Auth::canManageProject($id)) {
                $db->execute('UPDATE projects SET status = ? WHERE id = ?', ['archived', $id]);
                Helper::redirect('/projects', ['success' => Lang::get('project_updated')]);
            }

            if ($action === 'restore_project' && Auth::canManageProject($id)) {
                $db->execute('UPDATE projects SET status = ? WHERE id = ?', ['active', $id]);
                Helper::redirect('/project/' . $id . '/settings', ['success' => Lang::get('project_updated')]);
            }

            if ($action === 'delete_project' && Auth::canManageProject($id)) {
                $db->execute('DELETE FROM projects WHERE id = ?', [$id]);
                Helper::redirect('/projects', ['success' => Lang::get('project_deleted')]);
            }

        }

        $notifSettings = $db->fetch('SELECT * FROM project_notification_settings WHERE project_id = ?', [$id]) ?: [];
        view('projects/settings', compact('project', 'statuses', 'labels', 'errors', 'success', 'notifSettings'));
    }

    public static function members(int $id): void
    {
        Auth::requireLogin();
        if (!Auth::canEditProject($id)) {
            Helper::redirect('/project/' . $id, ['error' => 'access_denied']);
        }

        $db      = Database::getInstance();
        $project = $db->fetch('SELECT * FROM projects WHERE id = ?', [$id]);
        if (!$project) Helper::redirect('/projects');

        $errors = [];

        if (Helper::isPost()) {
            Helper::verifyCsrf();
            $action = Helper::post('action');

            if ($action === 'add_member') {
                $userId = (int)Helper::post('user_id');
                $role   = Helper::post('role', 'member');
                // Role choice is gated on `canManageProject` (owner-only).
                // Without this gate a manager could add a user with role
                // `owner` and then promote/demote others freely — effective
                // privilege parity with owners on the project. Managers may
                // still add members but only at member or guest level.
                if (!Auth::canManageProject($id)
                    && !in_array($role, ['member', 'guest'], true)) {
                    $role = 'member';
                }
                if ($userId && in_array($role, ['owner', 'manager', 'member', 'guest'])) {
                    $groupAllowed = (bool)PluginManager::filter(
                        'can_add_project_member',
                        true,
                        ['user_id' => $userId, 'is_admin' => Auth::isAdmin()]
                    );
                    if ($groupAllowed) {
                        try {
                            $db->execute(
                                'INSERT INTO project_members (project_id, user_id, role) VALUES (?,?,?)',
                                [$id, $userId, $role]
                            );
                            Helper::logActivity(Auth::id(), 'member_added', '', $id);
                        } catch (\Throwable) {}
                    }
                }
                Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_added')]);
            }

            if ($action === 'update_role') {
                // Owner-only: changing a member's role is a privilege-bearing
                // action. A manager would otherwise be able to self-promote
                // to owner via this endpoint.
                if (!Auth::canManageProject($id)) {
                    Helper::redirect('/project/' . $id . '/members', ['error' => Lang::get('access_denied')]);
                }
                $memberId = (int)Helper::post('member_id');
                $role     = Helper::post('role');
                if (in_array($role, ['owner', 'manager', 'member', 'guest'])) {
                    $current = $db->fetch('SELECT role FROM project_members WHERE id = ? AND project_id = ?', [$memberId, $id]);
                    if ($current && $current['role'] === 'owner' && $role !== 'owner') {
                        $ownerCount = (int)$db->fetch('SELECT COUNT(*) as cnt FROM project_members WHERE project_id = ? AND role = ?', [$id, 'owner'])['cnt'];
                        if ($ownerCount <= 1) {
                            Helper::redirect('/project/' . $id . '/members', ['error' => Lang::get('last_owner_error')]);
                        }
                    }
                    $db->execute('UPDATE project_members SET role = ? WHERE id = ? AND project_id = ?', [$role, $memberId, $id]);
                }
                Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_updated')]);
            }

            if ($action === 'remove_member') {
                $memberId = (int)Helper::post('member_id');
                $current  = $db->fetch('SELECT role FROM project_members WHERE id = ? AND project_id = ?', [$memberId, $id]);
                if ($current && $current['role'] === 'owner') {
                    $ownerCount = (int)$db->fetch('SELECT COUNT(*) as cnt FROM project_members WHERE project_id = ? AND role = ?', [$id, 'owner'])['cnt'];
                    if ($ownerCount <= 1) {
                        Helper::redirect('/project/' . $id . '/members', ['error' => Lang::get('last_owner_error')]);
                    }
                }
                $db->execute('DELETE FROM project_members WHERE id = ? AND project_id = ?', [$memberId, $id]);
                Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_removed')]);
            }

            if ($action === 'add_external') {
                $extName  = trim(Helper::post('ext_name', ''));
                $extEmail = trim(Helper::post('ext_email', ''));
                if (empty($extName)) {
                    $errors[] = Lang::get('required_field') . ': ' . Lang::get('ext_name_placeholder');
                } elseif (!filter_var($extEmail, FILTER_VALIDATE_EMAIL)) {
                    $errors[] = Lang::get('register_email_invalid');
                } else {
                    $token = bin2hex(random_bytes(32));
                    try {
                        $db->execute(
                            'INSERT INTO project_externals (project_id, name, email, token) VALUES (?,?,?,?)',
                            [$id, $extName, $extEmail, $token]
                        );
                        Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_added')]);
                    } catch (\Throwable) {
                        $errors[] = Lang::get('register_duplicate');
                    }
                }
            }

            if ($action === 'add_dummy') {
                $dummyName = trim(Helper::post('dummy_name', ''));
                if (empty($dummyName)) {
                    $errors[] = Lang::get('required_field') . ': ' . Lang::get('username');
                } else {
                    $db->execute(
                        'INSERT INTO project_externals (project_id, name, email, token) VALUES (?,?,NULL,NULL)',
                        [$id, $dummyName]
                    );
                    Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_added')]);
                }
            }

            if ($action === 'remove_external') {
                $extId = (int)Helper::post('external_id');
                $db->execute('DELETE FROM project_externals WHERE id = ? AND project_id = ?', [$extId, $id]);
                Helper::redirect('/project/' . $id . '/members', ['success' => Lang::get('member_removed')]);
            }
        }

        $members = self::getProjectMembers($id, $db);

        // Available users are no longer pre-loaded into the page (security-audit #24);
        // the "Add member" form queries /api/project/available-users via AJAX with a
        // minimum-length search term, returning at most a handful of matches.

        $externals = $db->fetchAll(
            'SELECT * FROM project_externals WHERE project_id = ? AND email IS NOT NULL ORDER BY name',
            [$id]
        );
        $dummies = $db->fetchAll(
            'SELECT * FROM project_externals WHERE project_id = ? AND email IS NULL ORDER BY name',
            [$id]
        );

        $success = Helper::flash('success');
        if ($e = Helper::flash('error')) $errors[] = $e;
        view('projects/members', compact('project', 'members', 'externals', 'dummies', 'errors', 'success'));
    }

    /**
     * Strict allowlist for plugin-supplied SQL fragments appended to a WHERE
     * clause. Must start with " AND " and contain only safe tokens — column
     * names, comparison/IN/IS NULL operators, parentheses, AND/OR, ? markers
     * and whitespace. Anything containing quotes, semicolons, or comment
     * delimiters is rejected so a malicious plugin cannot smuggle SQL into
     * the host query (security-audit #20).
     */
    public static function isSafeWhereFragment(string $sql): bool
    {
        // Hard denylist for characters that have no business in a parameterised fragment.
        if (preg_match('/[\'\"`;]|--|\/\*|\*\//', $sql)) return false;
        // Must start with " AND " (single leading space + AND + space).
        if (!preg_match('/^ AND /i', $sql)) return false;
        // Allowlist: tokens, parens, ? placeholders, comparison operators.
        return (bool)preg_match('/^( (AND|OR|NOT) |[A-Za-z_][A-Za-z0-9_.]*|\?|=|<>|!=|<=|>=|<|>|\bIN\b|\bIS\b|\bNULL\b|[(),\s])+$/i', $sql);
    }

    private static function getProjectMembers(int $projectId, Database $db): array
    {
        return $db->fetchAll(
            'SELECT pm.*, u.username, u.email, u.avatar, u.last_login
             FROM project_members pm
             JOIN users u ON u.id = pm.user_id
             WHERE pm.project_id = ?
             ORDER BY CASE pm.role WHEN \'owner\' THEN 0 WHEN \'manager\' THEN 1 WHEN \'member\' THEN 2 ELSE 3 END',
            [$projectId]
        );
    }
}
