const DIVISION_ORDER = [8, 7, 1, 2, 3, 4, 5, 6];

function createWorkflowHistoryService({ db, timeZone = 'Europe/Rome' }) {
  function getTodayDateString() {
    return new Intl.DateTimeFormat('en-CA', {
      timeZone,
      year: 'numeric',
      month: '2-digit',
      day: '2-digit'
    }).format(new Date());
  }

  function toDateString(value, fallback = null) {
    if (!value) return fallback;
    const stringValue = String(value).trim();
    if (/^\d{4}-\d{2}-\d{2}$/.test(stringValue)) return stringValue;
    const date = new Date(stringValue);
    return Number.isNaN(date.getTime()) ? fallback : date.toISOString().slice(0, 10);
  }

  function normalizeDateInput(value, fallback = getTodayDateString()) {
    return toDateString(value, fallback) || fallback;
  }

  function buildTimestamp(dateString) {
    return `${normalizeDateInput(dateString)}T12:00:00.000Z`;
  }

  function sortDivisions(divisions) {
    return [...divisions].sort((a, b) => DIVISION_ORDER.indexOf(a.id) - DIVISION_ORDER.indexOf(b.id));
  }

  function todoTouchesRange(todo, fromDate, toDate) {
    const startDate = normalizeDateInput(todo.work_date || todo.created_at, fromDate);
    const completedDate = toDateString(todo.completed_at);
    if (startDate >= fromDate && startDate <= toDate) return true;
    if (completedDate && completedDate >= fromDate && completedDate <= toDate) return true;
    return false;
  }

  function historyTouchesRange(history, fromDate, toDate) {
    const startDate = normalizeDateInput(history.start_date, fromDate);
    const endDate = toDateString(history.end_date);
    return (
      (startDate >= fromDate && startDate <= toDate)
      || (endDate && endDate >= fromDate && endDate <= toDate)
    );
  }

  function itemTouchesRange(item, history, currentTodos, historyTodos, fromDate, toDate) {
    const itemDate = normalizeDateInput(item.work_date || item.created_at, fromDate);
    if (itemDate >= fromDate && itemDate <= toDate) return true;
    if (historyTouchesRange(history, fromDate, toDate)) return true;
    if (currentTodos.length > 0 || historyTodos.length > 0) return true;
    return false;
  }

  function getBoardSnapshot({ fromDate = null, toDate = null } = {}) {
    const effectiveFrom = normalizeDateInput(fromDate);
    const effectiveTo = normalizeDateInput(toDate || effectiveFrom, effectiveFrom);
    const divisions = sortDivisions(db.prepare('SELECT * FROM divisions ORDER BY id ASC').all());
    const baseItems = db.prepare('SELECT * FROM items ORDER BY id ASC').all();
    const allTodos = db.prepare('SELECT * FROM todos ORDER BY id ASC').all();
    const historyRows = db.prepare(`
      SELECT *
      FROM item_division_history
      WHERE start_date <= ?
        AND (end_date IS NULL OR end_date >= ?)
      ORDER BY start_date ASC, id ASC
    `).all(effectiveTo, effectiveFrom);

    const mergedHistories = new Map();
    historyRows.forEach((row) => {
      const key = `${row.item_id}:${row.division_id}`;
      const existing = mergedHistories.get(key);
      if (!existing) {
        mergedHistories.set(key, { ...row });
        return;
      }
      existing.start_date = existing.start_date < row.start_date ? existing.start_date : row.start_date;
      if (!existing.end_date || !row.end_date) {
        existing.end_date = existing.end_date || row.end_date || null;
      } else {
        existing.end_date = existing.end_date > row.end_date ? existing.end_date : row.end_date;
      }
    });

    const itemsById = new Map(baseItems.map((item) => [item.id, item]));
    const divisionItems = new Map(divisions.map((division) => [division.id, []]));

    for (const history of mergedHistories.values()) {
      const item = itemsById.get(history.item_id);
      if (!item || !divisionItems.has(history.division_id)) continue;
      const currentTodos = allTodos
        .filter((todo) => todo.item_id === item.id && todo.division_id === history.division_id && todoTouchesRange(todo, effectiveFrom, effectiveTo))
        .sort((a, b) => Number(a.done) - Number(b.done) || a.id - b.id);
      const historyTodos = allTodos
        .filter((todo) => todo.item_id === item.id && todo.division_id !== history.division_id && todoTouchesRange(todo, effectiveFrom, effectiveTo))
        .sort((a, b) => Number(a.done) - Number(b.done) || a.id - b.id);
      if (!itemTouchesRange(item, history, currentTodos, historyTodos, effectiveFrom, effectiveTo)) continue;
      divisionItems.get(history.division_id).push({
        ...item,
        current_todos: currentTodos,
        history_todos: historyTodos,
        history_window_start: history.start_date,
        history_window_end: history.end_date
      });
    }

    divisions.forEach((division) => {
      division.items = (divisionItems.get(division.id) || []).sort((a, b) => {
        return String(b.updated_at || '').localeCompare(String(a.updated_at || '')) || b.id - a.id;
      });
    });

    return { divisions, from_date: effectiveFrom, to_date: effectiveTo };
  }

  function createItem({ divisionId, title, magicLink, workDate = null }) {
    const normalizedDivisionId = Number(divisionId);
    const normalizedWorkDate = normalizeDateInput(workDate);
    const timestamp = buildTimestamp(normalizedWorkDate);
    const result = db.prepare(`
      INSERT INTO items (division_id, title, magic_link, work_date, created_at, updated_at)
      VALUES (?, ?, ?, ?, ?, ?)
    `).run(normalizedDivisionId, String(title || '').trim(), magicLink, normalizedWorkDate, timestamp, timestamp);
    db.prepare(`
      INSERT INTO item_division_history (item_id, division_id, start_date, end_date, created_at, updated_at)
      VALUES (?, ?, ?, NULL, ?, ?)
    `).run(result.lastInsertRowid, normalizedDivisionId, normalizedWorkDate, timestamp, timestamp);
    return result.lastInsertRowid;
  }

  function moveItem(itemId, newDivisionId, moveDate = null) {
    const item = db.prepare('SELECT * FROM items WHERE id = ?').get(itemId);
    if (!item) return null;
    const normalizedDivisionId = Number(newDivisionId);
    if (Number(item.division_id) === normalizedDivisionId) return item;
    const normalizedMoveDate = normalizeDateInput(moveDate);
    const timestamp = buildTimestamp(normalizedMoveDate);
    db.prepare(`
      UPDATE item_division_history
      SET end_date = ?, updated_at = ?
      WHERE item_id = ? AND division_id = ? AND end_date IS NULL
    `).run(normalizedMoveDate, timestamp, itemId, item.division_id);
    db.prepare(`
      INSERT INTO item_division_history (item_id, division_id, start_date, end_date, created_at, updated_at)
      VALUES (?, ?, ?, NULL, ?, ?)
    `).run(itemId, normalizedDivisionId, normalizedMoveDate, timestamp, timestamp);
    db.prepare('UPDATE items SET division_id = ?, updated_at = ? WHERE id = ?').run(normalizedDivisionId, timestamp, itemId);
    return { ...item, division_id: normalizedDivisionId };
  }

  function addTodo({ itemId, divisionId, text, executiveOrderId = null, workDate = null, done = false }) {
    const normalizedWorkDate = normalizeDateInput(workDate);
    const timestamp = buildTimestamp(normalizedWorkDate);
    const completedAt = done ? timestamp : null;
    const result = db.prepare(`
      INSERT INTO todos (item_id, division_id, text, done, executive_order_id, work_date, created_at, updated_at, completed_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
    `).run(
      Number(itemId),
      Number(divisionId),
      String(text || '').trim(),
      done ? 1 : 0,
      executiveOrderId ? Number(executiveOrderId) : null,
      normalizedWorkDate,
      timestamp,
      timestamp,
      completedAt
    );
    return result.lastInsertRowid;
  }

  function toggleTodo(todoId, done) {
    const existing = db.prepare('SELECT * FROM todos WHERE id = ?').get(todoId);
    if (!existing) return null;
    const timestamp = new Date().toISOString();
    db.prepare('UPDATE todos SET done = ?, updated_at = ?, completed_at = ? WHERE id = ?')
      .run(done ? 1 : 0, timestamp, done ? (existing.completed_at || timestamp) : null, todoId);
    return { ...existing, done: done ? 1 : 0, updated_at: timestamp, completed_at: done ? (existing.completed_at || timestamp) : null };
  }

  function updateItemMeta(itemId, payload = {}) {
    const existing = db.prepare('SELECT * FROM items WHERE id = ?').get(itemId);
    if (!existing) return null;
    const nextTitle = String(payload.title ?? existing.title).trim() || existing.title;
    const nextWorkDate = payload.work_date === undefined
      ? existing.work_date
      : normalizeDateInput(payload.work_date, existing.work_date || getTodayDateString());
    const updatedAt = payload.work_date === undefined ? new Date().toISOString() : buildTimestamp(nextWorkDate);
    db.prepare('UPDATE items SET title = ?, work_date = ?, updated_at = ? WHERE id = ?')
      .run(nextTitle, nextWorkDate, updatedAt, itemId);
    const earliestHistory = db.prepare(`
      SELECT id
      FROM item_division_history
      WHERE item_id = ?
      ORDER BY start_date ASC, id ASC
      LIMIT 1
    `).get(itemId);
    if (earliestHistory) {
      db.prepare('UPDATE item_division_history SET start_date = ?, updated_at = ? WHERE id = ?')
        .run(nextWorkDate, updatedAt, earliestHistory.id);
    }
    return { ...existing, title: nextTitle, work_date: nextWorkDate, updated_at: updatedAt };
  }

  function updateTodoMeta(todoId, payload = {}) {
    const existing = db.prepare('SELECT * FROM todos WHERE id = ?').get(todoId);
    if (!existing) return null;
    const nextText = String(payload.text ?? existing.text).trim() || existing.text;
    const nextWorkDate = payload.work_date === undefined
      ? existing.work_date
      : normalizeDateInput(payload.work_date, existing.work_date || getTodayDateString());
    const updatedAt = payload.work_date === undefined ? new Date().toISOString() : buildTimestamp(nextWorkDate);
    db.prepare('UPDATE todos SET text = ?, work_date = ?, updated_at = ? WHERE id = ?')
      .run(nextText, nextWorkDate, updatedAt, todoId);
    return { ...existing, text: nextText, work_date: nextWorkDate, updated_at: updatedAt };
  }

  return {
    DIVISION_ORDER,
    getTodayDateString,
    normalizeDateInput,
    getBoardSnapshot,
    createItem,
    moveItem,
    addTodo,
    toggleTodo,
    updateItemMeta,
    updateTodoMeta
  };
}

module.exports = { createWorkflowHistoryService };
