fiddy/apps/web/lib/server/schedules.ts

if (process.env.NODE_ENV !== "test")
    require("server-only");
import getPool from "@/lib/server/db";
import { createEntry, requireActiveGroup } from "@/lib/server/entries";
import { enforceUserWriteRateLimit } from "@/lib/server/rate-limit";
import { listTagsForSchedules, normalizeTags, requireExistingTagsForGroup, setScheduleTags } from "@/lib/server/tags";
import type { Schedule, ScheduleEndCondition, ScheduleFrequency } from "@/lib/shared/types";

type ScheduleRow = {
    id: number;
    entry_type: "SPENDING" | "INCOME";
    amount_dollars: string | number;
    necessity: "NECESSARY" | "BOTH" | "UNNECESSARY";
    purchase_type: string;
    notes: string | null;
    starts_on: string;
    frequency: ScheduleFrequency;
    interval_count: number;
    end_condition: ScheduleEndCondition;
    end_count: number | null;
    end_date: string | null;
    next_run_on: string;
    last_run_on: string | null;
    run_count: number;
    is_active: boolean;
    legacy_source_entry_id: number | null;
};

type CreateScheduleInput = {
    groupId: number;
    userId: number;
    entryType: "SPENDING" | "INCOME";
    amountDollars: number;
    necessity: "NECESSARY" | "BOTH" | "UNNECESSARY";
    purchaseType: string;
    notes?: string;
    tags?: string[];
    startsOn: string;
    frequency: ScheduleFrequency;
    intervalCount?: number;
    endCondition?: ScheduleEndCondition;
    endCount?: number | null;
    endDate?: string | null;
    createEntryNow?: boolean;
};

type UpdateScheduleInput = {
    id: number;
    groupId: number;
    userId: number;
    entryType: "SPENDING" | "INCOME";
    amountDollars: number;
    necessity: "NECESSARY" | "BOTH" | "UNNECESSARY";
    purchaseType: string;
    notes?: string;
    tags?: string[];
    startsOn: string;
    frequency: ScheduleFrequency;
    intervalCount?: number;
    endCondition?: ScheduleEndCondition;
    endCount?: number | null;
    endDate?: string | null;
    nextRunOn?: string;
    isActive?: boolean;
};

export { requireActiveGroup };

function addInterval(dateIso: string, frequency: ScheduleFrequency, intervalCount: number) {
    const safeInterval = Math.max(1, Number(intervalCount || 1));
    const date = new Date(`${dateIso}T00:00:00Z`);
    if (Number.isNaN(date.getTime())) return dateIso;
    if (frequency === "DAILY") date.setUTCDate(date.getUTCDate() + safeInterval);
    else if (frequency === "WEEKLY") date.setUTCDate(date.getUTCDate() + safeInterval * 7);
    else if (frequency === "MONTHLY") date.setUTCMonth(date.getUTCMonth() + safeInterval);
    else date.setUTCFullYear(date.getUTCFullYear() + safeInterval);
    return date.toISOString().slice(0, 10);
}

function toSchedule(row: ScheduleRow, tags: string[]): Schedule {
    return {
        id: Number(row.id),
        entryType: row.entry_type,
        amountDollars: Number(row.amount_dollars),
        necessity: row.necessity,
        purchaseType: row.purchase_type,
        notes: row.notes,
        startsOn: row.starts_on,
        frequency: row.frequency,
        intervalCount: Number(row.interval_count || 1),
        endCondition: row.end_condition,
        endCount: row.end_count,
        endDate: row.end_date,
        nextRunOn: row.next_run_on,
        lastRunOn: row.last_run_on,
        runCount: Number(row.run_count || 0),
        isActive: Boolean(row.is_active),
        legacySourceEntryId: row.legacy_source_entry_id == null ? null : Number(row.legacy_source_entry_id),
        tags
    };
}

export async function listSchedules(groupId: number): Promise<Schedule[]> {
    const pool = getPool();
    const rows = (await pool.query<ScheduleRow>(
        `select id, entry_type, amount_dollars, necessity, purchase_type, notes, starts_on, frequency, interval_count,
                end_condition, end_count, end_date, next_run_on, last_run_on, run_count, is_active, legacy_source_entry_id
         from schedules
         where group_id=$1
         order by next_run_on asc, id asc`,
        [groupId]
    )).rows;
    const scheduleIds = rows.map(row => Number(row.id));
    const tagsMap = await listTagsForSchedules({ groupId, scheduleIds });
    return rows.map(row => toSchedule(row, tagsMap.get(Number(row.id)) || []));
}

export async function createSchedule(input: CreateScheduleInput): Promise<Schedule> {
    await enforceUserWriteRateLimit({ userId: input.userId, scope: "schedules:create" });
    const pool = getPool();
    const safeIntervalCount = Math.max(1, Number(input.intervalCount || 1));
    const endCondition = input.endCondition || "NEVER";
    const rows = (await pool.query<ScheduleRow>(
        `insert into schedules(group_id, created_by, entry_type, amount_dollars, necessity, purchase_type, notes, starts_on,
                               frequency, interval_count, end_condition, end_count, end_date, next_run_on, is_active)
         values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,true)
         returning id, entry_type, amount_dollars, necessity, purchase_type, notes, starts_on, frequency, interval_count,
                   end_condition, end_count, end_date, next_run_on, last_run_on, run_count, is_active, legacy_source_entry_id`,
        [
            input.groupId,
            input.userId,
            input.entryType,
            input.amountDollars,
            input.necessity,
            input.purchaseType,
            input.notes || null,
            input.startsOn,
            input.frequency,
            safeIntervalCount,
            endCondition,
            input.endCount ?? null,
            input.endDate || null,
            input.startsOn
        ]
    )).rows;
    const created = rows[0];
    const tags = normalizeTags(input.tags || []);
    const tagIds = await requireExistingTagsForGroup({ groupId: input.groupId, tags });
    await setScheduleTags({ scheduleId: Number(created.id), tagIds });

    if (input.createEntryNow) {
        const nextRunOn = addInterval(input.startsOn, input.frequency, safeIntervalCount);
        await createEntry({
            groupId: input.groupId,
            userId: input.userId,
            entryType: input.entryType,
            amountDollars: input.amountDollars,
            occurredAt: input.startsOn,
            necessity: input.necessity,
            purchaseType: input.purchaseType,
            notes: input.notes,
            tags,
            sourceScheduleId: Number(created.id)
        });
        const updatedRows = (await pool.query<ScheduleRow>(
            `update schedules
             set next_run_on=$1, last_run_on=$2, run_count=1, updated_at=now()
             where id=$3 and group_id=$4
             returning id, entry_type, amount_dollars, necessity, purchase_type, notes, starts_on, frequency, interval_count,
                       end_condition, end_count, end_date, next_run_on, last_run_on, run_count, is_active, legacy_source_entry_id`,
            [nextRunOn, input.startsOn, Number(created.id), input.groupId]
        )).rows;
        return toSchedule(updatedRows[0], tags);
    }

    return toSchedule(created, tags);
}

export async function updateSchedule(input: UpdateScheduleInput): Promise<Schedule | null> {
    await enforceUserWriteRateLimit({ userId: input.userId, scope: "schedules:update" });
    const pool = getPool();
    const safeIntervalCount = Math.max(1, Number(input.intervalCount || 1));
    const endCondition = input.endCondition || "NEVER";
    const nextRunOn = input.nextRunOn || input.startsOn;
    const rows = (await pool.query<ScheduleRow>(
        `update schedules
         set entry_type=$1,
             amount_dollars=$2,
             necessity=$3,
             purchase_type=$4,
             notes=$5,
             starts_on=$6,
             frequency=$7,
             interval_count=$8,
             end_condition=$9,
             end_count=$10,
             end_date=$11,
             next_run_on=$12,
             is_active=$13,
             updated_at=now()
         where id=$14 and group_id=$15
         returning id, entry_type, amount_dollars, necessity, purchase_type, notes, starts_on, frequency, interval_count,
                   end_condition, end_count, end_date, next_run_on, last_run_on, run_count, is_active, legacy_source_entry_id`,
        [
            input.entryType,
            input.amountDollars,
            input.necessity,
            input.purchaseType,
            input.notes || null,
            input.startsOn,
            input.frequency,
            safeIntervalCount,
            endCondition,
            input.endCount ?? null,
            input.endDate || null,
            nextRunOn,
            input.isActive ?? true,
            input.id,
            input.groupId
        ]
    )).rows;
    if (!rows[0]) return null;
    const tags = normalizeTags(input.tags || []);
    const tagIds = await requireExistingTagsForGroup({ groupId: input.groupId, tags });
    await setScheduleTags({ scheduleId: Number(input.id), tagIds });
    return toSchedule(rows[0], tags);
}

export async function deleteSchedule(input: { id: number; groupId: number; userId: number }) {
    await enforceUserWriteRateLimit({ userId: input.userId, scope: "schedules:delete" });
    const pool = getPool();
    await pool.query("delete from schedules where id=$1 and group_id=$2", [input.id, input.groupId]);
}