import { Router } from 'express';
import { authenticateToken, type AuthenticatedRequest } from '../middleware/auth.middleware';
import { tenantIsolation } from '../middleware/tenant.middleware';
import { pool } from '../db';
import { storage } from '../storage';
import { computeTaskTagColor } from '@shared/tag-color';

const homeRouter = Router();

/**
 * Добавляет каждой строке задачи tagColor/tagLabel — «главный» цветной тег
 * (первая цветная опция select-поля по порядку полей формы). Одна загрузка
 * полей на форму на запрос; вычисление в JS (shared/tag-color).
 */
async function decorateTagColor(
  rows: Array<{ formId: number; fieldValues?: Record<string, unknown> }>,
  orgId: number,
): Promise<void> {
  if (rows.length === 0) return;
  const formIds = [...new Set(rows.map(r => r.formId).filter(Boolean))] as number[];
  const fieldsByForm = new Map<number, Awaited<ReturnType<typeof storage.getFormFields>>>();
  await Promise.all(formIds.map(async (fid) => {
    fieldsByForm.set(fid, await storage.getFormFields(fid, orgId));
  }));
  for (const row of rows) {
    const tag = computeTaskTagColor(fieldsByForm.get(row.formId) ?? [], row.fieldValues);
    (row as Record<string, unknown>).tagColor = tag?.color ?? null;
    (row as Record<string, unknown>).tagLabel = tag?.label ?? null;
  }
}

const INBOX_SORT_COLUMNS: Record<string, string> = {
  title: 't.title',
  status: 'fs.name',
  statusName: 'fs.name',
  assignee: 'assignee_name',
  assigneeName: 'assignee_name',
  form: 'f.name',
  formName: 'f.name',
  createdAt: 't.created_at',
  dueDate: 't.due_date',
  updatedAt: 't.updated_at',
};

const RECENT_SORT_COLUMNS: Record<string, string> = {
  ...INBOX_SORT_COLUMNS,
  lastInteractionAt: 'rm.last_at',
};

function getSortClause(
  sortColumn: string | undefined,
  sortDirection: string | undefined,
  defaultOrder: string,
  allowedColumns: Record<string, string>,
): string {
  const col = sortColumn && allowedColumns[sortColumn];
  if (!col) return defaultOrder;
  const dir = sortDirection === 'asc' ? 'ASC' : 'DESC';
  if (col === 'assignee_name') {
    return `CONCAT(u.first_name, ' ', u.last_name) ${dir} NULLS LAST`;
  }
  if (col === 't.due_date' || col === 'rm.last_at') {
    return `${col} ${dir} NULLS LAST`;
  }
  return `${col} ${dir}`;
}

homeRouter.get('/api/home/inbox',
  authenticateToken,
  tenantIsolation,
  async (req: AuthenticatedRequest, res) => {
    try {
      const userId = req.user!.id;
      const orgId = req.user!.organizationId;
      const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);
      const offset = parseInt(req.query.offset as string) || 0;
      const search = (req.query.search as string) || '';
      const formId = parseInt(req.query.formId as string) || 0;
      const status = (req.query.status as string) || '';
      const assignee = (req.query.assignee as string) || '';
      const overdue = req.query.overdue === 'true';
      const sortColumn = req.query.sortColumn as string | undefined;
      const sortDirection = req.query.sortDirection as string | undefined;
      const orderClause = getSortClause(sortColumn, sortDirection, 't.updated_at DESC', INBOX_SORT_COLUMNS);

      const result = await pool.query(`
        WITH inbox_sources AS (
          SELECT t.id AS task_id, 'assignee' AS reason, NULL::text AS role_name
          FROM tasks t
          WHERE t.organization_id = $1
            AND t.assigned_to = $2
            AND t.is_completed = false

          UNION

          SELECT ta.task_id, 'assignee' AS reason, NULL::text AS role_name
          FROM task_assignees ta
          JOIN tasks t ON t.id = ta.task_id AND t.organization_id = $1 AND t.is_completed = false
          WHERE ta.user_id = $2 AND ta.organization_id = $1

          UNION

          -- Замещение: я — заместитель отсутствующего исполнителя
          SELECT ta_d.task_id, 'delegation' AS reason, NULL::text AS role_name
          FROM task_assignees ta_d
          JOIN tasks t_d ON t_d.id = ta_d.task_id AND t_d.organization_id = $1 AND t_d.is_completed = false
          WHERE ta_d.user_id = $2 AND ta_d.organization_id = $1
            AND ta_d.delegated_from_user_id IS NOT NULL

          UNION

          -- Замещение: я — отсутствующий исполнитель, задача ждёт моего ознакомления
          SELECT ta_r.task_id, 'delegation_review' AS reason, NULL::text AS role_name
          FROM task_assignees ta_r
          JOIN tasks t_r ON t_r.id = ta_r.task_id AND t_r.organization_id = $1 AND t_r.is_completed = false
          WHERE ta_r.user_id = $2 AND ta_r.organization_id = $1
            AND ta_r.pending_review = true

          UNION

          SELECT un.task_id, 'notification' AS reason, NULL::text
          FROM user_notifications un
          JOIN tasks t2 ON t2.id = un.task_id AND t2.organization_id = $1 AND t2.is_completed = false
          WHERE un.organization_id = $1
            AND un.user_id = $2
            AND un.is_read = false
            AND un.task_id IS NOT NULL

          UNION

          SELECT tr.task_id, 'role' AS reason, r.name AS role_name
          FROM task_roles tr
          JOIN role_members rm ON rm.role_id = tr.role_id AND rm.user_id = $2
          JOIN roles r ON r.id = tr.role_id
          JOIN tasks t3 ON t3.id = tr.task_id AND t3.organization_id = $1 AND t3.is_completed = false
          WHERE tr.organization_id = $1
        ),
        aggregated AS (
          SELECT
            task_id,
            ARRAY_AGG(DISTINCT reason) AS reasons,
            ARRAY_AGG(DISTINCT role_name) FILTER (WHERE role_name IS NOT NULL) AS role_names
          FROM inbox_sources
          GROUP BY task_id
        )
        SELECT
          t.id,
          t.title,
          t.form_id AS "formId",
          f.name AS "formName",
          fs.name AS "statusName",
          fs.color AS "statusColor",
          fs.is_final AS "statusIsFinal",
          t.current_status_id AS "statusId",
          COALESCE(
            CASE WHEN u.id IS NOT NULL THEN CONCAT(u.first_name, ' ', u.last_name) ELSE NULL END,
            (SELECT STRING_AGG(CONCAT(ua.first_name, ' ', ua.last_name), ', ' ORDER BY ta2.id)
             FROM task_assignees ta2 JOIN users ua ON ua.id = ta2.user_id
             WHERE ta2.task_id = t.id)
          ) AS "assigneeName",
          t.assigned_to AS "assignedTo",
          (
            SELECT JSONB_AGG(JSONB_BUILD_OBJECT('userId', ta3.user_id, 'user', JSONB_BUILD_OBJECT('firstName', ua3.first_name, 'lastName', ua3.last_name, 'email', ua3.email)))
            FROM task_assignees ta3 JOIN users ua3 ON ua3.id = ta3.user_id
            WHERE ta3.task_id = t.id
          ) AS "assignees",
          t.created_at AS "createdAt",
          t.updated_at AS "updatedAt",
          t.due_date AS "dueDate",
          t.is_completed AS "isCompleted",
          a.reasons,
          a.role_names AS "roleNames",
          (
            SELECT COUNT(*) FROM user_notifications un2
            WHERE un2.task_id = t.id AND un2.user_id = $2 AND un2.is_read = false
          )::int AS "unreadNotifications",
          (
            SELECT jsonb_object_agg('field_' || tfv.field_id::text, tfv.value)
            FROM task_field_values tfv WHERE tfv.task_id = t.id
          ) AS "fieldValues"
        FROM aggregated a
        JOIN tasks t ON t.id = a.task_id AND t.organization_id = $1
        JOIN forms f ON f.id = t.form_id
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        WHERE t.is_completed = false
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
          AND ($4 = 0 OR t.form_id = $4)
          AND ($7 = '' OR fs.name ILIKE '%' || $7 || '%')
          AND ($8 = '' OR COALESCE(CONCAT(u.first_name, ' ', u.last_name), '') ILIKE '%' || $8 || '%'
               OR EXISTS (SELECT 1 FROM task_assignees ta_f JOIN users ua_f ON ua_f.id = ta_f.user_id
                          WHERE ta_f.task_id = t.id AND CONCAT(ua_f.first_name, ' ', ua_f.last_name) ILIKE '%' || $8 || '%'))
          AND (NOT $9 OR (t.due_date IS NOT NULL AND t.due_date < NOW()))
          AND NOT EXISTS (
            SELECT 1 FROM task_reminders tr_snooze
            WHERE tr_snooze.task_id = t.id
              AND (
                tr_snooze.created_by_user_id = $2
                OR tr_snooze.recipients @> jsonb_build_array(jsonb_build_object('type', 'user', 'userId', $2))
                OR EXISTS (
                  SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                  WHERE r_el ->> 'type' = 'role'
                    AND r_el ->> 'role' = (SELECT app_role FROM users WHERE id = $2)
                )
                OR EXISTS (
                  SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                  WHERE r_el ->> 'type' = 'orgRole'
                    AND EXISTS (SELECT 1 FROM role_members rm WHERE rm.role_id = (r_el ->> 'roleId')::int AND rm.user_id = $2)
                )
              )
              AND tr_snooze.remind_at > NOW()
              AND tr_snooze.is_sent = false
          )
          AND NOT EXISTS (
            SELECT 1 FROM task_inbox_hidden tih
            WHERE tih.task_id = t.id AND tih.user_id = $2
          )
        ORDER BY ${orderClause}
        LIMIT $5 OFFSET $6
      `, [orgId, userId, search, formId, limit, offset, status, assignee, overdue]);

      const countResult = await pool.query(`
        WITH inbox_sources AS (
          SELECT t.id AS task_id
          FROM tasks t
          WHERE t.organization_id = $1 AND t.assigned_to = $2 AND t.is_completed = false
          UNION
          SELECT ta.task_id FROM task_assignees ta
          JOIN tasks t ON t.id = ta.task_id AND t.organization_id = $1 AND t.is_completed = false
          WHERE ta.user_id = $2 AND ta.organization_id = $1
          UNION
          SELECT un.task_id FROM user_notifications un
          JOIN tasks t2 ON t2.id = un.task_id AND t2.organization_id = $1 AND t2.is_completed = false
          WHERE un.organization_id = $1 AND un.user_id = $2 AND un.is_read = false AND un.task_id IS NOT NULL
          UNION
          SELECT tr.task_id FROM task_roles tr
          JOIN role_members rm ON rm.role_id = tr.role_id AND rm.user_id = $2
          JOIN tasks t3 ON t3.id = tr.task_id AND t3.organization_id = $1 AND t3.is_completed = false
          WHERE tr.organization_id = $1
        )
        SELECT COUNT(DISTINCT a.task_id)::int AS total
        FROM inbox_sources a
        JOIN tasks t ON t.id = a.task_id AND t.organization_id = $1
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        WHERE t.is_completed = false
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
          AND ($4 = 0 OR t.form_id = $4)
          AND ($5 = '' OR fs.name ILIKE '%' || $5 || '%')
          AND ($6 = '' OR COALESCE(CONCAT(u.first_name, ' ', u.last_name), '') ILIKE '%' || $6 || '%'
               OR EXISTS (SELECT 1 FROM task_assignees ta_f JOIN users ua_f ON ua_f.id = ta_f.user_id
                          WHERE ta_f.task_id = t.id AND CONCAT(ua_f.first_name, ' ', ua_f.last_name) ILIKE '%' || $6 || '%'))
          AND (NOT $7 OR (t.due_date IS NOT NULL AND t.due_date < NOW()))
          AND NOT EXISTS (
            SELECT 1 FROM task_reminders tr_snooze
            WHERE tr_snooze.task_id = t.id
              AND (
                tr_snooze.created_by_user_id = $2
                OR tr_snooze.recipients @> jsonb_build_array(jsonb_build_object('type', 'user', 'userId', $2))
                OR EXISTS (
                  SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                  WHERE r_el ->> 'type' = 'role'
                    AND r_el ->> 'role' = (SELECT app_role FROM users WHERE id = $2)
                )
                OR EXISTS (
                  SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                  WHERE r_el ->> 'type' = 'orgRole'
                    AND EXISTS (SELECT 1 FROM role_members rm WHERE rm.role_id = (r_el ->> 'roleId')::int AND rm.user_id = $2)
                )
              )
              AND tr_snooze.remind_at > NOW()
              AND tr_snooze.is_sent = false
          )
          AND NOT EXISTS (
            SELECT 1 FROM task_inbox_hidden tih
            WHERE tih.task_id = t.id AND tih.user_id = $2
          )
      `, [orgId, userId, search, formId, status, assignee, overdue]);

      await decorateTagColor(result.rows, orgId);

      res.json({
        success: true,
        tasks: result.rows,
        total: countResult.rows[0]?.total ?? 0,
      });
    } catch (err) {
      console.error('[home/inbox]', err);
      res.status(500).json({ success: false, error: 'Ошибка загрузки входящих' });
    }
  }
);

homeRouter.get('/api/home/recent',
  authenticateToken,
  tenantIsolation,
  async (req: AuthenticatedRequest, res) => {
    try {
      const userId = req.user!.id;
      const orgId = req.user!.organizationId;
      const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);
      const offset = parseInt(req.query.offset as string) || 0;
      const search = (req.query.search as string) || '';
      const formId = parseInt(req.query.formId as string) || 0;
      const status = (req.query.status as string) || '';
      const assignee = (req.query.assignee as string) || '';
      const sortColumn = req.query.sortColumn as string | undefined;
      const sortDirection = req.query.sortDirection as string | undefined;
      const orderClause = getSortClause(sortColumn, sortDirection, 'rm.last_at DESC', RECENT_SORT_COLUMNS);

      const result = await pool.query(`
        WITH recent_interactions AS (
          SELECT task_id, MAX(created_at) AS last_at
          FROM task_audit_log
          WHERE organization_id = $1 AND changed_by = $2
          GROUP BY task_id

          UNION ALL

          SELECT task_id, MAX(created_at) AS last_at
          FROM task_messages
          WHERE author_id = $2
            AND task_id IN (SELECT id FROM tasks WHERE organization_id = $1)
          GROUP BY task_id

          UNION ALL

          SELECT task_id, MAX(viewed_at) AS last_at
          FROM task_views
          WHERE organization_id = $1 AND user_id = $2
          GROUP BY task_id
        ),
        recent_max AS (
          SELECT task_id, MAX(last_at) AS last_at
          FROM recent_interactions
          GROUP BY task_id
        ),
        accessible AS (
          SELECT task_id FROM task_user_access
          WHERE user_id = $2 AND organization_id = $1 AND revoked_at IS NULL
          UNION
          SELECT id AS task_id FROM tasks WHERE created_by = $2 AND organization_id = $1
        )
        SELECT
          t.id,
          t.title,
          t.form_id AS "formId",
          f.name AS "formName",
          fs.name AS "statusName",
          fs.color AS "statusColor",
          fs.is_final AS "statusIsFinal",
          t.current_status_id AS "statusId",
          CASE
            WHEN u.id IS NOT NULL THEN CONCAT(u.first_name, ' ', u.last_name)
            ELSE NULL
          END AS "assigneeName",
          t.assigned_to AS "assignedTo",
          t.created_at AS "createdAt",
          t.updated_at AS "updatedAt",
          t.due_date AS "dueDate",
          t.is_completed AS "isCompleted",
          rm.last_at AS "lastInteractionAt",
          (
            SELECT jsonb_object_agg('field_' || tfv.field_id::text, tfv.value)
            FROM task_field_values tfv WHERE tfv.task_id = t.id
          ) AS "fieldValues"
        FROM recent_max rm
        JOIN tasks t ON t.id = rm.task_id AND t.organization_id = $1
        JOIN accessible acc ON acc.task_id = t.id
        JOIN forms f ON f.id = t.form_id
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        WHERE ($3 = '' OR t.title ILIKE '%' || $3 || '%')
          AND ($4 = 0 OR t.form_id = $4)
          AND ($7 = '' OR fs.name ILIKE '%' || $7 || '%')
          AND ($8 = '' OR CONCAT(u.first_name, ' ', u.last_name) ILIKE '%' || $8 || '%')
        ORDER BY ${orderClause}
        LIMIT $5 OFFSET $6
      `, [orgId, userId, search, formId, limit, offset, status, assignee]);

      const countResult = await pool.query(`
        WITH recent_interactions AS (
          SELECT task_id FROM task_audit_log WHERE organization_id = $1 AND changed_by = $2
          UNION
          SELECT task_id FROM task_messages
          WHERE author_id = $2
            AND task_id IN (SELECT id FROM tasks WHERE organization_id = $1)
          UNION
          SELECT task_id FROM task_views WHERE organization_id = $1 AND user_id = $2
        ),
        accessible AS (
          SELECT task_id FROM task_user_access
          WHERE user_id = $2 AND organization_id = $1 AND revoked_at IS NULL
          UNION
          SELECT id AS task_id FROM tasks WHERE created_by = $2 AND organization_id = $1
        )
        SELECT COUNT(DISTINCT ri.task_id)::int AS total
        FROM recent_interactions ri
        JOIN tasks t ON t.id = ri.task_id AND t.organization_id = $1
        JOIN accessible acc ON acc.task_id = ri.task_id
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        WHERE ($3 = '' OR t.title ILIKE '%' || $3 || '%')
          AND ($4 = 0 OR t.form_id = $4)
          AND ($5 = '' OR fs.name ILIKE '%' || $5 || '%')
          AND ($6 = '' OR CONCAT(u.first_name, ' ', u.last_name) ILIKE '%' || $6 || '%')
      `, [orgId, userId, search, formId, status, assignee]);

      await decorateTagColor(result.rows, orgId);

      res.json({
        success: true,
        tasks: result.rows,
        total: countResult.rows[0]?.total ?? 0,
      });
    } catch (err) {
      console.error('[home/recent]', err);
      res.status(500).json({ success: false, error: 'Ошибка загрузки последних' });
    }
  }
);

homeRouter.get('/api/home/inbox/count',
  authenticateToken,
  tenantIsolation,
  async (req: AuthenticatedRequest, res) => {
    try {
      const userId = req.user!.id;
      const orgId = req.user!.organizationId;

      const result = await pool.query(`
        WITH inbox_sources AS (
          SELECT t.id AS task_id
          FROM tasks t
          WHERE t.organization_id = $1 AND t.assigned_to = $2 AND t.is_completed = false

          UNION

          SELECT ta.task_id FROM task_assignees ta
          JOIN tasks t ON t.id = ta.task_id AND t.organization_id = $1 AND t.is_completed = false
          WHERE ta.user_id = $2 AND ta.organization_id = $1

          UNION

          SELECT un.task_id FROM user_notifications un
          JOIN tasks t2 ON t2.id = un.task_id AND t2.organization_id = $1 AND t2.is_completed = false
          WHERE un.organization_id = $1 AND un.user_id = $2 AND un.is_read = false AND un.task_id IS NOT NULL

          UNION

          SELECT tr.task_id FROM task_roles tr
          JOIN role_members rm ON rm.role_id = tr.role_id AND rm.user_id = $2
          JOIN tasks t3 ON t3.id = tr.task_id AND t3.organization_id = $1 AND t3.is_completed = false
          WHERE tr.organization_id = $1
        )
        SELECT COUNT(DISTINCT task_id)::int AS count
        FROM inbox_sources s
        WHERE NOT EXISTS (
          SELECT 1 FROM task_reminders tr_snooze
          WHERE tr_snooze.task_id = s.task_id
            AND (
              tr_snooze.created_by_user_id = $2
              OR tr_snooze.recipients @> jsonb_build_array(jsonb_build_object('type', 'user', 'userId', $2))
              OR EXISTS (
                SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                WHERE r_el ->> 'type' = 'role'
                  AND r_el ->> 'role' = (SELECT app_role FROM users WHERE id = $2)
              )
              OR EXISTS (
                SELECT 1 FROM jsonb_array_elements(tr_snooze.recipients) r_el
                WHERE r_el ->> 'type' = 'orgRole'
                  AND EXISTS (SELECT 1 FROM role_members rm WHERE rm.role_id = (r_el ->> 'roleId')::int AND rm.user_id = $2)
              )
            )
            AND tr_snooze.remind_at > NOW()
            AND tr_snooze.is_sent = false
        )
          AND NOT EXISTS (
            SELECT 1 FROM task_inbox_hidden tih
            WHERE tih.task_id = s.task_id AND tih.user_id = $2
          )
      `, [orgId, userId]);

      res.json({ count: result.rows[0]?.count ?? 0 });
    } catch (err) {
      console.error('[home/inbox/count]', err);
      res.status(500).json({ count: 0 });
    }
  }
);

homeRouter.get('/api/home/snoozed',
  authenticateToken,
  tenantIsolation,
  async (req: AuthenticatedRequest, res) => {
    try {
      const userId = req.user!.id;
      const orgId = req.user!.organizationId;
      const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);
      const offset = parseInt(req.query.offset as string) || 0;
      const search = (req.query.search as string) || '';

      const result = await pool.query(`
        SELECT DISTINCT ON (t.id)
          t.id,
          t.title,
          t.form_id AS "formId",
          f.name AS "formName",
          fs.name AS "statusName",
          fs.color AS "statusColor",
          fs.is_final AS "statusIsFinal",
          t.current_status_id AS "statusId",
          CASE
            WHEN u.id IS NOT NULL THEN CONCAT(u.first_name, ' ', u.last_name)
            ELSE NULL
          END AS "assigneeName",
          t.assigned_to AS "assignedTo",
          t.created_at AS "createdAt",
          t.updated_at AS "updatedAt",
          t.due_date AS "dueDate",
          t.is_completed AS "isCompleted",
          (
            SELECT jsonb_object_agg('field_' || tfv.field_id::text, tfv.value)
            FROM task_field_values tfv WHERE tfv.task_id = t.id
          ) AS "fieldValues",
          MIN(tr.remind_at) OVER (PARTITION BY t.id) AS "remindAt",
          CONCAT(cu.first_name, ' ', cu.last_name) AS "createdByName"
        FROM task_reminders tr
        JOIN tasks t ON t.id = tr.task_id AND t.organization_id = $1
        JOIN forms f ON f.id = t.form_id
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        LEFT JOIN users cu ON cu.id = tr.created_by_user_id
        WHERE tr.organization_id = $1
          AND (
            tr.created_by_user_id = $2
            OR tr.recipients @> jsonb_build_array(jsonb_build_object('type', 'user', 'userId', $2))
            OR EXISTS (
              SELECT 1 FROM jsonb_array_elements(tr.recipients) r_el
              WHERE r_el ->> 'type' = 'role'
                AND r_el ->> 'role' = (SELECT app_role FROM users WHERE id = $2)
            )
            OR EXISTS (
              SELECT 1 FROM jsonb_array_elements(tr.recipients) r_el
              WHERE r_el ->> 'type' = 'orgRole'
                AND EXISTS (SELECT 1 FROM role_members rm WHERE rm.role_id = (r_el ->> 'roleId')::int AND rm.user_id = $2)
            )
          )
          AND tr.remind_at > NOW()
          AND tr.is_sent = false
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
        ORDER BY t.id, tr.remind_at ASC
        LIMIT $4 OFFSET $5
      `, [orgId, userId, search, limit, offset]);

      const countResult = await pool.query(`
        SELECT COUNT(DISTINCT t.id)::int AS total
        FROM task_reminders tr
        JOIN tasks t ON t.id = tr.task_id AND t.organization_id = $1
        WHERE tr.organization_id = $1
          AND (
            tr.created_by_user_id = $2
            OR tr.recipients @> jsonb_build_array(jsonb_build_object('type', 'user', 'userId', $2))
            OR EXISTS (
              SELECT 1 FROM jsonb_array_elements(tr.recipients) r_el
              WHERE r_el ->> 'type' = 'role'
                AND r_el ->> 'role' = (SELECT app_role FROM users WHERE id = $2)
            )
            OR EXISTS (
              SELECT 1 FROM jsonb_array_elements(tr.recipients) r_el
              WHERE r_el ->> 'type' = 'orgRole'
                AND EXISTS (SELECT 1 FROM role_members rm WHERE rm.role_id = (r_el ->> 'roleId')::int AND rm.user_id = $2)
            )
          )
          AND tr.remind_at > NOW()
          AND tr.is_sent = false
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
      `, [orgId, userId, search]);

      await decorateTagColor(result.rows, orgId);

      res.json({
        success: true,
        tasks: result.rows,
        total: countResult.rows[0]?.total ?? 0,
      });
    } catch (err) {
      console.error('[home/snoozed]', err);
      res.status(500).json({ success: false, error: 'Ошибка загрузки отложенных' });
    }
  }
);

homeRouter.get('/api/home/hidden',
  authenticateToken,
  tenantIsolation,
  async (req: AuthenticatedRequest, res) => {
    try {
      const userId = req.user!.id;
      const orgId = req.user!.organizationId;
      const limit = Math.min(parseInt(req.query.limit as string) || 50, 100);
      const offset = parseInt(req.query.offset as string) || 0;
      const search = (req.query.search as string) || '';

      const result = await pool.query(`
        SELECT
          t.id,
          t.title,
          t.form_id AS "formId",
          f.name AS "formName",
          fs.name AS "statusName",
          fs.color AS "statusColor",
          fs.is_final AS "statusIsFinal",
          t.current_status_id AS "statusId",
          CASE
            WHEN u.id IS NOT NULL THEN CONCAT(u.first_name, ' ', u.last_name)
            ELSE NULL
          END AS "assigneeName",
          t.assigned_to AS "assignedTo",
          t.created_at AS "createdAt",
          t.updated_at AS "updatedAt",
          t.due_date AS "dueDate",
          t.is_completed AS "isCompleted",
          tih.created_at AS "hiddenAt",
          (
            SELECT jsonb_object_agg('field_' || tfv.field_id::text, tfv.value)
            FROM task_field_values tfv WHERE tfv.task_id = t.id
          ) AS "fieldValues"
        FROM task_inbox_hidden tih
        JOIN tasks t ON t.id = tih.task_id AND t.organization_id = $1 AND t.is_completed = false
        JOIN forms f ON f.id = t.form_id
        JOIN form_statuses fs ON fs.id = t.current_status_id
        LEFT JOIN users u ON u.id = t.assigned_to
        WHERE tih.user_id = $2
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
        ORDER BY tih.created_at DESC
        LIMIT $4 OFFSET $5
      `, [orgId, userId, search, limit, offset]);

      const countResult = await pool.query(`
        SELECT COUNT(*)::int AS total
        FROM task_inbox_hidden tih
        JOIN tasks t ON t.id = tih.task_id AND t.organization_id = $1 AND t.is_completed = false
        WHERE tih.user_id = $2
          AND ($3 = '' OR t.title ILIKE '%' || $3 || '%')
      `, [orgId, userId, search]);

      await decorateTagColor(result.rows, orgId);

      res.json({
        success: true,
        tasks: result.rows,
        total: countResult.rows[0]?.total ?? 0,
      });
    } catch (err) {
      console.error('[home/hidden]', err);
      res.status(500).json({ success: false, error: 'Ошибка загрузки скрытых задач' });
    }
  }
);

export default homeRouter;