Files

943 lines
43 KiB
Swift

import Foundation
public final class AppDatabase {
public let database: SQLiteDatabase
public let path: String
private let encoder = JSONEncoder()
private let decoder = JSONDecoder()
public init(path: String, seedDemo: Bool = true) throws {
self.path = path
self.database = try SQLiteDatabase(path: path)
try migrate()
// Only seed on a genuinely fresh install. After the user explicitly
// deletes all data we persist a marker so demo data does not reappear.
if seedDemo,
try churchCount() == 0,
setting("suppressDemoSeed") != "1" {
try DemoData.seed(into: self)
}
}
public static func defaultPath() -> String {
let fm = FileManager.default
let base = fm.urls(for: .applicationSupportDirectory, in: .userDomainMask).first
?? fm.homeDirectoryForCurrentUser.appendingPathComponent("Library/Application Support")
let folder = base.appendingPathComponent("LektorenPlan", isDirectory: true)
try? fm.createDirectory(at: folder, withIntermediateDirectories: true)
return folder.appendingPathComponent("lektorenplan.sqlite").path
}
public static func makeInMemory(seedDemo: Bool = false) throws -> AppDatabase {
try AppDatabase(path: ":memory:", seedDemo: seedDemo)
}
private func migrate() throws {
let schema = """
CREATE TABLE IF NOT EXISTS churches (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
short_name TEXT NOT NULL,
symbol TEXT NOT NULL,
color_hex TEXT NOT NULL,
address TEXT NOT NULL DEFAULT '',
sort_index INTEGER NOT NULL DEFAULT 0,
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS people (
id TEXT PRIMARY KEY,
short_code TEXT NOT NULL DEFAULT '',
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
roles TEXT NOT NULL,
email TEXT NOT NULL DEFAULT '',
phone TEXT NOT NULL DEFAULT '',
preferred_church_ids TEXT NOT NULL DEFAULT '[]',
preferred_weekdays TEXT NOT NULL DEFAULT '[]',
preferred_weekday_periods TEXT NOT NULL DEFAULT '[]',
preferred_service_titles TEXT NOT NULL DEFAULT '[]',
max_per_month INTEGER NOT NULL DEFAULT 6,
allow_beyond_preferences INTEGER NOT NULL DEFAULT 0,
active INTEGER NOT NULL DEFAULT 1,
notes TEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS absences (
id TEXT PRIMARY KEY,
person_id TEXT NOT NULL REFERENCES people(id) ON DELETE CASCADE,
start_date TEXT NOT NULL,
end_date TEXT NOT NULL,
reason TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_absences_person ON absences(person_id);
CREATE TABLE IF NOT EXISTS service_templates (
id TEXT PRIMARY KEY,
church_id TEXT NOT NULL REFERENCES churches(id) ON DELETE CASCADE,
weekday INTEGER NOT NULL,
time_minutes INTEGER NOT NULL,
title TEXT NOT NULL,
lectors_needed INTEGER NOT NULL DEFAULT 1,
helpers_needed INTEGER NOT NULL DEFAULT 1,
valid_from TEXT NOT NULL,
valid_to TEXT,
active INTEGER NOT NULL DEFAULT 1,
notes TEXT NOT NULL DEFAULT ''
);
CREATE INDEX IF NOT EXISTS idx_templates_church ON service_templates(church_id);
CREATE TABLE IF NOT EXISTS service_exceptions (
id TEXT PRIMARY KEY,
template_id TEXT NOT NULL REFERENCES service_templates(id) ON DELETE CASCADE,
date TEXT NOT NULL,
reason TEXT NOT NULL DEFAULT '',
UNIQUE(template_id, date)
);
CREATE TABLE IF NOT EXISTS services (
id TEXT PRIMARY KEY,
church_id TEXT NOT NULL REFERENCES churches(id) ON DELETE CASCADE,
location TEXT NOT NULL DEFAULT '',
template_id TEXT REFERENCES service_templates(id) ON DELETE SET NULL,
date TEXT NOT NULL,
time_minutes INTEGER NOT NULL,
title TEXT NOT NULL,
lectors_needed INTEGER NOT NULL DEFAULT 1,
helpers_needed INTEGER NOT NULL DEFAULT 1,
kind TEXT NOT NULL DEFAULT 'regular',
notes TEXT NOT NULL DEFAULT '',
locked INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_services_date ON services(date);
CREATE INDEX IF NOT EXISTS idx_services_church ON services(church_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_services_template_date
ON services(template_id, date) WHERE template_id IS NOT NULL;
CREATE TABLE IF NOT EXISTS assignments (
id TEXT PRIMARY KEY,
service_id TEXT NOT NULL REFERENCES services(id) ON DELETE CASCADE,
person_id TEXT NOT NULL REFERENCES people(id) ON DELETE CASCADE,
role TEXT NOT NULL,
locked INTEGER NOT NULL DEFAULT 0,
beyond_preferences INTEGER NOT NULL DEFAULT 0,
note TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL,
UNIQUE(service_id, person_id, role)
);
CREATE INDEX IF NOT EXISTS idx_assignments_service ON assignments(service_id);
CREATE INDEX IF NOT EXISTS idx_assignments_person ON assignments(person_id);
CREATE TABLE IF NOT EXISTS liturgical_days (
date TEXT PRIMARY KEY,
title TEXT NOT NULL,
rank TEXT NOT NULL DEFAULT '',
color TEXT NOT NULL DEFAULT 'g',
remark TEXT NOT NULL DEFAULT '',
first_reading TEXT NOT NULL DEFAULT '',
psalm TEXT NOT NULL DEFAULT '',
second_reading TEXT NOT NULL DEFAULT '',
gospel TEXT NOT NULL DEFAULT '',
is_obligation INTEGER NOT NULL DEFAULT 0,
source TEXT NOT NULL DEFAULT '',
updated_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_liturgical_date ON liturgical_days(date);
CREATE TABLE IF NOT EXISTS settings (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS dispatch_preferences (
person_id TEXT PRIMARY KEY,
channel TEXT NOT NULL DEFAULT 'email',
variant TEXT NOT NULL DEFAULT 'compact',
cover INTEGER NOT NULL DEFAULT 1,
updated_at TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS dispatch_log (
id TEXT PRIMARY KEY,
batch_id TEXT NOT NULL,
range_start TEXT NOT NULL,
range_end TEXT NOT NULL,
person_id TEXT NOT NULL DEFAULT '',
person_name TEXT NOT NULL DEFAULT '',
channel TEXT NOT NULL,
variant TEXT NOT NULL DEFAULT '',
cover INTEGER NOT NULL DEFAULT 0,
email TEXT NOT NULL DEFAULT '',
status TEXT NOT NULL,
detail TEXT NOT NULL DEFAULT '',
created_at TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_dispatch_log_created ON dispatch_log(created_at);
"""
try database.execute(schema)
// Older databases predate the free-text location for special services.
try addColumnIfMissing(
table: "services",
column: "location",
definition: "TEXT NOT NULL DEFAULT ''"
)
// People gained a Kürzel, weekday time periods and permission to be
// planned beyond their preferences.
try addColumnIfMissing(table: "people", column: "short_code", definition: "TEXT NOT NULL DEFAULT ''")
try addColumnIfMissing(table: "people", column: "preferred_weekday_periods", definition: "TEXT NOT NULL DEFAULT '[]'")
try addColumnIfMissing(table: "people", column: "allow_beyond_preferences", definition: "INTEGER NOT NULL DEFAULT 0")
// Assignments record when a volunteer was planned beyond preferences.
try addColumnIfMissing(table: "assignments", column: "beyond_preferences", definition: "INTEGER NOT NULL DEFAULT 0")
try database.run("INSERT OR REPLACE INTO settings(key, value) VALUES('schemaVersion', '4')")
}
private func addColumnIfMissing(table: String, column: String, definition: String) throws {
let columns = try database.query("PRAGMA table_info(\(table))")
guard !columns.contains(where: { $0.string("name") == column }) else { return }
try database.execute("ALTER TABLE \(table) ADD COLUMN \(column) \(definition);")
}
// MARK: - Churches
public func churchCount() throws -> Int {
try database.scalarInt("SELECT COUNT(*) AS value FROM churches")
}
public func churches() throws -> [Church] {
try database.query("SELECT * FROM churches ORDER BY sort_index, name").compactMap { row in
guard let id = row.string("id"),
let name = row.string("name"),
let shortName = row.string("short_name"),
let symbolRaw = row.string("symbol")
else { return nil }
return Church(
id: id,
name: name,
shortName: shortName,
symbol: ChurchSymbol(rawValue: symbolRaw) ?? .chapel,
colorHex: row.string("color_hex", default: "#2F5D62"),
address: row.string("address", default: ""),
sortIndex: row.int("sort_index", default: 0),
notes: row.string("notes", default: "")
)
}
}
public func upsert(_ church: Church) throws {
try database.run("""
INSERT INTO churches(id, name, short_name, symbol, color_hex, address, sort_index, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
name=excluded.name, short_name=excluded.short_name, symbol=excluded.symbol,
color_hex=excluded.color_hex, address=excluded.address,
sort_index=excluded.sort_index, notes=excluded.notes
""", [
.text(church.id), .text(church.name), .text(church.shortName),
.text(church.symbol.rawValue), .text(church.colorHex), .text(church.address),
.int(church.sortIndex), .text(church.notes)
])
}
public func deleteChurch(id: String) throws {
try database.run("DELETE FROM churches WHERE id=?", [.text(id)])
}
// MARK: - People
public func people() throws -> [Person] {
try database.query("SELECT * FROM people ORDER BY last_name, first_name").compactMap { decodePerson($0) }
}
public func person(id: String) throws -> Person? {
try database.query("SELECT * FROM people WHERE id=?", [.text(id)]).first.flatMap(decodePerson)
}
public func upsert(_ person: Person) throws {
let roles = encode(person.roles.map(\.rawValue).sorted())
let churches = encode(person.preferredChurchIDs.sorted())
let weekdays = encode(person.preferredWeekdays.sorted())
let periods = encode(person.preferredWeekdayPeriods.sorted { $0.weekday < $1.weekday })
let titles = encode(person.preferredServiceTitles)
try database.run("""
INSERT INTO people(id, short_code, first_name, last_name, roles, email, phone, preferred_church_ids,
preferred_weekdays, preferred_weekday_periods, preferred_service_titles,
max_per_month, allow_beyond_preferences, active, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
short_code=excluded.short_code,
first_name=excluded.first_name, last_name=excluded.last_name, roles=excluded.roles,
email=excluded.email, phone=excluded.phone,
preferred_church_ids=excluded.preferred_church_ids,
preferred_weekdays=excluded.preferred_weekdays,
preferred_weekday_periods=excluded.preferred_weekday_periods,
preferred_service_titles=excluded.preferred_service_titles,
max_per_month=excluded.max_per_month,
allow_beyond_preferences=excluded.allow_beyond_preferences,
active=excluded.active, notes=excluded.notes
""", [
.text(person.id), .text(person.shortCode.trimmingCharacters(in: .whitespacesAndNewlines)),
.text(person.firstName), .text(person.lastName), .text(roles),
.text(person.email), .text(person.phone), .text(churches), .text(weekdays),
.text(periods), .text(titles), .int(person.maxPerMonth),
.bool(person.allowBeyondPreferences), .bool(person.active), .text(person.notes)
])
}
public func deletePerson(id: String) throws {
try database.run("DELETE FROM people WHERE id=?", [.text(id)])
}
private func decodePerson(_ row: SQLiteRow) -> Person? {
guard let id = row.string("id"),
let first = row.string("first_name"),
let last = row.string("last_name")
else { return nil }
let roleStrings: [String] = decode(row.string("roles", default: "[]"))
return Person(
id: id,
shortCode: row.string("short_code", default: ""),
firstName: first,
lastName: last,
roles: Set(roleStrings.compactMap(Role.init(rawValue:))),
email: row.string("email", default: ""),
phone: row.string("phone", default: ""),
preferredChurchIDs: Set(decode(row.string("preferred_church_ids", default: "[]")) as [String]),
preferredWeekdays: Set(decode(row.string("preferred_weekdays", default: "[]")) as [Int]),
preferredWeekdayPeriods: decode(row.string("preferred_weekday_periods", default: "[]")),
preferredServiceTitles: decode(row.string("preferred_service_titles", default: "[]")),
maxPerMonth: row.int("max_per_month", default: 6),
allowBeyondPreferences: row.bool("allow_beyond_preferences", default: false),
active: row.bool("active", default: true),
notes: row.string("notes", default: "")
)
}
// MARK: - Absences
public func absences() throws -> [Absence] {
try database.query("SELECT * FROM absences ORDER BY start_date").compactMap { row in
guard let id = row.string("id"), let personID = row.string("person_id"),
let start = row.date("start_date"), let end = row.date("end_date")
else { return nil }
return Absence(id: id, personID: personID, start: start, end: end, reason: row.string("reason", default: ""))
}
}
public func absences(personID: String) throws -> [Absence] {
try database.query("SELECT * FROM absences WHERE person_id=? ORDER BY start_date", [.text(personID)]).compactMap { row in
guard let id = row.string("id"), let start = row.date("start_date"), let end = row.date("end_date") else { return nil }
return Absence(id: id, personID: personID, start: start, end: end, reason: row.string("reason", default: ""))
}
}
public func upsert(_ absence: Absence) throws {
try database.run("""
INSERT INTO absences(id, person_id, start_date, end_date, reason) VALUES(?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET person_id=excluded.person_id, start_date=excluded.start_date,
end_date=excluded.end_date, reason=excluded.reason
""", [
.text(absence.id), .text(absence.personID), .dateKey(absence.start),
.dateKey(absence.end), .text(absence.reason)
])
}
public func deleteAbsence(id: String) throws {
try database.run("DELETE FROM absences WHERE id=?", [.text(id)])
}
// MARK: - Service templates
public func templates() throws -> [ServiceTemplate] {
try database.query("SELECT * FROM service_templates ORDER BY weekday, time_minutes").compactMap { row in
guard let id = row.string("id"), let churchID = row.string("church_id"),
let title = row.string("title"), let from = row.date("valid_from")
else { return nil }
return ServiceTemplate(
id: id,
churchID: churchID,
weekday: row.int("weekday", default: 1),
timeMinutes: row.int("time_minutes", default: 600),
title: title,
lectorsNeeded: row.int("lectors_needed", default: 1),
helpersNeeded: row.int("helpers_needed", default: 1),
validFrom: from,
validTo: row.date("valid_to"),
active: row.bool("active", default: true),
notes: row.string("notes", default: "")
)
}
}
public func upsert(_ template: ServiceTemplate) throws {
try database.run("""
INSERT INTO service_templates(id, church_id, weekday, time_minutes, title, lectors_needed,
helpers_needed, valid_from, valid_to, active, notes)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET church_id=excluded.church_id, weekday=excluded.weekday,
time_minutes=excluded.time_minutes, title=excluded.title,
lectors_needed=excluded.lectors_needed, helpers_needed=excluded.helpers_needed,
valid_from=excluded.valid_from, valid_to=excluded.valid_to,
active=excluded.active, notes=excluded.notes
""", [
.text(template.id), .text(template.churchID), .int(template.weekday),
.int(template.timeMinutes), .text(template.title), .int(template.lectorsNeeded),
.int(template.helpersNeeded), .dateKey(template.validFrom),
template.validTo.map(SQLiteValue.dateKey) ?? .null,
.bool(template.active), .text(template.notes)
])
}
public func deleteTemplate(id: String) throws {
try database.run("DELETE FROM service_templates WHERE id=?", [.text(id)])
}
// MARK: - Exceptions
public func exceptions() throws -> [ServiceException] {
try database.query("SELECT * FROM service_exceptions ORDER BY date").compactMap { row in
guard let id = row.string("id"), let templateID = row.string("template_id"), let date = row.date("date") else { return nil }
return ServiceException(id: id, templateID: templateID, date: date, reason: row.string("reason", default: ""))
}
}
public func upsert(_ exception: ServiceException) throws {
try database.run("""
INSERT INTO service_exceptions(id, template_id, date, reason) VALUES(?, ?, ?, ?)
ON CONFLICT(template_id, date) DO UPDATE SET reason=excluded.reason
""", [.text(exception.id), .text(exception.templateID), .dateKey(exception.date), .text(exception.reason)])
}
public func deleteException(id: String) throws {
try database.run("DELETE FROM service_exceptions WHERE id=?", [.text(id)])
}
// MARK: - Services
public func services(in range: DayRange) throws -> [ServiceOccurrence] {
try database.query("""
SELECT * FROM services WHERE date >= ? AND date <= ?
ORDER BY date, time_minutes, church_id
""", [.dateKey(range.start), .dateKey(range.end)]).compactMap(decodeService)
}
public func services(on date: Date) throws -> [ServiceOccurrence] {
try database.query("SELECT * FROM services WHERE date=? ORDER BY time_minutes", [.dateKey(date)]).compactMap(decodeService)
}
public func service(id: String) throws -> ServiceOccurrence? {
try database.query("SELECT * FROM services WHERE id=?", [.text(id)]).first.flatMap(decodeService)
}
public func specialServices() throws -> [ServiceOccurrence] {
try database.query("SELECT * FROM services WHERE kind='special' ORDER BY date, time_minutes").compactMap(decodeService)
}
public func serviceCount() throws -> Int {
try database.scalarInt("SELECT COUNT(*) AS value FROM services")
}
public func upsert(_ service: ServiceOccurrence) throws {
try database.run("""
INSERT INTO services(id, church_id, location, template_id, date, time_minutes, title, lectors_needed,
helpers_needed, kind, notes, locked, created_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET church_id=excluded.church_id, location=excluded.location,
template_id=excluded.template_id,
date=excluded.date, time_minutes=excluded.time_minutes, title=excluded.title,
lectors_needed=excluded.lectors_needed, helpers_needed=excluded.helpers_needed,
kind=excluded.kind, notes=excluded.notes, locked=excluded.locked
""", [
.text(service.id), .text(service.churchID), .text(service.location ?? ""),
service.templateID.map(SQLiteValue.text) ?? .null,
.dateKey(service.date), .int(service.timeMinutes), .text(service.title),
.int(service.lectorsNeeded), .int(service.helpersNeeded), .text(service.kind.rawValue),
.text(service.notes), .bool(service.locked), .text(ISO8601DateFormatter().string(from: service.createdAt))
])
}
public func deleteService(id: String) throws {
try database.run("DELETE FROM services WHERE id=?", [.text(id)])
}
private func decodeService(_ row: SQLiteRow) -> ServiceOccurrence? {
guard let id = row.string("id"), let churchID = row.string("church_id"),
let date = row.date("date"), let title = row.string("title")
else { return nil }
return ServiceOccurrence(
id: id,
churchID: churchID,
location: row.string("location", default: ""),
templateID: row.string("template_id"),
date: date,
timeMinutes: row.int("time_minutes", default: 600),
title: title,
lectorsNeeded: row.int("lectors_needed", default: 1),
helpersNeeded: row.int("helpers_needed", default: 1),
kind: ServiceKind(rawValue: row.string("kind", default: "regular")) ?? .regular,
notes: row.string("notes", default: ""),
locked: row.bool("locked", default: false),
createdAt: ISO8601DateFormatter().date(from: row.string("created_at", default: "")) ?? Date()
)
}
/// Generates regular occurrences from the current service order. Existing occurrences
/// that already have assignments are never overwritten.
@discardableResult
public func generateServices(in range: DayRange, regenerateUnassigned: Bool = true) throws -> Int {
let allTemplates = try templates()
let allExceptions = try exceptions()
let exceptionKeys = Set(allExceptions.map { "\($0.templateID)|\(DayMath.key($0.date))" })
let existing = try services(in: range)
var existingByTemplate: [String: ServiceOccurrence] = [:]
for service in existing {
if let templateID = service.templateID {
existingByTemplate["\(templateID)|\(service.dateKey)"] = service
}
}
var created = 0
try database.transaction {
for day in range.days {
for template in allTemplates where template.isValid(on: day) {
let key = "\(template.id)|\(DayMath.key(day))"
if exceptionKeys.contains(key) { continue }
if let existing = existingByTemplate[key] {
guard regenerateUnassigned else { continue }
let assignmentCount = try assignmentCount(serviceID: existing.id)
if assignmentCount == 0 {
var updated = existing
updated.timeMinutes = template.timeMinutes
updated.title = template.title
updated.lectorsNeeded = template.lectorsNeeded
updated.helpersNeeded = template.helpersNeeded
try upsert(updated)
}
continue
}
let service = ServiceOccurrence(
churchID: template.churchID,
templateID: template.id,
date: day,
timeMinutes: template.timeMinutes,
title: template.title,
lectorsNeeded: template.lectorsNeeded,
helpersNeeded: template.helpersNeeded,
kind: .regular,
notes: template.notes
)
try upsert(service)
created += 1
}
}
}
return created
}
private func assignmentCount(serviceID: String) throws -> Int {
try database.scalarInt("SELECT COUNT(*) AS value FROM assignments WHERE service_id=?", [.text(serviceID)])
}
// MARK: - Assignments
public func assignments(in range: DayRange) throws -> [Assignment] {
try database.query("""
SELECT a.* FROM assignments a JOIN services s ON s.id = a.service_id
WHERE s.date >= ? AND s.date <= ?
ORDER BY s.date, s.time_minutes
""", [.dateKey(range.start), .dateKey(range.end)]).compactMap(decodeAssignment)
}
public func assignments(serviceIDs: [String]) throws -> [Assignment] {
guard !serviceIDs.isEmpty else { return [] }
let placeholders = Array(repeating: "?", count: serviceIDs.count).joined(separator: ",")
return try database.query(
"SELECT * FROM assignments WHERE service_id IN (\(placeholders))",
serviceIDs.map(SQLiteValue.text)
).compactMap(decodeAssignment)
}
public func assignments(personID: String) throws -> [Assignment] {
try database.query("SELECT * FROM assignments WHERE person_id=?", [.text(personID)]).compactMap(decodeAssignment)
}
public func assignmentCount() throws -> Int {
try database.scalarInt("SELECT COUNT(*) AS value FROM assignments")
}
public func upsert(_ assignment: Assignment) throws {
try database.run("""
INSERT INTO assignments(id, service_id, person_id, role, locked, beyond_preferences, note, created_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(id) DO UPDATE SET service_id=excluded.service_id, person_id=excluded.person_id,
role=excluded.role, locked=excluded.locked,
beyond_preferences=excluded.beyond_preferences, note=excluded.note
""", [
.text(assignment.id), .text(assignment.serviceID), .text(assignment.personID),
.text(assignment.role.rawValue), .bool(assignment.locked),
.bool(assignment.beyondPreferences), .text(assignment.note),
.text(ISO8601DateFormatter().string(from: assignment.createdAt))
])
}
/// Replaces the assignment for one role of a service. Passing nil clears the slot.
public func setAssignment(
serviceID: String,
role: Role,
personID: String?,
locked: Bool = false,
beyondPreferences: Bool = false
) throws {
try database.transaction {
try database.run("DELETE FROM assignments WHERE service_id=? AND role=?", [.text(serviceID), .text(role.rawValue)])
guard let personID else { return }
let assignment = Assignment(
serviceID: serviceID, personID: personID, role: role,
locked: locked, beyondPreferences: beyondPreferences
)
try upsert(assignment)
}
}
public func setAssignment(
serviceID: String,
personID: String?,
role: Role,
locked: Bool = false,
beyondPreferences: Bool = false
) throws {
try setAssignment(
serviceID: serviceID, role: role, personID: personID,
locked: locked, beyondPreferences: beyondPreferences
)
}
public func deleteAssignment(id: String) throws {
try database.run("DELETE FROM assignments WHERE id=?", [.text(id)])
}
public func toggleAssignmentLock(id: String) throws {
try database.run("UPDATE assignments SET locked = CASE locked WHEN 0 THEN 1 ELSE 0 END WHERE id=?", [.text(id)])
}
public func savePlan(_ assignments: [Assignment], in range: DayRange, replaceUnlocked: Bool) throws {
let serviceIDs = try services(in: range).map(\.id)
try database.transaction {
if replaceUnlocked, !serviceIDs.isEmpty {
let placeholders = Array(repeating: "?", count: serviceIDs.count).joined(separator: ",")
try database.run(
"DELETE FROM assignments WHERE locked=0 AND service_id IN (\(placeholders))",
serviceIDs.map(SQLiteValue.text)
)
}
for assignment in assignments {
try upsert(assignment)
}
}
}
private func decodeAssignment(_ row: SQLiteRow) -> Assignment? {
guard let id = row.string("id"), let serviceID = row.string("service_id"),
let personID = row.string("person_id"), let roleRaw = row.string("role"),
let role = Role(rawValue: roleRaw)
else { return nil }
return Assignment(
id: id,
serviceID: serviceID,
personID: personID,
role: role,
locked: row.bool("locked", default: false),
beyondPreferences: row.bool("beyond_preferences", default: false),
note: row.string("note", default: ""),
createdAt: ISO8601DateFormatter().date(from: row.string("created_at", default: "")) ?? Date()
)
}
// MARK: - Liturgical days
public func liturgicalDays(in range: DayRange) throws -> [LiturgicalDay] {
try database.query("SELECT * FROM liturgical_days WHERE date >= ? AND date <= ? ORDER BY date",
[.dateKey(range.start), .dateKey(range.end)]).compactMap(decodeLiturgicalDay)
}
public func liturgicalDay(on date: Date) throws -> LiturgicalDay? {
try database.query("SELECT * FROM liturgical_days WHERE date=?", [.dateKey(date)]).first.flatMap(decodeLiturgicalDay)
}
public func liturgicalDayCount() throws -> Int {
try database.scalarInt("SELECT COUNT(*) AS value FROM liturgical_days")
}
public func liturgicalYears() throws -> [Int] {
try database.query("SELECT DISTINCT substr(date,1,4) AS year FROM liturgical_days ORDER BY year").compactMap {
Int($0.string("year", default: "0"))
}
}
public func upsert(liturgicalDay: LiturgicalDay) throws {
try database.run("""
INSERT INTO liturgical_days(date, title, rank, color, remark, first_reading, psalm,
second_reading, gospel, is_obligation, source, updated_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(date) DO UPDATE SET title=excluded.title, rank=excluded.rank, color=excluded.color,
remark=excluded.remark, first_reading=excluded.first_reading, psalm=excluded.psalm,
second_reading=excluded.second_reading, gospel=excluded.gospel,
is_obligation=excluded.is_obligation, source=excluded.source, updated_at=excluded.updated_at
""", [
.dateKey(liturgicalDay.date), .text(liturgicalDay.title), .text(liturgicalDay.rank.rawValue),
.text(liturgicalDay.color.rawValue), .text(liturgicalDay.remark),
.text(liturgicalDay.readings.first), .text(liturgicalDay.readings.psalm),
.text(liturgicalDay.readings.second), .text(liturgicalDay.readings.gospel),
.bool(liturgicalDay.isObligation), .text(liturgicalDay.source),
.text(ISO8601DateFormatter().string(from: liturgicalDay.updatedAt))
])
}
public func upsert(liturgicalDays days: [LiturgicalDay]) throws {
try database.transaction {
for day in days { try upsert(liturgicalDay: day) }
}
}
public func replaceLiturgicalYear(_ year: Int, with days: [LiturgicalDay]) throws {
try database.transaction {
try database.run("DELETE FROM liturgical_days WHERE date LIKE ?", [.text("\(year)-%")])
for day in days { try upsert(liturgicalDay: day) }
}
}
private func decodeLiturgicalDay(_ row: SQLiteRow) -> LiturgicalDay? {
guard let date = row.date("date") else { return nil }
return LiturgicalDay(
date: date,
title: row.string("title", default: ""),
rank: LiturgicalRank(rawValue: row.string("rank", default: "")) ?? .weekday,
color: LiturgicalColor(rawValue: row.string("color", default: "g")) ?? .green,
remark: row.string("remark", default: ""),
readings: Readings(
first: row.string("first_reading", default: ""),
psalm: row.string("psalm", default: ""),
second: row.string("second_reading", default: ""),
gospel: row.string("gospel", default: "")
),
isObligation: row.bool("is_obligation"),
source: row.string("source", default: ""),
updatedAt: ISO8601DateFormatter().date(from: row.string("updated_at", default: "")) ?? Date()
)
}
// MARK: - Dispatch
public func dispatchPreferences() throws -> [String: DispatchPreference] {
try database.query("SELECT * FROM dispatch_preferences").reduce(into: [:]) { result, row in
guard let personID = row.string("person_id") else { return }
result[personID] = DispatchPreference(
channel: DispatchChannel(rawValue: row.string("channel", default: "email")) ?? .email,
variant: PlanListVariant(rawValue: row.string("variant", default: "compact")) ?? .compact,
cover: row.bool("cover", default: true)
)
}
}
public func saveDispatchPreference(_ preference: DispatchPreference, personID: String) {
try? database.run("""
INSERT INTO dispatch_preferences(person_id, channel, variant, cover, updated_at)
VALUES(?, ?, ?, ?, ?)
ON CONFLICT(person_id) DO UPDATE SET
channel=excluded.channel, variant=excluded.variant,
cover=excluded.cover, updated_at=excluded.updated_at
""", [
.text(personID), .text(preference.channel.rawValue),
.text(preference.variant.rawValue), .bool(preference.cover),
.text(ISO8601DateFormatter().string(from: Date()))
])
}
public func dispatchLog(limit: Int = 80) throws -> [DispatchLogEntry] {
let bounded = max(1, min(limit, 500))
return try database.query("""
SELECT * FROM dispatch_log ORDER BY created_at DESC, id DESC LIMIT ?
""", [.int(bounded)]).compactMap { row in
guard let id = row.string("id"),
let batchID = row.string("batch_id"),
let start = row.date("range_start"),
let end = row.date("range_end"),
let channel = DispatchChannel(rawValue: row.string("channel", default: "print")),
let status = DispatchStatus(rawValue: row.string("status", default: "pdf"))
else { return nil }
let created = ISO8601DateFormatter().date(from: row.string("created_at", default: "")) ?? Date()
return DispatchLogEntry(
id: id,
batchID: batchID,
rangeStart: start,
rangeEnd: end,
personID: row.string("person_id", default: ""),
personName: row.string("person_name", default: ""),
channel: channel,
variant: PlanListVariant(rawValue: row.string("variant", default: "compact")) ?? .compact,
cover: row.bool("cover"),
email: row.string("email", default: ""),
status: status,
detail: row.string("detail", default: ""),
createdAt: created
)
}
}
public func appendDispatchLog(_ entries: [DispatchLogEntry]) throws {
guard !entries.isEmpty else { return }
try database.transaction {
let formatter = ISO8601DateFormatter()
for entry in entries {
try database.run("""
INSERT INTO dispatch_log(id, batch_id, range_start, range_end, person_id, person_name,
channel, variant, cover, email, status, detail, created_at)
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""", [
.text(entry.id), .text(entry.batchID),
.dateKey(entry.rangeStart), .dateKey(entry.rangeEnd),
.text(entry.personID), .text(entry.personName),
.text(entry.channel.rawValue), .text(entry.variant.rawValue),
.bool(entry.cover), .text(entry.email),
.text(entry.status.rawValue), .text(entry.detail),
.text(formatter.string(from: entry.createdAt))
])
}
}
}
public func deleteDispatchLog() throws {
try database.run("DELETE FROM dispatch_log")
}
// MARK: - Settings
public func setting(_ key: String) -> String? {
guard let row = try? database.query("SELECT value FROM settings WHERE key=?", [.text(key)]).first else { return nil }
return row.string("value")
}
public func setSetting(_ key: String, value: String) {
try? database.run("INSERT INTO settings(key, value) VALUES(?, ?) ON CONFLICT(key) DO UPDATE SET value=excluded.value",
[.text(key), .text(value)])
}
public func removeSetting(_ key: String) {
try? database.run("DELETE FROM settings WHERE key=?", [.text(key)])
}
// MARK: - Resources / exchange
public func resources() throws -> ResourceBundle {
ResourceBundle(
churches: try churches(),
people: try people(),
absences: try absences(),
templates: try templates(),
exceptions: try exceptions()
)
}
public func applyResourceBundle(_ bundle: ResourceBundle, replaceExisting: Bool = false) throws {
try database.transaction {
if replaceExisting {
for template in try templates() { try deleteTemplate(id: template.id) }
for person in try people() { try deletePerson(id: person.id) }
for church in try churches() { try deleteChurch(id: church.id) }
}
for church in bundle.churches { try upsert(church) }
for person in bundle.people { try upsert(person) }
for absence in bundle.absences { try upsert(absence) }
for template in bundle.templates { try upsert(template) }
for exception in bundle.exceptions { try upsert(exception) }
}
}
public func exportPlan(range: DayRange, scope: ExchangeScope = .plan) throws -> PlanExchange {
let services = scope == .resources ? [] : try services(in: range)
let serviceIDs = services.map(\.id)
let assignments = scope == .resources ? [] : try assignments(serviceIDs: serviceIDs)
let peopleIDs = Set(assignments.map(\.personID))
let allPeople = try people()
let relevantPeople = scope == .resources ? allPeople : allPeople.filter { peopleIDs.contains($0.id) }
let allAbsences = try absences()
let relevantAbsences = scope == .resources ? allAbsences : allAbsences.filter { peopleIDs.contains($0.personID) }
return PlanExchange(
scope: scope,
range: range,
resources: ResourceBundle(
churches: try churches(),
people: relevantPeople,
absences: relevantAbsences,
templates: try templates(),
exceptions: try exceptions()
),
services: services,
assignments: assignments,
liturgicalDays: scope == .resources ? [] : try liturgicalDays(in: range),
note: "Export aus LektorenPlan · \(DayMath.key(range.start)) bis \(DayMath.key(range.end))"
)
}
public func applyPlanExchange(_ exchange: PlanExchange) throws -> ExchangeSummary {
try database.transaction {
for church in exchange.resources.churches { try upsert(church) }
for person in exchange.resources.people { try upsert(person) }
for absence in exchange.resources.absences { try upsert(absence) }
for template in exchange.resources.templates { try upsert(template) }
for exception in exchange.resources.exceptions { try upsert(exception) }
for day in exchange.liturgicalDays { try upsert(liturgicalDay: day) }
for service in exchange.services { try upsert(service) }
for assignment in exchange.assignments { try upsert(assignment) }
}
return exchange.summary
}
// MARK: - Utilities
private func encode<T: Encodable>(_ value: T) -> String {
guard let data = try? encoder.encode(value) else { return "[]" }
return String(data: data, encoding: .utf8) ?? "[]"
}
private func decode<T: Decodable>(_ string: String) -> T {
guard let data = string.data(using: .utf8),
let value = try? decoder.decode(T.self, from: data)
else {
if T.self == [String].self { return [] as! T }
if T.self == [Int].self { return [] as! T }
return [] as! T
}
return value
}
/// Removes all content while keeping application settings (range, preferences).
/// By default a marker is stored so the demo dataset is not re-seeded on the
/// next launch; resetting the demo clears that marker again.
public func deleteAllData(markDemoSuppressed: Bool = true) throws {
try database.transaction {
try database.execute("""
DELETE FROM assignments; DELETE FROM services; DELETE FROM service_exceptions;
DELETE FROM service_templates; DELETE FROM absences; DELETE FROM people;
DELETE FROM churches; DELETE FROM liturgical_days;
DELETE FROM dispatch_preferences; DELETE FROM dispatch_log;
""")
if markDemoSuppressed {
try database.run("INSERT OR REPLACE INTO settings(key, value) VALUES('suppressDemoSeed', '1')")
}
}
}
public func resetDemoData() throws {
try deleteAllData(markDemoSuppressed: false)
try database.run("DELETE FROM settings WHERE key='suppressDemoSeed'")
try DemoData.seed(into: self)
}
public func databaseSizeBytes() -> Int {
guard path != ":memory:" else { return 0 }
let attributes = try? FileManager.default.attributesOfItem(atPath: path)
return (attributes?[.size] as? NSNumber)?.intValue ?? 0
}
}